MySQL随笔

1、隔离级别

　　innoDB通过间隙锁锁定查询范围避免被其他事物修改

　　未提交读（导致脏读）、已提交读（导致不可重复读）、可重复读（mysql默认，导致幻读）、串行化 

　　脏读：事物执行的过程读到其他事物未提交的数据　　

　　不可重复读：事物a在多次读取某数据时，事物b进行了修改，导致食物a两次读取结果不一样

　　幻读：前后两次查询得到的结果不一样，事物前后两次查询统一范围数据时事物b进行了插入修改并提交

 

2、SQL优化

　　a.增加索引；但是要避免索引失效如like、函数等

　　b.避免返回不必要的数据列

　　c.适当优化sql结构，如避免子查询、全表扫描等

　　d.分库分表，单表数据量比较大时

　　e.读写分离，读多写少时

 

3、索引失效情况

索引：主键索引、普通索引

使用ecplain 查询sql当前执行计划

　　a 索引列上做函数运算导致失效

　　b 组合索引需要按照最左匹配法则

　　c 索引列存在隐式转换时候，即索引列是字符串类型但是没有加引号

　　d 索引列使用不等号或者not查询导致失效

　　e 使用like并且%加在前面时候失效，由于不符合最左匹配所以失效

　　f 使用or但是语句前后没有同时使用索引的时候也会失效

 

4、update执行时如果where包含索引列且只执行一个语句，使用行锁，否则使用表锁

 

5、为什么索引不适用二叉树而使用B+树

二叉树每次插入大的值需要添加到右侧，深度较高，查一次就要进行一次IO，查找效率没有帮助。

红黑树也是最大的插到右侧，但是会进行自旋，不会全都加到一条线，相当于平衡二叉树，查找次数差不多能减少一半，但是深度还是比较高，而且自旋转也需要消耗资源。

B树一个节点可以存储多个元素，深度较低。

 B+树是对B树的一个增强，如图

 innoDB引擎：自增的在添加的时候不需要修改节点，节省时间

 

一般推荐建立一到两个的联合索引（复合索引）

Explain 命令中的 type 列，显示MySQL查询所使用的 关联类型（Join Types） 或者 访问类型，它表明 MySQL决定如何查找表中符合条件的行。
常见访问类型性能由最差到最优依次为：

ALL < index < range < ref < eq_ref < const < system。

 一般需要range级别或者达到ref级别

　　range级别：

1. 范围条件查询：在 WHERE 子句里带有 BETWEEN、>、<、>=、<= 的查询。
2. 多个等值条件查询：使用 IN() 和 OR ，以及使用 like 进行前缀匹配模糊查询

 

非主键索引的都可以称之为二级索引，二级索引是非聚集索引，没有在叶子结点包含全部的数据，只存主键的id，需要根据主键id进行回表再查询；

mysql5.6以后的索引下推可以减少回表

覆盖索引查询的字段都在索引列中，不需要进行回表所以效率高一些。

mysql表关联的底层实现原理：当数据量比较大的时候就算走索引也比较慢

 

explain关键参数type:

 

 

 

 

　　

 

索引优化：

• 全值匹配：使用复合索引时候，查询条件包括索引所有的列；
• 索引列上少算数；使用函数没有办法根据有序性进行检索；
• 范围索引后面会失效；前面使用有序范围后，后面的字段是无序的；如果第一个字段搜索范围小想强制使用索引可以加上关键字Force Index（索引名称）防止后面字段全表扫描
•  尽量使用覆盖索引，不写星号（防止回表）；explice查看extra显示index
• 不等空值还有or，索引失效要少用，还有not in 、not exists也会失效，范围查询mysql自己判断
• like百分号写右边；
• 单引号不要省略

sql优化

• 不要写*
• 用小表驱动大表
• 连接查询代替子查询；具体场景具体分析
• 提升group by效率，分组字段添加索引
• 批量插入优化，mybatis提供了sqlsession.getMapper(EmpMapper.class).insertBatch(数据list)，五百以内
• 使用limit
• union all替代union 第一个不会去重，去重时需要遍历排序等内部操作
• 尽量少关联