线上的MySQL优化案例
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值，这样就比较快了。
最终的优化前后对比结果如图：
优化之前：
优化之后：
可以看到，效果还是很明显的。
今天的文章就到这里了。
腾讯云开发者
