InnoDB引擎之索引的优化及有效使用

墨蓝 2021-11-29 04:58 154阅读 0赞

`Innodb` 存储引擎的表数据和索引是存储在同一个表空间里面，在一定程度上索引的效率没有`MyISAM`快，但这丝毫不影响`InnoDB`存储引擎成为主流的存储引擎。  
拓展：`MyISAM` 存储引擎的表的数据和索引是分开存储的，即每个 `MyISAM` 在磁盘上存储 `.frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引）`，数据和索引存放在不同的文件里，获取速度更快。

**EXPLAIN 查看执行计划信息的重点列**

+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
    |  1 | SIMPLE      | user  | ref  | in_name       | in_name | 93      | const |    1 | Using where; Using index |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

如上所示，`explain`显示了多列内容，其中`type、possible_keys、key、key_len、ref、rows、Extra`是优化`SQL`时必须要关注的。

*  `type`：`mysql`决定如何查找表中的行，下面是type的类型，从上到下性能由好到差
    
        NULL：MySQL在优化阶段分解语句，在执行阶段甚至不需要访问表或者索引，例如从索引列中查找最小的索引通过单独查找索引就可以完成，而不需要访问表数据
        system,const：通过主键查询数据，只能返回一行数据，主键包括单列主键和联合主键，联合主键必须都存在，否则type列是ref类型，例如联合主键(id,name)，id和name都在where条件中才可以
        eq_ref：MySQL最多只会返回一行数据，待实验
        ref：使用索引查找，但不是主键和唯一索引，它可能会返回多个匹配的行
        range：有范围的索引扫描，即带有 BETWEEN 或在 WHERE 子句里面带有 > 的查询，有时候使用in或or也会显示范围扫描
        index：全表扫描，但会按索引次序进行而不是行，避免了排序，缺点是按索引次序读取数据整个表。如果在 Extra 列中看到了 Using index，说明使用的是覆盖索引，只扫描索引的数据，而不是按照索引次序。
        All：全表扫描
    
    ![在这里插入图片描述][20190726204246343.png]
 *  `possible_keys`  
    可能用到的索引
 *  `key`  
    用到的索引
 *  `key_len`  
    索引的长度。例如联合索引`（name,state,address）`，如果只使用`name`部分的索引，那么长度是`name`部分索引长度，如果使用了`name+state`部分索引，那么长度是两部分索引长度和。
 *  `ref`  
    显示`key`列使用的索引中查找值所用的列或常量
 *  `rows`  
    涉及的行数
 *  **`Extra`**
    
        # 索引相关
        using index：使用了覆盖索引
        using where：在查找使用索引的情况下，需要回表查询数据
        using index condition：查找使用了索引，但是需要回表查询查询数据
        using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询
        # 排序相关
        using index：使用单列索引排序；使用联合索引排序，且必须是联合索引的首列在前，并且多列使用相同的排序。无所谓查询的列是否只是索引列
        using filesort：使用一般列排序或联合索引的非首列
        using index ; using filesort：多字段排序时，部分使用索引字段，部分是一般列；或联合索引的多列时，非首列在在前；或联合索引的多列时，首列在前但多列使用不同的排序
        # 分组相关
        using temporary ; using filesort：group by默认情况下会排序
        using temporary ：使用order by null优化后的情况
        using index：使用索引分组

**索引的使用和优化**

1.  是否使用索引与`where`后的条件顺序无关
2.  索引列使用比较符号，会使用索引，单列索引和联合索引都可以，但是索引后面的索引不会被使用，联合索引比较详见第5条的案例
3.  尽量使用联合索引，减少使用单列索引。例如列`name、state、address`  
    联合索引`(name,state,address)`相当于创建了三个索引：`name、name+state、name+state+address`  
    单列索引`name、state、address`，当三个索引一起作为查询条件时，`MySQL`会选择一个最优的索引（数据的辨识度高的索引）来使用，并不会使用全部索引。  
    **辨识度高**是指根据索引查出的数据量小，例如数据库中有`100w`条数据，`name`和`state`都是单列索引，其中`name='瞎子' and state = 0`的数据只有一条，其他数据均为`name='瞎子' and state = 1`，对于`select * from table_name where name = '瞎子' and state = 0;`，`MySQL`会使用索引`status`
4.  索引全值匹配，例如联合索引`(name,state,address)`，`SQL：select * from table_name where state = '1' and name = '小乔' and address = '北京';`
5.  `SQL`符合联合索引的最左前缀法则，例如联合索引`(name ,state,address)`，要想用到索引，条件中必须要包括联合索引中的第一个列，具体会用几个索引参考下面的代码
    
        # 用到三个列的索引
        where name = '' and state = '' and address = ''
        # 用到两个列的索引
        where name = '' and state = ''
        # 用到name列的索引，因为跳过了state列，所以address的列不会使用索引
        where name = '' and address = ''
        # 用到name和state列的索引，address的列不会使用索引
        where name = '' and state > '1' and address = ''
        # 没有用到索引
        where state = '' and address = ''
6.  通常情况下，我们都会根据`where`条件来创建合适的索引，这是索引优化的一个方面，但数据库通过索引查询数据后要不要回表查询，这也是索引优化的方面，也就是说`MySQL`通过索引查数据时，索引的叶子节点上已经包含要查询的数据，这样就不需要回表查询了。这就是著名的**覆盖索引**。`MySQL`的`InnoDB`引擎只有`B-Tree`类型的索引具有覆盖索引，哈希索引、空间索引和全文索引都没有覆盖索引。  
    覆盖索引：查询只需要访问索引，而无须访问数据行  
    覆盖索引的原理：索引的叶子节点上包含该行的所有索引列  
    例如`id、name、state、address`四列，其中`id`为主键，`(name,state,address)`是联合索引
    
        # 对于下面三个sql，explain查看执行计划，发现Extra列的值为Using where; Using index，对应上面Extra的类型
        select name , address from table_name where name = 'p7';
        select id , name from table_name where name = 'p7';
        select state from table_name where name = 'p7';
        # 下面这个sql的Extra列的值是Using where，因为查询的password不是索引列也不是索引列的一部分
        select id , name , password from table_name where name = 'p7';
7.  `like`尾部模糊查询索引有效，头部模糊查询索引失效，利用覆盖索引优化`like`头部模糊查询
    
        # 通过覆盖索引查询id
        select id from table_name where name like '%p7%';
        # 通过id查询数据
        select * from table_name where id = xxx;
8.  `in`索引有效，`not in`索引无效
9.  `or`前的条件中使用索引，后面条件没有使用索引，那么查询不使用索引
10. `MySQL`执行`SQL`时发现全表扫描比索引快，则不使用索引。  
    示例1：`name`是单列索引，非主键，数据库中99条数据的`name`都是盲僧，只有一条数据是`p7`，在执行`where name = '盲僧'`时，`MySQL`会放弃索引，进行全表扫描。  
    示例2：`name`是单列索引，非主键，如果数据库中所有`name`字段没有`null`的情况，`is null`使用索引，`is not null`进行全表扫描；如果`name`为`null`占大多数，`is null`进行全表扫描，`is not null`使用索引  
    上面两种情况，均是`MySQL`内部优化，根据数据库中的数据占比而定的

[20190726204246343.png]: /images/20211129/6ebd9cfbe46c439a81da4a8f806d447f.png