MySQL 索引

一、MySQL 索引的介绍
索引是一种数据库结构，用于加快数据的查询和检索速度。它类似于书籍的目录，可以帮助数据库快速定位和获取所需的数据，从而提高查询性能。

索引主要分为以下几类：
主键索引（PRIMARY KEY）：每张表只能有一个主键索引，用于唯一标识表中的每行数据。
唯一索引（UNIQUE INDEX）：确保索引列的值是唯一的，但可以有空值。
普通索引（INDEX）：可以加快数据的查询速度，但不保证列值的唯一性。
全文索引（FULLTEXT INDEX）：用于对文本类型的列进行全文搜索。

创建索引的语法：

— 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

— 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

— 创建主键索引（通常在创建表时指定）
CREATE TABLE table_name (
column_name data_type PRIMARY KEY
);
创建表的时候直接指定索引：

CREATE TABLE table_name (
column_name1 data_type,
column_name2 data_type,
INDEX index_name (column_name1),
UNIQUE INDEX unique_index_name (column_name2),
PRIMARY KEY (column_name3)
);

普通索引、唯一索引的修改：

— 普通索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
— 唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX unique_index_name (column_name);

普通索引、唯一索引的删除：

— 普通索引
DROP INDEX index_name ON table_name;
— 唯一索引
DROP INDEX unique_index_name ON table_name;

使用 ALTER 命令添加和删除索引

添加：

ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE INDEX unique_index_name (column_name);
删除：

ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX unique_index_name;

二、在创建索引时，需要注意以下几点：

不要过度创建索引：过多的索引会增加数据插入、更新和删除操作的开销，因为每次数据修改都需要同时维护相关的索引。
选择合适的列创建索引：通常在经常用于查询、连接、排序和分组操作的列上创建索引。对于很少使用或数据重复率高的列，创建索引可能效果不佳。
避免在大字段上创建索引：例如，对于很长的文本字段或二进制大对象（BLOB）字段，创建索引可能会降低性能。
考虑索引的组合：如果经常基于多个列进行查询，可以创建组合索引。
对于大型数据表，谨慎创建索引：尤其是在数据量不断增长的情况下，要权衡索引带来的查询性能提升和维护成本。
不要在频繁更新的列上创建索引：这会增加更新操作的时间。
对于小数据表，创建过多索引可能得不偿失，因为直接全表扫描可能更快。
注意索引的顺序：在组合索引中，列的顺序很重要，最常用于查询条件和范围查询的列应放在前面。
定期评估和优化索引：根据业务需求和数据访问模式的变化，适时调整索引。

三、以下这些字段通常不太适合创建索引：

数据重复率高的字段：例如性别、状态等字段，其值的种类较少，创建索引的效果不明显，反而会增加插入、更新和删除操作的开销。
很少用于查询、连接、排序和分组的字段：如果某个字段几乎不会在查询条件中使用，创建索引没有实际意义。
数据量巨大且很少用于范围查询的字段：例如大文本字段或二进制大对象（BLOB）字段，为其创建索引可能会严重影响性能。
频繁更新的字段：对经常更新的字段创建索引会增加维护索引的成本，可能导致更新操作变得缓慢。
小型数据表中的字段：对于数据量很小的表，进行全表扫描的速度可能已经足够快，创建索引带来的性能提升可能不明显。

四、组合索引是在一个索引中包含多个列。在使用组合索引时，有以下要点：

最左前缀原则：组合索引的使用遵循最左前缀原则。这意味着在查询条件中，只有从索引的最左侧列开始使用，索引才能发挥作用。
例如，创建了一个组合索引 (col1, col2, col3) ，以下查询可以使用索引：

WHERE col1 = value
WHERE col1 = value AND col2 = value
WHERE col1 = value AND col2 = value AND col3 = value
而以下查询不能使用索引：

WHERE col2 = value （跳过了最左侧的 col1 ）
WHERE col3 = value （跳过了 col1 和 col2 ）

范围查询的影响：在组合索引中，如果在前面的列进行精确匹配，而在后续的列进行范围查询，那么索引仍然可以用于前面精确匹配的部分。
例如，对于索引 (col1, col2, col3) ，查询 WHERE col1 = value AND col2 > value 可以使用 col1 列的索引，但 col2 列之后的部分无法使用索引。

索引顺序的重要性：组合索引中列的顺序应该根据查询的频繁程度和选择性来确定。通常，将选择性更高（即值的分布更分散）和更频繁用于查询条件的列放在索引的左侧。
避免不必要的索引创建：如果某些查询很少使用或者通过其他方式可以更有效地解决，就不需要为其创建组合索引，以免增加维护成本。

总之，合理设计和使用组合索引可以提高数据库查询的性能，但需要充分考虑业务的查询模式和数据特点。