1. 什么是存储引擎？
与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。MySQL数据库在实际的工作中其实分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

2. 存储引擎种类

接下来我们就介绍两种在实际开发中使用最多的两种引擎【MyISAM】和【InnoDB】。

3. MyISAM 引擎
这种引擎是MySQL最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

静态MyISAM： 如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为 数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。

动态MyISAM: 如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。

压缩MyISAM： 以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

当然不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

• 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。 例如我创建了一个【test】表，那么就会生成以下三个文件：

4. InnoDB引擎
InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 使用InnoDB是最理想的选择：

• 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求 事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎
• 自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复
• 外键约束：MySQL支持外键的存储引擎，只有InnoDB支持自动增加列AUTO_INCREMENT属性

总结

• InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

在mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。
常用存储引擎对应的索引结构

mysql InnoDB存储引擎 是支持hash索引的，不过，我们必须启用，hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了。

1.Hash
Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询。其结构如下所示：

从上面结构可以看出，Hash 索引可以比较方便的提供等值查询的场景,由于是一次定位数据，不像BTree索引需 要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。但是对于范围查询的话，就需要进行全表扫描了。

但为什么我们使用BTree比使用Hash多呢？主要Hash本身由于其特殊性，也带来了很多限制和弊端：

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值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

2. B+Tree
B+Tree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：* select * from user where name like ‘jack%’; select * from user where name like ‘jac%k%’; 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： select * from user where name like ‘%jack’; select * from user where name like simply_name;

Mysql数据库中的B+树索引可以分为聚集索引和非聚集索引（辅助索引）

聚集索引
聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引（这里不懂先放着，一会举例），每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据(即:一张表只能有一个聚集索引)。

解释： 什么叫索引项的排序方式和表中数据记录排序方式一致呢？ 我们把一本字典看做是数据库的表，那么字典的拼音目录就是聚集索引，它按照A-Z排列。实际存储的字也是按A-Z排列的。这就是索引项的排序方式和表中数据记录排序方式一致。

对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则:

• 如果一个主键被定义了，那么这个主键就是作为聚集索引。
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

非聚集索引
非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。

MySQL目前主要有以下几种索引类型：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引
• 全文索引

普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

• 直接创建索引

• 修改表结构的方式添加索引

• 创建表的时候同时创建索引

• 删除索引

唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

• 创建唯一索引

• 修改表结构

（3）创建表的时候直接指定

主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循 最左前缀集合

全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

mysql执行计划中的extra列中表明了执行计划的每一步中的实现细节，其中包含了与索引相关的一些细节信息 其中跟索引有关的using index 在不同的情况下会出现Using index， Using where Using index ，Using index condition，(Using filesort, Using temporay 需要优化) 等 ，那么Using index 和 Using where;Using index 有什么区别？网上搜了一大把文章，说实在话也没怎么弄懂，于是就自己动手试试。

本文仅从最简单的单表去测试using index 和 using where using index以及简单测试using index condition的情况的出现时机 。 执行计划的生成与表结构，表数据量，索引结构，统计信息等等上下文等多种环境有关，无法一概而论，复杂情况另论。

测试环境搭建

创建存储过程（insertOrder），创建测试数据，脚本如下

Using index

1，查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index

• 查询的列全部在索引中,并且where筛选条件是索引的前导列
• type:ref(索引查找)+ Extra:using index

Using where Using index

查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的不是前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据

• 查询的列全部在索引中,并且where的筛选条件不符合索引的前导列
• type:index(索引扫描) + Extra:Using where; Using index

查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

• 查询的列全部在索引中,并且where的筛选条件是索引前导列的一个范围
• type:index(索引扫描) + Extra:Using where; Using index

NULL（既没有Using index，也没有Using where Using index，也没有using where）

查询的列未被索引覆盖，并且where筛选条件是索引的前导列， 　　 意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

• 查询的列不全在索引中,并且where的筛选条件是索引的前导列
• type:ref + Extra:NULL

Using where

查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where

• 查询的列不全在索引中,并且where的筛选条件不是索引的前导列

using where 意味着通过索引或者表扫描的方式进程where条件的过滤， 反过来说，也就是没有可用的索引查找，当然这里也要考虑索引扫描+回表与表扫描的代价。 这里的type都是all，说明MySQL认为全表扫描是一种比较低的代价。

Using index condition

查询的列不全在索引中，where条件中是一个前导列的范围

• 查询的列不全在索引中，where条件中是一个前导列的范围

查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）

• 查询的列不全在索引中，查询条件完全可以使用到索引（进行索引查找）

结论：

1. Extra中的为Using index的情况。 where筛选列是索引的前导列 &&查询列被索引覆盖 && where筛选条件是一个基于索引前导列的查询，意味着通过索引超找就能直接找到符合条件的数据，并且无须回表
2. Extra中的为空的情况。查询列存在未被索引覆盖&&where筛选列是索引的前导列，意味着通过索引超找并且通过“回表”来找到未被索引覆盖的字段，
3. Extra中的为Using where Using index。出现Using where Using index意味着是通过索引扫描（或者表扫描）来实现sql语句执行的，即便是索引前导列的索引范围查找也有一点范围扫描的动作，不管是前非索引前导列引起的，还是非索引列查询引起的。

MySQL执行计划中的Extra中信息非常多，不仅仅包括Using index，Using where Using index，Using index condition，Using where，尤其是在多表连接的时候，这一点在相对MSSQL来说，不够直观或者结构化。MSSQL中是通过区分索引查找（index seek），索引扫描（index scan），表扫描（table scan）来实现具体的查询的，这图形化的执行计划在不同的场景下是非常直观的，要想完全弄懂MySQL的这个执行计划，可能要更多地在实践中摸索。

1. id 表示执行的顺序，id越大越先执行，id一样的从上往下执行。
2. select_type
3. table表名或者表的别名。
4. partitions分区信息，非分区表为null。
5. type 访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：

6. possible_keys 表示mysql此次查询中可能使用的索引。
7. key 表示mysql实际在此次查询中使用的索引。
8. key_len 表示mysql使用的索引的长度。该值越小越好。
9. ref 表示连接查询的连接条件。
10. rows 表示mysql估计此次查询所需读取的行数。该值越小越好。
11. extra 表示mysql解决查询的其他信息，有几十种不同的值，该信息也是我们优化sql可以专注的一个值。关于这个extra信息我可能会再下一篇中介绍，这里先略过。

索引结果值从好到坏依次是：

优化原则

• 禁用select *
• 使用select count(*) 统计行数
• 尽量少运算
• 尽量避免全表扫描，如果可以，在过滤列建立索引
• 尽量避免在where子句对字段进行null判断
• 尽量避免在where子句使用!= 或者<>
• 尽量避免在where子句使用or连接
• 尽量避免对字段进行表达式计算
• 尽量避免对字段进行函数操作
• 尽量避免使用不是复合索引的前缀列进行过滤连接
• 尽量少排序，如果可以，建立索引
• 尽量少join
• 尽量用join代替子查询
• 尽量避免在where子句中使用in,not in或者having，使用exists,not exists代替
• 尽量避免两端模糊匹配 like %***%
• 尽量用union all代替union
• 尽量早过滤
• 避免类型转换
• 尽量批量insert
• 优先优化高并发sql，而不是频率低的大sql
• 尽可能对每一条sql进行explain
• 尽可能从全局出发

1. 如何解决非聚集索引二次查询(回表)的问题？
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句：

因为复合索引的列包括了col1和col2，不需要查询别的列，所以不需要进行二次查询。

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

2. 哪些情况下需要创建索引

• 主键自动建立索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他字段关联的字段，外键关系建立索引
• 频繁更新的字段不适合建立索引，因为每次更新不单单更新记录，还会更新索引
• where 条件里用不到的字段不创建索引
• 单键/组合索引的选择问题，在高并发下倾向创建组合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中的分组或统计字段

3. 哪些情况下不要创建索引

• 表的记录太少
• 经常增删改的表
• 数据重复且分布平均的表字段