《高性能MySQL》读书笔记—数据类型、MVCC、InnoDB、索引等

MySQL默认的隔离级别是repeatable read。

InnoDB解决死锁：将持有最少行级排他锁的事务回滚。

1、数据类型

    （1）tinyint(8)，smallint(16)，mediumint(24)，int(32)，bigint(64)

    （2）float(32)，double(64)，decimal（最多65个数字，不会丢失精度，cpu不支持decimal计算，是MySQL实现的）

    （3）char，varchar（可变长，需要1或2额外字节记录长度，InnoDB可以把过长的varchar转化为blob）

    binary，varbinary：二进制字符串

    （tiny，small，medium，long）blob，text存储很大的字符串，分别用二进制和字符，默认是smalltext，最大64kb

    MySQL把blob和text当做一个独立的对象处理，InnoDB会行内存储1-4字节的指针，指向外部的实际存储区域。blob存储二进制，没有排序规则会字符集，text有排序规则和字符集。MySQL对blob或text的列排序时只排序前面部分字符，不能将blob或text列全部长度的字符串进行索引。

    （4）datetime（8字节），timestamp（4字节，范围小，1970-2038年）

    （5）enum，set（多选的enum）

2、MVCC和InnoDB

    InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

    MVCC各个数据库的实现机制不尽相同，没有统一标准，可以有乐观的并发控制和悲观的并发控制。MVCC是通过保存数据在某个时间点的快照来实现的。根据事务的开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

    InnoDB的MVCC在每行记录后保存两个隐藏的列，行的创建时间和删除时间（不是实际时间，是系统版本号，每开始一个事务系统版本号递增），事务开始时刻的系统版本号作为该事务的版本号。

    举个例子，InnoDB repeatable read级别MVCC的实现（每行记录保存行版本号和删除版本号）：

    （1）查找时，返回行版本号≤事务版本号&&删除版本号==null || 删除版本号 ≥ 事务版本号的记录。

    （2）插入时，用当前系统版本号作为行版本号。

    （3）删除时，用当前系统版本号作为删除版本号。

    （4）更新时，插入一条新纪录，删除旧记录。

    InnoDB保存了额外的两列，不用加锁，只会读取到符合标准的行，但需要额外的存储空间和检查工作。MVCC只在read committed和repeatable read下工作，不兼容其他两个隔离级别。因为read uncommitted总是读取最新的数据，与版本号冲突；serializable加表锁。

    InnoDB采用MVCC支持高并发，通过间隙锁（next-key locking）防止幻读。间隙锁不仅仅锁定查询的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

    InnoDB表数据文件本身就是一个主键的聚集索引（B+树索引），叶节点存储完整的记录而不是指针。InnoDB所有的辅助索引都包含主键列，用索引项和主键作为data域（存储在叶节点），而不是在叶节点中存储指针。若表上索引多，主键应尽可能小。即相当于用主键作为指针。

    相比于InnoDB，MyISAM不支持事务、外键、行级锁，B+树索引的叶节点存储指针；可以对只读表进行压缩（应用于CD-ROM等）。

3、索引

    多列索引排序的依据是create index时列的顺序，B+树索引适用于随机查找、顺序查找以及多列时根据索引项的最左前缀查找（例如索引列为（A,B,C），只能根据A，（A,B）来查找）。

    全文索引：倒排索引

    覆盖索引：索引包括所有需要查询的字段

    InnoDB聚集索引就是表，二级索引（非聚集索引/辅助索引）叶子结点除了索引项，还存储主键作为指向行的“指针”。这样的策略减少了当出现行移动或数据页分裂时二级索引的维护工作，即InnoDB移动行时无需更新二级索引的“指针”，但二级索引更占空间。

    如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有，隐式定义一个主键作为聚集索引，聚集索引最大限度的提高了IO密集型应用的性能。

    InnoDB使用auto_increment可以按顺序插入行（auto_increment必须是主键），如果用UUID作为聚集索引会使插入随机。

    MySQL只能在索引中做最左前缀的like比较。

4、查询缓存

    MySQL的查询缓存缓存完整的select查询结果，如果表变化，缓存失效。

    查询缓存通过一个哈希值引用（包括查询语句本身、查询的数据库、客户端协议版本的哈希），SQL上的任何不同都会导致缓存不命中。