索引的概念
索引是帮助MySQL高效获取数据的数据结构。
索引是对数据库表中一列或者多列的值进行排序的数据结构,用于快速访问数据库表中的特定信息。
索引的分类
- 主键索引:特殊的唯一索引,也称聚簇索引,不允许有空值,并由数据库帮我们自动创建。
- 普通索引:指定一个字段为索引。
- 联合索引:多个字段创建的索引,遵守最左前缀匹配规则。
索引的优缺点
- 优点:
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
- 缺点:
- 创建和维护索引需要消耗时间,这种时间随着数据量的增加而增加,这样就降低了数据的维护速度。
- 索引需要占物理空间,处理数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间会更大。
索引的设计原则
- 选择唯一性索引:唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
- 为常作为查询条件的字段建立索引:入宫某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。
- 为经常需要排序、分组和联合操作的字段建立索引:经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间,入宫为其建立索引,可以有效避免排序操作。
- 限制索引的数目:每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间越大,修改表时,对索引的重构和更新很麻烦。
- 小表不建议索引(数量级在百万以内):由于数据较小,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
- 尽量使用数据量少的索引:如果索引的值很长,那么查询的速度会受到影响,此时尽量使用前缀索引。
- 删除不再使用或很少使用的索引。
主键索引
一个表中一定会有一个主键索引,如果设计表时没有设置主键,则会选取unique作为主键索引,如果连unique都没有,则会给每一个数据生成一个DB_ROW_ID作为主键。
数据表以页为单位存储数据。
-
数据页中:数据以单链表形式连接,数据以主键从小到大的顺序连接。有一个最小记录结点指向第一个数据结点,有一个最大记录结点指向最后一个数据结点。
- 数据页中的数据查找:在数据页中,会把数据分成不同的组,将每个组中主键最大的数据当作组长,组长会记录每个组的数据个数。再将每个小组长编成目录,这个目录被称为槽。
-
数据页之间:数据页之间连接以双向链表的形式进行连接。如果数据页太多,则会将数据页的页号拿出来编成一个目录。
-
数据页目录:数据页目录中会存储每一个数据页中最小的数据以及页号,将这些数据以单链表的形式连接,数据以主键从小到大的顺序连接,也有一个最小记录结点指向第一个数据结点,有一个最大记录结点指向最后一个数据结点。
如果数据页目录过多则会在生成一个数据页目录的目录,以此类推,最终会有一个根节点。
在索引的叶子结点中会存储所有的数据。
普通索引
- 数据页中:存储的数据只有普通索引和主键值,按照普通索引进行排序,其余与主键索引一致。
- 数据页目录:存储数据的普通索引和主键值,按照普通索引进行排序,其余与主键索引一致。
查询到需要的数据的主键值之后,再到主键索引中查找数据,这就是回表操作。
联合索引
- 数据页中:存储的数据只有联合索引和主键值,按照联合索引最左边的值进行排序,如果此值相同,则按照接下来的值进行排序。其余与主键索引一致。
- 数据页目录中:存储数据的联合索引和主键值,按照联合索引最左边的值进行排序,如果此值相同,则按照接下来的值进行排序。其余与主键索引一致。
查询到需要的数据的主键值之后,再到主键索引中查找数据。
因为索引是先按照最左边的值进行排序,所以联合索引需要遵循最左前缀匹配规则。