如何使用索引成就高性能的MySql（2万字详解）

使用索引，无疑是为了提高查询性能，但也需要付出一些代价，比如增加存储开销、影响写入的性能、增加复杂性使其变得不易维护等等，所以，使用索引需谨慎，索引问题不容易发现，因为给定的查询是否会使用索引并不是显而易见的。小型表就不要使用索引了，大型表也要选择适合的索引。

1. 索引的类型

1.1. 按逻辑功能划分

a. 普通索引

最常见、使用最多的一种，允许在索引列中存在重复值。

使用sql创建索引：

CREATE INDEX indexName ON tableName (columnName)  //创建
SELECT * FROM tableName WHERE name='HanMei'     //查询
ALTER TABLE tableName ADD INDEX indexName (columnName); //已存在表中添加索引
DROP INDEX  indexName ON tableName    //删除
SHOW INDEX FROM 	tableName   //查看表中索引

查询时，数据库会自动使用已创建的索引。普通索引创建不宜过多，且适合基数较高的列(不同值的数量较多)。

使用EFCore创建索引：

使用注解标注：

[Index(nameof(Username))]
public class User
{
    public string Username { get; set; }
    public string Password { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

或使用Fluent API：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasIndex(b => b.Username);
}

注意：在.net6以及.net6之后，不支持在属性上使用注解标记来标注索引。

b. 唯一索引

是一种确保数据库表中某一列（或组合列）中的所有值都是唯一的索引，即建立索引的列的值必须唯一，尝试为索引的列集插入多个具有相同值的实体将导致引发异常；但允许有空值，包含的空值可以有多个。若是复合唯一索引，组合的值唯一即可。

使用sql创建索引：

CREATE UNIQUE INDEX indexName ON tableName (columnName)  //创建
ALTER TABLE tableName ADD UNIQUE (columnName);  //已存在表中添加唯一索引
// 其他操作见“普通索引”

使用EFCore创建索引：

使用注解标注：

[Index(nameof(Username), IsUnique = true)]
public class User
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

使用Fluent API：

protected override void OnModelCreating(ModelBuilder modelBuilder)  
{  
    base.OnModelCreating(modelBuilder);  
    modelBuilder.Entity<User>()  
        .HasIndex(u => u.Username)  
        .IsUnique(); // 设置为唯一索引  
}  

c. 主键索引

要求所包含的列中的值是唯一的，且不能包含 NULL 值。
创建表时指定了主键，数据库自动创建主键索引。

CREATE TABLE User (  
    id INT PRIMARY KEY AUTO_INCREMENT,  -- id 列为主键  
    name VARCHAR(100),  
    lastname VARCHAR(50) 
);  

其他无需多说。

d. 复合索引

一个索引中包含多个列。在需要多个列配合查询的时候，可以使用复合索引（不要创建多个普通索引）。

创建索引：

//创建表
CREATE TABLE `User` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `Username` varchar(255),
  `Password` longtext,
  `LastName` varchar(255),
  `Email` varchar(255),
  `Department` varchar(255),
  PRIMARY KEY (`Id`),
  KEY `IX_User_Username_LastName` (`Username`,`LastName`)
)
//修改表
ALTER TABLE articles ADD FULLTEXT (title, content);  

使用EFCore创建索引：

使用注解标注：

[Index(nameof(Username), nameof(LastName))]
public class User
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

使用Fluent API：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasIndex(p => new { p.FirstName, p.LastName });
} 

e. 全文索引

Full-Text Index，是一种专门用于加速对文本字段的搜索的索引。它适用于CHAR、VARCHAR和TEXT类型的字段。

创建索引：

//创建表
CREATE TABLE articles (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    title VARCHAR(255),  
    content TEXT,  
    FULLTEXT (title, content)  
);  
//修改表
ALTER TABLE articles ADD FULLTEXT (title, content);  
//查询
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('关键词');  

注意：默认最小搜索长度为4个字符，低于4个字符会被忽略，当然这个长度是可以设置的，

查询mysql的最小搜索长度：

show variables like 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.01 sec)

设置最小搜索长度：
在MySQL配置文件（my.cnf或my.ini）中进行更改。

该配置文件的位置：
linux上一般在“/etc/mysql/my.cnf”，
Windows上一般在“C:\ProgramData\MySQL\MySQL Server X.Y\my.ini”。

在配置文件中添加或修改以下行：

[mysqld]  
ft_min_word_len = 3  # 将最小搜索长度设置为3  

需要重启MySQL服务才能生效。

注意：某些说使用命令来设置该参数是行不通的，
如：

mysql> SET GLOBAL ft_min_word_len = 3;
ERROR 1238 (HY000): Variable 'ft_min_word_len' is a read only variable
mysql> SET ft_min_word_len = 3;
ERROR 1238 (HY000): Variable 'ft_min_word_len' is a read only variable
mysql>

ft_min_word_len 是MySQL中的一个只读变量，无法通过 SET GLOBAL 语句动态修改。

ORM是不支持使用全文索引的，如果需要使用，还是需要使用sql语句来创建。

1.2 按物理实现划分

a. 聚簇索引

在InnoDB存储引擎中，聚簇索引是主键索引的特性，数据按照主键的顺序存储，这使得通过主键进行查询时效率更高。创建聚簇索引的过程实际上就是定义主键索引或将某列设置为主键。

• 聚簇索引使用B+树数据结构来存储索引，根节点和中间节点包含的是索引键（即主键值）和指向子节点的指针，而叶子节点直接包含了表的行数据。
• 每个表只能有一个聚簇索引，因为数据的存储顺序只能按照一个顺序进行。
• 据在磁盘上的实际存储顺序与聚簇索引的顺序一致，从而使得基于聚簇索引的查找非常高效。

优点：

• 高效的查询性能：对于主键查询、范围查询和顺序查询性能优越，因为数据是物理上顺序存储的。
• 减少I/O操作：基于聚簇索引的查询通常只需读取一层B+树。

缺点：

• 由于数据按主键顺序物理存储，因此更新主键时可能会导致数据的移动，从而影响性能。
• 如果有大量的插入，且插入的数据主键值是随机的，可能导致B+树的不平衡。
• 插入、删除或更新时，如果数据行的顺序不符合聚簇索引，可能会导致物理重组。

b. 非聚簇索引

非聚簇索引是指索引的结构与数据表的存储顺序无关。非聚簇索引创建后，数据的物理存储和索引的逻辑存储是分开的。

• 一个表可以有多个非聚簇索引。
• 叶子节点存储的是索引键及相应的指向对应行位置的指针（通常是主键或者行ID）。
• 非叶子节点存储索引键和指向子节点的指针。

优点：

• 灵活性高：可以创建多个非聚簇索引，以满足不同查询需求。
• 搜索快：非聚簇索引提供了快速的搜索能力，但需要额外的I/O来获取行数据。
• 不影响数据存储顺序：更容易处理数据的插入、更新和删除。

缺点

• 查询性能相对较差：由于需要通过索引找到数据的物理位置，性能上比聚簇索引低。
• 空间开销大：由于需要额外存储指针和索引结构，空间占用较聚簇索引大。

1.3 按数据结构划分

• B+Tree索引 是一种自平衡的树数据结构，主要用于高效的数据检索。它是数据库中最常用的索引结构，支持范围查询和精确查找。 多数mysql引擎都是支持B-Tree索引，只是略有差异，Archive除外，Archive到5.1版本之后才支持自增列。InnoDb是根据主键引用被索引的行，而MyISAM索引通过数据的物理位置引用被索引的行。
  普通索引、唯一索引、主键索引、复合索引、聚簇索引、非聚簇索引等全是基于B+Tree的索引。
  其查找时间复杂度为O(log n)，非常适合于大量数据，频繁执行范围查询或多列排序的场景。

• Hash索引 使用哈希表结构，将键值映射到一个固定大小的哈希表中，以实现快速查找。通常用于处理精确匹配查询。
  每个索引条目由key和value组成，key是被索引的列，value是指向行数据的指针。
  MySQL中只有memory引擎显式的支持哈希索引，这也是memory引擎默认的索引类型，当然memory也支持B-Tree索引。memory支持非唯一哈希索引。
  查找时间复杂度为O(1)（在理想情况下），适合等值查询，不适用于处理大于、小于等范围查询。

	CREATE TABLE testhash(
	    fname varchar(50) not null,
	    iname varchar(50) not null,
	    KEY USING HASH(fname)
	) ENGINE=MEMORY;

• FullText索引 FullText索引是专门用于处理大文本的索引类型，主要应用于文本搜索。它允许对文本字段执行更加复杂的搜索，比如关键词相关性检索。时间复杂度较高，维护成本较高。
  适用于需要强大搜索功能的应用，如博客、论坛、电子商务网站的商品搜索等。

2. 索引策略

2.1 选择合适类型的索引并控制索引数量

使用合适的索引类型可以显著提升查询性能，确保数据库在处理不同类型的查询时能够高效运行，不同的索引类型在不同的场景下表现不一，选错类型可能导致性能下降。参考上文，了解每种索引的特点，并根据具体的查询需求选择合适的索引类型。

减少冗余索引可以提升数据库的写入和更新性能，同时简化索引的维护。当然也要注意不要删除了重要的索引。定期审查现有索引，识别并移除不再使用的或冗余的索引。

2.2 执行计划分析索引使用情况

通过分析执行计划，可以直观了解到查询的性能瓶颈，为优化提供数据支持。定期使用 EXPLAIN 查看常用查询的执行计划，利用统计信息调整索引。

查询前加上 EXPLAIN，可以看到MySQL如何执行该查询，如：

EXPLAIN SELECT name, age FROM users WHERE age > 25;  

EXPLAIN 的输出通常包含以下几个字段：

• id：查询的唯一标识符，表示查询中执行的顺序。id相同，则执行顺序从上往下进行；id不同，则id值越大，优先级越高，越先执行。
• select_type：表示查询的类型。 常见的 select_type 类型：
  • SIMPLE：简单查询，不包含子查询或联合查询。
  • PRIMARY：查询的最外层 SELECT 语句，即使查询中包含一个或多个子查询，最外层的查询为 PRIMARY。
  • SUBQUERY：子查询中的 SELECT 语句。
  • UNION：合并多个 SELECT 查询的结果，可能会使用额外的索引，具体取决于各个子查询。
  • UNION RESULT：NION 的结果集，表示合并后的结果。
  • DERIVED：子查询的结果被当作一个临时表使用，表示查询中使用了派生表（临时表），通常会得到更好的优先级，以确保结果的准确性。
  • DEPENDENT UNION：依赖于外层查询的 UNION。
  • DEPENDENT SUBQUERY：依赖于外层查询的子查询，通常性能较差，因为子查询需要多次执行。
• table：显示正在访问的表名。
• type：访问类型，表示查询中如何查找表的行。常见类型（从最佳到最差）：
  • system：表只有一行。
  • const：通过主键或唯一索引查询。
  • eq_ref：针对唯一索引的一条记录。
  • ref：通过非唯一索引查找记录。
  • range：范围查询，使用索引找到一个范围。
  • index：全索引扫描，不返回行。
  • all：全表扫描，最低效。
• possible_keys：列出可能会用于该查询的索引。
• key：表明实际被使用的索引。
• key_len：使用的索引长度，可以帮助评估查询成本。
• ref：表明使用的列或常量与索引的连接。
• rows：MySQL估算需要扫描的行数。
• filtered：表示数据库根据给定的查询条件，预计从查询中满足条件的行的比例，是通过优化器预计的符合条件的行数与扫描的总行数的比率计算得出的百分比。
• Extra：提供额外的信息，如是否使用临时表、是否需要排序等。

下面我们来看一个示例：

mysql> explain select * from User where LastName < 'www-10';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | User  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  101 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

根据上述信息，我们可以进行分析：
type为“All”，说明进行了全表扫描，是低效行为，
possible_keys为null，但实际上数据库中存在Username 和 LastName的复合索引IX_User_Username_LastName，
key为null，没有使用到索引，
如果该项查询使用频次较高，则有必要调整索引，优化存在的索引。

如果该项查询频次并不高，或可以调整查询条件，来命中存在的索引：

mysql> explain select * from User where Username < 'www10';
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Username_LastName | IX_User_Username_LastName | 202     | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from User where Username < 'www10' and LastName > 'www-3';
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Username_LastName | IX_User_Username_LastName | 202     | NULL |    3 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

B-Tree有一个最左前缀法则，讲究索引的匹配从最左边的字段开始，匹配成功才能往右继续匹配下一个字段。
IX_User_Username_LastName 索引是Username和LastName和复合索引，且Username在最左侧，首先 需要匹配Username，只搜索LastName，是不能匹配该索引的。

再来看一个示例：

mysql> explain select * from User where Age < 33;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Age   | IX_User_Age | 4       | NULL |   34 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from User where Age < 34;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | User  | NULL       | ALL  | IX_User_Age   | NULL | NULL    | NULL |  101 |    34.65 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

同样的搜索条件，有同样的可用的索引，为什么Age < 33就可以命中索引，而Age < 34就不会命中索引呢？

这就涉及到 MySQL 的查询优化器的工作原理了，下面我们来了解一下：
MySQL 的查询优化器会根据多种因素决定是否使用索引。它主要考虑执行计划的效率以及查询返回的行数，当查询小于 33 的数据时，预计需要扫描行数为 34 行，它会认为可能远低于全表行数（101 行），而当查询小于 34 的数据时，优化器可能估算的返回结果行数接近于全表扫描的行数，可能全表扫描的成本较低，因此优化器会认为使用索引的开销不值得，所以选择不使用索引。

我们使用查询的时候，尝试将查询条件设置得更加精确，或使用组合条件，避免过大的范围。除此之外，页可以先使用 EXPLAIN 查看 MySQL 查询优化器如何处理你的查询，确认索引是否被使用。

如果你确定查询应使用特定的索引，可以使用 FORCE INDEX 提示来强制 MySQL 使用该索引：

mysql> explain select * from User force index (IX_User_Age)  where Age < 34;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Age   | IX_User_Age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2.3 创建复合索引

首先，创建多个普通索引的代价要比创建一个包含多个列的复合索引要高的多，不仅是表现在存储的开销上，还有很多方便，诸如影响写入性能，需要更多的维护成本，查询可能需要额外的联合操作，致使查询计划会更复杂且性能效率更低。因此，如果需要查询多列，尽量使用复合索引，而非多个普通索引。

2.4 定期手动更新统计信息

MySQL 其实是会自动更新统计信息的，但在某些情况下，定期手动执行 ANALYZE TABLE 还是非常有用的。
对于某些高频更新的表，MySQL 自动更新统计信息的频率可能不足以反映表的最新状态。尤其是在大量数据插入、更新或删除后，数据库的选择性和分布可能发生快速变化，而自动更新可能跟不上。而且自动更新的统计信息可能不会考虑到特定的使用场景和查询模式，因此，对于复杂查询，手动更新可能会使数据库优化器生成更合适的查询计划。MySQL 只有在一定条件下才会自动更新统计信息，例如行数增加或减少达到一定阈值。当更新操作的数量不够大时，可能不会触发自动更新。不仅如此，有些搜索引擎如Innodb，在一些情况下可能不会立即更新完整的统计信息，而是依赖于运行时的估计，所以，定期手动更新统计信息还是有必要的。

mysql> analyze table User;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test_db.User | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.02 sec)

定期运行 ANALYZE TABLE 是数据库维护的一部分，可以帮助确保在频繁进行数据操作的情况下优化器总是获得最准确的信息，以便提升查询性能。通过分析和监测表的统计信息，你可以识别潜在的性能问题，进而进行调整而不是完全依赖自动更新。

当然了，手动更新统计信息也是会需要一定的性能、时间等资源的开销的，尤其大型表或者高并发情况下，消耗的CPU资源和磁盘I/O会带来一定的影响，而且这种情况所需要的时间也可能是比较长的，且该过程可能会锁定表，所以执行前需要了解这些情况再决定是否更新。

2.5 使用覆盖索引

涵盖索引是指一个索引完全包含查询所需的所有列而无需访问表中的数据行。因此，数据库可以仅通过索引返回结果，而不需要访问原始数据表，减少了 I/O 操作，通过从索引中直接获取所有查询所需的数据，以提高查询性能。

有的同学说，覆盖索引不就是复合索引吗？

当然不是，复合索引是指一个索引包含多个列的索引。这种索引用于提高对多个列的查询效率，通常在 SELECT 语句的 WHERE 子句中涉及多个条件时使用；而覆盖索引是指一个索引能够满足查询所需的所有列，因此只通过索引就可以返回查询结果，无需访问原始数据表（即不需回表）。它可以是复合索引，也可以是单列索引。

复合索引：

mysql> explain select * from User where Username < 'www10' and LastName > 'www-3';
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Username_LastName | IX_User_Username_LastName | 202     | NULL |    3 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

旨在where 后面的搜索条件，而不强调select后面的列。

覆盖索引：

mysql> explain select Username, LastName from User where Username = 'www10';
+----+-------------+-------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | User  | NULL       | ref  | IX_User_Username_LastName | IX_User_Username_LastName | 202     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select Age from User where Age < 20;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Age   | IX_User_Age | 4       | NULL |   21 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

可能是单列索引，也可能是复合索引，索引中既要有where 后面的搜索条件所在的列，又要有select后面的列。

2.6 避免索引碎片

在进行大量的DELETE或UPDATE操作后，表中可能会产生碎片。
对于MyISAM 和 InnoDB 存储引擎，MySQL有专用的清理碎片的命令：

OPTIMIZE TABLE tableName

对于 MyISAM 表，OPTIMIZE TABLE 将释放未使用的存储空间。对 InnoDB 表，它不会释放磁盘空间，但是会重组数据，优化数据结构。该命令还会更新表的统计信息，MySQL 优化器在生成查询计划时会使用这些统计信息，从而可能进一步提高查询性能。

 mysql> optimize table User;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| test_db.User | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_db.User | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.17 sec)

在数据频繁进行插入、更新、删除操作后，表的性能可能会下降，使用碎片清理可以恢复性能。在对大表删除大量数据后，执行优化可以减少查询时的磁盘 I/O。可以将其作为定期维护的一部分，特别是在数据操作频繁的应用中。定期监控索引的碎片情况，选择合适的时机进行优化，避免对生产环境的冲击。

注意：在执行OPTIMIZE TABLE 时，MySQL 会对表进行锁定，这可能会暂时影响其他对该表的读写操作。在高并发环境中，需谨慎使用，最好安排在低峰时段 进行。而且，优化表的过程会消耗一定的 CPU 和 I/O 资源，虽然其最终目标是提高性能，但在执行优化时可能会影响系统的整体负载，需合理使用。

2.7 “独立的列”

所谓的 “独立的列”，就是索引列不能是表达式的一部分，也不能是函数的参数。这样说可能有些模糊，用示例对比说明就一目了然了。

示例1：

mysql> explain select * from User where Age + 1 = 20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | User  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  101 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

示例2：

mysql> explain select * from User where Age = 20 - 1;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | User  | NULL       | ref  | IX_User_Age   | IX_User_Age | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

虽然两者逻辑上相同，但在实际的查询执行中，示例 1（使用算术运算）导致 MySQL 不能利用索引，在 Age + 1 = 20 中，MySQL 需要对每个 Age 的值进行计算， 需要搜索全表101条数据。而在示例 2 中，Age = 19 能够直接利用索引得到其数据。

这就是使用不当的行为，我们应当养成简化where条件的习惯，将索引列单独放在比较符的一侧。

2.8 选择合适的索引列的顺序

对于B-Tree结构的索引来说，选择合适的索引列顺序在数据库性能优化中至关重要。

对于查询来说，复合索引遵循最左前缀法则，这意味着查询优化器会优先使用索引中左侧的列。当查询条件中包含索引的最左侧列时，MySQL 可以有效利用该索引来提高性能。
比如，如果有一个复合索引 (A, B)，查询条件 WHERE A = ? AND B = ? 会完全利用该索引，而 WHERE B = ? 则不能有效利用。

还是这个例子， User表中有一个 Username 和 LastName 的复合索引 (IX_User_Username_LastName) ：

mysql> explain select * from User where Username < 'www10';
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | User  | NULL       | range | IX_User_Username_LastName | IX_User_Username_LastName | 202     | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
explain select * from User where LastName < 'www-10';

mysql> explain select * from User where LastName < 'www-10';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | User  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  101 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用 Username 查询就可以命中索引， 而使用 LastName 查询就不能命中。所以，索引顺序很重要。
如果 Username 选择性较高，那么 Username 就应该放在前面，LastName 放在后面；相对的，如果LastName 选择性比较高， 则 LastName 就应该放前面了。

2.9 考虑使用分区表

使用分区表可以帮助管理和优化大数据量的表，并改善查询性能。分区可以减少每次查询时需要扫描的数据量。查询优化器可以快速定位到需要查询的特定分区，从而提高查询速度。

更重要的是，在某些情况下，MySQL 可以并行地处理多个分区，加快数据的检索速度，而且通过分区，过期或不再需要的数据分区可以轻松删除或归档，减少了对表的直接操作和数据迁移的复杂性，每个分区还可以独立进行维护，比如添加、删除、修改数据，以及更新统计信息，不会影响到其他分区。

分区表也是和索引一样，是有个庞大的知识体系，能够有效管理和查询大规模数据，并提供性能和维护上的优势。合理的分区策略、合适的分区键以及定期的监控和维护都是确保成功的关键。

总之

合理使用索引不仅能显著提高查询速度和性能，还能优化数据库的整体效率和资源使用，有效管理并发操作。然而，设计索引时也需考虑到每个索引的维护成本，平衡读写性能之间的关系，以满足具体业务需求。

本文中，我们列举了索引的类别，并详细介绍了各种索引使用SQL语句和EFCore分别怎么创建和使用，然后又根据实际情况，列举了使用索引的各种策略，并结合示例和小伙伴儿们经常遇到的疑问进行了说明，希望能够为大家带来帮助。
知识的海洋无边无尽，一个人遇到的风景总是有限的，然而，当众多人将其所见所思汇聚一处，便能共同绘制出一幅宏伟而多层次的知识画卷。
如果对文中有些地方有疑问， 或有一些独到的见解， 或遇到了什么样的问题，恳请大家私信我。