MySQL高级--查询优化
- 遇到sql优化我们应该如何处理，处理步骤：
-
分析:
-
1. 观察，至少跑1天，看看生产的慢SQL情况。
-
2. 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL， 并将它抓取出来。
-
3. explain+慢SQL分析
-
4. show profile
-
5. 运维经理 or DBA,进行SQL数据库服务器的参数调优。
-
-
总结：
1. 慢查询的开启并捕获
2. explain+慢SQL分析
3. showprofile查询SQL在Mysq1服务器里面的执行细节和生命周期情况
4. SQL数据库服务器的参数调优。
-
-
永远小表驱动大表
-
优化原则:小表驱动大表,即小的数据集驱动大的数据集。
-
select * from A where id in (select id from B)
-
等价于:
-
for select id from B
-
for select * from A where A.id = B.id .
-
当B表的数据集必须小于A表的数据集时，用in优于exists。
-
-
select * from A where exists (select 1 from B where B.id = A.id)
-
等价于
-
for select * from A
-
for select * from B where B.id = A.id
-
当A表的数据集系小于B表的数据集时，用exists优于in。
-
注意: A表与表的ID字段应建立索引。
-
-
EXISTS
-
SELECT ... FROM table WHERE EXISTS (subquery)
-
该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。
-
-
提示
-
1 EXISTS (subquery)只返回TRUE或FALSE， 因此子查询中的SELECT x也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别.
-
2 EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
-
3 EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
-
-
-
-
-
order by关键字优化：
- 建表插入数据：
-
CREATE TABLE tblA(
id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL); -
INSERT INTO tblA(age ,birth) VALUES(22,NOW());
INSERT INTO tblA(age,birth) VALUES(23,NOW());
INSERT INTO tblA(age,birth) VALUES(24,NOW());
-
-
建立索引：CREATE INDEX idx_A_ageBirth ON tblA(age,birth);
-
ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
-
MySQL支持二种方式的排序，FileSort和Index， Index效 率高.它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
-
ORDER BY满足两情况，会使用Index方式排序:
- ORDER BY语句使用索引最左前列
-
使用Where子句与Order BY子句条件列组合满足索引最左前列
-
-
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
-
如果不在索引列上，filesort有 两种算法:
- mysq|就要启动双路排序和单路排序
-
双路排序：
- MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据,读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表读取数据。
-
从磁盘取排序字段，在buffer进行排序， 再从磁盘取其他字段。
- 去一批数据，要对磁盘进行两次扫描，众所周知，I\o是很耗时的，所以在MySQ4.1之后，出现了第二种算法，就是单路排序
-
单路排序：
-
从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
-
它的效率更快- -些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间
-
因为它把每一行都保存在内存中了。
-
-
结论及引申出的问题：
-
由于单路是后出的，总体而言好过双路
-
但是用单路有问题：
-
在sort_ buffer中， 方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了
-
sort_ _buffer的容量， 导致每次只能取sort_ _buffer容量大小的数据，进行排序(创建tmp文件， 多路合并)，排完再取取
-
sort_ buffer容量大小， 再排...从而多次I/O.
-
本来想省-次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
-
-
-
优化策略：
-
增大sort_ buffer_ size参 数的设置
-
增大max_ length_ _for_ sort_ data 参数的设置
-
Why
-
-
提高Order By的速度：
-
1. Order by时select * 是一个大忌只Query需要的字段，这点非常重要。 在这里的影响是:
-
1.1当Query的字段大小总和小于max_ _length_ _for_ sort_ _data 而且排序字段不是TEXT|BLOB类型时，会用改进后的算法一-单路排序，否则用老算法一 一多路排序。
-
1.2两种算法的数据都有可能超出sort _buffer的容量， 超出之后，会创建tmp文件进行合并排序，导致多次I/O, 但是用单路排序算法的风险会更大- - 些，所以要提高sort_ buffer_ size.
-
-
2.尝试提高sort_ buffer_ size不管 用哪种算法，提高这个参数都会提高效率， 当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
-
3.尝试提高max_ _length_ for_ _sort_ data提高这个参数，会增加用改进算法的概率。 但是如果设的太高，数据总容量超出sort_ _buffer_ size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.
-
- 总结：
-
为排序使用索引
-
MySq|两种排序方式:文件排序或扫描有序索引排序
-
MySq|能为排序与查询使用相同的索引
-
-
KEYa_b_c(a,b, c)
-
order by能使用索引最左前缀
- ORDER BY a
- ORDERBY a,b
- ORDERBY a, b,c
- ORDERBY a DESC, b DESC,C DESC -
如果WHERE使用素引的最左前缀定义为常量,则order by能使用索引
- WHERE a= const ORDER BY b, C
- WHEREa = const ANDb = const ORDERBY C
- WHEREa= const ORDER BY b, C
- WHEREa= const ANDb > const ORDERBY b, C -
不能使用索引进行排序
- ORDER BY aASC, b DESC, c DESC /* 排序不一致*/
- WHERE g = const ORDERBYb,c丢失a素引 */
- WHERE a= const ORDER BYc /* 丢失b素引| */
- WHERE a = const ORDER BYa,d /* d不是素引的一部分*/
- WHERE a in (.) ORDER BYb,c /*对于排序来说,多个相等条件也是范围查询
-
- 建表插入数据：
-
group by关键字优化
-
group by实质是先排序后进行分组，遵照索引建的最佳左前缀
-
当无法使用索引列，增大max_ _length_ _for_ sort_ data 参数的设置+增大sort_ _buffer_ size参 数的设置
-
where高于having，能写在where限定的条件就不要去having限定了。
-
