sql优化的问题

浅浅的花香味﹌ 2022-06-09 06:27 114阅读 0赞

转载于：[http://blog.csdn.net/lishuangzhe7047/article/details/63684883][http_blog.csdn.net_lishuangzhe7047_article_details_63684883]  
**慢查日志**

*    慢查日志呢就是MySQL日志查询比较慢的SQL语句的日志详细的记录了所有的超过某一时间的SQL语句
 *     22 explain分析低效SQL执行计划
 *  **1. [数据库][Link 1]优化的目的**
    
          1）数据库慢速查询，解决界面内容加载不出来；
    
     2）增加数据库的稳定性；
    
    **2. 定位问题**
    
    ##     2.1 慢查日志 ##
    
    ##   慢查日志呢就是MySQL日志查询比较慢的SQL语句的日志。详细的记录了所有的超过某一时间的SQL语句。 ##
    
      1） 首先查看慢查日志是否开启
    
       show VARIABLES like ‘%slow%’，在结果中会有‘log\_slow\_queries’（或者是slow\_query\_log，MySQL版本不同）关键字及对应的值，默认为‘OFF’关闭状态。
    
       开启慢查日志即在my.ini文件中添加：
    
    **\[html\]** view plain copy
    
    print ?
    
    1.  <span style=“font-size:18px;”>\#mysql-slow  
    2.  log-slow-queries=d:/mysql-slow.log  
    3.  long\_query\_time=0  
    4.  log-queries-not-using-indexes</span>  
    
        #mysql-slow
        log-slow-queries=d:/mysql-slow.log
        long_query_time=0
        log-queries-not-using-indexes
    
         long\_query\_time执行时间超过其值得SQL语句会被记录到日志中。为了测试我这边把它设置为0，实现项目中根据要求设置吧，日志太多了也不好。
    
         2）慢查日志的格式
    
      我创建了一个t\_user表并插入3条数据，为测试慢查日志，我执行一条查询前两条的SQL，慢查日志的内容：
    
    **\[html\]** view plain copy
    
    print ?
    
    1.  <span style=“font-size:18px;”>\# Time: 170319 14:59:38  
    2.  \# User@Host: root\[root\] @ localhost \[127.0.0.1\]  
    3.  \# Query\_time: 0  Lock\_time: 0  Rows\_sent: 2  Rows\_examined: 2  
    4.  select\* from t\_user limit 2;  
    5.  \# User@Host: root\[root\] @ localhost \[127.0.0.1\]  
    6.  \# Query\_time: 0  Lock\_time: 0  Rows\_sent: 15  Rows\_examined: 305  
    7.  SELECT QUERY\_ID, SUM(DURATION) AS SUM\_DURATION FROM INFORMATION\_SCHEMA.PROFILING GROUP BY QUERY\_ID;  
    8.  \# User@Host: root\[root\] @ localhost \[127.0.0.1\]  
    9.  \# Query\_time: 0  Lock\_time: 0  Rows\_sent: 16  Rows\_examined: 307  
    10. SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000490\*100,3), ’%’) AS `Percentage` FROM INFORMATION\_SCHEMA.PROFILING WHERE QUERY\_ID=26 GROUP BY STATE ORDER BY SEQ;</span>  
    
        # Time: 170319 14:59:38
    
    \# User@Host: root\[root\] @ localhost \[127.0.0.1\] \# Query\_time: 0 Lock\_time: 0 Rows\_sent: 2 Rows\_examined: 2 select\* from t\_user limit 2; \# User@Host: root\[root\] @ localhost \[127.0.0.1\] \# Query\_time: 0 Lock\_time: 0 Rows\_sent: 15 Rows\_examined: 305 SELECT QUERY\_ID, SUM(DURATION) AS SUM\_DURATION FROM INFORMATION\_SCHEMA.PROFILING GROUP BY QUERY\_ID; \# User@Host: root\[root\] @ localhost \[127.0.0.1\] \# Query\_time: 0 Lock\_time: 0 Rows\_sent: 16 Rows\_examined: 307 SELECT STATE AS \`Status\`, ROUND(SUM(DURATION),7) AS \`Duration\`, CONCAT(ROUND(SUM(DURATION)/0.000490\*100,3), '%') AS \`Percentage\` FROM INFORMATION\_SCHEMA.PROFILING WHERE QUERY\_ID=26 GROUP BY STATE ORDER BY SEQ;
    
         3）慢查日志的分析
    
      有了日志，下一步就是分析了。当日志量足够大时，就需要使用工具了，慢查日志分析工具有很多，好用就行。这里不在具体介绍。我们不要忘记分析慢查询日志的目的，就是去优化慢查询语句，莫要把时间都放到分析上。
    
      实际中，慢查日志都是有公司DBA负责的，他会将超过时间的日志发给开发人员，由开发人员去优化。
    
    ##    2.2 explain分析低效SQL执行计划 ##
    
         我们可以通过 explain 或者 desc 获取[MySQL][Link 1] 如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引。
    
      
    
    
               ![Center][]
    
         type：显示连接使用何种类型。从好到坏依次为：const、eq\_reg、ref、range、index、all。
    
       possible\_keys:显示这张表应用的索引。
    
       key:实际使用的索引。
    
       key\_len:索引的长度。
    
       ref:索引的那一列被使用了。
    
      
    
    
        **2.3.通过show status了解各种SQL的执行频率**
    
      Myisam 和 Innodb：
    
       Com\_select 执行 select 操作的次数，一次查询只累加 1；
    
       Com\_insert 执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次；
    
       Com\_update 执行 update 操作的次数；
    
       Com\_delete 执行 delete 操作的次数；
    
      Innodb
    
       Innodb\_rows\_read select 查询返回的行数；
    
       Innodb\_rows\_inserted 执行 Insert 操作插入的行数；
    
       Innodb\_rows\_updated 执行 update 操作更新的行数；
    
       Innodb\_rows\_deleted 执行 delete 操作删除的行数；  
    
    
    **3. 解决问题**
    
    **    3.1索引问题**
    
    1)以%开头的LIKE查询不能够使用BTree索引。
    
    2)数据类型出现隐式转换的时候也不会使用索引。特别是类型为数值时，一定要在where条件后加上引号。
    
    3)符合索引的情况下，查询条件不包含索引列最左部分，即不满足最左原则不会使用符合索引。
    
    4)用or分割开的条件，如果or前有索引，or后没有索引则不使用索引。
    
     **3.2MySQL排序方式**
    
    1)通过有序索引顺序扫描直接返回有序数据。explain时显示的时using index，不需要额外的排序，效率高。
    
    2)通过对返回结果进行拍戏，filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort。
    
     **3.3SQL优化**
    
    1)插入数据
    
     insert into table values (),(),()…
    
    2)order by
    
     3.2中提到了[mysql][Link 1]的排序方式，order by的字段考虑创建索引。
    
     where条件和order by使用相同的索引；并且order by的字段都是升序或都是降序。
    
    ![Center 1][]  
    
    
    3)group by
    
     添加order by null禁止排序来提高效率。
    
    4)or
    
     所有条件都需要建立索引。

[http_blog.csdn.net_lishuangzhe7047_article_details_63684883]: http://blog.csdn.net/lishuangzhe7047/article/details/63684883
[Link 1]: http://lib.csdn.net/base/mysql
[Center]: /images/20220609/0a61575c676647e59a7993fd63bba502.png
[Center 1]: /images/20220609/4f8a4d3f90c44a448de267cb7f972d9d.png