答GavinGeng
联合索引列序这个事,我踩过的坑比看过的教程都多。先说结论:列序不是按选择性从高到低排就完事了,决定性因素其实是「你的查询怎么用这个索引」。
最容易被忽略的一点是最左前缀和范围查询的相互作用。等值条件(=、IN)能一直往下走,但只要某一列用了范围(>、<、BETWEEN、LIKE 'x%'),它右边那些列在索引里就排不了序、用不上了。所以我现在的习惯是:把所有等值条件列放左边,把那个唯一的范围列放最右。有一次我把 (created_at, user_id) 建了索引,查询却是按 user_id 等值 + created_at 范围,结果 created_at 这个范围列卡在中间,user_id 之后索引直接断,慢查询日志里全是全表扫。后来调成 (user_id, created_at) 才解决。
第二是排序和分组的利用。如果一条查询常 ORDER BY a, b,把 a、b 按相同顺序塞进索引尾部,优化器就能直接按索引顺序吐数据,省掉 filesort。这个比单纯追求高选择性实用得多,因为排序带来的临时表和文件排序开销常常比回表还狠。
第三是覆盖索引。SELECT 里要返回的列如果不多,干脆作为尾列include进索引(MySQL 用 INVISIBLE/二级索引冗余列,PG 用 INCLUDE),能彻底免回表。我见过一条高频查询从「索引+回表」变成纯索引扫描,RT 直接掉一截。
边界值查询要单独提一句:BETWEEN 是闭区间,和 >、< 的开闭搞混很容易多取或少取一行,数据对不上还很难查。另外边界往往落在数据分布两端,统计信息一过期优化器就误判行数、乱选索引,这时候 ANALYZE TABLE / UPDATE STATISTICS 一下比改索引更立竿见影。
最后一句提醒:WHERE 里对列套函数、或者隐式类型转换(字符串列传了数字),索引直接废。这个和列序无关,但经常被人当成「索引建错了」来折腾。
所以选列序时我一般先列清楚这条查询的访问模式:等值有哪些、范围是哪列、排序列是什么、要返回哪些列,再反过来排索引,而不是盯着选择性算半天。