Mysql的索引详解-拓冰建站

1.索引的分类

1.按照功能来分，可以分为主键索引、唯一索引、普通索引、全文索引
2.按照索引字段个数来分，可以分为单列索引、联合索引
3.按照物理实现方式来分，可以聚簇索引、非聚簇索引

2.适合添加索引的场景

1.具有唯一性约束的字段。
2.频繁地作为where查询条件的字段，包括select/update/delete。
3.需要gruop by 或 order by的字段
4.distinct字段需要添加索引(实测在数据量小的情况下意义不大)
5.多表连接join时需要注意的事项：连接表的张数最好不要超过3张，对where条件的字段增加索引，对于两表之间的关联字段，被关联表的该字段需要有索引且字段在两表中的属性保持一致。
6.区分度高的字段适合作为索引，反之则不适合。举例：性别字段就两种类型，这种区分度就很低，不适合作为索引。我们常用的xxId，一般来说这种id都是唯一的，每个人的id都不一样，这种区分度就是100%，非常适合作为索引。

3.慢sql分析

可以开启慢sql日志，然后分析慢sql日志中的慢sql。
在my.cnf中开启慢sql日志：
long_query_time=1; //设置慢查询的标准是1S，超过1S就是慢查询。
slow_query_log=ON; //设置开启慢查询日志记录

4.profile分析

对于慢查询sql，可以使用profile工具进行分析，确定SQL主要耗时是在什么步骤。
show profiles; //查询最近的一些sql
在这里插入图片描述
show profile for query 704; //展示我们需要查询的sql的详细耗时情况

在这里插入图片描述
可以看到上述sql耗时主要在executing步骤，也就是执行阶段。这种时候就需要explain一下sql，查看具体的执行计划进行分析。

5.explain分析

在这里插入图片描述
可以看到上述sql走了name索引，Using index表示是走了覆盖索引，但是还是耗时很长，最主要的原因就是扫描的行太多了，也就是rows字段，多达9307475行，就是数据量太大导致的耗时长。

5.1 explain重要关键字分析

explain的关键字还挺多的，每个关键字的各种情况分析也有点复杂，我们只需要记住最重要的几个关键字即可。下图是一个explain的所有关键字。
在这里插入图片描述

5.1.1 type

执行计划的一条记录代表的是MySQL对于某个表的执行查询时的访问方法，又称访问类型，其中type列就表明这个访问方法是啥。比如上面看到的type值是ref，表明mysql是使用ref访问方法来执行上述sql。
访问方法的所有值如下：system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL
从前往后，查询速度越来越慢，ALL就代表全表扫描，是最慢的情况。

5.1.1.1 system

当使用精确统计的表，且表内只有一条记录时，进行的查询会是system。

5.1.1.2 const

当使用主键索引或者唯一索引对某个常量进行等值匹配时，进行的查询会是const，这种情况只会匹配一行记录。
在这里插入图片描述

5.1.1.3 eq_ref

多表连接时，当被关联表的通过主键或唯一索引与驱动表进行关联时，进行的查询会是eq_ref
在这里插入图片描述

5.1.1.4 ref

当通过普通二级索引对某个常量进行等值匹配时，进行的查询会是ref。
在这里插入图片描述

5.1.1.5 ref_or_null

当对普通索引进行等值查询，且该值可能为NULL时，进行的查询会是ref_or_null。
在这里插入图片描述

5.1.1.6 index_merge

单表访问方法在某些场景下可以使用‘Intersection’、‘Union’、‘Sort-Union’这三种索引合并的方式来进行查询。
在这里插入图片描述

5.1.1.7 unique_subquery

unique_subquery是针对在一些包含in查询的查询语句中，如果查询优化器决定将in子查询转化为exists子查询，而起子查询可以用到主键进行等值匹配的话，那么该子查询的执行计划的type列就是unique_subquery。

5.1.1.8 range

如果获取的是索引某些范围内的记录，那么查询type可能是range。
在这里插入图片描述

5.1.1.9 index

需要查询的字段在索引中，但是where条件导致索引没有生效，这种情况下会对索引进行一个全量遍历，获取需要的数据然后返回，常见于查询的字段是联合索引的某个字段，但是where的查询条件也是联合索引的某个字段，但是查询条件不符合最左前缀原则导致联合索引失效，这种情况下的查询type就是index。
在这里插入图片描述

5.1.1.10 ALL

针对完全不走索引，进行全表扫描的情况，查询type会是ALL。
在这里插入图片描述

5.1.2 possible_keys和key

possible_keys是可能用到的key，而key是实际用到的key。
在这里插入图片描述

上面的查询语句，explain的执行结果显示可能会用到idx_stuid和idx_department，但是实际上只用到了idx_stuid。where条件里面出现的列只要可能会走索引，一般都会出现在possible keys中，但是实际最终使用哪个索引，需要经过查询优化器进行计算后，选择成本最低的索引。所以不一定是写在前面的索引就会优先被使用，可以看下面这个例子：
在这里插入图片描述
只是把student_id的查询条件从=换成了>，这时候查询优化器就会认为范围查询很耗时，计算之后会选择走后面等值查询的索引。

5.1.3 key_len

key_len表示使用的索引长度，有时候我们也可以通过这个值计算使用了那个或哪些索引，最常见的就是联合索引的情况，比如联合索引(a,b,c)，可能用只上了a，也可能用上了a和b，或者用上了a和b和c，这三种情况下key_len的长度都是不一样的。
在这里插入图片描述

可以看到上面的key_len是83，为什么是83呢？

name字段是varchar(20)的数据类型，计算key_len的时候是如下计算规则：
varchar长度 * ( character set：utf8mb4=4,utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(变长字段)。
我们设定的varchar长度是20，我们的字符集是utf8mb4，同时我们允许name字段为NULL，而varchar又是变长字段。
所以数学计算式是： 20 * 4 + 1 + 2 = 83.
如果想详细了解key_len的计算规则，可以看我这篇文章：关于explain执行mysql语句，索引key_len计算问题

下面我们再添加一个索引条件来看看：
在这里插入图片描述
上面的key_len是166，因为我对于department字段的设置也和name一样，都是varchar(20)且允许为NULL，此时name和department的索引都用上了，所以长度是83+83=166.

此时我们把department的查询条件由等值查询改为范围查询：
在这里插入图片描述
发现key_len还是为166，说明此时还是用到了department的索引。如果此时我们再添加remarks的查询条件呢？

此时索引长度未变化，说明remarks的索引未使用上，这就是大名鼎鼎的最左前缀原则了，联合索引的后一个索引要生效，必须满足前一个索引是等值查询，比如 department = ‘xxx’ 或者 department in (‘aa’,‘bb’)，如果前一个索引是范围查询，比如 department > ‘xxx’ ，那么department本身的索引依旧可以生效，但是其后的索引就无法使用了。

5.1.4 ref

当使用索引列进行等值查询时，与索引列进行等值匹配的对象信息。
在这里插入图片描述
上面的索引列是student_id，此时与student_id进行等值比较的对象是什么？是1233，这是一个常量，所以ref列的值是const。

此时可以看到和scores表的student_id索引进行匹配的是student表的student_id列。

5.1.5 rows

预估需要读取的记录行数，通常来说这个值越小越好，一般为1就是等值查询且仅匹配到一条记录，这种是最好的情况。
在这里插入图片描述
等值匹配这时候只有一个叫’zs123’的记录，所以rows是1，这是非常好的情况。

范围查询，这时候预估要扫描的行数就是9307475，而我这张表的总记录数也才一千万，说明基本上就是全表扫描了，前面的type = ALL也证明如此，这是我们要避免的情况。

5.1.6 filtered

表示某个表经过搜索条件过滤后剩余的记录条数的占之前rows的百分比，这个值是从100%到0%，这个值越大越好。比如100%，表明Mysql预估查询了100条记录，然后通过where条件进行匹配，发现都满足条件，这时候filtered就是100%。如果预估查询了100条记录，但是经过where条件后只剩50条，此时filtered就是50%。
在这里插入图片描述
再举个例子：

使用name的范围查询可以查出5条记录。

加上remarks = 'remark1000’后就仅能匹配一条记录。

所以此处的filtered就是10%。 ps:不应该是 1 / 5 = 20%吗？有没有大神说一下为什么o.O

对于单表查询来说，filtered字段对我们来说没太大意义，我们需要关注的多表连接查询时，驱动表中filtered的值，它决定了被驱动表的要执行的次数，即rows * filtered。
在这里插入图片描述

9307475 * 33.33 % = 3071466，可以看到执行数次很多，执行时间也是20.647S，这种肯定是要避免的。

5.1.7 Extra

5.1.7.1 当查询语句中没有from时

在这里插入图片描述

5.1.7.2 当查询语句的where条件永远是false时

在这里插入图片描述

5.1.7.3 当有where条件，且进行的是全表扫描时: Using where

在这里插入图片描述
remarks无索引

5.1.7.4 当where使用了索引且后续条件未使用索引时:Using where

在这里插入图片描述
student_id有索引，但是score无索引

5.1.7.5 当查询的字段就是where条件的索引时（覆盖索引）:Using index

在这里插入图片描述
这种情况下，在二级索引idx_student_id进行查询，找到student_id = 123456的索引值，因为索引本身就包含了我们需要查询的字段，所以就可以直接返回数据，而不需要回表操作，这就是覆盖索引。上面的例子可能有点让人困惑，感觉多次一举，实际上覆盖索引常用的场景是联合索引，如下面的例子：
在这里插入图片描述

上面的联合索引是(name,department,remarks)。我们的查询条件是name = ‘zs1234’，我们需要查询的字段是department，remarks，因为索引中就保存了department和remarks的数据，所以不需要回表查询对应的记录，直接返回即可。

5.1.7.6 当产生索引下推时: Using index condition

在这里插入图片描述
对于联合索引(name,department,remarks),当查询条件是 where name > ‘zs9999’ and department in (‘aaa’,‘bbbb’)时，按照最左前缀原则，由于name已经是范围查询了，department的索引应该是使用不上了，key_len=83也表明只使用了联合索引的name字段。此时假设name > 'zs99999’的索引记录有100条，那么在mysql5.6之前是会拿这100条记录的主键去回表查询这100条记录，返回给server层，然后在server层过滤出department等于’部门1234506’和’部门1234509’的记录。但是在MySQL5.6之后新增了索引下推的功能，再找到name > 'zs99999’的100条记录后，在引擎层会直接匹配department等于’部门1234506’和’部门1234509’的2条索引记录，然后用这2条索引记录对应的主键去回表，找到2条记录返回给server层。主要就是把where的过滤工作从server层下沉到engine层，减少回表次数。