MySQL如何用组合索引提高分页查询执行效率

60阅读　0评论2025-04-06　dingjun123
分类：Mysql/postgreSQL

1.排序键的排序能够消除，才能快速limit裁剪，如果排序键在访问条件里更好，索引支持desc访问，如果前导列是等值，不用建desc索引。
  如果order by里没有前导列，要消除排序，排序列必须紧跟在等值访问条件索引后面，或者作为前导列，支持desc扫描，不用建desc索引
  如果order by里有前导列，要消除排序，按order by顺序建组合索引即可,但是如果前导列非等值，则后面列如果desc，只能建desc索引
  
  
2.等值访问条件{BANNED}最佳好为前导列，就算选择性不好，也可以减少索引访问的行

3.遵循leftmost prefix规则，不能index访问的条件放到组合索引{BANNED}最佳后，可以起到index filter减少回表的作用

语句如下：

点击(此处)折叠或打开

  1. explain analyze select emp_no,first_name,last_name,hire_date,birth_date
  2. from emp1
  3. where gender='F'
  4. and hire_date between '1985-1-1' and '1995-1-1'
  5. and first_name like 'A%'
  6. order by hire_date desc
  7. limit 100\G
只用first_name列索引，并不能消除排序，要0.12s,这里索引访问rows=22038行，然后还要回表过滤，还要排序，然后才limit：

点击(此处)折叠或打开

  1. *************************** 1. row ***************************
  2. EXPLAIN: -> Limit: 100 row(s) (cost=18744.56 rows=100) (actual time=123.010..123.023 rows=100 loops=1)
  3.     -> Sort: emp1.hire_date DESC, limit input to 100 row(s) per chunk (cost=18744.56 rows=41654) (actual time=123.009..123.018 rows=100 loops=1)
  4.         -> Filter: ((emp1.gender = 'F') and (emp1.hire_date between '1985-1-1' and '1995-1-1')) (cost=18744.56 rows=41654) (actual time=0.071..120.242 rows=7783 loops=1)
  5.             -> Index range scan on emp1 using idx_emp1_firstname over ('A' <= first_name <= 'A????????????????????????????????????????????????????????????'), with index condition: (emp1.first_name like 'A%') (cost=18744.56 rows=41654) (actual time=0.039..113.310 rows=22038 loops=1)

  7. 1 row in set, 1 warning (0.12 sec)

根据规则，建立三个索引，hire_date放到第二列，放到前导列后面，支持descending扫描。。。不能放到第三列，因为first_name不是等值：

点击(此处)折叠或打开

  1. create index idx15_emp1 on emp1(gender,hire_date,first_name);

  3. *************************** 1. row ***************************
  4. EXPLAIN: -> Limit: 100 row(s) (cost=18744.56 rows=100) (actual time=2.465..2.493 rows=100 loops=1)
  5.     -> Index range scan on emp1 using idx15_emp1 over (gender = 'F' AND '1985-01-01' <= hire_date <= '1995-01-01' AND 'A' <= first_name <= 'A????????????????????????????????????????????????????????????') (reverse), with index condition: ((emp1.gender = 'F') and (emp1.hire_date between '1985-1-1' and '1995-1-1') and (emp1.first_name like 'A%')) (cost=18744.56 rows=149716) (actual time=2.463..2.486 rows=100 loops=1)

  7. 1 row in set, 1 warning (0.01 sec)


或者hire_date作为前导列，first_name是次列，hire_date非等值条件，first_name是ICP，也能消除排序：

点击(此处)折叠或打开

  1. explain analyze select emp_no,first_name,last_name,hire_date,birth_date
  2. from emp1
  3. where gender='F'
  4. and hire_date between '1985-1-1' and '1995-1-1'
  5. and first_name like 'A%'
  6. order by hire_date desc
  7. limit 100\G

  9. *************************** 1. row ***************************
  10. EXPLAIN: -> Limit: 100 row(s) (cost=18744.56 rows=100) (actual time=2.037..5.765 rows=100 loops=1)
  11.     -> Filter: (emp1.gender = 'F') (cost=18744.56 rows=10413) (actual time=2.035..5.758 rows=100 loops=1)
  12.         -> Index range scan on emp1 using idx15_emp1 over ('1985-01-01' <= hire_date <= '1995-01-01' AND 'A' <= first_name <= 'A????????????????????????????????????????????????????????????') (reverse), with index condition: ((emp1.hire_date between '1985-1-1' and '1995-1-1') and (emp1.first_name like 'A%')) (cost=18744.56 rows=149716) (actual time=2.031..5.727 rows=256 loops=1)

  14. 1 row in set, 1 warning (0.01 sec)

drop index idx15_emp1 on emp1;
create index idx15_emp1 on emp1(first_name,hire_date);
不能消除排序，因为前导列不是等值，hire_date不能索引访问，且hire_date是desc

点击(此处)折叠或打开

  1. explain analyze select/*+index(emp1 idx15_emp1)*/ emp_no,first_name,last_name,hire_date,birth_date
  2. from emp1
  3. where gender='F'
  4. and hire_date between '1985-1-1' and '1995-1-1'
  5. and first_name like 'A%'
  6. order by first_name,hire_date desc
  7. limit 100\G

  9. *************************** 1. row ***************************
  10. EXPLAIN: -> Limit: 100 row(s) (cost=19098.26 rows=100) (actual time=88.317..88.334 rows=100 loops=1)
  11.     -> Sort: emp1.first_name, emp1.hire_date DESC, limit input to 100 row(s) per chunk (cost=19098.26 rows=42440) (actual time=88.315..88.328 rows=100 loops=1)
  12.         -> Filter: (emp1.gender = 'F') (cost=19098.26 rows=42440) (actual time=0.056..84.748 rows=7783 loops=1)
  13.             -> Index range scan on emp1 using idx15_emp1 over ('A' <= first_name <= 'A????????????????????????????????????????????????????????????' AND '1985-01-01' <= hire_date <= '1995-01-01'), with index condition: ((emp1.hire_date between '1985-1-1' and '1995-1-1') and (emp1.first_name like 'A%')) (cost=19098.26 rows=42440) (actual time=0.037..82.290 rows=19481 loops=1)

  15. 1 row in set, 1 warning (0.09 sec)


如果hire_date不是desc,order by里将所有索引列直到hire_date都加上，hire_date没有desc,就算前导列是范围，也可以走索引消除排序：


点击(此处)折叠或打开

  1. explain analyze select/*+index(emp1 idx15_emp1)*/ emp_no,first_name,last_name,hire_date,birth_date
  2. from emp1
  3. where gender='F'
  4. and hire_date between '1985-1-1' and '1995-1-1'
  5. and first_name like 'A%'
  6. order by first_name,hire_date
  7. limit 100\G

  9. *************************** 1. row ***************************
  10. EXPLAIN: -> Limit: 100 row(s) (cost=19098.26 rows=100) (actual time=0.068..1.153 rows=100 loops=1)
  11.     -> Filter: (emp1.gender = 'F') (cost=19098.26 rows=2358) (actual time=0.067..1.145 rows=100 loops=1)
  12.         -> Index range scan on emp1 using idx15_emp1 over ('A' <= first_name <= 'A????????????????????????????????????????????????????????????' AND '1985-01-01' <= hire_date <= '1995-01-01'), with index condition: ((emp1.hire_date between '1985-1-1' and '1995-1-1') and (emp1.first_name like 'A%')) (cost=19098.26 rows=42440) (actual time=0.043..1.109 rows=228 loops=1)

  14. 1 row in set, 1 warning (0.00 sec)


因为前导列first_name非等值，虽然在order by里加上first_name,但是hire_date desc,组合索引必须hire_date desc才能消除排序：

点击(此处)折叠或打开

  1. drop index idx15_emp1 on emp1;
  2. create index idx15_emp1 on emp1(first_name,hire_date desc);

  4. explain analyze select/*+index(emp1 idx15_emp1)*/ emp_no,first_name,last_name,hire_date,birth_date
  5. from emp1
  6. where gender='F'
  7. and hire_date between '1985-1-1' and '1995-1-1'
  8. and first_name like 'A%'
  9. order by first_name,hire_date desc
  10. limit 100\G

  12. *************************** 1. row ***************************
  13. EXPLAIN: -> Limit: 100 row(s) (cost=19098.26 rows=100) (actual time=0.089..2.912 rows=100 loops=1)
  14.     -> Filter: (emp1.gender = 'F') (cost=19098.26 rows=2358) (actual time=0.088..2.901 rows=100 loops=1)
  15.         -> Index range scan on emp1 using idx15_emp1 over ('A' <= first_name <= 'A????????????????????????????????????????????????????????????' AND '1995-01-01' <= hire_date <= '1985-01-01'), with index condition: ((emp1.hire_date between '1985-1-1' and '1995-1-1') and (emp1.first_name like 'A%')) (cost=19098.26 rows=42440) (actual time=0.086..2.832 rows=241 loops=1)

  17. 1 row in set, 1 warning (0.01 sec)





上一篇：Oracle peeking关闭动态采样导致走错索引
下一篇：oracle 12c开始分页的三种写法及其下推裁剪优化