排查 mysql 的索引使用情况

,

mysql 表中已经设置了索引字段，但是查询依然很慢。使用 EXPLAIN 排查问题。

表结构如下：

1
2
3
4
5
6
7
8
9
10
11
-- ----------------------------
-- Table structure for posts
-- ----------------------------
DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `source_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `source_id` (`source_id`) USING BTREE
);

问题发现：

表中导入了约 100w 条数据，已经建立了 source_id 的索引，使用下面的查询语句，耗时达到了 10s，这是什么原因？

1
SELECT * FROM `posts` WHERE `source_id` = 1237564517523599

使用 EXPLAIN 查看结果:

EXPLAIN 的输出格式：

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息

当 source_id 为数字时：

1
EXPLAIN SELECT * FROM `posts` WHERE `source_id` = 1237564517523599

result:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE posts (NULL) ALL source_id (NULL) (NULL) (NULL) 905985 10.00 Using where

key 为 NULL，说明没有用到索引。 rows = 905985，表示此查询一共扫描了 905985 条数据。所以耗时会比较久。

当 source_id 为字符串时：

1
EXPLAIN SELECT * FROM `posts` WHERE `source_id` = "1237564517523599"

result:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE posts (NULL) ref source_id source_id 123 const 1 10.00 (NULL)

key 为 source_id，说明用到了索引。 rows = 1，表示此查询一共扫描了 1 条数据。所以耗时会比较短。

为什么类型不对也可以查询到结果？

表结构中的 source_id 为字符串类型，当在查询语句中使用数字类型也会得到结果，这是因为 mysql 会先把表中 source_id 转换成数字类型，然后再查询。

1
SELECT * FROM `posts` WHERE `source_id` = 100

等同于

1
SELECT * FROM `posts` WHERE convert(`source_id`, signed) = 100