数据库索引理论

一、索引介绍

数据库索引好比是一本书前面的目录，能够提高数据库的查询速度。

字典是最好的类比：

①拼音查字法把拼音首字母相同的字放在一起，便于人们根据读音查找，数据库中的聚簇索引的原理就是这样。

②部首查字法把特定部首的字的页数记录下来，根据页数去查找相应的字，数据库中的非聚簇索引的原理就是这样。

 

二、索引的优缺点

优点：

1、大大加大数据检索速度

2、加速表与表的连接

3、减少查询中分组和排序的时间

缺点：

1、索引需要占据额外的存储空间

2、每次对相应的列进行插入和更新时，对应的索引也会重建

3、创建索引和维护索引需要耗时

 

三、聚集索引

定义：数据行存放的物理顺序与列值（一般是主键）的逻辑顺序相同，一个表中只能有一个聚集索引。

数据库中存放方式：

地址	id	地区
0x01	1	北京
0x02	2	上海
0x03	3	天津

叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行，非常快捷。

注：MYSQL中没有CLUSTER索引的概念，只有INNODB的主键是使用CLUSTER方式存储的。

 

四、单列索引、多列索引以及最左前缀原则

下面通过实例简单介绍这几个名词：

。。。

1、单列索引

任务：Mysql>查找Name（城市名）为Haag，CountryCode（城市码）为NLD，District（地区）为Zuid-Holland的ID号。

SELECT ID FROM city WHERE NAME='Haag' AND CountryCode='NLD' AND District='Zuid-Holland'

由于表比较大，我们不想全表扫描，并且表修改的可能性很小，我们在Name上建立单列索引：

ALTER TABLE city ADD INDEX index_Name (Name); 

与执行全表搜寻相比，效率高很多。每次搜索的范围限定在Name='Haag'内，但如果Name='Haag'的行数很多，仍需要扫描大量数据，搜索效率依旧会不理想。

 

2、多列索引

使用多列索引，我们不用扫描任何记录，直接获得最终结果。

ALTER TABLE city ADD INDEX indexs (Name, CountryCode, District); 

 

3、最左前缀原则

我们创建了上述的多列索引后，相当于创建了(Name)单列索引、(Name,CountryCode)组合索引、（Name,CountryCode,District）组合索引。