MySQL之SQL性能（3）——查询截取分析（1）——查询优化、慢查询日志

目录

1.生产中SQL优化步骤

2.查询优化

（1）永远小表驱动大表

（2）order by关键字优化

1）order by子句尽量使用index方式来排序，避免使用filesort方式排序

2）双路排序和单路排序

 3）order by排序总结

（3）group by关键字优化

3.慢查询日志

（1）慢查询是什么

（2）慢查询怎么用？

1）说明

2）查看是否开启以及如何开启

3）开启慢日志查询以后，什么样的日志才会记录到慢查询日志里面？

4）演示慢查询

（3）日志分析工具mysqldumpslow

---

1.生产中SQL优化步骤

• 1.观察，至少跑一天，看看生产的慢SQL情况
• 2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来
• 3.explain+慢查询SQL分析
• 4.show profile查询SQL在mysql服务器里面的执行细节和生命周期情况
• 5.SQL数据库服务器的参数调优

2.查询优化

（1）永远小表驱动大表

这里类似于嵌套循环，如下

//第一种情况
for(int i=2;...)
{
    for(int j=1000000;...)
    {

    }
}

=================================

//第二种情况
for(int i=1000000;...)
{
    for(int j=2;...)
    {

    }
}

分析：

第一种情况：需要与数据库2次连接和释放的消耗，每次做1000000次的查询

第二种情况：需要与数据库建立1000000次连接和释放的消耗，每次做2次查询，显然这种方式，严重影响性能

in和exists分析

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 * from B where B.id=A.id)

等价于

for select * from A
for select * from B where B.id=B.id

当A表的数据集小于B表的数据集时，用exists优于in

注意：A表和B表的id字段应该建立索引

Exists

SELECT...FROM table WHERE EXISTS (子查询);

该语法可以理解为：将主查询的数据，放到子查询做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留

提示：

1.EXISTS(子查询)只返回TRUE或FALSE，因为子查询中的SELECT*也可以是SELECT 1或SELECT 'X'，官方说法是实际执行时会忽略SELECT清单，因此没有区别

2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题

3.EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

 （2）order by关键字优化

1）order by子句尽量使用index方式来排序，避免使用filesort方式排序

建表

CREATE TABLE tblA(
	age INT,
	birth TIMESTAMP NOT NULL
);

INSERT INTO tblA(age,birth)VALUES(22,NOW());
INSERT INTO tblA(age,birth)VALUES(22,NOW());
INSERT INTO tblA(age,birth)VALUES(22,NOW());

建立索引

接下来的关注点为会不会产生filesort

1)

order by后排序的字段顺序是按照索引建立的顺序进行，无filesort

2）

order by后排序的字段顺序是按照索引建立的顺序进行，无files