MySql索引概述

1 架构体系

2 存储引擎

MySql存储引擎

MySQL存储引擎是一个封装了数据的存储和读取的模块，负责在物理上如何表示表里的数据记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上。

为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

向下与管理器联系，管理数据库文件系统；向上为MySQL server 层提供统一的调用接口（也就是存储引擎API）， 通过API接口屏蔽了不同存储引擎之间的差异。

MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。

MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，注意：存储引擎是基于表的。

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。

MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，也就是说存储引擎是基于表的。

存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。其它数据库系统 (包括大多数商业选择)仅支持一种类型的数据存储， 也就是说采用“ 一个尺码满足一切需求 ”的存储方式，也意味着“功能强大，性能平庸”。而MySQL默认配置了许多不同的存储引擎，你可以根据业务需求选取一种最适配最高效的存储引擎。这也是为什么MySQL为何如此受欢迎的主要原因之一。

常见的存储引擎

查看当前安装的MySQL版本支持的存储引擎

-- 查看MySQL版本
select version();

-- 查看版本支持的存储引擎
show engines;

• MyISAM： 拥有较高的插入，查询速度，但不支持事务
• InnoDB ：5.5.8版本后Mysql的默认数据库引擎，支持ACID事务，支持行级锁定

• Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (note 1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes (note 2)	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes (note 3)	Yes (note 3)	Yes (note 4)	Yes (note 3)	Yes (note 3)
Foreign key support	No	No	Yes	No	Yes (note 5)
Full-text search indexes	Yes	No	Yes (note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes (note 7)	No	No
Hash indexes	No	Yes	No (note 8)	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support (note 1)	Yes	Limited (note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

每个存储引擎使用场景不一样，所以在以下特性的选择也不一样。

• 并发性：不同应用对锁的细粒度要求不一样，选择正确的锁定策略可以减少开销，从而提高整体性能。其中还包括对多版本并发控制或“快照” 读取等功能。

• 事务支持：并非每个应用程序都需要事务，但对于那些需要事务的应用程序，有非常明确的要求，例如 ACID 合规性等等。

• 引用完整性：服务器通过 DDL 定义的外键强制让关系数据库引用完整性。

• 物理存储：这涉及方方面面，从表和索引的页大小，到存储数据的格式，再到物理磁盘。

• 索引支持：每一个应用场景往往都有适合自己的索引策略。每个存储引擎通常都有自己的索引方法，也有像 B-tree 索引这种对几乎所有引擎都是通用的方法。

• 内存缓存：不同的应用程序对某些内存缓存策略的响应比其他的更好，因此尽管某些内存缓存对所有存储引擎都是通用的（例如用于用户连接的那些或 MySQL 的高速查询缓存），但其他的只有当一个特定的存储引擎正在发挥作用。

• 性能助手：这包括用于并行操作、线程并发、checkpoint技术、批量插入处理等的多个 I/O 线程。

• 其他目标功能：这可能包括对地理空间操作的支持、某些数据操作操作的安全限制以及其他类似功能。

这正是可插拔存储引擎设计的出发点，可以根据需求组合成特定的插件，当然有些特性是互斥的，或者说追求某种特性会引人相应的成本。

InnoDB可以在众多存储引擎中暂时脱颖而出，是因为其在大部分业务场景中各项特性都较良好且均衡。现在我们在网上看的很多讲mysql底层的文章，其他大部分讲的是InnoDB的底层实现。InnoDB有点像虚拟机中的Hotspot。

Java虚拟机规范	HotSpot
MySql	InnoDB

将规范和实现分开而带来的成功和长久的发展的例子应该还有不少。

InnoDB是一款平衡高可靠和高性能的通用存储引擎。

主要优点：

• 它的DML操作支持ACID事务，具备提交、回滚和保护用户数据的崩溃恢复能力。

• 行级锁提高了多用户并发数和性能。

• 表基于主键排列在磁盘上提高查询效率，每个表都通过主键创建聚合索引来组织数据，从而减少基于主键查询的I/O开销。

• 为了保证数据的整体性，InnoDB支持外键约束。使用外键检查插入、更新和删除以确保它们不会导致相关表之间的不一致。

3 InnoDB索引结构

索引常用的数据结构有：Hash、二叉搜索树、红黑树、B树以及B+树、LSM树、倒排索引等。

Hash索引索引自身只需存储对应的哈希值，索引的结构十分紧凑，这也让Hash索引查找的速度非常快。Hash索引的应用非常广泛，在 MySql 中，Memory引擎显式支持Hash索引。但是Hash索引容易出现Hash碰撞，在数据量很大的情况下，内存无法加载全部的数据索引。

二叉查找树的任意一个节点，其左子树的每个节点的值都要小于这个节点的值，而右子树节点的值都应大于这个节点的值。这种结构有利于快速查找一个数据。

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。解决了二叉查找树发生倾斜而造成查询效率减低的问题，但同时带来了维持平衡的成本，在增加和删除节点时要通过旋转来保持树的平衡。树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下。

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。当数据非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中，内存访问的时间比磁盘快一个等级。所以减少磁盘IO次数能够增加索引速度，B树多路的好处就是可以将多个关键字组成一个节点，有效的减低了B树的高度，每一个节点确定的范围更广也更精确，增加了缩小索引范围的速度。

页是mysql中磁盘和内存交换的基本单位，也是mysql管理存储空间的基本单位。同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改，需要注意的是不同的页大小最终也会导致区大小的不同。InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时， InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死，InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB
内容刷新到磁盘中。

3.1 B+Tree索引

B+树是由二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree）逐步优化而来。

B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。 B+ 树元素自底向上插入。

B+树的主要特征：

1，索引关键字从左到右递增排序，非叶子结点关键字比其左边的指针指向的子节点都大，比其右边的关键字都小。
2，B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
3，B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数,都一样；

4，B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。 非叶子节点的子节点数=关键字数。

B+树相比B树的优势：

1，每个节点存储的关键字更多，树的高度更低，查询时磁盘IO次数更少。

2，所有查询都必须找到叶子节点，查询性能更稳定。

3，所有叶子节点形成有序双链表，便于范围查找。

3.1.1 聚集索引和非聚集索引

InnoDB和MyISAM存储引擎都采用B+Tree作为索引，他们一个很大的区别是InnoDB的数据也是索引的一部分，存在B+tree的叶子节点上，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。聚簇索引和非聚簇索引本质的区别就是B+树的叶子节点上存储的是行数据还是行数据的地址(行号)。

这是聚集索引和非聚集索引的一种含义。在文件系统层面可以很直观的看到，

InnoDB存储引擎表的文件格式：

*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
*.ibd：InnoDB DATA，表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

MyISAM表的文件格式：

*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
*.MYD：MyISAM DATA，用于存储MyISAM表的数据
*.MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息

InnoDB		MyISAM
*.ibd		*.MYD，*.MYI
聚集索引	非聚集索引（辅助索引）	非聚集索引
主键索引

InnoDB中的每一张表都有且只有一个聚集索引，聚集索引和主键索引（PRIMARY KEY )紧密联系。如果一张表定义了主键索引，则用主键索引作为聚集索引；如果没有定义主键索引，则选第一个非空的唯一索引（Unique Index）作为聚集索引；如果连唯一索引（Unique Index）都没有的话，InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键，形成聚集索引。一个表中有多少索引就会建立多少棵 B+ 树。在InnoDB里，聚集索引以外的为辅助索引，也叫非聚集索引，辅助索引的叶子节点存的不是数据，而是主键，通过辅助索引查找时先查找到主键，然后用主键去聚集索引里查找数据。这是聚集索引和非聚集索引的第二种含义。

3.1.2 回表和索引覆盖

上面说的辅助索引先定位主键值，再在聚集索引里定位行记录，就是回表，因为要进行两次B+树查找，所以性能降低了。

CREATE TABLE user(
        id INT NOT NULL auto_increment,
        name VARCHAR(100) NOT NULL,
        age INT NOT NULL,
        city VARCHAR(100) NOT NULL,
        PRIMARY KEY (id),
        KEY idx_name (name)
);

SELECT * FROM user WHERE name = 'Jack' ;
该sql首先会在辅助索引里找到name为Jack的记录，因为select * 是要查询所有的字段，而idx_name索引里只有id和name两个字段，所以还要用找到的id去聚集索引里查出完整的数据。

索引覆盖就是为了解决这种问题，他从辅助索引中就可以查找到记录，而不用再查找一遍聚集索引。

SELECT id,name FROM user WHERE name = 'Jack' ;

因为id和name字段在辅助索引里已经有了，不需要再通过聚集索引查找，这样省掉了回表的性能损耗。

3.1.3 联合索引和最左前缀原则

联合索引就是由多个列组成的一个索引，必须遵守最左前缀原则。

CREATE TABLE user(
        id INT NOT NULL auto_increment,
        name VARCHAR(100) NOT NULL,
        age INT NOT NULL,
        city VARCHAR(100) NOT NULL,
        PRIMARY KEY (id),
        KEY idx_name_age_city (name,age,city)
);

联合索引idx_name_age_city 在B+树中的排序方式为：

先按照 name 列的值进行排序。
如果 name 列的值相同，则按照 age列的值进行排序。
如果 age列的值也相同，则按照 city的值进行排序。