MySQL的三种索引：主键索引，普通索引，联合索引_mysql 联合主键与索引

索引的概念

索引是帮助MySQL高效获取数据的数据结构。
索引是对数据库表中一列或者多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

索引的分类

• 主键索引：特殊的唯一索引，也称聚簇索引，不允许有空值，并由数据库帮我们自动创建。
• 普通索引：指定一个字段为索引。
• 联合索引：多个字段创建的索引，遵守最左前缀匹配规则。

索引的优缺点

• 优点：
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 缺点：
  • 创建和维护索引需要消耗时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
  • 索引需要占物理空间，处理数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间会更大。

索引的设计原则

• 选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
• 为常作为查询条件的字段建立索引：入宫某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
• 为经常需要排序、分组和联合操作的字段建立索引：经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，入宫为其建立索引，可以有效避免排序操作。
• 限制索引的数目：每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大，修改表时，对索引的重构和更新很麻烦。
• 小表不建议索引（数量级在百万以内）：由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
• 尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响，此时尽量使用前缀索引。
• 删除不再使用或很少使用的索引。

主键索引

一个表中一定会有一个主键索引，如果设计表时没有设置主键，则会选取unique作为主键索引，如果连unique都没有，则会给每一个数据生成一个DB_ROW_ID作为主键。
数据表以页为单位存储数据。

• 数据页中：数据以单链表形式连接，数据以主键从小到大的顺序连接。有一个最小记录结点指向第一个数据结点，有一个最大记录结点指向最后一个数据结点。
  

  • 数据页中的数据查找：在数据页中，会把数据分成不同的组，将每个组中主键最大的数据当作组长，组长会记录每个组的数据个数。再将每个小组长编成目录，这个目录被称为槽。

• 数据页之间：数据页之间连接以双向链表的形式进行连接。如果数据页太多，则会将数据页的页号拿出来编成一个目录。
  

• 数据页目录：数据页目录中会存储每一个数据页中最小的数据以及页号，将这些数据以单链表的形式连接，数据以主键从小到大的顺序连接，也有一个最小记录结点指向第一个数据结点，有一个最大记录结点指向最后一个数据结点。
  

如果数据页目录过多则会在生成一个数据页目录的目录，以此类推，最终会有一个根节点。
在索引的叶子结点中会存储所有的数据。

普通索引

• 数据页中：存储的数据只有普通索引和主键值，按照普通索引进行排序，其余与主键索引一致。
• 数据页目录：存储数据的普通索引和主键值，按照普通索引进行排序，其余与主键索引一致。
  查询到需要的数据的主键值之后，再到主键索引中查找数据，这就是回表操作。

联合索引

• 数据页中：存储的数据只有联合索引和主键值，按照联合索引最左边的值进行排序，如果此值相同，则按照接下来的值进行排序。其余与主键索引一致。
• 数据页目录中：存储数据的联合索引和主键值，按照联合索引最左边的值进行排序，如果此值相同，则按照接下来的值进行排序。其余与主键索引一致。
  查询到需要的数据的主键值之后，再到主键索引中查找数据。
  因为索引是先按照最左边的值进行排序，所以联合索引需要遵循最左前缀匹配规则。