MySQL索引及性能优化

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