MySQL索引优化分析 - 《MySQL》

SQL执行效率分析
常见的JOIN查询
索引简介
EXPLAIN性能分析
索引优化
参考文献

SQL执行效率分析

在SQL语句执行的过程中，常常会遇到SQL语句执行慢的问题，导致的原因通常可以分为以下三个部分：
1. 性能下降SQL慢
2. 执行时间长
3. 等待时间长
导致以上问题的原因大致包括：
1. 查询语句写的🌶🐔
2. 索引失效
3. 关联查询太多JOIN（设计缺陷或不得已的需求）
4. 服务器调优以及各个参数设置（缓冲、线程数等等）

常见的JOIN查询

SQL执行顺序

我们写的SQL关键字顺序

数据库的优化器执行时候的关键字顺序

总的来说，优化器需要先知道全部的数据源，然后再从中进行查询

Join图

图中一共有7种Join

案例

t_dept表

t_emp表

7种JOIN

INNER JOIN

取出emp和dept表中的部门id相同的数据并且两个表的id都不为null

LEFT JOIN,RIGHT JOIN与此类似

取出emp表中的所有数据，同时根据emp表的部门id去连接dept表。尽管dept表中不存在第10条记录对应的数据，补为null

LEFT JOIN并且JOIN的表的条件为null

取出emp表的全部数据，同时筛选出其中部门id是null的员工

FULL OUTER JOIN（MySQL不支持）

使用UNION实现， UNION自带去重功能

图中的语义就是emp和dept的公共部分加上emp的独有加上dept的独有部分

二者的独有部分联结

索引简介

什么是索引？
- MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以简单理解为排好序的快速查找数据结构，目的在于提高查询效率，可以类比从字典中查找数据。
  - 比如我们需要查找mysql这个字段，没有索引的时候，需要先从az中找到y…….
- 索引两大功能，快速查找和排序。影响到WHERE后面的条件是否使用了索引和ORDER BY排序后面的条件，也就是说索引会对查找和排序都有影响。
  
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引存储在.myi文件中，文件比较大，存储在磁盘上
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然除了B+树索引外，还有哈希索引（hash index）等。

索引的优势

排好序的快速查找数据结构

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
1. 索引的劣势
实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是占空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果数据库中有大量的表，就需要花时间研究建立最优秀的索引，或优化查询

MySQL索引分类
1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
4. 复合索引：即一个索引包含多个列
索引基本语法
1. 创建 ```sql CREATE [UNIQUE] INDEX 索引名 ON 表名(表中的列名);

ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名 (表中的列名);


      - 
中括号`[]`表示可选择，UNIQUE表示创建的是唯一索引
      - 
表中的列名，如果是多列的话，就是复合索引；单列的话就是唯一索引
> 创建索引
> ![](https://blog.chasingwind.top/HZi0Ot.png#alt=)
> 查看索引
> 图中第一个索引是 表创建的时候数据库默认创建的主键索引
> ![](https://blog.chasingwind.top/h8ymNt.png#alt=)
> 
---
> 使用ALTER创建索引  这里创建的是复合索引
> ![](https://blog.chasingwind.top/05b3xC.png#alt=)
> 查看索引
> ![](https://blog.chasingwind.top/EfIPVp.png#alt=)
   2. 
删除
```sql
DROP INDEX 索引名 ON 表名;

查看
```
SHOW INDEX FROM 表名;
```

MySQL索引结构
1. BTree索引
  - MySQL使用的就是BTree索引。
  - BTree

上图就是一颗BTree，浅蓝色的是磁盘块，深蓝色的是数据项，黄色的是指针

如磁盘块 1 包含数据项17和 35，包含指针 P1、P2、P3。P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。

非叶子结点不存储真实的数据，只存储指引搜索方向的数据，比如上图中磁盘块1中的17和35，并不真实存在于数据表中。

真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。

查找过程

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，

通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，

锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

也就是说我们磁盘的IO次数就是树的高度

3层BTree能存储的数据量

真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，

如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

Hash索引
full-text全文检索
R-Tree索引

哪些情况需要创建索引
1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单值/组合索引的选择问题，组合索引性价比更高
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段
哪些情况不要创建索引
1. 表记录太少
2. 经常增删改的表或者字段
3. Where 条件里用不到的字段不创建索引
4. 数据重复且分布平均的表字段不适合建索引，就是说，如果某个字段值重复率太高，就不适合建索引。比如，100万条数据中，某个字段只有T和F两种，这两个值分布都是在50%，那么这个字段就没必要创建索引，不会提高查询的效率。

EXPLAIN性能分析

在MySQL Query Optimizer（优化器）和MySQL数据库硬件以及配置都稳定的情况下，通过EXPLAIN来分析SQL的性能。

EXPLAIN是什么
- 使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN可以做什么
1. 表的读取顺序（id）
2. 数据读取操作的操作类型（select_type）
3. 哪些索引可以使用(possible_keys)
4. 哪些索引被实际使用(keys)
5. 表之间的引用（ref）
6. 每张表有多少行被优化器查询（rows）
EXPLAIN怎么使用
- EXPLAIN+SQL语句
解释各个字段的含义
执行EXPLAIN+SQL语句的结果

id：select查询序列号，是一组数字，表示查询中执行select子句或者操作表的顺序
- id相同：按照table列的顺序，由上到下的顺序执行

id不同：id越大，优先级越高，越先被执行

id既有相同，又有不同的，二者同时存在

DERIVED，衍生

上图中，首先执行的就是id为2的组（只有一个）中的t3，然后再在id为1的组中根据table列的顺序由上到下执行，先执行derived2，再执行t2。

derived2表示：derived2这个临时表是根据id为2的表t3衍生出来的。就是上图中的s1

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
table：显示这一行数据是关于哪张表的
type：访问类型排列

最好到最差的排列顺序：system>const>eq_ref>ref>range>index>ALL

一般来说：查询至少达到range级别，最好达到ref级别

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

const：表示通过索引一次就能找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

这里好比 t1是员工表，t2是部门表

查出来的是全公司唯一的总裁

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

这就好比，查找一个公司职位是程序员的人，查出来的是多个

range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index：Full Index Scan，index和ALL的区别就在于index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但是index是从索引中读取，而all是从硬盘中读取）

ALL：Full Table Scan，遍历全表找到匹配行

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
keys：实际使用的索引。如果为NULL，则没有使用索引，存在的情况有多种，比如索引没有建立，索引失效等等。还有一种就是如果存在多个索引，那么实际使用的是哪一个索引。

查询中若使用了覆盖索引，则该索引仅出现在key列中，possible_keys理论上使用的索引为null

覆盖索引：select后面查询的列和索引列相对应（个数和顺序都一样）

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

Key_len显示的是索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到的，不是通过表内检索出来的

第一个13是如何计算得到的：UTF-8，一个char占3个字节，同时，col1 DEFALUT是NULL，MySQL需要使用一个字节来标记NULL，也就是4 * 3 + 1 = 13。

同理，下面的26也是这样计算所得

ref：显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

执行表t1的时候，explain告诉我们使用到了idx_col1_col2这个索引，并且通过ref知道这个索引用于匹配哪些值

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

Extra：额外信息（除上述9个之外的信息）
1. Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
  
  这种情况是需要避免的，否则排序是重新进行的。

2.

Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

所以在我们进行排序和分组查询的时候，条件一定要使用索引顺序，这样的话，效率会高很多；否则就会使用临时表，这其中需要经历创建临时表，对临时表进行操作，删除临时表的过程，是很耗费时间的。

3.

Using INDEX：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行。直接通过查找索引而定位到数据，效率可以的！

如果同时出现Using where，表明索引被用来执行索引键值的查找；
如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

再说覆盖索引，

覆盖索引：select数据列只用从索引列中就可以获得，不必读取数据行。MySQL可以根据索引返回select的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。

如果要使用覆盖索引，一定要注意select列表中只取出需要的列（与索引列顺序，个数一致），不可以使用select *。否则的话使用的就不是索引覆盖，而仅仅是Using INDEX。

4.

Using where：表示SQL使用了where条件对数据进行了过滤。

5.

USing join buffer：使用了连接缓存，这个就是说join的表太多，可以将配置文件的缓冲区调大一点。

6.

impossible where：where子句的值总是false。

索引优化

索引分析

建表SQL参考

单表

建立文章article表

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

查询category_id为1且comments大于1的情况下，views最多的article_id。
- 对查询语句进行EXPLAIN分析

分析发现查询不仅遍历了全表查找数据，而且还使用了文件排序。

并且没有使用到索引，因为我们还没有创建索引。

尝试建立索引

由于上面的查询使用到了category_id，comments和views，尝试建立这三者的复合索引。

在建立索引之后再次进行查询的操作

发现虽然解决了全表扫描的问题并且使用了索引，但是还是出现了文件排序

出现的原因：按照BTree的工作原理，先排序category_id，如果遇到相同的category_id，再排序comments，如果遇到相同的comments再排序views。

因为where的第二个字段comments>1（range）是一个范围并且处于中间字段，MySQL无法对后面的views部分进行检索，也即range类型查询字段后面的索引无效。

重新建立索引

首先需要将之前的索引删除

尝试跳过comments建立索引

进行查询分析不仅使用了索引，并且访问类型也变成了ref，同时文件排序也不存在了。

两表

创建两张表，class和book表，两个表之间的联系就是card列对应相等

那么问题就出现了，两张表我们建立索引，建立在哪张表上面呢？class.card还是book.card？

未创建索引的时候

结果出现了全表扫描的情况，效率比较低

在左连接左表建立索引

再次执行EXPLAIN分析SQL语句，一个是遍历索引，一个是遍历全表的数据，并且rows还是全表

在左连接右表上建立索引

首先删除之前的索引

在book表上建立索引

再次执行相同的SQL，发现在book表的时候type为ref，非唯一性索引扫描；并且rows行也是1。

总结就是：左连接需要在右边的表建立索引；右连接需要在左边的表建立索引。

拿左连接举例来说：

LEFT JOIN 条件用于确认如何从右表搜索行，左边的表中的数据全都有。所以，右边的表是我们的关键点，需要建立索引。

三表

创建phone表，其中也有card字段。

未建立索引的时候

三张表都是全表扫描（ALL）

在左连接的右表建立索引

分别在两个左连接的右表创建索引再次执行EXPLAIN分析，发现之前的两个全表扫描已经变成了非唯一性索引扫描（ref），相比之前的ALL，效率提高很多。

JOIN语句的优化
1. 用小结果集驱动大的结果集，比如说LEFT JOIN，右表的表结果应该比左边的表结果小。
2. 优先优化内层嵌套的查询，比如JOIN一个表，先优化JOIN的表，而不是主表
3. 保证JOIN语句中被驱动表上JOIN条件字段已经被索引
4. 当无法保证被驱动表的JOIN字段被索引且内存资源充足的情况下，可以修改JoinBuffer的设置。

避免索引失效

创建staffs表并插入数据

建立索引

查看索引中字段的顺序

案例

使用索引中的NAME作为条件进行查找

使用索引中的NAME，age作为条件进行查找数据

使用NAME，age，pos作为条件进行查找

可见，要想结果更加精确，那么是需要付出更多的代价的（key_len）。

最佳左前缀法则

当我们去掉NAME，使用age和pos作为条件进行查询的时候

发现并没有用到索引而且是全表扫描

当我们仅仅使用pos作为条件的时候结果也是如此

当我们跳过中间的索引age，直接使用第一个索引NAME和第三个索引age的时候，我们会发现，key_len是74，仅仅是索隐列NAME的长度，ref也只有一个。也就是说索引只使用了一部分（NAME），pos索引并没有使用到。所以，最佳左前缀法则还有一点就是不能跳过索引的中间列。

最佳左前缀法则：如果索引使用了多个列（复合索引），要遵守最佳左前缀法则，就是说查询从索引的最左前列开始并且不跳过索引中的列。

比如上面的案例，要么使用NAME；要么使用NAME，age；要么使用NAME，age，pos。这样的话索引才不会失效。

不要在索引列上做任何的操作（计算，函数，类型转换），会导致索引失效而转向全表扫描

对索引列进行计算下图中的LEFT(NAME,4)表示取得NAME值的前4个

可见，对索引列进行计算的话。索引并没有起作用，导致了索引失效。

存储引擎不能使用索引中范围条件右边的列（范围之后全失效）

全值匹配

当age使用范围之后，范围之后的索引全部失效。

下图中可以看出，key_len是78，说明使用到了索引中的NAME和range。

但是NAME和range其实还是有区别的，NAME是用于检索，而age却用在排序。

尽量使用覆盖索引（索引列和查询列一致），减少select *的使用

当我们使用select *

而我们只取索引列的数据

Using INDEX 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行

MySQL在使用不等于（!=或者<>）的时候会无法使用索引导致全表扫描
IS NULL或者IS NOT NULL也无法使用索引
LIKE以通配符开头（’%abc…’）,MySQL索引失效变成全表扫描

前后都有通配符全表扫描

前面有通配符依旧是全表扫描

当通配符只出现在后面的时候发现是范围扫描

还有一个就是当我们like以常量开头，之后无论有没有%都不会影响索引，索引都可以正常使用

但是问题就是我们在实际问题中或许还是需要使用前后都有通配符的情况的，这该怎么解决呢？

解决like('%abc...%')索引失效的问题

如果两边都有%，我们可以通过覆盖索引来提升性能

创建tbl_user表

未创建索引之前，全部都是全表扫描

创建索引NAME，age，email

同样的上面的所有的查询语句 type已经变成了扫描索引，提升效率。

但是如果我们查询的列比覆盖索引的列多，那么还是会全表扫描

字符串不加单引号索引失效

我们向tbl_user表中添加一个数据 NAME为2000

当2000带单引号能查出来数据

当2000不带单引号也能查出来数据

这是因为MySQL在优化分析的时候，因为我们创建的表结构NAME是varchar类型的数据，MySQL拿到数字类型的时候，会将数字类型转换为varchar类型。这期间存在着隐式类型转换。

执行EXPLAIN分析当使用字符串的时候，使用到了索引，并且type是ref类型

使用数字类型的时候 NAME这个索引就失效了变成了全表扫描

结合第2点不要在索引上做任何操作（类型转换）同时理解

少用or，用它连接时索引失效

练习题分析

建表

建立索引

分析

当where条件为常量并且顺序和索引的顺序一致的时候，毫无疑问，4个索引都用到了

当where条件为常量的时候，但是顺序和索引顺序完全颠倒或者说不一致的时候，4个索引还是全部都使用到了

原因：MySQL查询优化器会自动对我们的SQL进行优化，因为上面的4个都是常量，所以顺序无关，MySQL会优化成与我们创建的索引的顺序相同。

使用到了3个索引，范围之后全失效。c1,c2用来索引数据，c3用来对数据进行排序

这里用到了4个索引

分析：首先优化器对SQL进行优化优化成按照索引的顺序也就是等同于下图由于范围c4是最后一个字段后面已经没有了所以4个索引都使用到了

ordey by后面会有更加详细的介绍

这里使用到了两个索引其实c3也用到了只不过是用于排序而非查找，没有统计到explain的keys中

去掉c4 还是只使用到了两个索引同样c3也是用于排序

order by c4

相比上面的ordey by c3，这里多出了文件排序

order by c2,c3，并且前面的where条件有c1的时候 Extra并没有出现 Using filesort

当我们直接使用c2,c3进行排序的时候 就会出现Using filesort

为了避免出现文件排序那么where条件和ordey by顺序要衔接上，比如我们要order by c2,c3，那么where中要有使用到有效的索引c1就不会出现文件排序

有效的索引不会导致后面的排序用的字段的索引失效

下图中的索引c2,c3就失效了出现了文件排序

order by顺序的问题

order by c3,c2 出现了Using filesort 文件排序

同样是order by c3,c2 这里却没有出现文件排序

原因：因为在where条件中c2就是一个常量，所以在order by c3,c2的时候 ,MySQL优化器会将order by优化成order by c3,常量值。也就是说排序已经不受c2的影响，等同于ordey by c3。

group by 分组

按照索引的顺序进行分组

出现了Using temporary

group by分组，分组之前必排序。所以说group by和order by索引优化几乎是一致的

group by分组如果索引没用起来的话，那么就会有临时表产生，影响效率（Using temporary）