Mysql索引 like篇

Mysql索引 like篇

Mysql在查询中使用like的时候，对应的字段上面的索引是否会生效呢？

• like ‘张’ 用到了索引
• like ‘张%’ 前缀匹配 用到了索引
• like ‘%张%’ 中间匹配 没有用到了索引
• like ‘%张’ 后缀匹配 没有用到了索引

mysql> CREATE TABLE `tea` (
    ->   `id` bigint NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
    ->   `number` bigint DEFAULT NULL COMMENT '编号',
    ->   `no_index_number` bigint DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `index1` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 3 warnings (0.08 sec)

mysql>
mysql> select * from tea;
+----+------+--------+-----------------+
| id | name | number | no_index_number |
+----+------+--------+-----------------+
|  1 | 张三 |  10001 |               3 |
|  2 | 李四 |  10002 |               2 |
|  3 | 王五 |  10003 |               1 |
+----+------+--------+-----------------+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql>
mysql> explain select * from tea where name like '张';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tea   | NULL       | range | index1        | index1 | 768     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tea where name like '张%';
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tea   | NULL       | range | index1        | index1 | 768     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql>
mysql> explain select * from tea where name like '%张%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tea   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tea where name like '%张';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tea   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

• 正则表达式匹配

mysql> explain select id,number from tea where name REGEXP '张';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tea   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>