数据库学习笔记——SQL语句分析

拼搏现实的明天。 2022-11-30 04:16 158阅读 0赞

# SQL语句分析 #

## 影响服务器性能的几个方面 ##

*  1、服务器硬件
 *  2、服务器的操作系统
 *  3、数据库存储引擎的选择
 *  4、数据库参数配置
 *  5、数据库结构设计和SQL语句的效率

### SQL性能下降的原因 ###

*  查询语句写的不好
 *  索引失效
 *  关联查询太多join
 *  服务器调优及各个参数设置

### SQL加载顺序 ###

*  手写SQL的顺序

select distinct 
        <select _list>
    from 
        <left_table>
    join  <right_table> on <join_codition>
    where
        <where_condition>
    group by
        <group_by_list>
    having
        <having_condition>
    order by
        <order_by_condition>
    limit <limit number>

*  机读的顺序

from <left_table>
    on <join_condition>
    <join_type> join <right_table>
    where by <group_by_list>
    having <having_condition>
    select
    distinct <select_list>
    order by <order_by_condition>
    limit <limit_number>

### MySQL常见瓶颈 ###

*  CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候
 *  IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
 *  服务器硬件的性能瓶颈

## explain分析SQL语句 ##

*  explain是什么  
    使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQl是如何处理SQL语句。
 *  explain能干什么
    
     *  表的读取顺序
     *  数据读取操作的操作类型
     *  哪些索引可以使用
     *  表之间的引用
     *  每张表有多少行被优化器查询
 *  explain的使用  
    `explain + SQL语句`

### explain字段解释 ###

![在这里插入图片描述][20200827191005712.png_pic_center]

*  **id表的读取顺序**
    
     *  select查询的顺序号,包含一组数字,表示查询中执行select子句或操作表的顺序
     *  两种情况:
        
         *  1、id相同,执行顺序由上至下
         *  2、id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
 *  select\_type

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单的SELECT语句（不包括UNION操作或子查询操作）</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>UNION操作中，查询中处于内层的SELECT，即被union的SELECT</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>子查询中的SELECT</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>表示包含在 From 子句中的 Select 查询</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>union的结果,此时id为NULL</td> 
  </tr> 
 </tbody> 
</table>

*  type  
    ![在这里插入图片描述][20200827182005162.png_pic_center]  
    从性能最好到最差依次是  
    `system > const > eq_ref > ref > range > index > ALL`
    
     *  system,表只有一行记录(等于系统表),这是const类型的特例
     *  const表示通过索引一次就找到了,const用于比较primary key
     *  eq\_ref,唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配
     *  ref,非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
     *  range,只检索给定范围的行,使用一个索引来选择行。
     *  index,Full Index Scan,index与ALL区别为index类型只遍历索引树。
     *  ALL,将遍历全表找到匹配的行
 *  possible\_keys  
    显示可能应用在这张表中的索引，一个或者多个。
 *  key  
    实际使用的索引。如果为null，则没有使用索引  
    查询中若使用了覆盖索引，则该索引仅出现在key列表中
 *  key\_len  
    表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好  
    key\_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key\_len是根据表定义计算而得,不是通过表内检索出的
 *  ref
 *  显示索引的哪一列被使用到了
 *  rows  
    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
 *  Extra

包含不适合在其他列中显示但十分重要的额外信息  
\- Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"  
\- Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。  
\- Using index,使用了索引,避免了全表扫描  
\- Using where,使用了where过滤  
\- Using join buffer,使用了连接缓存  
\- impossible where,不可能的条件,where子句的值总是false

### Show Profile进行SQL分析 ###

*  **Show Profile**
    
     *  是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量
     *  默认情况下，参数处于关闭状态，并保持15次的运行结果。
 *  **Show Profile的分析步骤**
    
     *  1、看当前MySQl版本是否支持
     *  2、默认是关闭的，使用前需要开启
 *  **type**

all         显示所有的开销信息
    block io    显示块IO相关开销
    cpu         显示CPU相关开销信息
    ipc         显示发送和接收相关开销信息
    memory      显示内存相关开销信息
    page faults 显示页面错误相关开销信息

*  **参数注意**

converting HEAP to MyISAM查询结果太大,内存都不够用了往磁盘上搬
    
    Creating tmp table 创建临时表
    
    Copying to tmp table on disk 把内存中临时表复制到磁盘,危险
    
    locked

*  全局查询日志
    
     *  开启命令  
        `select global general_log = 1;`
     *  将SQL语句写到表中  
        `set global log_output = 'TABLE';`
     *  将编写的SQL语句记录在MySQL库的genral\_log表中  
        `select * from mysql.general_log;`

[20200827191005712.png_pic_center]: /images/20221124/253e75c40db54d24864cf94b9615ff42.png
[20200827182005162.png_pic_center]: /images/20221124/877cd96392ed4978b0a3d22af0c806db.png