索引选择性（Selectivity）与索引失效分析 - 智猿学院-IT界的百科全书

各位观众老爷们，大家好！我是你们的老朋友，数据库界的段子手——索引小能手。今天咱们不聊风花雪月，来聊聊数据库里一个既重要又容易被忽视的小妖精——索引选择性（Selectivity）。

啥是索引选择性？为啥它这么重要？

话说，数据库就像一个巨大的图书馆，里面藏着海量的书籍（数据）。当你想找一本特定的书时，图书馆管理员（数据库优化器）有两种方式：

笨办法： 一排排书架挨个找，直到找到你要的书。这就像不使用索引，数据库要全表扫描，效率低下，慢到让你怀疑人生。
聪明办法： 先查阅图书馆的索引目录，找到目标书籍所在的架位，然后直接去对应的位置拿书。这就像使用了索引，数据库可以快速定位到目标数据，效率嗖嗖的。

索引选择性，就是衡量这个“索引目录”好不好用的一个指标。它代表着索引列中唯一值的数量与总记录数的比率。简单来说，就是这个索引能帮你过滤掉多少无用的数据。

数学公式：

选择性 = 唯一值数量 / 总记录数

选择性越高（越接近1），索引效果越好。 这意味着索引列的唯一值越多，索引能够筛选掉的数据就越多，数据库就能更快地找到你要的数据。就像图书馆的索引目录非常详细，能精确到每本书的位置。
选择性越低（越接近0），索引效果越差。 这意味着索引列的唯一值越少，索引几乎没法帮你筛选数据，反而可能拖慢查询速度。就像图书馆的索引目录只有“小说”、“漫画”等大类，你还得在这些大类里大海捞针。

举个栗子：

假设我们有一个users表，包含以下字段：

id (主键，自增)
name (姓名)
age (年龄)
gender (性别)

如果gender列只有两个值：“男”和“女”，那么它的选择性就很低，因为无论你搜索哪个性别，索引都只能帮你过滤掉一半的数据。而name列的每个值可能都不同，那么它的选择性就很高，索引能够帮你快速定位到指定的用户。

表格对比：

列名	唯一值数量	总记录数	选择性	索引效果
`id`	10000	10000	1	极好
`name`	9000	10000	0.9	好
`age`	80	10000	0.008	一般
`gender`	2	10000	0.0002	极差

为啥选择性低的索引会失效？

好，现在我们来聊聊正题：为啥选择性低的索引有时候反而会失效呢？这就像一件精心设计的衣服，穿在不合适的人身上，反而显得丑陋。

数据库优化器就像一个精明的裁缝，它会根据查询的条件和数据分布，来判断是否使用索引。当索引的选择性很低时，优化器会发现：

索引扫描的代价太高： 即使使用了索引，也只能过滤掉很少的数据，大部分数据还是要回表查询（根据索引中的主键值去表中查找其他列的值）。这个回表操作是很耗时的。
全表扫描可能更快： 如果全表扫描的代价低于索引扫描+回表的代价，优化器就会选择全表扫描，而放弃使用索引。

举个更形象的例子：

假设你要在图书馆里找到所有姓“张”的书。图书馆管理员告诉你，有一个索引可以按照姓氏查找。但是，图书馆里有 80% 的书都姓“张”。

使用索引： 你需要先查阅索引，找到所有姓“张”的书的架位，然后逐个去这些架位上拿书。这个过程需要频繁地在索引和书架之间切换，非常麻烦。
全表扫描： 你直接从第一个书架开始，一本本地看，只要看到姓“张”的书就拿走。虽然要遍历整个图书馆，但是省去了在索引和书架之间切换的麻烦。

在这种情况下，全表扫描可能比使用索引更快。数据库优化器也是这么想的！

索引失效的常见场景：

性别、状态等基数很低的列： 像gender、status、is_deleted等列，通常只有少数几个值，选择性很低，不适合建立索引。
对索引列进行函数运算： 例如 WHERE UPPER(name) = 'ZHANGSAN'。函数运算会导致索引失效，因为数据库无法直接使用索引来匹配转换后的值。
隐式类型转换： 例如 WHERE phone = 1234567890。如果phone列是字符串类型，数据库会将1234567890转换为字符串，导致索引失效。
使用了NOT、!=、<>等否定操作符： 这些操作符通常会导致全表扫描，因为数据库无法有效地使用索引来排除不需要的数据。
OR条件： 如果OR条件的两边都使用了索引，数据库可能会选择使用索引合并（Index Merge）。但是，如果OR条件的一边没有索引，或者索引选择性很低，数据库可能会选择全表扫描。
组合索引没有使用到最左前缀： 例如，你创建了一个组合索引idx_name_age (name, age)，但是你的查询条件只有age，没有name，那么这个索引就无法使用。

如何避免索引失效？

既然知道了索引失效的原因，我们就可以采取相应的措施来避免它：

选择合适的索引列： 优先选择唯一值较多的列，避免在基数低的列上建立索引。
避免在索引列上进行函数运算： 如果必须进行函数运算，可以考虑创建一个函数索引。
注意数据类型的一致性： 确保查询条件的数据类型与索引列的数据类型一致，避免隐式类型转换。
尽量避免使用否定操作符： 可以考虑使用其他方式来表达相同的逻辑。例如，可以使用IN代替NOT IN。
优化OR条件： 尽量将OR条件拆分成多个UNION ALL查询，或者确保OR条件的两边都使用了高效的索引。
遵循最左前缀原则： 在使用组合索引时，要确保查询条件包含了索引的最左边的列。
定期分析表： 数据库优化器会根据表的统计信息来选择执行计划。定期分析表可以更新统计信息，帮助优化器做出更明智的选择。
使用EXPLAIN命令： EXPLAIN命令可以帮助你查看查询的执行计划，了解数据库是否使用了索引，以及索引的使用情况。

一个更复杂的例子：

假设我们有一个orders表，包含以下字段：

id (主键，自增)
user_id (用户ID)
order_time (下单时间)
status (订单状态，例如 "待付款"、"已付款"、"已发货"、"已完成"、"已取消")

现在，我们想要查询某个用户在指定时间范围内，某个状态的订单数量：

SELECT COUNT(*)
FROM orders
WHERE user_id = 123
  AND order_time BETWEEN '2023-01-01' AND '2023-01-31'
  AND status = '已付款';

在这个例子中，我们可以考虑以下索引：

idx_user_id (user_id)：如果用户数量很多，user_id的选择性较高，可以考虑建立索引。
idx_order_time (order_time)：如果查询的时间范围较小，order_time的选择性较高，可以考虑建立索引。
idx_status (status)：如果订单状态只有少数几个值，status的选择性很低，不建议单独建立索引。
idx_user_id_order_time (user_id, order_time)：可以建立一个组合索引，同时包含user_id和order_time，可以同时优化user_id和order_time的查询条件。

最佳实践：

不要过度索引： 索引会占用存储空间，并且会增加数据修改的开销。只在必要的列上建立索引。
定期审查索引： 随着数据的变化，索引的有效性可能会降低。定期审查索引，删除不再使用的索引。
了解你的数据： 深入了解你的数据，包括数据的分布、数据的访问模式等，才能做出更明智的索引决策。

总结：

索引选择性是评估索引效果的重要指标。选择性高的索引可以有效地提高查询速度，而选择性低的索引反而可能导致索引失效。要避免索引失效，我们需要选择合适的索引列，避免在索引列上进行函数运算，注意数据类型的一致性，尽量避免使用否定操作符和OR条件，遵循最左前缀原则，定期分析表，并使用EXPLAIN命令来分析查询的执行计划。

记住，索引不是万能的，不要盲目地添加索引。要根据实际情况，权衡索引带来的好处和坏处，做出最佳的选择。

好了，今天的分享就到这里。希望大家能够对索引选择性有一个更深入的了解。下次再见！👋

发表回复 取消回复

发表回复取消回复