like百分号在前走不走索引

模糊查询，前置百分号不走索引；后置百分号才会走索引这可能是大部分人都知道的常识

表： t_user 索引：idx_mobile

CREATE TABLE `t_user` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `NAME` varchar(64) DEFAULT NULL COMMENT '名字',
  `MOBILE` varchar(11) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`ID`),
  KEY `INDEX_MOBILE ` (`MOBILE`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

DELIMITER $$
CREATE PROCEDURE populate_users(IN user_count int)
BEGIN
	DECLARE i int DEFAULT 0;
	DECLARE mobile varchar(64);
	WHILE i < user_count DO
		-- 生成随机手机号		
		SELECT concat('1', substring(cast(3 + 
			(rand() * 10) % 7 AS char(50)), 1, 1), right(left(trim(cast(rand() 
			AS char(50))), 11), 9)) INTO mobile;
		INSERT INTO t_user(NAME, MOBILE) VALUES (CONCAT('user', i), mobile);
	END
END$$
DELIMITER ;

CALL populate_users(150369);

首先看一下数据量，t_user有150369条数据

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM t_user;
+----------+
| count(*) |
+----------+
|   150369 |
+----------+
1 row in set (0.01 sec)

SELECT MOBILE FROM t_user WHERE MOBILE LIKE ‘%12’;

通过执行计划可以看出，实际上是走了索引的

mysql> EXPLAIN SELECT MOBILE FROM t_user WHERE MOBILE LIKE '%12';
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | INDEX_MOBILE | 36      | NULL | 133801 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

再看一下查同时查非索引字段的情况，是没走索引的。

mysql> EXPLAIN SELECT `NAME`, MOBILE FROM t_user WHERE MOBILE LIKE '%12';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 133801 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从上面几次试验，可以得到一个结论：like查询百分号前置，并不是100%不会走索引。如果只select索引字段，或者select索引字段和主键，也会走索引的

SELECT MOBILE FROM t_user WHERE MOBILE LIKE ‘%12’; 为什么会走索引？

使用到了索引覆盖这个优化策略，扫描INDEX_MOBILE这个索引就可以获取查询结果。

通过EXPLAIN ANALYZE可以看到，过滤成本是13484.35，扫描了14865行，整张表的数据150369条，而符合条件的有1493条，也就是说为了找出这1493条数据，扫描了133801行，大约占全表的89%左右。虽然走了索引，但是几乎是全索引扫描

mysql> SELECT count(ID) FROM t_user WHERE MOBILE LIKE '%12';
+-----------+
| count(ID) |
+-----------+
|      1493 |
+-----------+
1 row in set (0.09 sec)

mysql> EXPLAIN ANALYZE SELECT MOBILE FROM t_user WHERE MOBILE LIKE '%12'\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t_user.MOBILE like '%12')  (cost=13484.35 rows=14865) (actual time=0.056..96.805 rows=1493 loops=1)
    -> Index scan on t_user using INDEX_MOBILE  (cost=13484.35 rows=133801) (actual time=0.045..42.458 rows=150369 loops=1)

1 row in set (0.09 sec)

这里是没有用到索引条件下推这个优化策略的，因为Extra列里没有出现Using index condition

SELECT NAME, MOBILE FROM t_user WHERE MOBILE LIKE '%12’这种情况没走是因为查了非索引字段，需要回表查询name字段值，如果使用INDEX_MOBILE索引，则要进行一次全索引扫描，同时还要进行一次聚簇索引扫描（即全表扫描），不如直接进行全表扫描

mysql> EXPLAIN ANALYZE SELECT `NAME`, MOBILE FROM t_user WHERE MOBILE LIKE '%12'\G;
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t_user.MOBILE like '%12')  (cost=13484.35 rows=14865) (actual time=0.081..110.698 rows=1493 loops=1)
    -> Table scan on t_user  (cost=13484.35 rows=133801) (actual time=0.046..53.859 rows=150369 loops=1)

1 row in set (0.11 sec)

ERROR:
No query specified

总结：
like百分号在前不一定不走索引，使用索引覆盖的情况还是可能会走索引的