MySql教程

MySQL的索引

本文主要是介绍MySQL的索引,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

索引?

MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。可以简单理解为“排好序的快速查找的数据结构”,索引的目的在于提高查询效率,可以类比于字典。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是以索引文件的形式存储在磁盘中的

我们所说的索引,如果没有特别指明,都是指B树结构组织的索引。其中聚集索引。次要索引、复合索引、唯一索引默认都是使用的B+树,统称索引。当然除了B+树这种索引外,还有哈希(Hash index)索引等。
那么,建立索引都有哪些好处和弊端呢?

优点:

  • 提高数据的检索效率,降低数据库的IO成本
  • 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗

缺点:

  • 实际上索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,因此索引也需要耗费空间资源的
  • 虽然索引大大提升了查询效率,同时它也会降低更新表的速度。当对表进行增删改时,MySQL不仅要更新数据,还要更新索引,会调整因更新带来的键值变化后的索引信息。

基本语法

创建索引

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));或
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

删除索引

DROP INDEX [indexName] ON mytable;

查看索引

SHOW INDEX FROM mytable;

了解了索引的基本用法后,我们再来看看索引的数据结构及检索过程

BTree的结构

B树
每一个磁盘块对应B树的一个节点,最下层是叶子节点。最上面是根节点。注意上图中的数据是为了演示而编造的。我们可以看到每个磁盘块包含几个数据项(深蓝色)和指针(黄色)。例如:磁盘块1包含数据项17和35,包含指针P1、P2、P3。
P1指针指向的数据小于17的磁盘块,P2指针指向数据在17和35之间的磁盘块,P3指向数据大于35的磁盘块。真实的数据存储在叶子节点中,而非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不一定真实存在与数据表中。

查找过程

我们以29举例,如果要查找29,首先会将这棵树的根节点即磁盘块1加载进内存中,此时发生一次IO,在内存中用二分查找确定29在17和35之间,于是拿到磁盘块1的指针P2,锁定下一个磁盘块(内存操作时间非常短,相比磁盘IO,可以忽略不计),将下一个磁盘块3加载到内存中,发生第二次IO,如何继续对比,拿到相应的指针P2。通过P2指针将对应的磁盘块8加载进内存,这是第三次IO。同时在内存中使用二分查找找到29。至此查找成功,总共花费3次IO。

在真实的情况下,三层的BTree可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能的提升将是巨大的。如果没有索引,那么每个数据项都要发生一次IO,显然效率十分低下并且成本是非常高的。

这篇关于MySQL的索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!