MySQL 索引详解（保姆级教程）

一、索引概述

索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。

二、索引的优缺点

优点：

提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

索引列也是要占用空间的
索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

三、索引结构

3.1 索引结构介绍

索引结构描述B+Tree最常见的索引类型，大部分引擎都支持B+树索引（默认）Hash底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询R-Tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型（通常使用较少）Full-Text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES（通常使用较少）

3.2 不同存储引擎对索引支持情况

索引InnoDBMyISAMMemoryB+Tree索引支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text5.6版本后支持支持不支持
** 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。**

四、数据结构介绍（二叉树、红黑树、Btree、B+tree）

4.1 二叉树

二叉树（Binary Tree）是一种特殊的树状数据结构，其中每个节点最多有两个子节点，分别称为左子节点和右子节点。二叉树的定义如下：

一个二叉树可以为空（即没有节点），或者由一个根节点和两颗分别称为左子树和右子树的二叉树组成。

二叉树的特点：

每个节点最多有两个子节点，分别为左子节点和右子节点。
左子树和右子树也是二叉树，可以为空。
二叉树的子节点没有特定的顺序，可以根据具体应用决定左右子节点的位置。

4.2 红黑树

红黑树（Red-Black Tree）是一种自平衡的二叉查找树，它在插入和删除操作后通过重新安排节点的颜色来保持平衡。红黑树的名称来源于每个节点上的颜色标记，每个节点可以是红色或黑色。

红黑树具有以下特点：

每个节点要么是红色，要么是黑色。
根节点是黑色的。
所有叶子节点（NIL节点）都是黑色的。
如果一个节点是红色的，则其两个子节点都是黑色的。
对于任意节点，从该节点到其所有后代叶子节点的简单路径上，均包含相同数目的黑色节点。

这些特点确保了红黑树的关键性质，即从根节点到任何叶子节点的最长路径不会超过最短路径的两倍，从而保持了树的平衡性。这种平衡性使得红黑树在实际应用中非常高效，常被用作集合、映射等数据结构的基础。

** 红黑树缺点：大数据量情况下，层级较深，检索速度慢的问题。**

4.3 B-Tree（多路平衡查找树）

B-Tree（B树）是一种用于存储和组织大量数据的自平衡搜索树结构。它被广泛应用于数据库和文件系统等领域，以提供高效的数据访问和查询性能。

B-Tree的特点包括：

多路平衡性：每个节点可以包含多个关键字和子节点，这使得B-Tree具有较好的平衡性能。通常情况下，B-Tree的所有叶子节点都位于相同的层级上。
有序性：B-Tree中的关键字按照升序排列，在进行范围查询时非常高效。
磁盘友好性：B-Tree的节点大小通常与硬盘页的大小相匹配，这样可以最大程度地减少磁盘I/O操作，提高读写性能。
自适应性：B-Tree能够动态调整自身的结构以适应数据的动态插入和删除操作，保持平衡性和性能稳定。

B-Tree的基本操作包括插入、删除和查找。在插入和删除操作时，B-Tree会通过重新分配关键字和调整节点来保持平衡。通过使用B-Tree索引，可以显著提高数据的检索效率，尤其是对于大规模的数据集。

需要注意的是，B-Tree并不仅限于二叉树的结构，每个节点可以包含多个子节点，使其适用于处理大规模数据集的情况。

B-Tree 的数据插入过程动画参照：Data Structure Visualization

B-Tree Visualization （如果上面演示链接打不开，请更换）

4.4 B+Tree

B+树（B+Tree）是一种类似于B-Tree的自平衡搜索树结构，被广泛应用于数据库和文件系统等领域。它是B-Tree的一种变体，相较于B-Tree，在存储和查询性能上有一些优化。

B+树与B-Tree相似，也具有多路平衡性、有序性和磁盘友好性的特点。但B+树在某些方面具有不同的设计：

只有叶子节点存储数据：B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。
叶子节点之间通过指针连接：B+树的叶子节点使用指针进行连接，形成一个有序链表，便于范围查询和顺序遍历。
顺序访问性能更好：由于叶子节点之间的指针连接和有序链表的形式，B+树在顺序访问时具有更好的性能。例如，对于范围查询或者按照关键字顺序遍历数据，B+树比B-Tree更适合。
叶子节点之间没有互相连接：B+树的叶子节点之间并没有直接的连接，需要通过内部节点进行导航，这样可以减少内部节点的空间占用。

B+树通常被用作数据库系统的索引结构，特别适用于支持范围查询和按顺序访问数据的场景。它的平衡性和磁盘友好性使得在大规模数据集的存储和检索过程中具有良好的性能表现。

B+Tree 的数据插入过程动画参照：Data Structure Visualization

B+ Tree Visualization（如果上面演示链接打不开，请更换）

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

4.5 Hash 哈希索引

哈希索引（Hash Index）是一种在数据库中用于快速查找数据的索引结构。它通过将关键字（Key）通过散列函数（Hash Function）转换成一个固定长度的散列值（Hash Value），然后将这个散列值与存储位置建立映射关系，从而实现高效的数据查找。

哈希索引的主要特点包括：

快速查找：哈希索引通过使用散列函数将关键字映射到存储位置，可以在常数时间内直接访问目标数据，因此具有非常高的查找效率。
相等查询优化：哈希索引适用于相等比较查询（例如WHERE column = value），对于这类查询，只需要计算散列值并进行一次查找即可，不需要遍历整个索引。
不支持范围查询和排序：由于哈希索引是基于散列值进行查找的，因此不支持范围查询（例如WHERE column > value）和排序操作。
冲突处理：由于散列函数将不同的关键字映射到相同的散列值可能性存在，这种情况称为哈希冲突。常见的解决冲突的方法包括开放地址法和链表法。

需要注意的是，哈希索引在某些场景下可能效果不如B树索引，因为它无法支持范围查询和排序操作，并且对于存在大量冲突的情况下性能可能会下降。因此，在选择索引类型时需要根据具体的业务需求和数据特点进行综合考虑。

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

Hash索引特点：

Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

Memory
InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

面试题

为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

相比于二叉树：二叉树顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。B+树，能解决顺序插入的问题且层级更少，搜索效率高。
相比于红黑树：红黑树虽然解决了顺序插入形成链表的问题，但是本质上二叉树，大数据量情况下，层级较深，检索速度慢。B+树，能解决顺序插入的问题且层级更少，搜索效率高。
相比于B-Tree：对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。而B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。B+树的叶子节点会形成一个有序链表，便于范围查询和顺序遍历。
相对于 Hash 索引：Hash索引只支持等值匹配，不支持范围查询和排序。B+Tree 支持范围匹配及排序操作。

五、索引分类介绍

5.1 索引分类

分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT

5.2 InnoDB存储引擎索引分类

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：
分类含义特点聚集索引(Clustered Index)将数据存储与索引放一块，索引结构的叶子节点保存了行数据必须有，而且只有一个二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

假设user表的id字段为聚集索引，name字段为二级索引，那么select * from user where name = 'Arm'的查询顺序如下：

会先到二级索引中查询name = Arm的数据，查询到name=Arm的id为10，然后再去聚集索引中查询id=10的数据（聚集索引中存放的是这一行的行数据）流程图如下：

思考题

以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10;

select * from user where name = 'Arm';

-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

六、索引的使用（创建、查看、删除）

创建索引：

  CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

解释如下：

CREATE INDEX：创建索引的关键字。
[ UNIQUE | FULLTEXT ]：可选参数，指定索引类型。UNIQUE表示创建唯一索引，即索引列的值必须唯一；FULLTEXT表示创建全文索引，用于全文搜索。如果不指定，默认为普通索引。
index_name：指定索引的名称。
ON table_name：指定要在哪张表上创建索引，table_name是表名。
(index_col_name, ...)：指定要创建索引的列名，可以指定一个或多个列作为索引的键。多个列之间用逗号分隔。

例如，如果要在名为

users

的表上创建一个名为

idx_username

的普通索引，索引列为

username

，可以使用以下语句：

    CREATE INDEX idx_username ON users (username);

如果要创建一个唯一索引，可以将关键字

UNIQUE

添加到语句中：

    CREATE UNIQUE INDEX idx_email ON users (email);

如果要创建一个全文索引，可以将关键字

FULLTEXT

添加到语句中：

    CREATE FULLTEXT INDEX idx_content ON articles (content);

查看索引：

  SHOW INDEX FROM table_name;

删除索引：

  DROP INDEX index_name ON table_name;

**案例： **

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引

create index idx_user_name on tb_user(name);

-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引

create unique index idx_user_phone on tb_user (phone);

-- 为profession, age, status创建联合索引

create index idx_user_pro_age_stat on tb_user(profession, age, status);

-- 为email建立合适的索引来提升查询效率

create index idx_user_email on tb_user(email);

-- 删除索引

drop index idx_user_email on tb_user;

七、SQL性能分析

7.1 SQL执行频率（了解）

My5QL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。

使用My5QL客户端成功连接到MySQL服务器后，可以通过使用

SHOW SESSION STATUS

或

SHOW GLOBAL STATUS

命令来获取服务器的状态信息。具体地，可以使用以下指令来查看当前数据库中INSERT（插入）、UPDATE（更新）、DELETE（删除）和SELECT（查询）的访问频次：

SHOW SESSION STATUS LIKE 'Com_insert';
SHOW SESSION STATUS LIKE 'Com_update';
SHOW SESSION STATUS LIKE 'Com_delete';
SHOW SESSION STATUS LIKE 'Com_select';

在MySQL中，"session"和"global"都是用来指代不同级别的变量或参数。

Session级别：Session级别的变量或参数仅适用于当前会话（连接）。这意味着设置的值只对当前连接有效，并且对其他连接没有影响。例如，通过SET语句设置的会话级别变量只在当前会话中生效，并且在会话结束后会被重置为默认值。
Global级别：Global级别的变量或参数适用于整个MySQL服务器实例。这意味着设置的值对所有连接和会话都有效。例如，通过修改配置文件或使用SET GLOBAL语句设置的全局级别变量会影响所有连接和会话。

在命令中，可以使用以下方式来访问不同级别的变量或参数：

SHOW SESSION STATUS：显示当前会话级别的状态变量。
SHOW GLOBAL STATUS：显示全局级别的状态变量。

需要注意的是，某些变量可能只能在特定级别进行查看或设置。因此，在选择使用"session"还是"global"时，要考虑到所需的变量或参数是否在该级别下可用或具有所需的权限限制。

7.2 慢查询日志（了解）

MySQL慢查询日志是一种记录执行时间超过特定阈值的SQL查询语句的日志。它可以帮助您识别和优化数据库中的性能瓶颈。

要启用MySQL慢查询日志，您需要执行以下步骤：

打开MySQL配置文件（通常是my.cnf或my.ini）。您可以在以下位置找到该文件：

Linux：/etc/mysql/my.cnf或/etc/my.cnf
Windows：MySQL安装目录下的my.ini

在配置文件中找到 [mysqld] 部分，如果不存在，则在文件末尾添加该部分。
在 [mysqld] 部分中添加以下行来启用慢查询日志并设置阈值（以秒为单位）：
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
long_query_time = 2

slow_query_log：设置为 1 表示启用慢查询日志。
slow_query_log_file：指定慢查询日志的路径和文件名。请根据您的需求选择合适的文件路径和名称。
long_query_time：指定执行时间超过多少秒的查询被认为是慢查询。这个值根据您的应用需求进行调整。

保存并关闭配置文件。
重启MySQL服务，以使配置更改生效。
现在，MySQL慢查询日志已启用。执行时间超过阈值的查询语句将被记录在指定的日志文件中。

要查看慢查询日志，可以使用文本编辑器打开指定的日志文件（/path/to/slow-query.log），以查看其中记录的慢查询语句和相关信息。

请注意，启用慢查询日志可能会对数据库性能产生一定影响，因此在生产环境中应谨慎使用，并根据需要进行适当的配置和管理。

7.3 profile详情（查看SQL执行时间）（了解）

在MySQL中，查询的"profiling"功能可以用于跟踪和分析查询的性能。当启用查询性能分析后，MySQL将记录每个查询的详细执行统计信息。

要启用查询的profiling功能，并查看查询的详细执行统计信息，请按照以下步骤进行操作：

打开MySQL客户端或使用适当的MySQL图形用户界面工具连接到数据库。
在会话中，执行以下命令以启用查询的profiling功能：
SET profiling = 1;
然后，执行您希望分析的查询语句。
查询执行完成后，使用以下命令查看查询的profiling详情：
SHOW PROFILES;

这将显示一个包含所有查询的列表，每个查询都有一个唯一的查询ID。
选择要查看其profiling详情的查询，使用以下命令：
SHOW PROFILE FOR QUERY <query_id>;
将 <query_id> 替换为您要查看的查询的实际查询ID。

这将显示与所选查询相关的详细执行统计信息，包括查询的执行时间、扫描行数、临时表创建等等。
查看完查询的profiling详情后，可以使用以下命令来停止profiling并清除已记录的查询信息：
SET profiling = 0;

请注意，启用查询的profiling可能会对性能产生一定的影响。因此，应仅在需要详细分析查询性能时才启用profiling，并及时停止profiling以避免不必要的开销。

7.4 explain执行计划（重要）

Explain执行计划是MySQL中的一个命令，用于获取查询语句的执行计划。执行计划显示了MySQL优化器在处理查询时选择的操作顺序、使用的索引、数据访问方式等详细信息。

Explain执行计划的结果集中包含多个字段，每个字段提供了关于查询执行的不同方面的信息。以下是Explain执行计划结果集中常见的字段及其含义：

id：- 指示查询执行计划中每个操作的编号。- 对于复杂查询，可能会有多个操作，它们按照树状结构编号。
select_type：- 表示执行操作的类型。- 常见的类型包括：SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）、DERIVED（衍生表查询）、UNION（联合查询）等。- 该字段可以帮助您理解查询中不同操作的类型和关系。
table：- 指示操作涉及的表名。- 如果查询涉及多个表，则可能会出现多行，并以箭头表示连接顺序。
type：- 表示MySQL将如何访问表。- 常见的类型包括：ALL（全表扫描）、INDEX（使用索引扫描）、RANGE（范围扫描）、REF（使用引用键扫描）、EQ_REF（唯一索引查找）、CONST（常量查找）等。- 通常，较好的访问类型是使用索引的访问方式，而不是全表扫描。- 最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL. ---system表只有一行记录(等于系统表)const使用常量进行索引查询eq_ref唯一索引扫描，通常使用主键约束ref非唯一性索引扫描range索引范围扫描index全索引扫描ALL全表扫描
possible_keys：- 指示MySQL能够使用的潜在索引。- 如果查询中使用了索引，这些索引将显示在此字段中。
key：- 表示实际选择使用的索引。- 如果该字段为空，则表示没有使用索引。- 通常，较好的执行计划是使用有效的索引来加速查询。
rows：- 指示MySQL估计需要检查的行数。- 这是根据统计信息和索引选择器的算法得出的估计值。
Extra：- 提供了额外的执行信息，帮助进一步理解查询执行的细节。- 可能的取值包括：Using index（仅使用索引进行查询）、Using where（使用WHERE子句进行过滤）、Using temporary（使用临时表）、Using filesort（使用文件排序）等。

这些字段提供了查询执行的详细信息，可以帮助开发人员了解查询的执行方式、访问模式以及是否存在潜在的性能问题。通过分析Explain执行计划结果集中的字段，您可以做出相应的优化决策，如创建适当的索引、重写查询或调整查询语句，以提高查询性能。

八、索引使用规则

8.1 最左前缀法则

最左前缀法则是指在使用联合索引进行查询时，必须从索引的最左列开始，并且不能跳过中间的列。如果跳过了某一列，那么索引将只能部分生效，后续字段的索引将会失效。

这个规则的原因是因为联合索引的存储方式是按照索引的多个列依次排序的。当查询时，数据库系统会根据索引的最左列进行查找，并按照索引的顺序逐渐向右查找，直到找到满足所有条件的数据或者无法再继续匹配。

如果我们跳过了某一列进行查询，那么在该列之后的列将无法按照索引的顺序进行查找，导致索引失效。这样就会导致数据库需要扫描更多的数据页来满足查询条件，进而降低查询性能。

因此，在使用联合索引进行查询时，应该遵守最左前缀法则，按照索引列的顺序进行查询，这样可以最大程度地利用索引提供的性能优势。如果需要对多个列进行灵活的查询，可以考虑创建更合适的索引或者使用其他查询优化手段来提高性能。

8.2 联合索引避免范围查询

当使用联合索引进行范围查询（<, >）时，范围查询右侧的列索引将失效。这是因为范围查询需要按照一定的顺序扫描索引，从而无法完全利用索引的有序性。

为了规避这个索引失效问题，可以考虑改用>=或者<=来代替范围查询。通过使用>=或者<=操作符，可以将范围查询转化为等值查询或者单值查询，从而使得整个联合索引仍然保持有效。

例如，如果要进行范围查询 col1 > 5 AND col2 < 10，可以改写为 col1 >= 5 AND col1 < x AND col2 < 10，其中 x 是大于 5 的一个值。这样，我们将范围查询拆分成两个等值查询，保证了联合索引的有效使用。

需要注意的是，在拆分范围查询时，我们需要根据具体情况选择合适的拆分点（比如上述例子中的 x 值），以保证查询结果的正确性和覆盖率。此外，拆分后的查询条件可能会增加一些逻辑复杂性，需要谨慎设计和测试。

8.3 SQL提示

USE INDEX：指示MySQL使用特定的索引来执行查询。
IGNORE INDEX：指示MySQL忽略特定的索引，而选择其他可用的索引来执行查询。
FORCE INDEX：强制MySQL使用特定的索引来执行查询，并忽略其他可能更适合的索引。

8.4 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。

explain 中 extra 字段含义：

using index condition

：查找使用了索引，但是需要回表查询数据

using where; using index;

：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如

select id, name from xxx where name='xxx';

，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如

select id, name, gender from xxx where name='xxx';

所以尽量不要用

select *

，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：

select id, username, password from tb_user where username='itcast';

解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

8.5 前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

create index idx_xxxx on table_name(columnn(n));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

求选择性公式：

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

8.6 单列索引与联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：

explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';

这句只会用到phone索引字段

注意事项

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

九、索引设计原则

设计原则

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

十、索引失效情况

10.1 索引列运算

在索引列上进行运算操作，索引将失效。如：

explain select * from tb_user where substring(phone, 10, 2) = '15';

索引列运算是指在查询条件或者索引创建时对索引列进行运算（如计算、函数操作等）。在某些情况下，索引列的运算可能导致索引失效。以下是一些常见的原因：

运算结果不可预测：当对索引列进行运算时，可能会改变列的原始值，导致无法准确匹配索引中的键值。例如，如果在查询条件中使用了函数操作，如 WHERE UPPER(column) = 'VALUE'，由于索引只存储原始的列值而非函数操作的结果，数据库无法直接利用索引进行高效的查找和筛选。
运算结果类型不匹配：索引是按照特定的数据类型进行排序和存储的。如果进行的运算导致结果的数据类型与索引列的数据类型不匹配，索引将无法被正确地使用。例如，如果对整型索引列进行字符串拼接操作，可能会导致无法使用索引来加速查询。
运算造成索引列无法比较顺序：索引的主要目的是提供有序性以便快速定位和筛选数据。若进行的运算导致索引列的顺序无法保持一致，索引将失去有序性，并且无法为查询提供优化。例如，如果在查询条件中使用了不可逆的哈希函数操作，将导致索引列的值无法进行有序比较。

为了避免索引失效的问题，应该尽量避免在查询条件或者索引创建时对索引列进行运算。如果确实需要使用运算，可以考虑以下解决方案：

对索引列进行逆转运算：如果运算是可逆的，可以通过将运算应用到查询参数上，而不是索引列上来维持索引的有效性。
使用函数索引：某些数据库管理系统提供了函数索引的功能，可以根据特定的函数操作创建索引，以满足特定的查询需求。

10.2 字符串不加引号

如果在查询条件或创建索引时字符串没有加上引号，可能会导致索引失效。以下是一些常见的原因：

数据类型不匹配：数据库中的字符串需要用引号括起来表示，而非引号括起来的值通常被视为其他数据类型（例如列名、函数名等）。如果在查询条件或创建索引时未正确使用引号，数据库可能无法正确匹配字符串的数据类型，导致索引失效。
字符串比较问题：数据库在进行字符串比较时，通常会依赖字符串的排序规则。如果字符串未加引号，数据库可能会将其解析为其他类型的数据，而非按照字符串的排序规则进行比较。这可能导致索引无法正确地匹配查询条件，进而导致索引失效。
语法错误：在SQL语句中，字符串通常需要用引号括起来作为合法的语法结构。如果未使用引号，可能会导致语法错误，使得数据库无法正确解析查询条件或创建索引，从而造成索引失效。

为了避免索引失效的问题，应确保在查询条件和创建索引时，所有的字符串值都要正确地用引号括起来。这样可以使数据库正确识别字符串类型，并按照字符串的排序规则进行比较和索引优化。同时，建议参考相关数据库的文档以了解具体的语法规则和最佳实践。

10.3 模糊查询

模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：

explain select * from tb_user where profession like '%工程';

，前后都有 % 也会失效。

以下是关于尾部模糊匹配和头部模糊匹配对索引失效的原因：

尾部模糊匹配：如果模糊查询的通配符（如 %）仅出现在搜索字符串的尾部，索引仍然可以有效利用。比如使用 LIKE 'abc%' 进行尾部匹配查询，这样数据库可以通过使用索引进行查找，并返回以 "abc" 开头的匹配结果。
头部模糊匹配：相反，如果模糊查询的通配符（如 %）出现在搜索字符串的开头，索引将会失效。例如，使用 LIKE '%abc' 进行头部匹配查询，在这种情况下，由于无法确定匹配值的起始位置，数据库无法有效地利用索引进行查找。

主要原因在于，索引是按照一定的顺序存储数据的，而模糊匹配的头部通配符使得需要遍历整个索引进行匹配，无法通过索引的有序性进行高效的定位和筛选。

10.4 or连接的条件

当使用OR操作符将多个条件组合在一起时，如果其中一个条件的列没有索引，那么涉及的索引不会被用到。这是由于以下原因：

索引选择性：数据库优化器通常会根据索引的选择性来决定是否使用该索引。选择性是指索引中不同值的唯一性程度。当一个条件的列没有索引时，其选择性会较低，也就是说它包含的不同值很少。在这种情况下，使用该条件的索引可能无法提供足够的过滤效果，导致查询优化器决策不使用索引。
查询计划的成本估算：数据库优化器在确定查询计划时，会根据每个可能的执行路径进行成本估算。如果其中一个条件的列没有索引，那么涉及的索引可能无法提供有效的过滤，从而使得使用索引的执行路径的成本估算较高。因此，优化器可能会选择不使用索引的其他执行路径。
逻辑结构：对于OR操作符，数据库需要对每个条件进行独立的评估，并将结果进行合并。如果其中一个条件的列没有索引，数据库可能需要扫描整个表来评估该条件，这与使用其他已有索引的条件形成了冲突。为了避免不必要的数据访问和合并操作，优化器可能会选择不使用任何索引。

为了解决这个问题，可以考虑以下方案：

确保所有涉及的条件列都有适当的索引，以提高查询性能。
对于大型表，可以考虑重构查询，将OR操作符拆分成多个独立的查询，并使用UNION或UNION ALL来合并结果。这样可以确保每个子查询都能够使用适当的索引，并避免OR操作符导致的索引失效问题。

10.5 数据分布影响

当MySQL评估使用索引比全表扫描更慢时，会选择不使用索引。以下是一个例子：

对于一个学生表，如果包含列

info

，并且大部分记录的

info

字段为空，并且该列设置了索引，当执行以下查询时：

SELECT * FROM student WHERE info IS NULL;

在这种情况下，MySQL的优化器可能会选择不使用该列的索引。

原因如下：

索引选择性差：由于大部分记录的info字段为空，索引列的选择性非常低。索引的选择性是指不同值的唯一性程度。当一个列的选择性非常低时，意味着索引无法提供很好的过滤效果。优化器可能会认为全表扫描比使用索引更高效，因为使用索引进行查找和访问数据块的成本可能更高。
数据访问成本估算：由于大部分记录的info字段为空，在使用该列的索引进行范围扫描时，可能需要访问大量的数据块，这样会增加查询的成本。考虑到整体数据的分布情况，优化器可能会认为直接进行全表扫描的成本更低。

基于以上原因，MySQL优化器可能会选择不使用该列的索引，而是通过全表扫描来查找

info

为空的记录。

标签： mysql 数据库

本文转载自: https://blog.csdn.net/weixin_55772633/article/details/132215904
版权归原作者 Maiko Star 所有，如有侵权，请联系我们删除。

MySQL 索引 详解（保姆级教程）