MYSQL索引【index】

Naomi Zang

于 2024-09-19 17:23:12 发布

阅读量1.1k

点赞数 29

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_70003285/article/details/142350764

版权

类型、创建方法，还是性能优化？

表结构---设计表---SQL预览

表结构---设计表---SQL预览---索引

索引是数据库中提高查询性能的重要工具。主要类型包括：

B-Tree索引：常用的默认索引，适用于范围查询。
Hash索引：适用于等值查询，速度快，但不支持范围查询。
Full-Text索引：用于文本搜索，支持复杂的文本匹配。

创建索引可以通过 CREATE INDEX 语句，例如：

CREATE INDEX idx_name ON table_name(column_name);

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树和 Hash、红黑树。
索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。
MYSQL有慢查询日志

1.开启慢查询--explain select ....【explain关键字】

2.看慢是否走索引

索引的优缺点

优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。【磁盘读取数据库的IO】
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

创建索引和维护索引需要耗费许多时间【旋转（修改）根节点改变】。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引需要使用物理文件存储，也会耗费一定空间。

Hash 索引不支持顺序和范围查询。
二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

左子树所有节点的值均小于根节点的值。
右子树所有节点的值均大于根节点的值。
左右子树也分别为二叉查找树。

旋转：发生在增加、删除数据

AVL--绝对的平衡，每增加一个旋转一次

红黑树--不追求严格的平衡，而是大致的平衡。

红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

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

【B+树支持范围和顺序查找】

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

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

执行计划分析

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。