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+树与哈希表
- 范围查询
哈希表的数据都是通过哈希函数计算后散列分布的,不支持范围查询和排序操作,还不支持联合索引的最左匹配原则。
- 查询效率
如果数据量变大,重复键比较多,造成哈希碰撞还会导致效率变低。