MySQL索引

阳宝宝的向日葵

已于 2022-03-04 15:03:34 修改

阅读量604

点赞数

分类专栏： MySQL 文章标签： mysql 数据结构

于 2022-03-04 14:23:13 首次发布

本文链接：https://blog.csdn.net/m0_56709616/article/details/123274113

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySql索引

索引简介
索引的实现
性能分析
索引优化

索引简介

Sql慢

查询语句写的烂
索引失效
- 单值索引
- 复合索引
关联查询太多join（设计缺陷或者不得已的需求）
服务器调优及各个参数设置（缓冲、线程数等）

索引是什么

MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质是数据结构。
- 数据库在执行查询的时候，如果在没有任何索引的情况下，会采用全表扫描的方式进行查找。如果灿在索引，则会先去索引列表中定位到特定的行或者直接定位数据，从而可以极大地减少查询的行数，增加查询速度。
- 索引可以类比为一部字典开头的目录。

索引的数据结构

常见的数据结构
- 链表
- 数组
- Map
- 二叉树
- 红黑树
- Hash表
- B树

二叉树

二叉树或者红黑树只有两个叉，所以如果数据库的数据量比较大，那么就会导致树的高度比较高，树的高度高的话，磁盘的IO次数就会增多，这个时候查询的效率就会比较低（但还是高于全表扫描），所以二叉树或者红黑树不适合当索引。

Hash

Hash表也叫散列表，根据相关的key直接访问的数据结构。做法很简单，把key通过一个固定的运算转换成一个数字，然后把这个数字对数组的长度取余，最终的结果就当作数组的下标。对应的数据就放在该下标处。
如果采用Hash表作为索引，其查询效率也是很高的。但是Hash表会普遍用于MySQL的索引上来吗？
- 使用Hash作为索引会存在以下问题：
1. Hash索引仅仅能够查找=，in等情况的查找，无法进行范围查找
2. Hash索引无法进行排序（经过Hash运算后的数字大小和原本的数字大小没有关系）
3. Hash表这种数据结构可能会存在Hash冲突，即可能会存在不同的数据经过Hash运算后得到相同的hash值，因此即便得到了对应的Hash值所在的下标，仍有可能需要再次扫描表的数据
4. 如果存在大量Hash值相等的情况，此时Hash索引的查询性能不一定优秀

B树

B树是一种多路平衡查找树。B树的定义如下：
对于一棵m阶的B树而言
1. 树中的每个结点最多含有m个孩子；
2. 除了根结点和叶子结点，其他结点至少有 [ceil（m/2）]个孩子；
3. 若跟结点不是叶子结点时，则至少有两个孩子（除了没有孩子的根节点）
4. 所有的叶子结点都出现在同一层中，叶子结点不包含任何关键字信息；
问题一：B树相对于平衡二叉树或者红黑树，最大的优势在什么地方？
- 对于B树而言，如果结点内存储的索引数量越多，那么即便B树的高度只有三层或者四层，也可以存储千万条以上的数据。
- 一般情况下，如果B树的高度是3，那么就需要进行三次查询，也就是需要经过三次磁盘IO，查询限速步骤主要在于磁盘IO
问题二：如果将千万条数据都放在同一个节点内，不是只需要一次IO就可以找到对应的数据了吗？为什么不采用这样的方式呢？
- 首先要清除磁盘和内存是怎样交互的。磁盘的结构如下图：
- 磁盘由盘片组成。盘片有两面，称之为盘面surface。盘面上覆盖磁性材料。中间是一个可以旋转的轴Spindle，使得整个盘面能够旋转。通过速率为 5400 转每分钟或者7200 转每分钟。每个盘面又由一系列的同心圆组成，称之为磁道 track。磁道又被划分为一组组扇区 sector。扇区的大小大概都相等，为512 字节。
- 当从磁盘中读取数据时，利用读写头找到对应的磁道，这个步骤称之为寻道。找到磁道后，盘片开始转动，磁道上的每个位都可以被磁头感知到，然后读取到其内容，对磁盘的访问整个过程可以分为寻道时间和访问时间。一般情况下，寻道时间较慢。
- 由于磁盘存取的速度比内存慢很多，所以磁盘读取时，通常情况下并不是按需读取，而是会预读一部分数据。预读的长度通常情况下为一个页的整数倍。一个页一般情况下大小为 4k。也就是说一次磁盘 IO通常只会读取 4k 的几倍。因此，把全部数据写入到一个节点中，也并没有太大用处，因为一般只会读取4K 或者 4K 的几倍。
- 数据库的实现者也利用磁盘预读的这一点，将一个节点的大小设为一个页的大小或者一个页大小的几倍。

B+树

数据库底层是采用B+树来作为索引。他可以看成是B树的变种。具有以下特点：
- 非叶子结点不存储data，只存储key
- 所有的叶子节点存储完整的一份key信息以及key对应的data
- 每一个父节点都出现在子节点中，是子节点的最大或者最小的元素
- 每个叶子节点都有一个指针，指向下一个数据，形成一个链表
特点：B+树由于非叶子节点不存储数据，仅在叶子节点才存储数据，所以，单个非叶子节点可以存储更多的索引字段。

索引的实现

数据库的组成结构

连接器：负责管理连接，权限的验证等。
解析器：首先MySQL需要知道你想做什么。因此需要对输入的SQL进行解析。首先进行词法分析，需要识别出里面的字符串代表什么意思。比如SELECT代表查询，T代表某张表，ID代表某张表的列字段叫id；之后进行语法分析，根据语法规则，判断输入的sql语句是否符合MySQL语法。
优化器：经过解析之后，MySQL就知道你需要做什么事情了。但是在真正执行之前还需要经过优化器处理。比如当表中存在多个索引的时候，选择那个索引来使用。或者多表关联的时候，选择各个表的连接先后顺序。
执行器：开始执行之前首先确认对该表有无执行查询的权限。如果没有，则返回错误的信息提示。如果有权限，则开始执行。首先根据该表的引擎类型，使用这个引擎提供的接口。比如查询某表，然后利用某字段查找，如果没有添加索引，则调用引擎的接口取出第一行数据，判断结果是不是，如果不是，依次再调用引擎的下一行数据，直至取出这个表中所有的数据。

所以具体的数据是保存在引擎中的。在MySQL中，常见的数据库引擎有MyISAM和InnoDB。