Mysql索引相关

最新推荐文章于 2024-05-31 14:43:45 发布

本是后山人～

最新推荐文章于 2024-05-31 14:43:45 发布

阅读量173

点赞数

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

本文链接：https://blog.csdn.net/weixin_47505584/article/details/120636799

版权

DB 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

相关概念

索引

慢sql查询就是一条sql查询可能需要几秒或者几十秒才能返回结果，我们首先想到的就是添加索引，大多数情况下只要索引合理，就算数据量很大上千万，依然可以得到不错的查询速度。使得查询速度大幅度查询。

索引的一些机制：
**索引的本质是一种排好序的数据结构，也就是说他是一种数据结构。**在不加索引的情况下，一条sql语句查询需要逐行去查，一条一条的拿出来对比，实质上是一种全表扫描，而磁盘IO的效率是很低的。
mysql最早的索引结构是二叉树，每个节点是一个key-value的结构。树中每向下一层就进行一次磁盘IO，将节点从磁盘读入内存并比较，可以达到对数级别的时间复杂度。但仅使用二叉树会有问题。如果插入一个递增的序列，该二叉树可能会退化成一个链表。
早期mysql还考虑过使用红黑树作为索引的数据结构。红黑树是一种二叉平衡树，当一个子树比另一个子树高得多的时候会自动平衡树的结构。但mysql依然没有选择红黑树作为索引数据结构。因为在记录很多的情况下，几百万几千万条记录，红黑树的高度可能会非常的高，假设我们要查找的某个记录落在叶子节点上，则至少要查询树的高度这么多次，也就是要进行树的高度次的磁盘IO，这会导致速度太慢。也就是说当数据量很大的时候，红黑的树的高度是不可控的。
需要一种更好的数据结构，例如一种即使数据量很大，树的高度依然很小，例如在树的高度小于4的情况下，依然能存储几百万或者上千万的索引。我们如果既想要存储几千万的索引，又想要树的高度控制在4之内，那么只能横向的把树拉宽，从横向拓展，在每个大节点上横向上能存放更多的索引元素。这个结构就是B树。

b+树：

实际索引使用的是b+树，b+树是在b树的基础上做的优化。和b树相比较，b+树把b树上的所有数据全部放到叶子节点上，非叶子节点全部存放索引不存放数据。而叶子节点存放数据（这里的数据实际上是真实数据对应的磁盘地址），也就是说整张表的所有索引都在叶子节点中。中间非叶子节点的索引实际上是为了维护整个树结构的冗余索引。
b+树的特点：所有索引全部集中在叶子节点，非叶子节点全部是冗余索引，b+树中所有索引从左往右全部是递增的。b+树索引查询时，先把节点整个读取到内存，然后在内存中进行二分查找，确定目标索引所在的区间，然后再把这个区间对应的节点读入内存再次二分查找，直到查询到最后的叶子节点，叶子结点中存放的索引对应的数据就是真正数据对应的磁盘地址。

b+树怎么做到千万条记录，而树的高度依然只有3-4层？

mysql底层把每个节点的大小设置为16kb，这一个节点实际上就是mysql中的一个页（page），每个页中存储了非常多的索引。我们可以计算一个高度为3的b+树，当他的所有节点存满索引之后，这一课树一共有多少索引。假设一个主键索引使用bigint，一个bigint占8字节，而每一个索引都会对应一块空的空间，这个空间存储的就是下一个节点的磁盘文件地址，通过这个空的空间，我们才可能从一个节点寻址查找到另一个节点。源码中给这个空的空间分配的是6个字节，那么一个主键索引和一个节点地址加起来一共14字节，16384/14=1170，也就是说一个节点可以存储1170个索引，也就是说一个非叶子节点可以存储1170个索引，而叶子节点中，因为除了主键索引还存储了data，假设他们一共占1kb，则一个叶子节点可以存放16个索引，因为b+树种所有的表中的记录在叶子节点中都有完整的一份。所以叶子节点个数就是记录的个数。则总的索引个数为1170x1170x16约等于两千万，也就是高度为3的b+树可以存放两千万条索引，就是两千多万条记录。可以看出维护一个好的索引即使记录条数达到千万级别依然能保持很快的查询速度。
对于mysql较高的版本，非叶节点可以放到内存中，查找非叶节点直接通过折半查找定位，然后通过一次磁盘IO把叶节点load到内存。

数据库的表到底存在哪里？
默认存储在mysql安装目录下的data目录中，目录中的每个文件夹和一个数据库对应，库中的每个表对应文件夹中的文件。myisam是mysql早起引擎，innodb是现在的主流引擎。

存储引擎是形容表级别的引擎还是数据库级别？
可以配置表对应的存储引擎。

单字段索引结构

采用myisam存储引擎的表的主键索引结构：
frm文件：存储数据表表结构的信息
myd文件：表中所有的行记录数据
myi文件：索引
假设columnA列上建立一个索引文件，那么该索引将被存入myi文件。假设现在需要查找一个columnA中的记录，那么首先他会去看哪些字段上有索引，发现columnA是索引字段，那么就去myi文件中检索，找到叶子节点，叶子节点中存放了该行记录对应的磁盘文件地址，拿到这个地址以后去myd文件中直接进行寻址获取该记录的详细信息。
对于myisam来说主键索引和非主键索引没有太大的区别

采用innodb存储引擎的表的主键索引结构：
frm文件：存储数据表表结构的信息
ibd文件：数据+索引一起存在该文件中
innodb和myisam不同的地方就在于，innodb的叶节点中存放的是该索引所在行的所有字段的数据，而不像myisam一样仅仅只存储索引所在行的地址。
对于innodb来说主键索引和普通索引有区别。

innodb中普通索引的存储：
普通索引的叶子节点存储的是所在行的主键。

聚集索引和聚簇索引：

聚集索引就是叶子节点包含了完整的记录，他就是聚集索引（innodb的主键索引是聚集索引）。
非聚集索引：叶子结点中无数据本身，数据和索引是分开的（myisam的主键索引就是非聚集索引）

对于innodb引擎的表来说，整个表在存储上实际就是一颗b+树，如果有主键索引，那么默认使用主键索引组织整张表的数据结构。否则会帮你找一个类似主键索引的东西。例如找到了一列，该列的约束条件是unique，也就是值唯一，则可以使用该行作为索引。如果都没有，那么会默认帮你维护一个隐藏列，用该列来组织b+树的结构。我们一般建议手动创建主键，而不是让mysql帮我们维护一个充当主键的列，因为mysql的资源宝贵，应尽可能少的让mysql做额外的工作。

主键推荐使用自增的整形，而不是使用uuid，因为uuid是字符串，比大小的时候和整形比起来太慢，而且整形占用空间小，就算采用bigint也才8个字节。可以节省ssd成本。
除了b+树索引之外还有一种哈希索引，将索引字段通过哈希算法算出散列值，根据散列值定位到哈希桶数组中的某个位置中，还可以采用一些方式如rehash避免哈希冲突。但这种索引并没有被mysql采用，因为该方式只能进行单记录查找，不能进行范围查找。因为范围查找需要定位一片连续的地址，而哈希索引无法定位一片连续的范围。
而b+树能很好的支持范围查找，b+树的叶节点中除了存放索引，索引所在行的所有数据，还存放了双向指针，指向前后两条记录的磁盘地址。可以快速查找前后两条记录。b+树还有一个特点，就是叶子节点从左到右索引是依次递增的。就算表记录中是无序的，但其维护的索引依然是有序的。

为什么推荐采用自增主键？

采用自增主键插入元素只需要在叶子结点的最末未插入节点，但采用非自增主键，则有可能不断在叶子结点的中间节点插入元素，需要不断的进行树的分裂，平衡等操作，这会耗费很多mysql资源。采用自增节点mysql不必再帮你维护有序性，直接插入到节点最后面即可，很少会造成节点分裂。因此自增主键比非自增在底层效率上会更高一些。

b树和b+树区别？

b树无区间指针，也就是没有叶子节点上的双向指针，如果b树要进行范围查找必须从根节点上一个一个查，或者进行全表扫描，效率太低。b+树把所有的数据都放到了叶子节点，其中有很多冗余索引，而b树没有冗余索引。

为什么将数据都放到叶节点中？

为什么需要这么设计，将数据都放到叶节点？因为索引占的空间小，数据占的空间大，如果把数据都放到非叶节点上那么非叶节点存放的索引将会很有限，造成树的高度过高，增加磁盘IO次数，降低效率。因此b+树实质就是在非叶节点上尽可能多的存放索引，存放的索引越多，就能分更多的叉，树的高度越小，查询越快。

联合索引(联合主键)

单值索引用b+树存储还好理解，但联合索引用b+树如何存储？再次回顾一下索引的本质：是一个排好序的数据结构，

索引最左前缀原则：联合索引的排序方式，他的排序就是按照索引从左到右的先后顺序去比对。比如有如下索引：

KEY `idx_name_age_position`, `name`, `age`,`position`) USING BTREE

例如比较：（bill, 30, dev）和（hanmeimei, 28, dev），先比name，再比age，再比position

如果二级索引（普通索引）的两个叶子节点的索引值相等怎么办？如何区分？
这种情况下，因为二级索引的叶子节点存放的是该条记录所在行的主键，所以把主键拿出来进行回表查询即可。

索引的最左前缀原则

先看一个例子，下面哪条sql语句会使用索引查询?

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

EXPLAIN SELECT * FROM Employee WHERE name='Bill' and age =31;
EXPLAIN SELECT * FROM Employee WHERE age = 30 and position = 'dev';
EXPLAIN SELECT * FROM Employee WHERE position = 'dev';