索引面试题分析

分手后的思念是犯贱 2022-08-28 13:52 159阅读 0赞

# 一 口诀 #

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

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

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

KE 百分写最右，覆盖索引不写\*；

不等空值还有 OR，索引影响要注意；

VAR 引号不可丢，SQL 优化有诀窍。

# 二 脚本 #

create table test03(
      id int primary key not null auto_increment,
      c1 char(10),
      c2 char(10),
      c3 char(10),
      c4 char(10),
      c5 char(10)
    );
    
    insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
    insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
    insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
    insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
    insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
    
    create index idx_test03_c1234 on test03(c1,c2,c3,c4);

# 三 分析 #

我们创建了 idx\_test03\_c1234 ，分析各种 SQL 的索引使用情况。

# 最左前缀要遵守
    mysql> explain select * from test03 where c1='a1';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 最左前缀要遵守
    mysql> explain select * from test03 where c1='a1' and c2='a2';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 最左前缀要遵守
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 全值匹配我最爱
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 搜索引擎的优化器对 c1，c2，c4，c3 的顺序进行了优化，优化后，还是符合全值匹配我最爱，但最好还是不要这样用，因为会用到优化器，也会消耗性能
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref                     | rows | filtered | Extra |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    
    # 范围之后全失效，c1 和 c2 用于索引，c3 用于排序，c4 没用到
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 范围之后全失效，c1 c2 和 c3 用于索引，c4 用于排序
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4>'a4';
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    
    # where 之后的语句先优化成 where c1='a1' and c2='a2' and c3='a3' and c4>'a4'; 然后按照上面这句进行查找
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 |   100.00 | Using index condition |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 和 c2 用于查找，c3 用于排序
    mysql> mysql> explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 和上面这句功能一样，说明上句的 c4='a4' 是干扰项
    mysql> explain select * from test03 where c1='a1' and c2='a2' order by c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 和 c2 用于查找，c4 无法用于排序，使用到了 Using filesort（九死一生），中间兄弟不能断
    mysql> explain select * from test03 where c1='a1' and c2='a2' order by c4;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition; Using filesort |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+---------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 用于查找，c5 是干扰项，c2 和 c3 用于排序
    mysql> explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                              |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using where |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 用于查找，c5 是干扰项，c2 和 c3 无法用于排序，使用到了 Using filesort（九死一生），因为 c3 和 c2 的顺序反了
    mysql> explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                              |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+----------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1，c2 用于查找，c2，c3 用于排序
    mysql> explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |   100.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+-----------------------+
    1 row in set, 1 warning (0.01 sec)
    
    # 这里 c5='a5' 是干扰项，和上面这句一样
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition; Using where |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    1 row in set, 1 warning (0.01 sec)
    
    # c1 和 c2 用于查找，c5 是干扰项，因为 c2='a2'，排序字段已经是一个常量了，所以 c3,c2 即使顺序反了，也不会影响 c3 排序，不会产生 filesort
    mysql> explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra                              |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 |    20.00 | Using index condition; Using where |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------------+------+----------+------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 用于查找，c2 和 c3 用于排序，不会产生 filesort
    mysql> explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 用于查找，c2 和 c3 不能用于排序，产生了 temporary 和 filesort
    mysql> explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                                                  |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition; Using temporary; Using filesort |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+--------------------------------------------------------+
    1 row in set, 1 warning (0.01 sec)
    
    # c1,c2，c3 索引都用到————like 后常量开头
    mysql> explain select * from test03 where c1='a1' and c2 like 'kk%' and c3='a3';
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 索引用到
    mysql> explain select * from test03 where c1='a1' and c2 like '%kk' and c3='a3';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1 索引用到
    mysql> explain select * from test03 where c1='a1' and c2 like '%kk%' and c3='a3';
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    
    # c1,c2，c3 索引都用到————like 后常量开头
    mysql> explain select * from test03 where c1='a1' and c2 like 'k%kk%' and c3='a3';
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    | id | select_type | table  | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    |  1 | SIMPLE      | test03 | NULL       | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 |    20.00 | Using index condition |
    +----+-------------+--------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)

# 四 说明 #

group by 表面叫分组，但分组前必排序。所以 group by 的排序法则和 sort by 基本一致。但 group by 还会有临时表的产生。

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/1692c2bc10dd4c83a101b429bd553f19.png

发表评论取消回复

表情：

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

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

相关阅读

相关 MySQL面试题-索引篇

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

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

相关 MySQL索引面试题分析

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

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

相关 MySQL索引面试题汇总

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

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

相关数据库索引高频面试题：java堆栈分析工具有哪些

前言我在知乎上看到这样一个话题，关于程序员的： > 听同学说干码农这行每天都要加班，这样的话，不就没精力做别的事情了。有个师兄做 IT 销售，不太辛苦，生活还算是美滋

朴灿烈づ我的快乐病毒、/ 2022年10月15日 13:50/ 0 赞/ 98 阅读

相关数据分析面试题

文章目录 1 数理统计 1.1.T检验和ANOVA都是用来看样本之间均值是否相等，但是两者又有什么区别呢？ 1.2 说一个简单的场景，问用

╰半橙微兮°/ 2022年09月05日 15:44/ 0 赞/ 320 阅读

相关索引面试题分析

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

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

相关 MySQL面试：索引面试题

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

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

相关【面试题】海量数据面试题分析

海量面试题的条件一般都要求我们处理一批计算机内存放不下的数据。当我们遇到这种面试题时，常有以下几种思想： 1. 哈希切分哈希切分就是将一个大文件，利用哈希的原理，将其

灰太狼/ 2022年07月15日 08:35/ 0 赞/ 230 阅读

相关 Spring 面试题分析

1. 开发中主要使用 Spring 的什么技术 ? ? ①. IOC 容器管理各层的组件 ②. 使用 AOP 配置声明式事务 ③. 整合其他框架.

旧城等待，/ 2022年05月17日 13:27/ 0 赞/ 168 阅读

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

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

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