Mysql--重点篇--索引（索引分类，Hash和B-tree索引，聚簇和非聚簇索引，回表查询，覆盖索引，索引工作原理，索引失效，索引创建原则等）

索引是数据库中用于加速查询操作的重要机制。通过索引，MySQL可以快速定位到满足查询条件的数据行，而不需要扫描整个表。合理的索引设计可以显著提高查询性能，但不合理的索引可能会导致性能下降和磁盘空间浪费。因此，理解索引的工作原理、类型以及如何优化索引非常重要。

一、索引概述

索引是数据库中的一种数据结构，用于加速对表中数据的检索。它类似于书籍的目录，通过索引可以快速找到特定的数据行，而不需要逐行扫描整个表。索引通常存储在磁盘上，并与表的数据分开管理。

索引常见分类：

• 按照数据结构分类：HASH索引，B+Tree索引，Full-text索引、R-Tree索引。
• 按照规功能分类：主键索引，唯一索引，普通索引，复合索引，全文索引 ，空间索引。
• 按照存储形式分类：聚簇索引，非聚簇索引。
• 按照是否建立在主键上分类：主键索引，辅助索引。

索引的优点：

• 加速查询：通过索引，MySQL可以快速定位到满足查询条件的数据行，减少I/O操作，提升查询性能。
• 加速排序和分组：索引可以帮助MySQL快速完成ORDER BY和GROUP BY操作，减少排序和分组的时间开销。
• 加速连接操作：在多表连接查询中，索引可以加速表之间的匹配过程，减少连接操作的时间。

索引的缺点：

• 增加插入、更新和删除操作的开销：每次修改表中的数据时，MySQL都需要同步更新相关的索引，增加了写操作的开销。
• 占用额外的磁盘空间：索引本身需要占用额外的磁盘空间，尤其是当表中有大量数据时，索引的大小可能会非常大。

二、索引分类

1、按照数据结构分类

（1）、HASH索引

底层原理:

• 基于哈希函数构建：HASH索引通过将索引键值传递给一个哈希函数，生成一个固定的哈希值。这个哈希值用于快速定位到数据的位置。
• 哈希表结构：HASH索引内部使用哈希表（Hash Table）来存储数据。哈希表的每个槽位（bucket）存储一个或多个具有相同哈希值的记录。
• 等值查询高效：Hash索引只能用于对等比较(如=，in)，HASH索引可以通过哈希函数直接计算出哈希值，并快速定位到对应的槽位，查找速度非常快。
• 不支持范围查询：由于哈希函数的特性，HASH索引无法支持范围查询（如>, <, BETWEEN等），因为哈希值没有顺序关系。
• 不适合频繁更新的场景：当插入、删除或更新数据时，哈希表可能会发生冲突（即多个键值映射到同一个槽位），导致性能下降。因此，HASH索引更适合读多写少的场景。
• 存储引擎局限性：在MySQL中，支持Hash索引的是Memory存储引擎。而InnoDB中具有自适应Hash索引。Hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

自适应Hash索引（Adaptive Hash Index）的工作原理：

• InnoDB 会根据查询模式自动为频繁访问的B+Tree索引节点创建哈希索引。
• 这些哈希索引是临时的，只存在于内存中，不会持久化到磁盘。
• 当InnoDB发现某个B+Tree索引节点被频繁访问时，它会为该节点生成一个哈希索引，以加速等值查询。

适用场景：

• 精确匹配的等值查询：例如，查找特定的用户ID或订单号。
• 内存表（Memory Engine）和NDB Cluster存储引擎中使用。

示例:
sql：
– 创建一个内存表，并为 id 列创建 HASH 索引

CREATE TABLE hash_table (id INT PRIMARY KEY,name VARCHAR(50),KEY USING HASH (id)
) ENGINE=MEMORY;

– 插入一些数据

INSERT INTO hash_table (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

– 执行等值查询，HASH 索引会加速查询

SELECT * FROM hash_table WHERE id = 2;

Hash索引原理图：
创建Hash索引后，如name列。新增记录时会将name的值经过hash函数处理获取哈希值，在根据哈希值找到哈希表上的槽位，将数据存储进去。哈希表的每一个槽位都是一条链表，当哈希冲突时，会将数据追加到链表的尾部。

优点：

• 等值查询非常快，时间复杂度接近O(1)。
• 适合读多写少的场景。

缺点：

• 不支持范围查询、排序或分组操作。
• 不适合频繁更新的场景，因为哈希冲突会导致性能下降。

（2）、B+Tree索引

1、什么是B-Tree

B-Tree（即B树）是一种多叉平衡查找树，相对二叉树而言，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶)的B-Tree为例，那这个B树每个节点最多存储4个key。

5阶B-Tree示例图：
每个节点最多有4个元素，每个节点最多可以分5个叉出来。

B-tree特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

2、B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的B+tree为例。

B+Tree示例图：
所有数据都存储在叶子节点上，所有叶子节点组成单向链表。

B+tree特点：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

3、Mysql中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Mysql中的B+Tree示例图：

4、底层原理

• 基于B+Tree数据结构：B+Tree是一种多路平衡树，广泛用于数据库索引。它的特点是所有数据都存储在叶子节点中，而非叶子节点只存储索引键值和指向子节点的指针。
• 按顺序存储：B+Tree的叶子节点按照索引键值的顺序链在一起，因此可以高效地进行范围查询、排序和分组操作。
• 高度平衡：B+Tree保证了树的高度较低，通常只需要几次磁盘I/O操作即可找到目标数据，因此查询效率较高。
• 支持多种查询类型：B+Tree索引不仅支持等值查询，还支持范围查询（如>, <, BETWEEN）、排序（ORDER BY）和分组（GROUP BY）。

5、适用场景

• 等值查询：例如，查找特定的用户ID或订单号。
• 范围查询：例如，查找某个时间段内的订单。
• 排序和分组查询：例如，按日期排序或按部门分组统计。
• InnoDB和MyISAM存储引擎中使用。

6、使用示例

– 创建一个InnoDB表，并为name列创建B+Tree索引

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_name (name)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行等值查询，B+Tree 索引会加速查询

SELECT * FROM employees WHERE name = 'Alice';

– 执行排序查询，B+Tree 索引会加速查询

SELECT * FROM employees ORDER BY name;

7、优缺点

优点：

• 支持多种查询类型，包括等值查询、范围查询、排序和分组。
• 数据按顺序存储，适合范围查询。
• 高度平衡，查询效率高。

缺点：

• 插入、删除操作可能导致页分裂，影响写入性能。
• 对于等值查询，B+Tree索引的性能略逊于HASH索引。

（3）、Full-text索引

底层原理：

• 基于倒排索引（Inverted Index）：Full-Text索引使用倒排索引技术，将文本中的每个单词映射到包含该单词的文档（或行）。倒排索引允许快速查找包含特定单词的文档。
• 支持自然语言模式、布尔模式和查询扩展模式：
  • 自然语言模式：根据词频和文档频率计算相关性得分，返回最相关的文档。
  • 布尔模式：支持逻辑运算符（如AND、OR、NOT），允许精确控制查询条件。
  • 查询扩展模式：自动扩展查询词，返回与查询词相关的其他词的文档。
• 分词器：Full-Text索引使用分词器将文本分割成单词。MySQL提供了多种分词器，支持不同语言的文本处理。

正向索引和倒排索引解释：

• 正向索引（Forward Index）：在传统的索引中，索引是基于文档ID或记录ID来存储文档的内容或元数据。例如，对于一个包含多个文档的集合，正向索引会为每个文档分配一个唯一的ID，并存储该文档的所有内容或元数据。这种索引结构适合快速查找某个特定文档，但不适合快速查找包含特定词汇的文档。

• 倒排索引（Inverted Index）：与正向索引不同，倒排索引是基于词项（Term）来构建的。它将每个词项映射到包含该词项的文档列表。换句话说，倒排索引记录了每个词项出现在哪些文档中，而不是记录每个文档包含哪些词项。

• 词项（Term）：文本中的单词或其他标识符（如短语、标签等）。在构建倒排索引时，通常会对文本进行分词处理，将文本分割成一个个词项。

• 文档ID（DocID）：每个文档都有一个唯一的标识符，称为文档ID。倒排索引记录了每个词项对应的文档ID列表。

适用场景：

• 大文本字段的全文搜索：例如，文章、评论、日志等文本内容的搜索。
• 自然语言处理：支持复杂的文本查询，如模糊匹配、同义词扩展等。
• InnoDB和MyISAM存储引擎中使用。

使用示例：
– 创建一个带有 Full-Text 索引的表

CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(255),content TEXT,FULLTEXT (content)
) ENGINE=InnoDB;

– 插入一些文章

INSERT INTO articles (id, title, content) 
VALUES 
(1, 'Introduction to Databases', 'Databases are used to store and manage data.'),
(2, 'Understanding SQL', 'SQL is a language for interacting with databases.');

– 使用自然语言模式进行全文搜索

SELECT * FROM articles WHERE MATCH (content) AGAINST ('databases' IN NATURAL LANGUAGE MODE);

– 使用布尔模式进行全文搜索

SELECT * FROM articles WHERE MATCH (content) AGAINST ('+SQL +databases' IN BOOLEAN MODE);

运行结果：

优点：

• 支持复杂的文本查询，如模糊匹配、同义词扩展等。
• 适合大文本字段的全文搜索。
• 提供多种查询模式，灵活性强。

缺点：

• 不支持范围查询、排序或分组操作。
• 全文索引的维护成本较高，尤其是在频繁更新的场景下。

（4）、R-Tree索引

底层原理：

• 基于R-Tree数据结构：R-Tree是一种专门用于空间数据的索引结构，适用于多维数据（如地理坐标、矩形区域等）。R-Tree的每个节点表示一个矩形区域，包含多个子节点或数据点。
• 最小边界矩形（MBR）：R-Tree使用最小边界矩形（Minimum Bounding Rectangle, MBR）来表示空间对象。MBR是一个包围空间对象的最小矩形，用于快速过滤不符合条件的空间对象。
• 支持空间查询：R-Tree索引支持多种空间查询，如包含查询（MBRContains）、相交查询（MBRIntersects）、最近邻查询（ST_Distance）等。
• 动态平衡：R-Tree通过动态调整节点的分布，确保树的高度较低，查询效率较高。

适用场景：

• 地理信息系统（GIS）：例如，查找某个地理位置附近的餐馆、酒店等。
• 空间数据的索引和查询：例如，查找与某个矩形区域相交的对象。
• MyISAM 存储引擎中使用。

使用示例：
– 创建一个带有 R-Tree 索引的表

CREATE TABLE locations (id INT PRIMARY KEY,name VARCHAR(50),geom GEOMETRY,SPATIAL INDEX (geom)
) ENGINE=MyISAM;

– 插入一些地理位置数据

INSERT INTO locations (id, name, geom) 
VALUES 
(1, 'Central Park', ST_GeomFromText('POINT(40.785091 -73.968285)')),
(2, 'Empire State Building', ST_GeomFromText('POINT(40.748817 -73.985428)'));

– 查找与某个矩形区域相交的地点

SET @rect = ST_GeomFromText('POLYGON((40.78 -73.97, 40.79 -73.97, 40.79 -73.96, 40.78 -73.96, 40.78 -73.97))');
SELECT  FROM locations WHERE MBRIntersects(geom, @rect);

优点：

• 专为空间数据设计，支持多种空间查询。
• 查询效率高，尤其是对于大范围的空间数据。
• 动态平衡，能够适应频繁插入和删除操作。

缺点：

• 只适用于空间数据，不支持普通的数据类型。
• 维护成本较高，尤其是在频繁更新的场景下。

2、常规功能分类

（1）、主键索引

底层原理：

• 每个表只能有一个主键：主键是表中唯一的标识符，确保每一行数据的唯一性。
• 主键默认是聚簇索引（对于InnoDB的表），决定了数据的物理存储顺序。表的数据行按照主键的顺序存储。
• 不允许NULL值：主键列不能为空，必须有值。
• 支持等值查询、范围查询、排序和分组操作：由于主键是B+Tree索引，它支持多种查询类型。

适用场景：

• 作为表的唯一标识符：例如，用户ID、订单ID等。
• 频繁的等值查询：例如，查找特定的用户或订单。
• 范围查询、排序和分组查询：由于主键是B+Tree索引，它也适用于这些查询类型。

使用示例：
– 创建一个带有主键索引的表

CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(100)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', 'alice@example.com'), (2, 'Bob', 'bob@example.com');

– 执行等值查询，主键索引会加速查询

SELECT * FROM users WHERE id = 1;

优点：

• 主键索引是最高效的索引之一，因为它直接决定了数据的物理存储顺序。
• 支持多种查询类型，包括等值查询、范围查询、排序和分组。

缺点：

• 每个表只能有一个主键。
• 插入新行时可能引发页分裂，影响写入性能。

（2）、唯一索引

底层原理：

• 确保索引列中的值是唯一的：唯一索引要求索引列中的值不能重复。如果插入重复的值，MySQL会抛出错误。
• 可以是单列索引或多列组合索引：唯一索引可以应用于单个列或多个列的组合。
• 允许NULL值：与主键不同，唯一索引允许NULL值，但每个列中只能有一个NULL值。
• 基于B+Tree索引：唯一索引通常使用B+Tree结构，支持等值查询、范围查询、排序和分组操作。

适用场景：

• 确保某一列或多列的值唯一：例如，电子邮件地址、用户名等需要唯一性的字段。
• 频繁的等值查询：唯一索引可以加速等值查询，因为它是B+Tree索引。

使用示例：
– 创建一个带有唯一索引的表

CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', 'alice@example.com'), (2, 'Bob', 'bob@example.com');

– 尝试插入重复的电子邮件地址，会抛出错误

INSERT INTO users (id, name, email) VALUES (3, 'Charlie', 'alice@example.com');  -- 错误！

– 执行等值查询，唯一索引会加速查询

SELECT * FROM users WHERE email = 'alice@example.com';

优点：

• 确保列的唯一性，避免数据重复。
• 支持多种查询类型，包括等值查询、范围查询、排序和分组。

缺点：

• 插入新行时需要检查唯一性，可能会增加写入开销。
• 允许NULL值，但每个列中只能有一个NULL值。

（3）、普通索引

底层原理：

• 普通的B+Tree索引：普通索引是基于B+Tree结构的索引，支持等值查询、范围查询、排序和分组操作。
• 可以创建多个普通索引：一个表可以有多个普通索引，适用于不同的查询需求。
• 不保证唯一性：普通索引不要求列中的值是唯一的，允许重复值。

适用场景：

• 加速等值查询、范围查询、排序和分组操作：普通索引可以加速各种查询类型，尤其是当查询条件涉及多个列时。
• 多列联合查询：普通索引可以用于多列联合查询，尤其是在查询条件涉及多个列时。

使用示例：
– 创建一个带有普通索引的表

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_name (name)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行等值查询，普通索引会加速查询

SELECT * FROM employees WHERE name = 'Alice';

– 执行范围查询，普通索引会加速查询

SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

优点：

• 支持多种查询类型，包括等值查询、范围查询、排序和分组。
• 可以创建多个普通索引，适用于不同的查询需求。

缺点：

• 不保证唯一性，允许重复值。
• 多个普通索引会增加写入开销和磁盘空间占用。

（4）、复合索引

底层原理：

• 多列组合索引：复合索引是在多个列上创建的索引，适用于多列联合查询。复合索引的叶子节点存储多个列的组合值。
• 遵循最左前缀原则：复合索引的查询条件必须从索引的最左列开始匹配。例如，对于(col1, col2, col3)的复合索引，查询条件可以是WHERE col1 = ?或WHERE col1 = ? AND col2 = ?，但不能是WHERE col2 = ?或WHERE col3 = ?。
• 减少索引数量：通过创建复合索引，可以减少单独创建多个索引的开销。

适用场景：

• 多列联合查询：当查询条件涉及多个列时，复合索引可以显著提高查询性能。
• 覆盖索引：如果查询中涉及的所有列都在复合索引中，MySQL可以直接从索引中获取所需的数据，而不需要回表查询。

使用示例：
– 创建一个带有复合索引的表

CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_department_name_age (department, name, age)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行多列联合查询，复合索引会加速查询

SELECT name, age FROM employees WHERE department = 'Sales' AND name = 'Charlie';

– 使用覆盖索引，避免回表查询

SELECT name, age FROM employees WHERE department = 'Sales';

优点：

• 适用于多列联合查询，减少单独创建多个索引的开销。
• 可以实现覆盖索引，避免回表查询，提升查询性能。

缺点：

• 需要遵循最左前缀原则，查询条件必须从索引的最左列开始匹配。
• 复合索引的维护成本较高，尤其是在频繁更新的场景下。

（5）、全文索引

底层原理：

• 基于倒排索引（Inverted Index）：全文索引使用倒排索引技术，将文本中的每个单词映射到包含该单词的文档（或行）。倒排索引允许快速查找包含特定单词的文档。
• 支持自然语言模式、布尔模式和查询扩展模式：
  • 自然语言模式：根据词频和文档频率计算相关性得分，返回最相关的文档。
  • 布尔模式：支持逻辑运算符（如AND、OR、NOT），允许精确控制查询条件。
  • 查询扩展模式：自动扩展查询词，返回与查询词相关的其他词的文档。
• 分词器：全文索引使用分词器将文本分割成单词。MySQL 提供了多种分词器，支持不同语言的文本处理。

适用场景：

• 大文本字段的全文搜索：例如，文章、评论、日志等文本内容的搜索。
• 自然语言处理：支持复杂的文本查询，如模糊匹配、同义词扩展等。

使用示例：
– 创建一个带有全文索引的表

CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(255),content TEXT,FULLTEXT (content)
) ENGINE=InnoDB;

– 插入一些文章

INSERT INTO articles (id, title, content) 
VALUES 
(1, 'Introduction to Databases', 'Databases are used to store and manage data.'),
(2, 'Understanding SQL', 'SQL is a language for interacting with databases.');

– 使用自然语言模式进行全文搜索

SELECT * FROM articles WHERE MATCH (content) AGAINST ('databases' IN NATURAL LANGUAGE MODE);

– 使用布尔模式进行全文搜索

SELECT * FROM articles WHERE MATCH (content) AGAINST ('+SQL +databases' IN BOOLEAN MODE);

优点：

• 支持复杂的文本查询，如模糊匹配、同义词扩展等。
• 适合大文本字段的全文搜索。
• 提供多种查询模式，灵活性强。

缺点：

• 不支持范围查询、排序或分组操作。
• 全文索引的维护成本较高，尤其是在频繁更新的场景下。

（6）、空间索引

底层原理:

• 基于R-Tree数据结构：R-Tree是一种专门用于空间数据的索引结构，适用于多维数据（如地理坐标、矩形区域等）。R-Tree的每个节点表示一个矩形区域，包含多个子节点或数据点。
• 最小边界矩形（MBR）：R-Tree使用最小边界矩形（Minimum Bounding Rectangle, MBR）来表示空间对象。MBR是一个包围空间对象的最小矩形，用于快速过滤不符合条件的空间对象。
• 支持空间查询：R-Tree索引支持多种空间查询，如包含查询（MBRContains）、相交查询（MBRIntersects）、最近邻查询（ST_Distance）等。
• 动态平衡：R-Tree通过动态调整节点的分布，确保树的高度较低，查询效率较高。

适用场景:

• 地理信息系统（GIS）：例如，查找某个地理位置附近的餐馆、酒店等。
• 空间数据的索引和查询：例如，查找与某个矩形区域相交的对象。

使用示例:
– 创建一个带有空间索引的表

CREATE TABLE locations (id INT PRIMARY KEY,name VARCHAR(50),geom GEOMETRY,SPATIAL INDEX (geom)
) ENGINE=MyISAM;

– 插入一些地理位置数据

INSERT INTO locations (id, name, geom) 
VALUES 
(1, 'Central Park', ST_GeomFromText('POINT(40.785091 -73.968285)')),
(2, 'Empire State Building', ST_GeomFromText('POINT(40.748817 -73.985428)'));

– 查找与某个矩形区域相交的地点

SET @rect = ST_GeomFromText('POLYGON((40.78 -73.97, 40.79 -73.97, 40.79 -73.96, 40.78 -73.96, 40.78 -73.97))');
SELECT * FROM locations WHERE MBRIntersects(geom, @rect);

优点：

• 专为空间数据设计，支持多种空间查询。
• 查询效率高，尤其是对于大范围的空间数据。
• 动态平衡，能够适应频繁插入和删除操作。

缺点：

• 只适用于空间数据，不支持普通的数据类型。
• 维护成本较高，尤其是在频繁更新的场景下。

3、按照存储形式分类

（1）、聚簇索引

底层原理:

• 决定数据的物理存储顺序：聚簇索引决定了表中数据行的物理存储顺序。表的数据行按照聚簇索引的键值顺序存储。
• 每个表只能有一个聚簇索引：因为数据行只能按一种顺序存储，所以一个表只能有一个聚簇索引。
• 叶子节点存储完整的数据行：对于InnoDB表，默认情况下，主键是聚簇索引。如果表没有显式定义主键，InnoDB会自动选择一个唯一的非空索引作为聚簇索引；如果没有合适的索引，InnoDB 会创建一个隐藏的聚簇索引。聚簇索引的叶子节点存储完整的数据行，而不是像非聚簇索引那样只存储指向行的指针。
• 支持范围查询：由于数据是按顺序存储的，聚簇索引非常适合范围查询（如BETWEEN、>、<），因为可以快速定位到连续的数据块。
• 插入新行时可能引发页分裂：当插入新行时，如果新行的键值位于现有数据中间，MySQL可能需要将页面分裂成两个页面，以保持数据的顺序性，这会影响写入性能。

适用场景:

• 频繁的范围查询：聚簇索引非常适合范围查询，因为它按顺序存储数据。
• 等值查询：聚簇索引也适用于等值查询，尤其是基于主键的查询。
• 排序和分组查询：由于数据是按顺序存储的，聚簇索引也适用于排序和分组操作。

使用示例:
– 创建一个带有聚簇索引的表（InnoDB 默认为主键聚簇索引）

CREATE TABLE employees (id INT PRIMARY KEY,  -- 主键默认是聚簇索引name VARCHAR(50),age INT,department VARCHAR(50)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行范围查询，聚簇索引会加速查询

SELECT * FROM employees WHERE id BETWEEN 1 AND 2;

– 执行排序查询，聚簇索引会加速查询

SELECT * FROM employees ORDER BY id;

优点：

• 支持范围查询、排序和分组操作，查询效率高。
• 数据按顺序存储，适合大范围的顺序读取。

缺点：

• 每个表只能有一个聚簇索引。
• 插入新行时可能引发页分裂，影响写入性能。
• 如果表没有显式定义主键，InnoDB会自动创建一个隐藏的聚簇索引，这可能会浪费空间。

（2）、非聚簇索引

特点：

• 不决定数据的物理存储顺序：非聚簇索引不改变表中数据行的物理存储顺序。表的数据行仍然按照聚簇索引的顺序存储。
• 叶子节点存储索引键值和指向实际数据行的指针：非聚簇索引的叶子节点只存储索引键值和指向实际数据行的指针（通常是聚簇索引键或行ID）。因此，非聚簇索引的叶子节点不包含完整的数据行。
• 支持多种查询类型：非聚簇索引支持等值查询、范围查询、排序和分组操作，但通常需要回表查询（即通过指针回到聚簇索引中查找完整的数据行）。
• 可以有多个非聚簇索引：一个表可以有多个非聚簇索引，适用于不同的查询需求。

适用场景：

• 多列联合查询：非聚簇索引适用于多列联合查询，尤其是在查询条件涉及多个列时。
• 频繁读取但不经常更新的查询：非聚簇索引适合读多写少的场景，因为插入、删除或更新操作不会影响数据的物理存储顺序。
• 避免频繁的页分裂：与聚簇索引不同，非聚簇索引不会引发页分裂，因此写入性能较好。

使用示例：
– 创建一个带有非聚簇索引的表

CREATE TABLE employees (id INT PRIMARY KEY,  -- 主键是聚簇索引name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_department (department)  -- 非聚簇索引
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行等值查询，非聚簇索引会加速查询

SELECT * FROM employees WHERE department = 'Sales';

注意：
这里会发生回表查询，因为非聚簇索引只存储 department 和指向数据行的指针。要想获取*的所有字段，需要根据非聚簇索引的主键id，再次查询聚簇索引才能获取整个行的数据。

优点：

• 支持多种查询类型，包括等值查询、范围查询、排序和分组。
• 不影响数据的物理存储顺序，避免频繁的页分裂。
• 可以有多个非聚簇索引，适用于不同的查询需求。

缺点：

• 通常需要回表查询，即通过索引中的指针回到聚簇索引中查找完整的数据行，增加了I/O操作。
• 写入性能较好，但读取性能略逊于聚簇索引。

扩展：回表查询（Secondary Lookup）

回表查询是指当查询中涉及的列不在非聚簇索引中时，MySQL需要通过非聚簇索引中的指针回到聚簇索引中查找完整的数据行。这个额外的查找过程称为回表查询。

为什么会发生回表查询？
非聚簇索引的叶子节点只存储索引键值和指向实际数据行的指针，而不存储完整的数据行。因此，当查询中涉及的列不在非聚簇索引中时，MySQL必须通过指针回到聚簇索引中查找完整的数据行。这个过程会增加I/O操作，降低查询性能。

回表查询示例：

SELECT name, age FROM employees WHERE name = 'John';

在这个例子中，name列上有非聚簇索引idx_name，但查询中还涉及了age列，而age列不在idx_name中。因此，MySQL需要通过idx_name找到name = 'John’的行，然后通过指针回到聚簇索引中查找age列的值。这个过程就是回表查询。

如何避免回表查询？
1、使用覆盖索引：如果查询中涉及的所有列都在非聚簇索引中，MySQL 可以直接从索引中获取数据，而不需要回表查询。这种情况下，非聚簇索引被称为 覆盖索引，可以显著提高查询性能。
改进示例：

 CREATE INDEX idx_name_age ON employees (name, age);SELECT name, age FROM employees WHERE name = 'John';

2、减少查询列的数量：如果查询中只需要少数几列，尽量只选择这些列，而不是使用SELECT *。这样可以减少回表查询的次数。

 SELECT name, age FROM employees WHERE name = 'John';  -- 只选择需要的列

3、使用聚簇索引：如果查询中涉及的列是聚簇索引的键值（如主键id），MySQL可以直接从聚簇索引中获取数据，而不需要回表查询。

（3）、覆盖索引

覆盖索引是一种优化技术，并不是实质意义上的索引。
覆盖索引指的是查询中涉及的所有列都在同一个索引中，MySQL可以直接从索引中获取所需的数据，而不需要回表查询（即不需要通过索引中的指针回到聚簇索引或数据表中查找完整的数据行）。这种情况下，索引不仅用于加速查询条件的匹配，还包含了查询结果所需的全部数据，因此可以显著减少I/O操作，提升查询性能。

底层原理：

• 查询中涉及的所有列都在同一个索引中：覆盖索引是指查询中涉及的所有列都包含在同一个索引中。MySQL可以直接从索引中获取所需的数据，而不需要回表查询。
• 减少I/O操作：由于不需要回表查询，覆盖索引可以显著减少I/O操作，提升查询性能。
• 通常通过复合索引实现：覆盖索引通常是通过复合索引实现的，即将查询中涉及的所有列都包含在同一个复合索引中。
• 适用于只读查询：覆盖索引最适合只读查询，因为它减少了I/O操作，但对写入性能没有明显影响。

适用场景：

• 查询中涉及的列较少：覆盖索引最适合查询中涉及的列较少且固定的场景，例如查询用户的基本信息（如name、age、department）。
• 频繁读取但不经常更新的查询：覆盖索引适合读多写少的场景，因为它减少了I/O操作，提升了读取性能。
• 避免回表查询：通过覆盖索引，可以避免回表查询，减少I/O操作，提升查询性能。

使用示例：
– 创建一个带有覆盖索引的表

CREATE TABLE employees (id INT PRIMARY KEY,  -- 主键是聚簇索引name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_department_name_age (department, name, age)  -- 覆盖索引
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行查询，覆盖索引会加速查询

SELECT name, age FROM employees WHERE department = 'Sales';

注意：
这里不会发生回表查询，因为所有需要的列（name, age）都在覆盖索引中

优点：

• 显著减少I/O操作，提升查询性能。
• 避免回表查询，减少磁盘I/O操作。
• 适用于只读查询，尤其适合查询中涉及的列较少且固定的场景。

缺点：

• 覆盖索引通常是复合索引，可能会增加索引的大小，占用更多的磁盘空间。
• 对写入性能没有明显影响，但索引维护成本较高，尤其是在频繁更新的场景下。

4、是否建立在主键上分类

（1）、主键索引

特点：

• 唯一标识符：主键索引是表中唯一的标识符，确保每一行数据的唯一性。每个表只能有一个主键。
• 不允许NULL值：主键列不能为空，必须有值。
• 默认是聚簇索引（对于InnoDB表）：主键索引决定了数据的物理存储顺序。InnoDB引擎将表的数据行按照主键的顺序存储，因此主键索引是聚簇索引。
• 支持多种查询类型：主键索引支持等值查询、范围查询、排序和分组操作，因为它是基于 B+Tree结构的索引。

底层原理：

• 聚簇索引：主键索引是聚簇索引，这意味着表的数据行按照主键的顺序存储。聚簇索引的叶子节点存储完整的数据行，而不是像非聚簇索引那样只存储指针。
• 插入新行时可能引发页分裂：当插入新行时，如果新行的键值位于现有数据中间，MySQL可能需要将页面分裂成两个页面，以保持数据的顺序性，这会影响写入性能。

适用场景：

• 作为表的唯一标识符：例如，用户ID、订单ID等。
• 频繁的等值查询：主键索引非常适合基于主键的等值查询，因为它是聚簇索引，查询效率非常高。
• 范围查询、排序和分组查询：由于主键索引是聚簇索引，它也适用于这些查询类型，尤其是范围查询。

使用示例：
– 创建一个带有主键索引的表

CREATE TABLE users (id INT PRIMARY KEY,  -- 主键索引，默认是聚簇索引name VARCHAR(50),email VARCHAR(100)
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO users (id, name, email) 
VALUES (1, 'Alice', 'alice@example.com'), (2, 'Bob', 'bob@example.com');

– 执行等值查询，主键索引会加速查询

SELECT * FROM users WHERE id = 1;

– 执行范围查询，主键索引会加速查询

SELECT * FROM users WHERE id BETWEEN 1 AND 2;

– 执行排序查询，主键索引会加速查询

SELECT * FROM users ORDER BY id;

优点：

• 主键索引是最高效的索引之一，因为它直接决定了数据的物理存储顺序。
• 支持多种查询类型，包括等值查询、范围查询、排序和分组。

缺点：

• 每个表只能有一个主键。
• 插入新行时可能引发页分裂，影响写入性能。
• 如果表没有显式定义主键，InnoDB会自动创建一个隐藏的聚簇索引，这可能会浪费空间。

（2）、辅助索引

特点：

• 不决定数据的物理存储顺序：辅助索引不改变表中数据行的物理存储顺序。表的数据行仍然按照聚簇索引（通常是主键）的顺序存储。
• 可以有多个辅助索引：一个表可以有多个辅助索引，适用于不同的查询需求。
• 叶子节点存储索引键值和指向实际数据行的指针：辅助索引的叶子节点只存储索引键值和指向实际数据行的指针（通常是聚簇索引键或 ID）。因此，辅助索引的叶子节点不包含完整的数据行。
• 支持多种查询类型：辅助索引支持等值查询、范围查询、排序和分组操作，但通常需要回表查询（即通过指针回到聚簇索引中查找完整的数据行）。

底层原理：

• 非聚簇索引：辅助索引是基于B+Tree结构的非聚簇索引。它的叶子节点只存储索引键值和指向实际数据行的指针，而不像聚簇索引那样存储完整的数据行。
• 回表查询：当查询条件涉及的列不在辅助索引中时，MySQL需要通过索引中的指针回到聚簇索引中查找完整的数据行。这个过程称为回表查询，增加了I/O操作。

适用场景：

• 多列联合查询：辅助索引适用于多列联合查询，尤其是在查询条件涉及多个列时。
• 频繁读取但不经常更新的查询：辅助索引适合读多写少的场景，因为插入、删除或更新操作不会影响数据的物理存储顺序。
• 避免频繁的页分裂：与聚簇索引不同，辅助索引不会引发页分裂，因此写入性能较好。

使用示例：
– 创建一个带有主键索引和辅助索引的表

CREATE TABLE employees (id INT PRIMARY KEY,  -- 主键索引，默认是聚簇索引name VARCHAR(50),age INT,department VARCHAR(50),INDEX idx_department (department)  -- 辅助索引
) ENGINE=InnoDB;

– 插入一些数据

INSERT INTO employees (id, name, age, department) 
VALUES (1, 'Alice', 30, 'HR'), (2, 'Bob', 28, 'Engineering'), (3, 'Charlie', 35, 'Sales');

– 执行等值查询，辅助索引会加速查询

SELECT * FROM employees WHERE department = 'Sales';

注意：
这里会发生回表查询，因为辅助索引只存储 department 和指向数据行的指针

三、索引的工作原理

最常见的索引类型是B+Tree索引。

Mysql中B+Tree索引存储结构示例图：

特点：

• 树状结构：B+Tree索引由多层节点组成，最底层的叶子节点存储实际的数据指针，其他层的节点只存储索引键值。
• 有序性：B+Tree索引中的数据是按顺序存储的，因此非常适合范围查询。
• 叶子节点链接：B+Tree的叶子节点之间有双向链表连接，方便进行顺序扫描。

索引的查找过程：
假设我们有一个B+Tree索引，查找过程如下：
1、从根节点开始：MySQL从B+Tree的根节点开始查找，根据查询条件逐步向下遍历树。
2、逐层查找：在每一层节点中，MySQL通过二分查找算法快速定位到目标子节点。
3、到达叶子节点：当到达叶子节点时，MySQL找到满足查询条件的数据指针，并通过指针访问实际的数据行。
4、顺序扫描：如果查询条件是范围查询，MySQL可以通过叶子节点之间的链表进行顺序扫描，快速获取所有满足条件的数据行。

索引的维护：
每当表中的数据发生插入、更新或删除操作时，MySQL需要同步更新相关的索引。
索引的维护过程如下：

• 插入数据：当插入新数据时，MySQL会将新数据插入到B+Tree的适当位置，并调整树的结构以保持平衡。
• 更新数据：当更新数据时，MySQL会先找到对应的索引节点，然后更新该节点中的键值或数据指针。
• 删除数据：当删除数据时，MySQL会从B+Tree中移除相应的节点，并调整树的结构以保持平衡。

四、索引创建和管理

1、创建索引

可以在创建表时或表创建后添加索引。

示例：

• 创建普通索引：

  CREATE INDEX idx_column ON table (column);

• 创建唯一索引：

  CREATE UNIQUE INDEX idx_column ON table (column);

• 创建组合索引：

  CREATE INDEX idx_columns ON table (col1, col2, col3);

• 创建全文索引：

  CREATE FULLTEXT INDEX idx_content ON table (content);

• 创建空间索引：

  CREATE SPATIAL INDEX idx_location ON table (location);

• 在表创建时定义索引：

  CREATE TABLE table (id INT PRIMARY KEY,name VARCHAR(100),age INT,INDEX idx_name (name));

2、删除索引

可以使用DROP INDEX语句删除索引：

ALTER TABLE table DROP INDEX idx_column;

3、查看索引

可以使用SHOW INDEX语句查看表中的索引信息：

SHOW INDEX FROM table;

4、分析索引

可以使用EXPLAIN语句分析查询是否使用了索引：

EXPLAIN SELECT * FROM table WHERE column = 'value';

EXPLAIN语句会返回查询的执行计划，其中key列显示了查询使用的索引。如果key列为空，说明查询没有使用索引。

五、索引优化策略

为了提高查询性能，合理设计和优化索引非常重要。

1、选择合适的索引类型

• B+Tree索引：适用于大多数查询场景，尤其是等值查询、范围查询、排序和分组操作。
• 哈希索引：适用于等值查询，但在范围查询、排序和分组操作中表现不佳。
• 全文索引：适用于大文本字段的全文搜索。
• 空间索引：适用于地理空间数据的查询。

2、避免过度索引

• 索引越多越好吗？：并不是。过多的索引会增加写操作的开销，并占用额外的磁盘空间。因此，应该根据查询需求选择必要的索引，避免创建不必要的索引。
• 定期审查索引：随着业务的变化，某些索引可能不再有用。定期审查索引，删除不再需要的索引，可以提高写性能并节省磁盘空间。

3、使用组合索引

• 覆盖查询：如果查询中涉及的所有列都在组合索引中，MySQL可以直接从索引中获取数据，而不需要回表查询，从而提高查询性能。
• 最左前缀原则：组合索引遵循最左前缀原则，查询条件必须从索引的最左列开始匹配。因此，在设计组合索引时，应将最常用的查询条件放在最左边。
  最左前缀原则：
  组合索引遵循最左前缀原则，即查询条件必须从索引的最左列开始匹配。
  示例如，对于组合索引(col1, col2, col3)：
• 查询条件 WHERE col1 = ? 可以使用该索引。
• 查询条件 WHERE col1 = ? AND col2 = ? 也可以使用该索引。
• 查询条件 WHERE col1 = ? AND col2 = ? AND col3 = ? 完全使用该索引。
• 查询条件 WHERE col2 = ? 或 WHERE col3 = ? 无法使用该索引。

4、避免在索引列上使用函数

• 函数影响索引使用：如果在查询条件中对索引列使用函数，MySQL无法直接使用索引进行查找。例如，SELECT * FROM table WHERE YEAR(date_col) = 2023; 会导致MySQL全表扫描，而不是使用索引。
• 解决方案：尽量避免在索引列上使用函数。如果必须使用函数，可以考虑创建新的列（Generated Column）并为其创建索引。
  示例：

  ALTER TABLE table ADD COLUMN year_col INT GENERATED ALWAYS AS (YEAR(date_col)) STORED;CREATE INDEX idx_year_col ON table (year_col);

5、避免在索引列上使用LIKE通配符

• LIKE通配符的影响：如果查询条件中使用了LIKE通配符，并且通配符出现在查询条件的开头（如LIKE ‘%abc’），MySQL无法使用索引进行查找，而是进行全表扫描。
• 解决方案：尽量避免在索引列上使用LIKE通配符。如果必须使用LIKE，可以考虑使用全文索引或正则表达式索引。

6、使用EXPLAIN分析查询

• EXPLAIN语句：EXPLAIN语句可以帮助你分析查询的执行计划，查看查询是否使用了索引。通过EXPLAIN，你可以发现哪些查询没有使用索引，并进行优化。
  示例：
• 检查key列：EXPLAIN结果中的key列显示了查询使用的索引。如果key列为空，说明查询没有使用索引。
• 检查rows列：EXPLAIN结果中的rows列显示了MySQL预计要扫描的行数。如果rows数量较大，说明查询可能没有使用索引，或者索引的选择不够优化。
• 检查Extra列：EXPLAIN结果中的Extra列提供了额外的信息，例如是否使用了覆盖索引、是否进行了全表扫描等。

7、使用覆盖索引covering index

• 覆盖索引：如果查询中涉及的所有列都在索引中，MySQL可以直接从索引中获取数据，而不需要回表查询。这种情况下，索引被称为覆盖索引。
• 优势：覆盖索引可以显著提高查询性能，因为它减少了I/O操作。为了创建覆盖索引，可以在查询中涉及的所有列上创建组合索引。

8、避免频繁更新索引列

• 更新索引列的影响：每次更新索引列时，MySQL都需要同步更新相关的索引，增加了写操作的开销。因此，应尽量避免频繁更新索引列。
• 解决方案：如果某些列经常被更新，可以考虑将它们从索引中移除，或者使用非聚集索引（如covering index）来减少更新的开销。

9、使用前缀索引

对于较长的字符串列（如VARCHAR、TEXT），可以考虑使用前缀索引。前缀索引只索引列的前N个字符，而不是整个列的内容。这样可以减少索引的大小，提升索引的性能。

• 选择合适的前缀长度：前缀索引的长度应根据实际查询需求选择。通常，前缀长度越长，索引的精度越高，但索引的大小也会增加。因此，应根据查询的频率和精度要求，选择合适的前缀长度。
  示例：
  假设有一个VARCHAR(255)的列title，可以为其创建前缀索引：

 CREATE INDEX idx_title_prefix ON articles (title(50));

10、定期重建索引

随着时间的推移，索引可能会变得碎片化，影响查询性能。定期重建索引可以优化索引的结构，减少碎片化，提升查询性能。

• 使用OPTIMIZE TABLE语句：OPTIMIZE TABLE语句可以重建表并优化其索引。
  例如：

  OPTIMIZE TABLE table_name;

• 使用ALTER TABLE … ENGINE=InnoDB：对于InnoDB表，可以通过ALTER TABLE语句重建表并优化索引。
  例如：

  ALTER TABLE table_name ENGINE=InnoDB;

• 定期维护：建议定期执行索引重建操作，尤其是在高并发写入场景下，定期维护索引可以保持其高效性。

六、索引创建原则

索引是提升数据库查询性能的关键手段之一，但不合理的索引设计可能会导致性能下降、磁盘空间浪费以及写操作的开销增加。因此，遵循一些基本的索引创建原则可以帮助你设计出高效的索引结构，确保系统的高性能和可维护性。

1、根据查询需求创建索引

• 索引应服务于查询：索引的主要目的是加速查询，因此在创建索引时，应该优先考虑那些频繁执行的查询语句。分析应用程序中的常见查询模式，确定哪些列最常用于WHERE、JOIN、ORDER BY和 GROUP BY条件中，并为这些列创建索引。

示例：
– 分析频繁使用的查询

SELECT name, age FROM employees WHERE department = 'Sales';

– 为department列创建索引

CREATE INDEX idx_department ON employees (department);

2、选择合适的索引类型

• 根据查询场景选择索引类型：不同的索引类型适用于不同的查询场景。选择合适的索引类型可以显著提高查询性能。
  常见的索引类型包括：
  • B+Tree索引：适用于等值查询、范围查询、排序和分组操作。
  • 哈希索引：适用于等值查询，但在范围查询、排序和分组操作中表现不佳。
  • 全文索引：适用于大文本字段的全文搜索。
  • 组合索引：适用于多列联合查询，可以减少单独创建多个索引的开销。
  • 空间索引：适用于地理空间数据的查询。

示例：
– 为大文本字段创建全文索引

CREATE FULLTEXT INDEX idx_content ON articles (content);

– 为多列联合查询创建组合索引

CREATE INDEX idx_name_age_salary ON employees(name, age, salary);

3、使用组合索引（Composite Index）

• 组合索引可以减少索引数量：如果多个查询条件经常一起使用，可以考虑创建组合索引。组合索引可以在一个索引中包含多个列，减少单独创建多个索引的开销。

• 遵循最左前缀原则：组合索引遵循最左前缀原则，即查询条件必须从索引的最左列开始匹配。因此，在设计组合索引时，应将最常用的查询条件放在索引的最左边。
  示例：
  – 创建组合索引，将最常用的查询条件放在最左边

CREATE INDEX idx_department_name_age ON employees (department, name, age);

– 查询条件包含 department 和 name，可以使用组合索引

SELECT name, age FROM employees WHERE department = 'Sales' AND name = 'John';

4、避免过度索引

• 索引不是越多越好：虽然索引可以加速查询，但过多的索引会带来以下问题：
  • 增加写操作的开销：每次插入、更新或删除数据时，MySQL都需要同步更新相关的索引，增加了写操作的时间。尤其是在高并发写入场景下，可能导致性能瓶颈。
  • 占用额外的磁盘空间：每个索引都会占用额外的磁盘空间，尤其是在表中有大量数据时，索引的大小可能会非常大。

解决方法：

• 定期审查索引：随着业务的变化，某些索引可能不再有用。定期审查索引，删除不再需要的索引，可以提高写性能并节省磁盘空间。
• 只创建必要的索引：根据实际查询需求创建索引，避免为每个列都创建索引。可以通过EXPLAIN分析查询的执行计划，确定哪些查询确实需要索引支持。

5、避免在索引列上使用函数或表达式

• 函数或表达式会导致索引失效：当查询条件中对索引列使用了函数或表达式时，MySQL无法直接使用索引进行查找，而是会进行全表扫描。这是因为MySQL需要先计算函数的结果，然后再与索引键值进行比较。

示例：
– 函数使用导致索引失效

SELECT * FROM users WHERE YEAR(birth_date) = 2000;

改进：
避免在索引列上使用函数

ALTER TABLE users ADD COLUMN birth_year INT GENERATED ALWAYS AS (YEAR(birth_date)) STORED;
CREATE INDEX idx_birth_year ON users(birth_year);
SELECT * FROM users WHERE birth_year = 2000;

6、避免在索引失效场景

• LIKE通配符会导致索引失效：当LIKE通配符出现在查询条件的开头时，MySQL无法使用索引进行查找，而是会进行全表扫描。例如，LIKE '%abc’会导致索引失效，因为MySQL无法确定从哪个位置开始匹配。
• or连接，!=等场景

示例：
– 通配符出现在开头导致索引失效

SELECT * FROM articles WHERE title LIKE '%database%';

7、避免隐式类型转换

• 隐式类型转换会导致索引失效：当查询条件中的数据类型与索引列的数据类型不一致时MySQL会进行隐式类型转换，导致索引失效。例如，索引列是INT类型，而查询条件中使用了字符串类型的值，MySQL会将字符串转换为整数，从而导致索引失效。

示例：
– 隐式类型转换导致索引失效

CREATE INDEX idx_user_id ON users (user_id);
SELECT * FROM users WHERE user_id = '123';  -- user_id是INT类型，但查询条件使用了字符串

改进：
确保查询条件的数据类型与索引列的数据类型一致

SELECT * FROM users WHERE user_id = 123;  -- 使用整数类型

8、使用覆盖索引（Covering Index）

• 覆盖索引可以避免回表查询：如果查询中涉及的所有列都在同一个索引中，MySQL可以直接从索引中获取所需的数据，而不需要回表查询。这种情况下，索引被称为覆盖索引，可以显著减少I/O操作，提升查询性能。

示例：
– 创建覆盖索引，包含查询中涉及的所有列

CREATE INDEX idx_department_name_age ON employees (department, name, age);

– 查询中涉及的所有列都在索引中，避免回表查询

SELECT name, age FROM employees WHERE department = 'Sales';

9、定期重建和优化索引

• 索引可能会碎片化：随着时间的推移，索引可能会变得碎片化，影响查询性能。定期重建索引可以优化索引的结构，减少碎片化，提升查询性能。

解决方法：

• 使用OPTIMIZE TABLE语句：OPTIMIZE TABLE语句可以重建表并优化其索引。
  示例：

OPTIMIZE TABLE table_name;

• 使用ALTER TABLE … ENGINE=InnoDB：对于InnoDB表，可以通过ALTER TABLE语句重建表并优化索引。
  示例：

ALTER TABLE table_name ENGINE=InnoDB;

• 定期维护：建议定期执行索引重建操作，尤其是在高并发写入场景下，定期维护索引可以保持其高效性。

10、索引创建原则总结

索引创建是一项需要权衡的技术决策，既要考虑查询性能，又要兼顾写操作的开销和磁盘空间的占用。通过遵循上述索引创建原则，你可以设计出高效的索引结构，确保系统的高性能和可维护性。

• 根据查询需求创建索引：优先考虑频繁执行的查询，避免为不常用的查询创建索引。
• 选择合适的索引类型：根据查询场景选择合适的索引类型，如B+Tree、哈希、全文索引等。
• 使用组合索引：减少单独创建多个索引的开销，遵循最左前缀原则。
• 避免过度索引：定期审查索引，删除不再需要的索引，避免过多的索引增加写操作开销。
• 避免在索引列上使用函数、LIKE通配符、隐式类型转换：这些操作会导致索引失效，影响查询性能。
• 使用覆盖索引：避免回表查询，减少I/O操作，提升查询性能。
• 避免使用OR连接多个条件：使用UNION或创建多个索引来优化查询。
• 定期重建和优化索引：减少索引碎片化，提升查询性能。

七、索引失效

索引失效是指MySQL在执行查询时，虽然表中存在索引，但MySQL并没有使用这些索引，而是选择了全表扫描（Full Table Scan）或其他低效的查询方式。索引失效会导致查询性能大幅下降，尤其是在数据量较大的情况下。
索引失效的原因有很多，通常与查询条件、SQL语句的写法、索引的设计等因素有关。了解索引失效的场景并采取相应的解决方法，可以帮助你优化查询性能，确保索引的有效利用。

索引失效的常见场景：

1、在索引列上使用函数

当查询条件中对索引列使用了函数或表达式时，MySQL无法直接使用索引进行查找，而是会进行全表扫描。这是因为MySQL需要先计算函数的结果，然后再与索引键值进行比较。

问题示例：

SELECT * FROM users WHERE YEAR(birth_date) = 2000;

在这个例子中，YEAR()函数阻止了MySQL使用birth_date列上的索引，因为MySQL无法直接比较函数的结果与索引键值。

解决方法：

• 避免在索引列上使用函数：尽量避免在索引列上使用函数。如果必须使用函数，可以考虑创建生成列（Generated Column）并为其创建索引。
  改进示例：

  ALTER TABLE users ADD COLUMN birth_year INT GENERATED ALWAYS AS (YEAR(birth_date)) STORED;CREATE INDEX idx_birth_year ON users (birth_year);SELECT * FROM users WHERE birth_year = 2000;

• 使用表达式索引：某些存储引擎（如InnoDB）支持表达式索引，允许你为计算结果创建索引。
  改进示例：

  CREATE INDEX idx_expr ON users((YEAR(birth_date)));

2、在索引列上使用LIKE通配符（前缀匹配）

当LIKE通配符出现在查询条件的开头时，MySQL无法使用索引进行查找，而是会进行全表扫描。例如，LIKE '%abc’会导致索引失效，因为MySQL无法确定从哪个位置开始匹配。

问题示例：

SELECT * FROM articles WHERE title LIKE '%database%';

在这个例子中，%通配符出现在查询条件的开头，MySQL无法使用title列上的索引，而是进行全表扫描。

解决方法：

• 使用前缀匹配：如果只需要匹配字符串的前缀部分，可以将通配符放在查询条件的末尾，这样MySQL可以使用索引进行查找。
  改进示例：
  SELECT * FROM articles WHERE title LIKE ‘database%’;
• 避免在索引列上使用LIKE通配符：尽量避免在索引列上使用LIKE通配符，尤其是在通配符出现在查询条件的开头时。如果必须使用LIKE，可以考虑使用全文索引或正则表达式索引。
  改进示例：

  CREATE FULLTEXT INDEX idx_title ON articles (title);SELECT * FROM articles WHERE MATCH (title) AGAINST ('database');

3、隐式类型转换

当查询条件中的数据类型与索引列的数据类型不一致时，MySQL会进行隐式类型转换，导致索引失效。例如，索引列是INT类型，而查询条件中使用了字符串类型的值，MySQL会将字符串转换为整数，从而导致索引失效。

问题示例：

CREATE INDEX idx_user_id ON users (user_id);
SELECT * FROM users WHERE user_id = '123';  -- user_id是INT类型，但查询条件使用了字符串

在这个例子中，user_id列是INT类型，而查询条件中使用了字符串’123’，MySQL会进行隐式类型转换，导致索引失效。

解决方法：

• 确保查询条件的数据类型与索引列的数据类型一致：在编写查询时，确保查询条件中的数据类型与索引列的数据类型一致，避免隐式类型转换。
  改进示例：

SELECT * FROM users WHERE user_id = 123;  -- 使用整数类型

• 显式类型转换：如果必须使用不同类型的值，可以使用显式类型转换函数（如CAST或CONVERT），确保查询条件与索引列的数据类型一致。
  改进示例：

SELECT * FROM users WHERE user_id = CAST('123' AS UNSIGNED);

4、组合索引的最左前缀原则未满足

组合索引遵循最左前缀原则，即查询条件必须从索引的最左列开始匹配。如果查询条件没有包含组合索引的最左列，MySQL无法使用该索引，导致索引失效。

问题示例：

CREATE INDEX idx_name_age_salary ON employees (name, age, salary);
SELECT * FROM employees WHERE age = 30 AND salary > 5000;  -- 没有包含最左列 name

在这个例子中，组合索引(name, age, salary)的最左列是name，但查询条件中没有包含name，因此MySQL无法使用该组合索引，导致索引失效。

解决方法：

• 确保查询条件包含组合索引的最左列：在设计组合索引时，应将最常用的查询条件放在索引的最左边，确保查询条件能够充分利用组合索引。
  改进示例：

SELECT * FROM employees WHERE name = 'John' AND age = 30 AND salary > 5000;

• 创建多个单索引：如果查询条件经常不包含组合索引的最左列，可以考虑为不同的查询条件创建多个单独的索引，或者重新设计组合索引的顺序。
  改进示例：

CREATE INDEX idx_age_salary ON employees (age, salary);  -- 为age和salary创建单独的组合索引

5、使用OR连接多个条件

当查询条件中使用OR连接多个条件时，MySQL可能无法有效地使用索引，尤其是当OR的每个条件涉及不同的列时。MySQL会尝试为每个条件分别使用索引，但最终可能会选择全表扫描。

问题示例：

CREATE INDEX idx_name ON employees (name);
CREATE INDEX idx_department ON employees (department);
SELECT * FROM employees WHERE name = 'John' OR department = 'Sales';

在这个例子中，name和department分别有不同的索引，但OR连接的查询条件使得MySQL无法同时使用这两个索引，最终可能导致索引失效。

解决方法：

• 使用UNION替代OR：如果OR的每个条件涉及不同的列，可以考虑使用UNION将查询拆分为多个子查询，分别使用各自的索引。
  改进示例：

  (SELECT * FROM employees WHERE name = 'John')UNION(SELECT * FROM employees WHERE department = 'Sales');

• 使用覆盖索引：如果查询条件涉及的列都在同一个组合索引中，MySQL可以更有效地使用索引。
  改进示例：

CREATE INDEX idx_name_department ON employees (name, department);
SELECT * FROM employees WHERE name = 'John' OR department = 'Sales';

6、使用NOT IN或!=

NOT IN和!=操作符通常会导致索引失效，因为它们需要扫描整个表来排除不符合条件的行。特别是当NOT IN后面的子查询返回大量数据时，MySQL无法有效地使用索引。

问题示例：

SELECT * FROM orders WHERE status != 'Completed';
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE status = 'Inactive');

在这两个例子中，!=和NOT IN操作符使得MySQL无法有效使用索引，导致全表扫描。

解决方法：

• 使用LEFT JOIN和IS NULL替代NOT IN：对于NOT IN子查询，可以使用LEFT JOIN和IS NULL来替代，这样可以更好地利用索引。
  改进示例：

  SELECT o.* FROM orders oLEFT JOIN customers c ON o.customer_id = c.id AND c.status = 'Inactive'WHERE c.id IS NULL;

• 使用IN替代!=：如果!=操作符后面的值是常量，可以考虑将其转换为IN操作符，以便更好地利用索引。
  改进示例：

 SELECT * FROM orders WHERE status IN ('Pending', 'Processing');

7、使用ORDER BY和GROUP BY时未使用索引

当查询中使用ORDER BY或GROUP BY时，如果排序或分组的列不在索引中，MySQL无法使用索引进行排序或分组，而是会进行文件排序（FileSort）或临时表操作（Using temporary），这会导致性能下降。

问题示例：

SELECT * FROM employees ORDER BY salary DESC;
SELECT department, COUNT(*) FROM employees GROUP BY department;

在这两个例子中，salary和department列没有索引，MySQL无法使用索引进行排序或分组，导致性能下降。

解决方法：

• 为排序或分组的列创建索引：为ORDER BY或GROUP BY中的列创建索引，MySQL可以更有效地进行排序或分组。
  改进示例：

CREATE INDEX idx_salary ON employees (salary);
CREATE INDEX idx_department ON employees (department);

• 使用覆盖索引：如果查询中涉及的所有列都在索引中，MySQL可以直接从索引中获取数据，而不需要回表查询，从而提高查询性能。
  改进示例：

CREATE INDEX idx_department_count ON employees (department, id);
SELECT department, COUNT(id) FROM employees GROUP BY department;

8、使用LIMIT时未使用索引

当查询中使用LIMIT时，如果查询条件没有使用索引，MySQL仍然会扫描整个表，直到找到满足条件的行数。这会导致性能下降，尤其是在数据量较大的情况下。

问题示例：

SELECT * FROM articles LIMIT 10;

在这个例子中，查询没有使用任何索引，MySQL会扫描整个表，直到找到10行数据。

解决方法：

• 为查询条件添加索引：为查询条件添加索引，确保MySQL可以快速定位到满足条件的行，而不是扫描整个表。
  改进示例：

CREATE INDEX idx_created_at ON articles (created_at);
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10;

• 使用覆盖索引：如果查询中涉及的所有列都在索引中，MySQL可以直接从索引中获取数据，而不需要回表查询，从而提高查询性能。
  改进示例：

CREATE INDEX idx_created_at_title ON articles (created_at, title);
SELECT title FROM articles ORDER BY created_at DESC LIMIT 10;

乘风破浪会有时，直挂云帆济沧海！！！