目录
查询慢的原因
- 网络
- CPU
- IO
- 上下文切换
- 系统调用
- 生成统计信息
- 锁等待时间
Mysql 里有表锁 行锁 锁机制和存储引擎相关
优化数据访问
- 性能查询低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化（io）
- 确认应用程序是否在检索大量超过需要的数据
- 确认mysql服务器层是否在分析大量超过需要的数据行
- 是否想数据库请求了不需要的数据
- 查询不需要的记录 limit
- 多表关联时返回全部列 不用*
- 总是取出全部列
- 重复查询相同的数据 查询缓存 版本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%"
- join的实现方式原理
- 排序优化
- 两次传输排序 （第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排号序的结果按照需要去读取数据行，这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机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优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不安预想地方式运行
- 赋值等号:=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号
- 使用未定义变量不会产生任何语法错误
- 自定义变量的使用
445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
