4 minutes
MySQL查询优化之延迟关联
最近项目上遇到了由于一个分页查询的业务导致请求超时,页面无法正常显示的情况。分析解决问题后,记录下备忘。
简单描述下业务,是一张用来记录用户操作行为的表,数据量大概在1500W左右。前端用户通过选择类型,时间等筛选,得到分页结果。
表结构如下
| t_operate_6 | CREATE TABLE `t_operate_6` (
`Fid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Fcid` bigint(20) unsigned NOT NULL,
`Fuid` bigint(20) unsigned NOT NULL,
`Fname` varchar(32) DEFAULT NULL,
`FroleName` varchar(64) NOT NULL,
`Faction` int(2) NOT NULL,
`FobjectId` varchar(64) DEFAULT NULL,
`FobjectName` varchar(64) DEFAULT NULL,
`Fip` varchar(64) DEFAULT NULL,
`Fdevtype` int(2) NOT NULL DEFAULT '0',
`Fctime` int(11) unsigned NOT NULL,
PRIMARY KEY (`Fid`),
KEY `Fcid` (`Fcid`),
KEY `Fuid` (`Fuid`),
KEY `Fctime` (`Fctime`),
) ENGINE=InnoDB AUTO_INCREMENT=14491762 DEFAULT CHARSET=utf8 |
业务其实并不复杂,原来的实现逻辑基本基于以下俩条SQL语句。(以下数据基于本地测试库,添加了SQL_NO_CACHE条件避免查询语句被缓存)
根据查询条件得到数据总数(前端显示页数)
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime;
+----------+
| count(*) |
+----------+
| 760611 |
+----------+
1 row in set (1.93 sec)
可以看到虽然换成了本地测试库只有76W条数据,也耗时了1.93秒,使用explain分析下SQL语句,如下
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime;
+----+-------------+-------------+------+---------------+------+---------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+-------+--------+------------------------------------+
| 1 | SIMPLE | t_operate_0 | ref | Fcid,Fctime | Fcid | 8 | const | 156317 | Using index condition; Using where |
+----+-------------+-------------+------+---------------+------+---------+-------+--------+------------------------------------+
1 row in set (0.00 sec)
优化思路
通过建表语句可以看到,只是单独对Fcid、Fctime建了索引。其实针对上面的查询语句,直接对该字段建联合索引即可。
CREATE INDEX count ON t_operate_0 (Fcid, Fctime)
再看看查询结果
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime;
+----------+
| COUNT(*) |
+----------+
| 760611 |
+----------+
1 row in set (0.62 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime;
+----+-------------+-------------+-------+-------------------+-------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-------------------+-------+---------+------+--------+--------------------------+
| 1 | SIMPLE | t_operate_0 | range | Fcid,Fctime,count | count | 12 | NULL | 156317 | Using where; Using index |
+----+-------------+-------------+-------+-------------------+-------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
直接命中了联合索引,查询速度从1.93提高到了0.62。那还有没有再优化的空间呢?
我们仔细分析下业务,前端只需要得到数据总数。其实对应表里就有自增的主键字段,直接返回该字段的最新值即可
mysql> SELECT SQL_NO_CACHE Fid FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime DESC LIMIT 1;
+--------+
| Fid |
+--------+
| 762059 |
+--------+
1 row in set (0.01 sec)
仅0.01s就返回了结果!虽然数字和总数有些许出入,但是其实并不影响业务!我们继续看下一条语句。
根据查询条件得到分页数据
mysql> SELECT SQL_NO_CACHE * FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime DESC LIMIT 10000, 10;
10 rows in set (6.89 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime DESC LIMIT 10000, 10;
+----+-------------+-------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
| 1 | SIMPLE | t_operate_0 | ref | Fcid,Fctime | Fcid | 8 | const | 156317 | Using index condition; Using where; Using filesort |
+----+-------------+-------------+------+---------------+------+---------+-------+--------+----------------------------------------------------+
1 row in set (0.00 sec)
优化思路
条件基本和取总数一致,只是多limit了语句,首先还是应该通过建立联合索引覆盖查询条件(上面已经建好)
由于limit语句的原因,mysql其实还是先扫描了前10000条数据,丢弃后返回了需要的10条。针对这种情况<<高性能MySQL>>中其实有提到过利用延迟关联的方法来提高查询效率。优化后的SQL语句如下
mysql> SELECT SQL_NO_CACHE a.* FROM t_operate_0 a, (SELECT Fid FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime DESC LIMIT 10000, 10) b WHERE a.Fid = b.Fid;
10 rows in set (0.02 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE a.* FROM t_operate_0 a, (SELECT Fid FROM t_operate_0 WHERE Fcid = 2111131044822780 AND Fctime >= 1505171339 AND Fctime <= 1542992406 ORDER BY Fctime DESC LIMIT 10000, 10) b WHERE a.Fid = b.Fid;
+----+-------------+-------------+--------+-------------------+---------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+-------------------+---------+---------+-------+--------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10010 | NULL |
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | b.Fid | 1 | NULL |
| 2 | DERIVED | t_operate_0 | range | Fcid,Fctime,count | count | 12 | NULL | 156317 | Using where; Using index |
+----+-------------+-------------+--------+-------------------+---------+---------+-------+--------+--------------------------+
3 rows in set (0.00 sec)
通过建立联合索引和利用延迟关联调整SQL语句,查询时间直接从6.89s提升到了0.02s!
关于延迟关联
<<高性能MySQL>>
个人理解
- 原本的语句由于查询条件是
SELECT * WHERE ... LIMIT
,mysql首先通过where过滤出需要返回的数据,再根据limit扫描数据,丢弃数据,最后返回正确偏移的数据。 - 优化后的语句首先通过覆盖索引拿到对应的主键,虽然也需要扫描索引数据,但是扫描索引的速度是远远大于扫描数据的,然后再通过主键关联主表得到需要的数据,这样充分利用了索引大大提高了查询效率。
参考