SQL编写过程:
select .. from .. join .. on .. where .. group by .. having .. order by .. limit ..
SQL解析过程:
from .. on .. join .. where .. group by .. having .. select .. order by .. limit ..
创建索引 :
方式一:create 索引类型 索引名 on 表(字段);
单值索引语法: create index dept_index on tb(dept);
唯一索引语法: create unique index name_index on tb(name);
复合索引语法: create index dept_name_index on tb(dept,name);
方式二:alter table 表名 add 索引类型 索引名 (字段)
单值索引语法: alter table tb add index dept_index(dept)
唯一索引语法: alter table tb add unique index name_index(name);
复合索引语法: alter table tb add index dept_name_index(dept,name);
主键索引语法 : alter table tb add constraint tid_pk primary key(tid);
删除索引:drop index 索引名 on 表名 alter table 表名 drop primary key;
drop index name_index on tb;
查询索引:show index fron 表名;
explain查询执行计划 :explain + sql语句 (explain select * from tb;)
id:编号
select_type:查询类型
table:表
type:类型
possible_keys:预测用到的索引
key:实际用到的索引
key_len:实际用到的索引长度
ref:表之间的引用
rows:用过索引查询到的数据量
Extra:额外的信息
id详解:
id值相同:从上往下执行,数据量少的表优先查询(笛卡尔积)
id值不同:id值越大越优先查询(本质:在嵌套子查询时,先查内层,再查外层)
select_type:
PRIMARY:包含子查询的sql中的主查询(最外层)
SUBQUERY:包含子查询sql中的子查询(非最外层)
simple:简单查询(不包含子查询、union)
derived:衍生查询(使用到了临时表)
a.在from子查询中只有一张表
explain select cr.name from (select * from tb where tid in (1,2)) cr
b.在from子查询中,如果有tb1 union tb2 , 则 tb1就是derived ,tb2就是union
eplain select cr.name from (select * from tb where tid=1 union select * from tb where tid=2) cr
union:
union result:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_sub
type详解:
system > const > eq_ref > ref > range > index > all
system:只有一条数据的系统表或衍生表只有一条数据的主查询
const:仅仅能查到一条数据的sql,用于主键索引和唯一索引
eq_ref:唯一索引:对于每个索引建的查询,返回匹配唯一行数据(有且只有一个)
ref:非唯一索引,对于每个索引建的查询,返回匹配的所有行;(0,多)
range:检索指定范围的行,where后面是一个范围查询(between,in,> ,<, >=);in有时会失效
index:查询全部索引中的数据; ---只扫描索引表,不需要扫描全部数据
all:查询全部表中的数据; ----需要全表扫描
system/const:结果只有一条数据;
eq_ref:结果多条,但是每条数据是惟一的;
ref:结果多条,但是每条数据是0或者多条;
possible_keys:可能用到的索引,不一定准确;
key:实际用到的索引;
注意:如果possible_keys/key是null,则说明没用索引;
key_len:索引的长度,用于判断复合索引是否被完全使用
ref:指明当前表参照的字段
rows:被索引优化查询的数据个数;
Extra:
(1)using filesort:性能消耗大,需要‘额外’的一次排序;一般出现在order by 语句中;
排序:先查询,再排序
单值索引:如果排序和查找是同样一个字段不会出现using filesort,如果不是同一个字段,则会出现using filesort
避免:where哪些字段。order by哪些字段
复合索引:不能跨列(最佳左前缀)
避免:where和order by 按照复合索引的顺序使用,不要跨列或无序使用(where和order by拼起来不跨列)
(2)using temporary:性能消耗大,用到了临时表,一般出现在group by 语句中;
避免:查询哪些字段就根据哪些字段group by;
(3)using index :性能提升:索引覆盖。原因:不读取原文件,只从索引文件中获取数据(不需要回表查询); 只要使用到的列,全部都在索引中,就是索引覆盖;
(4)using where:需要回表查询
假设age是索引列
查询语句:select age,name from ... where age=... 此语句中必须回原表查name,因此会显示using where
感谢博主,喝杯咖啡~
还没有人发表评论