1.索引到底是怎么实现的?
- 数据库索引用于加速查询
- 虽然哈希索引是O(1),树索引是O(log(n)),但SQL有很多“有序”需求,故数据库使用树型索引
- InnoDB不支持哈希索引
- 数据预读的思路是:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,以便未来减少磁盘IO
- 局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO
- 数据库的索引最常用B+树:
- 很适合磁盘存储,能够充分利用局部性原理,磁盘预读;
- 很低的树高度,能够存储大量数据;
- 索引本身占用的内存很小;
- 能够很好的支持单点查询,范围查询,有序性查询;
MyISAM和InnoDB都使用B+树来实现索引:
- MyISAM的索引与数据分开存储
- MyISAM的索引叶子存储指针,主键索引与普通索引无太大区别
- InnoDB的聚集索引和数据行统一存储
- InnoDB的聚集索引存储数据行本身,普通索引存储主键
- InnoDB一定有且只有一个聚集索引
- InnoDB建议使用趋势递增整数作为PK,而不宜使用较长的列作为PK
2.《InnoDB,为何并发如此之高?》
总结
在大数据量,高并发量的互联网业务场景下,对于MyISAM和InnoDB
- 有where条件,count(*)两个存储引擎性能差不多
- 不要使用全文索引,应当使用《索引外置》的设计方案
- 事务影响性能,强一致性要求才使用事务
- 不用外键,由应用程序来保证完整性
- 不命中索引,InnoDB也不能用行锁
结论
在大数据量,高并发量的互联网业务场景下,请使用InnoDB:
- 行锁,对提高并发帮助很大
- 事务,对数据一致性帮助很大
这两个点,是InnoDB最吸引人的地方。
MySQL默认的事务隔离级别是 Repeated Read (RR),假设使用的存储引擎是InnoDB,在这个隔离级别下:
- (1)读取到数据,都是其他事务已提交的数据;
- (2)同一个事务中,相同的连续读,得到的结果应该是相同的;
- (3)不会出现insert幻象读;
总结
- 常见并发控制保证数据一致性的方法有锁,数据多版本;
- 普通锁串行,读写锁读读并行,数据多版本读写并行;
- redo日志保证已提交事务的ACID特性,设计思路是,通过顺序写替代随机写,提高并发;
- undo日志用来回滚未提交的事务,它存储在回滚段里;
- InnoDB是基于MVCC的存储引擎,它利用了存储在回滚段里的undo日志,即数据的旧版本,提高并发;
- InnoDB之所以并发高,快照读不加锁;
- InnoDB所有普通select都是快照读;
3.InnoDB的七种锁
总的来说,InnoDB共有七种类型的锁:
- 共享/排它锁(Shared and Exclusive Locks)
- 意向锁(Intention Locks)
- 记录锁(Record Locks)
- 间隙锁(Gap Locks)
- 临键锁(Next-key Locks)
- 插入意向锁(Insert Intention Locks)
- 自增锁(Auto-inc Locks)
自增锁是一种特殊的表级别锁(table-level lock),专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。
与此同时,InnoDB提供了innodb_autoinc_lock_mode配置,可以调节与改变该锁的模式与行为。
一,共享/排它锁(Shared and Exclusive Locks)
- 共享/排它锁的潜在问题是,不能充分的并行,解决思路是数据多版本
二,意向锁(Intention Locks)
- 意向锁,是一个表级别的锁(table-level locking);
- 意向锁是指,未来的某个时刻,事务可能要加共享/排它锁了,先提前声明一个意向。
- 排它锁是很强的锁,不与其他类型的锁兼容。
三,插入意向锁(Insert Intention Locks)
- 插入意向锁,是间隙锁(Gap Locks)的一种(所以,也是实施在索引上的),它是专门针对insert操作的。
- 多个事务,在同一个索引,同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此。
思路总结
- (1)InnoDB使用共享锁,可以提高读读并发;
- (2)为了保证数据强一致,InnoDB使用强互斥锁,保证同一行记录修改与删除的串行性;
- (3)InnoDB使用插入意向锁,可以提高插入并发;
- 除非显示加锁,普通的select语句都是快照读,例如:select * from t where id>2;
- 这里的显示加锁,非快照读是指:
- select * from t where id>2 lock in share mode; 设置IS锁
- select * from t where id>2 for update; 设置IX锁
一、记录锁(Record Locks)
- 记录锁,它封锁索引记录,例如:
- select * from t where id=1 for update; 它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行。
- select * from t where id=1;则是快照读(SnapShot Read),它并不加锁
二、间隙锁(Gap Locks)
- 间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。
- select * from t where id between 8 and 15 for update;
- 会封锁区间,以阻止其他事务id=10的记录插入。
- 画外音:为什么要阻止id=10的记录插入?
- 如果能够插入成功,头一个事务执行相同的SQL语句,会发现结果集多出了一条记录,即幻影数据。
- 间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”。
- 如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。
三、临键锁(Next-Key Locks)
- 临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。
- 更具体的,临键锁会封锁索引记录本身,以及索引记录之前的区间。
- 如果一个会话占有了索引记录R的共享/排他锁,其他会话不能立刻在R之前的区间插入新的索引记录。
- 临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。
总结
- InnoDB的索引与行记录存储在一起,这一点和MyISAM不一样;
- InnoDB的聚集索引存储行记录,普通索引存储PK,所以普通索引要查询两次;
- 记录锁锁定索引记录;
- 间隙锁锁定间隔,防止间隔中被其他事务插入;
- 临键锁锁定索引记录+间隔,防止幻读;
4. 《InnoDB如何巧妙实现,事务的4种隔离级别?》
事务ACID特性,其中I代表隔离性(Isolation)。
什么是事务的隔离性?
- 隔离性是指,多个用户的并发事务访问同一个数据库时,一个用户的事务不应该被其他用户的事务干扰,多个并发事务之间要相互隔离。
- 不同事务的隔离级别,实际上是一致性与并发性的一个权衡与折衷。
InnoDB的四种事务的隔离级别,分别是怎么实现的?
InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。
一,读未提交(Read Uncommitted)
- 这种事务隔离级别下,select语句不加锁。此时,可能读取到不一致的数据,即“读脏”。这是并发最高,一致性最差的隔离级别。
二,串行化(Serializable)
- 这种事务的隔离级别下,所有select语句都会被隐式的转化为select ... in share mode.
- 这可能导致,如果有未提交的事务正在修改某些行,所有读取这些行的select都会被阻塞住。
- 这是一致性最好的,但并发性最差的隔离级别。
在互联网大数据量,高并发量的场景下,几乎不会使用上述两种隔离级别。
三,可重复读(Repeated Read, RR)
这是InnoDB默认的隔离级别,在RR下:
(1)普通的select使用快照读(snapshot read),这是一种不加锁的一致性读(Consistent Nonlocking Read),底层使用MVCC来实现,具体的原理在《InnoDB并发如此高,原因竟然在这?》中有详细的描述;
(2)加锁的select(select ... in share mode / select ... for update), update, delete等语句,它们的锁,依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition),或者范围查询条件(range-type search condition):
- 在唯一索引上使用唯一的查询条件,会使用记录锁(record lock),而不会封锁记录之间的间隔,即不会使用间隙锁(gap lock)与临键锁(next-key lock)
- 范围查询条件,会使用间隙锁与临键锁,锁住索引记录之间的范围,避免范围间插入记录,以避免产生幻影行记录,以及避免不可重复的读
总结
- 并发事务之间相互干扰,可能导致事务出现读脏,不可重复度,幻读等问题
- InnoDB实现了SQL92标准中的四种隔离级别
(1)读未提交:select不加锁,可能出现读脏;
(2)读提交(RC):普通select快照读,锁select /update /delete 会使用记录锁,可能出现不可重复读;
(3)可重复读(RR):普通select快照读,锁select /update /delete 根据查询条件情况,会选择记录锁,或者间隙锁/临键锁,以防止读取到幻影记录;
(4)串行化:select隐式转化为select ... in share mode,会被update与delete互斥;
- InnoDB默认的隔离级别是RR,用得最多的隔离级别是RC
5.《别废话,各种SQL到底加了什么锁?》
一、普通select
- 在读未提交(Read Uncommitted),读提交(Read Committed, RC),可重复读(Repeated Read, RR)这三种事务隔离级别下,普通select使用快照读(snpashot read),不加锁,并发非常高;
- 在串行化(Serializable)这种事务的隔离级别下,普通select会升级为select ... in share mode;
二、加锁select
加锁select主要是指:
- select ... for update
- select ... in share mode
- 如果,在唯一索引(unique index)上使用唯一的查询条件(unique search condition),会使用记录锁(record lock),而不会封锁记录之间的间隔,即不会使用间隙锁(gap lock)与临键锁(next-key lock);
- 其他的查询条件和索引条件,InnoDB会封锁被扫描的索引范围,并使用间隙锁与临键锁,避免索引范围区间插入记录;
三、update与delete
- 和加锁select类似,如果在唯一索引上使用唯一的查询条件来update/delete,例如:update t set name=xxx where id=1;也只加记录锁;
- 否则,符合查询条件的索引记录之前,都会加排他临键锁(exclusive next-key lock),来封锁索引记录与之前的区间;
- 尤其需要特殊说明的是,如果update的是聚集索引(clustered index)记录,则对应的普通索引(secondary index)记录也会被隐式加锁,这是由InnoDB索引的实现机制决定的:普通索引存储PK的值,检索普通索引本质上要二次扫描聚集索引。
四、insert
- 同样是写操作,insert和update与delete不同,它会用排它锁封锁被插入的索引记录,而不会封锁记录之前的范围。
- 同时,会在插入区间加插入意向锁(insert intention lock),但这个并不会真正封锁区间,也不会阻止相同区间的不同KEY插入。
6.《超赞,InnoDB调试死锁的方法!》
- 间隙锁是否关闭 innodb_locks_unsafe_for_binlog :show global variables like "innodb_locks%";
- 事务自动提交 show global variables like "autocommit";
- 事务的隔离级别 show global variables like "tx_isolation";
- 设置事务的隔离级别:set session transaction isolation level X;
- X取:read uncommitted 、read committed、repeatable read、serializable
- 要模拟并发事务,需要修改事务自动提交这个选项,每个session要改为手动提交。
- 任何连上MySQL的session,都要手动执行:set session autocommit=0;以手动控制事务的提交。
【总结】
说了很多,希望大家能起手来,这样对InnoDB锁的机制,以及锁的调试印象会更加深刻:
- 并发事务,间隙锁可能互斥
(1)A删除不存在的记录,获取共享间隙锁;
(2)B插入,必须获得排他间隙锁,故互斥;
- 并发插入相同记录,可能死锁(某一个回滚)
- 并发插入,可能出现间隙锁死锁(难排查)
- show engine innodb status; 可以查看InnoDB的锁情况,也可以调试死锁
7.《MySQL不为人知的主键与唯一索引》
总结,对于主键与唯一索引约束:
- 执行insert和update时,会触发约束检查
- InnoDB违反约束时,会回滚对应SQL
- MyISAM违反约束时,会中断对应的SQL,可能造成不符合预期的结果集
- 可以使用 insert … on duplicate key 来指定触发约束时的动作
- 通常使用 show warnings; 来查看与调试违反约束的ERROR
8.其他
1. 从这个版本开始,可以支持全文索引了。
2. alter table可以不拷贝表了,且不阻塞写操作,online DDL,酷炫吧。
画外音:并不是所有的alter table都这样。
3. 建表时,允许一个table一个文件了,具体能玩出什么花呢?
这样就能够实现,热数据表放SSD里,数据量大的表放HDD里了。
4. 可以支持memcached插件了,关系型数据库和memcached缓存实现在一起,支持几十万的吞吐量,是不是简化了系统架构?
5. 可以支持只读实例了,这样就能够实现:
(1)把InnoDB表放在DVD或CD里,方便共享;
(2)多个实例公用一份数据了;