前言
在 MySQL 官方提到，改善操作性能的最佳方法 SELECT 在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。
尽管可能会为查询中使用的每个可能的列创建索引，但不必要的索引会浪费空间和时间，使MySQL难以确定要使用的索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，才能使用最佳索引集来实现快速查询。
那么，索引到底是什么？透过现象看本质：
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。
另外，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。
以及，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
为了更深入理解索引的本质，这里我们先了解一下磁盘相关知识。
外存储器-磁盘
计算机一般有两种存储的方式：内存储器(main memory)和外存储器(external memory)
- 内存:读写速度非常快，但是容量很小，而且造价非常贵，在不通电的情况下会数据会丢失，不能长期存储数据；
- 外存：磁盘是相对常见的外存储设备，它是以存取时间变化不大为特征的。可以直接存取任何字符组，且容量大、速度较其它外存设备更快。
磁盘的构造
磁盘是一个扁平的圆盘(与电唱机的唱片类似)。盘面上有许多称为磁道的圆圈，数据就记录在这些磁道上。磁盘可以是单片的，也可以是由若干盘片组成的盘组，每一盘片上有两个面。
当磁盘驱动器执行读/写功能时。盘片装在一个主轴上，并绕主轴高速旋转，当磁道在读/写头(又叫磁头) 下通过时，就可以进行数据的读/写了。
一般磁盘分为固定头盘(磁头固定)和活动头盘。
固定头盘的每一个磁道上都有独立的磁头，它是固定不动的，专门负责这一磁道上数据的读/写。
活动头盘的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道，所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的(行动整齐划一)，当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体，各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面。因此，柱面的个数也就是盘面上的磁道数。
磁盘的读/写原理和效率
磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、块号(磁道上的盘块)。
读/写磁盘上某一指定数据需要下面3个步骤：
- 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。
- 如上图6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)，这时根据盘面号来确定指定盘面上的磁道。
- 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。
经过上面三个步骤，指定数据的存储位置就被找到，这时就可以开始读/写操作了。
访问某一具体信息，由3部分时间组成：
- 查找时间(seek time) Ts: 完成上述步骤(1)所需要的时间。这部分时间代价最高，最大可达到0.1s左右；
- 等待时间(latency time) Tl: 完成上述步骤(3)所需要的时间。由于盘片绕主轴旋转速度很快，一般为7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有5400rpm(笔记本)、7200rpm几种)，因此一般旋转一圈大约0.0083s；
- 传输时间(transmission time) Tt: 数据通过系统总线传送到内存的时间，一般传输一个字节(byte)大概0.02us=2*10^(-8)s。
寻道时间Ts : Ts=m∗n+s
n : 跨越n条磁道的时间； s： 启动磁臂的时间，约为2ms ； m：与磁盘驱动器速度有关的常数，约为0.2ms。
延迟时间Tr : Tr=1/（2∗r）
r ： 磁盘的旋转速度
传输时间Tt : Tt=b/（r∗N）
r ： 磁盘的旋转速度； N：为一个磁道上的字节数；b：每次所读/写的字节数b
总平均存取时间 : Ta=Ts+Tr+Tt
磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上，因此我们应该尽量将相关信息存放在同一盘块，同一磁道中，或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间Ts。
所以，在大规模数据存储方面，大量数据存储在外存磁盘中，而在外存磁盘中读取/写入块(block)中某数据时，首先需要定位到磁盘中的某块，如何有效地查找磁盘中的数据，需要一种合理高效的外存数据结构。
索引的本质
索引是帮助MySQL高效获取数据的排好序的数据结构
索引数据结构，主要包含以下几类，我们来对比下
- 二叉树
- 平衡二叉树
- 红黑树
- Hash表
- B-Tree
二叉树
定义：每个结点最多有两个子树，左子树比父节点小，右子树比父节点大。
缺点：会出现极端情况导致整棵树只有左子树或只有右子树。
平衡二叉树（AVL Tree）
定义：平衡二叉树又称AVL树，是一种特殊的二叉查找树，其左右子数都是平衡二叉树，且左右子树高度差的绝对值不超过1。
缺点：AVL树是高度平衡的，频繁的插入和删除，会引起频繁的rebalance，导致效率下降。
红黑树
定义：
- 性质1. 节点是红色或黑色。
- 性质2. 根节点是黑色。
- 性质3 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
- 性质4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
缺点：数据量大会导致树层数比较多，这样就会造成查找数据慢。
Hash数据结构
定义：散列表（Hash table，也叫哈希表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。 对目标值进行hash运算得到hash值和数据磁盘指针地址保存到hash表，这样就达到快速定位数据位置。
缺点：精确查找十分快速，但范围查找就碰壁了。
BTree
定义：
- 一个节点可以存储多个数据，这样可以避免黑红树的缺点，树的层数很变小；
- 叶节点具有相同的深度，叶节点的指针为空；
- 所有索引元素不重复；
- 节点中的数据索引从左到右递增排列。
缺点：节点里面数组数据：每个数据的结构=索引数据+数据记录（即叶子节点存储键值和数据记录）。
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
模拟查找关键字29的过程：
- 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
- 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
- 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
- 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
- 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
- 在磁盘块8中的关键字列表中找到关键字29。
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
B+Tree
定义：B+Tree是在B-Tree基础上的一种优化。节点里面数组数据：每个数据只存储键信息，这样不存数据可以腾出空间放更多的键信息，让树层数越小
- 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的性能
将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值。（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
数据库中的B+Tree索引可以分为聚集索引（clustered index）和 辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。
辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
查看mysql文件页大小（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;
MySQL存储引擎
什么是存储引擎？
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
- InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。
- MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。
- MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。
MySQL 5.7 支持的存储引擎
MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。
可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如图所示。
如何选择 MySQL 存储引擎
不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。
可以根据以下的原则来选择 MySQL 存储引擎：
- 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
- 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
- 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
- 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。
提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。
使用下面的语句可以修改数据库临时的默认存储引擎 SET default_storage_engine=< 存储引擎名 > 注意：将 MySQL 数据库的临时默认存储引擎修改为 其他的存储引擎时 ，但是当再次重启客户端时，默认存储引擎仍然是 InnoDB。
MyISAM存储引擎
数据存储形式
MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中.frm 、.MYD、.MYI。
- .frm : 存储表结构
- .MYD ： 存储表数据
- .MYI ：存储表索引
锁的粒度
MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。
事务
MyISAM不支持事务。
数据的存储特点
MyISAM是基于非聚簇索引进行存储的。
索引实现
MyISAM索引文件和数据文件是分离的(非聚集)
其他
MyISAM提供了大量的特性，包括全文索引，压缩，空间函数，延迟更新索引键等。
- 进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。
- 全文索引，是一种基于分词创建的索引，可以支持复杂的查询。
- 延迟更新索引键，不会将更新的索引数据立即写入到磁盘，而是会写到内存中的缓冲区中，只有在清除缓冲区时候才会将对应的索引写入磁盘，这种方式大大提升了写入性能。
InnoDB存储引擎
数据存储形式
使用InnoDB时，会将数据表分为.frm 和 .ibd 两个文件进行存储。
- .frm : 存储表结构
- .ibd : 存储表数据和索引
innodb的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。
锁的粒度
InnoDB采用**MVCC(多版本并发控制)**来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁。【MVCC在稍后会进行介绍】
事务
InnoDB是典型的事务型存储引擎，并且通过一些机制和工具，支持真正的热备份。
数据的存储特点
InnoDB表是基于聚簇索引建立的，聚簇索引对主键的查询有很高的性能，不过他的二级索引（非主键索引）必须包含主键列，索引其他的索引会很大。
索引实现
InnoDB索引实现(聚簇)
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚簇索引-叶节点包含了完整的数据记录
- 为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
- 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
主键索引
辅助索引