Rumah >pembangunan bahagian belakang >tutorial php >MySQL索引优化之覆盖索引
最近处理老的业务代码遇到个问题,本文主要和大家分享MySQL索引优化之覆盖索引,希望能帮助到大家。
最近处理老的业务代码遇到这么个例子:
表结构如下:
CREATE TABLE `group_user` ( `id` int(11) NOT NULL auto_increment, `uid` int(11) NOT NULL, `username` varchar(16) NOT NULL, `gid` int(11) NOT NULL, `create_time` int(10) NOT NULL, `update_time` int(10) NOT NULL, PRIMARY KEY (`id`), KEY `idx_uid` (`uid`), KEY `idx_gid` (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=1530312 DEFAULT CHARSET=utf8
150w的数据,这么一个语句:
SELECT SQL_NO_CACHE uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;
慢查询日志里居然很多用了2秒的,Explain的结果是:
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ | 1 | SIMPLE | group_user | ref | idx_gid | idx_gid | 4 | const | 6535 | Using where; Using filesort | +----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+
从Explain的结果可以看出,查询已经使用了索引,但为什么还这么慢?
分析:首先,该语句ORDER BY 使用了Using filesort文件排序,查询效率低;其次,查询字段不在索引上,没有使用覆盖索引,需要通过索引回表查询;最后,数据分布上,gid相同的比较多,uid散列比较均匀,只利用二级索引的效果一般(不清楚索引分类请点击:MySQL索引分类介绍)。
解决方案:由于只需查询uid字段,添加一个联合索引便可以避免回表和文件排序,利用覆盖索引提升查询速度,同时利用索引完成排序。
覆盖索引:MySQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。
ALTER TABLE group_user ADD INDEX idx_gid_ctime_uid (gid, create_time, uid);
再Explain一次:
EXPLAIN SELECT SQL_NO_CACHE uid FROM group_user USE INDEX(idx_gid_ctime_uid) WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | group_user | ref | idx_gid_ctime_uid | idx_gid_ctime_uid | 4 | const | 6375 | Using where; Using index | +----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+
Extra信息已经有'Using Index',表示已经使用了覆盖索引(平时SELECT *的同事很多,坑)。
为什么语句需要手动指定使用哪个索引?因为MySQL查询优化器有可能使用了idx_gid这个索引,除非把它删除。
经过索引优化之后,线上的查询基本不超过0.001秒。
最后一个问题:如果这个表用的是MyISAM引擎呢,那么实际情况又会如何?
相关推荐:
Atas ialah kandungan terperinci MySQL索引优化之覆盖索引. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!