mysql索引相关

MySQL索引原理

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

various-system-software-hardware-latencies

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

详解b+树

b+树
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

---

explain sql 解析

• explain 一个sql之后出现如下的字段：id，select_type，table，partitions，type，possible_keys，key，key_len，ref，rows，Extra
• id：SELECT查询的标识符， 每个SELECT语句都会自动分配一个唯一的标识符
• select_type：每个select查询字句的类型

类型名称	含义
SIMPLE	简单SELECT,不使用UNION或子查询等
PRIMARY	查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
UNION	UNION中的第二个或后面的SELECT语句
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询
DERIVED	派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY	一个子查询的结果不能被缓存，必须重新评估外链接的第一行

• table：显示这一行的数据是查哪张表的，不过有时短路显示的不是真实的表名。
• partitions：匹配的分区(这个目前用处不大)
• type：访问类型，表示MySQL在表中找到所需行的方式，对应的值和解释如下：

类型名	优级别	解释
system	1	表仅有一行
const	2	表最多有一个匹配行,在查询开始时即被读取
eq_ref	3	使用primary key或者unique key作为多表连接的条件,仅从该表中读取一行
ref	4	作为查询条件的索引在每个表匹配索引值的行从表中读取出来
fulltext	5	全文索引检索
ref_or_null	6	和ref一致，但增加了NULL值查询支持
index_merge	7	表示使用了索引合并优化方法
unique_subquery	8	使用了替换了in子查询
index_subquery	9	使用了替换了in子查询，但只适用于子查询中的非唯一索引
range	10	只检索给定范围的行,使用一个索引来选择行
index	11	全表扫描，但扫描表的方式是按索引的次序进行
ALL	12	全表扫描的方式找到匹配的行

type作为访问类型，其值代表着当前查询所用的类型，是体现性能的一个重要指标，从表中可以看到，从上到下，扫描表的方式越来越宽，性能也就越来越差，因此，对于一个查询，最好能保持在range级别以上。

• possible_keys：主动指出查询能用哪个索引在表中找到记录也就是会列出在查询中的字段中有索引的字段，但不一定被查询所用。
• key：显示再查询中实际使用的索引/键，如果没有索引，则显示NULL。但如果想强制查询中使用或忽视possible_keys列中的索引，则可以在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
• key_len：表示索引中使用的字节数。
• ref：表示哪些列或常量被用于查找索引列上的值。
• rows：显示当前查询估算到的查找到匹配记录所需的记录行数。
• extra：显示当前查询所用的解决方式，它有以下几种情况：

类型名称	含义
Using where	列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	MySQL中无法利用索引完成的排序操作称为“文件排序”
Using join buffer	改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where	这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

like查询是否走索引

like有四种情况，分别为没有%、 %% 、左%、右%

• 没有% 走索引

explain select * from exp_store where waybill_no like '11111111';

image.png

• %% 不走索引

explain select * from exp_store where waybill_no like '%11111111%';

image.png

• 左% 不走索引

explain select * from exp_store where waybill_no like '%11111111';

image.png

• 右% 有索引

explain select * from exp_store where waybill_no like '11111111%';

image.png

联合索引问题（mysql字段查询顺序是否影响索引）

组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到
设置 waybill_no, receiver_phone, take_delivery_code 为索引

explain select * from exp_store where waybill_no = ''; # 索引有效
explain select * from exp_store where receiver_phone = ''; # 索引无效
explain select * from exp_store where take_delivery_code = ''; # 索引无效
explain select * from exp_store where waybill_no = '' and receiver_phone = '' and take_delivery_code = '';# 索引有效
explain select * from exp_store where receiver_phone = '' and take_delivery_code = '';# 索引无效
explain select * from exp_store where waybill_no = '' and take_delivery_code = '';# 索引有效
explain select * from exp_store where waybill_no = ''  or take_delivery_code = '';# 索引无效，如果分别对waybill_no和take_delivery_code加索引则有效，走的是index_merge

• 关于or查询不走索引原因
  所谓的索引失效指的是：假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法，没啥问题。

此刻需要注意type类型为index_merge。
我查资料说mysql 5.0 版本之前 使用or只会用到一个索引（即使如上我给userid和mobile都建立的单列索引），但自从5.0版本开始引入了index_merge索引合并优化！也就是说，我们现在可以利用上多个索引去优化or查询了。

• index_merge作用：
  • 索引合并是把几个索引的范围扫描合并成一个索引。
  • 索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
  • 这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
• index_merge应用场景：
  • 对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果
  • 对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果
  • 对AND和OR组合语句求结果

多表关联索引使用

关联最好使用主键关联，并对主键为外键的添加索引

大表之间关联查询索引是否能使用

最好避免大表之间的关联查询

索引失效情景（针对单列的）

like的模糊查询以%开头，索引失效（like通配符可能导致索引失效）
类型不匹配（e.g. 如果字段类型是字符串，where时一定用引号括起来，否则索引失效）
mysql估计使用全表扫描要比使用索引快,则不使用索引
查询条件包含or，可能导致索引失效（or的话必须对所有的列加索引才可能会走索引）
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
在索引列上使用mysql的内置函数，索引失效
对索引列运算（如，+、-、*、/），索引失效
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效（切记，非不得已不使用非）
索引字段上使用is null， is not null，可能导致索引失效
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效

如何查看索引优化效果

使用explain分析

如何查看mysql索引的使用率

show status like '%handler_read%'

image.png

字段详解

字段	含义
Handler_read_first	代表读取索引头的次数，如果这个值很高，说明全索引扫描很多
Handler_read_key	代表一个索引被使用的次数，如果我们新增加一个索引，可以查看Handler_read_key是否有增加，如果有增加，说明sql用到索引
Handler_read_next	代表读取索引的下列，一般发生range scan
Handler_read_prev	代表读取索引的上列，一般发生在ORDER BY … DESC
Handler_read_rnd	代表在固定位置读取行，如果这个值很高，说明对大量结果集进行了排序、进行了全表扫描、关联查询没有用到合适的KEY
Handler_read_rnd_next	代表进行了很多表扫描，查询性能低下

其实比较多应用场景是当索引正在工作，Handler_read_key的值将很高，这个值代表了一个行将索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表 扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引

时间大于等于是否走索引，以及效果如何

新增流水创建时间的索引
ALTER TABLE `account_statement` 
ADD INDEX `idx_create_time`(`create_time`) USING BTREE COMMENT '创建时间';

创建索引前
创建索引后

索引会让表变大多少？索引对增删改的效率影响如何？

暂无

覆盖索引问题

暂无

慢索引优化实战

暂无

参考：
美团技术：MySQL索引原理及慢查询优化
SQL语句优化、mysql不走索引的原因、数据库索引的设计原则
mysql的in查询是可以用到索引吗
19条效率至少提高3倍的MySQL技巧
mysql组合索引与字段顺序
最官方的 mysql explain type 字段解读
你的like语句为啥没索引？
哪些情况下sql索引会失效
后端程序员必备：索引失效的十大杂症