[MySQL]2-MySQL索引

目录

1.索引🌟

1.1索引结构

B树

B+树

聚簇索引（一级索引）与非聚簇索引（二级索引）

回表操作

1.2索引碎片

清理索引碎片的方法

1.3索引匹配方式🌟

在数据列上使用函数或者计算会导致索引失效的原因

匹配列前缀

前缀索引创建

全值匹配

索引覆盖

最左匹配原则

匹配范围值

精确匹配某一列而范围匹配另外一列

1.4全文索引

1.5创建索引🌟

慎重建立索引

为长字符串建立前缀索引

联合索引的顺序

1.6索引优化🌟

建立索引

调整索引

删除未使用索引

调整选择性比较低的索引

谨慎使用索引统计

减少碎片

1.7索引创建原则

2.EXPLAIN

2.1explain output 执行计划输出解释

2.2概括

2.3重点🌟

type列（连接类型，判断索引使用情况）

extra （额外信息，看SQL执行情况）

2.4MySQL执行优化

is null优化

Optimizing Subqueries with Materialization物化优化子查询

Index Merge索引合并优化

order by优化

gorup by优化

索引下推ICP 优化

---

1.索引🌟

排好序的用于快速查找数据结构。理解索引时如何工作的非常重要，应该根据裂解来创建合适的索引，而不是更具“在多列索引中将选择性最高的列放在第一列”这样的经验法则。

1.1索引结构

MySQL索引接口从B树-->B+树。

机械硬盘数据读取：从磁盘的磁片的磁道中获取数据。

B树

缺点：

范围查询力不从心；

查询效率不稳定，距离根节点越近越快；

树高度有下降空间。

B+树

MySQL B+Tree结构，节点为16KB的页，2000w的数据一般也只需要3层就可以了。

非叶子节点的16KB页只保留索引列信息、下一页地址信息；

将所有数据存储在底层的叶子节点16KB页，从而保证每次查询都到叶子节点，效率稳定；

叶子节点从小到大排序，很好范围查询。

聚簇索引（一级索引）与非聚簇索引（二级索引）

聚簇索引的非叶子节点，记录的是行的key——主键key或者Unionkey创建，或者隐藏的row_id

叶子节点记录行数据

非聚簇索引的非叶子节点，记录索引值

叶子节点，记录聚簇索引的key

补充：MySQL唯一限制和主键限制都是通过索引实现的

回表操作

二级索引树查询完返回一级索引树获取叶子节点行数据，这个操作就叫回表。

1.2索引碎片

频繁插入、更新、删除操作会导致索引页中数据分布不连续，形成未使用的空间或数据块。这种情况会导致查询性能下降，因为需要更多次的磁盘IO来访问数据。

清理索引碎片的方法

使用optimize table table_bame语句，来重新组织表中的数据和索引。

也可以重新建表，或者第三方工具。

建议定期检查并清理索引碎片。优化表是一个耗费资源的操作，需要选择合适的时间窗口执行。

1.3索引匹配方式🌟

B树（B+树）索引主要用于精确匹配和范围查询。

索引减少了服务器需要扫描的数据量、避免排序和临时表、将随机IO变成顺序IO，大大提升了SQL执行效率。

但不符合匹配方式就容易出现索引失效的情况。

在数据列上使用函数或者计算会导致索引失效的原因

匹配列前缀

可以匹配某一列的值的开头部分，比如使用LIKE 'prefix%'来进行搜索。这种情况下，只要前缀是固定的，就可以用到索引。

前缀索引创建

在为长字符串列创建前缀索引时，前缀索引的长度最好具备良好的选择性。详情见1.5创建索引-为长字符串建立前缀索引

全值匹配

查询条件完全匹配索引

索引覆盖

一个查询所需要的所有数据都可以从索引中获得，而无需访问实际的数据行。

最左匹配原则

使用联合索引时，第一个条件列需要匹配索引列。

且设计联合索引时，应该将选择性高的列放在左边，即列的值范围大的（比较分散的）放在左边。即按照选择性高低进行排列。

order by适合组合索引。

值得注意的是：尽管联合索引依据最左匹配原则可以给最左列左索引，但是最左列的单列索引效率更高。

匹配范围值

可以查找某一个范围的数据，例如>、

注意：使用IN操作符可以被视为多个等值比较的组合，可以有效使用索引，且后续索引列不会失效。

精确匹配某一列而范围匹配另外一列

可以在查询第一列的全部的同时对第二列进行范围匹配

1.4全文索引

CHAR、VARCHAR、TEXT类型可以创建有别于B树和哈希索引的全文索引。

全文索引允许用户对存储在数据库中的大块文本数据进行快速查找。它可以处理整本书、文章或任何其他形式的大段文字，并能根据需要获取其中的章节、段落、句子或单词等信息。这种类型的索引通常通过倒排索引来实现，即记录了每个单词出现在文档集合中的位置信息。

补充：现在使用Elasticsearch的比较多。

1.5创建索引🌟

解释：

1、主键会自动建立唯一索引，主键索引有唯一性，不要重复建立索引。

2、频繁更新、删除的列建立索引性能比较低，因为会经常变更索引树，insert语句会变慢，且经常删除会导致索引碎片，进而导致查询效率降低。

3、不要建立未使用的索引，因为每个增加的索引都会占据索引空间，增加存储成本，insert和update操作都需要维护索引树，效率会变低。

4、组合索引最左匹配原则可以视作最左列也有索引，但是性能比不上单列索引，因为索引更大，磁盘的IO操作更多，按需加载在缓存中占据的内存也更大。

5、接住索引本身的顺序完成排序

慎重建立索引

1、表记录很少，经常增删改的数据表需要谨慎创建索引。

2、建议单表不要超过五个索引。

表中的索引越多，插入数据就越慢

3、因为非聚簇索引依赖聚簇索引，如果选定的主键索引较大，其他的索引也会很大。当表中索引较多的时候，主键应当尽量小。

4、经历不要建立重复索引

为长字符串建立前缀索引

字符串类型的列的包含整个列值的索引太大了，影响性能并消耗大量磁盘空间。通常选择索引列前N个字符建立前缀索引。

前缀索引需要计算完整列的选择性以确认长度，这个长度需要让在这个长度下的数据分布的很均匀。

需要注意的是：前缀索引无法order by与group by以及覆盖扫描。

联合索引的顺序

设计联合索引时，应该将选择性高的列放在左边，即列的值范围大的（比较分散的）放在左边。即按照选择性高低进行排列。

1.6索引优化🌟

建立索引

建立合适索引，并匹配索引。

调整索引

用扫描出SQL语句，然后重新分配建立合适索引列（选择性高的列）。

合适索引数量，比如创建联合索引、适当冗余索引。

删除未使用索引

-- 查找没使用过的索引

select * from sys.schema_unused_indexes;

调整选择性比较低的索引

使用语句会给MySQL带来额外压力，注意使用时间。

基于INFORMATION_SCHEMA表可以查询选择性比较低的索引，或者和前缀匹配一样，做一个统计。

谨慎使用索引统计

InnoDB引擎时，使用SHOW TABLE STATUS 和 SHOW INDEX，会出发统计信息的更新，给服务器带来IO压力，

减少碎片

optimize table_name;可以整理索引空间碎片

不支持的optimize的可以使用不做任何操作的alter操作重建表

数据碎片有三种类型，比索引更复杂

行碎片：

数据行被存储在多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降。

1.7索引创建原则

2.EXPLAIN

2.1explain output 执行计划输出解释

https://dev.mysql.com/doc/refman/5.7/en/

https://dev.mysql.com/doc/refman/5.7/en/explain.html

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

官方解释是：EXPLAIN语句提供MySQL如何执行语句的信息。

可以查看应在哪些表添加索引以通过索引查找行来加快语句执行速度；

检查是否以最佳顺序连接表（如对SQL有理解，用户可以使用 SELECT STRAIGHT_JOIN指定连接顺序）。

2.2概括

explain output columns 列名	json name 使用format=json时输出中显示的效属性名称	meaning含义	详细解释
id	select_id	The SELECT identifier select标识符	这是查询中 SELECT 的顺序号。 id 号每个号码，表示一趟独立的查询 , 一个 sql 的查询趟数越少越好 如果该行引用其他行的并集结果，则值可以是 NULL 。在这种情况下， table 列显示一个类似于 的值，以指示该行引用具有 id 的 M 和 N 值的行并集。 可以查看执行顺序。 多表联查，id值越大，表查询越先执行。 id值相同，看输出顺序
select_type	None    无	The SELECT type    SELECT 类型	查询类型，说明单表查询、关联查询、子查询等信息
table	table_name	The table for the output row 输出行所引用的表	表名。 explain输出的每条记录都对应着都某个单表的访问方法。 M,N>该行指的是具有 M 和 N 的 id 值的行的并集 N>指的是具有 id 值为 N 的行的派生表结果。例如，派生表可能由 FROM 子句中的子查询生成 N>指的是具有 id 值为 N 的行的物化子查询的结果
partitions	partitions	The matching partitions    匹配分区	显示分区，对于非分区表值为null
type	access_type	The join type    连接类型	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types 描述了如何连接表。以下连接类型按照最好到最差排列： system 该表只有一行（=系统表）。const连接类型的特殊情况 const 该表最多一行匹配，该行在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器其余部分视为常数。const 表非常快，因为它们只读取一次。 eq_ref当索引的所有部分都被连接使用且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，使用此类型。 通常发生在连表查询中，关联的条件是某一张表的主键或者UNIQUE唯一非空索引。 ref ref 可用于使用 = 或 操作符比较的索引列。如果连接不能根据键值选择单行，则使用 ref 。 满足索引最左匹配原则，且不走主键或者唯一非空索引则为ref，非聚簇索引查询一般都为ref。 fulltext 使用fulltext索引进行连接操作 ref_or_null 类似于 ref ，但增加了 MySQL 对包含 NULL 值的行的额外搜索。这种连接类型优化最常用于解决子查询。 在ref基础上增加null值查询则为这个。 index_merge 此连接类型表示使用了索引合并优化。 一般是单表多个索引条件的查询，将多个搜索结果合并为一个，统一回表降低查询代价。 unique_subquery这种类型替换了以下形式的某些 IN 子查询中的 eq_ref。unique_subquery 是一个索引查找函数，它完全替换子查询以提高效率。 index_subquery 此连接类型类似于 unique_subquery 。它替换 IN 子查询，但适用于以下形式的子查询中的非唯一索引： range 仅检索给定范围内的行，使用索引选择行. key_len 包含使用的最长键部分。 ref 列是针对此类型的 NULL 当键列使用任何 = 、 <> 、 > 、 >= 、 < 、 、 BETWEEN 、 LIKE 或 IN() 运算符与常量进行比较时，可以使用 range index index 连接类型与 ALL类似，扫描索引树 ALL 全表扫描，尽量避免
possible_keys	possible_keys	The possible indexes to choose 可能的索引选择	表示 MySQL 可以从其中选择以查找此表中行的索引
key	key	The index actually chosen 实际选择的索引	表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用 possible_keys 索引之一来查找行，则该索引被列为键值。
key_len	key_length	The length of the chosen key 所选索引的长度	表示 MySQL 决定使用的键的长度（即：字节数）。 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
ref	ref	The columns compared to the index 列与索引的比较	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	rows	Estimate of rows to be examined 估计要检查的行数	表示 MySQL 认为必须检查的行数以执行查询。对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确。 值越小越好
filtered	filtered	Percentage of rows filtered by table condition 行过滤比例	表示由表条件过滤的表行估计百分比
Extra	None    无	Additional information    附加信息	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information 查询type为const时为null distinct MySQL 正在寻找不同的值，因此它在找到第一个匹配的行之后，就会停止为当前行组合搜索更多行。 Full scan on NULL key这发生在子查询优化中，作为当优化器无法使用索引查找访问方法时的后备策略。 Impossible HAVING HAVING 子句始终为假，无法选择任何行。 Impossible WHERE WHERE 子句始终为假，无法选择任何行。 Using filesortMySQL 必须进行额外遍历来确定如何以排序顺序检索行。排序是通过遍历所有行（根据连接类型）并存储所有匹配 WHERE 子句的行的排序键和行指针来完成的。然后对这些键进行排序，并按排序顺序检索行。请参阅第 8.2.1.14 节，“ORDER BY 优化”。 using index 该列信息仅通过索引树中的信息从表中检索，无需额外查找实际行。当查询仅使用单个索引中的列时，可以使用此策略。 using index condition 表通过访问索引元组并首先测试它们来确定是否读取完整的表行。这样，索引信息被用来延迟（“推下”）读取完整的表行，除非这是必要的。 Using index for group-by 与 Using index 表访问方法类似， Using index for group-by 表示 MySQL 找到一个可以用来检索 GROUP BY 或 DISTINCT 查询的所有列的索引，而无需对实际表进行任何额外的磁盘访问 Using MRR使用多范围读取优化策略读取 Using temporary为了解决查询，MySQL 需要创建一个临时表来存储结果。这通常发生在查询包含 GROUP BY 和 ORDER BY 子句，这些子句以不同的方式列出列时。 Using where 一个 WHERE 子句用于限制与下一个表匹配的行或发送给客户端的行。除非你明确打算检索或检查表中的所有行，否则如果 Extra 值不是 Using where ，并且表连接类型是 ALL 或 index ，你的查询可能有问题。

2.3重点🌟

type列（连接类型，判断索引使用情况）

结果值从最好到最坏依次是： system > const > eq_ref> ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。

range效率不高

index和all都是全表扫描

extra （额外信息，看SQL执行情况）

using index 覆盖索引 查询的数据都有索引

using index condition 索引下推

using where 所有过滤动作都由server层处理，没有索引（需要优化）

using MRR

using join buffer （BAK|BNL）

using union（indexs） 使用联合索引，多个查询条件都是索引列会发生索引合并

usring temporaray 使用了临时表，尽量使用索引，而非临时表，例如group by、union、distnct语句会使用到临时表

using filesort 通常出现在order by语句，查询之后需要额外进行排序。可以将条件与排序字段组成联合索引，由于有联合索引，排序字段本身就是有序的，不需要额外排序。

总结：避免临时表，尽量覆盖索引，像排序和函数什么的尽量避免

2.4MySQL执行优化

is null优化

MySQL 可以对 col_name IS NULL 执行与用于 col_name = constant_value 相同的优化。

is null或者 null都会进行优化

注意：Oracle不会对NULL进行索引

Optimizing Subqueries with Materialization物化优化子查询

物化通过将子查询结果作为临时表（通常在内存中）生成来加速查询执行。MySQL 第一次需要子查询结果时，会将该结果物化到临时表中。任何后续需要该结果时，MySQL 都会再次引用临时表。优化器可能会使用哈希索引来对表进行索引，以使查找快速且成本低。索引包含唯一值，以消除重复并使表更小。

Index Merge索引合并优化

索引合并访问方法通过多次 range 扫描检索行，并将结果合并为一个。此访问方法仅合并单个表中的索引扫描，而不是跨多个表的扫描。合并可以产生其底层扫描的并集、交集或交集的并集。

如果您的查询包含复杂的 WHERE 子句，并且具有深层 AND / OR 嵌套，并且 MySQL 没有选择最佳计划，请尝试使用以下恒等变换来分配术语

(x AND y) OR z => (x OR z) AND (y OR z)

(x OR y) AND z => (x AND z) OR (y AND z)

order by优化

gorup by优化

满足 GROUP BY 子句的最通用方法是扫描整个表，创建一个新临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组和应用聚合函数（如果有）。在某些情况下，MySQL 能够做得更好，通过使用索引访问来避免创建临时表。

索引下推ICP 优化

在传统的查询执行过程中，数据库系统首先根据索引找到可能符合条件的记录位置，然后将这些记录加载到内存中，最后在内存中应用WHERE子句的过滤条件。

而索引下推技术则是在索引扫描阶段就应用部分或全部的WHERE子句条件，直接在索引层面上过滤数据，只将真正符合条件的记录加载到内存中。即讲过滤逻辑下推到‘存储引擎’进行，整理好后发给server层，减少交互次数。

ICP 可以减少存储引擎必须访问基本表的次数以及 MySQL 服务器必须访问存储引擎的次数。

资料引用

B站《猿人林克》MySQL系列
CSDN用户《贝尔摩德苦艾酒》MySQL栏目
书籍《高性能MySQL》
MySQL官网