MySQL高级知识（七）——索引面试题分析

Bertha 。 2023-06-09 12:23 28阅读 0赞

前言：该篇随笔通过一些案例，对索引相关的面试题进行分析。

--------------------

### 0.准备 ###

\#1.创建test表（测试表）。

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

drop table if exists test;
    create table test(
    id int primary key auto_increment,
    c1 varchar(10),
    c2 varchar(10),
    c3 varchar(10),
    c4 varchar(10),
    c5 varchar(10)
    ) ENGINE=INNODB default CHARSET=utf8;
    
    insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
    insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
    insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
    insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
    insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

\#2.创建索引。

![format_png][]

### 1.根据以下Case分析索引的使用情况 ###

Case 1：

![format_png 1][]

分析：

①创建复合索引的顺序为c1,c2,c3,c4。

②上述四组explain执行的结果都一样：type=ref，key\_len=132，ref=const,const,const,const。

结论：在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。

Case 2：

![format_png 2][]

![format_png 3][]

分析：

当出现范围的时候，type=range，key\_len=99，比不用范围key\_len=66增加了，说明使用上了索引，但对比Case1中执行结果，说明c4上索引失效。

结论：范围右边索引列失效，但是范围当前位置（c3）的索引是有效的，从key\_len=99可证明。

Case 2.1：

![format_png 4][]

分析：

与上面explain执行结果对比，key\_len=132说明索引用到了4个，因为对此sql语句mysql底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了），注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上。

结论：范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。

Case 2.2：

![format_png 5][]

分析：

如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。

解决方式使用覆盖索引。

结论：在最佳左前缀法则中，如果最左前列（带头大哥）的索引失效，则后面的索引都失效。

Case 3：

![format_png 6][]

分析：

利用最佳左前缀法则：中间兄弟不能断，因此用到了c1和c2索引（查找），从key\_len=66，ref=const,const，c3索引列用在排序过程中。

Case 3.1：

![format_png 7][]

分析：

从explain的执行结果来看：key\_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。

Case 3.2：

![format_png 8][]

分析：

从explain的执行结果来看：key\_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。

Case 4：

![format_png 9][]

分析：

查找只用到索引c1，c2和c3用于排序，无Using filesort。

Case 4.1：

![format_png 10][]

分析：

和Case 4中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。

Case 4.2：

![format_png 11][]

![format_png 12][]

分析：

在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引。

Case 4.3：

![format_png 13][]

分析：

与Case 4.1对比，在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

Case 5：

![format_png 14][]

分析：

只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以key\_len=33，ref=const，表示只用到一个索引。

Case 5.1：

![format_png 15][]

分析：

对比Case 5，在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。

### 总结： ###

通过以上Case的分析，进行如下总结：

①最佳左前缀法则。

\#1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。

\#2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。

②group by容易产生Using temporary。

③通俗理解口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用。

--------------------

by Shawn Chen，2018.6.25日，下午。

[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230531/3a3ee89485634f61a84ae8b27e003574.png
[format_png]: /images/20230531/2f93ea33439e4d08a2bacd82b1e6b462.png
[format_png 1]: /images/20230531/450605d737334b059760806c1e605cf5.png
[format_png 2]: /images/20230531/484fd506b5ca4faeb90069553614f745.png
[format_png 3]: /images/20230531/740f174ba103444ebc388439e855ce91.png
[format_png 4]: /images/20230531/0d0e546f6608483f842306ab8b95b815.png
[format_png 5]: /images/20230531/1c16130e1d2c43bba866334c0cb116fc.png
[format_png 6]: /images/20230531/a717a092fd3f4516a08e357d338eabe9.png
[format_png 7]: /images/20230531/4667ed9927a1449e8bc87b652bce4235.png
[format_png 8]: /images/20230531/93643dfa1dfe4e0fba836879ebcf2acb.png
[format_png 9]: /images/20230531/9e7f4ed2906c4e428e22510ce521a936.png
[format_png 10]: /images/20230531/838abbabde524ff4be3d256d966ee6c4.png
[format_png 11]: /images/20230531/531a6975ca0b48109ee04272809366e0.png
[format_png 12]: /images/20230531/0971408f3f9a4950a99de1fd0a8018e2.png
[format_png 13]: /images/20230531/0c177fa8c2814192becb52dc58ff58ce.png
[format_png 14]: /images/20230531/3a2239d9e887443cb3e1b29c1759585d.png
[format_png 15]: /images/20230531/3b35d58083524993854eba69dbe8ee08.png

发表评论取消回复

表情：

评论列表（有 0 条评论，28人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL面试题-索引篇

目录 1.什么是索引 2.索引常见的数据结构？ 3.MySQL有哪些索引？ 4.什么是B+树？为什么B+树成为主要的SQL数据库的索引实现？ 5.聚簇索引和非聚簇索引

爱被打了一巴掌/ 2023年09月27日 15:17/ 0 赞/ 195 阅读

相关 MySQL索引面试题分析

MySQL索引面试题分析话不多说，先建立一个表testTable，其中id为自增主键 ![在这里插入图片描述][20200216213712809.png] 在

梦里梦外;/ 2023年07月06日 06:25/ 0 赞/ 161 阅读

相关 MySQL高级知识（七）——索引面试题分析

前言：该篇随笔通过一些案例，对索引相关的面试题进行分析。 -------------------- 0.准备 \1.创建test表（测试表）。 ![复制代码][aH

Bertha 。/ 2023年06月09日 12:23/ 0 赞/ 29 阅读

相关 MySQL索引面试题汇总

目录 1.前言 2.索引数据结构分类 3.二叉查找树 4.红黑树（自平衡二叉查找树） 5.B-Tree 6.B+Tree 6.1 B+Tree概述及特点

心已赠人/ 2022年11月06日 01:49/ 0 赞/ 358 阅读

相关索引面试题分析

一口诀全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效； KE 百分写最右，覆盖索引不写\；不等空

分手后的思念是犯贱/ 2022年08月28日 13:52/ 0 赞/ 234 阅读

相关 MySQL面试：索引面试题

谈谈你对索引的理解数据库索引，就是数据库管理系统(DBMS)中一个排序的数据结构，用以协助快速查询，更新数据库表中数据 ![在这里插入图片描述][watermark

悠悠/ 2022年08月28日 05:44/ 0 赞/ 311 阅读

相关 [MySQL高级](三) 索引分析

1. 单表 1.1 建表SQL > CREATE TABLE \`article\` ( > \`id\` int(10) NOT NULL, > \`au

悠悠/ 2022年05月04日 00:58/ 0 赞/ 342 阅读

相关 [MySQL高级](三) 索引分析

1. 单表 1.1 建表SQL > CREATE TABLE \`article\` ( > \`id\` int(10) NOT NULL, >

灰太狼/ 2022年01月29日 03:33/ 0 赞/ 496 阅读

相关 MySQL高级---索引优化分析(三、索引简介)

1、索引的概念 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为

不念不忘少年蓝@/ 2021年09月23日 19:26/ 0 赞/ 670 阅读

相关 MySQL--索引面试题分析

索引面试题分析建表及准备数据： CREATE TABLE test03( id INT PRIMARY KEY NOT NULL AUTO_INCRE

小咪咪/ 2021年08月13日 15:31/ 0 赞/ 423 阅读