MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。可以简单理解为“排好序的快速查找的数据结构”,索引的目的在于提高查询效率,可以类比于字典。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是以索引文件的形式存储在磁盘中的
我们所说的索引,如果没有特别指明,都是指B树结构组织的索引。其中聚集索引。次要索引、复合索引、唯一索引默认都是使用的B+树,统称索引。当然除了B+树这种索引外,还有哈希(Hash index)索引等。
那么,建立索引都有哪些好处和弊端呢?
优点:
缺点:
创建索引
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;
了解了索引的基本用法后,我们再来看看索引的数据结构及检索过程
每一个磁盘块对应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,显然效率十分低下并且成本是非常高的。