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>