MySQL SQL优化教程

电玩女神 2021-09-20 11:48 317阅读 0赞

## 转自:https://www.cnblogs.com/duanxz/archive/2013/02/01/2889413.html ##

## 一，查询SQL执行效率 ##

通过show status命令了解各种SQL的执行效率。

[?][Link 1]

Com\_xxx表示每个xxx语句执行的次数。  
Com\_select：执行select操作的次数，一次查询只累加1。  
Com\_inset：执行insert操作的次数，对于批量插入的insert操作，只累加一次。  
Com\_update：执行update操作的次数。  
Com\_delete：执行delete操作的次数。

通过查询，可以了解当前[数据库][Link 2]的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。

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

## 二，定位执行效率较低的SQL语句 ##

通过慢查询日志定位那么执行效率较低的SQL语句，启用慢查询后，mysqld写一个包含所有执行时间超过long\_query\_time秒的SQL语句的日志文件。

慢查询日志在查询结束以后才记录，所以在应用放映执行效率出现问题的时候查询慢查询的日志并不能定位问题。

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; explain </code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>employee_tbl </code> 
      <code>where</code> 
      <code>id = 3;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+</code> 
     </div> 
     <div> 
      <code>| id | select_type | </code> 
      <code>table</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
      <code>| type&nbsp; | possible_keys | </code> 
      <code>key</code>&nbsp;&nbsp;&nbsp;&nbsp; 
      <code>| key_len | ref&nbsp;&nbsp; | </code> 
      <code>rows</code> 
      <code>| Extra |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+</code> 
     </div> 
     <div> 
      <code>|&nbsp; 1 | SIMPLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | employee_tbl | const | </code> 
      <code>PRIMARY</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
      <code>| </code> 
      <code>PRIMARY</code> 
      <code>| 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | const |&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**select\_type**：表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个select）等。

**table**：输出结果集的表。

**type**：表示MySQL在表中找到所需行的方式，或者叫访问类型。

常见类型，从左至右，性能由最差到最好：

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>ALL</code> 
      <code>&lt; </code> 
      <code>index</code> 
      <code>&lt; range &lt; ref &lt; eq_ref &lt; const,system &lt; </code> 
      <code>NULL</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

1，type=ALL，全表扫描，MySQL遍历全表来找到匹配的行。  
2，type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行。  
3，type=range，索引范围扫描，常见于<,<=,>,>=,between等操作符。  
4，type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。  
5，type=eq\_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique index作为关联条件。  
6，type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，例如，根据主键primary key或者唯一索引unique index记性的查询。  
7，type=NULL，MySQL不用访问表或者索引，直接就能够得到结果。

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

## 三，通过show profile分析SQL ##

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div> 
    <div>
      22 
    </div> 
    <div>
      23 
    </div> 
    <div>
      24 
    </div> 
    <div>
      25 
    </div> 
    <div>
      26 
    </div> 
    <div>
      27 
    </div> 
    <div>
      28 
    </div> 
    <div>
      29 
    </div> 
    <div>
      30 
    </div> 
    <div>
      31 
    </div> 
    <div>
      32 
    </div> 
    <div>
      33 
    </div> 
    <div>
      34 
    </div> 
    <div>
      35 
    </div> 
    <div>
      36 
    </div> 
    <div>
      37 
    </div> 
    <div>
      38 
    </div> 
    <div>
      39 
    </div> 
    <div>
      40 
    </div> 
    <div>
      41 
    </div> 
    <div>
      42 
    </div> 
    <div>
      43 
    </div> 
    <div>
      44 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>//查看当前MySQL是否支持profile</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>@@have_profiling;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>------------------+</code> 
     </div> 
     <div> 
      <code>| @@have_profiling |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>------------------+</code> 
     </div> 
     <div> 
      <code>| YES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>------------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>//默认profiling是关闭的，可以通过</code> 
      <code>set</code> 
      <code>语句在session级别开启profiling</code> 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>select</code> 
      <code>@@profiling;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>-------------+</code> 
     </div> 
     <div> 
      <code>| @@profiling |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>-------------+</code> 
     </div> 
     <div> 
      <code>|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0 |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>-------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>mysql&gt; </code> 
      <code>set</code> 
      <code>profiling=1;</code> 
     </div> 
     <div> 
      <code>Query OK, 0 </code> 
      <code>rows</code> 
      <code>affected</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>mysql&gt; show profiles;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----------+-----------+-----------------------------------------+</code> 
     </div> 
     <div> 
      <code>| Query_ID | Duration&nbsp; | Query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----------+-----------+-----------------------------------------+</code> 
     </div> 
     <div> 
      <code>|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 0.0001725 | </code> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>employee_tbl </code> 
      <code>where</code> 
      <code>id = 3 |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>----------+-----------+-----------------------------------------+</code> 
     </div> 
     <div> 
      <code>1 row </code> 
      <code>in</code> 
      <code>set</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>mysql&gt; show profile</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>for</code> 
      <code>query 1;</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>--------------------------------+----------+</code> 
     </div> 
     <div> 
      <code>| Status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Duration |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>--------------------------------+----------+</code> 
     </div> 
     <div> 
      <code>| starting&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2.6E-5&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| Waiting </code> 
      <code>for</code> 
      <code>query cache lock&nbsp;&nbsp; | 3E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| checking query cache </code> 
      <code>for</code> 
      <code>query | 6E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| checking </code> 
      <code>privileges</code> 
      <code>on</code> 
      <code>cached&nbsp; | 3E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| checking permissions&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 7E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| sending cached result </code> 
      <code>to</code> 
      <code>clien | 0.000123 |</code> 
     </div> 
     <div> 
      <code>| logging slow query&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 4E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| cleaning up&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2E-6&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>+</code> 
      <code>--------------------------------+----------+</code> 
     </div> 
     <div> 
      <code>8 </code> 
      <code>rows</code> 
      <code>in</code> 
      <code>set</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Sending dat状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

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

## 四，通过trace分析优化器如何选择执行计划 ##

MySQL 5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。

转载于:https://www.cnblogs.com/sharpest/p/9305736.html

[Link 1]: https://www.2cto.com/database/201709/677378.html#
[Link 2]: https://www.2cto.com/database/