mysql查询优化策略中一个重要的策略就是建索引优化，了解索引的前提是了解 InnoDB 存储引擎
下面先简单介绍一下索引
索引介绍
索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于更快地获取信息。 mysql 有以下不同的索引类型：
1、主键索此 ( PRIMARY )
2、唯一索引 ( UNIQUE )
3、普通索引 ( INDEX )
4、全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）
5、组合索引 ( 多个字段组合起来的索引，例如 idx_condition(add_time,order_id) ）
6、覆盖索引（ 覆盖索引介绍详见 mysql覆盖索引）
建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好，因为创建索引是要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间维护索引
查询语句的优化思路和原则
1、优化更需要优化的Query；
2、定位优化对象的性能瓶颈；
3、明确的优化目标；
4、从Explain入手；
5、多使用profile
6、永远用小结果集驱动大的结果集；
7、尽可能在索引中完成排序；
8、只取出自己需要的Columns；
9、仅仅使用最有效的过滤条件；
10、尽可能避免复杂的Join和子查询；
mysql优化策略
mysql优化时最常用的手段就是创建索引，那么如何看sql语句使用的索引情况呢？答案是使用 explain 语句，如下图：
type（连接类型）：
system：表只有一行
const：表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref：每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref：如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext：全文搜索
ref_or_null：与ref类似，但包括NULL
index_merge：表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。
这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery：在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。所以不一定in子句中使用子查询就是低效的！
index_subquery：同上，但把形如”select non_unique_key_column“的子查询替换
range：常数值的范围
index：
a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；
b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；
c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；
d.如单独出现，则是用读索引来代替读行，但不用于查找
all：全表扫描
possible_keys：可能使用到的索引名称
key：本次查询真正使用到的索引
extra：通过 extra 字段中的值，就可以看出性能如何，性能递减 Using index condition/const-> Using index equal/ref -> Using index/primary -> Using index;Using where ->
Using where; Using temporary; Using filesort -> all
下面介绍一些具体的优化策略：
1、不要使用 select * 查询表的所有字段，需要哪些字段就查哪些，如果查询的字段都是建索引的字段最好了，这样就使用了覆盖索引，会大大的提高查询效率，例如： select id,name from a，其中id 和 name 都建了索引
2、尽量使用小表（数据少）驱动大表（数据多），例如： select id,name from t1(小表) left join t2（大表），即一句话：小表驱动大表
3、不要使用 like %abc, 因为最左索引的缘故，like %abc 会导致索引无法匹配即无法使用，可以使用 like abc%，例如： select id,name from t1 where name like abc%
4、查询字段上不要使用函数，例如： select max(id) from t1 id索引无效，select id from t1 id索引有效
5、不要在 where 条件中的字段做计算，例如： select name from t1 where CONCAT(id,name) = '1zs'，这里对 id 和 name 做拼接字符串了，不走索引
6、order by / group by 字段包括在索引当中减少排序，效率会更高，例如 select name from t1 order by name asc, name 创建了索引
7、使用组合索引时，当我们建立了idx_condition(id,name,order_id)组合索引后，where 条件中的字段必须有第一个索引字段，即id字段（最左索引），组合索引才有效，当 id 有时，没有name ，但是有 order_id 时组合索引也有效（索引下推，mysql 5.6 之后优化的），示例如下：
-
-- 没有id条件，组合索引无效
-
select name
from t1
where name
=
'zs';
-
select name
from t1
where order_id
=
1;
-
select name
from t1
where name
=
'zs'
and order_id
=
1;
-
-- 有id条件，组合索引有效
-
select name
from t1
where id
=
1
and name
=
'zs'
and order_id
=
1;
-
select name
from t1
where id
=
1
and order_id
=
1;
-
select name
from t1
where id
=
1
and name
=
'zs';
8、where 不定多条件查询时，使用mybatis的if判断，根据条件组合动态决定是否需要 left join 其他表，示例如下：
-
-- where 条件中只有t1表的字段，此时不需要关联t2表，即 left join t2
-
select name,age
from t1
left
join t2
on t2.user_id
= t1.id
where t1.name
=
'zs'
-
-- where 条件中只有t1表的字段，此时不需要关联t2表，即 left join t2。注意 select count的话会扫描表的很多记录，为了提高效率更需要要根据条件组合动态决定是否需要 left join 其他表
-
select
count(t1.id)
from t1
left
join t2
on t2.user_id
= t1.id
where t1.name
=
'zs'
9、尽量不要使用 order by / group by ，这样子会破坏索引，产生临时表
10、子查询时选择 exists 还是 in，取决于两个表的大小。 结论如下：
10.1、如果查询的两个表大小相当，使用 exists 和 in 差别不大
10.2、如果两个表中一个是小表，一个是大表，则子查询表大的用exists，子查询表小的用in，其中 t1(大表)，t2（小表），示例如下：
-
-- 查询效率高，使用了 t1 表 type 列的索引
-
select name
from t1
where t1.type
in (
select type
from t2)
-
-- 查询效率低，使用了 t2 表 type 列的索引
-
select name
from t1
where
exists (
select type
from t2
where t1.type
= t2.type)
-
-- 查询效率高，使用了 t1 表 type 列的索引
-
select name
from t2
where
exists (
select type
from t1
where t1.type
= t2.type)
-
-- 查询效率低，使用了 t2 表 type 列的索引
-
select name
from t2
where t2.type
in (
select type
from t1)
总结一句话就是： 子小in，子大exists
11、尽量不要使用 != 、 <> 和 not in , 因为这些比较符会导致不走索引，示例如下：
-
-- 不推荐下面所有的写法
-
select name
from t1
where name
!=
'zs';
-
select name
from t1
where name
<>
'zs';
-
select name
from t1
where name
not
in
'zs';
12、union all 替代 in，示例如下：
-
-- name 不走索引
-
select name
from t1
where name
=
'zs'
or name
=
'ls';
-
-- name 走索引
-
select name
from t1
where name
=
'zs'
union
all
select name
from t1
where name
=
'ls';
13、分页查询时尽量查询页数靠前的，因为 limit 100,10，会查询出110条数据，然后取10，这样子的话页数越靠后取出的数据量越大，查询也就越慢
14、查询时索引选择不是最优时，可以强制用 force index 来强制使用自己认为效率高的索引
15、where 中使用 is null 或者 is not null 都可能使索引失效，原因和数据分布有关，当满足数据的量达到一定时，mysql会优化成全表扫描，示例如下：
-
-- name 是 null 数据达到0.4（自测值，有待源码考证）以上时，索引就会失效
-
select name
from t1
where name
is
null;
-
-- name 不为 null 数据达到0.4（自测值，有待源码考证）以上时，索引就会失效
-
select name
from t1
where name
is
not
null;
这里说明一下：为了避免此问题，字段中最好都要有默认值
16、where 条件中查询出来的数据达到一定量（0.1,有待源码考证），索引会失效，使用 explain 可以查看是否用到了索引
17、禁止索引滥用，索引不是越多越好，过多的索引将导致数据库插入和更新变慢，因为插入和更新数据时索引要重新排序位置
18、区分度低的字段不要建立索引，这样不仅不会提高查询效率，而且会降低表中数据插入和更新的速度。区分度低是指数据类型比较单一，比如 status字段，status的值非0即1
19、字符串长的字段创建索引时最好指定长度，比如 name 字段值都比较长，而name 值前20位就可以达到区分度较高了，那么创建name索引时指定长度为20。由于索引都是储存在索引文件中，这样的话可以减小索引文件的大小，提高写入效率
20、删除表所有记录请用 truncate，不要用 delete
21、更新或者新增时，使用批量语句操作，不要一条一条的请求
22、where 中频繁出现的查询字段需要创建索引
23、group by 使用时，默认都是匹配到第一个数据就返回，即id最小的，那如果我们想使用group by 时获取到时间最大的数据怎么办呢？下面提供两种方式：
-
-- 自连接方式，分组查询name的创建时间最新值（使用order by 排序）
-
-
select t1.
*
from test t1
join (
select id
from test
order
by add_time
desc)
as t2
where t1.id
= t2.id
GROUP
BY t1.name;
-
-
-- 自连接方式，分组查询name的创建时间最新值（使用 max 函数），和上面自连接相比，如果 id 大的可以认为是创建时间是最新的话，这种方法效率更高一些
-
-
select t1.
*
from test t1
join (
select
max(id)
as id
from test
GROUP
BY name)
as t2
where t1.id
= t2.id;
-
-
-- 子查询方式，分组查询name的最新值（创建时间最大），这种方式效率相比前面的自连接效率更差一些
-
-
select
*
from (
select
*
from test
order
by create_time
desc )
as t
GROUP
BY t.business_no;
查询效率由高到底： 自连接(使用max函数) -> 自连接（使用order by 排序） -> 子查询
880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
