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)多个实例公用一份数据了；
193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
