Mysql性能优化

本文宗旨在于详述Java开发工程师如何写出高性能SQL语句。

关系型数据库

在各类ORM框架充斥的项目中，开发人员或者学习者很久没有手写一条SQL语句是很正常的事情，毕竟一个接口一个配置一个注解就能搞定的DAO层实在没有太多可以操作的空间，即便想写一条两表连接查询的SQL语句，这样的需求也实在是难找。现在的市场行情是：大部分互联网企业采用的还是传统关系型数据库＋新型NoSQL数据库的方式。并且大部分使用的是Mysql数据库，但好多企业里面的神仙都会将Mysql数据库的存储引擎修改，甚至替换。和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
在进入正题之前先来复习一下传统数据库知识点：

- ACID
关系型数据库遵循ACID规则，事务在英文中是transaction，和现实世界中的交易很类似，它有如下四个特性：
原子性：Atomicity 一个事务不可再分割，要么都执行要么都不执行
一致性：Consistency 一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：lsolation 一个事务的执行不受其他事务的干扰
持久性：durability 一个事务一旦提交，则会永久的改变数据库的数据.

- 事务的隔离级别
数据库事务的隔离性质是为了防止在并发情况下脏读、幻读、不可重复读的发生。
脏读：一个事务读取了另一个事务更新但还没提交的数据，若回滚，读取到的数据就是无效的。
不可重复读：一个事务读取了数据，另一个事务更新了该数据，若第一个事务再次读取，值就不同了
幻读：一个事务读取了数据，另一个事务插入了一些新的数据，第一个事务再次读取就会多出一些来
事务的隔离级别分为四种：
未提交读 read uncommited 脏读，不可重复读，虚读都有可能发生
已提交读 read commited 避免脏读。但是不可重复读和虚读有可能发生
可重复读 repeatable read 避免脏读和不可重复读.但是虚读有可能发生.
串行化的 serializable 避免以上所有读问题.
Oracle 支持read commited，serializable
Mysql支持全部4种，默认是 repeatable read

数据库事务是面试经常问的，除了事务还有DML数据库操作语句、DDL数据定义语言、DQL的数据库连接查询、子查询、各种字句（group by、having、on、limit、order by等等）、建数据库视图view、函数function、存储过程procedure、循环分支等等这些是基础内容，这里将不做介绍。

索引

存储引擎
主要介绍两种：

• InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况。

• MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
除了这两种之外还有：
Archive引擎、Blackhole引擎、CSV引擎、Memory引擎、Federated引擎

在Mysql眼里一条SQL语句是这么执行的

索引是什么？
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。排好序的快速查找数据结构就是索引。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
索引会影响到order by 排序和where后面的查找。
除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

所以，索引的实质就是一种排好序的数据结构。

应该建立索引的情况
1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创建索引where字句
3、查询中与其它表关联的字段
4、外键关系建立索引，单键/组合索引的选择问题
5、在高并发的情况下倾向于创建组合索引
6、查询中排序字段，排序字段如果通过索引去访问将大大提高排序速度
7、查询中统计或者分组字段

不应该建索引的情况
1、 表的记录太少经常会增删改的表
2、where条件里用不到的字段不创建索引。Why？提高了查询速度，同时却会降低更新表的速度
3、如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件
4、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
5、注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

Explain
直接使用explain进行SQL语句的性能分析

看这张表就能评价SQL到底写的好不好了，怎么看呢？

explain是Mysql进行SQL性能分析的一个重要手段，SQL语句写的好不好使用这种方式就能显而易见的评价了，观察表头主要重视这几个指标：

1. id
   select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
   id相同，执行顺序由上至下
   id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
   id相同不同，同时存在
   小表驱动大表，需要逐步的微调和优化加载顺序
2. select_type
   查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，可能出现的值是
   -simple 简单的 select 查询,查询中不包含子查询或者UNION
   -primiary 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
   -derived 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。
   -subquery 在SELECT或WHERE列表中包含了子查询
   -dependent subquery 在SELECT或WHERE列表中包含了子查询,子查询基于外层
   -uncacheable subquery 无法被缓存的子查询
   -union 若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
   -union result 从UNION表获取结果的SELECT
3. table
   显示这一行的数据是关于那张表的
4. type
   显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： system>const>eq_ref>ref>range>index>ALL 一般来说，得保证查询至少达到range级别，最好能达到ref
5. possible_key
   显示可能应用在这张表上的索引，一个或者多个，查询涉及到的字段上若存在索引，则该索引将被抛出，但不一定被查询实际应用
6. key
   实际使用的查询，如果为null，表示没有使用索引，查询中若使用了覆盖索引，则索引和查询的select字段重叠。
7. key_len
   表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引
8. ref
   显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
9. rows
   rows列显示MySQL认为它执行查询时必须检查的行数。
10. extra
    包含不适合在其他列中显示但十分重要的额外信息
    -Using filesort文件内排序 九死一生，这种时候比较危险，就是建立的索引没有全部使用，只是使用了一部分。
    -Using temporary 新建了临时表 十死无生 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    -Using Index表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
    覆盖索引：建立的索引是col1_col2，查询的列表刚好就是col1 col2或者部分满足。
    -Using where 使用了where进行过滤
    -Using join buffer 使用了连接缓存

优化分析案例

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

结论：type 有All
添加索引优化
左连接把索引加在右边
ALTER TABLE book ADD INDEX Y ( card);
第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

第二行的 type 变为了 ref,rows 也变成了优化比较明显。
继续优化索引：
删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

发现type变成了AlL那么是第二种情况下的索引好，为什么呢？
左连接左表全部都有，这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。索引反正建立，能保证小表驱动大表。

这只是最简单的建立索引优化查询语句的简单案例，需要不断练习，不断检验，一次次尝试才能写出好的SQL语句。关于索引优化，索引失效等等还有好多内容，限于篇幅，不再赘述。

查询优化

• 子查询优化
  优化的原则是：小表驱动大表，用小的数据集合去驱动大的数据集合
  select * from A where id in (select id from B)
  当B表的数据集小于A表的数据集时，用in优于exists
  select * from A where exists (select 1 from B where b.id = a.id)
  当A表的数据集小于B表的数据集时，用exists优于in

• order by 字句优化

create table if not exists tblA(
id int primary key not null auto_increment,
age int,
birth timestamp not null
);
create index idx_a_agebirth on tblA(age,birth);

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
尽可能在索引列上完成排序，遵照索引的最佳左前缀原则，如果不再索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序。
双路排序：
Mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中的值重新从列表中读取对应的数据输出。
单路排序：
从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,到那时它会使用更多的空间，因为它把每一行都保存在内存中了。
但是单路排序的使用是会产生问题的，就是能不能一次抓完。有可能取出的数据的总大小超过了sort_buffer的容量，导致每次只能娶sort_buffer容量大小的数据进行排序，排序完成之后再次读取，导致磁盘多次IO,反而得不偿失。
优化策略是：
增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置
结论：提高Order By的速度：
1、 Order by时 select * 是一个大忌，只写需要query的字段就可以，这里会产生的影响是：当query的字段大小总和小鱼max_length_for_sort_data 而且排序字段不是text或者blob类型时候，会使用单路排序算法，否则就会使用双路排序算法。两种算法都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是使用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。select * 会很容易把缓冲区用满。
2、 尝试提高sort_buffer_size，不管使用哪种排序算法，提高这个参数都会提高效率，当然需要根据系统的性能去调整。
3、 尝试提高max_length_for_sort_data,会增加用改进算法的概率，但是设置的太高，数据总量超出sort_buffer_size的概率也就增大。
小结：
为排序使用索引
Mysql两种排序方式：文件排序 filesort 或扫描有序索引排序表 using index
Mysql能为排序与查询使用相同的索引

• group by 字句优化

1、group by 的实质是先排序后进行分组，遵照索引建的最佳左前缀原则
2、当无法使用索引列，增大max_length_for_sort_data参数的设置，增大sort_buffer_size参数的设置
3、where高于having，能写在where限定的条件就不要去having 限定了

问题排查步骤

（面试常问）

1、看生产环境上的慢SQL,开启慢查询日志，设置阀值，超过某一时间的sql即为慢SQL
show variables like ‘%slow_query_log%’;
set global slow_query_log=1
使用日志分析工具 mysqldumpshow 可以查看一些特定的SQL语句，比如说含有连接查询、访问次数比较多，返回结果集比较多，就可以看到这些SQL语句。
2、拿到了这些可能会导致查询变慢的SQL语句之后，接下来可以使用explain关键字去进行性能分析，从而知道MySQL是如何处理你的SQL语句，分析你的查询语句或是表结构的性能瓶颈。看是不是有索引失效的情况。
从id去排查select字句的操作表的顺序，看是不是小表驱动大表。
从type去排查是不是索引没有用上出现ALL，至少要保证range级别，最好能达到ref级别才可以。
从key去排查有没有使用索引
从rows去看查询检查了多少行
从extra去看有没有出现文件内排序using filesort、新建临时表using temporary这样特别耗费性能的情况出现。
到这个时候基本就可以解决绝大部分的问题了，其他比较棘手的问题需要使用show profile
3、show profile
先用show profiles 命令去查看一下sql语句的执行时间，当然需要开启这个功能，mysql好多牛逼的功能都是默认关闭的。执行时间长的SQL语句在这个命令下面可以很直观的看到，接着载去查看具体的CPU,IO消耗的时间，拿到这些数据去排查，基本就能解决问题了。
4、最后一些查询语句特别难处理的话需要去调整一些系统参数值来进行优化，就是将sort_buffer_size参数和max_length_for_sort_data这两个参数调大。

SQL索引优化口诀

全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能掉。索引列上少计算，范围之后全失效
Like百分写最右，覆盖索引不写星。不等空值还有or, 索引失效要少用。字符引号不能丢，SQL高级也easy

致敬
本文是学习尚硅谷周阳老师MySQL高级课程的笔记，Mysql高级内容确实很多，当然也很有意思，表锁、行锁、悲观锁、乐观锁等内容也在Mysql高级课程中。NoSQL、Redis等于数据库相关的知识也请期待下一次的技术分享。