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>