MySQL：索引（Index）语句

索引的限制

• 每个表最多可以有 16 个索引（InnoDB 表的限制）。

• 单个索引最多可以包含 16 列。

• 索引列的最大长度为 767 字节（对于 CHAR, VARCHAR, 和 BINARY 类型），3072 字节（对于 BLOB 类型）。

索引的使用场景

• 普通索引：适用于一般的查询场景。

• 主键索引：作为表的唯一标识符，用于快速查找记录。

• 唯一索引：用于确保某一列或多列的值是唯一的，例如作为唯一标识符。

• 全文索引：用于全文检索，例如搜索引擎。

• 组合索引：用于加速涉及多个列的查询。

索引的性能考量

• 写操作：索引会增加写操作的开销，因为每次插入或更新数据时都需要更新索引。

• 读操作：合理的索引可以极大地提高查询速度，特别是对于大型表。

• 索引维护：定期分析和优化索引以保持最佳性能。

创建索引

创建索引时的注意事项

• 选择性高的列更适合创建索引。
• 尽量避免在经常修改的列上创建索引。
• 使用前缀索引来减少索引的大小和提高性能（适用于较长的字符串列）。

创建表时创建索引

CREATE TABLE table_name(
	-- 字段定义
	-- ...
	
-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
	PRIMARY KEY ('column1'),
-- 创建唯一索引
	UNIQUE INDEX ('column2'),
-- 创建单例索引
	INDEX ('column3'),
-- 创建组合索引
	INDEX ('column4', 'column5', ...)
)ENGINE = INNODB DEFAULT CHARSET = utf8

在已经存在的表中创建索引

使用 ALTER TABLE 语句为表创建索引

ALTER TABLE table_name
ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  
[index_name] (column [(长度)] [ASC | DESC]) [USING 索引方法]；

• UNIQUE：可选。表示索引为唯一性索引。

• FULLTEXT：可选。表示索引为全文索引。

• SPATIAL：可选。表示索引为空间索引。

• INDEX 和 KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。

• index_name：可选。给创建的索引取一个新名称。索引名称应具有唯一性，以免与其他索引发生冲突。若不指定名称，MySQL 会默认生成一个索引名称。

• column：指定索引对应的字段的名称，该字段必须是前面定义好的字段。

• 长度：可选。指索引的长度，必须是字符串类型才可以使用。

• ASC：可选。表示升序排列。

• DESC：可选。表示降序排列。

• 索引方法默认使用 B+Tree。

普通索引 INDEX

普通索引是最基本的索引类型，可以加速对表中任意列的查询。

• 语法：

ALTER TABLE table_name
ADD INDEX index_name ('column');

主键索引 PRIMARY KEY

主键索引用于标识表中的每一行记录，它是唯一的且不允许为空。

• 语法：

ALTER TABLE table_name
ADD 
PRIMARY KEY ('column');

唯一索引 UNIQUE

唯一索引确保索引列中的值是唯一的（除了NULL值，NULL值不受唯一性约束限制）。

• 语法：

ALTER TABLE table_name
ADD 
UNIQUE (column_list);

全文索引 FULLTEXT

全文索引用于支持全文搜索，适用于文本内容的搜索。

• 语法：

ALTER TABLE table_name
ADD 
FULLTEXT index_name (column_list);

组合索引

组合索引是在多个列上建立的索引，可以用于加速涉及这些列的查询。

ALTER TABLE table_name
ADD 
INDEX index_name ('column1','column2',...);

空间索引 SPATIAL

使用空间索引，需要确保 MySQL 数据库版本支持地理空间功能，并且已经安装了适当的地理空间库。从 MySQL 5.7 版本开始，InnoDB 开始提供对空间索引的支持。

ALTER TABLE table_name
ADD 
SPATIAL index_name (column_list);

添加索引

使用 CREATE INDEX 语句，能够在已存在的表中增加除主键索引之外的其他索引，必须为索引设置索引名，且不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  
index_name ON  table_name (column_list) 
[USING 索引方法]；

普通索引

CREATE INDEX index_name ON table_name ('column');

组合索引

CREATE INDEX index_name ON table_name ('column1','column2',...) 

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_list) 

全文索引

CREATE FULLTEXT INDEX index_name ON table_name (column_list) 

空间索引

空间索引用于地理空间数据，支持空间查询和地理坐标搜索。

CREATE SPATIAL INDEX index_name ON table_name (column_list) 

• 注意：空间索引通常应用于 GEOMETRY 类型的列。

删除索引

删除索引可以使用 ALTER TABLE 或 DROP INDEX 语句来实现，并且 DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理

DROP INDEX index_name on table_name;

-- 删除指定索引
ALTER TABLE table_name
DROP INDEX index_name;

-- 删除主键索引
ALTER TABLE table_name
DROP PRIMARY KEY;

执行 DROP PRIMARY KEY 语句时，因为一个表只可能有一个主键索引，因此不需要指定索引名。如果没有创建主键索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

查看建立的索引

可以使用 SQL 语句或直接使用工具查看表中已经建立的索引

SHOW INDEX FROM table_name;

查看SQL语句对索引的使用情况

在 SELECT 语句前加上 EXPLAIN，可查看 SQL 语句对索引的使用情况，即查询 SQL 的查询执行计划。

执行如下语句：

EXPLAIN SELECT * FROM book WHERE price < 30;

查询结果：

也可以使用 SQL 工具查看，如：Navicat 中的“解释”选项即可查看。

使用索引

单例索引

• 当查询只涉及到其中的一个字段时，会使用到该字段指定的索引

• 使用多个索引时，先使用哪个索引后使用哪个索引，是由 MySQL 的优化器经过一些列计算后作出的抉择。

• 涉及到多个索引字段时，如果这些索引字段中，存在主键索引，那么只会使用该索引（MYSQL 优化器会选出并先执行“最严”的索引）

• 涉及到多个索引字段时，如果这些索引字段中，不存在主键索引的话，那么就会使用这些字段对应的索引（如果通过其中的部分索引就能准确定位的话，那么其余的索引就不再被使用）

• 当对索引字段进行 $>$， $<$，$>=$， $<=$，NOT IN，BETWEEN …… AND ……，函数(索引字段)，LIKE 模糊查询 % 在字段前时，不会使用该索引。

组合索引

在实际使用时，如果查询涉及到多列，我们一般都不会将这些列一一创建为单列索引，而是将这些列创建为组合索引。

最左前缀原则

最左前缀原则是数据库中一个重要的索引规则，用于指导在有多个列上的复合索引中，如何使用索引进行查询。

最左前缀原则规定：在尝试使用复合索引进行查询时，必须按照索引中列的顺序依次指定，并且查询条件中连续的列必须是索引的左侧列，不能跳过左侧列，否则该索引将无法使用。

例如，如果一个复合索引包含了两个列 (A, B)。那么，如果我们要使用这个索引来查询数据，必须符合以下条件：

1. 要查询的列至少包含前两个索引列 A 和 B。
2. 查询条件中必须包含 A 列，否则无法使用索引。
3. 如果查询条件中包含了 A 和 B 列，则可以使用复合索引完成查询操作。否则，只能使用单独的 A 列索引或者全表扫描。

最左前缀原则的意义在于，保证复合索引的效率。因为索引是按照列的顺序构建的，只有按照规则使用索引，才能最大限度地利用索引，提高查询效率。如果不遵循最左前缀原则，那么即使建立了复合索引，也无法使用该索引，会导致查询效率降低，需要花费更多的时间和资源。

MySQL 5.7 开始，会自动优化，将条件优化为按组合索引的顺序进行查询。
如：假设组合索引为 A,B,C，会把查询列中的 C,B,A 优化为 A,B,C 使之完全遵循最左原则；会把 C,A 优化为 A,C 使之部分遵循最左原则。即：SQL语句中的对应条件的先后顺序无关。

最左原则的使用情况

• 完全满足最左原则：即时条件中字段的顺序与索引不同，也满足最左原则，此时会使用组合索引

• 部分满足最左原则：假设索引为（A,B,C），查询列条件为 A,C，此时 A满足最左原则，而 C 不满足最左原则，此时 A 会使用组合索引，而 C 不使用组合索引。

• 满足(部分满足)最左原则的字段里，有字段不满足“索引”自身的使用规范：如果 SQL 语句里的字段里，有字段满足了最左原则，这些字段会使用组合索引，但是某些字段不满足“索引”自身的使用规范（如使用比较操作符等），这些字段不会使用组合索引。

• 不满足最左原则：假设索引为（A,B,C），查询列条件为 B,C，此时不满足最左原则，不会使用组合索引。

聚簇索引与非聚簇索引

每个 InnoDB 表具有一个特殊的索引称为 聚簇索引（主索引）（也叫聚集索引，聚类索引，簇集索引）。

• 如果表上定义有主键，该主键索引就是聚簇索引。

• 如果未定义主键，MySQL 取第一个唯一索引（UNIQUE）而且只含非空列（NOT NULL）作为主键，InnoDB 使用它作为聚簇索引。

• 如果没有这样的列，InnoDB 就自己产生一个这样的 ID 值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫 非簇状索引（辅助索引）（secondary indexes）。

回表

当二级索引无法直接查询到 SQL 中 SELECT 需要的所有列的数据时，会通过二级索引查询到聚簇索引（即：一级索引）后，再根据聚簇索引，查询到二级索引中无法提供的数据。

这种通过二级索引查询出一级索引，再通过一级索引查询二级索引中无法提供的数据的过程，就叫做回表。

当无需回表时，不遵循最左原则也是会走组合索引