一.单表索引
即查询只涉及一张表
1.单值索引
尽量选择过滤性较好的列来建立索引。
比如要从学生表里列出来自成都市(完整数据中包含了很多城市)的女生,现在city和gender这两列中只能选其中一个列来建立索引,那么肯定选择city来建立索引。
通常我们可以从show index from table 结果中的cardinality这列来看谁的过滤性较好,一般来说cardinality值越大的索引过滤性越好。
如上图所示,我们给员工的编号(empno)和员工年龄(age)分别都加上了索引,从cardinality来看,idx_empno的过滤性要好。
EXPLAIN SELECT * FROM emp WHERE age=40 AND empno<100100;
现在我们想查询年龄为40,并且编号小于100100的员工。当两个索引都适用于本查询时,会选择过滤性较好的那个,即idx_empno,可以看到查询的列为99列。
如果我们把idx_empno删了,只剩idx_age,结果如下:
可以看到,查询的列变成了43776.效率变差很多。
2.组合索引
- 选择多个变量建立组合索引时,对当前查询过滤效果越好的字段在组合索引中位置越前
- where涉及多个变量时,建立的而索引尽量包含这些变量
- 当某个变量出现范围查询时(比如<,>等),尽量把这个字段放在联合索引的后面
3.索引失效
- 在索引涉及到的变量上进行额外操作(比如计算、函数、(自动或手动)类型转换),会导致索引失效。
e.g.
CREATE INDEX idx_name ON emp(NAME); #给name建立一个索引
EXPLAIN SELECT * FROM emp WHERE LEFT(NAME,4)='odyj'; #在name上进行了函数操作(lfet)
可以看到,idx_name这个索引并没有被使用
EXPLAIN SELECT * FROM