避免索引失效的一些原则

1.复合索引，不要跨列或无序使用，否则会使组合索引失效
复合索引，尽量使用全索引匹配

2.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;
//用到了at2个索引
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid*2 = 2;
//用到了a1个索引
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid*2 = 2;
//用到了0个索引
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid = 2;
//用到了0个索引
//原因：对于复合索引，左边失效了右边全部失效。（a,b,c）中b失效，那b,c都失效，a没有失效。（如果不是复合索引，则没有关系）

DROP INDEX idx_atb ON book;
ALTER TABLE book ADD INDEX idx_authroid(authroid);
ALTER TABLE book ADD INDEX idx_typeid(typeid);
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid = 2;

3.复合索引不能使用不等于（！=或<>）或is null(is not null),否则自身以及右侧索引全部失效。
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;

只用了一个索引，原因：
–SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测
EXPLAIN SELECT * FROM book WHERE authorid != 1 AND typeid = 2;

上面的结论是针对复合索引，对单独的索引没有上述影响。

体验概率情况：
　　原因是服务层中有SQL优化器，可能影响我们的优化。

show index from book;
drop index idx_typeid on book;
drop index idx_authorid on book;
alter table book add index idx_book_at(authorid,typeid);

explain select * from book where authorid = 1 and typeid = 2;
//复合索引全部使用，不同于单独索引
explain select * from book where authorid >1 and typeid = 2;
//复合索引中如果有大于号，则自身和右侧索引全部失效。
explain select * from book where authorid =1 and typeid > 2;
//复合索引at全部使用(**体验概率**情况)

—明显的概率问题—

explain select * from book where authorid < 1 and typeid = 2;//复合索引at中只用到了一个索引
explain select * from book where authorid < 4 and typeid = 2;//复合索引at全部失效

　　明显发现改个数字索引的使用情况都不一样，说明sql优化索引的情况是个概率问题，我们用的优化手段通常都是大部分适用的结论，但由于sql优化器的原因，该结论不是100%正确。
一般而言，范围查询（> < in），之后的索引失效。

4.思考：那到底有没有100%使用索引的情况呢？
答案：有，索引覆盖（using index）—尽量使用索引覆盖

5.like尽量以“常量”开头，不要以‘%’开头，否则索引失效。

select * from xx where name like '%x%';//name索引失效
show index from teacher;

explain select * from teacher where tname like '%x%';//tname索引失效
explain select * from teacher where tname like 'x%';//tname索引有效
explain select tname from teacher where tname like '%x%';//如果必须使用like '%x%',可以使用索引覆盖，挽救一部分（range->index）。

6.尽量不要使用类型转换（显示、隐式），否则索引失效。

desc teacher;//tname是字符串
show index from teacher;//tname是索引
explain select * from teacher where tname = 'abc';
explain select * from teacher where tname = 123;//程序将123->'123'，即进行了类型转换，因此索引失效

7.尽量不要使用or，否则索引失效

explain select * from teacher where tname = '' and tcid > 1;
explain select * from teacher where tname = '' or tcid > 1;//甚至让左边的索引失效

8.一些其他的优化方法
（1）exist和in
select … from table exist/in (子查询);
如果主查询的数据集大，则使用in，效率高。
如果子查询的数据集大，则使用exist，效率高。
exist语法：
　　将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据与主查询结果相对应，如果有数据则校验成功），如果符合校验，则保留数据。

select tname from teacher where exists (select * from teacher);
//这里子查询中就有主查询的结果
//等价于select tname from teacher;
select tname from teacher where exists (select * from teacher where tid = 999);
//没有数据

（2）order by优化—using firesort
有两种算法：双路排序、单路排序（根据IO的次数）
MySQL4.1之前，默认使用双路排序；
　　双路：扫描两次磁盘（1.从磁盘读取排序字段，对排序字段进行排序（在buffer(缓存区)中进行排序）；2.扫描其他字段）。IO较消耗性能
MySQL4.1之后，默认使用单路排序；
　　只读取一次（全部字段），在buffer中进行排序。但这单路排序会有一定的隐患（不一定真的是“单路 1次IO”，有可能多次IO—原因：如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取、多次读取”）。

注意：单路排序比双路排序会占用更多的buffer。
　　单路排序在使用时，如果数据量大，可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024 单位byte
如果max_length_for_sort_data值太低，则MySQL会自动从单路->双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：
a.选择使用单路、多路；调整buffer的容量大小；
b.避免select * …(->程序还要去计算表示哪些字段)
c.复合索引不要跨列使用，避免using firesort
d.保证全部的排序字段 排序的一致性（都是升序或降序）

9.SQL排查 —慢查询日志：
　　MYSQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值的SQL语句（long_query_time，默认10秒）—一个sql10秒都没查询出来，就是慢sql，需要优化。
慢查询日志默认时关闭的；建议：开发调优时打开，而最终部署时关闭。
检查是否开启了慢查询日志：show variables like ‘%slow_query_log%’;
临时开启：set global slow_query_log = 1;—在内存中开启
exit;
service mysql restart;
show variables like ‘%slow_query_log%’;—off
永久开启：
/etc/my.cnf中追加配置：
vim /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slog.log
慢查询阈值：
show variables like ‘%long_query_time%’;
临时设置阈值：
set global long_query_time = 5;—设置完毕后，重新登陆后起效（不需要重启服务）
永久设置阈值：
/etc/my.cnf中追加配置：
vim /etc/my.cnf
[mysqld]
long_query_time=3；