mysql优化常用备忘录

记录整理常用的mysql优化知识，供学习和工作备注使用。

1. 查询慢的一些原因

主要表现在mysql查询慢，执行时间长，等待时间长。

1. 查询语句写的烂，多级子查询嵌套。
2. 索引失效，优化器没有执行索引，高水位。
3. 关联查询太多join（设计缺陷，业务调整）。
4. 服务器调优及各个参数设置（缓冲、线程数等）。
5. 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
6. 同步等待锁，如执行的时候，遇到锁，如表锁、行锁。
7. 机器资源被耗尽，CPU和内存占用率高。
8. 数据量本身很大，需要分库分表或者数据迁移归档。
9. 没有走分区，如果使用分区的话。

2. SQL执行顺序

select解析的顺序，from -> on -> join -> where -> group by -> having -> select -> order by -> limit

3. 索引

1. 索引是一种数据结构，可以高效获取数据，提高查询效率。可以理解为“排好序的便于快速查找的数据结构”。
2. 平常所说的索引，如果没有特别说明，都是指B+树，是一种多路搜索树。叶子节点存储数据，相邻的叶子节点用链表相连，可以快速范围查找，树的高度低，自顶而下查找，查找稳定。B-树子节点都会存储数据，不支持范围查找。
3. 索引文件本身也会占用空间，索引往往以索引文件的形式存储在磁盘上。
4. 索引提高了查询的速度，同时会降低更新表的速度。因为在更新表时，Mysql不仅要保存数据，还要向索引文件中更新索引列字段，会调整因为更新带来的键值变化后的索引信息。
5. 索引只是提高效率的一个因素，如果mysql有较多的数据（小于2KW），需要花时间研究建立最优秀的索引，或优化查询语句。
6. 索引分类：单值索引，唯一索引，复合索引。
7. 索引结构：BTree索引，Hash索引，full-text全文索引，R-Tree索引。
8. 适合建立索引的条件
   • 主键自动建立唯一索引
   • 频繁作为查询条件的字段应该创建索引
   • 查询中与其他表关联的字段，外键关系建立索引
   • 查询中排序的字段
   • 查询中统计或者分组字段
9. 不适合建立索引的条件
   • 表记录太少
   • 频繁更新的字段，每次更新还会更新索引，加重了IO负担。
   • 数据重复且分布平均的字段 ，辨识度不高。
   • 经常增删改的表

4. 性能分析

1. mysql Query Optimizer，mysql中专门负责优化select语句的优化器模块，主要功能是计算分析系统中收集到的统计信息，为客户端请求的Query提供认为最有的执行计划。当客户端向mysql请求一条query，命令解析模块完成请求分类，区别出是SELECT并转发给mysql Query Optimizer时，优化器首先会对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转化，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的hint信息，判断hint信息是否可以完全确定该Query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
2. mysql常见瓶颈：
   • CPU，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
   • IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
   • 服务器硬件的性能瓶颈：top,free,iostat,vmstat查看系统的性能状态
3. 查看执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈，可以分析出以下的关系。
   • 表的读取顺序；
   • 数据读取操作的操作类型；
   • 哪些索引可以使用；
   • 哪些索引被实际使用；
   • 表之间的引用；
   • 每张表有多少行被优化器查询
4. 执行计划包含的信息
   
   • id，select查询的序列号，是一组数字，表示查询中执行select子句或操作表的顺序。ID相同，表示执行顺序是由上而下的；如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
   • DERIVED = 衍生虚表 DERIVED2 = 衍生虚表 + ID
   • select_type，查询的类型，主要用于区别 普通查询、联合查询、子查询等的复杂查询
     • SIMPLE，简单的select查询，查询中不包含子查询或者UNION
     • PRIMARY，查询中若包含任何复杂的字部分，最外层查询则被标记，最后执行的
     • SUBQUERY，在SELECT或者WHERE列表中包含了子查询
     • DEVIED，在from列表中包含的子查询被标记为DERIVED，MYSQL会递归执行这些子查询，把结果放在临时表里
     • UNION，若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
     • UNION REULT，从UNION表获取结果的SELECT
   • table，显示这一行是属于哪一张表的
   • type， 访问类型排序，从最好到最差依次为， system > const > eq_ref > ref > range > index > all； 一般来说，得保证查询至少达到range级别，最好能达到ref。
     • System, 表中只有一行记录（等于系统表），这是const的特列，平时不会出现，这个也可以忽略不计
     • const, 表示通过索引一次就找到了，用于比较primary key或者unique索引。因为只匹配一行数据，索引很快。如将主键置于where条件，MYSQL就能将该查询转化为一个常量。
     • eq_ref, 唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
     • ref, 非唯一性索引扫描，返回匹配某个单独之的所有行；本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
     • range, 只检索给定范围的行，使用一个索引来选择行。key列显示了使用了哪个索引；一般就是在你的where语句中出现了between,<,>,in等的查询；这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一个点，而技术于另一个点，不用扫描全部索引
     • index, Full Index Scan，index于ALL区别为Index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是所虽然all和index都是读全表，但是index是从索引读取的，而all是从硬盘中读的）这里和阿里的开发文档上不太一样，阿里的那条规范比较绝对，如果是覆盖索引的话，index应该还是比全表扫描要好
     • ALL,Full Table Scan，将遍历全表以找到匹配的行。
   • possible_keys, 显示可能应用在这张表中的索引，一个或多个；查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
   • key, 实际使用的索引。如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现在key列表中，其中possible_keys为NULL，此时Extra列中会出现Using index。
   • key_len, 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
   • ref, 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。NULL表示一般type为index
   • rows, 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
   • Extra, 包含不适合在其他列中显示但十分重要的额外信息；
     • using filesort，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，Mysql中无法利用索引完成的排序操作成为“文件排序”。
     • using temporary, 使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
     • using index, 表示相应的select操作中使用了覆盖索引，避免了访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where,表明索引用来读取数据而非执行查找动作（覆盖索引,type=index）。
     • using where, 表明使用了where过滤
     • using join buffer, 使用了连接缓存
     • impossible where, where子句的值总是false，不能用来获取任何元组，（同个字段使用了2个条件）
     • select tables optimized away, 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不比等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
     • distinct, 优化distint操作，在找到第一匹配的元祖后即停止找同样值的工作。

注意：如果要使用覆盖索引，一定要注意select列表中只读取需要的列，不可select *， 因为如果要将所有字段一起做索引会导致索引文件过大，查询性能下降。

5. 索引优化

1. 左链接，LEFTJOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是关键，一定需要在右边建立索引
2. 最佳左前缀法则，如果索引中包含了多列（复合索引），要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中列。（B+树原理）；尽量在之前的索引上加列，减少冗余和重复索引
3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转化），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中的范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询或者索引列和查询列一致），减少select*，当然这不是为了利用覆盖索引而将索引包含所有的列
6. mysql在使用不等式（！=或者<>）的时候无法使用索引会导致全表扫描
7. is null,is not null也无法使用索引，在mysql5.7上测试还是走索引，估计是mysql高版本已经做了优化。具体可以参考https://juejin.im/post/5d5defc2518825591523a1db
8. like以通配符开头（%abc…）的索引会失效变成全表扫描，可以将该字段放到覆盖索引
9. 字符串不加单引号索引会失效
10. 少用or，用它来连接时会索引失效
11. 未使用的索引可以去除，减少存储空间，查看每个索引的使用频率。

select * from information_schema.STATISTICS;

通过查看

select * from performance_schema.table_io_waits_summary_by_index_usage;

可以获得系统运行到现在，哪些索引从来没有被用过。
13. 对于单键索引，尽量选择针对当前query过滤性更好的索引
14. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
15. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
16. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

6. 查询优化

1. 永远小表驱动大表，即小的数据集驱动大的数据集；当B表的数据集必须小于A表的数据集时，用in优于exists。当A表的数据集小于B表的数据集时，用exists优于in。
2. order by子句，尽量使用Index方式排序，避免使用FileSor方式排序。
   • Mysql支持二种方式的排序，FileSort和Index,Index效率高，它指Mysql扫描索引本身完成排序。FileSort方式效率低，filesort有2种排序算法。
     • 双路排序，两次扫描磁盘，最终获取数据。读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。主要在Mysql4.1之前使用的。
     • 单路排序，从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中的了。
     • 优化策略，增大sort_buffer_size参数的设置；增大max_length_for_sort_data参数的设置。
   • Order by后多个字段尽量使用一种排序规则，全部是升序或者降序。
   • order by满足两种情况，会使用Index方式排序：Order by语句使用索引最左前列；使用Where子句与Order BY子句条件列组合满足索引最左前列。
3. group by和distinct优化，实质是先排序后进行分组，遵照索引键的最佳左前缀；当无法使用索引时，会使用临时表或者文件排序来做分组。如果需要对关联査询做分组（GROUP BY),并且是按照査找表中的某个列进行分组，那 么通常采用査找表的标识列分组的效率会比其他列更髙。
4. 由于统计的失误，导致系统没有走索引，而是走了全表扫描。可以通过

show index from t;

来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令

analyze table t;

来重新统计分析。

7. 慢查询日志

1. mysql中记录查询较慢的sql的日志，查看是否开启，SHOW VARIABLES LIKE’%slow_query_log%’，可配置时间阈值
2. mysql的日志分析工具-mysqldumpshow，命令行，可以方便在生产上使用。
3. show profile，是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。功能强大，可以用于SQL的调优的测量默认情况下。参数处于关闭状态，并保存最近15次的运行结果；
   • 诊断SQL，show profile cpu，block i of or query上一步前面的问题SQL数字号码。可完整查看一条SQL的执行情况，如耗时、IO等。
   • 日常开发需要注意的结论
     • converting HEAP to MyISAM查询结果太大， 内存都不够用了往磁盘上搬了。
     • Crea ing tmp table创建临时表图；拷贝数据到临时表；用完再删除
     • Copying to tmp table on disk把内存中临时表复制到磁盘， 危险!!!
     • locked，锁
4. 全局查询日志，尽量在测试环境上进行。永远不要再生产环境开启这个功能。

set global general_log=1；
set global log_output='TABLE'

此后， 你所编写的sql语句， 将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log；

最后总结

主要是https://www.bilibili.com/video/BV1RE41187Yo?p=20当中的学习内容。。

参考资料

1. https://zhuanlan.zhihu.com/p/109903803?utm_source=ZHAppShareTargetIDZhihuClub&utm_medium=social&utm_oi=830045516543959040
2. https://www.cnblogs.com/cchust/p/5061131.html
3. 高性能MySql（第三版）
4. MySql技术内幕InnoDB存储引擎（第二版）
5. https://github.com/Snailclimb/JavaGuide/blob/master/docs/database/MySQL%E9%AB%98%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E8%A7%84%E8%8C%83%E5%BB%BA%E8%AE%AE.md
6. https://www.bilibili.com/video/BV1RE41187Yo?p=20