数据库调优-索引:创建索引的原则(参考)

2020-11-04   38 次阅读


建议创建索引的场景

  • select语句,频繁作为where条件的字段
# 经常需要使用first_name作为查询条件,那么就可以为first_name创建一个索引
select *
from employees
where first_name = 'Georgi';
-- index(first_name)

# 经常需要使用first_name和last_name去筛选数据,就应该创建一个组合索引
select *
from employees
where first_name = 'Georgi'
  and last_name = 'Cools';
-- index(first_name, last_name)
-- 在创建组合索引的时候,要考虑最左前缀原则
-- index(last_name, first_name) ==> where first_name = 'Georgi' 
-- 无法使用该索引
  • update/delete语句的where条件
# update/delete语句,where条件的字段一般也要创建索引
update employees
set first_name = 'Jim'
where emp_no = '100001';

delete 
from employees
where first_name = 'Georgi';
-- 这里的update语句就应该为emp_no创建索引
-- 但是我们这里的emp_no是主键,不需要额外创建索引
-- 这里的delete语句就应该为first_name创建索引
-- 这是因为update/delete语句,他需要先根据where条件查询出对应的数据
-- 然后在对这些数据进行更新/删除
  • 需要分组、排序的字段
select dept_no, count(*)
from dept_emp
order by dept_no;

select dept_no, count(*)
from dept_emp
group by dept_no;
-- dept_no可以设置索引
  • distince所使用的字段
select distinct(first_name)
from employees;
-- first_name可以设置索引
  • 字段的值有唯一性约束

  • 对于多表查询,联接的字段应创建索引,且类型务必保持一致

    • 避免隐式转换(隐式转换可能会导致索引无法使用)
select emp.*, d.dept_name
from employees emp
    left join dept_emp de
        on emp.emp_no = de.emp_no
    left join departments d
        on de.dept_no = d.dept_no
where de.emp_no = '100001';
-- emp.emp_no = de.emp_no 的类型要一致,
-- on de.dept_no = d.dept_no 的类型要一致,
-- 否则就可能导致隐式转换,从而导致索引可能无法使用

不建议创建索引的场景

  • where子句里用不到的字段

  • 表的记录非常少

  • 某个字段有大量重复数据,选择性低(如性别)

    • 索引的选择性越高,查询效率越好,因为可以在查找时过滤更多行
      这也是唯一索引比普通索引查询效率好的原因
  • 频繁更新的字段,如果创建索引要考虑其索引维护开销
    维护索引是需要开销的,在修改或者删除数据的时候,也需要更新索引,如果某一个字段修改非常的频繁,而查询很少的话,那么不建议为这个字段创建索引

注意

原则只是参考,不必要死守教条

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

那一年,我也变成了光!!