Mysql技术内幕——表&索引算法和锁

表

4.1、innodb存储引擎表类型

innodb表类似oracle的IOT表(索引聚集表-indexorganized table)，在innodb表中每张表都会有一个主键，如果在创建表时没有显示的定义主键则innodb如按照如下方式选择或者创建主键。首先表中是否有唯一非空索引(unique not null)，如果有则该列即为主键。不符合上述条件，innodb存储引擎会自动创建一个6字节大小的指针，rowid()。

4.2、innodb逻辑存储结构

innodb的逻辑存储单元由大到小分别是 tablespace,segment,extent,page(block)组成

4.2.1、表空间(tablespace)

所有数据都是存放在表空间中的，启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间中，每张表空间内存放的只是数据，索引和插入缓冲，其他类的数据，如undo信息，系统事务信息，二次写缓冲等还是存放在原来你的共享表空间。

4.2.2、段(segment)

常见的segment有数据段、索引段、回滚段。innodb是索引聚集表，所以数据就是索引，索引就是数据，那么数据段即是B+树的页节点(leaf node segment)，索引段即为B+树的非索引节点(non-leaf node segment)。而且段的管理是由引擎本身完成的。

4.2.3、区(extend)

　 区是由64个连续的页主成，每个页大小为16K，即每个区的大小为(64*16K)=1MB,对于大的数据段，mysql每次最多可以申请4个区，以此保证数据的顺序性能。

4.2.4、页(page)

页是innodb磁盘管理最小的单位，innodb每个页的大小是16K，且不可更改。常见的类型有：数据页 B-tree Node；undo页 Undo Log Page；系统页 System Page；事务数据页 Transaction system Page；插入缓冲位图页 Insert Buffer Bitmap；插入缓冲空闲列表页 Insert Buffer freeBitmap；未压缩的二进制大对象页Uncompressed BLOB Page；压缩的二进制大对象页 Compressed BLOB Page。

4.2.5、行

innodb存储引擎是面向行的(row-oriented),也就是说数据的存放按行进行存放。每个页最多可以存放16K/2～200行,也就是7992个行。

5.5.1、B+树索引的管理

索引可以索引整个列的数据，也可以只索引一个列的开头部分数据。InnoDB Plugin支持一种称为快速索引创建方法，这种方法只限定于辅助索引，创建索引会对表加上一个S锁，删除时只需将辅助索引的空间标记为可用，并删除内部视图上的对该表的索引定义即可。

5.6、B+树索引的使用

5.6.1、什么时候使用B+树索引

当某个字段的取值范围很广，几乎没有重复，即高选择性，则使用B+树索引是最适合的。根据笔者经验，一般取出数据占整个的20%时，优化器就不会使用索引，而是全表扫描。

5.6.2、顺序读，随机读与预读取

顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据，只是逻辑地顺序读在物理磁盘上可能还是随机读取。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。为提高读取性能，InnoDB采用预读取方式将所需数据读入内存，包括随机预读取 random read ahead 和线性预读取 linear read ahead。但是自InnoDB Plugin1.0.4起,随机访问的预读取被取消了,保留了线性预读取,并加入了innodb_read_ahead_threshold参数。它控制一个区中多少页被顺序访问时，InnoDB才启用预读取，预读取下一个页中所有的页。

5.7、hash索引

innodb存储引擎中自适应hash索引使用的是散列表(hash table)的数据结构。但是散列表不只存在于自适应hash中，每个数据库中都存在，用来加速内存中数据的查找。

5.7.1哈西表(hash table)

hash table又叫散列表，由直接寻址表改进而来。利用哈希函数解决了直接寻址遇到的问题，同时又使用链接发解决了碰撞问题。

5.7.2自适应哈西索引

它是数据库系统自己创建并使用的，DBA本身并不能对其进行干预。需要注意的是，哈希索引只能用来搜素等值的查询，对于其它的查找是不能使用哈希索引的。我们只能通过参数innodb_adaptive_hash_index来禁用或启动此特性。

锁

锁是区别文件系统和数据库系统的一个关键特性。

6.1、什么是锁?

锁是用来管理对共享文件的并发访问。innodb会在行级别上对数据库上锁。不过innodb存储引擎会在数据库内部其他多个地方使用锁，从而允许对不同资源提供并发访问。例如操作缓冲池中的LRU列表，删除，添加，移动LRU列表中的元素，为了保证一致性，必须有锁的介入。

6.2、innodb存储引擎中的锁

6.2.1、锁的类型

S lock 共享锁允许事务读一行数据。X lock 排它锁允许事务删除或者更新一条数据。IS lock 意向共享锁事务想要获得一个表中某几行的共享锁。IX lock 意向拍他所事务想要获得一个表中某几行的排它锁。因为InnoDB存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表扫描以外的任何请求。

6.2.2、一致性的非锁定读操作

一致性非锁定读(consistent nonlocking read)是指innodb通过多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。非锁定读的机制大大提高了数据读取的并发性，在InnoDB引擎中为默认的读取方法，即读取不会占用和等代表上的锁。多版本控制是通过快照实现的，快照数据其实就是当前数据之前的历史版本，可能有多个版本。这种技术称为行多版本技术，由此带来的并发控制叫做多半本并发控制(multi version concurrency control,MVCC).在Read Committed和Repeatable Read(innodb默认的事务隔离级别)下，innodb存储引擎使用非锁定的一致性读。但是对于快照数据的定义却不同。在Read Commited级别，对于快照数据，非一致性读总是读取被锁定行的最新一份快照。在Repeatable级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

6.2.3、SELECT…FOR UPDATE &SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE 可以获得一个X锁。SELECT…LOCK IN SHARE MODE 可以获得一个S锁。注意上述操作时必须使用显示提交方式，即加上begin,start transaction或者set autocommit = 0。

6.2.4、自增长和锁

对于含有子增长计数器的表进行插入时，会执行”SELECT MAX(auto_inc_col) FROM t FOR UPDATE;”插入操作会更具这个自增长的计数器值加1赋予自增长列。这个实现方式叫做AUTO-INC Locking。这是一种特殊的锁，为了提高并发，它不会在事务执行完才释放，只是在语句执行后立即释放。从mysql-5.1.22版本开始，innodb引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了子增长值插入的性能。并且mysql-5.1.22开始，innodb引擎提供了一个参数innodb_autoinc_lock_mode，默认的值为1。在讨论新的增长方式之前我们需要对自增长实现方式分类：1.INSERT-LIKE：指所有的插入语句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。2.Simple insert：指在插入前就能确定插入行数的语句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。3.Bulk inserts：指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.4.Mixed-mode inserts:指其中一部分是子增长的，有一部分是确定的。现在有SIMPLE INSERT、BULK INSERTS、MIXED-MODE INSERTS三种类型的INSERT语句，有AUTO-inc locking(最早的)和轻量级互斥量的自增长两种auto—increment锁。1.innodb_autoinc_lock_mode=0 5.1.22之前的方式，也就是所有类型的insert都用AUTO-inc locking。2.innodb_autoinc_lock_mode=1 这个参数是5.1.22之后出现的也是之后的默认值，对于SIMPLE INSERT，使用轻量级互斥量的锁，对于BULK INSERT，使用AUTO-inc locking。3.innodb_autoinc_lock_mode=2 指不管什么情况都使用轻量级互斥的锁，效率最高，但是复制只能使用row-basereplication，因为statement-base replication会出现问题。另外就是innodb和myisam的一个区别，innodb下，自增长必须是索引，而且必须是索引的第一个列，不然会报错，myisam不会出现这个问题。

6.2.5、外键和锁

外键主要用于引用完整性的约束检查。innodb中，对于一个外键列，如果没有显示的对这个列加索引，innodb就自动的对其加一个索引。

6.3、锁的算法

1.Record Lock，单行记录上的锁，锁住索引记录。2.GapLock，间隙锁能锁定一个范围，但不包括记录本身如 < 6 时，依然可以插入6。3.Next-KeyLock：Gap Lock + Record Lock，锁定一个范围并且锁定记录本身，如 < 6，插入6时会被阻塞。在REPEATABLE READ模式下 Next-KeyLock算法是默认的行记录锁定算法。

6.4、锁问题

本来锁问题会导致的是更新丢失、幻读、脏读、不可重复读，但是innodb作者却只写出了三种问题,可能是幻读通过innodb Next-key Lock解决了，作者就没有提及。这几个锁问题对应事务隔离的4个安全级别:READ UNCOMMITTED(事务隔离最低的级别，有事务隔离就能解决更新丢失，但是存在脏读的问题)。READ COMMITED(ORACLE和SQL SERVER默认的隔离级别，解决了脏读，但是一个事务多次读取的内容不同，出现了不可重复读的问题)。READ REPEATABLE(可重复读，innodb引擎的默认事务隔离级别，解决了不可重复读的问题，但是产生了幻读，innodb通过Next-key lock解决了幻读)。SERIALIZABLE(可串行话，通过强制事务排序解决幻读问题，会降低性能)总的看来innodb默认的 READ REPEATABLE是非常棒的。

6.5、阻塞

innodb中需要其他事务的锁释放它锁占用的资源，这个时候就会发生锁等待，这就是阻塞。innodb引擎有两个相关参数：innodb_lock_wait_timeout 用来设定等待的时间，默认是50秒，这是一个动态参数，可以随时调整；innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作，默认是OFF，代表不回滚，这是一个静态参数。

6.6、死锁

死锁会产生阻塞，所以可以通过6.5的参数，让超时的阻塞回滚。还有就是开发的时候，每个事务对表，字段，行的操作，都是顺序的，这样可以很大程度上避免死锁。