JAVA-如何理解Mysql的索引

一、索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用(指针/地址)。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。

二、索引是什么，用来干嘛

• 数据库中的表、数据、索引之间的关系，类似于书架上的 图书(表)、书籍内容(数据)、书籍目录(索引)  的关系
• 索引的作用类似书籍的目录，可以用于快速定位、检索数据
• 索引对数据库的性能有很大的帮助

 

三、使用索引的代价

• 需要额外的存储空间（对于大部分情况而言，并没有什么大的影响，除非是一些空间较小的嵌入式计算机）
• 索引可能会造成增删改的效率下降，（但为什么说索引会对数据库的性能有很大的帮助，因为大部分情况，查询比增删改的次数多。但为什么右说是可能会造成效率下降呢？因为有时候查询速度差不多，有时候更慢、有时候更快）

 所以总体来说是利大于弊的

四、索引的使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑几点：

• 数据量大，且经常对这些列进行条件查询。(只会提升有索引列的查询速度)
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 在存储空间非常吃紧的情况下不太推荐使用，索引会占用额外空间

 满足以上条件，考虑对表中经常用来查询条件的列，创建索引

 

五、使用案列

1.查看表中的索引

语法：show index from 表名;

 如何查看：

比较重要的信息：

可以自动创建索引的数据库约束：

• primary key
• foreign key
• union

那么可以看到上图，自动创建的索引名，会在索引名这一列显示由什么操作创建的。如果是我们手动创建的，就是我们自己指定的索引名。

 2.创建索引

语法：create index 索引名 on 表名(列名);

值得注意的是，创建索引是一种危险的操作，如果表中数据量大 千万级……此时创建索引操作，可能会触发硬盘大量IO，直接把机器搞挂了

最好还是在刚开始设计表的时候就把结果设计好

如果实在是要改了，再用一个数据库把数据都拷贝过去，再进行更改索引

再次提醒：

只有，有索引的这一列是可以通过条件查询提升查询速度的，如果用其他列的查询，是不会有性能提升的。

3.删除索引 

语法：drop index 索引名 on 表名;

此时我们创建的索引就被删除了。

但是需要注意，只有手动创建的索引才是可以删除的，如果是在约束一些条件自动创建的索引，是不能够被删除的。

其次，删除索引和创建索引一样，是一种危险的操作

 

六、 索引背后的数据结构

其实所谓的添加索引，就是引入一些数据结构。

那能大大提升查询速度的数据结构是哪个？

我其实第一时间想到的是Hash，但是有Hash有个问题：

值1 < 值2  但是  并不代表  hash(值1)  <  hash(值2)

所以其实Hash并不适合范围内的查询，Hash适合 等于 这样的查询操作 

那博主也是在学习之后才知道，B+数这是一个量身为数据库打造的数据结构。

 在了解B+树前我们先了解一下B树

 1.B数

B树是一个N叉搜索树，每个节点有N个元素，最大可以有N+1个子树

 当我们查询数据的时候，就依次遍历节点中的值去查找范围

此时虽然高度降低了，但是每个节点的比较次数变多了，有区别吗？

其实，优势还是很大的！每个节点，访问的时候一次硬盘IO就可以了。

如果某个节点进行比较的时候，我们只需要一次硬盘IO，把所有的这个节点内容都读取出来，接下来的比较是在内存中进行的所以是比较快的。

所以我们最主要的目的是减少硬盘IO，在内存中比较数据是非常快的。

 

 2.B+树

于B树不同，B+树也是N个节点，但它是N个子节点，少了最后一个元素后的子树。

B+树存储数据：

查询操作都会在叶子节点进行操作

查询 key >= 5 and key =< 13 的情况：

所以B+树对范围的查询是非常方便的

B+树的特点：

• N叉搜索树，每个节点上包含N个key值，万分出N个子区间
• 每个父亲节点中的元素，都会下沉到子节点中，分别作为该孩子节点中的最大值
• 叶子节点包含了，表中的所有元素
• 使用类双向链表的结构，把叶子结点串起来

B+树的优势：

• B+树是N叉搜索树，高度比较低，硬盘IO就少
• 叶子节点包含了所有元素，并且用双向链表结构连接起来，非常便于查询
• B+树，所有的查询都是落到叶子结点上完成的，经历的IO次数都差不多，查询的开销稳定
• 由于B+树，叶子结点是全集，非叶子节点上不必存储“数据行”，只需要存储索引列的Key值即可如果非叶子节点里的值有1000w个，每个key值类型为int，一个int4个字节，4000万个字节，也才差不多40M个数据

补充 每个单位的值代表多大：

• kb -> 千
• mb -> 百万
• gb -> 十亿