Python之SQL语句分析

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

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

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

1.2 SQL性能下降的原因

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

1.3 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 表
on join
where 条件
group by 分组
having 子条件
select 查询的字段
distinct  去掉重复
order by 排序
limit 打印的行数

1.4 MySQL常见瓶颈

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

2.0 explain分析SQL语句

2.1 explain是什么？

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。

2.2 explain能干嘛？

• 表的读取顺序
• 数据读取操作的操作类型
• 那些索引可以使用
• 那些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

2.3 explain怎么玩？

explain + SQL语句  如下:
explain select & from 表 ;

2.4 explain字段解释

1. id表的读取顺序
   两种情况:
   (1) id相同,执行顺序由上至下.
   (2) id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行.
2. select_type 数据读取操作的操作类型
   
3. table
   显示这一行数据时，关于当前表的
4. partition
   关于访问的分区
5. 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,将遍历全表找到匹配的行

6. possible_keys
   显示可能应用在这张表中的索引,一个或多个。
7. key
   实际使用的索引。如果为null,则没有使用索引
   查询中若使用了覆盖索引,则该索引仅出现在key列表中
8. key_len
   表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
   key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
9. 索引长度计算

varchr(24)变长字段且允许NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL  
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL    
10*(Character Set：utf8=3,gbk=2,latin1=1)

10. ref
    显示索引那一列被使用到了
11. rows
    根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
    
12. Extra
    包含不适合在其他列中显示但十分重要的额外信息
    • Using filesort,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"
    • Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
    • Using index,使用了索引,避免了全表扫描
    • Using where,使用了where过滤
    • Using join buffer,使用了连接缓存
    • impossible where,不可能的条件,where子句的值总是false

3.0 show profile进行SQL语句分析

3.1 Show Profile进行SQL分析

是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量
默认情况下,参数处于关闭状态,并保持最近15次的运行结果。

3.2 Show Profile分析步骤

1. 是否支持,看看当前MySQL版本是否支持
2. 开启功能,默认是关闭,使用前需要开启

show variables like 'profiling';
默认是关闭的，需要手动开启
set proling=on; 开启
测试：先随意输入几条查询语句
show tables;
select * from qi;
select * from mylock;
select * from mylock order by id;
select * from mylock order by id,name desc;
select * from mylock order by id,id desc;
select * from mylock order by id desc;
show profiling;
show profiles;-- 使用查询命令
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.00079400 | show tables                                |
|        2 | 0.00360300 | select * from goood                        |
|        3 | 0.49156100 | select * from test                         |
|        4 | 0.00062425 | show tables                                |</