作为开发也要了解的 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 a
LEFT JOIN userinfo u ON a.id = u.account_id
LEFT JOIN orderinfo o on a.id =o.account_id
WHERE
    u.age >= 30 and u.sex=0  and o.id is NOT NULL;
    
    ## 查询时间30多秒  

使用 explain 命令分析

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

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

EXPLAIN 
SELECT
                count(*)
FROM
    account a
LEFT JOIN userinfo u ON a.id = u.account_id
LEFT join orderinfo o on a.id =o.account_id
WHERE
     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 account
WHERE id IN 
    (       SELECT          max(account_id)         FROM            orderinfo   );
    
    #  带有union的联合查询
    EXPLAIN SELECT *
FROM account
WHERE id = 100
UNION ALL
SELECT *
FROM account
WHERE 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(*),
    sex
FROM
    userinfo
GROUP 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 命令。发现问题是关键步骤,至于解决方法,每个场景的解决方法都会有不同,这就需要各位结合自身经验,或者借助搜索引擎,或者请教更专业的人来想办法了。

欢迎关注我的微信公众号:古时的风筝

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏轮子工厂

数据库性能优化,原来还可以有这种操作

621
来自专栏JetpropelledSnake

SQL学习笔记之MySQL索引知识点

之前写过一篇Mysql B+树学习,简单的介绍了B+数以及MySql使用B+树的原因, 有了这些基础知识点,对MySql索引的类型以及索引使用的一些技巧,就...

631
来自专栏java学习

数据库_mysql多表操作

多表操作 实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等...

3508
来自专栏数据和云

MySQL SQL优化之覆盖索引

内容概要 利用主索引提升SQL的查询效率是我们经常使用的一个技巧,但是有些时候MySQL给出的执行计划却完全出乎我们的意料,我们预想MySQL会通过索引扫描完成...

4226
来自专栏码神联盟

碎片化 | 第四阶段-35-Struts2-Spring结合jdbc实现查询列表-视频

如清晰度低,可转PC网页观看高清版本: http://v.qq.com/x/page/q0566q6xkdu.html 结合jdbc实现查询功能 步骤: 1...

3559
来自专栏idba

死锁案例之二

一 前言 死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有...

885
来自专栏西安-晁州

sqlserver - FOR XML PATH

FOR XML PATH 有的人可能知道有的人可能不知道,其实它就是将查询结果集以XML形式展现,有了它我们可以简化我们的查询语句实现一些以前可能需要借助函数活...

2200
来自专栏码神联盟

mysql数据库常见锁机制

关于互联网常见层次架构,由于小编还没整理完毕(预计周四推送),先来一篇数据库的干货,来满足下大家的胃口,关于mysql的行级锁、表级锁、页级锁的分析,这个在行业...

4009
来自专栏java一日一条

MySQL分页性能优化指南

很多应用往往只展示最新或最热门的几条记录,但为了旧记录仍然可访问,所以就需要个分页的导航栏。然而,如何通过MySQL更好的实现分页,始终是比较令人头疼的问题。虽...

1373
来自专栏Laoqi's Linux运维专列

Mysql中MyISAM引擎和InnoDB引擎的比较

结论 如果不清楚自己应该用什么引擎,那么请选择InnoDB,Mysql5.5+的版本默认引擎都是InnoDB,早期的Mysql版本默认的引擎是MyISAM --...

3646

扫码关注云+社区

领取腾讯云代金券