索引类别及各索引的区别

  • 主键索引：特殊的唯一索引，不允许有空值
  • 唯一索引：索引的列值必须唯一，但是允许存在空值
  • 普通索引：最基本的索引，没有任何限制
  • 全文索引：针对M有ISAM引擎，针对较大的数据，生成全文索引很耗时
  • 组合索引：遵循”最左前缀”原则

主键索引和唯一索引的区别

  • 主键索引是特殊的唯一索引
  • 主键索引不允许存在空值，唯一索引允许空值

唯一索引和普通索引的区别

查找

在查找上，唯一索引和普通索引性能差别微乎其微。
逻辑区别是，唯一索引找到等于条件的情况会立即返回，普通索引会按顺序往下遍历，知道找不不符合条件的项出现。

更新

普通索引和唯一索引的区别很大，涉及到change buffer。

  • 普通索引的更新使用change buffer，所以普通索引更新比唯一索引快。
  • 唯一索引由于所有的更新操作之前都需要判断唯一性约束，需要查到这条数据，保证数据在磁盘内，所以唯一索引不需要change buffer。
    change buffer
    有两个部分，一部分在内存的buffer pool中，一部分在磁盘内ibdata。
    当有更改的需求时，如果数据不在内存，会将修改写入change buffer，同时将change buffer的记录写入redo log。
    change buffer的数据刷入数据页中的动作叫做merge，很多情况下会触发merge：访问该数据页，后台线程定期执行。
    redo log 和 change buffer
    redo log，会按顺序将操作记录写入磁盘文件，所以是减少了随机写磁盘的次数。
    change buffer，将记录写在内存，不用每次从磁盘读数据更新，减少了随机读的次数。
    change buffer的merge流程
  1. 从磁盘读入数据页到内存（老版本的数据页）；
  2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
  3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
  4. 此时内存的merge就就结束了，之后就要刷回自己的物理内存了。

选错索引

容易出现在频繁删除和插入数据的表中。在删除和插入数据的操作间隔过于接近的场合，由于为了维持某个连接A的一致性视图，所以连接B对数据进行的删除操作并不会直接删除，而是保留（因为一致性视图，需要维持undo log链表），于是产生了explain的统计信息出现错误，使得优化器选错索引。

选错索引的常用方法

  1. 使用analyze table重新统计表信息。
  2. 可以使用force key手动指定索引。