mysql高级

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优化技巧:
  1. 复合索引,全职匹配效率更高;
  2. 复合索引有顺序,第一个索引尽量不要缺少,中间索引尽量不要断,这样查询效率高;
  3. 不要在索引列上做任何操作,如:计算,函数,类型转换,会导致索引失效,进行全表扫描;
  4. 复合索引中使用范围条件检索时,范围右侧(不包含范围索引)的索引全失效;
  5. 使用不等号(!=或<>),null,not null,or,in会导致索引失效,导致全表扫描;
  6. 使用like模糊查询时,%开头会导致索引失效;
  7. 字符串不加单引号也会导致索引失效;