MySQL 索引常见问题

Question

问题：联合索引的使用场景介绍（结合网易面试）

• 对于 innodb 数据库中，主键为聚集索引，其余索引为非聚集索引（辅助索引）
• select a,b,c from tableA where a = 5 and c = 1;
• 最左匹配原则：
  • 使用的是非聚集索引
  • 因此可以从 B+ 树的数据结构中来进行分析
  • 在遇到 <,>,between,like 停止匹配
  • 这里这个实例中，可以建立 a,b,c 的联合索引，原因在于在通过 a,c 找到记录以后，还是需要到数据库中重新获取 b 的值，如果 b 的数据较多，要根据实际业务情况决定是否加入 b 的索引
  • 同时：如果 c = 1 类似于状态为 1 则为有效这种情况，也要按照实际业务情况判断是否需要建立 c 的索引

问题：索引失效的场景

• 条件中存在着 or
  • or 条件中的每个列都加上索引才可以生效
• 条件查询中， Like 中以 % 开始
• 多列索引的最左匹配原则
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 如果使用全表扫描比索引更快，索引失效

问题： auto_increment 修饰主键有什么好处

• 没有设置主键的情况下：
  • 没有设置主键：第一个不包含 NULL 的唯一索引
  • 没有这样的索引：会选择内置 6 字节长的 ROWID 作为隐含的聚集索引
• 使用自增主键：
  • 每一次插入新的记录，记录就会顺序添加到当前索引节点的后续位置：这样就保证了一个紧凑的索引结构
  • 每一次插入的时候也不用移动数据，不会增加很多开销在维护索引
• 不使用的情况
  • 多次进行数据的移动，出现较多的内存碎片
  • OPTIMIZE TABLE 来重建表并优化填充页面

问题：为什么不使用红黑树和 AVL 树进行存储

• 磁盘预读原理
  • 将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入
  • 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O
• 红黑树和 AVL 树
  • 每一个节点存储的数目不多（不多多路树），因此深度较高，会导致磁盘读取次数过多
  • 无法使用局部性原理
    • 区间查询
    • 逻辑上很近的节点（父子）物理上可能很远

问题： innodb 为什么非要用主键

• 个人理解：
  • 从业务上来说，设置主键可以保证每一次删除都会删除到特定的一条数据，不会出现其他条件的删除
  • 从数据结构来说：

    innodb 的数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。对于 innodb 的非聚集索引也是辅助索引：需要两次查询，先查询到主键，然后再通过主键查询到数据

    MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针

• 无特殊需求下 innodb 建议使用与业务无关的自增 ID 作为主键
  • 见上

问题：哪种类型的适合建索引；

• 在上面说到树的结构，当索引过大的时候，也就是 image ， text 和 bit 数据类型的时候是不适合建立索引的
• 这也是主键不能为空的原因：要起到唯一标识的作用

问题：数据库聚集索引和非聚集索引，以及其他数据结构实现

• HashMap ： 对点查找速度更快
• B- 树和 B+ 树的区别
  • B- 树
    • 根结点的儿子数为 [2, M]
    • 除根结点以外的非叶子结点的儿子数为 [M/2, M]
    • 每个结点存放至少 M/2-1 （取上整）和至多 M-1 个关键字
    • 非叶子结点的关键字个数 = 指向儿子的指针个数 -1
    • 特性：
      • 关键字集合分布在整颗树中
      • 任何一个关键字出现且只出现在一个结点中
  • B+ 树
    • 内节点不存储 data ，只存储 key
    • B+ 树只有达到叶子结点才命中
    • 指针因为是做索引的目的，所以指针个数的上限和关键字个数相同
    • 顺序访问指针：
      • 提高区间访问的性能
• 聚集索引和非聚集索引
  • MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址，就像上文中提到的 MyISAM 是非聚集索引，主索引和辅助索引 （Secondary key） 在结构上没有任何区别
  • MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引 （Secondary key） 在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复
  • InnoDB 的数据文件本身就是索引文件
    • data 域保存了完整的数据记录，因此称为聚集索引，其实本质的数据结构没有太大的区别。这也是上文中提到的：要求表必须有主键