MySql存储引擎&MySQL索引

MySql存储引擎

存储引擎在MySQL的体系架构中位于第三层，负责MySQL中的数据的存储和提取，是与文件打交道的
子系统，它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制，这种机制就叫作存储引
擎。
使用show engines命令，就可以查看当前数据库支持的引擎信息。

在5.5版本之前默认采用MyISAM存储引擎，从5.5开始采用InnoDB存储引擎。

• innoDB：支持事务，具有提交、回滚和崩溃恢复能力，事务安全
• MyISAM：不支持事务和外键，访问速度快
• Memory：利用内存创建表，访问速度非常快，因为数据在内存，而且默认使用Hash索引，但是一旦关闭，数据就会失去
• Arichive：归档类型引擎，仅能支持insert和select语句
• Csv：以Csv文件进行存储，由于文件限制，所有列必须强制指定not null，另外Csv引擎也不支持索引和分区，适合做数据交换的中间表
• BlackHole：黑洞，只进不出，进来消失，所有插入数据都不会保存
• Federated：可以访问远端MySQL数据库中的表。一个本地表，不保存数据，远程访问表内容
• MRG_MyISAM：一组MyISAM表的组合，这些MyISAM表必须结构相同，Merge表本身没有数据，对merge操作可以对一组MyISAM表进行操作

InnoDB和MyISAM对比

InnoDB和MyISAM时使用MySQL时最常用的两个引擎类型，我们重点关注下两者的区别：

• 事务和外键
  InnoDB支持事务和外键，具有安全性和完整性，适合大量的insert和update操作
  MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
• 锁机制
  InnoDB支持行级锁，锁定指定记录。基于索引加锁实现
  MyISAM支持表级锁，锁定整张表
• 索引结构
  InnoDB使用聚集索引(聚簇索引)，索引和记录在一起存储，既缓存索引，也缓存记录
  MyISAM使用非聚集索引(非聚簇索引)，索引和记录分来
• 并发处理能力
  MyISAM使用表级锁，会导致写操作并发率低，读之间不阻塞，读写阻塞
  InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制(MVCC)来支持高并发
• 存储文件
  InnoDB表对应两个文件，一个是.frm表结构文件，一个是.ibd数据文件。InnoDB表最大支持64TB
  MyISAM表对应三个文件，一个是.frm表结构文件，一个是MYD表数据文件，一个是.MYI索引文件。从MySQL5.0开始默认限制是256TB
• 适用场景
  MyISAM
  不需要事务支持（不支持）
  处理并发能力相对较低（锁定机制问题）
  数据修改相对较少，以读为主
  数据一致性要求不高
  InnoDB
  需要事务支持（较好的事务特性）
  行级锁锁定对高并发有很好的的适应能力
  数据更新比较频繁的场景
  数据一致性要求较高
  硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

总结

两种引擎如何选择：

• 是否需要有事务？ 有 ， InnoDB
• 是否存在并发修改？ 存在 ， InnoDB
• 是否追求快速查询，且数据修改少？ 是 ， MyISAM
• 在绝大多数情况下，推荐使用InnoDB

MySQL索引

索引类型

索引可以提升查询的速度，会影响where查询，以及order by 排序。MySQL索引类型如下：

• 从索引的存储结构划分：B tree索引、Hash索引、FULLTEXT全文索引、R tree索引
• 从应用分层划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。
创建普通索引的方法如下：

• create index <索引的名字> on tablename(字段名)；
• alter table tablename add index [索引的名字] (字段名)
• create table tablename ([…], index [索引的名字] (字段名))

唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
创建唯一索引的方法如下：

• CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
• CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
创建主键索引的方法如下：

• CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
• ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。
创建组合索引的方法如下：

• CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);
• ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2…);
• CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );

复合索引使用注意事项：

• 复合索引字段是有顺序的，在查询使用时要按照索引字段的顺序使用。例如：select * from user where name = xx and age = xx，匹配(name,age)组合索引，不匹配(age,name)
• 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
• 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

全文索引(了解，重点在ES)

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

创建全文索引的方法如下：

• CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
• ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
• CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from user where match(name) against('aaa');

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against('a*' in boolean mode);

索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护
工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

• 首先定位left和right两个指针
• 计算(left+right)/2
• 判断除2后索引位置值与目标值的大小比对
• 索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：

• 第一次查找
  
• 第二次查找
  
• 第三次查找
  
• 第四次查找
  

Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。
Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。

InnoDB提供的自适应哈希索引功能强大，接下来重点描述下InnoDB 自适应哈希索引。

InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当
InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内
存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。

InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。

自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页
建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法
进行人工干涉。

show engine innodb status \G; 
show variables like '%innodb_adaptive%';

B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

• B-Tree结构
  - 索引值和data数据分布在整棵树结构中
  - 每个节点可以存放多个索引值及对应的data数据
  - 树节点中的多个索引值从左到右升序排列
  
  B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有
  命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

• B+Tree结构
  - 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  - 叶子节点包含了所有的索引值和data数据
  - 叶子节点用指针连接，提高区间的访问性能
  
  相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进
  行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

聚簇索引和辅助索引

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

• 聚簇索引（聚集索引）
  聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
  InnoDB的表要求必须要有聚簇索引：
  • 如果表定义了主键，则主键索引就是聚簇索引
  • 如果表没有定义主键，则第一个非空unique列作为聚簇索引
  • 否则InnoDB会从建一个隐藏的row-id作为聚簇索引
• 辅助索引
  InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
  
• 非聚簇索引
  与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。
  

索引分析与优化

EXPALAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信
息，供开发人员有针对性的优化。例如：

EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

• select_type
  表示查询的类型。常用的值如下：
  • SIMPLE ： 表示查询语句不包含子查询或union
  • PRIMARY：表示此查询是最外层的查询
  • UNION：表示此查询是UNION的第二个或后续的查询
  • DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
  • UNION RESULT：UNION的结果
  • SUBQUERY：SELECT子查询语句
  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

• type
  表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
  • const：表示使用主键或唯一索引做等值查询，常量查询。
  • NULL：表示不用访问表，速度最快。

• possible_keys
  表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
• key
  表示查询时真正使用到的索引，显示的是索引名称。
• rows
  MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
• key_len
  表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
• key_len的计算规则如下：
  • 字符串类型
    字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4
    char(n)：n*字符集长度
    varchar(n)：n * 字符集长度 + 2字节
  • 数值类型
    TINYINT：1个字节
    SMALLINT：2个字节
    MEDIUMINT：3个字节
    INT、FLOAT：4个字节
    BIGINT、DOUBLE：8个字节
  • 时间类型
    DATE：3个字节
    TIMESTAMP：4个字节
    DATETIME：8个字节
  • 字段属性
    NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。
• Extra
  Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
  • Using where
    表示查询需要通过索引回表查询数据。
  • Using index
    表示查询需要通过索引，索引就可以满足所需数据。
  • Using filesort
    表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
  • Using temprorary
    查询使用到了临时表，一般出现于去重、分组等操作。

回表查询

覆盖索引

最左前缀原则

LIKE查询

NULL查询

索引与排序

查询优化

慢查询定位

慢查询优化

分页查询优化