线上的MySQL优化案例

AsiaYe

发布于 2019-11-06 09:44:27

1.3K00

代码可运行

文章被收录于专栏：DBA随笔DBA随笔

运行总次数：0

代码可运行

线上的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 |
+----+-------------+---------+------+---------------+-----------+---------+-------+------+--------------------------+