两边都有百分号的,有全表扫描
mysql> EXPLAIN SELECT * FROM staffs where name LIKE '%july%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
百分号在左边,有全表扫描
mysql> EXPLAIN SELECT * FROM staffs where name LIKE '%july';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
百分号在右边的,使用到了索引
mysql> EXPLAIN SELECT * FROM staffs where name LIKE 'july%';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
口诀: 百分Like加右边
解决 like '%字符串%'时索引不被使用的方法
准备数据
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL ,
`age` INT(11) DEFAULT NULL ,
`email` VARCHAR(20) DEFAULT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('1aa1', 21, 'b@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('2aa2', 222, 'a@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('3aa3', 265, 'c@163.com');
INSERT INTO tbl_user(`name`,`age`, `email`)VALUES ('4aa4', 21, 'd@163.com');
CREATE INDEX idx_user_nameAge ON tbl_user(`name`,`age`);
以下查询的字段只要全部是索引中的字段,就能使用索引
mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN SELECT age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name, age FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE name LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
如果两边非要用%模糊查询,就要用覆盖索引