常见思路

15.数据库优化

索引

提到接口优化，相信最直接的就是会想到添加索引。没错，添加索引就是成本最小的优化，而且一般优化效果都很不错。

索引优化这块的话，一般从这几个维度去思考：

• 你的SQL加索引了没？
• 你的索引是否真的生效？
• 你的索引建立是否合理？

SQL没加索引

我们开发的时候，容易疏忽而忘记给SQL添加索引。所以我们在写完SQL的时候，就顺手查看一下 explain执行计划。

explain select * from user_info where userId like '%123';

你也可以通过命令show create table ，整张表的索引情况。

show create table user_info;

如果某个表忘记添加某个索引，可以通过alter table add index命令添加索引

alter table user_info add index idx_name (name);

一般就是：SQL的where条件的字段，或者是order by 、group by后面的字段需需要添加索引。

索引不生效

查看索引使用情况

在sql前面加上explain关键字，就能够看到它的执行计划，通过执行计划，我们可以清楚的看到表和索引执行的情况，索引有没有执行、索引执行顺序和索引的类型等。

explain详解

用一条简单的sql看看使用explain关键字的效果：

explain select * from test1;

id列

该列的值是select查询中的序号，比如：1、2、3、4等，它决定了表的执行顺序。

某条sql的执行计划中一般会出现三种情况：

• id相同
• id不同
• id相同和不同都有 那么这三种情况表的执行顺序是怎么样的呢？

id相同:

执行sql如下：

explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id

结果：

图中我们看到执行结果中的两条数据id都是1，是相同的。

这种情况表的执行顺序是怎么样的呢？

答案：从上到下执行，先执行表t1，再执行表t2。

id不同:

执行sql如下：

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果：

我们看到执行结果中两条数据的id不同，第一条数据是1，第二条数据是2。

这种情况表的执行顺序是怎么样的呢？

答案：序号大的先执行，这里会从下到上执行，先执行表t2，再执行表t1。

id相同和不同都有:

执行sql如下：

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果：

我们看到执行结果中三条数据，前面两条数据的的id相同，第三条数据的id跟前面的不同。

这种情况表的执行顺序又是怎么样的呢？

答案：先执行序号大的，先从下而上执行。遇到序号相同时，再从上而下执行。所以这个列子中表的顺序顺序是：test1、t1

select_type列

常用的其实就是下面几个：

下面看看这些SELECT类型具体是怎么出现的：

SIMPLE

执行sql如下：

explain select * from test1;

结果：

它只在简单SELECT查询中出现，不包含子查询和UNION，这种类型比较直观就不多说了。

PRIMARY 和 SUBQUERY

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果：

我们看到这条嵌套查询的sql中，最外层的t1表是PRIMARY类型，而最里面的子查询t2表是SUBQUERY类型。

DERIVED

执行sql如下：

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果：

最后一条记录就是衍生表，它一般是FROM列表中包含的子查询，这里是sql中的分组子查询。

UNION 和 UNION RESULT

执行sql如下：

explain
select * from test1
union
select* from test2

结果：

test2表是UNION关键字之后的查询，所以被标记为UNION，test1是最主要的表，被标记为PRIMARY。而表示id=1和id=2的表union，其结果被标记为UNION RESULT。

UNION 和 UNION RESULT一般会成对出现。

此外，回答上面的问题：id列的值允许为空吗？

如果仔细看上面那张图，会发现id列是可以允许为空的，并且是在SELECT类型为： UNION RESULT的时候。

table列

该列的值表示输出行所引用的表的名称，比如前面的：test1、test2等。

但也可以是以下值之一：

：具有和id值的行的M并集N。 ：用于与该行的派生表结果id的值N。派生表可能来自（例如）FROM子句中的子查询 。 ：子查询的结果，其id值为N

partitions列

该列的值表示查询将从中匹配记录的分区

type列

该列的值表示连接类型，是查看索引执行情况的一个重要指标。

执行结果从最好到最坏的的顺序是从上到下。

我们需要重点掌握的是下面几种类型：

system > const > eq_ref > ref > range > index > ALL

在演示之前，先说明一下test2表中只有一条数据：

并且code字段上面建了一个普通索引：

下面逐一看看常见的几个连接类型是怎么出现的：

system

这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。

const

通过一次索引就能找到数据，一般用于主键或唯一索引作为条件的查询sql中，执行sql如下：

explain select * from test2 where id=1;

结果：

eq_ref

常用于主键或唯一索引扫描。执行sql如下：

explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

结果：

此时，有人可能感到不解，const和eq_ref都是对主键或唯一索引的扫描，有什么区别？

答：const只索引一次，而eq_ref主键和主键匹配，由于表中有多条数据，一般情况下要索引多次，才能全部匹配上。

ref

常用于非主键和唯一索引扫描。执行sql如下：

explain select * from test2 where code = '001';

结果：

range

常用于范围查询，比如：between … and 或 In 等操作，执行sql如下：

explain select * from test2 where id between 1 and 2;

结果：

index

全索引扫描。执行sql如下：

explain select code from test2;

结果：

ALL

全表扫描。执行sql如下：

explain select *  from test2;

结果：

possible_keys列

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

该列表示可能的索引选择。

key列

该列表示实际用到的索引。

可能会出现possible_keys列为NULL，但是key不为NULL的情况。

演示之前，先看看test1表结构：

test1表中数据：

使用的索引：

code和name字段使用了联合索引。

explain select code  from test1;

结果：

这条sql预计没有使用索引，但是实际上使用了全索引扫描方式的索引。

key_len列

该列表示使用索引的长度。上面的key列可以看出有没有使用索引，key_len列则可以更进一步看出索引使用是否充分。不出意外的话，它是最重要的列。

有个关键的问题浮出水面：key_len是如何计算的？

决定key_len值的三个因素：

1. 字符集
2. 长度
3. 是否为空

常用的字符编码占用字节数量如下：

目前我的数据库字符编码格式用的：UTF8占3个字节。

mysql常用字段占用字节数：

此外，如果字段类型允许为空则加1个字节。

上图中的 184是怎么算的？

184 = 30 * 3 + 2 + 30 * 3 + 2

再把test1表的code字段类型改成char，并且改成允许为空：

执行sql如下：

explain select code  from test1;

结果：

怎么算的？ 183 = 30 * 3 + 1 + 30 * 3 + 2

还有一个问题：为什么这列表示索引使用是否充分呢，还有使用不充分的情况？

执行sql如下：

explain select code  from test1 where code='001';

结果：

上图中使用了联合索引：idx_code_name，如果索引全匹配key_len应该是183，但实际上却是92，这就说明没有使用所有的索引，索引使用不充分。

当"EXPLAIN"语句中的"key_len"显示使用索引不充分时，意味着查询在执行时没有充分利用表的索引，可能会导致以下后果：

• 查询性能下降：由于没有充分利用索引，查询执行时需要扫描更多的数据行，因此查询的性能可能会降低。(查询性能下降：索引的主要作用是加速查询操作，当查询没有充分利用索引时，数据库需要扫描更多的数据行来获取所需的数据。这将导致查询的执行时间增加，性能下降。对于复杂的查询或者大数据量的表来说，性能下降可能会非常明显。在极端情况下，如果查询始终无法充分利用索引，可能会导致系统陷入繁忙状态，无法及时响应其他查询和事务。)
• 系统资源浪费：当查询没有充分利用索引时，可能会导致更多的系统资源被占用，如CPU、内存和磁盘I/O等，从而影响其他查询和事务的执行。(系统资源浪费：当查询没有充分利用索引时，数据库可能需要使用更多的系统资源来完成查询操作。这可能导致CPU、内存和磁盘I/O等资源的使用率上升，从而影响其他查询和事务的执行。例如，如果CPU使用率过高，可能会导致其他查询和事务的执行被阻塞，从而降低系统的并发处理能力。同样，如果内存使用率过高，可能会引发内存泄漏或者OOM（内存溢出）等异常。)
• 增加数据库负载：如果长时间存在索引不充分的情况，可能会导致数据库的负载增加，进一步影响数据库的稳定性和可用性。(增加数据库负载：如果长时间存在索引不充分的情况，可能会导致数据库的负载增加。在高负载的情况下，数据库可能需要处理更多的查询和事务，从而影响数据库的稳定性和可用性。此外，高负载还可能导致数据库的响应时间变长，进一步影响用户体验和业务处理速度。在极端情况下，过高的负载可能会导致数据库崩溃，从而导致数据丢失和系统崩溃。)
• 影响业务处理：由于查询性能下降，可能会导致业务处理速度变慢，影响用户体验，甚至可能导致业务系统崩溃。因此，当"EXPLAIN"语句中的"key_len"显示使用索引不充分时，应尽快采取措施优化查询和索引，以提高查询性能、降低系统资源消耗，并保障数据库的稳定性和可用性。(影响业务处理：由于查询性能下降，可能会导致业务处理速度变慢，影响用户体验。例如，在电子商务网站上，如果商品搜索查询的性能下降，可能会导致用户无法快速找到所需的商品，从而降低购物体验。在某些情况下，如果查询性能下降严重，可能会导致业务系统崩溃，从而导致数据丢失和系统崩溃。因此，对于关键业务系统来说，确保查询充分利用索引非常重要。)

ref列

该列表示索引命中的列或者常量。

执行sql如下：

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

结果：

我们看到表t1命中的索引是const(常量)，而t2命中的索引是列sue库的t1表的id字段。

rows列

该列表示MySQL认为执行查询必须检查的行数。

对于InnoDB表，此数字是估计值，可能并不总是准确的。

Extra列

该字段包含有关MySQL如何解析查询的其他信息，这列还是挺重要的，但是里面包含的值太多，就不一一介绍了，只列举几个常见的。

Impossible WHERE

表示WHERE后面的条件一直都是false

explain select code  from test1 where 'a' = 'b';

Using filesort

表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。

explain select code  from test1 order by name desc; 

这里建立的是code和name的联合索引，顺序是code在前，name在后，这里直接按name降序，跟之前联合索引的顺序不一样。

Using index

表示是否用了覆盖索引，说白了它表示是否所有获取的列都走了索引。

Using temporary

表示是否使用了临时表，一般多见于order by 和 group by语句。

explain select name  from test1 group by name;

Using where

表示使用了where条件过滤。

索引优化的过程

先用慢查询日志定位具体需要优化的sql

使用explain执行计划查看索引使用情况

重点关注：

• key（查看有没有使用索引）
• key_len（查看索引使用是否充分）
• type（查看索引类型）
• Extra（查看附加信息：排序、临时表、where条件为false等）

一般情况下根据这4列就能找到索引问题。

以一个慢sql工单举例，大概是这样的 “select xxx from tabel where type = 1”。

咦，type字段明明有索引啊，为啥是慢sql呢？

通过执行explain，发现实际上数据库执行了全表扫描，从而被系统判定为慢sql。所以这就要分析有索引的情况，索引为什么失效了。

不满足最左匹配

假如表中有个组合索引，idx_start_org_code_start_province_id_trans_type,它的索引顺序如下：

start_org_code,

start_province_id,

trans_type

当我们从第二个索引字段开始查询时就不会走索引：

因为不满足索引的 最左匹配原则，该原则只适用于 1 12 123 这种查询情况。

后端接口性能优化分析-数据库优化（中）:https://developer.aliyun.com/article/1413678