SQL优化

为什么要做SQL优化

因为1.系统的吞吐量和瓶颈往往体现在数据库的访问速度上

2.随着系统的运营时间日积月累，数据往往与日俱增，处理的速度会越来越慢，所以需要一个优秀的数据库设计保证在大数据上系统还能保持快速的处理速度

3.数据是存储再硬盘上没有再内存里面的数据读写速度

SQL优化分为3个部分

一.根据业务的设计层面，包括但不限于表设计，字段设计

原则：

1.尽量使用字符串类型varchar(非定长)而不是char（定长，不足位补0）

2.单表字段不宜过多

3.遇到复杂业务可以使用外键

4.多使用枚举类型字段 （0-是 1- 否）

5.使用默认值是避免null数据（sql在处理null字段数据上比not null的数据复杂很多）

二.硬件层面，包括数据库选型，存储引擎（innodb）,索引建立，分表分库，集群部署，读写分离等等

1.索引的数据类型是B+树

2.存储引擎主要分为（innodb,MyiSAM,MEMORY）但是主流都是使用innodb

三.通常的sql书写规范等等

基础的sql优化

1.尽量不要使用select * ,要什么字段查询什么字段节约资源

2.尽量避免再where条件中使用or的查询条件

使用or的查询条件可能导致mysql的优化器试别出来然后不走索引走全表是扫描

正确的是使用union all 链接两条sql

错误示范

SELECT * FROM student WHERE id=1 OR salary=30000

正确示范

SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000

3.where查询条件避免字符串类型转换   id = '1111'

4.where 查询条件避免使用运算符 ！= ，<>

5.where查询条件避免函数表达式，数学公式等

6.gruop by数据先过滤后分组，减少数据提高分组效率

6.复合索引最左原则，书写sql的时候注意 

MYSQL的事务隔离级别

读非提交

读已提交

可重复读

可串行读

MySQL默认是可重复读事务隔离级别

数据库锁

表锁：每次操作锁住整张表开销小速度块，不会出现死锁，锁定力度大出现锁冲出概率小

行锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

MVCC

多版本并发控制，是在多个事务发生的时候保证每个事务不相互影响的机制，

两个核心模块，undo日志版本链：在一行数据被多个事务依次修改过后，每次的修改记录都会保存到undo日志版本链中，用于回滚操作

一致性视图read-view：每一个事务开启后，执行任何查询sql时就会生成当前事务的一致性视图。这个视图是由查询的时候所有未提交的事务id数组和已创建的最大事务id组成。