MySQL 面试题(二)

索引篇

什么是索引

• 索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针
• 索引是一种数据结构，通俗的说，索引相当于目录，也是要占据物理空间的

索引有哪些优缺点

• 优点：加快数据检索速度，通过使用索引，可以再查询的过程中，使用优化隐藏器，提高系统性能
• 缺点：创建索引和维护索引比较耗费时间，对表中数据进行更新时，索引也要动态维护，效率降低，索引会占据物理空间

索引分类和作用

• 普通索引：基本索引类型，没有唯一性限制，允许为 null 值

• 唯一索引：数据列不允许重复，允许为 null 值，一个表可以有多个唯一索引

• 主键索引：数据列不允许重复，不允许为 null 值，一个表最多只能有一个主键

• 聚集索引：表中行的物理顺序与键值的索引顺序相同

• 非聚集索引：表记录的排列顺序与索引的排列顺序不一致

• 复合索引：在创建索引时，并不是只能对一列进行创建索引，可以将多列组合为索引

• 全文索引：在字符串数据中进行复杂的词搜索提供有效支持

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

• 主键索引列不允许为空值，唯一索引列允许空值
• 主键创建后一定包含一个唯一索引，唯一索引并不一定就是主键
• 一个表只能创建一个主键，但可以创建多个唯一索引

索引的数据结构

• 哈希索引：类似于散列表，主要通过 hash 算法(常见的有：直接定址法、平方取中法、折叠法、除数取余法、随机数法) 将数据库字段数据转换成定长的 hash 值，与这条数据的行指针一并存入 hash 表的对应位置，如果发生 hash 碰撞，则在对应 hash 键下以链表形式存储；进行查询时，调用一次 hash 函数获取到键值，然后再进行回表查询获得实际数据
• B 树索引

  • 实际上是用 B+树实现的，因为在查看表索引时，mysql 一律打印 BTREE ，所以简称为 B 树索引
  • 底层实现是多路平衡查找树，每一次查询都是从根节点出发，查找到叶子节点方可获取所需键值，然后根据查询判断是否需要回表查询数据
  • 查询方式

    • 主键索引区: PI(关联保存的是数据的地址)按主键查询
    • 普通索引区: SI(关联的id的地址，然后再到达数据的地址)，所以按主键查询速度最快
  • B+tree 性质

    • n棵子 tree 的节点包含 n 个关键字，不用来保存数据而是保存数据的索引
    • 所有的叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依赖于关键字的大小自小而大顺序链接
    • B+ tree 中，数据对象的插入和删除仅在叶子节点上进行
    • B+ tree 有2个头指针，一个是树的根节点，一个是最小关键码的叶节点

索引的基本原理

• 用来快速的查找具有特定值的记录，如果没有索引，一般来说执行查询时会遍历整张表

• 把无序的数据变成有序的查询

  1. 把创建了索引的列的内容进行排序

  2. 对排序结果生成倒排表

  3. 在倒排表内容上拼上数据地址链

  4. 在查询的时候，先拿到倒排内容，再取出数据地址链，从而拿到具体数据

索引设计原则

• 出现在 where 子句中的列，或者连接子句中的列
• 基数较小的列，索引效果较差，没有必要建立索引
• 对长字符串列建立索引，应指定一个前缀长度，可节省大量索引空间
• 不要过度建立索引，索引需要额外磁盘空间，并降低了更新操作的性能

创建索引的原则

• 最左前缀匹配原则，也是组合索引非常重要的原则，MySQL 会一直向右匹配直到遇到范围查找
• 非空字段
• 取值离散程度高的字段
• 频繁作为查询条件的字段
• 更新频繁字段不适合创建索引
• 尽量扩展索引，不要新建索引
• 查询中很少涉及且重复值较多的列不要建索引
• text、image、bit 此类数据类型的列不要建索引

创建索引的三种方式

• 在建表时创建索引
• 使用 ALTER TABLE 命令去增减索引：ALTER TABLE table_name ADD INDEX index_name(column_list)
• 使用 CREATE INDEX 命令创建：CREATE INDEX index_name ON table_name(column_list)，该方式不能创建主键索引

如何删除索引

• ALTER TABLE table_name DROP KEY index_name
• 如果主键自增则不能直接进行删除，需要先取消自增

B树和B+树的区别

• 在B树中，你可以将键和值存放在内部节点和叶子节点；在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值
• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立
• B树可以把频繁访问的数据放在靠近根节点的地方会提高热点数据查询效率，这点使得它在重复多次查询的场景中更高效
• B+树一次读取可以获得更多的键，有利于缩小范围，查询时间复杂度低

Hash 索引和 B+ 树索引区别

• 一般情况下 hash 索引进行等值查询更快，当出现 hash 碰撞时效率则可能较差；无法进行范围查找
• 因为在 hash 索引中经过 hash 函数建立索引后，索引得顺序与原顺序无法保持一致，不能支持范围查找；而 B+ 树得索所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围查找
• hash 索引不支持使用索引进行排序，原理同上
• hash 索引不支持模糊查询以及多列索引得最左前缀匹配；远离也是因为 hash 函数的不可预测性
• hash 索引任何时候都避免不了回表查询数据，而B+树在符合某些条件的时候都可以只通过索引完成查询

数据库为什么使用 B+树而不是B树

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索
• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低
• B+树查询效率更加稳定，增删文件节点时，效率更高

B+树在满足聚簇索引和覆盖索引时不需要回表查询数据

• 在 B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引
• 在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引；如果没有唯一键则隐式的生成一个键来建立聚簇索引
• 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不再进行回表查询

非聚簇索引是否一定会回表查询

• 不一定，涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中索引，不会再进行回表查询
• 假设我们在员工表的年龄上建立了索引，那么进行在 where age <20 查询时，在索引的叶子节点上，以及包含了 age 信息，不会再次进行回表查询

- END -