一、索引选择性是什么鬼？
咱们先来个接地气的解释。索引选择性就像是你去图书馆找书，如果图书管理员告诉你"书在第三排架子"，这信息就很有用（选择性高）；但如果他说"书在文学区"，那你还得在几百本书里慢慢找（选择性低）。在数据库里，索引选择性就是衡量索引能帮我们缩小多少搜索范围的能力。
计算公式很简单：
选择性 = 不同索引值的数量 / 表总行数
举个OceanBase的例子（技术栈：OceanBase 4.x）：
-- 创建测试表
CREATE TABLE user_orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT,
order_status VARCHAR(20),
create_time DATETIME,
INDEX idx_user_id (user_id),
INDEX idx_status (order_status)
);
-- 插入测试数据（假设我们插入了100万条订单数据）
-- 其中user_id有10万个不同值，order_status只有5种状态
-- 计算user_id索引的选择性
SELECT
COUNT(DISTINCT user_id) / COUNT(*) AS selectivity
FROM user_orders;
-- 结果约0.1（10万/100万）
-- 计算order_status索引的选择性
SELECT
COUNT(DISTINCT order_status) / COUNT(*) AS selectivity
FROM user_orders;
-- 结果0.000005（5/100万）
从上面的例子能明显看出，user_id索引的选择性要比order_status高得多。这就意味着：
- 用user_id查订单，能快速定位到约10条记录（100万/10万）
- 用order_status查订单，会找到约20万条记录（100万/5）
二、怎么评估索引有没有用？
光知道选择性还不够，我们得会判断索引的实际效果。这里教大家几个实用技巧：
1. 执行计划分析法
OceanBase提供了EXPLAIN命令，让我们能看到查询到底用没用索引：
-- 查询特定用户的订单（高选择性）
EXPLAIN SELECT * FROM user_orders WHERE user_id = 123456;
-- 结果会显示使用了idx_user_id索引
-- 查询所有已完成的订单（低选择性）
EXPLAIN SELECT * FROM user_orders WHERE order_status = 'completed';
-- 可能会显示全表扫描，因为索引效率太低
2. 实际查询耗时对比
有时候执行计划说用了索引，但实际还是很慢。这时候我们需要实际测试：
-- 启用计时
SET TIMING ON;
-- 高选择性查询
SELECT * FROM user_orders WHERE user_id = 123456;
-- 可能只需要几毫秒
-- 低选择性查询
SELECT * FROM user_orders WHERE order_status = 'completed';
-- 可能需要几百毫秒甚至秒级
3. 索引使用统计
OceanBase的系统表记录了索引使用情况：
-- 查询索引使用统计
SELECT
index_name,
rows_selected,
rows_updated
FROM oceanbase.__all_virtual_table_index_stat
WHERE table_name = 'user_orders';
如果发现某个索引的rows_selected很低但rows_updated很高，说明这个索引可能得不偿失。
三、优化索引的实战技巧
知道了问题，接下来就是怎么优化了。分享几个我在OceanBase上常用的优化方法：
1. 复合索引的黄金法则
对于多条件查询，复合索引往往比单列索引更有效。记住这个口诀："等值在前，范围在后，排序跟着走"。
-- 优化前：两个单列索引
CREATE INDEX idx_user ON user_orders(user_id);
CREATE INDEX idx_status ON user_orders(order_status);
-- 优化后：复合索引
CREATE INDEX idx_user_status ON user_orders(user_id, order_status);
-- 这样既能高效查询特定用户的状态订单
SELECT * FROM user_orders
WHERE user_id = 123456 AND order_status = 'completed';
2. 函数索引的妙用
有时候我们需要对字段做计算或转换，这时候普通索引就失效了。OceanBase支持函数索引：
-- 假设我们经常要按月份查订单
CREATE INDEX idx_month ON user_orders(DATE_FORMAT(create_time, '%Y-%m'));
-- 现在按月查询就能用索引了
SELECT * FROM user_orders
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-10';
3. 覆盖索引减少IO
如果索引能包含查询需要的所有列，就不用回表查数据了：
-- 优化前：需要回表
SELECT order_status, create_time FROM user_orders
WHERE user_id = 123456;
-- 优化后：创建包含所有需要字段的索引
CREATE INDEX idx_user_cover ON user_orders(user_id, order_status, create_time);
-- 现在查询只需要扫描索引，不需要访问表数据
4. 定期维护索引统计信息
OceanBase的优化器依赖统计信息来做决策，所以要及时更新：
-- 手动收集统计信息
ANALYZE TABLE user_orders;
-- 或者设置自动收集（OceanBase 4.x支持）
ALTER TABLE user_orders SET STATS_AUTO_SAMPLE = TRUE;
四、什么时候不该用索引？
索引不是万能的，有些情况下加索引反而会降低性能：
- 超小表：数据量小于1000行的表，全表扫描可能更快
- 频繁更新的列：每次更新都要维护索引，开销很大
- 低选择性列：比如性别、是否删除这种只有几个值的字段
- 大文本字段：文本太长会导致索引体积暴增
-- 不建议加索引的例子
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY,
gender CHAR(1), -- 只有'M','F'两个值
is_deleted TINYINT, -- 只有0和1
resume TEXT -- 大文本
);
-- 这些索引可能弊大于利
CREATE INDEX idx_gender ON user_profiles(gender);
CREATE INDEX idx_resume ON user_profiles(resume(255)); -- 前缀索引也很占空间
五、真实案例分享
去年我们遇到一个典型问题：一个订单查询接口白天很慢，晚上却很快。排查后发现：
- 白天查询条件复杂，优化器选错了索引
- 晚上数据量少，全表扫描反而快
解决方案是：
-- 1. 创建更适合的复合索引
CREATE INDEX idx_query_optimized ON orders(region, status, create_time);
-- 2. 用INDEX HINT强制使用正确索引
SELECT /*+ INDEX(orders idx_query_optimized) */ *
FROM orders
WHERE region = 'east' AND status = 'processing'
ORDER BY create_time DESC;
-- 3. 定期在低峰期执行ANALYZE
这个优化让查询速度从2秒降到了200毫秒以内。
六、总结与最佳实践
经过上面的分析，给大家总结几个OceanBase索引优化的黄金法则：
- 选择性大于0.1的列才考虑加索引
- 复合索引的顺序要符合查询模式
- 定期维护统计信息
- 监控索引的实际使用情况
- 不要过度索引，每个额外索引都会增加维护成本
最后记住：索引优化是个持续的过程，随着数据量和查询模式的变化，需要不断调整优化策略。
评论