MySQL 索引 详解（保姆级教程）
一、索引概述https://i-blog.csdnimg.cn/blog_migrate/550cde14f23d8344345f24594c960918.png
索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。
二、索引的优缺点
https://i-blog.csdnimg.cn/blog_migrate/2b13845b7f2247f61a523bba0f0c95d3.png
优点：
[*]提高数据检索效率，降低数据库的IO成本
[*]通过索引列对数据举行排序，降低数据排序的成本，降低CPU的消耗
缺点：
[*]索引列也是要占用空间的
[*]索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE
三、索引结构
3.1 索引结构介绍
https://i-blog.csdnimg.cn/blog_migrate/8187ef215935d5e021452ee64667ee13.png
索引结构描述B+Tree最常见的索引范例，大部分引擎都支持B+树索引（默认）Hash底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询R-Tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引范例，重要用于地理空间数据范例（通常使用较少）Full-Text(全文索引)是一种通过创建倒排索引，快速匹配文档的方式，雷同于 Lucene, Solr, ES（通常使用较少） 3.2 差别存储引擎对索引支持情况https://i-blog.csdnimg.cn/blog_migrate/bcb5f101fc790116a219569eb6b9c108.png
索引InnoDBMyISAMMemoryB+Tree索引支持支持支持Hash索引不支持不支持支持R-Tree索引不支持支持不支持Full-text5.6版本后支持支持不支持 我们寻常所说的索引，假如没有特殊指明，都是指B+树结构组织的索引。
四、数据结构介绍（二叉树、红黑树、Btree、B+tree）
4.1 二叉树
二叉树（Binary Tree）是一种特殊的树状数据结构，其中每个节点最多有两个子节点，分别称为左子节点和右子节点。二叉树的定义如下：
一个二叉树可以为空（即没有节点），或者由一个根节点和两颗分别称为左子树和右子树的二叉树组成。
二叉树的特点：
[*]每个节点最多有两个子节点，分别为左子节点和右子节点。
[*]左子树和右子树也是二叉树，可以为空。
[*]二叉树的子节点没有特定的顺序，可以根据具体应用决定左右子节点的位置。
https://i-blog.csdnimg.cn/blog_migrate/8b4473dc91521a5f69256cdb49f31b92.png
4.2 红黑树
红黑树（Red-Black Tree）是一种自平衡的二叉查找树，它在插入和删除操作后通过重新安排节点的颜色来保持平衡。红黑树的名称来源于每个节点上的颜色标记，每个节点可以是红色或玄色。
红黑树具有以下特点：
[*]每个节点要么是红色，要么是玄色。
[*]根节点是玄色的。
[*]所有叶子节点（NIL节点）都是玄色的。
[*]假如一个节点是红色的，则其两个子节点都是玄色的。
[*]对于任意节点，从该节点到其所有子女叶子节点的简朴路径上，均包罗雷同数目的玄色节点。
这些特点确保了红黑树的关键性质，即从根节点到任何叶子节点的最长路径不会超过最短路径的两倍，从而保持了树的平衡性。这种平衡性使得红黑树在实际应用中非常高效，常被用作聚集、映射等数据结构的底子。
https://i-blog.csdnimg.cn/blog_migrate/405bab02c8531c204a47c022b5348e26.png
红黑树缺点：大数据量情况下，层级较深，检索速度慢的题目。
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并不仅限于二叉树的结构，每个节点可以包罗多个子节点，使其实用于处理大规模数据集的情况。
https://i-blog.csdnimg.cn/blog_migrate/8e30040889d9d8b0567bed76d29c59d7.png
B-Tree 的数据插入过程动画参照：Data Structure Visualization
B-Tree Visualization （假如上面演示链接打不开，请更换）
https://i-blog.csdnimg.cn/blog_migrate/9631ef5727979e13da09df85aa99fe8f.png
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+树通常被用作数据库系统的索引结构，特殊实用于支持范围查询和按顺序访问数据的场景。它的平衡性和磁盘友爱性使得在大规模数据集的存储和检索过程中具有良好的性能表现。
https://i-blog.csdnimg.cn/blog_migrate/34c979b5179e02eead087a6343ab81ef.png B+Tree 的数据插入过程动画参照：Data Structure Visualization
B+ Tree Visualization（假如上面演示链接打不开，请更换）
https://i-blog.csdnimg.cn/blog_migrate/3d30eeeae7a856f3846c896318dd6d25.png
MySQL 索引数据结构对经典的 B+Tree 举行了优化。在原 B+Tree 的底子上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。https://i-blog.csdnimg.cn/blog_migrate/ed1b085ed82d0df6b03186cc0f25d1ff.png
4.5 Hash 哈希索引
哈希索引（Hash Index）是一种在数据库中用于快速查找数据的索引结构。它通过将关键字（Key）通过散列函数（Hash Function）转换成一个固定长度的散列值（Hash Value），然后将这个散列值与存储位置创建映射关系，从而实现高效的数据查找。
哈希索引的重要特点包罗：
[*] 快速查找：哈希索引通过使用散列函数将关键字映射到存储位置，可以在常数时间内直接访问目标数据，因此具有非常高的查找效率。
[*] 相称查询优化：哈希索引实用于相称比力查询（例如WHERE column = value），对于这类查询，只须要计算散列值并举行一次查找即可，不须要遍历整个索引。
[*] 不支持范围查询和排序：由于哈希索引是基于散列值举行查找的，因此不支持范围查询（例如WHERE column > value）和排序操作。
[*] 辩论处理：由于散列函数将差别的关键字映射到雷同的散列值大概性存在，这种情况称为哈希辩论。常见的办理辩论的方法包罗开放地点法和链表法。
须要留意的是，哈希索引在某些场景下大概结果不如B树索引，因为它无法支持范围查询和排序操作，并且对于存在大量辩论的情况下性能大概会降落。因此，在选择索引范例时须要根据具体的业务需求和数据特点举行综合考虑。
https://i-blog.csdnimg.cn/blog_migrate/3ce8bdab65ebdab0f43931773255903d.png
哈希索引就是接纳肯定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
假如两个（或多个）键值，映射到一个雷同的槽位上，他们就产生了hash辩论（也称为hash碰撞），可以通过链表来办理。
https://i-blog.csdnimg.cn/blog_migrate/6c758a64af9751fe4b9162e16a29603d.png
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 索引分类
https://i-blog.csdnimg.cn/blog_migrate/37d91d13b012d06d6268b6af5440cb06.png
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词，而不是比力索引中的值可以有多个FULLTEXT
5.2 InnoDB存储引擎索引分类
在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：https://i-blog.csdnimg.cn/blog_migrate/ad0dcb9396a927880ef63ca122b25d5d.png
分类含义特点聚集索引(Clustered Index)将数据存储与索引放一块，索引结构的叶子节点生存了行数据必须有，而且只有一个二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个 聚集索引选取规则：
[*]假如存在主键，主键索引就是聚集索引
[*]假如不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
[*]假如表没有主键或没有合适的唯一索引，则 InnoDB 会自动天生一个 rowid 作为隐蔽的聚集索引
假设user表的id字段为聚集索引，name字段为二级索引，那么select * from user where name = 'Arm'的查询顺序如下：
会先到二级索引中查询name = Arm的数据，查询到name=Arm的id为10，然后再去聚集索引中查询id=10的数据（聚集索引中存放的是这一行的行数据）流程图如下：
https://i-blog.csdnimg.cn/blog_migrate/e561a104e8006eb0f4f3a5c8d9aed035.png
思考题
以下 SQL 语句，哪个执行效率高？为什么？
select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引
答：第一条语句，因为第二条须要回表查询，相当于两个步骤。
六、索引的使用（创建、查看、删除）
https://i-blog.csdnimg.cn/blog_migrate/d504f80ef1bc983297ce06959c909776.png
创建索引：
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执行频率（了解）
https://i-blog.csdnimg.cn/blog_migrate/ace0ae790e8e3b7b95e47070a451e3fb.png
My5QL客户端毗连成功后，通过show 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 慢查询日志（了解）
https://i-blog.csdnimg.cn/blog_migrate/1f230f88b44c0c586c5aff8b51d719a6.png
MySQL慢查询日志是一种记录执行时间超过特定阈值的SQL查询语句的日志。它可以帮助您辨认和优化数据库中的性能瓶颈。
要启用MySQL慢查询日志，您须要执行以下步骤：
1. 打开MySQL设置文件（通常是`my.cnf`或`my.ini`）。您可以在以下位置找到该文件：
[*]Linux：/etc/mysql/my.cnf或/etc/my.cnf
[*]Windows：MySQL安装目录下的my.ini
2. 在设置文件中找到 `` 部分，假如不存在，则在文件末尾添加该部分。
3. 在 `` 部分中添加以下行来启用慢查询日志并设置阈值（以秒为单位）：
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：指定执行时间超过多少秒的查询被以为是慢查询。这个值根据您的应用需求举行调整。
4. 生存并关闭设置文件。
5. 重启MySQL服务，以使设置更改生效。
6. 如今，MySQL慢查询日志已启用。执行时间超过阈值的查询语句将被记录在指定的日志文件中。
要查看慢查询日志，可以使用文本编辑器打开指定的日志文件（`/path/to/slow-query.log`），以查看其中记录的慢查询语句和相关信息。
请留意，启用慢查询日志大概会对数据库性能产生肯定影响，因此在生产环境中应谨慎使用，并根据须要举行适当的设置和管理。
7.3 profile详情（查看SQL执行时间）（了解）
https://i-blog.csdnimg.cn/blog_migrate/03f4499328181f358ed6896ee7ba63b8.pnghttps://i-blog.csdnimg.cn/blog_migrate/38a5b9a62b21df6071f674fb17640618.png
在MySQL中，查询的"profiling"功能可以用于跟踪和分析查询的性能。当启用查询性能分析后，MySQL将记录每个查询的详细执行统计信息。
要启用查询的profiling功能，并查看查询的详细执行统计信息，请按照以下步骤举行操作：
1. 打开MySQL客户端或使用适当的MySQL图形用户界面工具毗连到数据库。
2. 在会话中，执行以下命令以启用查询的profiling功能：
SET profiling = 1;
3. 然后，执行您希望分析的查询语句。
4. 查询执行完成后，使用以下命令查看查询的profiling详情：
SHOW PROFILES;
这将显示一个包罗所有查询的列表，每个查询都有一个唯一的查询ID。
5. 选择要查看其profiling详情的查询，使用以下命令：
SHOW PROFILE FOR QUERY <query_id>;
将 `<query_id>` 替换为您要查看的查询的实际查询ID。
这将显示与所选查询相关的详细执行统计信息，包罗查询的执行时间、扫描行数、临时表创建等等。
6. 查看完查询的profiling详情后，可以使用以下命令来停止profiling并清除已记录的查询信息：
SET profiling = 0;
请留意，启用查询的profiling大概会对性能产生肯定的影响。因此，应仅在须要详细分析查询性能时才启用profiling，并及时停止profiling以避免不须要的开销。
7.4 explain执行筹划（重要）
https://i-blog.csdnimg.cn/blog_migrate/c81673d5d61b29aa612930394810639d.png
Explain执行筹划是MySQL中的一个命令，用于获取查询语句的执行筹划。执行筹划显示了MySQL优化器在处理查询时选择的操作顺序、使用的索引、数据访问方式等详细信息。https://i-blog.csdnimg.cn/blog_migrate/80dec24ac3ff2158950db5f32b133200.png
https://i-blog.csdnimg.cn/blog_migrate/da5b79088d5e70869eccde1850eed722.png
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 最左前缀法则
https://i-blog.csdnimg.cn/blog_migrate/b1c4d00e1a253725b56246b63691fbeb.png
最左前缀法则是指在使用团结索引举行查询时，必须从索引的最左列开始，并且不能跳过中间的列。假如跳过了某一列，那么索引将只能部分生效，后续字段的索引将会失效。
这个规则的原因是因为团结索引的存储方式是按照索引的多个列依次排序的。当查询时，数据库系统会根据索引的最左列举行查找，并按照索引的顺序逐渐向右查找，直到找到满足所有条件的数据或者无法再继续匹配。
假如我们跳过了某一列举行查询，那么在该列之后的列将无法按照索引的顺序举行查找，导致索引失效。这样就会导致数据库须要扫描更多的数据页来满足查询条件，进而降低查询性能。
因此，在使用团结索引举行查询时，应该遵守最左前缀法则，按照索引列的顺序举行查询，这样可以最大水平地利用索引提供的性能上风。假如须要对多个列举行机动的查询，可以考虑创建更合适的索引或者使用其他查询优化本领来提高性能。
8.2 团结索引避免范围查询
https://i-blog.csdnimg.cn/blog_migrate/c51454c5e691fb6edf25a0339154cfe1.png
当使用团结索引举行范围查询（<, >）时，范围查询右侧的列索引将失效。这是因为范围查询须要按照肯定的顺序扫描索引，从而无法完全利用索引的有序性。
为了规避这个索引失效题目，可以考虑改用>=或者<=来取代范围查询。通过使用>=或者<=操作符，可以将范围查询转化为等值查询或者单值查询，从而使得整个团结索引仍然保持有效。
例如，假如要举行范围查询 col1 > 5 AND col2 < 10，可以改写为 col1 >= 5 AND col1 < x AND col2 < 10，其中 x 是大于 5 的一个值。这样，我们将范围查询拆分成两个等值查询，保证了团结索引的有效使用。
须要留意的是，在拆分范围查询时，我们须要根据具体情况选择合适的拆分点（比如上述例子中的 x 值），以保证查询结果的正确性和覆盖率。此外，拆分后的查询条件大概会增加一些逻辑复杂性，须要谨慎设计和测试。
8.3 SQL提示
https://i-blog.csdnimg.cn/blog_migrate/46c915d0cc9da4551b14c1852f97e846.png
[*]USE INDEX：指示MySQL使用特定的索引来执行查询。
[*]IGNORE INDEX：指示MySQL忽略特定的索引，而选择其他可用的索引来执行查询。
[*]FORCE INDEX：强制MySQL使用特定的索引来执行查询，并忽略其他大概更适合的索引。
8.4 覆盖索引
https://i-blog.csdnimg.cn/blog_migrate/ed8e1eb06993f328a9a21df30c7c37c8.png
尽量使用覆盖索引（查询使用了索引，并且须要返回的列，在该索引中已经全部能找到），淘汰 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 前缀索引
https://i-blog.csdnimg.cn/blog_migrate/58a3b202c8558a57fadd8f7766b745f2.png
当字段范例为字符串（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 单列索引与团结索引
https://i-blog.csdnimg.cn/blog_migrate/9bf7cd0ecc01177c5e557cbe52ba31be.png
单列索引：即一个索引只包罗单个列
团结索引：即一个索引包罗了多个列
在业务场景中，假如存在多个查询条件，考虑针对于查询字段创建索引时，建议创建团结索引，而非单列索引。
单列索引情况：
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
这句只会用到phone索引字段
留意事项
[*]多条件团结查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询
九、索引设计原则
https://i-blog.csdnimg.cn/blog_migrate/3863db7d22e62a8d7f8329d055ed5bab.png
设计原则
[*]针对于数据量较大，且查询比力频仍的表创建索引
[*]针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段创建索引
[*]尽量选择区分度高的列作为索引，尽量创建唯一索引，区分度越高，使用索引的效率越高
[*]假如是字符串范例的字段，字段长度较长，可以针对于字段的特点，创建前缀索引
[*]尽量使用团结索引，淘汰单列索引，查询时，团结索引很多时间可以覆盖索引，节省存储空间，避免回表，提高查询效率
[*]要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增编削的效率
[*]假如索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包罗NULL值时，它可以更好地确定哪个索引最有效地用于查询
十、索引失效情况
10.1 索引列运算
https://i-blog.csdnimg.cn/blog_migrate/42c362a308fbe827fe7444fcd5c32c4a.png
在索引列上举行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
索引列运算是指在查询条件或者索引创建时对索引列举行运算（如计算、函数操作等）。在某些情况下，索引列的运算大概导致索引失效。以下是一些常见的原因：
[*] 运算结果不可预测：当对索引列举行运算时，大概会改变列的原始值，导致无法正确匹配索引中的键值。例如，假如在查询条件中使用了函数操作，如 WHERE UPPER(column) = 'VALUE'，由于索引只存储原始的列值而非函数操作的结果，数据库无法直接利用索引举行高效的查找和筛选。
[*] 运算结果范例不匹配：索引是按照特定的数据范例举行排序和存储的。假如举行的运算导致结果的数据范例与索引列的数据范例不匹配，索引将无法被正确地使用。例如，假如对整型索引列举行字符串拼接操作，大概会导致无法使用索引来加速查询。
[*] 运算造成索引列无法比力顺序：索引的重要目的是提供有序性以便快速定位和筛选数据。若举行的运算导致索引列的顺序无法保持一致，索引将失去有序性，并且无法为查询提供优化。例如，假如在查询条件中使用了不可逆的哈希函数操作，将导致索引列的值无法举行有序比力。
为了避免索引失效的题目，应该尽量避免在查询条件或者索引创建时对索引列举行运算。假如确实须要使用运算，可以考虑以下办理方案：
[*]对索引列举行逆转运算：假如运算是可逆的，可以通过将运算应用到查询参数上，而不是索引列上来维持索引的有效性。
[*]使用函数索引：某些数据库管理系统提供了函数索引的功能，可以根据特定的函数操作创建索引，以满足特定的查询需求。
10.2 字符串不加引号
https://i-blog.csdnimg.cn/blog_migrate/d56940fd6393b4995ef26a06b24583e8.png
假如在查询条件或创建索引时字符串没有加上引号，大概会导致索引失效。以下是一些常见的原因：
[*] 数据范例不匹配：数据库中的字符串须要用引号括起来表现，而非引号括起来的值通常被视为其他数据范例（例如列名、函数名等）。假如在查询条件或创建索引时未正确使用引号，数据库大概无法正确匹配字符串的数据范例，导致索引失效。
[*] 字符串比力题目：数据库在举行字符串比力时，通常会依靠字符串的排序规则。假如字符串未加引号，数据库大概会将其解析为其他范例的数据，而非按照字符串的排序规则举行比力。这大概导致索引无法正确地匹配查询条件，进而导致索引失效。
[*] 语法错误：在SQL语句中，字符串通常须要用引号括起来作为正当的语法结构。假如未使用引号，大概会导致语法错误，使得数据库无法正确解析查询条件或创建索引，从而造成索引失效。
为了避免索引失效的题目，应确保在查询条件和创建索引时，所有的字符串值都要正确地用引号括起来。这样可以使数据库正确辨认字符串范例，并按照字符串的排序规则举行比力和索引优化。同时，建议参考相关数据库的文档以了解具体的语法规则和最佳实践。
10.3 模糊查询
https://i-blog.csdnimg.cn/blog_migrate/269db975ea86c386b46da1a541a3ccdb.png
模糊查询中，假如仅仅是尾部模糊匹配，索引不会是失效；假如是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。
以下是关于尾部模糊匹配和头部模糊匹配对索引失效的原因：
[*] 尾部模糊匹配：假如模糊查询的通配符（如 %）仅出如今搜索字符串的尾部，索引仍然可以有效利用。比如使用 LIKE 'abc%' 举行尾部匹配查询，这样数据库可以通过使用索引举行查找，并返回以 "abc" 开头的匹配结果。
[*] 头部模糊匹配：相反，假如模糊查询的通配符（如 %）出如今搜索字符串的开头，索引将会失效。例如，使用 LIKE '%abc' 举行头部匹配查询，在这种情况下，由于无法确定匹配值的起始位置，数据库无法有效地利用索引举行查找。
重要原因在于，索引是按照肯定的顺序存储数据的，而模糊匹配的头部通配符使得须要遍历整个索引举行匹配，无法通过索引的有序性举行高效的定位和筛选。
10.4 or毗连的条件
https://i-blog.csdnimg.cn/blog_migrate/a5247e103ca0b40d08cceba35cd63e15.png
当使用OR操作符将多个条件组合在一起时，假如其中一个条件的列没有索引，那么涉及的索引不会被用到。这是由于以下原因：
[*] 索引选择性：数据库优化器通常会根据索引的选择性来决定是否使用该索引。选择性是指索引中差别值的唯一性水平。当一个条件的列没有索引时，其选择性会较低，也就是说它包罗的差别值很少。在这种情况下，使用该条件的索引大概无法提供足够的过滤结果，导致查询优化器决策不使用索引。
[*] 查询筹划的成本估算：数据库优化器在确定查询筹划时，会根据每个大概的执行路径举行成本估算。假如其中一个条件的列没有索引，那么涉及的索引大概无法提供有效的过滤，从而使得使用索引的执行路径的成本估算较高。因此，优化器大概会选择不使用索引的其他执行路径。
[*] 逻辑结构：对于OR操作符，数据库须要对每个条件举行独立的评估，并将结果举行合并。假如其中一个条件的列没有索引，数据库大概须要扫描整个表来评估该条件，这与使用其他已有索引的条件形成了辩论。为了避免不须要的数据访问和合并操作，优化器大概会选择不使用任何索引。
为了办理这个题目，可以考虑以下方案：
[*]确保所有涉及的条件列都有适当的索引，以提高查询性能。
[*]对于大型表，可以考虑重构查询，将OR操作符拆分成多个独立的查询，并使用UNION或UNION ALL来合并结果。这样可以确保每个子查询都可以或许使用适当的索引，并避免OR操作符导致的索引失效题目。
10.5 数据分布影响
https://i-blog.csdnimg.cn/blog_migrate/d74b28fdd3c8c186e47e21b698b7c4bd.png
当MySQL评估使用索引比全表扫描更慢时，会选择不使用索引。以下是一个例子：
对于一个学生表，假如包罗列info，并且大部分记录的info字段为空，并且该列设置了索引，当执行以下查询时：
SELECT * FROM student WHERE info IS NULL;
在这种情况下，MySQL的优化器大概会选择不使用该列的索引。
原因如下：
[*] 索引选择性差：由于大部分记录的info字段为空，索引列的选择性非常低。索引的选择性是指差别值的唯一性水平。当一个列的选择性非常低时，意味着索引无法提供很好的过滤结果。优化器大概会以为全表扫描比使用索引更高效，因为使用索引举行查找和访问数据块的成本大概更高。
[*] 数据访问成本估算：由于大部分记录的info字段为空，在使用该列的索引举行范围扫描时，大概须要访问大量的数据块，这样会增加查询的成本。考虑到整体数据的分布情况，优化器大概会以为直接举行全表扫描的成本更低。
基于以上原因，MySQL优化器大概会选择不使用该列的索引，而是通过全表扫描来查找info为空的记录。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]