首頁 > 文章 > 後端開發 > MySQL索引優化之覆蓋索引

MySQL索引優化之覆蓋索引

小云云原創: 2018-03-22 09:29:531656瀏覽

最近處理舊的業務程式碼遇到個問題，本文主要跟大家分享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引擎呢，那麼實際情況又會如何？

MySQL索引優化之覆蓋索引

相關文章