【MySQL】索引

---

一、介绍

索引（index）

• 是帮助数据库高效获取数据的数据结构 。
• 使用索引可以提高查询的效率

模拟数据结构搜索：

二、优缺点

优点：

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

缺点：

1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

基本上项目里 90% 都是 select查询，关于删改效率可以忽略

三、树结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

回顾下之前所学习的树结构：

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。

可以选择平衡二叉树或者是红黑树来解决上述问题。（红黑树也是一棵平衡的二叉树）

但是在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。

思考：采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？

• 最大的问题就是在数据量大的情况下，树的层级比较深，会影响检索速度。
• 因为不管是二叉搜索数还是红黑数，一个节点下面只能有两个子节点。
• 此时在数据量大的情况下，就会造成数的高度比较高，树的高度一旦高了，检索速度就会降低。

说明：

• 如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。
• 所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。

四、B+Tree

B+Tree(多路平衡搜索树) ：

B+Tree结构：

• 每一个节点，可以存储多个key（有n个key，就有n个指针）
• 节点分为：叶子节点、非叶子节点
  • 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上
  • 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
• 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

拓展：

非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。

• 查看mysql索引节点大小：show global status like ‘innodb_page_size’; – 节点大小：16384

当根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：11701170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。
.
对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W16结果就是2000W+的元素个数。

结合上述分析B+Tree有如下优点：

• 千万条数据，B+Tree可以控制在小于等于3的高度
• 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找

五、语法

5.1 创建索引

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

案例：为tb_emp表的name字段建立一个索引

create index idx_emp_name on tb_emp(name);

在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束

5.2 查看索引

show  index  from  表名;

案例：查询 tb_emp 表的索引信息

show  index  from  tb_emp;

5.3 删除索引

drop  index  索引名  on  表名;

案例：删除 tb_emp 表中name字段的索引

drop index idx_emp_name on tb_emp;

---

总结

注意事项：

• 主键字段，在建表时，会自动创建主键索引
• 添加唯一约束时，数据库实际上会添加唯一索引