联合索引和多个单列索引使用中的索引命中情况及索引创建原则

联合索引和多个单列索引使用中的索引命中情况

mysql Ver 8.0.28

联合索引

创建表

CREATE TABLE `multi_index_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

添加联合索引

ALTER TABLE `multi_index_test` ADD index index_a_b_c(`a`, `b`, `c`);

sql命中情况

SELECT * FROM `multi_index_test` WHERE a='xxx' and b='xxx' and c='xxx'; 命中
SELECT * FROM `multi_index_test` WHERE a='xxx' and b='xxx'  命中
SELECT * FROM `multi_index_test` WHERE a='xxx' and c='xxx'  命中
SELECT * FROM `multi_index_test` WHERE c='xxx' and b='xxx' and a='xxx';  命中
SELECT * FROM `multi_index_test` WHERE a='xxx'; 命中

---
SELECT * FROM `multi_index_test` WHERE a='xxx' or b='xxx' or c='xxx'; 未命中
SELECT * FROM `multi_index_test` WHERE  b='xxx' and c='xxx';未命中
SELECT * FROM `multi_index_test` WHERE  b='xxx'; 未命中
SELECT * FROM `multi_index_test` WHERE  c='xxx'; 未命中

• 创建联合索引时一定要注意创建的列顺序 对索引中的所有列或者前几列执行搜索时 多列索引非常有用

多个单列索引

创建表

CREATE TABLE `single_index_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

创建多个单独的索引

ALTER TABLE `single_index_test` ADD index index_a(a);
ALTER TABLE `single_index_test` ADD index index_b(b);
ALTER TABLE `single_index_test` ADD index index_c(c);

*索引命中情况

SELECT * FROM `single_index_test` WHERE a='xxx';命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx';命中index_b
SELECT * FROM `single_index_test` WHERE c='xxx';命中index_c
SELECT * FROM `single_index_test` WHERE a='xxx' and b='xxx' and c='xxx';只命中index_a 
SELECT * FROM `single_index_test` WHERE a='xxx' and b='xxx'; 只命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx' and a='xxx'; 只命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx' and c='xxx'; 只命中index_b
SELECT * FROM `single_index_test` WHERE c='xxx' and b='xxx'; 只命中index_b

SELECT * FROM `single_index_test` WHERE a='xxx' or b='xxx'; 未命中

• 多个单列索引都有效的时候 mysql的优化策略选取了它认为最高效的一个index，而不会都使用

总结

1. 创建联合索引时一定要注意创建的列顺序 对索引中的所有列或者前几列执行搜索时 多列索引非常有用
2. 多个单列索引都有效的时候 mysql的优化策略选取了它认为最高效的一个index，而不会都使用

构建索引的原则

• 使用唯一索引

  索引的基数越大，索引的效果越好。

• 使用短索引

  尽量选择区分度高的列去建立索引
  如果对字符串列进行索引，尽可能的指定一个前缀长度，例如一个char(200)的列，前面10个或者20个字符进行索引能够进可能的减少索引的空间，也能使查询更快，较小的索引涉及的磁盘IO也较小，较短的值比较起来更快。

• 利用最左索引

  创建一个N列索引的时候，实际上创建的是一个mysql可利用的n个索引，多列索引可以起到几个索引的作用，可以利用索引中最左边的列集来匹配行，这样的列集称为最左索引。

• 不要过度的使用索引

  尽量去根据需求修改索引 而不是去新增索引
  每个额外的索引都会占用额外的空间，降低写操作性能，在修改表的内容的时候，表的索引也会更新，索引越多，所花时间越长，索引太多，也会使mysql选择不到索要使用的最好索引，只保持所需要的索引有利于查询优化。

• 对于innDB存储引擎的表

  默认记录会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存，如果没有主键，但是有唯一索引，会按照唯一索引的顺序保存，如果即没有主键，也没有唯一索引，那么表中会自动生成一个内部列（按照主键和内部列进行访问是最快的），inndb表的普通索引都会保存主键的键值，所以主键要尽可能的选择较短的数据类型，可以有效的减少索引的磁盘占用，提高索引的缓存效果。

本作品采用《CC 协议》，转载必须注明作者和本文链接