关于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 持续深挖这 最左前缀法令。

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 字段原本就在联结索引外面，直接判断就完事了~

所以，这就是索引下推。

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 通过比拟零碎变量 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;

优化后：

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

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

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

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(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

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

前言

最左前缀法令

什么是最左前缀法令？

案例剖析

SQL 案例

索引下推

OrderBy

order by 是怎么应用索引的？

什么是文件排序？

单路排序

双路排序

MySQL 是抉择用哪种排序的？

小结

分页查问

Join 查问

应用索引字段关联查问

应用非索引字段查问

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

小结

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

In&Exist

count 查问

索引设计准则

前言

最左前缀法令

什么是最左前缀法令？

案例剖析

SQL 案例

索引下推

OrderBy

order by 是怎么应用索引的？

什么是文件排序？

单路排序

双路排序

MySQL 是抉择用哪种排序的？

小结

分页查问

Join 查问

应用索引字段关联查问

应用非索引字段查问

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

小结

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

In&Exist

count 查问

索引设计准则

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