1、查询SQL尽量不要使用select *，而是select具体字段。
反例子：
复制
select * from employee;1.
正例子：
复制
select id，name, age from employee;1.
select具体字段，节省资源、减少网络开销。select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。
2、应尽量避免在where子句中使用or来连接条件
反例:
复制
select * from user where userid=1 or age =181.
正例：
复制
//使用union all
select * from user where userid=1
union all
select * from user where age = 18
//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 181.2.3.4.5.6.7.8.
使用or可能会使索引失效，从而全表扫描。一位朋友踩过这个坑，差点把数据库CPU打满了。
如果userId加了索引，age没加索引，以上or的查询SQL，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。
3. 尽量使用limit，避免不必要的返回
假设一个用户有多个订单，要查询最新订单的下单时间的话，你是这样查询：
复制
select id, order_date from order_tab
where user_id=666
order by create_date desc;1.2.3.
获取到订单列表后，取第一个的下单时间：
复制
List<Order> orderList = orderService.queryOrderListByUserId(666);
Date orderDate = orderList.get(0).getOrderDate();1.2.
还是用limit ，只获取最新那个订单返回：
复制
select id, order_date from order_tab
where user_id=666
order by create_date desc limit 1;1.2.3.
显然，使用limit的更好~ 因为整体性能更好。
4. 尽量使用数值类型而不是字符串
比如我们定义性别字段的时候，更推荐0代表女生，1表示男生，而不是定义为WOMEN 或者MAN的字符串。
因为：
数值类型（如 INT, FLOAT, DECIMAL 等）通常占用的存储空间比字符串类型（如 VARCHAR, CHAR）小数值类型的比较和计算速度通常比字符串快
5. 批量操作(更新、删除、查询)
反例：
复制
for(User u :list){
INSERT into user(name,age) values(#name#,#age#)
}1.2.3.
正例：
复制
//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>1.2.3.4.5.
理由：
批量插入性能好，更加省时间
打个比喻: 假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?
6、尽量用 union all 替换 union
如果检索结果中不会有重复的记录，推荐union all 替换 union。
反例：
复制
select * from user where userid=1
union
select * from user where age = 101.2.3.
正例：
复制
select * from user where userid=1
union all
select * from user where age = 101.2.3.
理由：
如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。
7. 尽可能使用not null定义字段
如果没有特殊的理由， 一般都建议将字段定义为NOT NULL。
复制
city VARCHAR(50) NOT NULL1.
为什么呢？
NOT NULL 可以防止出现空指针问题。其次，NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。NULL值有可能会导致索引失效
8、尽量避免在索引列上使用mysql的内置函数
业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)
反例：
复制
select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();1.
正例：
复制
explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);1.
理由：
索引列上使用mysql的内置函数，索引失效
9、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫
反例：
复制
select * from user where age-1 =10；1.
正例：
复制
select * from user where age =11；1.
理由：
虽然age加了索引，但是因为对它进行运算，索引直接迷路了。。。
10、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。
假设有一个 orders 表，存储了所有用户的订单信息，并包含 city 字段表示用户所在城市。我们想要计算来自北京的每个用户的总消费金额。
复制
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
city VARCHAR(50) NOT NULL,
amount DECIMAL(10, 2)
);1.2.3.4.5.6.
计算北京用户的消费总额，按用户分组，反例SQL（不使用 WHERE 条件过滤）：
复制
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = 北京;1.2.3.4.
应该先用 WHERE 条件过滤，正例如下:
复制
SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = 北京
GROUP BY user_id;1.2.3.4.
11、优化你的like语句
日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。
反例：
复制
select userId，name from user where userId like %123;1.
正例：
复制
select userId，name from user where userId like 123%;1.
理由：
把%放前面，并不走索引.
有些时候你就是需要包含关键词，可以结合其他查询条件（加索引的其他条件）结合起来。或者可以借助 Elasticsearch 来进行模糊查询，这里知道like在前会导致索引失效这个点就好啦。
12.使用小表驱动大表的思想
小表驱动大表，这主要是为了优化性能，让查询执行得更高效。背后的核心原因是减少数据扫描量，尽量让数据库在处理时能先过滤掉大量无关数据，从而缩短查询时间。
假设我们有个客户表和一个订单表。其中订单表有10万记录，客户表只有1000行记录。
现在要查询下单过的客户信息，可以这样写：
复制
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);1.2.3.4.
EXISTS 会逐行扫描 customers 表（即小表），对每一行 c.id，在 orders 表（大表）中检查是否有 customer_id = c.id 的记录。
当然，也可以使用in实现：
复制
SELECT * FROM customers
WHERE id IN (
SELECT customer_id FROM orders
);1.2.3.4.
in 查询会先执行内部查询部分 SELECT customer_id FROM orders，获得 orders 表（大表）中的所有 customer_id，然后在 customers 表（小表）中查找匹配的 id。
因为orders表的数据量比较大，因此这里用exists效果会相对更好一点。
13. in查询的元素不宜太多
如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过200个，如果超过了，建议分组，每次200一组进行哈。
反例：
复制
select user_id,name from user where user_id in (1,2,3...1000000);1.
如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑.如下这种子查询：
复制
select * from user where user_id in (select author_id from artilce where type = 1);1.
正例是，分批进行，比如每批200个：
复制
select user_id,name from user where user_id in (1,2,3...200);1.
14. 优化limit分页
我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下，也就是出现深分页问题。
反例：
复制
select id,name,balance from account where create_time> 2020-09-19 limit 100000,10;1.
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到100000，则SQL可以修改为：
复制
select id,name,balance FROM account where id > 100000 limit 10;1.
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。
延迟关联法
延迟关联法，就是把条件转移到主键索引树，然后减少回表。如下：
复制
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > 2020-09-19 limit 100000, 10) AS acct2 on acct1.id= acct2.id;1.
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
15. 尽量使用连接查询而不是子查询
因为使用子查询，可能会创建临时表。
反例如下：
复制
SELECT *
FROM customers c
WHERE c.id IN (
SELECT o.customer_id
FROM orders o
);1.2.3.4.5.6.
IN 子查询会在 orders 表中查询所有 customer_id，并生成一个临时结果集。
我们可以用连接查询避免临时表：
复制
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id;1.2.3.
通过 JOIN 直接将 customers 和 orders 表关联，符合条件的记录一次性筛选完成。MySQL 优化器通常可以利用索引来加速 JOIN，避免了临时表的创建，查询效果就更佳
16、Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小
Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。
都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。
反例:
复制
select * from tab1 t1 left join tab2 t2 on t1.size = t2.size where t1.id>2;1.
正例：
复制
select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;1.
理由：
如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。
17、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
反例：
复制
select age,name from user where age <>18;1.
正例：
复制
//可以考虑分开两条sql写
select age,name from user where age <18;
select age,name from user where age >18;1.2.3.
理由：
使用!=和<>很可能会让索引失效
18、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。
表结构：（有一个联合索引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 `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;1.2.3.4.5.6.7.8.
反例：
复制
select * from user where age = 10;1.
正例：
复制
//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;1.2.3.4.
理由：
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。
19、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。
反例：
复制
select * from user where address =深圳 order by age ;1.
正例：
复制
添加索引
alter table user add index idx_address_age (address,age)1.2.
20、在适当的时候，使用覆盖索引。
覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。
反例：
复制
// like模糊查询，不走索引了
select * from user where userid like %123%1.2.
正例：
复制
//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like %123%;1.2.
21、删除冗余和重复索引
反例：
复制
KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)1.2.
正例:
复制
//删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
KEY `idx_userId_age` (`userId`,`age`)1.2.
理由：
重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。
22、不要有超过3个以上的表连接
连表越多，编译的时间和开销也就越大。把连接表拆开成较小的几个执行，可读性更高。如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。
23、索引不宜太多，一般5个以内。
索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。
24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。
因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
25、如何字段类型是字符串，where时一定用引号括起来，否则索引失效
反例：
复制
select * from user where userid =123;1.
正例：
复制
select * from user where userid =123;1.
理由：
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。
26、尽量避免向客户端返回过多数据量。
假设业务需求是，用户请求查看自己最近一年观看过的直播数据。
反例：
复制
//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)1.2.
正例：
复制
//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize
//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;1.2.3.4.5.
理由：
查询效率：当返回的数据量过大时，查询所需的时间会显著增加，导致数据库性能下降。通过限制返回的数据量，可以缩短查询时间，提高数据库响应速度。网络传输：大量数据的传输会占用网络带宽，可能导致网络拥堵和延迟。减少返回的数据量可以降低网络传输的负担，提高数据传输效率。
