复合索引 order by 语句的写法

复合索引 order by 语句的写法

• 多列排序使用组合索引

• 创建索引

• create index idx_a_b on table_user (a,b);

• 多列排序要遵循最左原则

• select * from order by a,b limit 10 ,

• select * from order by a limit 10

• 不走索引的情况

  • select * from order by b limit 10;
  • select * from order by b,a limit 10;

• 多列排序 升序和降序需要一致 (默认 ASC 升序)

  • select * from order by a DESC, b DESC

• 不走索引的情况

  • select * from order by a ASC , b DESC 索引失效

-- 千万级别的数据

--  查看当前 连接客户端的所有 session 的数量
SHOW PROCESSLIST

show index from user

-- 创建索引
create index `idx_sex_pass` on `user`(sex,password)

-- 测试是否通过索引排序  0.025秒
select * from user order by sex, password limit 10

-- 最左单个字段 排序 0.019s (索引生效)
select * from user order by sex limit 10

-- 跳过第一个索引字段, 通过 password 排序, (索引失效, 全表扫描) 8.055s
EXPLAIN select * from user order by password  limit 10

-- 将联合索引字段 顺序颠倒  (索引失效, 全表扫描)  8.392s
select * from user order by password ,sex  limit 10