MySQL 隐式转换与模糊匹配的索引使用分析

MySQL服务版本

• 版本信息：Server version: 8.0.30 MySQL Community Server - GPL

字段结构

mysql> desc connection;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int          | NO   | PRI | NULL    | auto_increment |
| type                  | int          | NO   |     | NULL    |                |
| name                  | varchar(50)  | NO   | MUL | NULL    |                |
| server_url_master     | text         | YES  |     | NULL    |                |
| prometheus_url_master | text         | YES  |     | NULL    |                |
| prometheus_url_slave  | text         | YES  |     | NULL    |                |
| username              | varchar(100) | NO   |     | NULL    |                |
| create_time           | timestamp    | YES  |     | NULL    |                |
| update_time           | timestamp    | YES  |     | NULL    |                |
| server_url_slave      | text         | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

索引结构

为表 connection 添加了以下索引

alter table connection add index connection_name_index(name);
alter table connection add index connection_type_index(type)

• 目前索引情况

mysql> SHOW INDEXES FROM connection;
+------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| connection |          0 | PRIMARY               |            1 | id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| connection |          1 | connection_name_index |            1 | name        | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| connection |          1 | connection_type_index |            1 | type        | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

查询分析

int索引查询

• int 索引 int 条件：

mysql> explain select type from connection where type = 1;
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | connection | NULL       | ref  | connection_type_index | connection_type_index | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果：type = 1 使用了索引，type字段的值是ref，表示直接通过索引定位到具体行，效率较高。
Extra：Using index，表示查询使用了覆盖索引，不需要回表查询表数据。

• int 索引 string 条件：

mysql> explain select type from connection where type = '1';
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | connection | NULL       | ref  | connection_type_index | connection_type_index | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

结果：type = ‘1’ 同样使用了索引，type字段的值是ref，表示直接通过索引定位到具体行，效率较高。
Extra：Using index，表示查询使用了覆盖索引，不需要回表查询表数据。

varchar 索引查询

• varchar 索引 int 条件：

mysql> explain select name from connection where name = 56;
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | connection | NULL       | index | connection_name_index | connection_name_index | 202     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

结果：name = 56 使用了索引，但需要回表过滤。type字段的值是index，表示进行了索引扫描。
Extra：Using where; Using index，表示查询使用了索引，但需要通过WHERE子句过滤数据。

mysql> explain select name from connection where name = 56root;
ERROR 1054 (42S22): Unknown column '56root' in 'where clause'

结果：条件隐式转化失败，直接报错。
字段值无法转换为INT：如果STRING类型的字段中存在无法转换为INT的值（如包含非数字字符的字符串），在查询时MySQL会报错

• varchar 索引 string 条件：

mysql> explain select name from connection where name = '56';
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | connection | NULL       | ref  | connection_name_index | connection_name_index | 202     | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

结果：name = ‘56’ 使用了索引，type字段的值是ref，表示直接通过索引定位到具体行，效率较高。
Extra：Using index，表示查询使用了覆盖索引，不需要回表查询表数据。

type 字段：表示查询的访问类型。常见的访问类型有 const、eq_ref、ref、range、index 和 all 等。
如果 type 字段为 const 或 eq_ref，则查询不需要回表。
如果 type 字段为 ref、range、index 或 all，则查询可能需要回表

like 匹配

• MySQL使用索引直接定位到name = '56’的行。这是通过索引快速查找的方式，效率较高。
• 使用了覆盖索引，不需要回表查询表数据。这意味着所有需要的字段（id和name）都在索引中，MySQL可以直接从索引中获取数据，而不需要访问表数据。

mysql> explain select id,name from connection where name like '56';
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | connection | NULL       | index | connection_name_index | connection_name_index | 202     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

• MySQL使用了索引扫描。这意味着MySQL会扫描整个索引，而不是直接定位到具体的行。

mysql> explain select id,name from connection where name = '56';
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | connection | NULL       | ref  | connection_name_index | connection_name_index | 202     | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 最左匹配，范围查询，走索引，使用了索引条件推送 ICP技术，在存储引擎层直接评估索引条件，返回给服务器层，减少回表次数。
• 使用索引进行范围查询 range，效率较高。

mysql> explain select id,name,type from connection where name like '56%';
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | connection | NULL       | range | connection_name_index | connection_name_index | 202     | NULL |    5 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

• 使用了索引扫描，无需回表

mysql> explain select id,name from connection where name like '56%';
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | connection | NULL       | index | connection_name_index | connection_name_index | 202     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

• 不符合最左匹配，但仍然可以索引扫描，子句过滤。
• MySQL仍然可以使用索引进行全索引扫描（index）。这意味着MySQL会扫描整个索引，而不是直接定位到具体的行。
• 在这种情况下，索引扫描的效率比全表扫描（ALL）高，但仍然不如通过索引直接定位到具体行（如ref或range）高效。

mysql> explain select id,name from connection where name like '%6%';
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | connection | NULL       | index | NULL          | connection_name_index | 202     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• 不符合最左匹配，但仍然可以索引扫描，子句过滤。

mysql> explain select id,name from connection where name like '%root';
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | connection | NULL       | index | NULL          | connection_name_index | 202     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

总结

• 隐式转换：
  • 索引字段类型为字符串时，加不加引号都可以走索引。
  • 索引字段类型为int时，如果不加引号且条件值无法隐式转换为int，会报错；否则会自动隐式转换。
• 模糊匹配：
  • LIKE '56%' 符合最左匹配原则，可以利用索引进行范围查询，效率较高。
  • LIKE '%6%' 和 LIKE '%root' 不符合最左匹配原则，但仍然可以使用索引进行全索引扫描，效率不如直接定位到具体行（如 ref 或 range）高效。