避免使用select *
只取需要的字段，节省资源、减少网络开销。
尽可能走覆盖索引了，避免回表查询。

不使用 select * 的七个理由

查询结果只有一条或者只要最大/最小一条记录，建议用limit 1
避免全表扫描


避免在where子句中使用or
or可能会使索引失效，导致全表扫描。

select * from user where userid=1 or age =18

select * from user where userid=1 
union all 
select * from user where age = 18


limit分页优化
反例：select id,name,age from employee limit 10000,10
正例：
方案一  返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10
方案二  order by + 索引
select id,name from employee order by id  limit 10000,10


like语句优化
select userId,name from user where userId like '%123';
使用前缀索引
select userId,name from user where userId like '123%';



使用where条件限定要查询的数据，避免返回多余的行

避免在索引列上使用内置函数
查询最近七天内登陆过的用户
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

避免在 where 子句中对字段进行表达式操作
select * from user where age-1 =10
select * from user where age =11

Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

Inner join 只保留两张表中完全匹配的结果集
left join  返回左表所有的行，即使在右表中没有匹配的记录。
right join 返回右表所有的行，即使在左表中没有匹配的记录。

避免在 where 子句中使用!=或<>  ，可能导致索引失效


联合索引 最左匹配原则
联合索引idx_userid_age userId在前，age在后
无法利用索引
select * from user where age = 10

以下语句符合最左匹配原则，会走索引
select * from user where userid=10
select * from user where userid=10 and age=10

考虑在 where 及 order by 涉及的列上建立索引，避免全表扫描

插入数据过多，考虑批量插入

尽量利用覆盖索引，避免回表查询

慎用distinct关键字

SELECT DISTINCT * from  user
select DISTINCT name from user

删除冗余和重复索引
KEY `idx_userId_age` (`userId`,`age`)
组合索引（A,B）相当于创建了（A）和（A,B）索引


如果数据量较大，优化修改/删除语句 ,避免一次性删除更新太多记录，尽量小批量执行

分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000
一次性删除太多数据，可能导致 lock wait timeout exceed

避免使用 null 字段 
全表扫描
select * from user where age is not null
走索引
select * from user where age>0

!= <>  is null  is not null 有可能使索引失效 ，避免使用


避免多表连接

exist  in 合理利用
select * from A where deptId in (select deptId from B);
select * from A where exists (select 1 from B where A.deptId = B.deptId)
小表驱动大表，小的数据集驱动大的数据集
选择最外层循环小的，如果B的数据量小于A，适合用in，如果B的数据量大于A，适合用exist


尽量用 union all 替换 union
使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。
如果已知检索结果没有重复记录，使用union all 代替union

控制索引数量

尽量使用数字型字段

索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

避免向客户端返回过多数据行

尽可能使用varchar/nvarchar 代替 char/nchar

group by 之前先过滤结果集

select job,avg（salary） from employee  group by job having job ='president' or job = 'managent'
select job,avg（salary） from employee where job ='president' or job = 'managent' group by job

如何字段类型是字符串，where条件要加引号，否则索引失效



explain 使用 
type possible_keys key
索引类型 type
ALL range





小表驱动大表
用小表的数据集驱动大表的数据集。
假设有order和user两张表，记录数 order表 10000，user 100条

查所有有效的用户下过的订单列表

select * from order
where user_id in (select id from user where status=1)

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1)


in 适用于左边大表，右边小表
exists 适用于左边小表，右边大表


select id,name,age 
from user where id > 1000000 limit 20;


用连接查询代替子查询

select * from order
where user_id in (select id from user where status=1)

子查询优点 
简单，结构化
缺点
执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有额外的性能消耗。


select o.* from order o inner join user u on o.user_id = u.id where u.status=1

join的表不宜过多 
join过多，索引选择复杂 ，
如果没有命中索引 ，nested loop join 分别从两个表读数据进行两两对比

left join   两个表的交集 + 左表剩下的数据
inner join  两个表交集

inner join ，mysql会自动选择两张表中的小表，去驱动大表
left join  ，mysql默认用左表 驱动 右表 ，如果左边的表数据很多时，会有现性能问题
left join ，尽量 左边 小表，右边大表

提升group by 效率
去重 分组 
select user_id,user_name from order group by user_id having user_id <= 200;
优化  分组前先过滤 
select user_id,user_name from order where user_id <= 200 group by user_id




 mysql explain 简介 

 mysql索引失效的几种情况