索引的创建与Explain的使用

索引是帮助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循环次数，用小的结果集驱动大的结果集。
https://www.cnblogs.com/xqzt/p/4469673.html
优先优化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

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容