优化数据库的基础:建立正确的索引
索引的定义:加快关系型数据库查询、存储在磁盘的数据结构
预储备知识
1.操作系统对磁盘进行I/O操作的最小单位:从物理层面为扇区,从逻辑层面为簇、块(页)
可以了解SSD固态硬盘的4K对齐
2.应用空间局部性原理和时间局部性原理来提高预读能力
3.数据库页是Mysql操作的最小单位
索引
1.哈希索引(InnoDB中的自适应哈希索引)
优点:等值匹配非常高效
缺点:不能进行范围查找
2.树型索引
平衡二叉树(不采用):I/O次数多(树的高度高),效率低(读取的结点数据量小,一次I/O的效率低)
多路平衡查找树(B-树):I/O次数少(树的高度低),效率高(读取的结点数据量大,一次I/O的效果更高)
B+树(根节点和枝节点不再保存数据,仅有叶子节点才有数据区):可进行范围查找、一次交互的I/O效率高、性能稳定(数据存储在叶子节点,查询的I/O次数稳定)
索引在不同引擎中的实现(Mysql5.7 为例)
Myisam落地: .frm文件 .MYD文件(存储数据).MYI文件(存储索引)
主索引和辅助索引(Secondary key)在结构上没有区别,叶子节点存储的都是数据的地址
InnoDB落地:.frm文件 .idb文件(储存索引和数据)
主索引和辅助索引在结构上有区别,主索引的叶子节点的数据域存储了完整的数据信息,辅助索引的叶子节点存储的是主键的值(利用辅助索引查询到主键,再到主索引上查询完整数据,也叫回表)