针对 select A, select AC, 和 select ABC 这样的查询，如何建立索引？组合索引一文看懂

面试题：针对 select A, select AC, 和 select ABC 这样的查询，如何建立索引？

其实面试官问我们的是组合索引（联合索引）的知识点

先来看看什么索引

索引

在数据库中，索引是一种特殊的数据结构，用于加快数据检索速度。它类似于书籍的目录，可以帮助数据库系统快速定位到存储数据的位置，而不必完全扫描整个数据表。

索引可以根据特定列或多个列的值来排序，并将这些值映射到实际数据存储的位置，从而加速查询和检索操作。它们提供了更快的数据访问路径，减少了数据库系统需要扫描的数据量，因此能够显著提高查询效率。

不同类型的索引（如B+树索引、哈希索引等）在不同情况下表现出不同的优势。B+树索引是最常见的一种，它适用于范围查询和排序，而哈希索引则更适合等值查询。数据库的索引通常是根据查询的模式和数据访问方式来选择的。

总的来说，索引可以加速查询速度，但也会增加数据插入、删除和更新的成本。因此，在创建索引时需要权衡查询性能和数据变更成本。

索引分类

索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；
• 组合索引(也叫复合索引、联合索引)，即一个索引包含多个列。

单列索引

普通索引是最基本的索引，它没有任何限制

• 创建索引

 CREATE INDEX index_name ON your_table(your_column);

• 创建表的时候直接指定

 CREATE TABLE your_table(
  id INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  userpassword VARCHAR(16) NOT NULL,
  index / key `index_name` (your_column)
 );

• 删除索引的语法：

DROP INDEX `index_name` ON your_table;

唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

• 创建索引

 CREATE UNIQUE INDEX `index_Name` ON your_table(your_column)

• 创建表的时候直接指定

 CREATE TABLE your_table(
  id INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  userpassword VARCHAR(16) NOT NULL,
  UNIQUE `index_name` (your_column)
 );

主键索引

主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

 CREATE TABLE your_table( 
  id INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  userpassword VARCHAR(16) NOT NULL, 
  PRIMARY KEY(id) 
 );

记住：一个表只能有一个主键。

组合索引

由多个字段组成的索引叫组合索引。

CREATE TABLE `student` (
`id`  int NOT NULL ,
`name`  varchar(50) NULL DEFAULT NULL ,
`age`  int NULL DEFAULT NULL ,
`score`  int NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
key `key_ABC` (`name`, `age`, `score`) 
)

建立这样的组合索引（ABC），其实是相当于分别建立了下面三组组合索引：

(name)，(name, age)，(name, age, score)

为什么没有age, score这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。

简单的理解就是只从最左面的开始组合。组合索引的第一个字段必须出现在查询组句中，并且不能跳跃，这个索引才会被用到，因此并不是只要包含这三列的查询都会用到该组合索引。

组合索引使用

下面的几个SQL就会用到这个组合索引：

select * from student where name = 'cungle' -- 相当于按照A进行查询
select * from student where name = 'cungle' and age = 23 -- 相当于按照AB进行查询
select * from student where name = 'cungle' and age = 23 and score = 100 -- 相当于按照ABC进行查询

而下面几个则不会用到：

select * from student where age = 23 -- 相当于按照B进行查询
select * from student where age = 23 and score = 100 -- 相当于按照BC进行查询
select * from student where score = 100 -- 相当于按照C进行查询

注意

索引的字段可以是任意顺序的因为mysql的查询优化器会帮我们矫正查询顺序以匹配我们的索引，达到快速查询，尽量避免全表扫描的情况出现，如：

select * from student where age = 23 and name = 'cungle' -- 相当于按照BA进行查询，但是也会使用到索引
select * from student where score = 100 and age = 23 and name = 'cungle' -- 相当于按照CBA进行查询，但是也会使用到索引

索引失效

可能导致索引失效的场景

• 索引列不独立. 独立是指: 列不能是表达式的一部分, 也不能是函数的参数
• 使用了左模糊，like "%xxx"
• 使用or查询的部分字段没有索引
• 字符串条件为使用 ’ ’ 引起来
• 不符合最左前缀原则的查询
• 索引字段建议添加 NOT NULL 约束
• 隐式转换导致索引失效
• 索引失效导致行锁升级为表锁

当然还有特殊情况

使用了组合索引中的部分索引

select * from student where name = 'cungle' and score = 100 -- 这种相当于按照AC进行查询
select * from student where score = 100 and name = 'cungle' -- 这种相当于按照CA进行查询
-- 这两种查询条件是相同的

当我们用explain（explain详细参数说明请参考这一篇）进行sql分析时发现

可以看到type为ref这时也是使用到了索引，为什么呢，其实where a = xxx and c = xxx相当于只用到了联合索引中的A索引，可以看到后面的ref为一个const，也就是说只命中了一个索引a，

后面的c并没有使用到索引，可以看到Extra有Using where和Using index，详情请往下看trace工具分析

而where a = xxx and b = xxx 时是：

还有一种情况，覆盖索引

什么是覆盖索引呢？

就是：将被查询的字段，建立到联合索引（如果只有一个字段，普通索引也可以）。例如：

select name,age,score FROM student 

这种情况下用explain分析一下：

可以看到type为index

• index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

当然，可能explain这种方式还是不能真正的看到sql的执行过程和优化器优化过程以及真正用到的索引和索引具体使用情况，所以我们用mysql的trace工具来帮我们分析一下

MySQL中trace工具的使用

trace是MySQL5.6版本后提供的SQL跟踪工具，通过使用trace可以让我们明白optimizer（优化器）如何选择执行计划。
注意：开启trace工具会影响mysql性能，所以只适合临时分析sql使用，用完之后请立即关闭。

语法

如何开启trace工具

-- 1，打开trace，设置格式为JSON，设置trace的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。
set optimizer_trace="enabled=on",end_markers_in_J