菜鸟谈——mysql之查询截取分析

àì夳堔傛蜴生んèń 2024-02-18 17:54 83阅读 0赞

### 1. 数据表优化步骤 ###

1.  开启慢日志查询日志，设置阙值。。。收集执行超过阙值的sql语句
2.  Explain+sql 分析
3.  ShowProfile
4.  Sql服务器的参数调优

### 2. 查询优化 ###

#### 2.1 永远小表驱动大表 ####

#### 2.2 Order by 关键字优化 ####

#### 2.3 Group by关键字优化 ####

#### 2.4 小总结 ####

为排序使用索引

Mysql两种排序方式：文件排序（Using filesort）或扫描有序索引排序（Using index）

Mysql能为排序与查询使用相同的索引（索引是排好序的快速查找数据结构）

**KEY a\_b\_c(a,b,c)**

##### 2.4.1 **Order by *\****能使用索引最左前缀\* #####

-ORDER BY a
    
    -ORDER BY a,b
    
    -ORDER BY a,b,c
    
    -ORDER BY a DESC,b DESC,c DESC（同升同降）

##### 2.4.2 **如果WHERE\*\***使用索引的最左前缀定义为常量，则order by能使用索引\*\* #####

-WHERE a=const ORDER BY b,c
    
    -WHERE a=const AND b=const ORDER BY c
    
    -WHERE a=const ORDER BY b,c
    
    -WHERE a=const AND b>const PRDER BY b,c

##### 2.4.3 **不能使用索引进行排序** #####

-ORDER BY a ASC,b DESC,c DESC   排序不一致
    
    -WHERE g=const ORDER BY b,c            丢失a索引
    
    -WHERE a=const ORDER BY c                丢失b索引
    
    -WHERE a=const ORDER BY a,d             d不是索引的一部分
    
    -WHERE a in(…) ORDER BY b,c           对于排序来说，多个相等条件也是范围查询

### 3. 慢日志查寻 ###

##### 3.1 是什么 #####

Mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阙值的语句，具体指运行时间超过long\_query\_time值得sql，则会被记录到慢日志中。

具体运行时间超过long\_query\_time值得sql，则会被记录到慢日志中。long\_query\_time的默认值为10，意思是运行10秒以上的语句。

由他来查看那些sql超出了我们的最大忍耐时间值，比如一条sql执行时间超过5秒钟，我们就算慢sql，希望能收集超过5秒的sql，结合之前explain进行全面解析。

##### 3.2 怎么玩 #####

###### 3.2.1 说明 ######

默认情况下，mysql没有开启慢查询日志，需要我们手动设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

###### 3.2.2 查看是否开启及如何开启 ######

查询是否开启

show variables like '%slow_query_log%';

开启

set global slow_query_log = 1;

###### 3.3.3 那么开启了慢查询日志，什么样的sql才会被记录到慢日志里面呢？ ######

*  这个是由参数long\_query\_time控制，默认情况下long\_query\_time值为10秒
 *  命令：SHOW VARIABLES LIKE ‘long\_query\_time%’;  
    ![这里写图片描述][SouthEast]
 *  可以使用命令修改，也可以在my.cnf参数里修改。
 *  假如运行时间正好等于long\_query\_time的情况，并不会被记录下来。。
 *  也就是说在mysql源码里是判断：**大于long\_query\_time，而非大于等于**

###### 3.3.4 case热身 ######

###### 3.3.4.1 查询是否开启 ######

Show variables like ‘%slow\_query\_log%’;  
![这里写图片描述][SouthEast 1]

###### 3.3.4.2 开启，并且设置阙值 ######

Set global slow\_query\_log=1;  
![这里写图片描述][SouthEast 2]

Set global long\_query\_time=3;  
![这里写图片描述][SouthEast 3]

###### 3.3.4.3 为什么设置后看不出变化？ ######

1) 需要重新连接或新开一个会话才能看到修改值。SHOW VARIABLES LIKE’long\_query\_time%’;

2) show global variables like ‘long\_query\_time’;

###### 3.3.4.4 记录慢sql，并用于分析 ######

select sleep(4);

###### 3.3.4.5 查询当前系统中有多少条慢查询记录 ######

Show global status like ‘%slow\_queries%’;  
![这里写图片描述][SouthEast 4]

###### 3.3.5 配置版 ######

#windows系统my.ini配置文件
    # General and Slow logging.
    log-output=FILE
    general-log=0
    general_log_file="2XQBTV8H6ML83VA.log"
    slow-query-log=1
    slow_query_log_file="2XQBTV8H6ML83VA-slow.log"
    long_query_time=10
    
    #linux系统my.cnf配置文件
    #段：[mysqlId]的配置
    slow_query_log=1;
    slow_query_log_file=/var/lib/mysql/xxxxxxx-slow.log;
    long_query_time=3;
    log_output=FILE;

##### 3.3 日志分析工具 #####

mysqldumpslow

[http://blog.csdn.net/yabingshi\_tech/article/details/51220831][http_blog.csdn.net_yabingshi_tech_article_details_51220831]

### 4. ShowProfile ###

#### 4.1 是什么 ####

是mysql用来分析当前会话中语句执行的资源消耗情况。。可以用于sql调优的测量

#### 4.2 默认情况下，处于关闭状态，并且保持最近15次的运行结果 ####

#### 4.3 分析步骤 ####

##### 4.3.1 是否支持 #####

##### 4.3.2 开启（默认是关闭的） #####

##### 4.3.3 运行sql #####

sleep(4);

##### 4.3.4 查看结果 #####

1) Show profiles;

![这里写图片描述][SouthEast 5]

2) 诊断sql，show profile cpu,block io for query上一步前面的问题 sql 数字号码；

如：show profile cpu,block io for query 7;  
![这里写图片描述][SouthEast 6]

##### 4.3.5 日常开发注意 #####

ConvertHEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了

Creatingtmp table 创建临时表，拷贝数据到临时表，用完再删除

Copying totmp table on disk 把内存中临时表复制到磁盘，，，危险！！！！！

locked

### 5. 全局日志查询 ###

[SouthEast]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/73cedf3aa5f2476c865401594f30486a.png
[SouthEast 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5e7e989fad0f45e4857b2458ced9d286.png
[SouthEast 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/206752cacf28464f9c359d617f081f7a.png
[SouthEast 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b778b4f386bf4b25a61c2b4394ea7404.png
[SouthEast 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b819d212a0b84d6d82243fc0ec16dd39.png
[http_blog.csdn.net_yabingshi_tech_article_details_51220831]: http://blog.csdn.net/yabingshi_tech/article/details/51220831
[SouthEast 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fa77d43461744978bc7b50ceed391027.png
[SouthEast 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a0336f7d475b4b43940f05529d62f162.png