MySQL索引及性能优化
1.索引
索引原理
◆索引结构
B+Tree 和 顺序访问指针实现,既有B+Tree的平衡性,顺序访问指针可以提高区间查询速度
B+Tree的一个节点中,key从左到右非递减排列,如果某个指针左右相邻的key分别为keyi 和 keyi+1且不为null,则该指针指向节点的所有key都大于等于keyi,小于等于keyi+1
◆查找操作
在进行查找操作时,首先在根节点进行二分查找,找到一个key所在的指针,根据指针递归查找到叶子节点,在叶子节点中二分法查找到key及其对应的data
◆与红黑树比较
更少的查找次数
B+Tree查询时间复杂度跟树高h相关
利用磁盘的预读特性
为减少磁盘I/O操作,磁盘往往不是按需读取,而是每次都预读。预读过程中,磁盘进行顺序读取,顺序读取不需要磁盘寻道,速度非常快。
操作系统一般将磁盘和内存分割成固定大小的块称之为页,磁盘和内存之间以页为单位进行数据交换,数据库系统将节点的大小设置为页的大小,一次I/O可以加载一个节点,利用磁盘的预读,加载相邻的节点数据。
MySQL索引
◆B+Tree索引
此索引有序,可以查询、排序、分组
主索引
主索引的叶子节点data域记录着完整的数据,这种索引被称之为聚簇索引。无法将数据存放在不同的地方,所以一个表只能有一个聚簇索引。
辅助索引
辅助索引的叶子节点data域记录着主键,根据辅助索引查询时,首先定位主键,然后再到主索引中查找。
◆哈希索引
不可排序、分组,可以快速精确定位
InnoDB -> 自适应哈希索引
◆全文索引
MyISAM -> 倒排索引
索引优化
◆独立索引
不能使用表达式、函数
◆多列索引
多个列查询时使用
◆索引列顺序
选择性强的索引靠前
◆前缀索引
Blob、Text、Varchar等
◆覆盖索引
索引优点
减少服务器扫描的行数
将随机I/O变为顺序I/O
避免排序、分组时创建临时表
索引使用条件
中大型表
小表不适用,特大型表需要分区
2.查询性能优化
Explain
select_type: 查询类型,简单查询、关联查询、范围查询、子查询
key: 使用到的索引
row: 扫描的行数
优化数据访问
◆减少请求的数据量
查询只返回需要的列,查询只返回需要的行数
◆减少服务端扫描的行数
尽量利用索引,减少服务端全表扫描
重构查询方式
◆切分大查询
如果一次性执行的话,会锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多重要的小查询
◆拆分大连接(关联)查询
将大连接查询分解成多个表查询,由应用程序进行关联:
大连接查询,只要有一个表数据变化,整个查询缓存无法使用
分解成多个表查询,缓存结果会被其他查询使用到
减少锁竞争
3.数据库引擎
InnoDB
MyISAM