优化SQL最重要点就是避免全文搜索，下面我会举几个例子，用explain解析优化策略：
前面工作：
create table test1(
id int(11) not null auto_increment,
c1 varchar(32),
c2 varchar(32),
c3 varchar(32),
primary key (id)
);
在test1表加索引：
create index idx_test on test1(c1(10),c2(10));
PS:
explain的type：
表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
1.避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
explain select * from test1
explain select * from test1 where c1 = '111'
我们可以看到没加where条件时，type是ALL（全表扫描），加了where的c1（索引）条件后type是ref，即哪些列或常量被用于查找索引列上的值。显然非全表扫描会快很多。
2.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
explain select * from test1 where c1 = 'aaa' order by c3;
explain select * from test1 order by c1;
我们看到order by即使是用到了c1索引，type依然为ALL全表扫描。
3.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。测试如下：
explain select * from test1 where c1 like '%a%';
explain select * from test1 where c1 like 'a%';
4.不要在列上进行运算或者对字段进行函数操作
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′
explain select * from test1 where id/2 = 2;
explain select * from test1 where id = 2*2;
5.MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。
建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
explain select * from test1 where c1 < 'a';
explain select * from test1 where c1 != 'aaa';
explain select * from test1 where c1 in ('aaa', 'aaa2');
explain select * from test1 where c1 not in ('aaa', 'aaa2');
6.避免使用or逻辑
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
explain select * from test1 where c1 = 'aaa' or c2 = 'bbb';
explain
select * from test1 where c1 = 'aaa'
union all
select * from test1 where c2 = 'bbb';
7.慎用in和not in逻辑
in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t1 where num in(select id from t2 where id > 10)
此时外层查询会全表扫描，不使用索引。可以修改为：
select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id
此时索引被使用，可以明显提升查询效率。
explain select * from test1 where c1 in (select c1 from test1);
explain select t1.c1,t1.c2 from test1 t1, (select c1 from test1) t where t1.c1 = t.c1;
8.如果列值单一，索引也可能失效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
9.表格字段类型选择
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
账号名建议用varchar，密码建议用char，varchar是可变字符的，char是固定长度的，密码通过加密后都是等长的字符。
10.查询语法中的字段
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
作者：Michael孟良
链接：https://www.jianshu.com/p/33d9a4828437
随时随地看视频
