数据库---索引

概述

索引是帮助mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点：

• 1、提高数据检索的效率，降低数据库的IO成本
• 2、通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

• 1、索引列也要占用空间
• 2、索引大大提高了查询效率，同事却降低了更新表的书读，如对表进行INSERT、UPDATE、DELETE是，效率降低

数据库查询，当没有索引时，是通过全表扫描的方式查询的，效率极低！

数据结构：二叉树、红黑树、b-tree、b+tree

二叉树

索引结构

b+tree结构时唯一都支持三种引擎的结构：innodb、myisam、memory

二叉树

二叉树缺点：按顺序插入的时候就形成了单向链表

红黑树

红黑树（自平衡二叉树）缺点：大数据量情况下，层级较深，检索速度慢。

b-tree(多路平衡查找树)

以一颗最大度数(max-degree)为5（5阶）的b-tree为例（每个节点最多存储4个key,5个指针）：

树的度数指的是一个节点的子节点个数
n个key，n+1个指针

数据结构动画网站：

http://www.rmboot.com/


可以看到，b-tree为5阶时，有5个指针，最多4个key(树)，每个节点可保存的数量为4，就可以解决层级深的缺点

b+tree

以一颗最大度数(max-degree)为4（4阶）的b+tree为例：
1、b+tree所有的元素都会出现在最后的叶子节点上，上面的分页子节点只起到检索的作用
2、b+tree的叶子节点会形成一个单向链表


相对于b-tree的区别：
1、所有数据都会出现在叶子节点
2、叶子节点形成一个单向链表

mysql的b+tree中进行了优化。在原b+tree的基础上，增加一个指向相领叶子节点的链表指针，就形成了带有顺序指针的b+tree,提高区间访问的性能。

hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应得槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），也可以通过链表来解决

hash索引特点
1、hash索引只能用于对等比较（=，in）,不支持范围查询（between,>,<…）
2、无法利用索引完成排序操作
3、查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

索引分类

根据索引的存储形式，又可以分为下面两种：
聚集索引：必须有，而且只有一个（因为聚集索引将数据和索引都存放了，该聚集索引的叶子节点保存了该索引对应得那一条数据的所有列数据）
二级索引：叶子节点只存放了对应得主键

聚集索引选取规则：

1、如果存在主键，主键索引就是聚集索引。
2、如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3、如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：
当使用二级索引查询数据时，如果查询的不仅仅是二级索引的key值或者叶子节点上的主键值，还有表中其他字段，就会回表查询，通过id再去聚集索引里面查询得到其他字段，所以这种情况会比直接用聚集查询效率慢点

计算innodb主键索引的b+tree高度

n:代表当前节点key的数量
n+1:代表指针的数量
假设设置的主键的类型为bigint，则key占用8个字节,
InnoDB指针占用6个字节
而数据库一页的大小为16k,则有16*1024个字节
参考：https://blog.csdn.net/qq_43470725/article/details/126497366

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区；表结构中的记录以及索引都是在页中存储的
16KB是指16x1024个字节

最后算出来每个节点key的个数大概是1170，那就会有1171个指针。
这个公式算出来的是第一行根节点（数据库物理内存允许情况下一个节点也就是逻辑内存下的一页的内存大小）的key和指针数量，那么当高度为2时，第一行有1171个指针，那第二行可以有1171个指针指向叶子节点，而每个叶子节点可以存放16行这样的数据，所以有1171*16=18736个数据可以存放。当高度为3时，同理可以每个子节点（当做时第一次根节点）又有1171个子节点
所以当高度为3时，最后叶子节点可以存放的数据数量为：1171117116=21939856（2千多万条数据）

sql索引语法

创建索引：

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,..);

查看索引：

SHOW INDEX FROM table_name;

删除索引：

DROP INDEX index_name ON table_name;