Mysql调优(5)查询优化

目录

查询慢的原因

优化数据访问

执行过程的优化

优化特定类型的查询

1. 网络
2. CPU
3. IO
4. 上下文切换
5. 系统调用
6. 生成统计信息
7. 锁等待时间

Mysql 里有表锁 行锁 锁机制和存储引擎相关

优化数据访问

1. 性能查询低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化（io）
   1. 确认应用程序是否在检索大量超过需要的数据
   2. 确认mysql服务器层是否在分析大量超过需要的数据行
2. 是否想数据库请求了不需要的数据
   1. 查询不需要的记录 limit
   2. 多表关联时返回全部列 不用*
   3. 总是取出全部列
   4. 重复查询相同的数据 查询缓存 版本8之后被干掉了

执行过程的优化

查询缓存

查询优化处理

• • 语法解析器和预处理 抽象语法树 根据关键字进行了切分
  • 查询优化器 （*重点）当语法树没有问题后，相应的要由优化器将其转成执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的， 优化器的最主要目的就是要选择最有效的执行计划
  • • show status like 'last_query_cost' 可以看到这条查询语句需要多少数据页可以找到对应的数据，是根据统计计算的（每个表或索引的页面个数，索引的基数，索引和数据行的长度，索引的分布情况)
    • 在很多时候mysql会选择错误的执行计划
    • mysql不会考虑不受其控制的操作成本
    • mysql不考虑其他并发执行的查询（执行存储过程或者用户自定义函数的成本）
    • mysql的最优可能跟你想的不一样（mysql的优化是基于成本模型的优化，但是有能不是最快的优化）
    • 执行计划的成本估算不等同于实际执行的成本（有时候某个执行计划虽然需要读取更多的页面，但是他的成本更小，原因是这些页面都是顺序读或者已经存在内存中，那么访问成本就会狠下，mysql层面不知道哪些页面在内存中，哪些在磁盘，所以查询之际执行过程中到底需要多少次io是无法得知的）
    • 统计信息不准确 (innodb因为其mvcc的架构，并不能维护一个数据表的行数和精确统计信息）
    • 优化器的优化策略
      • mysql对查询的静态优化只需要一次，但对动态优化在每一次执行时都需要重新评估
      • 动态优化 动态优化和查询的上下文有关，也可能跟取值、索引对应的行数有关
      • 静态优化 直接对解析树进行分析，并完成优化
      • 优化器的优化类型
        • 重新定义关联表的顺序 (数据表的关联并不总是暗中在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能）
        • 将外连接转化成内连接，内连接的效率要高于外连接
        • 使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式
        • 优化count(),min(),max()
        • 预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理
        • 索引覆盖扫描，当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引
        • 子查询优化
        • 等值传播
      • 关联查询
        • join的实现方式原理
          • simple nested-loop join
          • index nested-loop join
          • block nested-loop join
            • join buffer 会缓存所有参与查询的列而不是只有join的列
            • 可以通过调整join_buffer_size 缓存大小
            • join_buffer_size的默认值是256k,join_buffer_size的最大值再mysql 5.1.22版本之前是4G-1，而之后的版本才能才64位操作系统下申请大于4G的Join buffer空间
            • 使用block nested-loop join 算法需要开启优化器管理配置的loptimizer_switch的设置block_nested_loop为on，默认为开启。
            • show variables like "%join_buffer%"
      • 排序优化
        • 两次传输排序 （第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排号序的结果按照需要去读取数据行，这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机io，读取数据成本比较高。两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
        • 单次传输排序（先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序io读取所有的数据，而无需任何的随机io，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据。）
        • 当需要排序的列的总大小加上orderby的列大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

优化特定类型的查询

• 优化count()查询
  • 总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的
  • 使用近似值 在某些应用场景中，不需要完全精准的值，可以参考使用近似值来代替，比如可以使用explain来获取近似的值，其实很多OLAP的应用中，需要计算某个列值的基数，有一个计算近似值的算法交hyperloglog
  • 更复杂的优化 一般情况下，count()需要扫描大量的行才能获取精确的数据，其实很难优化，在实际操作的时候可以考虑使用索引覆盖扫描，或者添加汇总表，或者增加外部缓存系统。
• 优化关联查询
  • 确保on或者using子句中的列有索引，在创建索引的时候就要考虑到关联的顺序
  • 确保任何的groupby和orderby中的表达式只涉及到一共表中的列，这样mysql才有可能使用索引来优化这个过程
• 优化子查询 子查询的优化最重要的优化建议就是尽可能使用关联查询代替（子查询是临时表 其实也是io 还不如join 关联）
• 优化group by 和distinct 很多场景下，mysql使用相同的方法来优化groupby和distinct的查询，使用索引是最有效的方式，当时有很多的情况下无法使用索引，可以使用临时表或者文件排序来分组
  • 如果对关联查询做分组，并且是按照查找表中的某个列进行分组，那么可以采用查找表的标识列分组的效率比其他列更高
• 优化limit分页 在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能
  • 优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列
• 优化union查询 除非确实需要服务器消除重复的行，否则一定要使用union all，因此美欧all 关键字，mysql 会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高。
• 推荐使用用户自定义变量
  • 自定义变量的使用
    • set @one := 1
    • set @min_actor := select min(actor_id)from actor
  • 自定义变量的限制
    • 无法使用查询缓存
    • 无能在使用常量或者标识符的地方使用自定义变量
    • 用户自定义变量的生命周期是在一个连接中有效，所以不能用他们来做连接间的通信
    • 不能显式地生命自定义变量地类型
    • mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不安预想地方式运行
    • 赋值等号:=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
    • 使用未定义变量不会产生任何语法错误