星云-全栈知识体系

索引优化分析

2023/8/27 数据库MySQL

# 1、索引简介

# 1.1、索引是什么？

MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果。

从而可以获得索引的本质：索引是排好序的快速查找数据结构。

  • 例子： 索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a---z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？

重点：索引会影响到MySQL查找（WHERE的查询条件）和排序（ORDER BY）两大功能！

  • 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

  • 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

[root@Ringo ~]# df -h					# Linux下查看磁盘空间命令 df -h 
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   16G   23G  41% /
devtmpfs        911M     0  911M   0% /dev
tmpfs           920M     0  920M   0% /dev/shm
tmpfs           920M  480K  920M   1% /run
tmpfs           920M     0  920M   0% /sys/fs/cgroup
overlay          40G   16G   23G  41%
1
2
3
4
5
6
7
8
  • 我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

# 1.2、索引的优劣势

优势

  • 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
  • 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
  • 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

# 1.3、MySQL索引的分类

索引分类

  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但是允许空值。
  • 复合索引：一个索引包含多个字段。

建议

  • 一张表建的索引最好不要超过5个！
  • 尽量使用复合索引，而少使用单列索引，多个单列索引时，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

# 1.4、索引相关语法

-- 1、创建索引 [UNIQUE]可以省略
-- 如果只写一个字段就是单值索引，写多个字段就是复合索引
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tabName ADD [UNIQUE] INDEX indexName ON (columnName(length));

-- 2、删除索引
DROP INDEX [indexName] ON tabName;

-- 3、查看索引
-- 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看
SHOW INDEX FROM tabName \G;

-- 4、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

-- 5、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次)
ALTER TABLE tabName ADD UNIQUE INDEX indexName(column_list);

-- 6、该语句创建普通索引，索引值可以出现多次
ALTER TABLE tabName ADD INDEX indexName(column_list);

-- 7、该语句指定了索引为FULLTEXT，用于全文检索
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 1.5、MySQL索引数据结构

索引数据结构

  • B+Tree 索引
  • Hash 索引
  • Full-text 全文索引
  • R-Tree 索引

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+Tree 索引检索原理

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

Full-text全文索引

MySQL具备全文搜索的能力，它可以让你在不使用模板匹配操作的情况下进行单词或短语的查找。全文搜索有3种类型：自然语言搜索(默认类型)、布尔模式捜索、查扩展搜索。

R-Tree索引

R树是用来做空间数据存储的树状数据结构。例如给地理位置，矩形和多边形这类多维数据建立索引。R树是由Antonin Guttman于1984年提出的。 ...... 可以用它来回答“查找距离我2千米以内的博物馆”，“检索距离我2千米以内的所有路段”（然后显示在导航系统中）或者“查找（直线距离）最近的加油站”这类问题。R树还可以用来加速使用包括大圆距离在内的各种距离度量方式的最邻近搜索。

# 1.6、哪些情况需要建立索引？

  • 主键自动建立主键索引（唯一 + 非空）
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度，建的复合索引尽量与order by一致
  • 查询中统计或者分组字段（group by也和索引有关）

# 1.7、哪些情况不要建立索引？

  • 记录太少的表
  • 经常增删改的表
  • 频繁更新的字段不适合创建索引，更新的时候不仅要更新数据，还要更新索引
  • Where条件里用不到的字段不创建索引
  • 假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高

# 2、性能分析

# 2.1、MySQL查询优化器

Query Optimizer

MySQL中专门负责优化SELECT语句的优化器模块。

主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

  • 例子：当客户端像MySQL请求一条Query，命令解析器模块完成请求分类，去别处是SELECT并转发给MySQL Query Optimizer，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

# 2.2、MySQL常见瓶颈

  • CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读区数据的时候
  • IO：磁盘IO瓶颈发生再装入数据远大于内存容量的时候

服务器硬件的性能瓶颈： top free iostat和vmstat来查看系统的性能状态

# 2.3、Explain执行计划分析

EXPLAIN

SQL的执行计划，使用的EXPLAIN的关键字可以模拟优化器执行的SQL的查询语句，从而知道的MySQL的是如何处理的SQL的语句的

语法

# \G 可省略，不加\G默认表格形式显示,加了\G列表形式显示
explain + SQL语句 [\G]
1
2

查询执行计划：

EXPLAIN字段解析（重点）

id

表的读取和加载顺序，值有以下三种情况

  • id相同，执行顺序由上至下
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

table

该次查询涉及的表

select_type

数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

  • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION
  • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY：在SELECT或者WHERE子句中包含了子查询
  • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
  • UNION RESULT：从UNION表获取结果的SELECT

type

访问类型排列，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。

  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计
  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where中，MySQL就能将该查询转化为一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。除了 system 和const 类型之外, 这是最好的联接类型
  • ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录
  • range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引
  • index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。 也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的
  • ALL：Full Table Scan，没有用到索引，全表扫描

possible_keys

显示可能应用在这张表中的索引，一个或者多个

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没建或没有使用索引，即索引失效

查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。与Extra有关

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

key_len计算规则：

--  索引列为字符串类型的情况
1) 列长度：
2) 列是否为空： NULL(+1)，NOT NULL(+0)
3) 字符集： 如 utf8mb4=4,utf8=3,gbk=2,latin1=1
4) 列类型为字符： 如 varchar(+2), char(+0)
计算公式：key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)

--  数值数据的key_len计算公式：
TINYINT允许NULL = 1 + 1(NULL)
SMALLINT允许为NULL = 2 + 1(NULL)
INT允许为NULL = 4 + 1(NULL)

--  日期时间型的key_len计算：（针对mysql5.5及之前版本）
DATETIME允许为NULL = 8 + 1(NULL)
TIMESTAMP允许为NULL = 4 + 1(NULL)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

哪些列或常量被用于查找索引列上的值

  • NULL：没有用到
  • const：用到常量
  • 库名.表名.字段：用到字段

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

  • Using filesort（需优化）：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为"文件内排序"。
create table user (
    id     integer primary key auto_increment,
    name   varchar(20) not null,
    age    integer     not null,
    gender tinyint     not null
);
create index user_name_gender on user(name, gender);

# 排序没有使用索引
explain select * from user where name ='zhangsan1' order by id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

#~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
# 排序使用到了索引
explain select * from user where name ='zhangsan1' order by gender \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ref
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
  • Using temporary（需优化）：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
explain select count(*) from user where gender = 18 group by age;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20
        Extra: Using where; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)


explain select count(*) from user where gender =18 group by name;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index 
possible_keys: user_name_gender
          key: user_name_gender
      key_len: 63
          ref: NULL
         rows: 5
     filtered: 20
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  • Using index（好）：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作
# 覆盖索引
# 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，即查询列要被所使用的索引覆盖
# 注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。
explain select name, gender from user;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pms_category
   partitions: NULL
         type: index
possible_keys: NULL       
          key: user_name_gender
      key_len: 63
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index   # select的数据列只用从索引中就能够取得，不必从数据表中读取   
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
  • Using where（好）：表明使用了WHERE过滤
  • Using join buffer（好）：使用了连接缓存
  • impossible where（需优化）：WHERE子句的值总是false，不能用来获取任何元组
mysql> explain select name from user where name = 'zs' and name = 'ls'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Impossible WHERE   # 不可能字段同时查到两个名字
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，即查询列要被所使用的索引覆盖，注意：如果要使用覆盖索引，一定不能写SELECT *，要写出具体的字段。

# 3、索引优化分析

索引分析是结论论证过程，也可以不看解析过程，直接看结论。

# 3.1、单表索引分析

案例：查询category_id为1且comments大于1的情况下，views最多的article_id

1. 数据准备

DROP TABLE IF EXISTS `article`;

CREATE TABLE IF NOT EXISTS `article` (
    `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `author_id`   INT(10) UNSIGNED NOT NULL COMMENT '作者id',
    `category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
    `views`       INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
    `comments`    INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
    `title`       VARCHAR(255)     NOT NULL COMMENT '标题',
    `content`     VARCHAR(255)     NOT NULL COMMENT '正文内容'
) COMMENT '文章';

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
VALUES (1, 1, 1, 1, '1', '1'),
       (2, 2, 2, 2, '2', '2'),
       (3, 3, 3, 3, '3', '3'),
       (1, 1, 3, 3, '3', '3'),
       (1, 1, 4, 4, '4', '4');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2. 编写SQL语句并查看SQL执行计划

# 1、sql语句
SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

# 2、sql执行计划
mysql> EXPLAIN SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using filesort  # 产生了文件内排序，需要优化SQL
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3. 创建索引idx_article_ccv

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
1

4. 查看当前索引

5. 查看现在SQL语句的执行计划

创建复合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是在order by排序的时候没有用到索引，MySQL居然还是用的Using filesort，为什么？

6.我们试试修改SQL，看看SQL的执行计划

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
1

执行计划

推论：当comments > 1的时候order by排序views字段索引就用不上，但是当comments = 1的时候order by排序views字段索引就可以用上！！！所以，范围之后的索引会失效

7. 知道了范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views会失效，那么如果删除这个索引，创建idx_article_cv索引呢？

/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);
1
2

查看当前的索引

8. 当前索引是idx_article_cv，来看一下SQL执行计划

由此可见，范围之后的索引会失效

# 3.2、两表索引分析

两表连接查询的SQL执行计划

  1. 数据准备
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '商品类别';

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';
1
2
3
4
5
6
7
8
9
10
11
12
  1. 不创建索引的情况下，SQL的执行计划。

book和class两张表都是没有使用索引，全表扫描，那么如果进行优化，索引是创建在book表还是创建在class表呢？下面进行大胆的尝试

  1. 左表(book表)创建索引

创建索引idx_book_card

CREATE INDEX idx_book_card ON book(card);
1

在book表中有idx_book_card索引的情况下，查看SQL执行计划

  1. 删除book表的索引，右表(class表)创建索引 创建索引idx_class_card
CREATE INDEX idx_class_card ON class(card);
1

在class表中有idx_class_card索引的情况下，查看SQL执行计划

由此可见，左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适

# 3.3、三表索引分析

  1. 数据准备
DROP TABLE IF EXISTS `phone`;

CREATE TABLE IF NOT EXISTS `phone`(
`phone_id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类' 
) COMMENT '手机';
1
2
3
4
5
6

  1. 不加任何索引，查看SQL执行计划。

  2. 根据两表查询优化的经验，左连接需要在右表上添加索引，所以尝试在book表和phone表上添加索引

CREATE INDEX idx_book_card ON book(card);			/* 在book表创建索引 */
CREATE INDEX idx_phone_card ON phone(card);		/* 在phone表上创建索引 */
1
2

再次执行SQL的执行计划 后两个都是ref且总row优化很好，效果不错。

因此索引最好设置再需要经常查询的字段中

# 3.4、结论

  • 单表索引结论：范围之后的索引会失效
  • 两索引结论：左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适
  • 三表索引结论：索引最好设置再需要经常查询的字段中

JOIN语句的优化

  • 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集
  • 优先优化NestedLoop的内层循环
  • 保证JOIN语句中被驱动表上JOIN条件字段已经被索引
  • 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置

# 4、索引失效

数据准备

CREATE TABLE `staffs`(
  `id` INT(10) PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
  `pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
  `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';

INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('Ringo', 18, 'manager');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');

/* 创建索引 */
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 4.1、索引失效的情况

  1. 全值匹配我最爱
  2. 最佳左前缀法则
  3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  4. 索引中范围条件右边的字段会全部失效
  5. 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *
  6. MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描
  7. is null、is not null也无法使用索引，所以字段一般都要给默认值
  8. like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）
  9. 字符串不加单引号索引失效
  10. 少用or，用它来连接时会索引失效

# 4.2、最佳左前缀法则

  • 最佳左前缀法则：如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段

口诀：带头大哥不能死，中间兄弟不能断

# 4.3、索引列上不计算

  • 在索引列上进行计算，会使索引失效

口诀：索引列上不计算

# 4.4、范围之后全失效

  • 范围之后的索引字段会失效

口诀：范围之后全失效

# 4.5、覆盖索引尽量用

  • 在写SQL的不要使用SELECT *，用什么字段就查询什么字段

口诀：查询一定不用*

# 4.6、不等有时会失效

/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';
type：index

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';
type：ALL
1
2
3
4
5
6
7

口诀：不等有时会失效

# 4.7、like百分加右边

-- 索引失效 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
type：ALL

-- 索引失效 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
type：ALL

-- 使用索引范围查询
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';
type：range
1
2
3
4
5
6
7
8
9
10
11

口诀：like百分加右边

如果一定要使用%like，而且还要保证索引不失效，那么使用覆盖索引来编写SQL

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
type：index

/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';
type：ALL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

口诀：覆盖索引保两边

# 4.8、字符要加单引号

-- 使用到了覆盖索引
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';

-- 使用到了覆盖索引
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;

-- 索引失效 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;
1
2
3
4
5
6
7
8

这里name = 2000在MySQL中会发生强制类型转换，将数字转成字符串

口诀：字符要加单引号

# 4.9、索引相关题目

假设index(a,b,c)

Where语句 索引是否被使用
where a = 3 Y，使用到a
where a = 3 and b = 5 Y，使用到a，b
where a = 3 and b = 5 and c = 4 Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 N，没有用到a字段
where a = 3 and c = 5 使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5 使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like 'kk%' and c = 4 Y，a，b，c都用到
where a = 3 and b like '%kk' and c = 4 只用到a
where a = 3 and b like '%kk%' and c = 4 只用到a
where a = 3 and b like 'k%kk%' and c = 4 Y，a，b，c都用到

# 4.10、面试题分析

数据准备

/* 创建表 */
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','b22','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`);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

题目：最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次

/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  
   	 c3字段用于排序了但是没有统计到key_len中，c4字段失效*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, 
		 c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，
		 c4字段排序产生了 Using filesort 说明排序没有用到c4字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，
      再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort 和(10)进行对比学习！*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;


/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort Using temporary 
			5.7之后这种不是分组函数和GROUP BY出现的字段不能select*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生

# 4.11、总结

索引优化的一般性建议

  • 对于单值索引，尽量选择针对当前query过滤性更好的索引
  • 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

口诀

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

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

查询一定不用 *，覆盖索引尽量用，

不等有时会失效，like百分加右边，

字符要加单引号，一般SQL少用or。

查询截取分析