索引优化(二)

╰半橙微兮° 2023-09-29 12:43 163阅读 0赞

在上一篇[《索引优化(一)》][Link 1]的文章中，已经介绍了联合索引、索引下推、排序分组和文件排序的优化策略，还介绍了如果通过`trace`工具来查看MySQL选择执行方案的过程，这边文章继续介绍常用的索引优化以及如何设计索引。

### 一、分页查询优化 ###

很多时候，业务系统需要实现分页展示的功能，可能会用到下面的SQL:

select * from employees limit 10000,10;

该SQL从表中取出从10001开始的10条数据。虽然从结果看是只查询了10条数据，但实际上这条SQL是先读取10010条记录，然后抛弃前10000条记录，然后读到后面10条想要的数据。

因此，如果要查询一张大表比较靠后的数据，执行效率是非常低的，有两种解决方案：

#### 1.1 依靠自增且连续主键 ####

以下面的SQL为例：

select * from employees limit 90000,5;

该SQL查询从第90001开始的五条数据，由于没有单独指定`order by`，则表示默认通过主键排序。

在前面通过存储过程向employees表插入数据的时候，因为`主键是自增且连续的`，所以可以改写成通过主键去查询从第90001开始的第五行数据，如下：

select * from employees where id > 90000 limit 5;

这两条SQL的查询结果是一模一样的

原始SQL执行计划：  
![在这里插入图片描述][3e38b32eb1c740b1be9ac54c57f1be96.png_pic_center]  
改写后SQL执行计划：  
![在这里插入图片描述][398366a526b24fb2b338d7a6890fb3f1.png_pic_center]

改写后的SQL扫描行数大大减少，效率显著提高。

**但这种优化在实际业务场景中并不适用，因为它对数据表的记录有严格的限制，要保证主键ID一定是自增且连续的。但凡第90000行之前的数据删除一行，这两个SQL的直接结果就是不一样的。**  
**这种优化需要满足两个前提条件：**  
**1) 主键自增且连续**  
**2) 结果是按照主键排序的**

#### 1.2 让返回的字段尽可能少 ####

对于非主键字段排序的分页查询(实际场景中这种情况居多)，以下面的SQL为例:

select * from employees ORDER BY name limit 90000,5;

通过执行计划可以看出，虽然`name`字段有连续索引，但并没有走索引，MySQL通过成本计算觉得扫全表比走索引还要快一些，所以优化器放弃使用索引。

既然我们已经知道`limit`自身的缺陷导致它就是要把之前全部的记录查出来，这是没法改变的，那我们就想可以通过让返回的字段尽可能少来提升效率，可以对其进行改写。

原始SQL执行计划：  
![在这里插入图片描述][a7c87aaa18954d4fbc4b396faa0a198d.png_pic_center]  
改写后SQL执行计划  
![在这里插入图片描述][dcfeb51473f84837901998d27c3d92a5.png_pic_center]

通过关联查询，先分页查询出主键ID，在根据主键ID关联就可以走索引，原始SQL排序使用的是`Using filesort`，而改写为内连接的查询方式后，可以直接使用索引进行排序。

### 二、Join关联查询优化 ###

MySQL的表关联查询中，会用`Nested-Loop Join`算法或`Block Nested-Loop Join`算法。

#### 2.1 数据准备 ####

关联查询中存在驱动表与被驱动表的概念，也就是先查询哪个表，再查询哪个表。

所以这里创建`user1`和`user2`两个表结构完全一样的表，比较数据量不同的表作为驱动表的效率。

CREATE TABLE user1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      name VARCHAR(20) DEFAULT NULL,
      age int(11) DEFAULT NULL,
      PRIMARY KEY (id),
      KEY idx_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    create table user2 like user1;

**user1**表插入10000条数据

DROP PROCEDURE IF EXISTS INSERT_user1;
    delimiter $
    CREATE PROCEDURE INSERT_user1()
    BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE (i<=10000) DO
    	INSERT INTO user1(name,age) VALUES(CONCAT('lizhi',i),i);
    	SET i=i+1;
    END WHILE;
    END $
    CALL INSERT_user1();

**user2**表插入100条数据

DROP PROCEDURE IF EXISTS INSERT_user2;
    delimiter $
    CREATE PROCEDURE INSERT_user2()
    BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE (i<=100) DO
    	INSERT INTO user2(name,age) VALUES(CONCAT('lizhi',i),i);
    	SET i=i+1;
    END WHILE;
    END $
    CALL INSERT_user2();

#### 2.2 Nested-Loop Join(NLJ)算法 ####

嵌套循环连接\*\*Nested-Loop Join(NLJ)\*\*算法的逻辑是：

依次一行一行从第一张表(**称为驱动表**)中读取满足条件的行，然后在这些行数据中取到关联字段，根据关联字段在另一张表(**称为被驱动表**)取出满足条件的行，然后取出两张表的结果合集。

它的执行计划如下：  
![在这里插入图片描述][6df9c6df83294b708c94e4906fda54c0.png_pic_center]  
从执行计划中可以看出:  
user2表是作为驱动表先执行它的查询，user1作为被驱动表(执行计划的`ID`如果一样就从上到下依次执行);优化器一般会选择**小表作为驱动表**，用`where`条件过滤完驱动表，然后再与被驱动表做关联查询。  
**注:使用`inner join时，排在前面的并不一定是驱动表`，MySQL会根据筛选的数据量大小自行选择驱动表**  
当使用`left join`时，左边的表就是驱动表，右边的表是被驱动表；使用`right join`时，右边的表就是驱动表，左边的表是被驱动表。  
一般`Join`语句中，如果执行计划没有`Extra`中没有出现`Using join buffer`则表示使用的算法是`NLJ`。

上面SQL的执行过程大概如下：

*  从表`user2`中过滤出`age=22`的记录，然后从记录中取出一行数据
 *  从第一次的数据中，取出关联字段`name`，然后到`user1`表中查找(由于有索引，基本依次一次就找到)
 *  取出表`user1`中满足条件的行，与`user2`中获取到的结果合并，作为结果返回给客户端
 *  重复上面的步骤

前面的SQL使用`NLJ`的关联查询，整个过程会读取`user2`表中所有数据(扫描100行，`age`没有建索引，直接是`ALL`,全表扫描)，然后遍历每行数据中`name`的值，根据`user2`表中`name`的值利用索引扫描`user1`表中对应的行(因为有索引，一次扫描可以认为是最终只扫描`user1`表中一行完整数据，而`user2`表筛选的数据只有一条，所以`user1`也就只扫描了一行)。因此整个扫描过程扫描了101行数据。

如果被驱动表的关联字段没有索引，使用`NLJ`算法性能就比较低，因为没有索引的情况下，扫描`user1`表时，就是全表进行磁盘扫描，效率很低。  
这时MySQL就会选择`Block Nested-Loop Join(BNL)`算法

#### 2.3 Block Nested-Loop Join(BNL)算法 ####

基于块的嵌套循环连接**Block Nested-Loop Join(BNL)算法相比于`NLJ`算法，`BNL`算法多了一个`Join buffer`的内存空间，关联查询时，会把驱动表的数据全部加载到`join buffer`中，然后扫描被驱动表**，把**被驱动表**每一行数据读取出来与`join buffer`中的数据做对比。

执行计划如下：  
![在这里插入图片描述][d518cbaab1024dec89800307fe91a50f.png_pic_center]  
`Extra`中的`Using join buffer (Block Nested Loop)`说明该关联查询使用的是`BNL` 算法。

上面的SQL关联查询，关联字段没有索引，所以会采用`BNL`算法来执行，其大概过程如下：

*  把`user2`表中的数据都加载进`join buffer`中
 *  把表`user1`中的每一行数据取出来，跟`join buffer`中的数据做比对
 *  返回满足`join`条件的数据

整个过程对`user1`和`user2`都做了一次全表扫描，因此扫描的总行数为100+10000=10100。并且因为`join buffer`中的的数据是无序的，因此表`user1`的每一行数据，都需要在内存中做100次判断，索引内存中的判断次数为100\*10000=100万次  
在这个例子中，`user2`表的数据是比较少的，如果表中数据很多，而`join buffer`的内存空间又是有限的，这个时候就没法一次性将表中数据全部加载到`join buffer`中。`Join buffer`的大小由参数`join_buffer_size`决定，默认值是`256K`。如果不能一次性放下`user2`的所有数据，解决方式就是**分段放**。  
比如`user2`表有500行数据，而`join buffer`一次只能存放300行，那么就先把前300行数据放入到`join buffer`中。然后从`user1`表中取出数据与`join buffer`中数据做比对，这300行记录比对完成后，就把`join buffer`清空，把剩下的200行数据加载到`join buffer`中，然后再重新扫描`user1`表与`join buffer`中的数据做比对。  
相比于一次性全部放入，这种需要放两次的情况，就需要多扫描一次`user1`表。

**问题：关联字段无索引为什么选择BNL算法？**

以上面的SQL为例，如果选择使用`NLJ`算法，就需要一次从磁盘中读取`user2`表的数据，然后在对表`user1`进行10000次磁盘扫描比对，这样下来，就需要100万次的磁盘扫描。而使用`BNL`算法，可以通过几次扫描将`user2`的数据全部加载进`join buffer`中，然后在对`user1`表进行10000次的磁盘扫描，然后在内存中做100万次的比较，因为磁盘扫描的次数大幅度降低，所以性能也就更好一些。

**MySQL对于被驱动表的关联字段，如果有索引，就是用NLJ算法；如果没有索引，就是用BNL算法。**

#### 2.4 SQL关联的优化 ####

##### 2.4.1 关联字段加索引 #####

MySQL做Join操作时，尽量选择`NLJ`算法，即让为关联字段建立索引。  
驱动表因为要全部查询出来，所以对于驱动表的过滤条件，也尽量使用索引，避免全表扫描。总之，能走索引的过滤条件都尽量走索引。

##### 2.4.2 小表驱动大表 #####

在写多表关联的SQL时，如果明确直到哪张表是小表，可以使用`straight_join`写法固定连接驱动方式，省去MySQL优化器判断的时间。

`straight_join`功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。  
比如下面的SQL:

select * from user2 straight_join user1 on user2.name = user1.name;

执行`user2`作为关联查询的驱动表。  
`straight_join`只适用于`inner join`，并不适用于`left join，right join`。（因为`left join，right join`已经代表指定了表的执行顺序）  
尽可能让优化器去判断，使用`straight_join`一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

大表和小表并不是看原始记录数，而是按照各自的条件进行过滤，过滤完成之后，计算参与`join`的各个字段的总数据量，**数据量小的那个表，就是小表，应该作为驱动表**

##### 2.4.3 IN和EXISITS优化 #####

`IN`和`EXSITS`的优化原则依然是小表驱动大表  
假设有A和B两个表：

当B表的数据集小于A表时，`in`优于`exsits`

select * from A where id in (select id from B)

等价于:

for(select id from B){
        
      select * from A where A.id = B.id
    }

当A表的数据集小于B表时，`exsits`优于`in`

select * from A where exists (select 1 from B where B.id = A.id)

等价于:

for(select * from A){
      select * from B where B.id = A.id
    }

将主查询A的数据，放入到子查询B中做条件验证，根据验证的结果(true或false)来决定主查询的数据是否保留

总结：

1、`EXISTS (subquery)`只返回TRUE或FALSE,因此子查询中的`SELECT *`也可以用`SELECT 1`替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别  
2、`EXISTS子查询`的实际执行过程可能经过了优化而不是我们理解上的逐条对比  
3、`EXISTS子查询`往往也可以用JOIN来代替，何种最优需要具体问题具体分析

### 三、count()查询优化 ###

#### 3.1 统计方式比较 ####

-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
    mysql> set global query_cache_size=0;
    mysql> set global query_cache_type=0;

在统计全表的记录时，通常有以下四种方式来统计:

EXPLAIN select count(1) from employees;
    EXPLAIN select count(id) from employees;
    EXPLAIN select count(name) from employees;
    EXPLAIN select count(*) from employees;

**注：以上四种方式，只有根据某个字段统计时，不会统计该字段为null的数据行**

执行计划：  
![在这里插入图片描述][6b0fcd901cd6465b8ebdbd937159e64d.png_pic_center]  
这四个SQL的执行计划是一模一样的，可见这四个SQL的执行效率都差不多，但这四个SQL还是有一些略微的差异，主要分为两种情况：

统计字段有索引：

在统计字段有索引的情况下，`count(字段)`会走二级索引，二级索引索引存储数据比主键索引少，所以`count(字段) > count(id)`  
综合就是：  
**count(\*)≈count(1)>count(字段)>count(id)**

统计字段无索引：

如果统计的字段没有索引，`count(字段)`就没法走索引，只能全表扫描，而`count(id)`还可以走主键索引，所以`count(id) > count(字段)`  
综合就是：  
**count(\*)≈count(1)>count(id)>count(字段)**

#### 3.2 常见优化方案 ####

##### 3.2.1 MySQL自行维护总行数 #####

对于MyISAM存储引擎的表，不带where条件的count查询性能是非常高的，因为MyISAM存储引擎的表的总行数会被MySQL存储在磁盘上；查询时直接取出来就可以了，不需要计算。

##### 3.2.2 show table status #####

![在这里插入图片描述][8d4c1f3bc0064f11a8f13c9b00e5833e.png_pic_center]  
如果只需要统计表记录数的估计值，可以使用`show table status`，性能也是非常高的，只是记录数不是绝对准确的而已：

show table status LIKE 'employees';

##### 3.2.3 将数据维护在缓存里面 #####

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

##### 3.2.4 增加数据库计数表 #####

插入或删除表数据行的时候同时维护计数表，这样就可以很简单的保证事务一致性。计数器表可以通过多条记录来摊分记录总数，类似于JDK中`LongAddr`的**热分散**设计思想。

### 四、数据类型选择 ###

在MySQL中选择正确的数据类型，对于性能至关重要，一般遵循下面两步：  
1、确定合适的大类型：数字、字符串、时间、二进制(基本不用)  
2、确定具体的类型：有无符号，取值范围、变长定长等。  
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。

#### 4.1 数值类型 ####

1）如果整形数据没有负数，如ID号，建议指定为`UNSIGNED`无符号类型，容量可以扩大一倍。  
2）建议使用**TINYINT**代替**ENUM、BITENUM、SET**。  
3）避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。  
4）DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。`DECIMAL`实际是使用字符串存储的，所以它所占用的字节大小与`DECIMAL(M,D)`中的M和D有关，如果M>D,字节大小为M+2;否则字节大小为D+2.  
5）建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。  
6）整数通常是最佳的数据类型，因为它速度快，并且能使用`AUTO_INCREMENT`。  
\***注意：使用INT时，如果指定了长度，这个长度并不是存储的数值长度，而是显示长度。如果只是指定了数值并没有什么用，只有与`ZEROFILL`结合时才会生效，表示用`0`进行填充，比如字段类型为`TINYINT(2)`，而字段值为5，它实际显示的内容就是`05`,也就是在显示长度不够时，用0填充**

#### 4.2 日期和时间 ####

1）MySQL能存储的最小时间粒度为秒。  
2）建议用DATE数据类型来保存日期。MySQL中默认的日期格式是`yyyy-mm-dd`。  
3）用MySQL的内建类型**DATE、TIME、DATETIME**来存储时间，而不是使用字符串。  
4）当数据格式为**TIMESTAMP**和**DATETIME**时，可以用`CURRENT_TIMESTAMP`作为默认(MySQL5.6以后)，MySQL会自动返回记录插入的确切时间。  
5）**TIMESTAMP**是UTC时间戳，与时区相关。  
6）**DATETIME**的存储格式是一个`YYYYMMDD HH:MM:SS`的整数，与时区无关，你存了什么，读出来就是什么。  
7）除非有特殊需求，一般的公司建议使用**TIMESTAMP**，它比**DATETIME**更节约空间，但是像阿里这样的公司一般会用**DATETIME**，因为不用考虑**TIMESTAMP**将来的时间上限问题。

#### 4.3 字符串 ####

1）字符串的长度相差较大用**VARCHAR**；字符串短，且所有值都接近一个长度用**CHAR**。  
2）**CHAR**和**VARCHAR**适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用**VARCHAR**类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。  
3）尽量少用**BLOB**和TEXT，如果实在要用可以考虑将**BLOB**和**TEXT**字段单独存一张表，用id关联。  
4）**BLOB**系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。  
5）**BLOB**和TEXT都不能有默认值。

**更多SQL优化的内容可以参考阿里巴巴开发手册中SQL优化指南部分。**

### 五、索引设计原则 ###

#### 5.1 先代码，后索引 ####

一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

#### 5.2 联合索引尽量覆盖查询条件 ####

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的**where、order by、group by**的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

#### 5.3 不要在小基数字段上建立索引 ####

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。  
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。  
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

#### 5.4 长字符串可以采用前缀索引 ####

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。

当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。

对于这种\*\*varchar(255)\*\*的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 **KEY index(name(20),age,position)**。

此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。

**注：这个20是阿里巴巴的开发手册提供的一个通用值，在后面的文章中，会介绍如果通过现有的数据进行计算来设置这个前缀值**。

但是假如你要是**order by name**，那么此时你的**name**因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， **group by**也是同理。所以这里要对前缀索引有一个了解。

#### 5.5 where与order by冲突时优先where ####

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?

一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。  
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

#### 5.6 基于慢sql查询做优化 ####

可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

[Link 1]: https://blog.csdn.net/sermonlizhi/article/details/124500985?spm=1001.2014.3001.5502
[3e38b32eb1c740b1be9ac54c57f1be96.png_pic_center]: https://img-blog.csdnimg.cn/3e38b32eb1c740b1be9ac54c57f1be96.png#pic_center
[398366a526b24fb2b338d7a6890fb3f1.png_pic_center]: https://img-blog.csdnimg.cn/398366a526b24fb2b338d7a6890fb3f1.png#pic_center
[a7c87aaa18954d4fbc4b396faa0a198d.png_pic_center]: https://img-blog.csdnimg.cn/a7c87aaa18954d4fbc4b396faa0a198d.png#pic_center
[dcfeb51473f84837901998d27c3d92a5.png_pic_center]: https://img-blog.csdnimg.cn/dcfeb51473f84837901998d27c3d92a5.png#pic_center
[6df9c6df83294b708c94e4906fda54c0.png_pic_center]: https://img-blog.csdnimg.cn/6df9c6df83294b708c94e4906fda54c0.png#pic_center
[d518cbaab1024dec89800307fe91a50f.png_pic_center]: https://img-blog.csdnimg.cn/d518cbaab1024dec89800307fe91a50f.png#pic_center
[6b0fcd901cd6465b8ebdbd937159e64d.png_pic_center]: https://img-blog.csdnimg.cn/6b0fcd901cd6465b8ebdbd937159e64d.png#pic_center
[8d4c1f3bc0064f11a8f13c9b00e5833e.png_pic_center]: https://img-blog.csdnimg.cn/8d4c1f3bc0064f11a8f13c9b00e5833e.png#pic_center