mysql手册08_优化SQL的准备工具

墨蓝 2022-11-27 12:24 150阅读 0赞

**mysql手册08\_优化SQL的准备工具**

--------------------

**查看SQL语句的执行频率：**

查询当前连接的SQL执行频率：
    
    show status like 'Com_______';
    
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Com_binlog    | 0     |
    | Com_commit    | 0     |
    | Com_delete    | 1     |
    | Com_import    | 0     |
    | Com_insert    | 5     |
    | Com_repair    | 0     |
    | Com_revoke    | 0     |
    | Com_select    | 22    |
    | Com_signal    | 0     |
    | Com_update    | 1     |
    | Com_xa_end    | 0     |
    +---------------+-------+
    
    查询全局（自数据库上次启动至今）的SQL执行频率：
    show global status like 'Com_______';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | Com_binlog    | 0     |
    | Com_commit    | 0     |
    | Com_delete    | 6     |
    | Com_import    | 0     |
    | Com_insert    | 44    |
    | Com_repair    | 0     |
    | Com_revoke    | 0     |
    | Com_select    | 1966  |
    | Com_signal    | 0     |
    | Com_update    | 13    |
    | Com_xa_end    | 0     |
    +---------------+-------+
    
    查询InnoDB存储引擎全局的SQL执行频率（统计的是行数）
    show global status like 'Innodb_rows_%';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Innodb_rows_deleted  | 454   |
    | Innodb_rows_inserted | 516   |
    | Innodb_rows_read     | 18758 |
    | Innodb_rows_updated  | 1265  |
    +----------------------+-------+

**定位低效率执行SQL：**

实时查看连接进程中执行效率较低的SQL：
    show processlist;
    
    也可以通过慢查询日志定位执行效率较低的SQL

**通过explain指令分析SQL语句的执行计划：**

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>sleect查询的序列号，表示操作表的顺序</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>select查询的类型</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>输出结果集的表</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表的连接类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>查询时可能使用的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际使用的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>索引字段的长度（越短越好）</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>查询时扫描行的数量</td> 
  </tr> 
  <tr> 
   <td>extra</td> 
   <td>执行情况的说明和描述</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>simple</td> 
   <td>简单查询，不包括子查询或UNION</td> 
  </tr> 
  <tr> 
   <td>primary</td> 
   <td>查询中若包含了子查询，最外层查询标记为PRIMARY</td> 
  </tr> 
  <tr> 
   <td>subquery</td> 
   <td>在 Select 或 Where 中包含的子查询</td> 
  </tr> 
  <tr> 
   <td>derived</td> 
   <td>From 中的子查询</td> 
  </tr> 
  <tr> 
   <td>union</td> 
   <td>联合查询</td> 
  </tr> 
  <tr> 
   <td>union result</td> 
   <td>从union表获取结果的select</td> 
  </tr> 
 </tbody> 
</table>

**SIMPLE 简单查询：**

**PRIMARY & SUBQUERY：**

**UNION & UNION RESULT ：**

<table> 
 <thead> 
  <tr> 
   <th>type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>null</td> 
   <td>MySQL不访问任何表，索引，直接返回结果</td> 
  </tr> 
  <tr> 
   <td>system</td> 
   <td>表中只有一条记录（系统表）</td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>表示通过索引一次就找到了，且只返回一条记录</td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>多表关联查询中的数据结果只有一条</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>非唯一性索引扫描，返回匹配某个值的所有行</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>只检索给定返回的行，where之后出现between, &lt;, &gt;,in等操作</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>与all的区别是：index只遍历了索引树，而all是遍历了数据文件</td> 
  </tr> 
  <tr> 
   <td>all</td> 
   <td>遍历全表数据找到结果</td> 
  </tr> 
 </tbody> 
</table>

**NULL：**

**const：**

**index:**

**all：**

**rows 查询时扫描行的数量：**

**extra：执行情况的说明和描述**

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>using filesort</td> 
   <td>对数据进行了非表内索引顺序的排序，效率低，需要考虑优化！</td> 
  </tr> 
  <tr> 
   <td>using temporary</td> 
   <td>查询过程中用到了临时表保存中间结果，效率低，需要考虑优化！</td> 
  </tr> 
  <tr> 
   <td>using index</td> 
   <td>表示查询用到了索引，效率较高</td> 
  </tr> 
 </tbody> 
</table>

**using filesort & using index ：**

**using temporary：**

**profiling工具分析SQL的执行耗时：**

查询当前系统是否支持profiling工具：
    select @@have_profiling;
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    查询profiling工具是否开启，0表示未开启：
    select @@profiling;
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    
    在会话session级别开启profiling：
    set profiling = 1;
    
    测试一些SQL语句：
    use demo01;
    select * from city;
    select count(*) from city;
    select * from city where city_id = 3;
    
    查看profiles结果,查看SQL语句的执行耗时情况：
    show profiles;
    +----------+------------+--------------------------------------+
    | Query_ID | Duration   | Query                                |
    +----------+------------+--------------------------------------+
    |        1 | 0.00030350 | select @@profiling                   |
    |        2 | 0.00022225 | SELECT DATABASE()                    |
    |        3 | 0.00973300 | select * from city                   |
    |        4 | 0.01025975 | select count(*) from city            |
    |        5 | 0.00053925 | select * from city where city_id = 3 |
    +----------+------------+--------------------------------------+
    
    查看某条SQL语句的执行阶段与各阶段耗时：
    show profile for query 4;
    +--------------------------------+----------+
    | Status                         | Duration |
    +--------------------------------+----------+
    | starting                       | 0.000105 |
    | Executing hook on transaction  | 0.000005 |
    | starting                       | 0.000013 |
    | checking permissions           | 0.000009 |
    | Opening tables                 | 0.000043 |
    | init                           | 0.000008 |
    | System lock                    | 0.000013 |
    | optimizing                     | 0.000007 |
    | statistics                     | 0.000023 |
    | preparing                      | 0.000025 |
    | executing                      | 0.009788 |
    | end                            | 0.000025 |
    | query end                      | 0.000007 |
    | waiting for handler commit     | 0.000016 |
    | closing tables                 | 0.000017 |
    | freeing items                  | 0.000125 |
    | cleaning up                    | 0.000035 |
    +--------------------------------+----------+

注：以上过程没有 Sending data 阶段，Sending data 表示MySQL线程开始**访问数据行（查询）**， 并把 **结果返回** 给客户端的过程。

**trace工具分析优化器的执行计划：**  
在MySQL的体系结构中，**优化器**的作用是**优化**传入的SQL语句。**trace**的作用是**分析优化器的执行计划**。

打开trace，设置格式为json。
    设置trace最大能够使用的内存大小，避免接续过程中因为默认内存太小而不能够完整显示
    set optimizer_trace="enabled=on",end_markers_in_json=on;
    set optimizer_trace_max_mem_size=1000000;
    
    测试一条sql语句：
    select * from city where city_id <=3;
    
    检查infomation_schema.optimizer_trace，通过trace查看mysql是如何执行sql的：
    select * from information_schema.optimizer_trace\G;
    *************************** 1. row ***************************
                                QUERY: select * from city where city_id <=3
                                TRACE: {注：trace内容过长已省略，注：trace内容过长已省略}