sql优化

sql优化

概念

锁

数据库解决并发场景的锁： 共享锁（读锁），排他锁（写锁）

乐观锁：适用于数据竞争不激烈的场景，读多写少，通过版本号和时间戳实现
悲观锁：每次操作都会锁定数据。适用于并发量大的场景

表锁：锁定整张表，开销小，但是有严重的锁竞争
行锁：开销大，但是可以支持高并发

MVCC

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期或者被删除。

在可重复读 （repeatable-read）事务隔离级别下：

• select：读取创建版本号<=当前事务版本号。
• INSERT时，保存当前事务版本号为行的创建版本号。
• DELETE时，保存当前事务版本号为行的删除版本号。
• UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行。

每行记录都需要额外的存储空间来记录version，增加了行检查与维护工作，但是可以减少锁的适用，读取数据操作简单，性能好。通过MVCC读取的数据其实是历史数据，而不是最新数据。这种读取数据的方式叫做快照读（snapshot reda）,只使用select时就是快照读。

事务

(1) 原子性(Atomicity)
事务的原子性指的是，事务是数据库执行操作的最小单元，它所做的对数据修改操作要么全部执行，要么完全不执行。这种特性称为原子性。

(2)一致性(Consistency)   
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。

(3)隔离性(Isolation)
隔离性指并发的事务是相互隔离的。

(4)持久性(Durability)
持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，要保证它对数据库中数据的改变应该是永久性的，即对已提交事务的更新能恢复。持久性通过数据库备份和恢复来保证。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	√	√	√
读已提交（read-committed）	×	√	√
可重复读 （repeatable-read）	×	×	√
串行化 （serializable）	×	×	×

建表

• 选择对应的数据类型
• 如果可以，选择更小的数据类型
• 在创建表时要带上索引
• 索引不要选择过长的字符串
• 特别长的字符串，可以使用前缀索引

查询

• 避免 select *
• 避免查询无关的行
• 分解关联查询。将多表关联查询的一次查询，分解成对单表的多次查询。可以减少锁竞争，查询本身的查询效率也比较高。因为MySql的连接和断开都是轻量级的操作，不会由于查询拆分为多次，造成效率问题。