mysql索引以及算法数据结构

超、凢脫俗 2024-04-19 23:56 71阅读 0赞

一、序言

这个问题是属于mysql中比较有难度的问题了，毕竟数据结构是构成计算机的重要部分，做过开发的朋友都知道索引能够帮助我们提升sql查询的能力，但是索引到底是什么？为什么能够做到这一点，接下来就揭开索引的面纱。

二、索引是什么？为什么使用索引？

索引简单来说就是帮助我们优化sql，提升查询性能的一种手段，也可以说它是一种数据结构，索引就是通过不断的缩小想要的数据的范围筛选出来最终的结果，同时将随机的事件变成顺序事件。也就是使用索引的方式快速锁定我们想要的数据。

三、预备知识

既然说索引是一种数据结构，这种数据结构有什么优势？从哪个切入点进行优化的？先不着急，先了解一下计算机组成原理以及操作系统相关的一些知识，它能够帮助我们更好的理解索引（数据结构）。

1、磁盘IO和预读

磁盘IO，其实就是对磁盘中数据的读和写，这个相比在内存加载来说是非常低的。查询数据的时候为了减少访问IO的次数，就将索引设计成为B+Tree，保存索引的时候，其数据结构保证查询的时候使用最少的IO次数查询出结果。

那么朋友们可能会问：

（1）既然为了访问最少的次数，不都放到同一层，如果都放到一层，时间复杂度为O(n)，需要一个个的去遍历，效率非但没有提升；另外都放到同一层，操作系统的预读和行记录存储方式也不允许，效率不见得有多高，并且随着数据量的增加，效率会明显的有差异。

（2）为什么使用T+Tree而不是红黑树或者二叉平衡树等，因为它们的高度比B+Tree高，有多高查询的时候就需要多查询多少次，进行多少次的IO操作。

（3）既然使用B+Tree算法，为什么每个节点上面不多放一些数据这样的话高度就会减少，IO操作次数就会减少，因为这个会涉及到操作系统的预读和数据的行记录存储方式（下面会说），操作系统一次预读，读取的有限，所以每个节点上面不能够放足够多的数据。

预读，考虑到磁盘IO是非常昂贵的操作，计算机操作系统帮助我们做了优化，当一次IO不光通过磁盘中数据地址将指定的数据读取出来加载到内存中，还会把该数据相邻的数据也读取到内存中，因为操作系统虽然不能够智能化的读取人大脑的信息从而预测下一步读取什么数据，但是它却使用另外一种方式，其认为既然读取了当前的数据，也就是说相邻的数据被使用的几率也是相对较大的，所以就将该数据相邻的数据也读取出来。每次IO读取的数据我们称之为一页，具体一页有多大跟操作系统有关系，一般是4K或者8K，也就是说我们读取一页数据其实就是发生了一次IO。

2、mysql数据在磁盘上的存储方式

mysql在磁盘上是以数据块为单位存储的，然而一个存储块又是由多个磁盘块组成的。存储引擎负责管理数据块，mysql读取表中的数据到内存中也是一块一块的读取的，假如要查询的表和其他表都在同一个数据库中，加载块的时候，读取要查找的表，其他表也会被查出来。

当一个块内的部分表被删除的时候，这就形成了碎片，这样会降低装载到内存的速度，所以为了解决这个问题，会形成一个的块头，用来记录一个数据块内表的大小，有无空闲空间以及空闲空间的位置等。

mysql是按照行存储数据的，因此一行数据是一个统一的物理地址，mysql的最小存储单位为数据块，所以可能会出现字段比较少，多条记录在同一块中。

相对应的列式数据库不同的字段存放在不同的地址中。

四、索引的种类

我们在网上查找mysql索引的时候肯定会搜到什么主键索引、唯一索引、组合索引、聚集索引、非聚集索引等，一些文章并没有准确的划分，导致我们一知半解，区分不出来，这里呢就帮助朋友们稍微的区分一下

**聚集索引（聚簇索引）**

数据行的存储顺序和某列的值的存储数据是一直的，一般这一列被称为主键。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70][]

如上图所示：第一列是数据行在磁盘上的存储地址，数据行的存储地址和id的排列顺序是一致的。所以一张表中只有一个主键列，如果我们创建数据块表的时候没有创建主键，但是没关系，数据库会默认为我们创建一列主键。

所以一般说聚集索引其实就是主键索引

**非聚集索引**

非聚集索引可以这样理解，数据行的排列顺序和该索引中的索引列无关，所以简单来说就是除了聚集索引（主键索引）之外的都是非聚集索引，既然说了除了主键索引其他都是非聚集索引，那么毫无疑问普通索引、唯一索引、组合索引等都是非聚集索引。

唯一索引

唯一索引不允许两行的该索引列相同，但是可以为null的，并且可以有多个null，这也是主键索引和唯一索引的一个区别，也可以说主键索引在形式上就是唯一索引的一种特殊类型。但是两者确实没有什么可比性，不在同一起跑线上，没有办法做对比。在同一张表中唯一索引可以有多个。这里朋友可能会迷惑，有了主键索引还要什么唯一索引，不为什么就是因为一张表中只能有一个主键索引，所以才有了唯一索引。

组合索引

多个字段共同组成一个索引，其遵循最左前缀原则，至于最左前缀原则下面会讲解，组合索引中的几列必须唯一，这里我们又有了疑惑，既然有了唯一索引，并且唯一索引在一张表中可有多个，那为什么还要什么组合索引，因为索引也是要占用空间的，如果有三个列age、name、city，如果在每个上面都加上唯一索引或者普通索引的话，首先占用内存上面要比组合索引多，效率也不见得比组合索引查询效率高，存储引擎会选择三个单列索引中效率较高的索引查询作为辅助索引，不明白为什么没关系，等下面看了索引的数据结构就明白了。

普通索引

索引列可以重复，也是我们最常用的，既然有了唯一索引为什么还是用普通索引，不为什么就是因为列可以重复。

全文索引

全文索引仅能够作用于myisam总的char、varchar、text。

覆盖索引

该索引也是最近学习的时候察觉到的，之前对索引以及sql优化并不是太了解，只从学习了之后才发现，这太有用了，对sql优化有很大的帮助。感觉有必要珏哥例子说一下。

举例：目前有一张学生表：student\_id（主键） 、student\_name（名称）、age（唯一索引）

select age from student;

现在查找所有的age字段，哈哈，这是不涉及数据的，只是涉及到索引，因为我们知道innodb中age是辅助索引，如果想要通过辅助索引查找某一行数据的话，首先遍历age索引对应的B+Tree，找到对应的主键，然后通过主键去遍历主键B+Tree，找到对应的数据行信息，也正是因为这一点，如果只是查找age的话不需要再去通过主键索引的B+Tree查找数据行记录，而是仅仅遍历了age的唯一索引B+Tree，速度提升了。

五、索引的数据结构

其实本篇文章中最重点的就是这里了。先说使用的算法：B-Tree、B+Tree。

1、现在大都使用B+Tree为什么不使用B-Tree？

B-Tree结构图![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 1][]

上面说过的，每个节点只有尽可能的存储多个值才能够降低树的高度，树的高度才能够决定访问IO的次数，如果每个节点放的没有数据，这样的话，可以节省下来一部分空间放置键值和指针，高度肯定降低（每个节点是操作系统的预读大小，大小是固定的）。

上面是B-Tree结构图，B+Tree的结构图先不放了，下面都是B+Tree的结构图，自己进行对比即可

2、目前使用的是B+Tree的变种而并非是B+Tree，为什么？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 2][]

其实B+Tree结构图中并没有叶子节点之间的指针的，当我们使用主键作为范围查找的时候，叶子节点之间的指针就有很明显的优势了。

MYISAM

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 3][]

非叶子节点并不存储真正的数据，每个叶子节点都是存储的索引列以及对应行的地址，这里是和innodb的一个区别，因为在数据库引擎一文中说过，两者的文件存储方式不同，myisam中索引和数据行不再同一文件中，所以每个叶子节点都是存放对应行的地址。而innodb中索引和记录在同一行，所以每个叶子节点存储的是记录本身。

INNODB

主键索引

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 4][]

叶子节点包含了完整的数据记录，这种索引叫做聚集索引，因为innodb的数据文件本身要按主键具体，所以innodb要求表必须由主键（myisam可以没有），如果没有显示指定，innodb会自动为该表生成一个主键列，首先会在表中查找是否由某个列可以唯一标识数据记录，如果有的话，就使用该列作为主键，如果没有就生成一个隐含字段作为主键，这个字段的长度为6个字节，类型为长整型。

辅助索引搜索时

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 5][]

通过辅助索引查找的时候，就是先查找辅助索引的B+Tree，找到对应的主键之后，然后查找主键索引的B+Tree，查找到对应的数据行的数据。

组合索引：

因为组合索引和其他非主键的单列索引一样的道理，只不过数据结构不太好想象

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 6][]

图中对应地址就是主键地址，是不是道理是一样的。

六、最左前缀原则

这个没什么好说的，因为上面图中的组合索引已经明显的表达出来了，如果age、name、sex作为组合索引

（1）select \* from student where name = '张三' and sex = '男'

上面是不走组合索引的，因为结构图中索引是以age作为主索引进行组合B+Tree的，直接使用name、age达不到效果

（2）select \* from student where age = 12 and sex = '男'

从删改你组合索引图可以看到，走组合索引，但是sex并未起效

（3）select \* from student where age = 12 and name = '男'

从删改你组合索引图可以看到，走组合索引，并且age、name都起作用

（4）select \* from student where age = 12 and name = '张三' and sex = '男'

从删改你组合索引图可以看到，走组合索引，并且age、name、sex都起作用了。

这就是最左前缀原则，当然了，有必要说的就是，如果你给age添加了唯一索引，或者其他的什么情况，执行引擎可能会根据效率来判断是走唯一索引还是组合索引，这都是可以通过explain关键字可进行分析的，关于执行计划explain的解析请移步看我的另一篇文章。

注意事项

1.  范围查询

Mysql会一直向右匹配，直到遇到（>、<、between、like）就停止匹配，范围列可以使用索引，但是范围列后面的列无法使用索引，即：索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全部使用索引

1.  like索引问题

如果通配符不出现在开头，则可以使用索引，但根据具体情况不同坑你只会用其中一个前缀在like ’value%’可以使用索引，但是like’%value%’不会使用索引，走的时全表扫描

七、总结

这篇文章如果理解了，特别上面的图，索引相关的问题都基本能够沿用这个思路解决自己的疑惑。这篇文章确实有些难度，一旦理解，相信受益匪浅，加油，朋友们有什么感觉写的有什么的问题或者有什么不对的地方尽管提出来，及时进行回复和修改。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/84bb85ae034b40118a0bd1f24fb54e57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/8286b687d68d4adf96ba61b8cf3d576d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ef270e06909e433eba5ab7f3037ca67c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d8f8d18cb110417cbf123997e8993685.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/04a8770f1c3f466eb4b5f92b2c560f3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/90793b517ac2450baac9fbcb922ce014.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0phdmFXZWJfSGFv_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/95da022728e74142aeef40cb9a0cb07f.png