索引重建
什么时候需要重建索引
- 索引基数（索引基数：以性别为例索引基数为2）远小于总行数，则很有可能发生了严重的索引倾斜，应该考虑重建索引。
- 查询index_stats表中的索引的深度、已删除叶子结点与叶子结点总数的比值。如果height>=4或已经删除叶子结点的数量与叶子结点总数的比值>0.2则型该考虑重建索引。
- 表上频繁地发生了增删改操作，则会导致索引结构的空洞和碎片化，大大降低索引的效率。当发生delete操作，会在索引页中释放空间，但是并没有回收这些空间，这些空间将会留着给将来更新数据或者新增数据的时候使用，这些未被回收的空间在物理地址上可能并不是相邻的，因此很容易成为内部碎片，这就是索引空洞。当发生新增或者更新操作时，MySQL会根据索引值找到需要操作的页，当页内空间不足时会造成页分裂，新的页可能在物理地址上与旧的页并不相邻，这就造成了页的碎片化，在范围查找时顺序IO就有可能退化成随机IO，大大降低了索引的性能。
- Oracle数据库当表上发生了alter table move，由于move操作会导致ROWID发生变化，也需要进行索引的重构。
如何重建索引
- 先drop原索引，再创建新索引。（比较费事）
- Oracle可以直接重建索引（rebuild/ rebuild online），进行rebuild操作时，需要检查表空间是否足够，rebuild online不会阻塞DML操作，他会复制一份新索引，并暂时保留旧索引，并产生一个临时的日志记录DML操作，在新索引维护完成以后，再将日志中的操作维护到索引中去，然后Drop旧索引。为了防止崩溃，每一步对索引的更改都会记录在Redolog日志里，rebuild操作将会导致产生大量的redolog日志。
索引类型的选择
为什么选择B+树索引
- B+树索引可以采用较低的深度存储大量的数据。在B+树索引结构中，行数据只存在于叶子结点对应的页中，一个页的大小为16KB，假设一条数据、一个目录项所占用的空间均为16字节，则一个数据页中可以存1024条数据。数据页存满1024条数据时，会发生页分裂，同时根节点变为目录项页。同理，当目录项页的1024个目录项存满，将会发生页分裂，同时根节点变为目录项页的目录项页，此目录项页仍然可以存放1024个目录项。那么，以这个阶数为3的B+树为例，它总共可以存储的数据条数为102410241024 > 10亿条数据。
- 一个阶数为3的B+树，当查询其中的数据时，只需要3次IO，大大降低了查询时磁盘的IO次数。因为B+树的底层在进行磁盘IO操作的时候，采用的是页加载的机制，而页加载机制又是基于局部性原理提出的，页加载机制利用局部性原理大大降低了磁盘的IO次数。局部性原理分为空间局部性和时间局部性，时间局部性是指一个数据如果在当前被访问，那么在接下来的时间里它再次被访问的概率远大于其他数据。空间局部性是指，当一个数据在当前被访问，那么物理地址与它相邻的其他数据在接下来被访问的概率也会大大增加。B+树在将磁盘中的数据加载到内存中时是以页为单位，而不是以数据行为单位进行加载的，加载后会驻留一段时间，这很好的利用了局部性原理的思想，大大减少了磁盘的IO次数。以阶数为3的B+树为例，在进行查询时，首先会加载根目录页到内存中，对根目录页中的目录项进行二分查找，找到目录项页所在的页，然后将目录项页加载到内存中，最后对目录项页进行二分查找，找到叶子结点所在的页号，将该叶子结点对应的数据页加载到内存中，然后对数据页中的数据进行二分查找，即可找到所需查找的数据行。因此这个过程只需要3次加载的操作，也就对应着3次IO。
- B+树结构的索引在进行排序、范围查找时，性能优于其他的索引。因为B+树的所有数据行都存储在叶子结点中，数据行之间按索引值从小到大的顺序用单向链表进行连接。 叶子结点之间，也就是数据页之间，按照索引值从小到大的顺序用双向链表进行连接。因此B+树索引的数据行天然的有序性决定了其在进行排序和范围查找时，性能优于其他的索引。
B+树和B树有什么区别
B树和B+树最大的区别就在于非叶子节点是否存储数据行。B+树的所有数据行都存储在叶子结点中，非叶子结点用于存放目录项。而B树的飞叶子结点中是存储了一些的数据行的。B+树的叶子结点的数据行之间以单向链表从小到大进行连接，数据页之间以双向链表按从小到大的顺序进行连接，当进行范围查找和排序操作时，由于其数据全部存在叶子结点中，并且是严格有序的，只需要对叶子结点层的有序的列表进行遍历即可，性能将会非常高。虽然B树的叶子结点中的数据也是从小到大严格有序排列的，但是有一些数据存在于非叶子结点中，因此在进行范围查找和排序时候，仅仅对叶子结点层的有序的列表进行遍历无法实现，逻辑将会相较于B+树更为复杂。
B树相对于B+树有其独特的优势吗
在以下的场景下，B树的性能可能会优于B+树：
- 数据较少，树的深度低。因为B+树只有叶子结点存放数据，因此每次查询直到遍历到叶子结点才可以将行数据取出，而B树的非叶子结点中是存了一些数据的，因此数据较少，树的深度低情境下，B树在进行数据查询的时候，可能在遍历到非叶子节点时就已经命中了。
- 在单个数据查找时而不是范围查找时，B树的性能也很强。
Hash索引
InnoDB和MyISam存储引擎都不支持HASH索引，但是Memory存储引擎是支持HASH索引的。
注：Memory存储引擎是用内存存储数据的，而不是磁盘。
hash索引的原理
每一个行数据在进行插入前，都会调用HASH函数，计算出其索引列的HASH值，根据这个HASH值进行插入对应的buket。当进行数据查询的时候，也是先计算出其索引列的HASH值，然后根据这个hash值直接取出对应的行数据。
hash索引的优缺点
优点：
- hash索引的原理决定了它在单个数据的精确查找时候，效率是非常高的。
缺点：
- hash索引不支持范围查找，因为索引值相近的数据计算出的hash值并不相近。是散列存放的。
- hash索引在联合索引时，不再遵循最左前缀原则，只能使用完整的联合索引。因为对于联合索引的hash值，是基于完整的联合索引进行计算得到的。
- hash索引不支持排序操作，因为hash索引存储数据行的方式本身就是无序的。
InnoDB的存储结构
InnoDB的数据存储结构主要包括：页、区、段、碎片区、表空间。
- 数据行和目录项都是存储在页中。区内的页之间的存储空间是连续分配的，这是为了提升读写性能，让顺序IO代替随机IO。
- 一个区包含64个页，一个页的大小为16KB，因此一个区的大小为1MB。
- 在InnoDB存储引擎中，一个段中可以有多个区，但是一个表空间中有多个段，索引段（非叶子节点段）、数据段（叶子结点段）、回滚段（用于事务的回滚）。
- 当数据量较小时，InnoDB存储引擎不会直接进行区的分配，而是采用分配碎片区的方式，碎片区中的页可以为不同的段服务，碎片区中的页之间存储空间也不一定连续，碎片区从属于表空间。当数据量上升，达到一定的阈值，InnoDB存储引擎就不再分配碎片区，转为分配区。
select in中使用索引需要注意的问题
- 字段如果是字符类型，则in中需要加单引号，防止发生隐式类型转换导致索引失效。
- 如果要用到联合索引，需要满足最左前缀原则，例如：
索引：(name, age, score)
select * from student where name in ('Tom', 'Lily') and age = 18;
遵循了最左前缀原则，索引生效。
模糊查询如何使用索引
在模糊查询中，如果使用通配符作为模糊匹配的字符串的的开头，会使索引失效。可以采用冗余列＋倒序文本的方式，对冗余列建立索引，实现模糊匹配。
例如： mobile like ‘%8765’会使索引失效。
可以采用如下方案：
如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可
