做开发也要掌握的 mysql 优化思路

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

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

表结构说明

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

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

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

业务需求说明

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

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

SELECT

count(*)

FROM

account a

LEFTJOINuserinfo uONa.id= u.account_id

LEFTJOINorderinfo oona.id=o.account_id

WHERE

u.age>=30andu.sex=ando.idisNOTNULL;

## 查询时间30多秒

使用 explain 命令分析

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

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

EXPLAIN

SELECT

count(*)

FROM

account a

LEFTJOINuserinfo uONa.id= u.account_id

LEFTjoinorderinfo oona.id=o.account_id

WHERE

u.age>=30andu.sex=ando.idisNOTNULL;

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

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

id

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

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

# 包含子查询的

EXPLAIN

SELECT*FROMaccount

WHEREidIN

(SELECTmax(account_id)FROMorderinfo);

# 带有union的联合查询

EXPLAINSELECT*

FROMaccount

WHEREid =100

UNIONALL

SELECT*

FROMaccount

WHEREid =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

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

key

当前查询真正使用的索引

ref

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

rows

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

extra

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

Using filesort：当 Extra 中有 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 , 都建议优化去掉, 因为这样的查询 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(*),

sex

FROM

userinfo

GROUPBY

sex;

+----------+------+

|count(*) | sex |

+----------+------+

|5000||

|5000|1|

+----------+------+

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

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

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

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

ALTERTABLEuserinfoADDINDEXindex_account_id (`account_id`);

查看索引：

SHOWINDEXFROMuserinfo;

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

ALTERTABLEuserinfoDROPINDEXindex_account_id;

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

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

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

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

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

ALTERTABLEuserinfoADDINDEXindex_accountid_age_sex (`account_id`,`age`,`sex`);

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

注意点

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

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

最后

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