MySQL索引(一)

MySQL索引(一)

学习网站：https://xiaolincoding.com/mysql

一条查询SQL执行的完整过程

MySQL执行一条查询SQL语句时会经过连接器、查询缓存、解析器、优化器、执行器、存储引擎等模块。

1.MySQL的连接器会负责建立连接、校验身份、接收客户端的SQL语句；

2.去MySQL的查询缓存中去查找数据，找到直接返回数据给客户端，否则继续向下查询；查询缓存在8.0版本后被删除了，因为进行了写操作的表的查询缓存会失效

3.MySQL的解析器，对SQL语句进行词法和语法分析，构建语法树，读取表名、字段等；

4.MySQL的优化器基于查询成本，选择最小查询成本的执行计划。

MySQL引擎

存储引擎分类

常见的存储引擎有InnoDB、MyISAM、Memory等。

InnoDB引擎，是MySQL的默认存储引擎，支持事务和行级锁，具有事务提交、事务回滚和崩溃恢复功能。

MyISAM引擎，只支持表锁，锁的粒度较大，更新性能差，适合读多写少的场景。

Memory引擎，数据存储在内存中，读写比较快，但是数据不具有持久性，适合临时存储数据的场景。

InnoDB和MyISAM的区别

• 数据存储

InnoDB存储引擎的数据存储方式是索引组织表，索引即数据，即表数据和索引数据都存储在同一个文件中。

MyISAM存储引擎的数据存储方式是堆表，数据和索引分开，表数据和索引数据在两个不同的文件中。

索引组织表的优点：

1.索引和数据保存在同一个B+树中，从聚簇索引获取数据比非聚簇索引更快，查询数据更快。

2.除非主键发生修改，若仅仅是记录发生改变，其他索引无需进行维护

堆表的缺点:

1.堆表都是二级索引，每次索引查询都需要回表(因为主键也是二级索引，没有聚簇索引)

ps：这里的回表就是指根据索引表去查数据表

2.索引的叶子节点存放了数据在堆表中的地址，堆表的数据发生变化且地址发生变化，所有索引的地址都要更新，严重影响性能

• B+树结构

InnoDB引擎的B+树叶子节点存储：索引+数据

MyISAM引擎的B+树叶子节点存储：索引+地址数据

• 锁的粒度

InnoDB引擎支持行锁

MyISAM引擎只支持表级锁

• 事务

InnoDB引擎支持事务

MyISAM引擎不支持事务

索引原理

按数据结构分：B+树索引、Hash索引、Full-text索引(全文索引)

按物理内存分：聚簇索引(主键索引)、二级索引(辅助索引)

按字段特性分：主键索引、唯一索引、普通索引、前缀索引

按字段个数分：单列索引、联合索引

InnoDB的索引

InnoDB引擎支持B+树索引和Full-text索引

B+树索引特性：

• 数组组织形式

分为非叶子节点和叶子节点：非叶子节点只存放索引值和指向子节点的指针，也就是MySQL中的索引；叶子节点存储索引键值和行数据。

因此InnoDB引擎的主键索引属于聚簇索引。

• 叶子节点

所有叶子节点通过指针相连，形成一个双向链表，支持快速的顺序访问和范围查询。

• 平衡树结构

所有叶子节点在同一层，树高度平衡，保证任何记录的查找、插入、删除、更新的路径长度相同及稳定性。

B+树的特性

B+树的优点

B+树是一个多叉树，特性有3个：

1.只有叶子节点存储数据，其余节点只存储索引

2.叶子节点通过指针串联起来，形成一个双向链表，方便顺序遍历和范围查询

3.查询性能稳定，所有叶子节点在同一层，所有查询都具有相同I/O延迟；并且存储千万级别的数据，B+树只需要3-4层，换句话说就是，千万级别数据查询只需要3-4次磁盘I/O就可以查询到目标数据

B+树的缺点

B+树缺点是可能会产生大量随机I/O，每次修改数据可能会需要对整棵树进行递归的合并、分裂等调整树的操作，不同节点在磁盘上的位置可能并不是连续的，这会导致一些随机写入的操作。

B+树更新操作过多导致的随机I/O的缺点被LSM树解决。

B+树与B树

• 磁盘IO

B+树只有叶子节点才存放索引和数据，而B树所有节点存放索引和数据。也就是相同数据量下B+树比B树更矮胖，查询叶子节点的磁盘IO会更少

• B+树便于范围查询

B+树所有的叶子节点都会用链表进行连接，便于范围查询，而B树只能进行中序遍历来查询。B树的范围查询还会涉及更多的节点的磁盘IO操作，效率不如B+树

• B+树的增删改查更稳定

B+树的数据都是在最后一层的，B树是在随机一层找到目标数据的，因此查询、删除、插入数据B+树都要走到最后一层，所以B+树更加稳定

ps：1.稳定是指B+树查询是需要去叶子节点位置进行的，它的时间复杂度都是固定的；而B树可能在根节点找到或者在叶子节点找到，时间复杂度是不固定的。

2.不稳定的影响：在一些场景下，我们需要判断查询时到底是什么问题导致查询变慢时，B树的不稳定导致我们无法判断是什么原因导致查询变慢。到底是B树要走到叶子节点去查还是其他问题。

B+树与红黑树

• 磁盘IO

数据量越大，红黑树比B+数越高。因为红黑树是二叉树，B+树是多叉树。树高度越高，说明磁盘IO就越多，会影响查询性能。

• 范围查询

B+树叶子节点是通过链表连接的，可以很简单的实现范围查询；红黑树需要进行中序遍历，涉及多次磁盘IO，效率不如B+树。

B+树与哈希表

• 范围查询

哈希表的数据都是通过哈希函数计算后散列分布的，不支持范围查询和排序操作，还不支持联合索引的最左匹配原则。

• 查询效率

如果数据量变大，重复键比较多，造成哈希碰撞还会导致效率变低。