[TOC] # 存储引擎 ## 什么是存储引擎 与其他数据库例如**Oracle** 和**SQL Server**等数据库中只有一种存储引擎不同的是，**MySQL** 有一个被称为“**Pluggable Storage Engine Architecture**”(可替换存储引擎架构)的特性，也就意味着**MySQL**数据库提供了多种存储引擎。 用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。 **MySQL**数据库在实际的工作中其实分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。 简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。 ## 存储引擎种类 | 存储引擎 | 说明 | | --- | --- | | **InnoDB** | 5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢 | | **MyISAM** | 高速引擎，拥有较高的插入，查询速度，但不支持事务 | | ISAM | MyISAM的前身，MySQL5.0以后不再默认安装 | | Memory | 内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失 | | Archive | 将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记录的数据，但是只能进行插入和查询操作 | ## MyISAM 引擎 * v5.7 之前：每个`MyISAM`表都存储在磁盘上的三个文件中。这些文件的名称以表名开头，并具有指示文件类型的扩展名。`.frm`文件存储表格格式。数据文件具有`.MYD`(`MYData`) 扩展名。索引文件具有`.MYI`(`MYIndex`) 扩展名。 * v5.7 之后：每个`MyISAM`表都存储在磁盘上的两个文件中。数据文件具有`.MYD`(`MYData`) 扩展名。索引文件具有`.MYI`(`MYIndex`) 扩展名。表定义存储在 MySQL 数据字典中。 ~~~ CREATE TABLE t (i INT) ENGINE = MYISAM; ~~~ ### 实现 **MyISAM** 引擎使用**B+Tree**作为索引结构，叶节点的 data 域存放的是数据记录的地址。  这里设表一共有三列，假设我们以 Col1 为主键，上图是一个 MyISAM 表的**主索引（Primary key）**示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。 在 MyISAM 中，**主索引**和**辅助索引（Secondary key）**在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。  **辅助索引** 同样也是一颗 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以 data 域的值为地址，读取相应数据记录。 MyISAM的索引方式也叫做“**非聚集**”的，之所以这么称呼是为了与InnoDB的聚集索引区分。 ### 特性  > **不是事务安全的，不支持外键，如果有大量的 `select`**，MyISAM比较合适。插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。 ## InnoDB 引擎 【默认引擎】 ### 实现 **InnoDB**也使用 B+Tree 作为索引结构。在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。InnoDB 的数据文件本身就是索引文件。  InnoDB 主索引（Primary Key）叶节点包含完整的数据记录，这种索引方式叫做 **聚簇索引**。 因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。  InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址，换句话说，InnoDB的所有辅助索引都引用主键作为data域。 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。 **为什么不建议使用过长的字段作为主键？** 因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大 ### 特性  > **支持事务处理，支持外键，行锁，支持崩溃修复能力和并发控制**。如果需要对事务的**完整性要求比较高**（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要**频繁的`Insert`、`Update`操作的数据库**，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。 ## MyISAM 和 InnoDB 的区别 **MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。** * **行级锁** MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。 * **事务** MyISAM 不提供事务支持。 * **外键** MyISAM 不支持外键。 > 外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。通常情况下，不建议在实际生产项目中使用外键 * **数据库异常崩溃后的安全恢复** MyISAM 不支持 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于`redo log` * **索引实现** 都是使用 B+Tree 作为索引结构，但是两者实现方式不一样。 InnoDB 引擎，数据文件本身就是索引文件。MyISAM 索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。 # 索引结构（方法、算法） MySQL 常用两种索引结构 BTree 和 Hash 。 | 存储引擎 | 显示支持索引结构 | | --- | --- | | InnoDB | BTree | | MyISAM | BTree | | Memory | HASH，BTREE | ## Hash Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。 Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。 ### 特性 1. hash 索引仅仅能满足 `=`，`IN`，`<=>` 查询，不能会用范围查询 2. 联合索引中， Hash 索引不能利用部分索引键查询。对于联合索引中的多个列，Hash 是要么全部使用，要么全部不使用，并不支持 BTree 支持的联合索引的最左前缀，也就是联和索引的前面一个或者几个索引键进行查询时， Hash 索引无法被利用。 3. Hash索引无法避免数据的排序操作 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。 4. Hash索引任何时候都不能避免表扫描 Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。 5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高 对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。 ## B+Tree 在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。  # 索引方式 ## **聚集索引** 指索引项的排序方式和表中数据记录排序方式一致的索引。聚集索引的叶子节点存储了整个行数据(即:一张表只能有一个聚集索引)。 什么叫索引项的排序方式和表中数据记录排序方式一致呢？ 我们把一本字典看做是数据库的表，那么字典的拼音目录就是聚集索引，它按照A-Z排列。实际存储的字也是按A-Z排列的。这就是索引项的排序方式和表中数据记录排序方式一致。 > 对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则: > * 如果一个主键被定义了，那么这个主键就是作为聚集索引。 > * 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。 > * 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。 ## **非聚集索引** 非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。 # 索引类型 ## 普通索引 （INDEX） 最基本的索引，没有任何限制。 1. 直接创建 ~~~ CREATE INDEX [索引名] ON [表名]([字段]) ~~~ 2. 修改表结构--添加索引 ~~~ ALTER TABLE [表名] ADD INDEX [索引名] ON ([字段名]) ~~~ 3. 创建表时创建 ~~~ CREATE TABLE `users` (    `id` int(11) NOT NULL AUTO_INCREMENT ,    `name` char(18) CHARACTER NOT NULL ,    INDEX name_index (name) ) ~~~ ## 唯一索引（UNIQUE） 索引列的值必须唯一，但允许有空值。 1. 创建唯一索引 ~~~ CREATE UNIQUE INDEX [索引名称] ON [表名]([字段名]) ~~~ 2. 修改表结构 ~~~ ALTER TABLE [表名] ADD UNIQUE [索引名] ON ([字段名]) ~~~ 3. 创建表的时候直接指定 ~~~ CREATE TABLE `users` (    `id` int(11) NOT NULL AUTO_INCREMENT ,     `name` char(18) CHARACTER NOT NULL ,     UNIQUE unique_name (name) ); ~~~ ## 主键索引 （PRIMAY KEY） 一个表只能有一个主键，不允许有空值，是一种特殊的唯一索引。 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT , PRIMARY KEY (`id`) ); ## 联合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循**最左前缀集合** ~~~ ALTER TABLE `users` ADD INDEX name_city_age (name,city,age); ~~~ ## 全文索引（FULLTEXT） 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配 # 索引优化 ## 回表 根据上面的索引说明，聚簇索引和非聚簇索引的查询区别 * 如果语句是 `select * from T where ID=500`，即主键查询方式，则只需要搜索 ID 这棵 B+ 树； * 如果语句是 `select * from T where k=5`，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为**回表**。 ## 索引下推（Index Condition Pushdown） 当 MySQL 使用一个索引来检索表中的行时，可以使用 **ICP** 作为一种优化方案。5.6以后推出的功能。 在这里有张用户表 user，记录着用户的姓名，性别，身高，年龄等信息。表中 id 是自增主键，(name,sex) 是联合索引。在这里用 1 表示男，2 表示女。现在需要查找所有姓王的男性信息。 ### 没有索引下推时： ~~~ select * from user where name like '王%' and sex = 1; ~~~ **查询原理：** 根据联合索引最左前缀原则，在非主键索引树上找到第一个满足条件的值时，通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比是否为当前所要查找的性别。 每条数据都会回表。  ### 有索引下推时： 索引下推就是只有符合条件再进行回表，对索引中包含的字段先进行判断，不符合条件的跳过。减少了不必要的回表操作。  > 索引下推是在**非主键索引**上的优化，可以有效减少回表的次数，大大提升了查询的效率。 ## 覆盖索引 * `select` 查询的数据列已创建索引 * 一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做**覆盖索引**。 * 是非聚集组合索引的一种形式，它包括在查询里的 `select`、`join`和`where`子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用 B-Tree 索引做覆盖索引 当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在`EXPLAIN`的 **Extra**列可以看到“Using index” 的信息 ~~~ mysql> explain select id from users where name = '赵四'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set (0.04 sec) ~~~