MySQL索引

七老板的blog

已于 2023-04-18 22:45:16 修改

阅读量341

点赞数

文章标签： mysql 数据库 java

于 2023-04-18 22:22:18 首次发布

本文链接：https://blog.csdn.net/qq_33803276/article/details/130232640

版权

一.什么是索引

1.数据库为了提高检索效率的一种数据结构
2.存储在磁盘的文件中，占用物理空间

二.构建索引的数据结构需要满足哪些条件

1.支持高效查询(减少IO)
2.支持范围查询
3.支持插入更新等操作

三.索引分类

3.1数据结构维度

B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
哈希索引: 适合等值查询，检索效率高，一次到位。
全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char,text,varchar类型上创建。
R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

3.2物理存储维度

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

3.3逻辑维度

主键索引：一种特殊的唯一索引，不允许有空值。
普通索引：MySQL中基本索引类型，允许空值和重复值。
联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

四.索引结构(B+树)

B+树是一种基于B树的索引结构，它的叶子节点存储了所有的索引记录，在叶子节点之间使用指针连接起来，因此可以支持范围查询和按序遍历。以下是一张B+树的示意图：

                       +--------------------------------------------------+
                       |                                                  |
               +--------------+                                   +--------------+
               |  30  |  40  |  ------->                   | 70 | 80 |
               +--------------+                                  +--------------+
              /                             \                                                  \
+---------------+        +---------------+         +---------------+         +--------------+
|     10    |     20 |      |     30   |     40  |       |      50    |        |     70   |     80  |
+---------------+        +---------------+         +---------------+         +--------------+

在这个示意图中，每个节点都有指向其它节点的指针。
所有的叶子节点都包含索引条目（也就是真正的数据），但是没有子节点指针；
非叶节点仅包含索引条目，并且有指向下一层子节点的指针；
叶节点相互连接，形成链表；非叶节点相互连接，形成索引树。
在B+树中，搜索通常从根节点开始逐层向下进行。当搜索到叶子节点时，检索到的值便出现在该节点上了。
总体来说，B+树的索引结构方便查找和范围查询，这是因为B+树的叶子节点之间都是通过指针链接起来的。

五.MySQL索引为什么要采用B+树数据结构

5.1为什么其它数据结构不合适？

1.哈希表存在哈希冲突，同时不支持高效的范围查找
2.跳表层级高的时候，查询不高效，需要多次IO
3.树结构中，搜索二叉树容易退化为链表，不能支持高效查找
4.树结构中，B-tree范围查找不是很高效，且因为每个结点都会存储数据，当数据量大的时候，树高会变大，从而增加IO次数，效率变低

5.2为什么B+Tree结构就合适?

1.B+树只在叶子结点存储数据，非叶子节点存储索引键值，从而可以保证树的高度稳定。
2.叶子结点通过指针形成链表，支持高效范围查找；
3.叶子结点会冗余一部分非叶子节点的数据，在增删数据的时候，不会存在复杂的结构变换（比如平衡树的旋转变换）

六.在什么情况下要建立索引

1.使用WHERE子句过滤大量数据时需要使用索引。
2.在ORDER BY子句中进行排序操作时，可以使用索引来加快查询速度。
3.通过JOIN连接表时，使用索引可以加快连接操作的速度。

七.索引失效的情况

1.使用 SELECT * 进行查询;
2.创建了组合索引，但查询条件未遵守最左匹配原则;

思考：具体的执行过程？

3.在索引列上进行计算、函数、类型转换等操作，及使用（！= 或者 < >）;

思考：使用（！= 或者 < >）什么时候失效？

4.以 % 开头的 LIKE 查询比如 like ‘%abc’;

思考：具体的执行过程？

5.查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;

思考：哪些情况生效？哪些情况不生效？为什么？

6.发生隐式转换;
7.左右连接，关联的字段编码格式不一样

思考：未建立主键索引，普通索引会失效吗？

八.EXPLAIN的使用

九.MySQL各版本中对索引大小限制（InnoDB引擎）

1.联合索引
MySQL 3.23：最大联合索引长度为1000字节。
MySQL 4.0.x/4.1.x/5.0.x：最大联合索引长度为1000字节。
MySQL 5.1.x：最大联合索引长度为3072字节（InnoDB存储引擎），或1000字节（MyISAM存储引擎）。
MySQL 5.5.x/5.6.x/5.7.x：最大联合索引长度为3072字节（InnoDB存储引擎），或1000字节（MyISAM存储引擎）。
MySQL 8.0.x：最大联合索引长度为3072字节（InnoDB和MyISAM存储引擎）。
2.单列索引
MySQL 5.6 及之前版本： InnoDB 存储引擎中单列索引的大小限制为767字节。这个限制是由于InnoDB存储引擎默认使用UTF-8字符集，而UTF-8编码中一个字符可能占用3个字节的存储空间，所以InnoDB将限制单列索引不能超过255个字符（因为255*3=765）。
MySQL 5.7：InnoDB 存储引擎中单列索引的大小限制已经被扩展到3072字节。这个改变主要是为了适应更长的JSON列索引。
MySQL 8.0：InnoDB 存储引擎中单列索引的大小限制依然是3072字节。

参阅:
https://juejin.cn/post/7193682380077400122
https://juejin.cn/post/6850037271233331208#heading-24
https://juejin.cn/post/7081065180301361183(为什么要使用B+树结构)
https://javaguide.cn/database/mysql/mysql-query-execution-plan.html#%E5%A6%82%E4%BD%95%E8%8E%B7%E5%8F%96%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92（执行计划）