第三章:查询截取分析
![](https://img-blog.csdnimg.cn/f0e8a53e688d43d3bb216aa86e9bc4e8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
优化1:小表驱动大表
exist语法:将主查询的数据放到子查询中做条件验证,判断数据是否可以保留
in的时候先执行内查询(括号),exist的时候先执行外查询
![](https://img-blog.csdnimg.cn/c159a95b8db2415fb366a369f8d9938e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
优化2:Order By关键字优化
单路排序
![](https://img-blog.csdnimg.cn/8801c6ecb15c4326afcc1112d9ab4db7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/2972c0a8737946b0ba8efc4b22e565d0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/0cb629a0cd4d4e37928dcce7bc50cd85.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
优化3:group by关键字优化
![](https://img-blog.csdnimg.cn/f012d5227c484a07a8b197213352440d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
慢查询日志
![](https://img-blog.csdnimg.cn/7781da1d4f614011b61d1204faef12a9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_1)
![](https://img-blog.csdnimg.cn/4598d23da7a7453f9ceaa9d9127554e3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/cfae9afae1504b729f37fe8d6f0c7369.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
show global status like ‘%Slow_queries%’ 可以显示慢sql条数,可用作健康度的检查
cat一下slow.log(数据量少的时候)
MySql提供了日志分析工具mysqldumpslow
![](https://img-blog.csdnimg.cn/61363ff6c67f4823b129984fb6674268.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/fef1ddcbdbca422ab2aacf536f8cfdd9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
分析步骤:
![](https://img-blog.csdnimg.cn/e0bfb04483704509bf8b6884cba77cb3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)
1、SHOW VARIABLES LIKE ‘%profiling%’;
2、SET profiling=ON
![](https://img-blog.csdnimg.cn/af98fd1fbd664f7385f488fb4f7ab4d0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LSl54Gr572X5rGJ5p6c,size_20,color_FFFFFF,t_70,g_se,x_16)