索引是帮助mysql高效获取数据的数据结构,可以简单理解为,已经排好序的用于快速查找的数据结构。

排序和快速查找是关键。

索引会影响到order by排序。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

索引的优势

通过索引可以降低数据的查询成本,提高查询性能,降低数据库IO成本。

通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

索引的劣势

索引也会占用磁盘空间。

索引虽然提高了查询速度,但是更新表时,不仅要更新数据,也要更新索引表,效率会降低。

分类

单值索引

一个索引只包含单个列,一个表可以由多个单列索引

唯一索引

索引列的值必须唯一,但允许由空值。

复合索引

一个索引包含多个列

基本语法

create [unique] index (indexname) on tablename(columname(length));//创建索引
alter tablename add [unique] index (indexname) on tablename(columname(length));//添加索引
drop index indexname on tablename;//删除索引
show index from tablename\G;//查看索引 \g用来格式化显示的内容

索引结构

B+Tree索引结构

什么情况下需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其他表相关联的字段,外键关系建立索引。

4.频繁更新的字段不适合创建索引

5.where条件里用不到的字段不创建索引

6.单键/组合索引的选择问题,高并发下倾向创建组合索引

7.查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

8.查询中统计或者分组字段

什么情况不需要创建索引

1.表记录太少

2.经常增删改的表

3.数据重复且分布比较平均的表字段

性能分析

Mysql Query Optimizer

MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块,它主要的功能就是通过计算分析系统中收集的各种统计信息,为客户端请求的Query 给出他认为最优的执行计划,也就是他认为最优的数据检索方式。

mysql常见瓶颈
CPU,IO,服务器的硬件瓶颈。
explain
explain select * from employees;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE employees null ALL null null null null 107 100.00 null
id:select查询的序列号,表示查询中执行select子句或操作表的顺序
id相同,执行顺序由上至下。
EXPLAIN SELECT
e.*
FROM
employees e,
departments d,
jobs j
WHERE
e.department_id = d.department_id
AND j.job_id = e.job_id;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE j null index PRIMARY PRIMARY 22 null 19 100.00 Using index
1 SIMPLE e null ref dept_id_fk,job_id_fk job_id_fk 23 test.j.job_id 5 100.00 Using where
1 SIMPLE d null eq_ref PRIMARY PRIMARY 4 test.e.department_id 1 100.00 Using index
id相同,表的执行顺序如上,j,e,d。并不是按 from中e,d,j的顺序执行。
id不同:
如果是子查询,id的序号会递增,id值越大,优先级越高,越先被执行。
EXPLAIN SELECT * FROM employees WHERE department_id =( SELECT department_id FROM departments WHERE department_id = 10 );
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY employees null ref dept_id_fk dept_id_fk 5 const 1 100.00 Using where
2 SUBQUERY departments null const PRIMARY PRIMARY 4 const 1 100.00 Using index
id相同不同同时存在。
同样id大的先执行,id相同的顺序执行。
explain 可以查看表的执行顺序
select_type
用来区别查询的类型
常见值:
SIMPLE : 简单查询,查询中不包含子查询或者UNION
PRIMARY:查询中若包含子查询,最外层的查询为PRIMARY
SUBQUERY:查询中包含了子查询,内层查询为SUBQUERY
DERIVIED:在FROM后跟的子查询定义被标记为DERIVED,mysql会把这个子查询查询到的结果放在临时表里。DERIVIED2就代表此表是序号为2的查询的衍生子查询。
UNION:若第二个select出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED。
UNION:多个表UNION后的结果。
explain select * from employees e left join departments d on d.department_id=e.department_id
union select * from employees e right join departments d on d.department_id=e.department_id;
UNION示例
table
表名
type
ALL:全表扫描
index:和All一样都是读全表,但是index是从索引当中读,all是从硬盘中读。
range:只检索给定范围的行,使用一个索引来选择行。一般是在where使用了between and, in ,>等等的查询。
ref:
非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,然而他可能会找到多个符合条件的行。
eq_ref:
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
const,system:
system表只有一行记录,等于系统表,是const类型的特例,可以忽略不记。
const用于表示通过索引一次就找到了,用于比较primary key或者unique索引,因为只匹配一行数据,所以很快,如将主键置于where列表中,Mysql就能将该查询转换为一个常量。
NULL;
EXPLAIN SELECT
e.*
FROM
employees e,
departments d,
jobs j
WHERE
e.department_id = d.department_id
AND j.job_id = e.job_id;
示例
对于表d的查询来说 department_id是其主键,每个主键在表中只有一条记录与之匹配。
而对于表e来说,department_id不是主键,因此可能会查询出多个行有相同的department_id值。
对于表j来说,不知道为什么不是eq_ref而是index,可能跟下图mysql的选择有关。
EXPLAIN SELECT * FROM employees
WHERE department_id =( SELECT department_id FROM departments WHERE department_id = 10 );
示例
对于表d来说, department_id=10,已经是一个固定值,因此只需要查一次,返回一个数据。而eq_ref则是查多次,因为department_id的值不固定,每次都返回一个数据。
system>const>eq_res>ref>range>index>ALL;
possible_key
可能会涉及到的索引,但是不一定会被实际使用。
key
查询用到的索引,为null则有可能是索引失效,或者是本就没有索引。
key_len
表示查询中索引的字节数。长度越短越好。
key_len显示的值并不是索引得到实际使用长度,是根据表定义得到的,而不是表内的实际值检索得到的。
ref
哪一列使用了这个索引。
示例
由于表j先被加载,因此没有其他列使用j的索引,所以其j.job_id只能全盘扫面出值,其次是e表,
j.job_id = e.job_id; e表的job_id索引被j表使用。同理d表的主键索引也被e表使用。
rows
根据表统计信息和索引选用情况,大致推算出找到所需的记录需要读取的行数。越小越好。
Extra
额外信息
或者Using filesort,distinct等
加索引注意的点
左右连接索引加在从表中。
where中出现了>或
join语句的优化 //TODO
尽可能减少Join语句的NestedLoop循环次数,用小的结果集驱动大的结果集。
优先优化NestedLoop的内层循环
保证Join语句中被驱动表上Join条件字段已经被索引。
最佳左前缀法则
如果索引为复合索引,要遵循最左前缀法则。也就是查询从索引的最左列开始并且不跳过索引中的列。
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
//索引加到了name,age,pos三列上,查询时如果不查name只查其余的两列或者一列,索引就会失效。
//而只要查询到了name,无论是只查name一列还是带上其他两列,索引都会被使用。
//但是并不一定是使用了全部的索引,也就是如果只查了name和Pos,Pos的索引也不会使用。
//要使用全部的索引,就必须查到所有的字段。带头大哥和中间兄弟都不能断。
不在索引列上做任何操作(操作,函数,类型转换),否则失效
EXPLAIN select * from staffs where name='July';
EXPLAIN select * from staffs where left(name,4)='July';//索引失效
存储引擎不能使用索引中范围条件右边的列,范围右边全失效
尽量只访问索引的查询(索引列和查询列一致),减少select*
使用!=会导致索引失效,8.0版本不会
is null,is not null无法使用索引
LIKE
//如果 索引中只包含name一列
EXPLAIN select * from staffs where name Like 'July%';//百分号在右边不会失效
EXPLAIN select * from staffs where name Like '%July%';//失效
EXPLAIN select * from staffs where name Like '%July';//失效
//如果索引是复合索引且name是复合索引的第一个字段
//那么只要select的列的范围小于符合索引列的范围或者select的列为主键,%在前面也不会影响索引。
字符串不加单引号索引会失效(可能会发生类型转换)
少用or,用or连接会导致索引失效
索引与Order By
Order by子句要尽量使用 Index方式进行排序,避免使用FileSort方式排序。
尽可能在索引列上完成排序操作,遵照索引最佳左前缀规则。
FileSort排序有两种算法:
双路排序:MySQL 4.1之前使用双路排序,扫描两次磁盘。
单路排序:扫描一次磁盘,总体而言好过双路。但是也有小问题。
提高Order By的速度:
使用Order By的时候尽量只查询需要的字段,不要用select*。
尝试提高sort_buffer_size和max_length_for_sort_data