做开发也要掌握的 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 命令。发现问题是关键步骤,至于解决方法,每个场景的解决方法都会有不同,这就需要各位结合自身经验,或者借助搜索引擎,或者请教更专业的人来想办法了。

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180420G0B67W00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券