面试题：MySQL索引列不重复值个数对不同类型索引性能的影响

Question

MySQL有多种索引类型，如B - Tree索引、哈希索引等。当索引列中不重复值个数变化时，对这些不同类型索引的性能影响有何差异？请详细分析并举例说明。

Answer 1

B - Tree索引

1. 不重复值个数较多时
   • 性能优势：B - Tree索引在这种情况下性能良好。因为B - Tree索引基于比较进行查找，它可以通过树结构快速定位到目标数据。例如，在一个用户表中，假设按照用户ID建立B - Tree索引，用户ID一般具有较高的唯一性。当执行SELECT * FROM users WHERE user_id = 123;这样的查询时，B - Tree索引能够利用其树结构，从根节点开始，通过比较节点值快速向下遍历，迅速定位到对应的数据行，查询效率高。
   • 原因：B - Tree的结构特点使得范围查询和精确查询都能高效执行。每个节点包含多个键值对和指针，通过比较键值可以快速决定搜索方向，减少磁盘I/O操作。
2. 不重复值个数较少时
   • 性能影响：性能会有所下降。因为此时B - Tree索引树的高度可能会降低，节点中键值的区分度变小，导致每次查找需要比较更多的键值。例如，在一个状态表中，状态字段可能只有“已完成”“未完成”等少数几个取值，若基于此状态字段建立B - Tree索引，当执行SELECT * FROM tasks WHERE status = '已完成';查询时，由于索引列不重复值少，B - Tree索引不能很好地发挥其快速定位的优势，查询可能需要遍历更多的节点，增加了查询时间。
   • 原因：B - Tree索引依赖键值的区分度来高效定位数据，区分度低时，索引的过滤效果变差。

哈希索引

1. 不重复值个数较多时
   • 性能优势：哈希索引在这种情况下性能出色。哈希索引基于哈希函数进行数据存储和查找，对于精确查询，只要哈希函数计算出的哈希值不冲突，就可以直接定位到数据所在位置。例如，在一个订单表中，按照订单编号建立哈希索引，订单编号具有较高的唯一性。当执行SELECT * FROM orders WHERE order_number = '20230101001';查询时，哈希索引可以通过对订单编号计算哈希值，直接定位到对应的数据行，查询速度极快。
   • 原因：哈希索引的查找过程是基于哈希值的直接定位，不需要像B - Tree那样进行比较和遍历树结构，所以在精确查询时效率极高。
2. 不重复值个数较少时
   • 性能影响：性能会急剧下降。因为哈希索引的哈希函数会将不同的键值映射到有限的哈希桶中，当不重复值个数较少时，哈希冲突的概率会大大增加。例如，在一个性别字段（只有“男”“女”两个取值）上建立哈希索引，当执行SELECT * FROM employees WHERE gender = '男';查询时，由于哈希冲突，可能需要在哈希桶中遍历多个数据项来找到符合条件的数据，这大大降低了查询效率，甚至可能比全表扫描还慢。
   • 原因：哈希冲突使得哈希索引原本快速定位的优势丧失，需要额外的操作来处理冲突，增加了查询开销。