关于mysql:MySQL索引优化万字详解

前言

索引优化这四个字说实话我认为其实挺难了解的。看到这四个字我脑门上是：？？？？

索引还要优化吗？调优SQL一般来说不就是看它有没有走索引，没走索引给它加上索引就好了吗？

嗯，所以你是怎么给它加索引的？

看SQL应该怎么走索引撒！

那SQL是怎么走索引的呢？又是怎么判断这条SQL会不会走索引呢？

我：…, 咱明天就来剖析剖析！

要是你还不理解MySQL底层的数据结构，倡议你先看看MySQL数据结构

最左前缀法令

咱们个别要优化的都是简单SQL,而简单SQL个别走的都是联结索引，说到联结索引的匹配规定，就逃不开这个：最左前缀法令

什么是最左前缀法令？

最左前缀法令即为：索引的匹配从最右边的字段开始，匹配胜利能力往右持续匹配下一个字段。

不了解？没关系，咱们先来看看这个联结索引：name_age_position

联结索引是以三个字段name,age,position组成，并且创立该索引时字段程序为name、age、positon。

那么该索引就会以这样的形式排序（索引就是排好序的高效的数据结构）

name字段从小到大排序
name字段的值雷同时，age字段从小到大排序
age字段的值雷同时，postion字段从小到大排序

如上图所示，从zhangsan18到zhangsan100是程序的，而name都为zhangsan18的三个结点中，age又是从小到大排序，age雷同时position也是从小到大排序。

请你肯定要把这个数据结构牢记于心，忘了就看看

当初通过这个联结索引再来解析一下最左前缀法令：在索引匹配时，必须先可能匹配name字段(最右边的)，能力持续匹配age字段(下一个), age字段匹配胜利了能力匹配position字段。

为什么？

因为联结索引中的最右边字段是有序的，而第二个字段是在第一个字段雷同的状况下有序，第三个字段是在第二个字段雷同的状况下有序。

如果你想要用age字段间接在联结索引中查找数据，对不起，找不到，因为age字段中联结索引中是无序的。

你把第一行name字段遮掉看看age字段的状况：18,18,20,15,25,16,33。无序的对吧。

还是有点蛊惑？没关系，咱们再来通过案例剖析剖析。

什么是走索引？就是看索引会不会起到作用，可能起到作用就叫走了索引，没有起到作用就叫没走索引。

案例剖析

表构造：

CREATE TABLE `employees` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时\r\n间',  PRIMARY KEY (`id`),  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

sql1

explain select * from employees where name > 'zhangsan18'

name字段是联结索引最右边的字段，所以会走索引

sql2

explain select * from employees where age = 18

age字段并非联结索引最右边的字段，在索引中无序，故不走索引，全表扫描

sql3

explain select * from employees where name = 'zhangsan18' and age = 20;

name字段和age字段都会走索引，因为在name字段雷同时，age字段是有序的, 所以此时age也能够走索引。

以上图为例，当定位到zhangsan18时，能够间接定位到age=20这条数据,不须要从age=18的中央遍历寻找，所以索引对age字段也起到作用了。

你当初明确什么是最左前缀法令了吧，还不明确就私信我吧[叹气.jpg]。

SQL案例

当初，咱们再来通过一些sql持续深挖这最左前缀法令。

sql4

explain select * from employees where age = 20 and name = 'zhangsan18';

和sql3雷同，name和age都会走索引，最左前缀和你sql语句的地位无关，mysql在执行时会主动调整地位，也就是改成name = 'zhangsan18' and age = 20

sql5

explain select * from employees where name > 'zhangsan18' and age = 20;

只有name字段会走索引，age不会走索引，因为此时mysql的查问逻辑是定位到name=zhangsan18最左边的一条数据，而后通过叶子结点的指针向右扫描遍历，索引对age字段未起到作用。如图

explain后果：

sql6

explain select * from employees where name >= 'zhangsan18' and age = 20;

和sql5差不多，惟一的区别就是name是大于等于。此时name和age都会走索引。

当初，我预计你肯定晕了，网上不是说范畴查找会导致索引生效吗？怎么还走了age字段。

这样，我把sql这样写：

explain select * from employees where (name = 'zhangsan18' and age = 20) or (name > 'zhangsan18' and age = 20);

name = 'zhangsan18' and age = 20局部：name和age都会走索引，这个没问题吧？

name > 'zhangsan18' and age = 20局部：name走索引，age不走索引，这个也没问题吧？

合起来就是name和age都会走索引，因为name = 'zhangsan18' and age = 20时age要走索引。

还是蛊惑？那梳理下流程。

mysql执行时先定位到name=zhangsan18, 而后因为前面还有个age=20条件，所以会间接定位到这里

而后再往右扫描name>zhangsan18的记录, 你通知我这个过程有没有用上age字段的索引？用上了吧，所以age字段也会走索引，也仅仅是这个时候会走索引，前面name>zhangsan18的还是不走索引。

sql7

explain select * from employees where name like 'zhangsan18%' and age = 10

name和age都会走索引，和sql6一样了解就好。

sql8

explain select * from employees where name between 'zhangsan18' and 'zhangsan50' and age = 10

name和age都会走索引

到这里，你对最左前缀法令应该会有个粗浅的意识了，更多的想法，就由你本人去摸索啦

索引下推

MySQL在5.6之后加了一个优化：索引下推，能够在索引遍历过程中，对索引中蕴含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，能够无效的缩小回表次数

拿这条sql举例：

explain select * from employees where name > 'zhangsan18' and age = 20;

这条sqlname字段走索引，age不走索引，在没有索引下推时，查问逻辑是这样的：

1、存储引擎通过联结索引找到name > 'zhangsan18'的记录

2、而后应用联结索引存储的主键进行回表操作，查问出所有数据

3、将数据返回给Server层

4、Server层判断这条记录的age是否为20, 是则返回给客户端，否则抛弃

这里就有个优化点，在第一步用联结索引找到name > 'zhangsan18'的记录时，能不能直接判断age是否为20？如果是再进行前面的步骤。

哎，你感觉能不能？

能！age字段原本就在联结索引外面，直接判断就完事了~

所以，这就是索引下推。

OrderBy

order by是怎么应用索引的？

order by同样遵循最左前缀法令，只有当order by的字段是最左字段或者追随where条件的字段时，能力应用索引排序

排序排序，要害就在于：有序

如以下联结索引：name_age_position

name字段是人造有序的，name值雷同时，age是有序的，age雷同时，position是有序的。

那应该怎么判断sql应用了索引排序呢？

如以下sql

explain select id from employees order by name;

Extra列：

Using index：应用笼罩索引

Using filesort：将用文件排序而不是索引排序，数据较小时从内存排序，否则须要在磁盘实现排序。

只有没有Extra列呈现use filesort，那么就是用的索引排序

再看看应用文件排序的sql

explain select id from employees order by age;

留神，应用了索引是应用了索引，文件排序是文件排序，这是两码事。
比方你应用了索引进行查找数据，然而查找出的数据是用的文件排序。

接下来看看一些案例

sql1

explain select * from employees where name = 'zhangsan18' order by age,position;

索引排序，age跟在name字段后,position跟在age字段后

sql2

explain select * from employees where name = 'zhangsan18' order by position,age;

文件排序，因为该sql是先应用position字段排序，再应用age字段排序，而position字段在name雷同时仍旧是无序的。

sql3

explain select * from employees where name = 'zhangsan18' and age = 18 order by position,age;

索引排序，position跟在age后，是有序的，而orderby后的age其实会被优化成常量，因为是通过age=10查问出的数据

sql4

explain select * from employees where name = 'zhangsan18' order by age asc,position desc;

文件排序，尽管age字段能够用索引排序，然而position字段逆序排序。

可能会不太好了解，这里联合图阐明一下

索引是先通过age字段排序，而后对age字段雷同的记录，进行position逆序排序，最终查问出的后果是这样的

所以position字段需应用文件排序。

sql5

select * from employees where name = 'zhangsan18' order by age desc,position desc;

索引排序，因为age，position字段都是逆序的，相当于是索引上从右往左遍历

sql6

explain select * from employees where name > 'zhangsan18' order by age,position;

文件排序，因为name走范畴查问，age字段走不了索引了。同上篇索引优化一中sql5的剖析

sql7

explain select * from employees where name >= 'zhangsan18' order by age,position;

仍旧是文件排序，如果你看了上文，你可能又会有纳闷了：age字段不是会走索引吗？咋是文件排序勒？

这里再强调一遍：走索引是走索引，排序是排序。

没错，在name=zhangsan18时，age,position是有序的，能够应用索引排序。

然而在name>zhangsan18时，age,position是无序的，须要应用文件排序。

15,25,16,33：无序的对吧
好了，对于排序的案例就到这里，更多的案例就还是由你本人去摸索吧

什么是文件排序？

文件排序分为单路排序和双路排序

单路排序

一次性取出满足条件行的所有字段，而后在sort buffer中进行排序

双路排序

首先依据相应的条件取出相应的排序字段和能够间接定位行数据的行id(主键)，而后在sort buffer中进行排序，排序完后须要再次取回其它须要的字段。

MySQL是抉择用哪种排序的？

MySQL通过比拟零碎变量max_length_for_sort_data(默认1024字节)的大小和须要查问的字段总大小来判断应用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么应用单路排序模式
如果字段的总长度大于max_length_for_sort_data ，那么应用双路排序模式。

小结

1、如果能够应用索引排序，尽量应用索引排序，然而切实没有方法进行索引排序也不要勉强，优先对where筛选语句做索引优化，因为筛选出的数据往往是很少的，排序老本很低。

2、如果没有方法应用文件排序，服务器内存又短缺的状况下，那么能够适当调整下max_length_for_sort_data,让MySQL应用单路排序，这样能够缩小回表，效率会好一些。

分页查问

在平时，咱们写的分页查问sql个别是这样

explain select * from employees order by name limit 10000,10;

这样的sql你会发现越翻到前面查问会越慢，这是因为这里看似是从表中查问10条记录，实际上是在表中查问了10010条记录，而后将10000条记录抛弃所失去的后果。

优化sql如下：

explain select * from employees t1 join (select id from employees order by `name` limit 10000, 10) t2 on t1.id = t2.id;

执行打算：

优化思路：先应用笼罩索引形式查出10条数据，再应用这10条数据连贯查问。

笼罩索引：查问的字段被索引齐全笼罩，比方id在联结索引中

原理：联合MySQL数据结构, 主键索引(innodb引擎)会存储残缺的记录，而二级索引只存储主键。MySQL一个结点默认为16KB。

故：二级索引一个叶子结点可能寄存的记录会多的多，扫描二级索引比扫描主键索引的IO次数会少很多。

图示：

优化前sql查问工夫

set global query_cache_size=0;
set global query_cache_type=0;

优化后：

Join查问

jion查问分为内连贯，左连贯，右连贯；

关联时又有两种状况：应用索引字段关联，不应用索引字段关联。

我以案例举例说明，如以下两张表t1,t2, a字段有索引，b字段无索引

CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

t2表构造与t1完全相同

其中t1表具备1w条数据，t2表具备100条数据。

应用索引字段关联查问

explain select * from t1 inner join t2 on t1.a = t2.a;

执行打算：

剖析执行打算：

1、先全表扫描t2表(100条数据)

2、应用t2表的a字段关联查问t1表，应用索引idx_a

3、取出t1表中满足条件的行，和t2表的数据合并，返回后果给客户端

成本计算：

1、扫描t2表：100次

2、扫描t1表：100次，因为应用索引能够定位出的数据，这个过程的工夫复杂度大略是O(1)

此处说的100次只是为了更好的计算和了解,理论可能就几次

翻译成代码可能是这样：

for x in range(100): # 循环100次  print(x in t1) # 一次定位

所以总计扫描次数：100+100=200次

这里引出两个概念

小表驱动大表, 小表为驱动表，大表为被驱动表

inner join时,优化器个别会优先选择小表做驱动表, 排在后面的表并不一定就是驱动表。
left join时，左表是驱动表，右表是被驱动表
right join时，右表时驱动表，左表是被驱动表

嵌套循环连贯 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，依据关联字段在另一张表（被驱动表）里取出满足条件的行，而后取出两张表的后果合集。

应用索引字段关联查问的个别为NLJ算法

应用非索引字段查问

explain select * from t1 inner join t2 on t1.b = t2.b;

执行打算：

Extra列：Using join buffer：应用join buffer（BNL算法）

剖析执行打算：

1、先全表扫描t2表(100条数据)，将数据加载到join buffer(内存)中

2、全表扫描t1表，逐个和join buffer中的数据比对

3、返回满足条件的行

成本计算：

1、扫描t2表：100次

2、扫描t1表：1w次

3、在内存中比对次数：100*10000=100w次

翻译成代码可能是这样：

for i in range(100): # 循环100次  for j in range(10000) # 循环10000次

所以总计扫描次数为：100+10000=10100次，内存中数据比对次数为：100*1w=100w次

这个过程称为：基于块的嵌套循环连贯Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到join buffer中，而后扫描被驱动表，把被驱动表每一行取出来跟join buffer中的数据做比照。

应用BNL算法`join buffer`不够时怎么办？

案例中t2表只有一百行数据，如果数据量很大时，比方t2表一共有1000行数据，join buffer一次只能放800行时怎么办？

此时会应用分段放的策略：先放入800行到join buffer，而后扫描t1表，比对结束之后，将join buffer清空，放入残余的200行，再次扫描t1表，再比对一次。

也就是说：此时会多扫描一次t1表，如果2次都放不下，就再多扫描一次，以此类推。

小结

join查问中个别有两种算法：

嵌套循环连贯(NLJ)算法：应用索引字段关联查问
基于块的嵌套循环连贯(BNL)算法：应用非索引字段关联查问

NLJ算法比BNL算法性能更高

关联查问的优化形式：

对关联字段加索引：让MySQL尽量抉择NLJ算法
小表驱动大表：一般来说MySQL优化器会本人判断哪个是小表，如果应用left join和right join是要留神。
如果不得已要应用BNL算法，那么在内存短缺的状况下，能够调大一些join buffer，防止屡次扫描被驱动表。

为什么非索引字段不应用NLJ算法？

NLJ算法性能这么好，为什么非索引字段关联时不应用这种算法呢？

这是因为NLJ算法采纳的是磁盘扫描形式：先扫驱动表，取出一行数据，通过该数据的关联字段到被驱动表中查找，这个过程是应用索引查找的，十分快。

如果非索引字段采纳这种形式，那么通过驱动表的数据的关联字段，到被驱动表中查找时，因为无奈应用索引，此时走的是全表扫描。

比方驱动表有100条数据，那么就要全表扫描被驱动表100次，被驱动表有1w条数据，那么就是磁盘IO:100*1w=100w次，这个过程是十分慢的。

In&Exist

in和exist的优化只有一个准则：小表驱动大表

in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)

即in中的表为小表

exist: 当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)

即外层的表为小表

count查问

对于count这里就不具体阐明了，因为各种用法效率都差不多。

字段有索引：count(*)≈count(1)>count(字段)≈count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)

索引设计准则

对于索引局部到这里就差不多了，总结一下索引设计准则

先写代码，再依据状况建索引
一般来说，都是都没代码写完之后，能力明确哪些字段会用到索引，但我也发现大部人写完代码就不论了。所以如果在设计时能够初步晓得哪些字段能够建设索引，那么能够在设计表时就建好索引，写完代码再做调整
尽量让联结索引笼罩大部分业务
一个表不要建设太多的索引，因为MySQL保护索引也是须要消耗性能的，所以尽量让一到三个联结索引就笼罩业务外面的sql查问条件
不要在小基数的字段上建索引
如果在小基数的字段上建设索引是没有意义的，如性别，一张1千万数据的表，对半分的话500w男，500w女，筛选不出什么。
字符串长度过长的索引能够取局部前缀建设索引
字段过长的话也会导致索引占用的磁盘空间比拟大，如varcahr(255), 这个时候能够取局部前缀建设索引，如前20个字符。但要留神的是，这样会导致排序生效，因为只取了前20个字符串，索引只能保障大范畴的有序。
也能够在前期依据肯定的计算规定计算最佳索引长度：distinct(left(字段，长度))/count约等于1
前期能够依据慢sql日志持续优化索引
随便业务的迭代，查问条件也会产生扭转，此时能够依据慢sql继续优化索引
能够建惟一索引，尽量建惟一索引
where条件和order by抵触时时，优先取where的条件建索引
因为筛选出数据后，个别数据量比拟少，排序的老本不大，所以优先让数据更快的筛选进去。

如果我的文章对你有所帮忙，还请帮忙点赞、珍藏、转发一下，你的反对就是我更新的能源，非常感谢！

追更，想要理解更多精彩内容，欢送关注公众号：程序员阿紫

集体博客网站：https://zijiancode.cn

前言

最左前缀法令

什么是最左前缀法令？

案例剖析

SQL案例

索引下推

OrderBy

order by是怎么应用索引的？

什么是文件排序？

单路排序

双路排序

MySQL是抉择用哪种排序的？

小结

分页查问

Join查问

应用索引字段关联查问

应用非索引字段查问

应用BNL算法join buffer不够时怎么办？

小结

为什么非索引字段不应用NLJ算法？

In&Exist

count查问

索引设计准则

应用BNL算法`join buffer`不够时怎么办？