MySQL索引

索引是什么

1. 索引是帮助MySQL高效获取数据的数据结构
2. 索引存储在文件系统中
3. 索引的文件存储形式与存储引擎有关
4. 索引文件的结构：hash、二叉树、B树、B+树

索引的优缺点

优点：

1. 大大加快数据的查询速度
2. 唯一索引可以保证数据库表每一行的唯一性
3. 加速表连接时间

缺点：

1. 创建、维护索引要耗费时间，所以，索引数量不能过多
2. 索引是一种数据结构，会占据磁盘空间
3. 对表进行更新操作时，索引也要动态维护，降低了维护速度

hash表的索引格式缺点：利用hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间；如果所有的查询都是等值查询，hash很快，但是企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此hash就不太合适了。

二叉树和红黑树缺点：都会因为树的深度过深而造成io次数变多，影响数据读取的效率。

B树特点：

1. 所有的键值分布在整棵树中
2. 搜索有可能在非叶子结点结束，在关键字全集内做一次查找，性能逼近二分查找
3. 每个节点最多拥有m个子树
4. 根节点至少有2个子树
5. 分支节点至少拥有m/2课子树（除根节点和叶子节点外都是分支节点）
6. 所有叶子节点都在同一层、每个节点最多可以有m-1个可以，并且以升序排序

B树实例图：

说明：

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子根节点的指针，指针存储的是子节点所在的磁盘块的地址，两个关键字划分成的三个范围域对应三个指针指向的数据的范围域。以根节点为例，关键字为16和34，p1指针指向的子树的数据范围小于16，p2指针指向的子树的数据范围为16~34，p3指针指向的子树的数据范围大于34.

查找过程：

1. 根据根节点找到磁盘块1，读入内存。[磁盘I/O操作第1次]
2. 比较关键字29在区间(16,34)，找到磁盘块1的指针p2。
3. 根据p2指针找到磁盘块3，读入内存。[磁盘I/O操作第2次]
4. 比较关键字29在区间(25,31)，找到磁盘块3的指针p2。
5. 根据p2指针找到磁盘块8，读入内存。[磁盘I/O操作第3次]
6. 在磁盘块8中的关键字列表中找到关键字29。

缺点：

1. 每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数量变小
2. 当存储的数据量很大的时候会导致深度较大，增大查询磁盘IO次数，进而影响查询性能。

MySQL索引数据结构

B+Tree：

B+树是在B树的基础之上做的一种优化：

1. B+Tree每个节点可以包含更多的节点：1.为了降低树的高度 2.将数据范围变为多个区间，区间越多，数据检索越快。
2. 非叶子节点存储key，叶子节点存储key和数据
3. 叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高。

B+Tree实例图：


B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此B+树进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

索引的分类

mysql索引的五种类型：主键索引、唯一索引、普通索引、全文索引、组合索引。通过给字段添加索引以提高数据的读取速度，提高项目的并发能力和抗压能力。

主键索引：主键是一种唯一性索引，但他必须指定为PRIMARY KEY，每个表只能有一个主键。

唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空。

普通索引：基本的索引类型，值可以为空，没有唯一性的限制。

全文索引：全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建

组合索引：多列值组成一个索引，专门用于组合搜索

MySQL存储引擎（常用）

MyISAM

使用MyISAM存储引擎会在磁盘上存储成三个文件。

1. frm文件：存储表的定义数据
2. MYD文件：存放表具体记录的数据
3. MYI文件：存储索引

frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。MyISAM存储引擎通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。

支持数据的类型：

1. 静态固定长度表：存储速度快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。默认的存储格式。
2. 动态可变长表：节省空间，但出错后恢复起来比较麻烦。
3. 压缩表：在数据文件发生错误时，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

不支持事务，存储速度更快，如果读写操作允许有错误数据，只追求速度，可以选择MyISAM引擎。

InnoDB

默认的数据库存储引擎。

主要特点：

1. 可以通过自动增长列，方法是auto_increment。
2. 支持事务。默认的事务隔离级别为可重复读，通过MVCC来实现。
3. 使用的锁粒度为行级锁，可以支持更高的并发。
4. 支持外键约束；外键约束会降低表的查询速度，但是增加了表之间的耦合度。
5. InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。
6. 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+树的叶子节点上。

InnoDB的存储表和索引的形式：

1. 使用共享表空间存储：所有的表和索引存放在同一表空间中。
2. 使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，使用分区表可以提升查询效率。

InnoDB最大的特点就是支持事务，但是会降低效率。

Memory

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

1. 支持的数据类型有限制，不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，varchar会被自动存储为char类型。
2. 支持的锁粒度为表级锁。访问量较大时，表级锁会成为Memory存储引擎的瓶颈。
3. 由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失。
4. 查询的时候，如果有用到临时表，而且临时表中有BLOB、TEXT类型的字段，那么这个临时表会转化为MyISAM类型的表，性能急剧降低。
5. 默认使用hash索引。
6. 如果内部表很大，会转化为磁盘表。

MyISAM与InnoDB区别

1. InnoDB支持事务，MyISAM不支持事务
2. InnoDB支持外键，MyISAM不支持
3. InnoDB是聚簇索引，MyISAM是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此InnoDB必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后在通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也会很大。MyISAM是非聚簇索引，数据文件是分离得到，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，执行select count(*) from table是需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述的语句时只需要读出该变量即可，速度很快；
5. InnoDB最小的锁粒度是行级锁，MyISAM最小的锁粒度是表级锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

如何选择

1. 是否要支持事务，需要选择InnoDB，不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是查询，可以考虑MyISAM，如果读写也频繁使用InnoDB。
3. 系统崩溃后，MyISAM恢复起来更困难，如果不能接受就选择InnoDB

回表查询

先通过索引的值定位聚簇索引值，在通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，性能较扫一遍索引树更低。

覆盖索引

只需要在一棵索引树就能获取SQL所需的所有数据，无需回表，速度更快。

索引原则

索引不是越多越好

不要对经常变动的数据添加索引

小数据量的表不需要添加索引

索引一般加在常用来查询的字段上