mysql中联合索引和多个单列索引的创建原则、索引命中场景示例

数据库  /  管理员 发布于 5个月前   379

下面以mysql8.0.28版本为例测试mysql中联合索引和多个单列索引的创建原则及使用中索引命中场景

创建索引原则

使用唯一索引

这个不用说性价比最好的，索引的基数越大，索引的效果越好。

使用短索引

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

最左索引

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

非必要不要添加索引

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

对InnoDB存储引擎的表

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

联合索引

1.创建联合索引示例测试表

CREATE TABLE `multi_sy_test` (
    `id` int NOT NULL AUTO_INCREMENT,
    `a` varchar(255) NOT NULL,
    `b` varchar(255) NOT NULL,
    `c` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='联合索引测试表';

2.添加联合索引

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

3.sql命中场景

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

ps:

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

多个单列索引

1.创建多个单列索引示例测试表

CREATE TABLE `single_sy_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 COMMENT='多个单列索引测试表';

2.创建多个单独的索引

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

3.多个单列索引命中场景

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

ps：

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

最后

创建有效的索引将会大幅度的提高了查询性能，比如

索引大大减少了服务器需要扫描的数据量；

索引可以帮助服务器避免排序和临时表；

索引可以将随机I/O 变为顺序I/O；

等等...

当然，如果创建无效的索引那就反之，并且是加倍的，希望文章能帮助你