Mysql 索引

1. 索引介绍

一种用于快速查询和检索数据的数据结构，本质：一种排序好的数据结构
索引底层数据结构存在很多类型，常见的索引结构有：B树、B+树和 Hash、红黑树。在 Mysql 中，无论是 Innodb 还是 Myisam，都使用了 B+ 树作为索引结构

2. 优缺点

优点

加快数据的检索速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

缺点

创建索引耗时，当 CUD 时，如果数据有索引，那么索引也需要动态修改，降低 SQL 执行效率
索引也需要物理存储，也会耗费一定空间

但是，使用索引一定能提高查询性能么?

大多数情况下，索引查询比全表查询要快
数据量不大时，提升不明显

3. 索引底层结构选型

Hash

不支持顺序和范围查询

二叉查找树 BST

非常依赖平衡程度

自平衡二叉查找树 AVL

AVL 树的特点：左右高度差不超过 1 ===> 查找、插入、删除复杂度都是 O(logn)
缺点；

频繁地进行旋转来操作来保持平衡
每个树节点仅存储一个数据，而每次进行磁盘 IO 时，只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO

红黑树

B树和 B+ 树

B 树也称 B-树,全称为多路平衡查找树，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。

B 树& B+树两者有何异同呢？

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

4. 索引类型总结

按照数据结构划分

BTree
哈希
RTree
全文索引

按照底层存储方式划分

聚簇（聚集）索引	非聚簇（聚集）索引
索引结构和数据一起存放【InnoDB 中的主键索引】	索引结构和数据分开存放【二级（辅助）索引】

按照应用维度划分

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

5. 主键索引（Primary）

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段

如果有，则选择该字段为默认的主键
否则 InnoDB 将会自动创建一个 6Byte 的自增主键

6. 二级索引【通过二级索引（叶子节点存储的数据是主键），可以定位主键的位置】

二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等都是二级索引

7. 聚簇索引与非聚簇索引

聚簇索引（Clustered Index）
在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：查询速度非常快
缺点：依赖于有序的数据、更新代价大

非聚簇索引（Non-Clustered Index）
非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

优点：更新代价比聚簇索引要小
缺点：依赖于有序的数据、可能会二次查询(回表)
这是 MySQL 的表的文件截图：

聚簇索引和非聚簇索引:

8. 覆盖索引与联合索引

1 覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

判断是否使用覆盖索引 ===> 使用 explain 命令进行分析

通过 Extra 这一列的 Using filesort ，我们发现是没有用到覆盖索引的。
现在这里以 score 和 name 2个字段建立联合索引

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

创建完成之后，再用 EXPLAIN 命令分析再次分析这条 SQL 语句

通过 Extra 这一列的 Using index，说明这条 SQL 语句成功使用了覆盖索引

2. 联合索引

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。
以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

3. 最左前缀匹配原则【使用联合索引时】

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配

如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成
在执行过程中遇到范围查询（如 >、< ）才会停止匹配
对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

最左前缀的底层原理：
首先对联合索引最左边的字段进行排序（user_name），在第一个字段基础上，再对第二个字段进行排序（user_age）=> 所以最左前缀要使用上

不按照创建联合索引的顺序，使用索引（但是包括最左前缀索引）> 还是使用到了索引 ==> Mysql 底层做了优化

9. 索引下推

索引下推（Index Condition Pushdown）
是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引【Myisam 引擎】遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

10. 正确使用索引

选择合适的字段创建索引

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。
索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

字符串类型的字段使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些

SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖
创建了组合索引，但查询条件未准守最左匹配原则
在索引列上进行计算、函数、类型转换等操作
以 % 开头的 LIKE 查询比如 LIKE '%abc'
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同)

删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。
MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

知道如何分析语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的执行计划，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。
EXPLAIN 并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

posted @ 2023-12-28 17:20 爱新觉罗LQ 阅读(48) 评论(0) 收藏举报

刷新页面返回顶部

爱新觉罗LQ

Mysql 索引

Mysql 索引

1. 索引介绍

2. 优缺点

3. 索引底层结构选型

Hash

二叉查找树 BST

自平衡二叉查找树 AVL

红黑树

B树 和 B+ 树

4. 索引类型总结

按照数据结构划分

按照底层存储方式划分

按照应用维度划分

5. 主键索引（Primary）

6. 二级索引【通过二级索引（叶子节点存储的数据是主键），可以定位主键的位置】

7. 聚簇索引与非聚簇索引

8. 覆盖索引与联合索引

1 覆盖索引

2. 联合索引

3. 最左前缀匹配原则【使用联合索引时】

9. 索引下推

10. 正确使用索引

选择合适的字段创建索引

被频繁更新的字段应该慎重建立索引

限制每张表上的索引数量

尽可能的考虑建立联合索引而不是单列索引

注意避免冗余索引

字符串类型的字段使用前缀索引代替普通索引

避免索引失效

删除长期未使用的索引

知道如何分析语句是否走索引查询

公告

B树和 B+ 树