索引选择性（Selectivity）与索引失效分析

好的，各位观众，大家好！我是今天的主讲人，江湖人称“数据库小诸葛”（别笑，是自封的😎）。今天，咱们聊点数据库里的小秘密，保证让各位听得津津有味，看完茅塞顿开。

今天要讲的主题是——索引选择性（Selectivity）与索引失效分析。听起来好像很高大上，但其实啊，它就像咱们平时挑水果，选对了甜到心里，选错了酸掉大牙！

一、索引：数据库的“高速公路”🛣️

首先，咱们得明白索引是啥。想象一下，你手里有一本厚厚的《新华字典》，要查“魑魅魍魉”这几个字，如果没有目录，你是不是得从头翻到尾，累个半死？有了目录（索引），咻的一下就找到了，省时省力！

索引在数据库里也是一样，它是一种数据结构，能够帮助数据库系统快速定位到符合查询条件的数据行，避免全表扫描，提高查询效率。简单来说，索引就是数据库的“高速公路”，能让查询跑得更快。

但是，高速公路也不是随便上的，得有合适的入口和出口，否则反而会堵车！这就是我们今天要聊的重点——索引选择性。

二、索引选择性：高速公路的“利用率”🚦

什么是索引选择性呢？咱们先来个形象的比喻：

想象一下，你在一个人口密集的城市里修了一条高速公路，结果发现每天只有寥寥几辆车通过，那这条高速公路是不是就浪费了？反之，如果在一个人烟稀少的村庄修了一条高速公路，那可能根本就没人用，也毫无意义。

索引选择性就是衡量索引价值的重要指标。它指的是索引列中唯一值的比例。

计算公式：

索引选择性 = 索引列唯一值数量 / 表的总行数

选择性越高，说明索引列的区分度越高，索引的价值也就越大。

举个栗子🌰：

假设我们有一张名为users的用户表，有100万条记录，其中包含id、name、age、gender、city等字段。

字段	数据类型	示例值
id	INT	1
name	VARCHAR	张三
age	INT	25
gender	ENUM(‘男’, ‘女’)	男
city	VARCHAR	北京

• id字段： 每条记录的id都是唯一的，所以id字段的唯一值数量是100万，索引选择性是 100万 / 100万 = 1。 选择性高！
• gender字段： gender字段只有两个值（男、女），所以gender字段的唯一值数量是2，索引选择性是 2 / 100万 = 0.000002。 选择性极低！
• city字段： city字段可能有很多不同的城市，假设有100个，那么city字段的唯一值数量是100，索引选择性是 100 / 100万 = 0.0001。 选择性较低！

总结：

• 选择性越高，索引效果越好。 当查询条件是id = 123时，数据库可以直接通过id索引快速定位到目标数据行，就像精准制导导弹一样。
• 选择性越低，索引效果越差。 当查询条件是gender = '男'时，由于gender字段的选择性极低，大部分数据行都符合条件，数据库即使使用了gender索引，也需要扫描大量数据行，甚至还不如直接全表扫描。

形象比喻：

• id字段就像每个人的身份证号码，独一无二，用身份证号码找人，效率最高。
• gender字段就像性别，只有两种可能，用性别找人，重名重姓的太多了，效率很低。

三、索引失效：高速公路“封闭施工”🚧

索引并非万能的，有时候，即使你创建了索引，数据库也可能不会使用它，这就是所谓的“索引失效”。索引失效就像高速公路封闭施工，查询只能走“国道”，速度慢得让人抓狂。

那么，哪些情况会导致索引失效呢？

1. 模糊查询（LIKE）以%开头：

   SELECT * FROM users WHERE name LIKE '%张';  -- 索引失效
   SELECT * FROM users WHERE name LIKE '张%';  -- 索引有效

   为什么呢？因为以%开头的模糊查询，数据库无法利用索引的前缀匹配特性，只能全表扫描。想象一下，你在字典里查一个以“？”开头的词语，是不是也只能从头翻到尾？

2. 使用函数或表达式：

   SELECT * FROM users WHERE YEAR(birthday) = 1990; -- 索引失效
   SELECT * FROM users WHERE age + 10 > 30;        -- 索引失效

   当查询条件中使用了函数或表达式时，数据库无法直接利用索引中的原始值进行比较，只能先计算出结果，再进行匹配，导致索引失效。这就好比，你拿着一张加密过的身份证去查户口，警察叔叔得先解密才能查到你，效率自然就低了。

3. 数据类型不匹配：

   SELECT * FROM users WHERE phone = 13800000000;  -- 索引有效，假设phone是VARCHAR类型，且有索引
   SELECT * FROM users WHERE phone = '13800000000'; -- 索引有效
   SELECT * FROM users WHERE phone = '13800000000'+0; -- 索引失效，隐式转换

   如果查询条件中的数据类型与索引列的数据类型不匹配，数据库可能会进行隐式类型转换，导致索引失效。这就好比，你拿着一张英文版的身份证去办理业务，工作人员可能需要翻译，才能识别你的身份。

4. 组合索引未遵循最左前缀原则：

   假设我们创建了一个组合索引idx_name_age，包含name和age两个字段。

   SELECT * FROM users WHERE name = '张三' AND age = 25;  -- 索引有效
   SELECT * FROM users WHERE name = '张三';                 -- 索引有效
   SELECT * FROM users WHERE age = 25;                    -- 索引失效

   组合索引的最左前缀原则是指，查询条件必须包含组合索引的最左边的字段，才能使用该索引。如果查询条件只包含组合索引的非最左边的字段，索引就会失效。这就好比，你拿着一张火车票，必须先检票才能上车，如果直接拿着票上车，是会被罚款的。

5. OR条件：

   SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 大部分情况下索引失效

   当使用OR条件时，如果OR连接的两个字段都有索引，数据库可能会选择使用索引合并（Index Merge），但这种情况下，通常不如直接全表扫描效率高，因此，数据库可能会放弃使用索引。

   例外: 如果OR条件连接的是同一个字段，且该字段有索引，则索引仍然有效。

   SELECT * FROM users WHERE age = 25 OR age = 30;  -- 索引有效

6. NOT IN / != / <> 操作符：

   SELECT * FROM users WHERE age NOT IN (20, 25, 30);  -- 索引失效
   SELECT * FROM users WHERE age != 25;              -- 索引失效
   SELECT * FROM users WHERE age <> 25;              -- 索引失效

   这些操作符通常会导致数据库无法有效地利用索引，因为它们需要排除索引中的大量数据，还不如直接全表扫描。

7. 全表扫描更快：

   有时候，即使查询条件能够使用索引，但数据库经过评估后认为，全表扫描比使用索引更快，就会放弃使用索引。这种情况通常发生在查询结果集占表的大部分数据时。

   例如，gender字段的选择性极低，查询gender = '男'时，可能需要返回表中一半的数据，数据库可能会认为全表扫描更有效率。

四、如何避免索引失效？🤔

既然索引失效这么可怕，那我们该如何避免呢？

1. 避免模糊查询以%开头：

   尽量避免使用LIKE '%xxx'，如果必须使用模糊查询，可以考虑使用全文索引或者其他搜索技术。

2. 避免在查询条件中使用函数或表达式：

   尽量将函数或表达式放在等号的右边，例如：

   -- 优化前
   SELECT * FROM users WHERE YEAR(birthday) = 1990;
   
   -- 优化后
   SELECT * FROM users WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';

3. 保持数据类型一致：

   确保查询条件中的数据类型与索引列的数据类型一致，避免隐式类型转换。

4. 遵循最左前缀原则：

   在使用组合索引时，确保查询条件包含组合索引的最左边的字段。

5. 尽量避免使用OR条件：

   如果必须使用OR条件，可以考虑使用UNION ALL或者其他方式进行优化。

6. 慎用NOT IN / != / <>操作符：

   尽量使用其他方式替代这些操作符，例如，可以使用BETWEEN或者IN来替代NOT IN。

7. 定期分析表和索引：

   定期使用数据库的分析工具（例如，MySQL的ANALYZE TABLE命令）来更新表的统计信息，以便数据库能够做出更合理的查询计划。

五、实战演练：索引优化案例 👨‍💻

咱们来个实际的例子，假设我们有一个orders订单表，包含order_id、user_id、order_time、order_amount等字段。

• order_id是主键。
• 我们经常需要根据user_id和order_time查询订单。

优化前：

SELECT * FROM orders WHERE user_id = 123 AND order_time BETWEEN '2023-01-01' AND '2023-12-31';

如果没有合适的索引，这个查询可能会很慢。

优化方案：

1. 创建组合索引：

   CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time);

   这样，数据库就可以利用这个组合索引快速定位到符合条件的订单。

2. 优化查询语句：

   如果查询中使用了函数或者表达式，可以尝试进行优化，例如：

   -- 优化前
   SELECT * FROM orders WHERE YEAR(order_time) = 2023;
   
   -- 优化后
   SELECT * FROM orders WHERE order_time BETWEEN '2023-01-01' AND '2023-12-31';

六、总结：索引的艺术 🎨

索引的选择性和索引失效分析是一门艺术，需要我们不断学习和实践，才能掌握其中的精髓。记住，索引不是越多越好，也不是越大越好，而是要根据实际情况，选择合适的索引，并避免索引失效。

希望今天的分享对大家有所帮助，如果大家有什么问题，欢迎随时提问。

小贴士：

• 可以使用EXPLAIN命令来查看数据库的查询计划，了解数据库是否使用了索引，以及使用了哪个索引。
• 不同的数据库系统在索引实现和优化方面可能存在差异，需要根据具体的数据库系统进行调整。

好了，今天的分享就到这里，感谢大家的观看！下次再见 👋！