MySQL学习(二)

阳光穿透心脏的1/2处 2022-01-10 15:53 574阅读 0赞

## 一. Mysql优化SQL步骤 ##

### 1.查看SQL执行频率 ###

show status like 'Com_______'; # 这些参数对于所有存储引擎的表操作都会进行累计。 
    show status like 'Innodb_rows_%'; # 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

### 2.定位低效率执行SQL ###

1.慢查询日志 :: 通过慢查询日志定位那些执行效率较低的 SQL 语句;(详细讲解)  
2.show processlist :令查看当前MySQL在进行的线程，包括线程的状态、是否 锁表等;

### 3. explain分析执行计划 ###

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th align="left">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id select</td> 
   <td align="left">查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td align="left">表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、 PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语 句）、SUBQUERY（子查询中的第一个 SELECT）等</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td align="left">输出结果集的表</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td align="left">表示表的连接类型，性能由好到差的连接类型为( system —&gt; const -----&gt; eq_ref ------&gt; ref -------&gt; ref_or_null----&gt; index_merge —&gt; index_subquery -----&gt; range -----&gt; index ------&gt; all )</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td align="left">表示查询时，可能使用的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td align="left">表示实际使用的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td align="left">索引字段的长度</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td align="left">扫描行的数量</td> 
  </tr> 
  <tr> 
   <td>extra</td> 
   <td align="left">执行情况的说明和描述</td> 
  </tr> 
 </tbody> 
</table>

#### 3.1 explain 之id ####

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。  
id 情况有三种 :

1.  id 相同表示加载表的顺序是从上到下。
2.  id 不同id值越大，优先级越高，越先被执行。
3.  id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越 大，优先级越高，越先执行。

#### 3.2 explain 之 select\_type ####

表示 SELECT 的类型，常见的取值:

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单的select查询，查询中不包含子查询或者UNION</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>查询中若包含任何复杂的子查询，外层查询标记为该标识</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>在SELECT 或 WHERE 列表中包含了子查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查 询，把结果放在临时表中</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子 查询中，外层SELECT将被标记为 ： DERIVED</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION表获取结果的SELECT</td> 
  </tr> 
 </tbody> 
</table>

#### 3.4 explain 之 table ####

展示这一行的数据是关于哪一张表的

#### explain 之 type ####

type 显示的是访问类型，是较为重要的一个指标，可取值为：

<table> 
 <thead> 
  <tr> 
   <th align="left">type</th> 
   <th align="left">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">NULL</td> 
   <td align="left">MySQL不访问任何表，索引，直接返回结果</td> 
  </tr> 
  <tr> 
   <td align="left">system</td> 
   <td align="left">表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现</td> 
  </tr> 
  <tr> 
   <td align="left">const</td> 
   <td align="left">表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数 据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较</td> 
  </tr> 
  <tr> 
   <td align="left">eq_ref</td> 
   <td align="left">类似ref，区别在于使用的是唯一索引，使用主键的<font>多表关联查询</font>，关联查询出的记录只有一条。<font>常见 于主键或唯一索引扫描</font></td> 
  </tr> 
  <tr> 
   <td align="left">ref</td> 
   <td align="left"><font>非唯一性索引扫描，返回匹配某个单独值的所有行</font>。本质上也是一种索引访问，返回所有匹配某 个单独值的所有行（多个）</td> 
  </tr> 
  <tr> 
   <td align="left">range</td> 
   <td align="left">只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， &lt; , &gt; , in 等操作。</td> 
  </tr> 
  <tr> 
   <td align="left">index</td> 
   <td align="left">index 与 ALL的区别为 index 类型只是<font>遍历了索引树</font>， 通常比ALL 快， ALL 是遍历数据文件。</td> 
  </tr> 
  <tr> 
   <td align="left">all</td> 
   <td align="left">将遍历全表以找到匹配的行</td> 
  </tr> 
 </tbody> 
</table>

性能：

> NULL > system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL

> system > const > eq\_ref > ref > range > index > ALL

一般来说， 我们需要保证查询至少达到 range 级别， 好达到ref 。

#### 3.5 explain 之 key ####

possible\_keys : 显示可能应用在这张表的索引， 一个或多个。

key ： 实际使用的索引， 如果为NULL， 则没有使用索引。

key\_len : 表示索引中使用的字节数， 该值为索引字段大可能长度，并非实际使用长度，在不损失精确性的前 提下， 长度越短越好

#### 3.6 explain 之 rows ####

扫描行的数量.

#### 3.7explain 之 extra ####

其他的额外的执行计划信息，在该列展示 。

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th align="left">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>using ﬁlesort</td> 
   <td align="left">说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。</td> 
  </tr> 
  <tr> 
   <td>using temporary</td> 
   <td align="left">使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低</td> 
  </tr> 
  <tr> 
   <td>using index</td> 
   <td align="left">表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。</td> 
  </tr> 
 </tbody> 
</table>

### 4. show proﬁle分析SQL ###

Mysql从5.0.37版本开始增加了对 show proﬁles 和 show proﬁle 语句的支持。show proﬁles 能够在做SQL优化时 帮助我们了解时间都耗费到哪里去了。  
首先，我们可以执行一系列的操作，如下图所示：

show databases; 
    use db01;
    show tables; 
    select * from tb_item where id < 5; 
    select count(*) from tb_item;

执行完上述命令之后，再执行show proﬁles 指令， 来查看SQL语句执行的耗时：

> show proﬁles ;

通过show proﬁle for query query\_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间.

### 5.trace分析优化器执行计 ###

…

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

## 二. 索引使用 ##

### 1. 全值匹配 ###

全值匹配 ，对索引中所有列都指定具体值。  
该情况下，索引生效，执行效率高。

### 2. 最左前缀匹配 ###

如果索引了多列，要遵守左前缀法则。指的是查询从索引的左前列开始，并且不跳过索引中的列。

比如，对表table的A，B，C建立联合索引，

CREATE INDEX index_A_B_C ON table(A，B，C)

走索引的情况大致有：

# 走索引(A，B，C)中的A
    select * from table where a=？；
    # 走索引(A，B，C)中的A,B
    select * from table where a=？and b= ？ ；
    # 走索引(A，B，C)中的A,B,C
    select * from table where a=？and b= ？ and c =？；
    # 走索引(A，B，C)中的A,B,C 和顺序无关，只看是否包含最左边的列
    select * from table where b= ？ and c =？and a=？； 
    # 走索引(A，B，C)中的A
    select * from table where  a=？ and  c =？；

### 3. 索引失效的情况： ###

#### 3.1 范围查询 ####

范围查询右边的列，不能使用索引

# 走索引(A，B，C)中的A,B
    select * from table where a=？ and b>? and c >?；

#### 3.2 字段运算 ####

不要在索引列上进行运算操作， 索引将失效。

#### 3.3 字符串不加单引号 ####

字符串不加单引号，造成索引失效。  
没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效.

#### 3.4 select \*造成回表查询 ####

尽量使用覆盖索引，避免select \*  
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select \* 。

# explain会提示using index condition 
    select * from  table where a = ?

如果查询列，超出索引列，也会降低性能(回表查询)。

# explain会提示 using index ; using where 
    select a,b,c from  table where a = ?

> TIP :  
> using index ：使用覆盖索引的时候就会出现  
> using where：在查找使用索引的情况下，需要回表去查询所需的数据  
> using index condition：查找使用了索引，但是需要回表查询数据  
> using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据

#### 3.5 or连接列导致不走索引 ####

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='张三' or createtime = '2088-01-01 12:00:00'\G;

#### 3.6 以%开头的Like模糊查询 ####

#走索引
    select * from table where a like 'zhangsan%';
    #不走索引
    select * from table where a like '%zhangsan';
    #不走索引
    select * from table where a like '%zhangsan%';

如何解决这种问题：通过覆盖索引来解决

#不走索引 
    select id，A，B，C from table where a like '%zhangsan%';

#### 3.7 如果MySQL评估使用索引比全表更慢，则不使用索引 ####

mysql评估全表扫描快的话，会不走索引。

#### 3.8 is NULL ， is NOT NULL 有时索引失效 ####

可以用3.7来理解，如果mysql底层评估全表扫描快的话，会不走索引。  
如果数据库中该字段null值占绝大部分，用is not null 来作为查询条件，是走索引的；  
如果数据库中该字段null值占极小部分，用is null 来作为查询条件，是走索引的；

#### 3.9 in走索引，not in索引失效 ####

#### 3.10 单列索引和复合索引。 ####

尽量使用复合索引，而少使用单列索引 。

> create index idx\_name\_sta\_address on tb\_seller(name, status, address);
> 
> 就相当于创建了三个索引 ：  
> name  
> name + status  
> name + status + address

创建单列索引:

create index idx_seller_name on tb_seller(name); 
    create index idx_seller_status on tb_seller(status); 
    create index idx_seller_address on tb_seller(address);

数据库会选择一个优的索引（辨识度高索引）来使用，并不会使用全部索引 。  
如何评价一个字段辨识度高不高：  
比如性别字段辨识度就是低的，而身份证号辨识度是高的。

#### 3.11 查看索引使用情况 ####

show status like 'Handler_read%';   
    show global status like 'Handler_read%';

> Handler\_read\_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低 越好）。  
> Handler\_read\_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的 性能改善不高，因为索引不经常使用（这个值越高越好）。  
> Handler\_read\_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列， 该值增加。  
> Handler\_read\_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。  
> Handler\_read\_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。 你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应 该建立索引来补救。  
> Handler\_read\_rnd\_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说 明你的表索引不正确或写入的查询没有利用索引。