MySQL--索引
索引
1. 索引是什么？有什么作用？使用索引的优缺点？
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash索引。
优点：
- 使用索引可以大大加快数据的检索速度。
- 通过唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
- 创建索引和维护索引需要耗费很多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要物理文件存储，也会耗费一定的空间。
2. MySQL索引底层的实现原理？
常见的索引结构有Hash索引、B- Tree 索引和B+ Tree索引。MySQL索引底层采用的数据结构是B+ Tree。为什么不使用Hash索引和B- Tree索引呢？
首先我们我们看一下Hash索引：
哈希索引是基于哈希表实现的，哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。 为何能够通过 key 快速取出 value呢？原因在于哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 value 对应的index，找到了index 也就找到了对应的value。哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。
hash = hashfunc(key)
index = hash % array_size
既然哈希表能够进行快速索引，为什么MySQL没有把它当做数据结构呢？主要有以下两个原因：
- 哈希冲突问题；
- 哈希索引不支持顺序和范围查询，如果要对表中的数据进行排序或者进行范围查询，那Hash索引就不行了。
接着我们看看B- Tree索引（MongoDB中索引的实现原理）：B树指的是多路平衡查找树，它的形式如下;
B树的特点如下：
1. 多路，非二叉树
2. 每个节点既保存索引，又保存数据
3. 搜索时相当于二分查找
接着我们看看B+ Tree索引： B+树是B树的变种。
B+ 树的特点如下：
1. 多路，非二叉树
2. 只有叶子节点保存数据
3. 搜索时相当于二分查找
4. 增加了相邻节点的指向指针
B-树和B+树的异同：
B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
B树每个节点即保存数据又保存索引，所以访问磁盘IO的次数多，B+树只有叶子节点保存，访问磁盘IO数量少，并且区间访问比较好。
MongoDB使用B树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql。而Mysql作为一个关系型数据库，数据的关联性是非常强的，区间访问是常见的一种情况，B+树由于数据全部存储在叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。
3. 常见的索引类型？
3.1 主键索引
数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。在 MySQL的InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个6Byte的自增主键。
为什么要使用自增ID作为主键索引？
如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。数据记录本身被存于主键索引的叶子结点上，这就要求同一个叶子结点内的各条数据记录按主键顺序存放。每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16）,则开辟一个新的页。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开辟一个新的页。如果使用非自增的主键（比如身份证、学号等），由于每次插入主键的值近似于随机插入，因此每次新记录就会被插到现有索引页的中间某个位置，此时MySQL就会移动数据，造成频繁的页分裂和页旋转，使得插入速度比较慢。
因此，InnoDB的主键应该尽量使用整型自增的ID。这样能够在存储和查询上提高效率。
3.2 二级索引（辅助索引）
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。
唯一索引：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
前缀索引：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
全文索引：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
3.3 聚集索引
聚集索引：即索引结构和数据一起存放的索引。主键索引属于聚集索引。在 Mysql中，InnoDB 引擎的表的.ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
优点：
- 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
缺点：
- 依赖于有序的数据：因为B+树是多路平衡树，如果索引不是有序的，那么就需要在插入时排序，如果数据是整型还好，如果是字符串或者UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
- 更新代价大：修改数据时，对应的索引也可能会被修改，所以对于主键索引来说，主键一般都是不可被修改的。
3.4 非聚集索引
非聚集索引：即索引结构和数据分开存放的索引。MYISAM引擎的表的.MYI文件包含了表的索引， 该表的索引(B+树)的每个叶子非叶子节点存储索引，叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
优点：更新代价比聚集索引要小。
缺点：也依赖于有序的数据；可能会二次查询（回表），当查到索引对应的指针和主键后，可能还需要根据指针和主键在回到数据文件或表中查询。
3.5 覆盖索引
覆盖索引：需要查询的字段正好是索引字段，那么直接根据该索引就能查到数据了，而无需回表查询。如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。在如普通索引，如果一条SQL需要查询name，name字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。
3.6 创建索引的注意事项？
1. 选择合适的字段创建索引：
不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
2. 被频繁更新的字段应该慎重建立索引：
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
3. 尽可能的考虑建立联合索引而不是单列索引：
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4. 注意避免冗余索引：
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
5. 考虑在字符串类型的字段上使用前缀索引代替普通索引：
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
3.7 如何为表添加索引？
1.添加PRIMARY KEY（主键索引）： ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) ： ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) ： ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) ： ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引： ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
