MySQL高级第二篇：索引的分类及MyISAM与InnoDB中的索引对比

一、索引的分类

1. 聚簇索引

• 针对主键构建的索引，聚簇即数据行和相邻的键值存储在一起。

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，即所有的用户记录都存储在了叶子结点，索引即数据，数据即索引。

• 特点：

  • 叶子节点存储一条条完整记录，记录之间单向链表相连
  • 页内记录按主键大小顺序排序，页之间双向链表连接，页之间也升序排列
  • 这种索引无序我们手动创建，InnoDB会自动创建

• 优点：

  • 数据访问更快，因为直接保存了完整记录，直接就查到了，无需回表操作
  • 对主键的 排序查找和范围查找更快

• 缺点：

  • 插入速度严重依赖插入顺序
  • 更新主键代价比较高
  • 二级索引访问需要两次索引查找

• 限制：

  • MySQL数据库只有InnoDB支持聚簇索引
  • 每个MySQL表只能有一个聚簇索引，一般就是主键
  • 如果没有定义主键，InnoDB会选择非空的唯一索引代替，如果页没有，就会隐式的定义一个主键作为聚簇索引

2. 非聚簇索引（二级索引，辅助索引）

• 针对非主键构建的索引,可以有多个非聚簇索引
• 非聚簇索引不保存完整记录，且按照索引列排序
• 非聚簇索引只能确定主键，查找完整数据时，需要回表，再到聚簇索引中查找一遍

3. 联合索引（也属于非聚簇索引）

• 同时为多个列建立索引，以多个列大小作为排序规则
• 本质上也是一个二级索引

二、InnoDB的 B+ 树索引注意

1. 根页面位置始终不变

• 每当为某个表创建索引时，最开始还没有数据的时候，就会为它创建一个根结点页面。
• 然后慢慢往表中插入记录，这时先保存在这个根结点页中
• 当根结点满了后，会将根节点所有记录复制一份，然后页分裂，移动数据等，这时根结点页便升级为目录页了
• 整个过程最开始的根页面始终不会移动改变

2.内节点中目录项记录的唯一性

• 对于二级索引来说，当为某列建立索引时，假如多条记录的这一列都是相同的，我们再来添加一条记录，和已有的那些记录也相同，这时，我们就找不到要插入到哪一页了。
• 所以，我们要保证目录项记录的唯一性，如果这一列有重复，可以冗余保存上主键，这样就不会重复了

3. 一个页面最少存储两条记录

这个很简单，每个页只存储一条的话，完全没有什么效果，连树的结构都不能形成。

三、MyISAM与InnoDB中的索引对比

• MyISAM索引文件和数据文件分离，索引仅保存数据地址
• MyISAM的索引都是非聚簇的，每次都要进行一次回表
• 它的回表是根据地址直接去拿，比较快速
• InnoDB表必须有主键，MyISAM无所谓

四、思考？

• 索引这么方便，我们是不是上来就要建索引呢？
• 其实并不是，索引虽然好，但不合理的使用索引只有坏处没有好处
• 一个表上的索引越多，占用的存储空间就会越大，在增删改记录的时候需要维护索引，性能就会变差
• 所以，学习如何合理的使用索引就显得尤为重要