MySQL查询优化

基础知识篇

MySQL优化只记优化方法，不去了解MySQL的设计，难成大器。

Mysql的基本架构

Server层：包括连接器、查询换成、分析器、优化器、执行器等，涵盖了Mysql的大多数核心服务功能，以及所有的内置函数（如日期、时间、数字和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层：负责数据的存储和读取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的是InnoDB,在Mysql5.5版本以后就开始成为默认的存储引擎了。可以在create table 语句中使用engine=memory,来指定内存引擎创建表。

索引的常见的数据结构

哈希表：一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免地，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

• 哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎

有序数组：有序数组就是最好的数据结构了，但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。适用于静态存储引擎，比如你要保存的是2017年某个城市的所有人口信息，这类不会再修改的数据。

• 序数组在等值查询和范围查询场景中的性能就都非常优秀

二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。这个时间复杂度是O(log(N))。

B+树索引
InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列
• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用双向指针连接，提高区间访问的性能。
• 索引page的默认大小是16k，索引元素8B 指向下一层指针 6B，差不多是1200叉树。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。

各种概念术语

聚簇索引（clustered index）：主键索引，叶子节点存的是整行数据，尽量选用自增主键。
二级索引（secondary index）：非主键索引的叶子节点内容是主键的值。所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
回表：回到主键索引树搜索的过程
覆盖索引：要查询的字段就在二级索引树上
最左前缀原则：索引项是按照索引定义里面出现的字段顺序排序的，查询从索引的最左前列开始并且不跳过索引中的列
索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

Explain工具

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL。

• explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通 过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表 进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）
• explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的 话，会显示查询将访问的分区。

Optimizer Trace工具

optimizer_trace用来跟踪优化器的执行过程，来选择合适的索引来对query进行优化。我们目前想要通过分析optimizer_trace的执行过程来推测未构建的索引对query的cost的计算

索引优化篇

基本优化方法

全值匹配：最简单最优的查询方式
利用最左前缀法则：如基础篇描述
不操作索引字段：索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致优化器选择全表扫描
存储引擎不能使用索引中范围条件右边的列：若中间索引列用到了范围，则后面的索引全失效。
尽量使用覆盖索引：（只访问索引的查询(索引列和查询列一致)），减少select* 。
覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
Mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描：很好理解需要遍历所有记录才能得到结果
IS NULL和IS NOT NULL也无法使用索引：同上
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作：同上
字符串不加单引号索引失效：类型变了，需要用到转换函数，隐式同不操作索引字段
用or来连接时索引会失效应少用：优化器会预估范围后可能选择放弃索引。
范围查询优化：同上，可以通过拆分多个子查询来协助优化器，快速优化。

复杂查询优化

Order by与Group by:要严格遵守最左前缀原则，并且通过索引树结构来分析选择合适的排序字段。可以通过Explain工具查看Extra字段是否有Using filesort。

• Using index是指MySQL扫描索引本身完成排序。
• Using filesort文件排序，需要利用临时表。单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
• 对于group by的优化如果不需要排序的可以加上order by null禁止排序

分页查询优化
limit 10000 10:是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据

• 如果能使用递增的主键索引，先圈定范围后再取前几行。
• 非主键字段排序的分页查询,排序和分页操作先查出主键，然后根据主键查到对应的记录

Join关联查询优化
嵌套循环连接 Nested-Loop Join(NLJ) 算法：一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动 表）里取出满足条件的行，然后取出两张表的结果合集
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

• 关联字段加索引
• -小标驱动大表
• straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执 行顺序

count(*)查询优化
myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算。
innodb存储引擎的表mysql不会存储表的总记录行数，由于MVCC存在，查询count需要实时计算。

• show table status：表总行数的估计值，误差很大
• 将总数维护到Redis里：利用原子操作
• 增加计数表
• count(1) = count(*) > count(二级索引字段（可能丢为NULL的计数）) > count(主键）