关于MySQL查询时索引的方法

以此表为例

CREATE TABLE `single_table`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `key2` int NULL DEFAULT NULL,
  `key3` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `key_part1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `key_part2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `key_part3` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `common_field` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_key1`(`key1`) USING BTREE,
  UNIQUE INDEX `idx_key2`(`key2`) USING BTREE,
  INDEX `idx_key3`(`key3`) USING BTREE,
  INDEX `idx_p1_p2_p3`(`key_part1`, `key_part2`, `key_part3`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

const

select * from single_table where id = 143

上面这条SQL语句会直接利用主键值在聚簇索引中定位对应的用户记录，可以理解为有且只有一个记录，一般在主键索引，唯一索引中此索引方法才会生效

ref

select * from single_table where key1 = 'abc'

此SQL语句是以一个常量进行等值匹配，其扫描区间为['abc','abc']，也就是说单点等值扫描区间。查询代价也是比较小的。

ref_or_null

select * from single_table where key1 = 'abc' or key1 is null

此SQL语句是以一个常量进行等值匹配，其扫描区间为['abc','abc']，并且加了一个[null,null]的范围

range

select * from single_table where key1 = in('abc','bcd') or (key2 >= 128 and key2 <= 586)

此SQL语句在执行时的扫描区间为['abc','abc']  ['bcd','bcd']  [128,586] 会形成多个单点范围扫描或范围扫描

index

SELECT  key_part1,key_part2,key_part3 FROM single_table WHERE key_part3 = 'abc'

对于此SQL来说他需要的结果恰好是整个二级索引的记录，也就是扫描整个二级索引记录，且不需要进行回表

all

母庸质疑，放弃索引进行全表扫描；因为某些情况走二级索引时产生的范围较大且需要回表，此时就会放弃走索引

index_marge

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'rp889932'

对于此SQL来说进行了idx_key1 和 idx_key3的索引合并，MySQL会对两个索引进行合并进行查询；其要求是二级索引的id必须是排序的。目的是为了通过排好序的主键id进行回表操作时，减少随机IO带来的性能开销。