sql优化的问题

转载于：http://blog.youkuaiyun.com/lishuangzhe7047/article/details/63684883
慢查日志

•   慢查日志呢就是MySQL日志查询比较慢的SQL语句的日志详细的记录了所有的超过某一时间的SQL语句
•    22 explain分析低效SQL执行计划
• 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 如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引。

  
  
            
       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的排序方式，order by的字段考虑创建索引。

   where条件和order by使用相同的索引；并且order by的字段都是升序或都是降序。

  
  

  3)group by

   添加order by null禁止排序来提高效率。

  4)or

   所有条件都需要建立索引。