【mysql】--mysql的索引

索引：让服务器快速地定位到表的指定位置。
索引的三大优点：
索引大大减少了服务器需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变成顺序I/O。

一、索引方式

Mysql目前主要索引方式：hash、Btree、Rtree、fullText

1.1、Hash

Hash索引包含哈希值和数据行指针信息记录，保存在hash表中。
检索效率高且比较精准。

弊端：
Hash索引仅仅满足=、in查询，不能使用范围查询【hash运算后的hash值，只能进行等值过滤，不能基于范围过滤】；
Hash索引无法被用来避免数据的排序操作【hash值大小和之前键值无关，所以不适合做排序】；
Hash索引不能利用部分索引键查询【组合索引如果是Hash方式，是将整个一起进行hash，那么无法使用“最左原则”】；
Hash索引在任何时候都不能避免表扫描【hash值和所对应的行指针信息在一个Hash表中，不同索引键会存在相同Hash值，所以找到对应“桶”，还是需要访问“行指针”去进行实际数据比较，得到相应的结果】；
Hash索引遇到大量Hash值相等后，性能并不一定会比B+树索引高。

1.2、Btree

1.2.1、B-树

在非叶子节点中也可以存储数据；
叶子节点相邻的没“左右链指针”；

1.2.2、B+树

所有关键字只会出现在叶子节点的链表中，且链表的关键字是有序的；
非叶子节点相当于叶子节点的索引，叶子节点相当于存储数据的数据层【非叶子节点存储的是指向叶子节点的索引】；
非叶子节点不会存储数据；
叶子节点是类似双向链表结构，左右有链指针【适合范围查询】；

1.3、Rtree

主要用来解决空间数据检索的问题，常用于MyISAM存储引擎中。

1.4、fullText

Full-text索引在MySQL中，仅有MyISAM存储引擎支持它，且只有数据类型为CHAR、VARCHAR、TEXT的列可以创建Full-txt索引。

二、索引方式的选择对比

问题1：InnoDB为什么不用二叉树/红黑树或二叉排序树？
二叉树相当于一个二分查找。极端下是一个线性链表结构，这样的情况下，相当于全表扫描。
二叉搜索树/二叉平衡树的子节点高度差不超过1，能解决线性链表问题。二叉平衡树是左右两个节点，在数据量大时，会有很大的树高度。树的查询时间和树的高度有关，树有多高就需要检索几次，每个节点读取，都对应一次磁盘的IO操作【B+树可以降低树的高度，提高查找效率】。另外，不支持范围查询，因为范围查询需要从根节点多次遍历，效率低。
因此，考虑多路平衡二叉树来解决上面的问题，即B+树。

问题2：既然增加树的路数可以降低树的高度，那么无限增加树的路数是不是可以有效的查找？
这样会变成有序数组，索引在磁盘上，如果数据量大的话，不一定能一次性加载到内存。对于B+树，可以每次加载B+树的一个节点，然后一步一步往下找。

问题3：InnoDB为什么不选择B树？
B树的特点：
(1)、B树是一个节点有多个分叉，降低了树高度；
(2)、所有节点都存储数据，包含了键值和数据，但是会按照键值排序；
(3)、父节点的元素不会出现在子节点；
(4)、所有叶子节点位于同一层，叶子点具有相同的深度，叶子节点没指针相连；
B树的缺点：
(1)、不支持范围查询的快速查找，需要回到根节点重新遍历，随机IO性能差、成本高；

问题4：InnoDB存储引擎选择B+树作为底层数据结构？
B+树和B树区别：非叶子节点是否存储数据、父节点会出现在叶子节点、叶子节点间有指针相连【双向有序链表】。

B+树的特点：
(1)、只有叶子节点存储数据，非叶子节点存储的是键值；
(2)、只有叶子节点存储数据；
(3)、叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表；
对于B+树，其叶子节点是通过指针依次按顺序连接【叶子节点有链表相连】，在范围查询时只是在多个叶子节点间进行跳转，不需要返回根节点或支节点，所以能节省大量I/O时间。

例如：下面分析【>4，<9】例子。

对于B树，加载根节点所在的页，发现大于4的元素6；
在加载左子节点所在的页，遍历得到元素5；
重新加载根节点，在遍历右子节点所在的页，遍历页面的数据找到7和8；

对于B+树，找到元素5，直接利用B+树的叶子节点是通过指针依次按顺序连接特性，在叶子节点上进行查询，不需要返回根节点或支节点进行遍历。

三、innoDB索引类型

innoDB主要拥有索引类型如下：主键索引、唯一索引、普通索引、组合索引

主键是表中的一个字段或多个字段，用来唯一地标识表中的一条记录【一个表可以不建立主键】。如果一个表创建时没有主键，那么此时聚簇索引会使用其他列。如果表设置了主键，系统默认在主键列加上唯一约束的聚簇索引。
唯一索引：索引列的值必须唯一，但允许有空值【不能有重复记录】。
一个表的主键(索引)只能有一个且不能为null，但唯一索引可以多个且可以为null。

3.1、聚集索引(主键索引)

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。叶子节点存储整张表的行记录信息。使用primary key关键字。

聚簇索引的优点：
可以把相关数据保存在一起。聚簇索引的数据在叶子节点，且叶子节点间用链表连接。按照数据页来获取数据就可以减少I/O通信【顺序数据按照页存储，可以一同加载出需要的数据】。
数据访问更快。数据是直接放在叶子节点data域。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：
聚簇数据最大限度地提高I/O密集型性能，但数据全部放在内存，访问的顺序就不重要了。
插入速度严重依赖插入顺序。【按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式】
更新聚簇索引列的代价高。
基于聚簇索引的表在插入新行或主键被更新，导致“页分裂”问题。【将一个新行插入到已满的页时，存储引擎会将页分裂成两个页面，这是一次页分裂操作。页分裂会导致表占用多的磁盘空间】
行比较稀疏、页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描变慢。
二级索引(非聚簇索引)比较大，是因为二级索引的叶子节点包含引用行的主键列。
二级索引访问需要两次索引查找。

3.2、唯一索引

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值，通常使用unique关键字。
如果是组合索引，则列值的组合必须唯一。【加速查找+约束，唯一】

3.3、普通索引

普通索引：这是最基本的索引，它没有任何限制。【加速查找】

3.4、组合索引

组合索引：即一个索包含多个列。

组合索引的Data域有组合关键字和 主键id的关系。B+树的索引的Data域都是有主键id，进入去主键索引来获取数据行信息。

四、聚簇索引和非聚簇索引的问题

4.1、非聚簇索引的二次查找问题

问题现象
因为Innodb二级索引存储的是主键，所以通过索引查找时，第一次查询是通过二级索引找到主键值，第二次查询是通过主键在聚簇索引找到对应的行位置。

解决方案
A：索引覆盖：只查找聚簇索引的主键列id。【因为二级索引的叶子节点存储的是主键列信息，所以直接查主键列id信息】
B：延时关联：先使用索引覆盖查找主键，再通过主键关联原表的数据。

一般在典型 “排序+分页”查询，延时关联能提高查询效率。
“排序+分页”查询： order by a limit N,M -----一般是先扫描N行，再取m行。N越大，mysql需要扫描不需要的数据。

[SQL] SELECT * from t_hg_content_191125_p ORDER BY last_update limit 3000,10;
受影响的行: 0
时间: 2.367s

-----这个走的是二级索引。

[SQL] 
SELECT * from t_hg_content_191125_p1 p1 
INNER JOIN (SELECT id from t_hg_content_191125_p1 ORDER BY last_update limit 3000,10) p2 
ON p1.id = p2.id;
受影响的行: 0
时间: 0.108s

----综合比较发现，“延时关联”能提高查询效率。

4.2、聚簇索引为什么按照自增主键顺序插入

聚簇索引的数据物理存放顺序和索引顺序一致，即索引是相邻的，对应的数据也是相邻地存放在磁盘上。如果主键不是自增id，那么会不断进行页分裂操作，会产生大量的随机IO。主键是自增id，只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。