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

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字段解释

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

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

• type
  
  从性能最好到最差依次是
  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;