MySQL索引优化(万字详解)

2023-03-2810:19:06数据库教程Comments770 views字数 9529阅读模式
作者：程序员阿紫
来源：知乎文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

索引优化这四个字说实话我认为其实挺难理解的。看到这四个字我脑门上是：？？？？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

索引还要优化吗？调优SQL一般来说不就是看它有没有走索引，没走索引给它加上索引就好了吗？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

嗯，所以你是怎么给它加索引的？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

看SQL应该怎么走索引撒！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

那SQL是怎么走索引的呢？又是怎么判断这条SQL会不会走索引呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

我：…, 咱今天就来分析分析！文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

要是你还不了解MySQL底层的数据结构，建议你先看看MySQL数据结构文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

最左前缀法则

我们一般要优化的都是复杂SQL,而复杂SQL一般走的都是联合索引，说到联合索引的匹配规则，就逃不开这个：最左前缀法则文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

什么是最左前缀法则？

最左前缀法则即为：索引的匹配从最左边的字段开始，匹配成功才能往右继续匹配下一个字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

不理解？没关系，我们先来看看这个联合索引：name_age_position文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120150557493

联合索引是以三个字段name,age,position组成，并且创建该索引时字段顺序为name、age、positon。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

那么该索引就会以这样的方式排序（索引就是排好序的高效的数据结构）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

如上图所示，从zhangsan18到zhangsan100是顺序的，而name都为zhangsan18的三个结点中，age又是从小到大排序，age相同时position也是从小到大排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

请你一定要把这个数据结构牢记于心，忘了就看看文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

现在通过这个联合索引再来解析一下最左前缀法则：在索引匹配时，必须先能够匹配name字段(最左边的)，才能继续匹配age字段(下一个), age字段匹配成功了才能匹配position字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

为什么？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

因为联合索引中的最左边字段是有序的，而第二个字段是在第一个字段相同的情况下有序，第三个字段是在第二个字段相同的情况下有序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

如果你想要用age字段直接在联合索引中查找数据，对不起，找不到，因为age字段中联合索引中是无序的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

你把第一行name字段遮掉看看age字段的情况：18,18,20,15,25,16,33。无序的对吧。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

还是有点迷惑？没关系，我们再来通过案例分析分析。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

什么是走索引？就是看索引会不会起到作用，能够起到作用就叫走了索引，没有起到作用就叫没走索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

案例分析

表结构：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

explain select * from employees where name > 'zhangsan18'

name字段是联合索引最左边的字段，所以会走索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120145946516

sql2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

explain select * from employees where age = 18

age字段并非联合索引最左边的字段，在索引中无序，故不走索引，全表扫描文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120150113853

sql3文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

name字段和age字段都会走索引，因为在name字段相同时，age字段是有序的, 所以此时age也可以走索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120150855590

以上图为例，当定位到zhangsan18时，可以直接定位到age=20这条数据,不需要从age=18的地方遍历寻找，所以索引对age字段也起到作用了。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120151229081

你现在明白什么是最左前缀法则了吧，还不明白就私信我吧[叹气.jpg]。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

SQL案例

现在，我们再来通过一些sql继续深挖这最左前缀法则。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

sql4文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

和sql3相同，name和age都会走索引，最左前缀和你sql语句的位置无关，mysql在执行时会自动调整位置，也就是改成name = 'zhangsan18' and age = 20文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

sql5文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

只有name字段会走索引，age不会走索引，因为此时mysql的查询逻辑是定位到name=zhangsan18最右边的一条数据，然后通过叶子结点的指针向右扫描遍历，索引对age字段未起到作用。如图文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120171406509

explain结果：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120171842732

sql6文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

和sql5差不多，唯一的区别就是name是大于等于。此时name和age都会走索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120172053144

现在，我估计你一定晕了，网上不是说范围查找会导致索引失效吗？怎么还走了age字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这样，我把sql这样写：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

name = 'zhangsan18' and age = 20部分：name和age都会走索引，这个没问题吧？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

name > 'zhangsan18' and age = 20部分：name走索引，age不走索引，这个也没问题吧？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

合起来就是name和age都会走索引，因为name = 'zhangsan18' and age = 20时age要走索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

还是迷惑？那梳理下流程。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

mysql执行时先定位到name=zhangsan18, 然后由于后面还有个age=20条件，所以会直接定位到这里文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120172715433

然后再往右扫描name>zhangsan18的记录, 你告诉我这个过程有没有用上age字段的索引？用上了吧，所以age字段也会走索引，也仅仅是这个时候会走索引，后面name>zhangsan18的还是不走索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

sql7文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

name和age都会走索引，和sql6一样理解就好。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120172053144

sql8文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

name和age都会走索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120172053144

到这里，你对最左前缀法则应该会有个深刻的认识了，更多的想法，就由你自己去探索啦文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

索引下推

MySQL在5.6之后加了一个优化：索引下推，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

拿这条sql举例：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

这条sqlname字段走索引，age不走索引，在没有索引下推时，查询逻辑是这样的：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

1、存储引擎通过联合索引找到name > 'zhangsan18'的记录文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、然后使用联合索引存储的主键进行回表操作，查询出所有数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

3、将数据返回给Server层文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

4、Server层判断这条记录的age是否为20, 是则返回给客户端，否则丢弃文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这里就有个优化点，在第一步用联合索引找到name > 'zhangsan18'的记录时，能不能直接判断age是否为20？如果是再进行后面的步骤。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

哎，你觉得能不能？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

能！age字段本来就在联合索引里面，直接判断就完事了~文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

所以，这就是索引下推。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

OrderBy

order by是怎么使用索引的？

order by同样遵循最左前缀法则，只有当order by的字段是最左字段或者跟随where条件的字段时，才能使用索引排序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

排序排序，关键就在于：有序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

如以下联合索引：name_age_position文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230120150557493

name字段是天然有序的，name值相同时，age是有序的，age相同时，position是有序的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

那应该怎么判断sql使用了索引排序呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

如以下sql文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

explain select id from employees order by name;
MySQL索引优化(万字详解)
image-20230120221351505

Extra列：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

Using index：使用覆盖索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

Using filesort：将用文件排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

只要没有Extra列出现use filesort，那么就是用的索引排序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

再看看使用文件排序的sql文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

explain select id from employees order by age;
MySQL索引优化(万字详解)
image-20230120221742350

注意，使用了索引是使用了索引，文件排序是文件排序，这是两码事。
比如你使用了索引进行查找数据，但是查找出的数据是用的文件排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

接下来看看一些案例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

sql1文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

索引排序，age跟在name字段后,position跟在age字段后文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121214814792

sql2文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

文件排序，因为该sql是先使用position字段排序，再使用age字段排序，而position字段在name相同时依旧是无序的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121215028356

sql3文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

索引排序，position跟在age后，是有序的，而orderby后的age其实会被优化成常量，因为是通过age=10查询出的数据文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121215301638

sql4文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

文件排序，虽然age字段可以用索引排序，但是position字段逆序排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121220740347

可能会不太好理解，这里结合图说明一下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121221138310

索引是先通过age字段排序，然后对age字段相同的记录，进行position逆序排序，最终查询出的结果是这样的文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121221406010

所以position字段需使用文件排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

sql5文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

索引排序，因为age，position字段都是逆序的，相当于是索引上从右往左遍历文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121221643560
MySQL索引优化(万字详解)
image-20230121221709624

sql6文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

文件排序，因为name走范围查询，age字段走不了索引了。同上篇索引优化一中sql5的分析文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121222001356

sql7文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

依旧是文件排序，如果你看了上文，你可能又会有疑惑了：age字段不是会走索引吗？咋是文件排序勒？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这里再强调一遍：走索引是走索引，排序是排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

没错，在name=zhangsan18时，age,position是有序的，可以使用索引排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

但是在name>zhangsan18时，age,position是无序的，需要使用文件排序。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230121222646265

15,25,16,33：无序的对吧
好了，关于排序的案例就到这里，更多的案例就还是由你自己去探索吧文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

什么是文件排序？

文件排序分为单路排序和双路排序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

单路排序

一次性取出满足条件行的所有字段，然后在sort buffer中进行排序文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

双路排序

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行id(主键)，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL是选择用哪种排序的？

MySQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

小结

1、如果可以使用索引排序，尽量使用索引排序，但是实在没有办法进行索引排序也不要勉强，优先对where筛选语句做索引优化，因为筛选出的数据往往是很少的，排序成本很低。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、如果没有办法使用文件排序，服务器内存又充足的情况下，那么可以适当调整下max_length_for_sort_data,让MySQL使用单路排序，这样可以减少回表，效率会好一些。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

分页查询

在平常，我们写的分页查询sql一般是这样文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

这样的sql你会发现越翻到后面查询会越慢，这是因为这里看似是从表中查询10条记录，实际上是在表中查询了10010条记录，然后将10000条记录丢弃所得到的结果。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

优化sql如下：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230123092745913

优化思路：先使用覆盖索引方式查出10条数据，再使用这10条数据连接查询。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

覆盖索引：查询的字段被索引完全覆盖，比如id在联合索引中文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

原理：结合MySQL数据结构, 主键索引(innodb引擎)会存储完整的记录，而二级索引只存储主键。MySQL一个结点默认为16KB。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

故：二级索引一个叶子结点能够存放的记录会多的多，扫描二级索引比扫描主键索引的IO次数会少很多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

图示：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
compare-index

优化前sql查询时间文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230123092607529

set global query_cache_size=0; set global query_cache_type=0;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

优化后：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230123093007842

Join查询

jion查询分为内连接，左连接，右连接；文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

关联时又有两种情况：使用索引字段关联，不使用索引字段关联。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

我以案例举例说明，如以下两张表t1,t2, a字段有索引，b字段无索引文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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完全相同文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

其中t1表具有1w条数据，t2表具有100条数据。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

使用索引字段关联查询

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

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230122111216120

分析执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

1、先全表扫描t2表(100条数据)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、使用t2表的a字段关联查询t1表，使用索引idx_a文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

3、取出t1表中满足条件的行，和t2表的数据合并，返回结果给客户端文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

成本计算：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

1、扫描t2表：100次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、扫描t1表：100次，因为使用索引可以定位出的数据，这个过程的时间复杂度大概是O(1)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

此处说的100次只是为了更好的计算和理解,实际可能就几次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

翻译成代码可能是这样：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

所以总计扫描次数：100+100=200次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这里引出两个概念文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

小表驱动大表, 小表为驱动表，大表为被驱动表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

嵌套循环连接 Nested-Loop Join(NLJ) 算法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

使用索引字段关联查询的一般为NLJ算法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

使用非索引字段查询

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

执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

MySQL索引优化(万字详解)
image-20230123094246851

Extra列：Using join buffer：使用join buffer（BNL算法）文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

分析执行计划：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

1、先全表扫描t2表(100条数据)，将数据加载到join buffer(内存)中文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、全表扫描t1表，逐一和join buffer中的数据比对文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

3、返回满足条件的行文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

成本计算：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

1、扫描t2表：100次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

2、扫描t1表：1w次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

3、在内存中比对次数：100*10000=100w次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

翻译成代码可能是这样：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

所以总计扫描次数为：100+10000=10100次，内存中数据比对次数为：100*1w=100w次文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这个过程称为：基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

把驱动表的数据读入到join buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟join buffer中的数据做对比。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

案例中t2表只有一百行数据，如果数据量很大时，比如t2表一共有1000行数据，join buffer一次只能放800行时怎么办？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

此时会使用分段放的策略：先放入800行到join buffer，然后扫描t1表，比对完毕之后，将join buffer清空，放入剩余的200行，再次扫描t1表，再比对一次。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

也就是说：此时会多扫描一次t1表，如果2次都放不下，就再多扫描一次，以此类推。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

小结

join查询中一般有两种算法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

  • 嵌套循环连接(NLJ)算法：使用索引字段关联查询
  • 基于块的嵌套循环连接(BNL)算法：使用非索引字段关联查询

NLJ算法比BNL算法性能更高文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

关联查询的优化方式：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

  • 对关联字段加索引：让MySQL尽量选择NLJ算法
  • 小表驱动大表：一般来说MySQL优化器会自己判断哪个是小表，如果使用left join和right join是要注意。
  • 如果不得已要使用BNL算法，那么在内存充足的情况下，可以调大一些join buffer，避免多次扫描被驱动表。

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

NLJ算法性能这么好，为什么非索引字段关联时不使用这种算法呢？文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

这是因为NLJ算法采用的是磁盘扫描方式：先扫驱动表，取出一行数据，通过该数据的关联字段到被驱动表中查找，这个过程是使用索引查找的，非常快。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

如果非索引字段采用这种方式，那么通过驱动表的数据的关联字段，到被驱动表中查找时，由于无法使用索引，此时走的是全表扫描。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

比如驱动表有100条数据，那么就要全表扫描被驱动表100次，被驱动表有1w条数据，那么就是磁盘IO:100*1w=100w次，这个过程是非常慢的。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

In&Exist

in和exist的优化只有一个原则：小表驱动大表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

in：当B表的数据集小于A表的数据集时，in优于exists文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

即in中的表为小表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

exist: 当A表的数据集小于B表的数据集时，exists优于in文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

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

即外层的表为小表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

count查询

关于count这里就不详细说明了，因为各种用法效率都差不多。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

字段有索引：count(*)≈count(1)>count(字段)≈count(主键 id)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

索引设计原则

关于索引部分到这里就差不多了，总结一下索引设计原则文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html

  1. 先写代码，再根据情况建索引
    一般来说，都是都没代码写完之后，才能明确哪些字段会用到索引，但我也发现大部人写完代码就不管了。所以如果在设计时可以初步知道哪些字段可以建立索引，那么可以在设计表时就建好索引，写完代码再做调整
  2. 尽量让联合索引覆盖大部分业务
    一个表不要建立太多的索引，因为MySQL维护索引也是需要耗费性能的，所以尽量让一到三个联合索引就覆盖业务里面的sql查询条件
  3. 不要在小基数的字段上建索引
    如果在小基数的字段上建立索引是没有意义的，如性别，一张1千万数据的表，对半分的话500w男，500w女，筛选不出什么。
  4. 字符串长度过长的索引可以取部分前缀建立索引
    字段过长的话也会导致索引占用的磁盘空间比较大，如varcahr(255), 这个时候可以取部分前缀建立索引，如前20个字符。但要注意的是，这样会导致排序失效，因为只取了前20个字符串，索引只能保证大范围的有序。
    也可以在后期根据一定的计算规则计算最佳索引长度：distinct(left(字段，长度))/count约等于1
  5. 后期可以根据慢sql日志继续优化索引
    随意业务的迭代，查询条件也会发生改变，此时可以根据慢sql持续优化索引
  6. 可以建唯一索引，尽量建唯一索引
  7. where条件和order by冲突时时，优先取where的条件建索引
    因为筛选出数据后，一般数据量比较少，排序的成本不大，所以优先让数据更快的筛选出来。
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/32188.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/32188.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定