首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >线上的MySQL优化案例

线上的MySQL优化案例

作者头像
AsiaYe
发布2019-11-06 17:44:27
1.2K0
发布2019-11-06 17:44:27
举报
文章被收录于专栏:DBA随笔DBA随笔
线上的SQL优化案例(一)
今天上班的时候,业务方询问了一个问题,说是某一服务每次在查询的时候会有0.5s的延迟,让DBA帮忙查一查到底是什么原因,听到0.5s的这个数字的时候,我感觉问题倒不是很严重,我解决这个问题的方法如下:

1、先查看了一下慢日志中的内容,发现慢日志中没有具体的记录。这个问题比较好解决,其实他的本质是设定的慢日志的阈值是1s,只有超过1s的SQL语句才会被记录,这里我把参数long_query_time的值设置成为0.4,这样,就可以把查询超过0.4s的SQL都记录到慢日志里面了。

2、查看慢日志中的SQL语句,如下:

SELECT * FROM `account` 
WHERE  
`accountid` = 20000000528 
and 
`accounttype` = 1 
and 
`appid`=10005;

语句也比较简单,是根据某3列的值来查询一条记录,我先查看了一下结果集,结果集中只有一条记录,说明这个查询的结果是比较少的。

3、查看对应的表结构:

mysql--dba_admin 12:59:09>>show create table account\G
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `appid` int(4) unsigned NOT NULL,
  `uid` bigint(8) unsigned NOT NULL,
  `accountid` char(32) NOT NULL DEFAULT '',
  `accounttype` int(4) unsigned NOT NULL,
  `mtime` bigint(8) unsigned NOT NULL DEFAULT '0',
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `appid` (`appid`,`accountid`,`accounttype`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到,这个表有一个主键,一个普通索引,这个普通索引包含appid、accountid、accounttype以及uid这4个字段,是联合索引,查看了一眼表中的数据量,大概有5w多条,并不算很多。

4、初步修改意见

有了对表结构的认知和SQL语句,我当时的第一反应是这个SQL写的是否满足需求,问了一下业务方,他们说是为了查询uid写出来的SQL,那么其实这个SQL中的select *是不必要的,如果只要uid的值,则可以只查新uid即可,那么SQL语句其实可以被改写为:

SELECT uid FROM `account` 
WHERE  
`accountid` = 20000000528 
and 
`accounttype` = 1 
and 
`appid`=10005;

这样写,有一个好处,就是需要查询的uid和三个查询条件appid、accountid以及accounttype字段都在同一棵索引的B+树上,这样可以利用覆盖索引的原理,避免回表扫描。

5、这样测试了一把之后,业务方反馈说是并没有明显的降低查询时间。时间还是在0.3s左右,还是不能满足需求。这个时候,我已经开始怀疑这个表的索引创建的是否有问题了,于是首先查看了一眼表中的数据,看看appid的值都包含哪些吧。

mysql--dba_admin 12:02:25>>select distinct accounttype from account limit 10;
+-------------+
| accounttype |
+-------------+
|           1 |
+-------------+
1 row in set (0.02 sec)

mysql--dba_admin 12:02:25>>select distinct appid  from account limit 10;
+-------------+
|   appid     |
+-------------+
|       10005 |
+-------------+
1 row in set (0.02 sec)

经过查看,发现appid的值目前只包含一个10005,而accounttype的值也只包含数字1,而accountid和uid的值包含5w左右,这样问题已经有点明确了。

6、修改索引字段

我们再来看这个联合索引:

key appid(appid,accountid,accounttype,uid)

这样明显是不合理的,因为appid和accounttype的值基数太小,只有1,索引这个索引应该修改,具体的改法有很多,我是通过修改联合索引的先后顺序,将基数大的字段放在前面,这样扫描的时候能够过滤的更加准确一些。将索引改为:

key 'idx_accid' (accountid,uid,appid,accounttype)

这样,再来看一眼执行计划:

mysql--dba_admin 13:05:51>>explain SELECT uid FROM `account` WHERE  `accountid` = 20000000528;
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows  | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | account | index | idx_accid     | idx_accid | 112     | NULL | 57237 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这个问题比较奇怪,为什么看着走了索引,但是却扫描了这么多记录???改成单个字段的试试,这里我又把索引改成了:

KEY `idx_accid` (`accountid`)

只保留一个字段,看看执行结果:

mysql--dba_admin 13:12:17>>explain SELECT uid FROM `account` WHERE  `accountid` = 20000000528 AND `accounttype` = 1 and `appid`=10005;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | account | ALL  | idx_accid     | NULL | NULL    | NULL | 57237 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

我去,竟然不走索引,这就奇了怪了。

7、终于,我想了又想,猜了又猜,看到了一处细节。

建表语句如下:

mysql--dba_admin 13:16:24>>show create table account\G
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `appid` int(4) unsigned NOT NULL,
  `uid` bigint(8) unsigned NOT NULL,
  `accountid` char(32) NOT NULL DEFAULT '',
  `accounttype` int(4) unsigned NOT NULL,
  `mtime` bigint(8) unsigned NOT NULL DEFAULT '0',
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `idx_accid` (`accountid`)
) ENGINE=InnoDB AUTO_INCREMENT=57055 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SQL语句如下:

SELECT uid FROM `account`

WHERE

`accountid` = 20000000528

AND

`accounttype` = 1

and

`appid`=10005;

到这里,问题应该已经能够看出来了,这个字段accountid在表中应该是一个char类型的,但是在SQL语句中写成了整数类型,发生了隐式类型转换,导致索引不可用。

弄清楚了这个,再来看这两个执行计划:

mysql--dba_admin 13:17:54>>explain SELECT id FROM `account` WHERE  `accountid` = 20000000528;
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows  | Extra                    |
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | account | index | idx_accid     | idx_accid | 96      | NULL | 57237 | Using where; Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

mysql--dba_admin 13:18:25>>explain SELECT id FROM `account` WHERE  `accountid` = '20000000528';
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | account | ref  | idx_accid     | idx_accid | 96      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+

其中扫面行数的区别最明显,一个是5w多行,一个是1行。所以,只需要将原来的SQL语句改为:

SELECT uid FROM `account`

WHERE

`accountid` = '20000000528'

AND

`accounttype` = 1

and

`appid`=10005;

就可以解决问题了。

8、到这里,问题基本上算是解决了,使用accountid作为索引的字段,这样在进行where条件过滤的时候,就可以过滤出很少的记录,然后再回表去通过id值查到想要的uid值,这样就比较快了。

最终的优化前后对比结果如图:

优化之前:

优化之后:

可以看到,效果还是很明显的。

今天的文章就到这里了。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-08-23,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档