查询性能优化
1.检查应用程序是否获取超过需要的数据。访问过多的行和列2.检查MySQL服务器是否分析了超过需要的行(分析开销指标:执行时间,检查和返回的行,检查的行和访问类型(EXPLAIN的type列,包括全表扫描,索引扫描,范围扫描,唯一查找扫描,常量。访问速度依次递增))
重构查询的方式
1.可以考虑把一个复杂查询分解多个简单查询2.缩短查询
3.把多表连接分解成多个单个查询,在应用程序端实现连接操作
查询执行过程
客户端/服务器协议
协议半双工的,可以发送和接受数据,不能同时进行。无法控制流程,一旦一方发送数据,另一方在发送回复消息前,必须完全接受对方发送的数据。客户端如果只有部分数据,要么接受完整数据后,丢弃不需要的数据,要么断开连接。(limit子句的重要性就在这)
查询状态:每个MySQL线程在任何时刻都有一个状态来标识正在进行的事情。
1.休眠:线程等待客户端新查询
2. 查询:线程正在查询或向客户端发送数据
3. 锁定:线程正在等待服务器授予一个表锁
4.分析和统计:线程正在检查存储引擎的统计信息并且优化查询
5. 拷贝到磁盘上的临时表:线程正在处理查询并把结果拷贝到临时表,结果可能是GROUP BY,对文件排序,UNION
6. 排序结果:线程正在对查询结果排序
查询优化过程:解析(解析成解析树),预处理(检查)和优化。
优化:静态优化(简单的探测解析树来完成)和动态优化。MySQL只进行一次静态优化,每次运行查询时候进行动态优化。常用优化类型: 1.对连接中的表重新排序
2.将外连接转换成内连接
3.代数等价法则(优化掉不可能的限制和常量条件)
4.优化COUNT(),MIN(),MAX()
5.计算和减少常量表达式
6.覆盖索引
7.子查询优化
8. 相等传递(可以辨认一个查询中有两个列相等的情况)
MySQL联接执行策略
MySQL将每个查询语句看成一个联接,把每个联接看成一个嵌套循环,用一个循环从表中读取数据,在利用一个嵌套循环从下一个表中发现匹配数据。当发现一行匹配数据时,根据SELECT字句中列输出。接着查找更多匹配的行,如果没有,就回溯到前一个表,用新的行开始下一轮迭代。
MySQL将UNION看成一系列单个查询,将结果写入临时表,在读取出来形成结果。
处理FROM子查询时,先执行子查询,把结果放到临时表,再进行下一步处理
执行计划:采用左深度树
联接优化器:决定了多表查询最佳执行顺序
MySQL查询优化的限制
子查询优化得最差的是where子句中使用IN。MySQL会试着让IN列表与外部表产生联系,可能外表会先执行并且做全表扫描,对每一次执行在做子查询应该把LIMIT、ORDER BY条件添加到UNION内部的每一个子句上,如果在外部MySQL会把所有表放到临时表中,在做条件处理
MySQL不支持扫描不连续的索引
不能很好优化MIN(),MAX()
不能对同一个表进行update同时进行select
优化特定类型的查询
1)优化COUNTcount统计值的数量和统计行的数量。COUNT(*)统计行的数量
2)优化联接
确保ON或USING使用的列上有索引
确保GROUP BY或ORDER BY只引用一个表中的列
3)优化子查询
尽可能使用联接。(子查询创建的临时表没有索引)
4)优化GROUP BY和DISTINCT
都可以从索引中获益
不使用索引时,MySQL优化GROUP BY:使用临时表或文件排序进行分组。使用SQL_SMALL_RESULT强制选择临时表,SQL_BIG_RESULT强制使用文件排序
MySQL会自动对GROUP BY里面的列进行排序
5)优化LIMIT和OFFSET
在覆盖索引上进行偏移,将从覆盖索引上提取出来的数据和全行数据进行联接,取得需要的列
6)优化UNION
始终使用UNION ALL。除非需要服务器消除重复的行