一.单表索引
即查询只涉及一张表
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 emp WHERE NAME LIKE 'odyj%';
#改用另一种方式，得到的查询结果一样，但是使用到了索引（见下图）
- 索引涉及到的变量如果是不等于(即!= 或 <>)，索引失效
e.g.
- 索引涉及到的变量如果是is not null，索引失效（但is null 不失效）
e.g. - 组合索引中，若前面的变量是范围查找，那么该变量后面的索引变量失效。
e.g. - 从key_len可以看出，idx_age_depid_name这个索引并没有被全部使用（全部使用的话，key_len应该是72）
- 索引涉及到的变量如果使用like，并以通配符(%,_)开头的话，会是索引失效
e.g. - 字符串不加引号会使索引失效
e.g. - （PS：像第二种这个，其实SQL在执行时，强制给你加了引号，相当于进行了自动的类型转换(从int转换为字符串)）
二.多表情况
- 当使用外连接（left join or right join）时，要让大表做主表，小表做从表。
比如我们之前那个例子，员工有50万个，部门有1万个。我们要将两张表通过部门id连接起来。那么由于部门信息较少，所以我们应该使用部门信息表作为主表，避免出现更多的null值。 - 外连接时，要把索引建在从表上
e.g.
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; #主表和从表的card都没有建立索引
CREATE INDEX idx_ccard ON class(card); #在主表上建立索引
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
可以看到，在主表上建立索引，虽然type变了，但是需要搜索的行数并没有减少。
CREATE INDEX idx_bcard ON book(card); #在从表上建立索引
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
可以看到，在从表上建立索引之后，需要搜索的行数明显减少。
原因在于，外连接时，主表的信息本来就要全部出现在结果之中，所以在主表上建立索引的效果差于在从表上建立索引的效果。
PS：inner join时，mysql会自动选择小表作为主表。
-子查询尽量不要放在从表，有可能使用不到索引；能够直接多表关联的尽量直接关联，不用子查询。
三.子查询优化
-子查询尽量不要使用not in 或者 not exists，如果有，那么尽量使用多表关联查询然后用is null 来选出符合条件的观测
e.g.
使用子查询
EXPLAIN
SELECT age,COUNT(*) FROM t_emp a
WHERE id NOT IN(SELECT ceo FROM t_dept b WHERE ceo IS NOT NULL) #我不知道是不是因为有一个部门没有负责人(即该部门的CEO为null，所以这里才必须添加一个is not null的
GROUP BY age;
使用多表关联
EXPLAIN
SELECT age,COUNT(*) FROM t_emp a
LEFT JOIN t_dept b
ON b.`CEO` = a.`id`
WHERE b.`CEO` IS NULL
GROUP BY a.age;
- 当范围条件(比如where var1<100）与order by涉及到的变量不一样时，索引该如何建立？
e.g.
想要找出年龄为30，empno小于100100的员工，并且按姓名排序
#不建索引
EXPLAIN
SELECT *
FROM emp
WHERE age=30 AND empno<100100
ORDER BY NAME;
可以看到，效率很低。那么索引该如何建立呢？该查询一共涉及到3个变量：age、empno、name，考虑建一个包含3个变量的索引？
CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
EXPLAIN
SELECT *
FROM emp
WHERE age=30 AND empno<100100
ORDER BY NAME;
可以看到row有明显的下降，但是索引变量并没有全部使用完(key_len只有9），并且using filesore仍然存在。这是因为empno是一个范围查询(empno<100100)，导致索引中的name变量失效。也就是说，在本例中，建立idx_age_empno_name这个3变量索引的效果其实和建立idx_age_empno这个2变量索引的效果是一样的
CREATE INDEX idx_age_empno ON emp(age,empno);
EXPLAIN
SELECT *
FROM emp
WHERE age=30 AND empno<100100
ORDER BY NAME;
那如果建立age和name的索引呢？
CREATE INDEX idx_age_name ON emp(age,NAME);
EXPLAIN
SELECT *
FROM emp
WHERE age=30 AND empno<100100
ORDER BY NAME;
从key_len的长度可以看出，在这个索引中，name还是没有被用到，但是using filesort已经没有了。然而rows却变成了47988行。
这样一比较，其实感觉使用idx_age_empno的效果还要好一点。
问题：为什么name没有走索引，但是using filesort没有了？？？？！！！！
原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
结论： 当where中出现范围查询，且和order by 的变量不一样时 ，优先观察where变量的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围查询的字段上。反之，亦然。
