MySQL覆盖索引 LIKE优化

测试过程：
[cc lang='sql']
表结构,创建数据
root@test 02:16:15>show create table tmp_xf_like\G
*************************** 1. row ***************************
Table: tmp_xf_like
Create Table: CREATE TABLE `tmp_xf_like` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`title` varchar(128) DEFAULT NULL,
`memo` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_userid` (`user_id`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=503 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

insert into tmp_xf_like(user_id,title,memo) values(1,'abc','abcefeghiklimldsdfa'),
(2,'ccc','abcefeghiklimldsdfa'),(3,'bdc','abcefeghiklimldsdfa'),(4,'eee','abcefeghiklimldsdfa');

insert into tmp_xf_like(user_id,title)
select user_id,title from tmp_xf_like;
[/cc]
索引走法
[cc lang='sql']标准的覆盖索引
root@test 02:13:21>explain select id from tmp_xf_like where user_id=1 ;
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 8 | const | 64 | Using index |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

先根据索引，然后回表
root@test 02:13:41>explain select * from tmp_xf_like where user_id=1;
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 8 | const | 64 | |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------+
1 row in set (0.00 sec)

还是覆盖索引，但是因为查询字段是两个，所以extra中提示 Using where
root@test 02:15:05>explain select id from tmp_xf_like where user_id=1 and title='abc';
+----+-------------+-------------+------+---------------+------------+---------+-------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------------+---------+-------------+------+--------------------------+
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 395 | const,const | 64 | Using where; Using index |
+----+-------------+-------------+------+---------------+------------+---------+-------------+------+--------------------------+
1 row in set (0.01 sec)

这个sql会先通过user_id过滤，然后回表再过滤title的数据，虽然title已经在联合索引中
root@test 03:21:47>explain select * from tmp_xf_like where user_id=1 and title like '%b%';
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 8 | const | 64 | Using where |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

这个sql虽然用了like，但是用到了覆盖索引。所以上面的sql我们可以引用这种写法来解决大量回表的问题
root@test 03:21:48>explain select id from tmp_xf_like where user_id=1 and title like '%b%';
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tmp_xf_like | ref | idx_userid | idx_userid | 8 | const | 64 | Using where; Using index |
+----+-------------+-------------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

先使用覆盖索引，然后根据主键和原表关联获取数据
root@test 03:23:12>explain SELECT T2.*
-> FROM (SELECT ID
-> FROM TMP_XF_LIKE
-> WHERE USER_ID = 1
-> AND TITLE LIKE '%b%') T1,
-> TMP_XF_LIKE T2
-> WHERE T1.ID = T2.ID;
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 64 | |
| 1 | PRIMARY | T2 | eq_ref | PRIMARY | PRIMARY | 4 | T1.ID | 1 | |
| 2 | DERIVED | TMP_XF_LIKE | ref | idx_userid | idx_userid | 8 | | 64 | Using where; Using index |
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
[/cc]

MySQL的分页sql也有回表的问题，推荐写法如下
[cc lang='sql']
root@test 03:29:41>explain
-> SELECT T1.ID, T1.user_id, T1.title, T1.memo
-> FROM (SELECT ID
-> FROM tmp_xf_like
-> WHERE user_id = '1'
-> ORDER BY title LIMIT 20, 10) T2,
-> tmp_xf_like T1 FORCE INDEX(PRIMARY)
-> WHERE T1.ID = T2.ID;
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | PRIMARY | T1 | eq_ref | PRIMARY | PRIMARY | 4 | T2.ID | 1 | |
| 2 | DERIVED | tmp_xf_like | ref | idx_userid | idx_userid | 8 | | 64 | Using where; Using index |
+----+-------------+-------------+--------+---------------+------------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

普通的mysql分页：

root@test 03:30:05>explain
-> SELECT T1.ID, T1.USER_ID, T1.TITLE, T1.MEMO
-> FROM TMP_XF_LIKE T1
-> WHERE USER_ID = '1'
-> ORDER BY TITLE LIMIT 20, 10;
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | T1 | ref | idx_userid | idx_userid | 8 | const | 64 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+[/cc]

--eof

---

This work is licensed under a CC A-S 4.0 International License.

---

• ← Previous Post
• Next Post →