博客

sql优化

• 把数据量大的表作为驱动表 (straight join)
• 用exists代替in的子查询
• 在查询多的表上建立索引(一般结果在20%左右)

​ 1.在经常使用where条件的字段上建立索引

​ 2.在表关联的字段上建立索引

​ 3.在经常使用order by 或 group by的字段建立索引

​ 4.根据情况优先使用唯一索引, 单个索引区分度不大时使用联合索引

• 索引失效的情况

  1.右模糊匹配时

  2.联合索引使用了只使用了右边的索引

  3.索引字段是int使用str查询时

  4.索引参与运算或使用函数

  5.数据量特别少只有几条(全表查询更快时)

事务和隔离级别

事务的四个特性：原子性、 隔离性、 持久性、 一致性

事务出现的问题

• 脏写：事务A改了行数据，事务B也改了该行数据，事务A回滚，事务B提交，事务B的数据也被回滚。
• 脏读：事务A改了行数据，事务B读了事务A修改后的数据，事务A回滚，事务B又读到不同数据。
• 不可重复读：事务A读行数据，事务B修改了该行数据提交事务，事务A再次读到事务B修改后的数。
• 幻读：事务A读行数据，事务B增加了行数据提交事务，事务A再次读到事务B修改后的多条数据。

SQL标准的四种隔离级别

• read uncommitted：只支持快照读(snapshot read)。
• read committed：解决了脏读，使用mvcc实现。
• reapeatable read：解决了重复读，使用mvcc实现(防止的是快照读的幻读)，mysql默认。
• serializable：事务串行执行，解决了幻读，使用当前读(s锁实现，qps只有几十效率慢)。

ps：MySQL的默认RR级别可以避免幻读发生，因为mvcc机制。

mvcc(多版本并发协议): undo log多版本链条 + ReadView机制

1)事务版本号: 每次事务开启会一个自增长的事务ID, 可以从事务ID判断事务的执行先后顺序

2)表的隐藏列:

• DB_TRX_ID: 记录操作该数据事务的事务ID
• DB_ROLL_PTR：指向上一个版本数据在undo log 里的位置指针
• DB_ROW_ID: 隐藏ID, 当创建表没有合适的索引作为聚簇索引时, 会用该隐藏ID创建聚簇索引
  
  3)undo log: 记录数据变化的过程, 事务回滚时使用, MVCC快照读时使用
  
  4)read view: 执行一个事务的时都会得到一个read_view
• trx_ids: 当前系统活跃(未提交)事务版本号集合。
• low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。
• up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”
• trx_id: 创建当前read view的事务版本号；

多版本并发协议(MVCC)

实现原理：undo log多版本链条 + ReadView机制

• 事务版本号: 每次事务开启会一个自增长的事务ID, 可以从事务ID判断事务的执行先后顺序

• 表的隐藏列:

  • DB_TRX_ID：记录操作该数据事务的事务ID。

  • DB_ROLL_PTR：指向上一个版本数据在undo log 里的位置指针。

  • DB_ROW_ID：隐藏ID，当创建表没有合适的索引作为聚簇索引时，会用该隐藏ID创建聚簇索引。

• undo log：记录数据变化的过程, 事务回滚时使用, MVCC快照读时使用

• read view：执行一个事务的时都会得到一个read_view

  • trx_ids：当前系统活跃(未提交)事务版本号集合。
  • low_limit_id：创建当前read view 时“当前系统最大事务版本号+1”。
  • up_limit_id：创建当前read view 时“系统正处于活跃事务最小版本号”。
  • trx_id：创建当前read view的事务版本号。

read view的匹配规则

• TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
• TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用
• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
  • 提交读：如果要读取的数据还存在于TRX_ID事务列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
  • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务。
  • 再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

MySQL锁机制

写

多个事务同时更新一行数据，此时都会加锁然后都会排队等待，必须一个事务执行完毕了提交

了释放了锁才能唤醒别的事务继续执行。

读

• 快照读：rc和rr隔离级别支持，使用mvcc实现。
• 当前读：使用 next-key lock(record lock && gap lock) 实现。

ps：mysql的查询默认是使用mvcc机制不用加锁的

InnoDB存储引擎的行锁

• 加共享锁 select * from table lock in sharemode
• 加排它锁 select * from table for update

ps：一般开发业务系统查询主动加共享锁的情况较为少见，数据库的行锁是实用功能，一般不会在数据库层面做复杂的手动加锁操作，反而会用基于redis/zookeeper的分布式锁来控制业务系统的锁逻辑。

InnoDB存储引擎的表锁

• LOCK TABLES xxx READ：这是加表级共享锁。
• LOCK TABLES xxx WRITE：这是加表级独占锁。
• 意向共享锁：读数据自动加。
• 意向独占锁：更新数据时会自动加。

ps：DDL语句(alter)是通过MySQL通用的Metadata Locks(元数据锁)实现的，元数据锁与行锁是互斥的。元数据锁和表锁是不一样的。

锁的分类

• 排它锁(X)和共享锁(S)。行锁。

• 意向锁：意向共享锁(IS)和意向排它锁(XS)。表锁。

  • 设置IS：select … lock in share mode

  • 设置IX：select … for update

• 索引记录锁(record locks)。行锁。

• 间隙锁(gap locks)。行锁。

  锁定索引记录之间的间隙，rr默认开启间隙锁，rc不支持间隙锁。间隙锁可以防止幻读，也是rr和rc最大的区别所在。比如普通索引b的值为[10, 15, 20, 30]，如果锁定值为20的行，此时间隙锁锁定区间为(15, 20)和(20, 30)。

• next-key locks：record locks和gap locks的结合。

• 插入意向锁(insert intention locks)

  一种间隙锁，在insert之前被设置。这个锁表示将要插入数据，多个事务同时插入相同的索引间隙内不会冲突，除非插入的是同一个位置。假设索引列有两个值4和7，两个不同的事务分别想要插入5和6, 在获得索引行上的X锁之前，他们各自会使用插入意向锁，锁定4和7之间的间隙，但是他们不会相互阻塞，因为插入的是不同位置的行。

• 自增主键锁(auto-inc locks)

  特殊的表级别(table-level)的锁，当插入包含auto-increment属性列的表时会用到该锁。简单是说，一个事务向表中插入一条数据，那么其他事务的插入操作就需要等待，这样第一个获得锁的事务就能获得一个连续自增的主键值。