目录
索引优化、查询优化、库表结构优化
慢查询基础：优化数据访问
-
是否向数据库请求了不需要的数据
- 查询不需要的记录：只需要10条记录时，不需要请求所有的然后去掉不需要的。用LIMIT只查询10条数据
- 多表关联时返回全部列：返回需要的列即可
- 总是取出全部列：只取需要的列
- 重复查询相同的数据：初次查询的时候进行缓存
-
MySQL是否在扫描额外的记录
衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。
通过慢查询日志来进行分析。
- 响应时间：包括服务时间和排队时间。排队时间包括等IO操作完成或者等待行锁。
- 扫描的行数和返回的行数
- 扫描的行数和返回的类型
如果发现查询需要扫描大量的数据但只返回少数的行，下面的方式优化：
重构查询的方式
优化查询：以一个更优的方法取获得实际需要的结果
-
一个复杂的查询还是多个简单查询
-
切分查询：如果一个大的查询语句一次性完成的话，则可能需要锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但是重要的查询。
-
分解关联查询
查询执行的基础
-
MySQL客户端/服务端通信协议
半双工通信
查询状态：SHOW FULL PROCESSLIST(SLEEP,QUERY LOCKED,Analyzing and statistics\copying to temp table(on disk),sorting result，sending data)等
-
查询缓存
通过大小写敏感的哈希表实现的，若命中还要检查用户权限，有权限才返回结果。
-
查询优化处理
语法解析器和预处理：生成解析树，预处理验证解析树是否合法，验证权限。
查询优化器
查询优化器就是找到最好的执行计划，基于成本的优化器，选用成本最低的查询计划。
静态优化-编译时优化。动态优化-运行时优化。
MySQL能处理的优化类型
- 重新定义关联表的顺序：循环嵌套查询
- 将外连接转换成内连接
- 使用等价变换规则：简化并规范表达式
- 优化COUNT()、MIN()、MAX()：索引和列是否为空通常可以帮助优化这类表达式。mysisam存储了变量来记录数据的行数。
- 预估并转换为常数表达式：如果一个变量可以转换为常数时，就把该表达式作为常数。
- 覆盖索引扫描
- 子查询优化：in or exist
- 提前终止查询：limit
- 等值传播：两个列的值通过等式关联，那么会进行传递。
- 列表IN()的比较：与or比较，in会先进行排序，然后进行二分查找，复杂度为o(logn)
数据和索引的统计信息
由存储引擎提供给服务器，服务器会根据这些统计信息来选择一个最优的执行计划。
关联查询
- mysql如何执行关联查询
“嵌套循环查询”。在第一个表中找到一个匹配的数据之后，去第二个表关联查询，第二个表查询完成之后，再返回第一个表中下一个匹配的数据，重复操作。
- 执行计划
- 关联查询优化器
关联查询优化器通过评估不同顺序时的成本代价来选择一个代价最小的关联顺序。
最好是用小表驱动大表。
好的关联顺序会让查询进行更少的嵌套和回溯操作。
当需要关联的表超过指定值时采用“贪婪”搜索策略。
左连接和子查询不能随意更改顺序，因为后面的查询结果依赖前面表的查询结果，可以减少扫面的表的行数。
排序优化
MySQL在排序时，如果数据量较小在内存中进行排序，如果数据量较大，在磁盘中进行排序。不使用索引扫描进行排序的时候需要使用临时表。
临时表需要的空间很大，因为对每一个记录分配足够厂的空间，就是每一个字段的最长空间之和。
使用Limit子句，不再对所有结果进行排序，节省空间。
查询执行引擎
返回结果给客户端
MySQL查询优化器的局限性
关联子查询
对外层的数据行进行全表扫描，然后一个一个去匹配子查询的数据。（小表驱动大表）
如何用好关联子查询
UNION的限制
对单个查询都是用Limit可以减少临时表的大小。
索引合并优化
MySQL能够访问单个表的多个索引以合并和交叉过滤的方式定位需要查找的行。
等值传递
最大值和最小值优化
在同一个表查询和更新
不允许。
优化特定类型的查询
优化count（）查询
count(*)统计结果集的行数，count（指定列)统计列值的数量
优化关联查询
确保on或者using子句中的列都在索引上
确保GROUP BY和ORDER BY中的表达式只涉及一个表中的列（可以使用索引）
优化子查询
尽可能使用关联查询替代
优化GROUP和DISTINCT
可以使用索引来进行优化
优化LIMIT分页
加上ORDER BY时使用索引
优化SQL_CALC_FOUND_ROWS
优化UNION查询
限制每个子查询
扫一扫
1994
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
