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