1.explain性能指标分析:
-
id:
执行sql查询的顺序,越大越先查询,相同时越靠上越先查询; -
select_type:
simple 简单查询(单表查询);primary 主查询(多表查询时的主查询);subquery 子查询;
derived 衍生表查询(将一条查询结果新命名的表);
union 和uniton result联合查询 -
type:
本次查询索引使用类型 从好到差依次:system(常量)>const(表中的索引常量如:=)
>eq_ref(关联查询时在另外一张表中只有一条数据与之关联)
>ref(关联查询时在另一张表中在索引上有数据关联(尽量关联到索引))
>range(索引上范围查询)
>index(查询东西在索引上(查询全部))>all(没有用到索引,全表查询)
多表查询时出现all,则说明没有关联到任何索引值,查询效率极低
system举例: explain select id from (select id from tb1_emp where id = 1)d -
ref: 关联到哪一张表
-
key_len: 索引值的长度
-
extra: 其他信息
不建议出现的信息:
1.using filesort(使用文件内排序):
原因: 条件查询后排序没能够充分利用复合索引,导致查询后的结果进行了二次排序;
收获: 建立复合索引时索引顺序与常用sql查询多字段排序顺序相同.
2.using temporary(分组查询时使用了临时空间)
plsql:
首创与Oracle,有返回值的叫数据库函数,没有返回值的叫存储过程(目的是执行一些复杂的数据库操作,如:自动生成表数据,多张表操作).
- 开启数据库函数操作状态(mysql):
-- 查询数据库函数操作状态
show variables like 'log_bin_trust_function_creators';
-- 开启数据库函数操作权限
set global log_bintrust_function_creators=1;
-
事务的隔离级别:
脏读: 读到别人未提交的数据(基于修改)
幻读: 督导别人新增或修改前的数据(基于增删)
不可重复读: 查的时候是1,修改的时候成0了 -
数据库加锁:
#加读锁(读锁特点: 阻塞写,不阻塞读)
lock table 表名 read;
#加写锁(写锁特点: 读和写都阻塞)
lock table 表名 write;
#释放锁
unlock tables;
行锁存在,才有事务(行锁可以让读读互斥)
select 查询语句后面加 for update查询加行锁,避免查询和更新同时发生,导致读到的库存数量发生不可重复读的错误
update 自带行锁(在InnoDB引擎中)
无索引状态查询会将行锁上升为表锁
间隙锁:
当使用范围条件检索数据时,加行锁或者update(同理加了行锁),如果范围内有不存在的数据,此时别人正向
该不存在的数据添加记录时,会受到阻塞,这叫做间隙锁(解决方法: 尽量用等值代替范围条件检索)
- SQL优化技巧:
- 复合索引,全职匹配效率更高;
- 复合索引有顺序,第一个索引尽量不要缺少,中间索引尽量不要断,这样查询效率高;
- 不要在索引列上做任何操作,如:计算,函数,类型转换,会导致索引失效,进行全表扫描;
- 复合索引中使用范围条件检索时,范围右侧(不包含范围索引)的索引全失效;
- 使用不等号(!=或<>),null,not null,or,in会导致索引失效,导致全表扫描;
- 使用like模糊查询时,%开头会导致索引失效;
- 字符串不加单引号也会导致索引失效;