关注:架构师学习路线,每日更新互联网最新技术文章与你不断前行,实战资料,笔试面试
在说优化之前需要先GET到以下知识点,这样便于后续的分析。看完这篇文章不仅要会如何优化,还要搞懂为什么这样优化。
半双工通信:MySQL的数据传输采用的是半双工通信,同一时间要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生。MySQL对客户端发送数据也有要求,一次发送所有数据,等服务端响应后才能发送下次数据。
顺序读写与随机读写:数据库数据都是要落盘的,由于磁盘物理结构,寻道时间过长,故顺序读写比随机读写效率高很多。如果不太懂,可以想想平时坐车,你是坐一趟车直达(顺序读写)好呢?还是各种换乘(随机读写)好呢?
结果缓存:MySQL对查询的结果是支持缓存的,默认关闭。(提示一下,对于频繁更新的数据尽量不要使用MySQL本身的缓存,缓存失效造成更多性能浪费)
SQL查询流程:客户端发送查询SQL,通过数据传输到服务端,优先查询结果缓存,如果未命中则先后通过解析器、预处理器、优化器、执行计划、执行引擎、存储引擎后得到结果放入内存中并返回给客户端。(后续专门写一篇文章介绍下)
索引(Index):帮助MySQL高效获取数据的数据结构,MySQL中大部分索引都使用多路平衡查找树。
在对索引优化之前,需要知道索引的具体结构。根据不同的存储引擎数据的存储结构也不一样,存储引擎主要使用的有InnoDB、MyISAM。本文主要讲InnoDB的索引优化。
InnoDB引擎索引说明
聚簇索引
每个表都有一个聚簇索引:主键存在时以主键为聚簇索引,
主键不存在时,以第一个不含有null值的唯一索引作为聚簇索引
以上索引都不存在时,MySQL会创建一个隐藏字段rowid的聚簇索引。
每个表的数据按照聚簇索引而聚集在一起形成B+树。其中在最后的叶子节点挂载非索引数据,叶子节点之间存在有序的指针。
聚簇索引图示1
辅助索引
表中除了聚簇索引外其他非聚簇索引成为二级索引或者辅助索引,辅助索引中的叶子节点不再挂载非索引数据,而是存储聚簇索引的索引值
辅助索引图示2
联合索引
特殊的辅助索引:联合索引,B+树的节点存储的不是一个列数据,而是多个列数据,按照定义的顺序构成一个节点。
联合索引图示3
在对B+树存储结构有一定了解下,从实用角度来分析如何优化SQL。这也是SQL优化器要做的功能。
索引优化
主键的选择
首先了解B+树是有序多路平衡查找树,也就是插入之前需要排序的,为了平衡还需要拆页、旋转等操作。
先说顺序本身,顺序是比较之后的结果,如何比较?MySQL在建立数据的时候必须指定编码格式和排序方式,这时便有了比较顺序的方式。无论主键是何种类型,数字、字符串都会转换编码,然后排序。主键的可比较性决定了主键的效率
再说顺序意义,