作为开发也要了解的 mysql 优化思路

作为开发人员，数据库知识掌握的可能不是很深入，但是一些基本的技能还是要有时间学习一下的。作为一个数据库菜鸟，厚着脸皮来总结一下 mysql 的基本的不能再基本的优化方法。

为了更好的说明，我假想出来了一个业务场景，可能在实际业务中并不存在这样的场景，只为举例说明问题：

表结构说明

• 用户账号表（account），主要存储用户账号、密码、注册时间等信息，1万条数据

• 用户基本信息表（userinfo），主要存储用户个人信息，包括年龄、性别等，关联 account 表，关联字段 account_id，1万条数据

• 订单表（orderinfo）,主要存储用户订单信息，关联account 表，关联字段 account_id，10万条数据

如果需要表结构和数据初始化的脚本，可以在本公众号回复关键字 「mysql」，这里就不占篇幅了。

业务需求说明

统计出年龄大于 30 岁，性别为女（0）的用户所下订单的总数量。 当然用其他方式可以实现，但这里不考虑非数据库处理的其他方式。

下面是 sql 查询语句，三个表做 join 查询，并通过三个条件做筛选。做查询之前，这三个表都没有做其他处理，只是主键 INT 类型设置了自增。 执行下面的语句，在我本地的时间是 35s 左右，这已经不能忍受了。

SELECT
    count(*)FROM
    account aLEFT JOIN userinfo u ON a.id = u.account_idLEFT JOIN orderinfo o on a.id =o.account_idWHERE
    u.age >= 30 and u.sex=0  and o.id is NOT NULL;
    
    ## 查询时间30多秒

使用 explain 命令分析

碰到这种执行时间非常慢的慢查询语句时，就要有请神器 explain 命令了，这是 mysql 提供的查询语句优化分析工具。

使用方法非常简单，就是在查询语句前加上 explain 命令，比如分析上面的语句就是这样的：

EXPLAIN SELECT
                count(*)FROM
    account aLEFT JOIN userinfo u ON a.id = u.account_idLEFT join orderinfo o on a.id =o.account_idWHERE
     u.age >= 30 and u.sex=0 and o.id is NOT NULL;

命令执行后是下面这样的结果：

下面分别解释一下各个字段的含义：

id

每个 SELECT 都会自动分配一个唯一的标识符。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置。本例中因为只有一个 select ,所以 id 都是1。

下面两条语句会编号为1、2，可以运行试一下：

#  包含子查询的EXPLAIN SELECT * FROM accountWHERE id IN 
    (       SELECT          max(account_id)         FROM            orderinfo   );
    
    #  带有union的联合查询    EXPLAIN SELECT *FROM accountWHERE id = 100UNION ALLSELECT *FROM accountWHERE id = 101;

select_type

查询的类型。有如下几种类型：

table

查询的是哪个表，显示表名或者别名

partitions

查询的分区，如果数据库没有做过分区操作，此字段为 null

type

表示查询语句的扫描类型，有如下几种：

性能从高到低为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys

表示查询时, 能够使用到的索引。但是, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被使用到。在查询时具体使用了哪些索引, 由 key 字段决定

key

当前查询真正使用的索引

ref

表示使用了哪个列或 const 与 key(查询所用到的索引) 一起从表中做选择

rows

可以 sql 的优化过程就是为了减小 rows 字段的数量，rows 表示要扫描的行数，行数越多，当然查询的时间就越长。

extra

该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

Using index："覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错；

Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

回过头来看我们上面的例子，这三个表只有主键 id 有索引。

1、首先先扫描 userinfo 表 ，type 为 ALL ，为全表扫描， rows 字段为 10000 行，扫描了 10000 行。

2、然后连接了 account 表，并使用索引 PRIMARY (也就是主键 id),通过 ref userinfo.account_id，进行了行选择，所以这里的 rows 为1，也就是没有进行扫描，直接定位到了要查询的行。

3、之后扫描 orderinfo 表，type 为 ALL ,还是全表扫描，rows 为 99900。

所以三次扫描执行下来，在我本地的机器上平均35s左右。

简单优化

优化原则大体上是这样的：

• sql 层面有优化空间的，先优化了再说。最常用的手段就是加索引。

• 如果 sql 语句无法优化了，看一下是不是能够修改 sql 查询语句的结构，比如有子查询的语句，能不能用 union 查询两次或多次。

• 如果 sql 层面确实无法优化了，考虑用程序的方式，或者修改架构。但并不是说 sql 层面优化了，程序中就不需要优化了，两者并不冲突，当然是性能越快越好了。

来看一下这个例子，join 了三个表，但这三个表都只有主键有索引。第一步优化：加索引。加索引有一下几个原则：

1、较频繁的作为查询条件的字段应该创建索引

2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，也就是区分度太低，比如性别，比如查看性别的区分度可以用这个语句：

SELECT
    count(*),
    sexFROM
    userinfoGROUP BY
    sex;
    
    +----------+------+
| count(*) | sex  |
+----------+------+
| 5000     | 0    |
| 5000     | 1    |
+----------+------+

可以看到，一共有两个性别，每个5000，即使加了索引，每次也需要扫描一半的数据。

3、更新非常频繁的字段不适合创建索引；

4、不会出现在 WHERE 子句中的字段不该创建索引

先给 userinfo 表的 account_id 字段加上索引，因为 join 连接条件是用的它。加索引的命令如下：

ALTER TABLE userinfo ADD INDEX index_account_id (`account_id`);

查看索引：

SHOW INDEX FROM userinfo;

顺便说以下删除索引的命令：

ALTER TABLE userinfo DROP INDEX index_account_id;

再次执行 explain 命令，结果如下：

看到没，查询 userinfo 时使用了刚刚创建的索引，rows 马上变成了1，再次执行，执行时间就下降到了0.5s以下。

然后再给 orderinfo 的 account_id 建立索引，再次 explain ,分析如下：

这次 orderinfo 表查询的时候走了索引，但是 userinfo 表没有，mysql 会自动选择最优的索引。再次执行查询，查询时间降到了30ms左右。

如果查询的条件较多，还可以考虑联合索引，比如本例中可以考虑给account_id、age、sex 建立联合索引，只是举个例子，sex 字段其实并不适合纳入索引列。

ALTER TABLE userinfo ADD INDEX index_accountid_age_sex (`account_id`, `age`, `sex`);

但由于 mysql 的自动选择最优索引的机制，即使加了联合索引，也还是会优先使用 orderinfo 的索引，因为使用那个索引效率更高。但是如果 userinfo 的记录更多，那结果就不一样了。

注意点

1、除非列有要求要存空值 null，否则建议列设置为不允许为 null，因为 null 无法利用索引，而且会占用额外的空间；

2、建议减少对大表的 join 查询，如果是 myisam 引擎会产生表锁，会导致其他写操作被阻塞。innodb 引擎会产生行锁，倒是影响不大；

最后

本篇主要是为了说明 mysql 的分析方法，就是用 explain 命令。发现问题是关键步骤，至于解决方法，每个场景的解决方法都会有不同，这就需要各位结合自身经验，或者借助搜索引擎，或者请教更专业的人来想办法了。

原文出处