mysql优化常用备忘录

1. 查询慢的一些原因

2. SQL执行顺序

3. 索引

4. 性能分析

5. 索引优化

6. 查询优化

7. 慢查询日志

最后总结

参考资料

记录整理常用的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. 适合建立索引的条件
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 查询中排序的字段
  • 查询中统计或者分组字段
  1. 不适合建立索引的条件
  • 表记录太少
  • 频繁更新的字段，每次更新还会更新索引，加重了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查看系统的性能状态
  1. 查看执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。分析查询语句或是表结构的性能瓶颈，可以分析出以下的关系。
  • 表的读取顺序；
  • 数据读取操作的操作类型；
  • 哪些索引可以使用；
  • 哪些索引被实际使用；
  • 表之间的引用；
  • 每张表有多少行被优化器查询
  1. 执行计划包含的信息
  • 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高版本已经做了优化。具体可以参考
  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子句条件列组合满足索引最左前列。
  1. group by和distinct优化，实质是先排序后进行分组，遵照索引键的最佳左前缀；当无法使用索引时，会使用临时表或者文件排序来做分组。如果需要对关联査询做分组（GROUP BY),并且是按照査找表中的某个列进行分组，那 么通常采用査找表的标识列分组的效率会比其他列更髙。
  2. 由于统计的失误，导致系统没有走索引，而是走了全表扫描。可以通过
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，锁
  1. 全局查询日志，尽量在测试环境上进行。永远不要再生产环境开启这个功能。
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
  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