Sql的编写过程和解析过程
编写过程：
select dinstinct ...from ...join ...on ...where ...group by ...having ...order by ...limit ...解析过程：
from ... on... join ...where ...group by ...having ...select dinstinct ...order by ... limit ...Mysql中explain
explain ：可以模拟SQL优化器执行SQL语句，从而让 开发人员 知道自己编写的SQL状况
比如下边的sql：
EXPLAIN
select *
from t_flow_sterilize_batch
a
join ssd_dev.t_flow_sterilize_batch_instance
b
where a.id = b.sterilize_batch_id结果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | b | (null) | ALL | (null) | (null) | (null) | (null) | 8 | 100.0 | Using where |
| 1 | SIMPLE | a | (null) | eq_ref | PRIMARY | PRIMARY | 130 | ssd_dev.b.sterilize_batch_id | 1 | 100.0 | (null) |
id : 优先级，id值越大越优先；id值相同，从上往下 顺序执行
select_type ：查询类型
table ：表
type ：索引类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用，指明当前表所 参照的 字段
rows ：通过索引查询到的数据量 filtered：返回结果的行占需要读取的行（rows列的值）的百分比，就是百分比越高，说明需要查询到数据越精确，百分比越小，说明查询到的数据量大，而结果集很少
Extra ： 额外的信息
下边对比较重要的字段进行说明：
select_type字段
- simple:简单查询（不包含子查询、union）
- PRIMARY:包含子查询SQL中的 主查询 （最外层）
- SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
- derived:衍生查询（使用到了临时表，union）
- union:使用了union
- union result :告知开发人员，那些表之间存在union查询
在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union，例如下边：
explain select cr.cname
from ( select * from course where tid = 1
union
select * from course where tid = 2 ) cr ;type字段
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 ，要对type进行优化的前提：有索引
其中：system,const只是理想情况；实际能达到 ref>range
system（忽略）: 只有一条数据的系统表 或 衍生表只有一条数据的主查询
const：仅仅能查到一条数据的SQL，通过索引一次命中 ,用于Primary key 或unique索引
eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0），常见于唯一索引 和 主键索引，有可能需要回表
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多），有可能需要回表
range：检索指定范围的行 ,where后面是一个范围查询（between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引 all）
index：查询全部索引中数据，只遍历索引树
all：全表扫描
system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的 ；
ref：结果多条；但是每条数据是是0或多条 ；
key_len字段
作用：用于判断复合索引是否被完全使用。
- 如果索引字段可以为Null，则会多加1个字节用于标识。
- 如果索引字段是可变长度的，会多加2个字节标识可变长度。
不同的编码一个字符的字节数不同：
utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节
Extra字段
using filesort ：没有走索引排序，性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
explain select * from test02 where a1 ='' order by a2 ; --using filesort对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
explain select *from test02 where a1='' order by a3 ; --using filesort
explain select *from test02 where a2='' order by a3 ; --using filesort
explain select *from test02 where a1='' order by a2 ; --没有using filesort
explain select *from test02 where a2='' order by a1 ; --using filesort上边是复合索引的情况。
小结： where和order by 按照复合索引的顺序使用，不要跨列或无序使用。
using temporary:性能损耗大 ，用到了临时表。一般出现在group by 语句中。
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ; --没有using temporary
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary避免：查询那些列，就根据那些列 group by
using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）
例如：test02表中有一个复合索引（a1,a2）
explain select a1,a2 from test02 where a1='' or a2= '' ; --using index
explain select a1,a3 from test02 where a1='' or a3= '' ; --没有索引覆盖using where （需要回表查询）
假设age是索引列
但查询语句select age,name from ... where age =...,此语句中必须回原表查Name，因此会显示using where
explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询impossible where ： where子句永远为false
explain select * from test02 where a1='x' and a1='y' ; --a1不可能又是x又是yexplain的两种格式
Mysql中explain有两种格式，分别是
- format=tree
- format=json，返回json格式的explain
举个栗子：
format = tree
EXPLAIN format=tree
select *
from t_flow_sterilize_batch a
join ssd_dev.t_flow_sterilize_batch_instance b
where a.id = b.sterilize_batch_id结果为：
-> Nested loop inner join (cost=3.85 rows=8)
-> Filter: (b.sterilize_batch_id is not null) (cost=1.05 rows=8)
-> Table scan on b (cost=1.05 rows=8)
-> Single-row index lookup on a using PRIMARY (id=b.sterilize_batch_id) (cost=0.26 rows=1)
format = json
EXPLAIN format=json
select *
from t_flow_sterilize_batch a
join ssd_dev.t_flow_sterilize_batch_instance b
where a.id = b.sterilize_batch_id结果为：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.85"
},
"nested_loop": [
{
"table": {
"table_name": "b",
"access_type": "ALL",
"rows_examined_per_scan": 8,
"rows_produced_per_join": 8,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.80",
"prefix_cost": "1.05",
"data_read_per_join": "8K"
},
"used_columns": [
"id",
"sterilize_task_id",
"packet_instance_id",
"sterilize_batch_id",
"create_user",
"create_time",
"last_user",
"last_time",
"delete_flag"
],
"attached_condition": "(`ssd_dev`.`b`.`sterilize_batch_id` is not null)"
}
},
{
"table": {
"table_name": "a",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "130",
"ref": [
"ssd_dev.b.sterilize_batch_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 8,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.00",
"eval_cost": "0.80",
"prefix_cost": "3.85",
"data_read_per_join": "13K"
},
"used_columns": [
"id",
"box_id",
"batch_no_day",
"load_count",
"status",
"load_user",
"load_start_time",
"load_end_time",
"sterilize_user",
"sterilize_start_time",
"sterilize_end_time",
"sterilize_program_id",
"batch_no_month",
"expected_time",
"create_user",
"create_time",
"last_user",
"last_time",
"delete_flag",
"valid_num",
"valid_unit"
]
}
}
]
}
}
