MySQL - 索引详解以及优化；Explain执行计划

淡淡的烟草味﹌ 2024-02-20 11:22 92阅读 0赞

#### 官网文档 ####

[MySQL :: MySQL 5.7 Reference Manual :: 8.3 Optimization and Indexes][MySQL _ MySQL 5.7 Reference Manual _ 8.3 Optimization and Indexes]

### Mysql优化（出自官方文档） - 第八篇（索引优化系列） ###

*  [Mysql优化（出自官方文档） - 第八篇（索引优化系列）][Mysql_ -]
    
     *  [Optimization and Indexes][]
     *  [1 Foreign Key Optimization][]
     *  [2 Column Indexes][]
        
         *  [Indexes in the MEMORY Storage Engine][]
     *  [3 Column Indexes && Multiple-Column Indexes][3 Column Indexes _ Multiple-Column Indexes]
     *  [4 Comparison of B-Tree and Hash Indexes][]
        
         *  [B-Tree Index Characteristics][]
         *  [Hash Index Characteristics][]
     *  [5 Use of Index Extensions][]
     *  [6 Invisible Indexes][]
     *  [7 Descending Indexes][]

###### 声明：以下内容出自动力节点！！！ ######

## 一、索引 ##

### （一）索引 ###

MySQL官方对于索引的定义：**索引是帮助MySQL高效获取数据的数据结构**。

MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。

索引简单理解为“**排好序的可以快速查找数据的数据结构**”。

MySQL中常用的的索引数据结构有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存储引擎)等等

## （二）索引优势 ##

1、提高数据检索的效率，降低数据库的IO成本

2、通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

## （三）索引劣势 ##

索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引也是需要占用空间的。

在索引大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改的同时，MySQL不仅要更新数据，还需要保存一下索引文件。

每次更新添加了的索引列的字段，都会去调整因为更新带来的减值变化后的索引的信息

## （四）索引使用场景 ##

#### 1、哪些情况需要创建索引： ####

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引(where 后面的语句)

3、查询中与其它表关联的字段，外键关系建立索引

4、多字段查询下倾向创建组合索引

5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6、查询中统计或者分组字段

#### 2、哪些情况不推荐建立索引: ####

1、表记录太少

2、经常增删改的表

3、Where条件里用不到的字段不建立索引

### （五）索引分类 ###

#### 1、**主键索引** ####

1、表中的列设定为主键后，数据库会自动建立主键索引。

2、单独创建和删除主键索引语法：

创建主键索引语法： alter table 表名 add primary key (字段);

删除主键索引语法： alter table 表名 drop primary key;

#### 2、 **唯一索引** ####

1、表中的列创建了唯一约束时，数据库会自动建立唯一索引。

2、单独创建和删除唯一索引语法：

创建唯一索引语法：alter table 表名 add unique 索引名(字段)；

或 create unique index 索引名 on 表名(字段)；

删除唯一索引语法：drop index 索引名 on 表名;

#### 3、 **单值索引** ####

即一个索引只包含单个列，一个表可以有多个单值索引。

1、建表时可随表一起建立单值索引

2、单独创建和删除单值索引：

创建单值索引： alter table 表名 add index 索引名(字段);

或 create index 索引名 on 表名(字段);

删除单值索引：drop index 索引名 on 表名;

#### 4、 **复合索引** ####

即一个索引包含多个列：

1、建表时可随表一起建立复合索引

2、单独创建和删除复合索引：

创建复合索引：create index 索引名 on 表名(字段1,字段2);

或 alter table 表名 add index 索引名(字段,字段2);

删除复合索引: drop index 索引名 on 表名;

## **二、性能分析；Explain查看SQL执行计划** ##

### （一） **Explain** ###

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。其作用:

1、表的读取顺序

2、哪些索引可以使用

3、数据读取操作的操作类型

4、那些索引被实际使用

5、表之间的引用

6、每张表有多少行被优化器查询

EXPLAIN关键字使用起来比较简单： explain + SQL语句

### （二）**Explain重要字段名** ###

####  **1、id** ####

select查询的序列号，表示查询中执行select子句或操作表的顺序。

id相同时，执行顺序由上至下。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。

id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

#### 2、 **select\_type** ####

查询的类型，常见值有：

SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。

PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY: 在SELECT或WHERE列表中包含了子查询。

#### 3、 **table** ####

显示这一行的数据是关于哪张表的。

#### 4、**type** ####

访问类型排序:

![389f9c388df140e181d565a61a7ee0ec.png][]

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

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

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

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

**Range**：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

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

**All**：Full Table Scan，将遍历全表以找到匹配的行。

> **从最好到最差依次是：system>const>eq\_ref>ref>range>index>All** 
> 
> **一般来说，最好保证查询能达到range级别，最好能达到ref**

#### 5、 **possible\_keys** ####

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上。

#### 6、 **key** ####

查询中实际使用的索引，如果为NULL，则没有使用索引。

#### 7、 **ref** ####

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

#### 8、 **rows** ####

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

#### 9、 **extra** ####

一些常见的重要的额外信息：

Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。

Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。

Using where：表示使用了where过滤

## 三、索引优化 ##

### （一）索引口诀 ###

> ##### `全值匹配我最爱，最左前缀要遵守；` #####
> 
> ##### `带头大哥不能丢，中间兄弟不能断；` #####
> 
> ##### `索引列上不计算，范围之后全失效；` #####
> 
> ##### `LIKE百分写最右，覆盖索引不写 *；` #####
> 
> ##### `不等空值还有or，索引失效要少用；` #####
> 
> ##### `字符单引不可丢，SQL高级也不难。` #####

1、全值匹配我最爱

建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

2、最佳左前缀原则

最前缀法则，必须有车头，中间车厢不能断

3、不在索引列上做任何操作（计算，函数，（自动/手动）类型转换），会导致索引失效而进行全表扫描

4、存储引擎不能使用索引中范围条件右边的列（范围之后全失效，不包括本身）

若中间索引列用到了范围（>、<、like等），则后面的索引全失效

5、尽量使用覆盖索引（尽量不用\*）

6、like左模糊查询会导致索引失效

有时候右模糊查询，并不能查到想得到的内容；如果想需要使用两边都是%号，又不想索引失效，  
此时应使用覆盖查询（查询范围在已经建立的索引范围之内，尽量与索引的个数，顺序相同）

使用select \*

使用覆盖查询

7、is null 和 is not null将无法使用索引

8、字符串不加引号，索引失效

9、少用or，用它连接会可能会造成索引失效

![57ffda714cbe4bf3b87e436774ca8d9a.png][]

### （二）排序，分组优化 ###

1、尽量避免使用Using FileSort方式排序。

2、order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。

3、where子句中如果出现索引范围查询会导致order by索引失效

![8f8a2d3cfe184c1db8061527bb337220.png][]

[MySQL _ MySQL 5.7 Reference Manual _ 8.3 Optimization and Indexes]: https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
[Mysql_ -]: https://www.cnblogs.com/seancheer/p/11382991.html#mysql%E4%BC%98%E5%8C%96%E5%87%BA%E8%87%AA%E5%AE%98%E6%96%B9%E6%96%87%E6%A1%A3---%E7%AC%AC%E5%85%AB%E7%AF%87%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96%E7%B3%BB%E5%88%97
[Optimization and Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#optimization-and-indexes
[1 Foreign Key Optimization]: https://www.cnblogs.com/seancheer/p/11382991.html#1-foreign-key-optimization
[2 Column Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#2-column-indexes
[Indexes in the MEMORY Storage Engine]: https://www.cnblogs.com/seancheer/p/11382991.html#indexes-in-the-memory-storage-engine
[3 Column Indexes _ Multiple-Column Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#3-column-indexes--multiple-column-indexes
[4 Comparison of B-Tree and Hash Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#4-comparison-of-b-tree-and-hash-indexes
[B-Tree Index Characteristics]: https://www.cnblogs.com/seancheer/p/11382991.html#b-tree-index-characteristics
[Hash Index Characteristics]: https://www.cnblogs.com/seancheer/p/11382991.html#hash-index-characteristics
[5 Use of Index Extensions]: https://www.cnblogs.com/seancheer/p/11382991.html#5-use-of-index-extensions
[6 Invisible Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#6-invisible-indexes
[7 Descending Indexes]: https://www.cnblogs.com/seancheer/p/11382991.html#7-descending-indexes
[389f9c388df140e181d565a61a7ee0ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/52101b10576a460cb40e24b29800630d.png
[57ffda714cbe4bf3b87e436774ca8d9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/9ed5f7d10387483984f8e6add5ef7d37.png
[8f8a2d3cfe184c1db8061527bb337220.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/cee2e6618b83481a9b81e19d7acc4910.png