1. 查询SQL不要使用select *,而是select具体字段
理由：
- 只取需要的字段，节省资源/减少网络开销
- select * 进行查询时，很有可能就不会使用到覆盖索引了，就会造成回表查询。
2. 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1
理由：
- 加上limit 1后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高。
- 当然，如果字段是唯一索引的话，就不必加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能，如果一个语句本身可以预知不用全表扫描，有没有limit，性能差别并不大。
3. 应尽量避免在where子句中使用or来连接条件
新建一个user表，它有一个普通索引userid，表结构如下：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假设现在需要查询userid为1或者年龄为18岁的用户的用户，很容易有以下sql
反例：
select name from user where userid = 1 or age = 18
正例：
//使用union all
select name from user where userid = 1
union all
select name from user where age = 18
//或者分开两条sql写：
select name from user where userid = 1
select name from user where age = 18
理由：
- 使用or可能会使索引失效，从而全表扫描
对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。 mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。
4. 优化limit分页
我们日常做分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下。
反例：
select id,name,age from employee 1000,10
正例：
//方案一：返回上次查询的最大记录（偏移量）
select id,name, from employee where id > 10000 limit 10
//方案二：order by + 索引
select id,name from employee order by id limit 10000,10
//方案三：在业务允许的情况下限制页数
理由：
- 当偏移量较大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先查询偏移量+要取的条数，然后抛弃掉前面偏移量这一段数据。
- 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
- 方案二使用order by + 索引，也是可以提高查询效率的。
- 方案三的话，建议跟业务讨论，有没有必要查询这么后的分页。因为大多数用户都不会往后翻太多页。
5. 优化like语句
日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效
反例：
select userid,name from user where userid like '%12300'
正例：
select userid,name from user where userid like '123%'
理由：
- 把%放在前面，并不走索引
- 把%放在关键字后面，会走索引
6. 尽量避免在索引列上使用mysql的内置函数
业务需求：查询最近七天内登录过的用户（假设loginTime加了索引）
反例：
select userid,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >= now()
正例：
select userid,longTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY)
理由：
- 索引列上使用mysql的内置函数，索引失效
- 如果索引列不加内置函数，会走索引
7. 应尽量避免在where子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫
反例：
select userid from user where age - 1 = 10
正例：
select userid from user where age = 11
理由：
- 虽然age加了索引，但是因为对它进行运算，导致索引失效
8. inner join,left join,right join,优先使用inner join，如果是left join，左边表结果尽量小
Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集 left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。 right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
都满足SQL需求的前提下，推荐优先使用inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。
反例：
select name from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id > 2
正例：
select name from (select name from tab1 where id > 2) t1 left join tab2 t2 on t1.size = t2.size
理由：
- 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一些
- 同理，使用了左连接，左边表数据尽量小，条件尽量放在左边处理，意味着返回的行数可能比较少。
9. 应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
反例：
select age,name from user where age <> 8
正例：
//可以考虑分开两天sql写
select age,name from user where age < 18;
select age,name from user where age > 18;
理由：
- 使用!=和<>很可能会让索引失效
10. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。
表结构：（有一个联合索引idx_userid_age,userId在前，age在后）
CREATE TABLE `user`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
反例：
select name from user where age = 10;
正例：
//符合最左匹配原则
select name from user where userId = 10 and age = 10;
//符合最左匹配原则
select name from user where userId = 10
理由：
- 当我们创建一个联合索引的时候，如（k1,k2,k3),相当于创建了（k1）、（k1,k2）和（k1,k2,k3）三个索引，这就是最左匹配原则。
- 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关。
11. 对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。
反例：
select name from user where address = '深圳' order by age;
正例：
//添加索引
alter table user add index idx_address_age(address,age)
12. 如果插入数据过多，考虑批量插入。
反例：
for(User u : list){
INSERT INTO user(name,age) values(#{name},#{age})
}
正例：
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>
理由：
- 批量插入性能好，更加省时间
打个比喻:假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?
- 在mybatis中可以开启batch模式
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
13. 在适当的时候，使用覆盖索引
覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。
反例：
//like模糊查询，不走索引了
select name from user userid like '%123%'
正例：
//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '%123%';
14. 慎用distinct关键字
distinct关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。
反例：
SELECT DISTINCT * FROM USER;
正例：
select DISTINCT name from user;
理由：
- 带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。
15. 删除冗余和重复索引
反例：
KEY `idx_userId`(`userId`)
KEY `idx_userId_age`(`userId`,`age`)
正例：
//删除userId索引，因为组合索引（A,B）相当于创建了（A）和（A,B）索引
KEY `idx_userId_age`(`userId`,`age`)
理由：
- 重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这回影响性能。
16. 如果数据量较大，优化你的修改/删除语句。
避免同时修改或删除过多数据，因为会造成cpu利用率过高，从而影响别人对数据库的访问。
反例：
//一次删除10万或者100万+？
delete from user where id < 100000;
//或者采用单一循环操作，效率低，时间漫长
for(User user : list){
delete from user;
}
正例：
//分批进行删除，如每次500
delete user where id < 500
delete product where id >= 500 and id < 1000;
理由：
- 一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。
17. where子句中考虑使用默认值代替null。
反例：
select name from user where age is not null;
正例：
select name from user where age > 0;
理由：
- 并不是说使用了is null或者is not null就不会走索引了，这个跟mysql版本以及查询成本都有关。
如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的
- 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。
18. 超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
19. in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。
B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist 。
20. 尽量使用union all 替换 union
如果检索结果中不会有重复的记录，推荐union all替换union。
反例：
select name from user userid = 1
union
select name from where age = 10
正例：
select name from user where userid = 1
union all
select name from user where age = 10
理由：
- 如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果进行排序。如果已知检索结果没有重复记录，使用union all代替union，这样会提高效率。
21. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型
反例：
`king_id` varchar(20) NOT NULL COMMENT '守护者Id'
正例：
`king_id` int(11) NOT NULL COMMENT '守护者Id'
理由：
- 相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。
22. 尽可能使用varchar/nvarchar 代替 char/nchar。
反例：
`deptName` char(100) DEFAULT NULL COMMENT '部门名称'
正例：
`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'
理由：
- 因为首先变长字段存储空间小，可以节省存储空间。
- 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。
23. 为了提高group by语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。
反例：
select job,avg(salary) from employee group by job having job = 'president'
正例：
select job,avg(salary) from employee where job = 'president' group by job
24. 如果字段类型是字符串，where时一定用引号包裹，否则索引失效
反例：
select name from user where userid = 123
正例：
select name from user where userid = '123'
理由：
- 为什么第一条语句末加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式类型转换，把他们转换为浮点数再做比较。
25. 使用explain分析SQL的执行计划
通过这个判断sql是否走索引
explain select name from user where userid = 10086 or age = 18
