关于sql的一些优化

1. 避免使用 SELECT *，使用具体字段

反例：

SELECT * FROM crm_customer;

正例：

SELECT id, name, age FROM customer;

使用具体字段可以节省资源、减少网络开销，且能避免回表查询。

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;

原因：OR 会导致索引失效并引发全表扫描。

3. 使用 LIMIT 避免不必要的数据返回

反例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC;

正例：

SELECT id, order_date FROM order_tab WHERE user_id=666 ORDER BY create_date DESC LIMIT 1;

LIMIT 提升查询效率，避免多余的数据返回。

4. 使用数值类型代替字符串

例子：性别字段建议用数值（如0代表女生，1代表男生）而非字符串（如"WOMEN"、"MAN"）。
原因：数值类型占用存储空间小、比较速度更快。

5. 批量操作（插入、删除、查询）

反例：

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>

原因：批量插入性能更优。

6. 使用 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 则省去这一步。

7. 尽可能使用 NOT NULL 定义字段

原因：NOT NULL

可以防止出现空指针问题。

NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。

NULL值有可能会导致索引失效

8. 避免在索引列上使用内置函数

反例：

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);

原因：索引列上使用函数会导致索引失效。

9. 避免在 WHERE 子句中对字段进行表达式操作

反例：

SELECT * FROM user WHERE age - 1 = 10;

正例：

SELECT * FROM user WHERE age = 11;

10. 在 GROUP BY 前进行条件过滤

反例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
HAVING city = '北京';

正例：

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE city = '北京'
GROUP BY user_id;

11. 优化 LIKE 语句

反例：

SELECT userId, name FROM user WHERE userId LIKE '%123';

正例：

SELECT userId, name FROM user WHERE userId LIKE '123%';

原因：% 放在前面会导致索引失效。
可以加入反向索引，通过冗余一个字段来存储userId倒序，来保证索引的命中

12. 使用小表驱动大表

小表先执行以减少扫描量，如使用 EXISTS 或 IN 进行过滤。

假设我们有个客户表和一个订单表。其中订单表有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);

提示客户端一次性查询的条数，不能超过500条，或者是使用多线程，将这批值使用多线程的方式查询出来，然后进行数据的汇总合并

14、exists 和 in 的取舍

select * from student where school_id in (select id from school);
select * from police p where exists (select 1 from user u where u.id = p.id);

如果子查询得出的结果集数据较少，主查询中的表较大且又有索引时，应该用in；反之，如果外层的主查询数据较少，子查询]中的表大，又有索引时使用exists。

  • 如果是exists，那么以外层表为驱动表，先被访问。
  • 如果是in，那么先执行子查询。

in 是把外表和内表作 hash 连接，而 exists 是对外表作 loop 循环，每次 loop 循环再对内表进行查询。所以，我们会以驱动表的快速返回为目标，目标是以小表驱动大表，这是性能优化的本质。

15. 优化 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内连接，这样后面直接走了主键索引了，同时也减少了回表。

16. 优先使用连接查询而非子查询

因为使用子查询，可能会创建临时表。
反例：

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);

IN 子查询会在 orders 表中查询所有 customer_id，并生成一个临时结果集。

正例：

SELECT DISTINCT c.* FROM customers c JOIN orders o ON c.id = o.customer_id;

通过 JOIN 直接将 customers 和 orders 表关联，符合条件的记录一次性筛选完成。
MySQL 优化器通常可以利用索引来加速 JOIN，避免了临时表的创建，查询效果就更佳

17. Inner join 、left join、right join，优先使用Inner join，如果是left 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是等值连接，或许返回的行数比较少，所以性能相对会好一点。同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

18. 避免 != 或 <> 操作符

反例：

SELECT age, name FROM user WHERE age <> 18;

正例：可分为两个查询。

select age,name  from user where age <18;
select age,name  from user where age >18;

使用!=和<>很可能会让索引失效

19. 使用联合索引时遵循最左匹配原则

例如联合索引 (userId, age)，查询 userId 和 age 时优先使用 userId。

表结构：（有一个联合索引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优化器有关的。

20. 对 WHERE 和 ORDER BY 涉及的列建索引

反例：

SELECT * FROM user WHERE address = '深圳' ORDER BY age;

正例：
覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

ALTER TABLE user ADD INDEX idx_address_age (address, age);

21. 使用覆盖索引

正例：

SELECT id, name FROM user WHERE userid LIKE '123%';

22. 删除冗余索引

避免重复索引，节省资源。
反例：

KEY `idx_userId` (`userId`)  
KEY `idx_userId_age` (`userId`,`age`)

正例:

//删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
  KEY `idx_userId_age` (`userId`,`age`)

理由：重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。

23. 避免超过3个以上的表连接

不要有超过3个以上的表连接连表越多，编译的时间和开销也就越大。

把连接表拆开成较小的几个执行，可读性更高。

如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

24. 索引数不宜超过5个

索引不宜太多，一般5个以内。

索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。

insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。

一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

25. 索引不适合建立在大量重复数据的字段上

如性别字段，重复数据多时优化器可能放弃索引。

26. 字符串类型字段在 WHERE 中使用引号

反例：

SELECT * FROM user WHERE userid = 123;

正例：

SELECT * FROM user WHERE userid = '123';

27. 避免返回过多数据量

反例：

SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR);

正例：

-- 分页查询
SELECT * FROM LivingInfo WHERE watchId = userId AND watchTime >= DATE_SUB(NOW(), INTERVAL 1 YEAR) LIMIT offset, pageSize;

理由：
查询效率：当返回的数据量过大时，查询所需的时间会显著增加，导致数据库性能下降。
通过限制返回的数据量，可以缩短查询时间，提高数据库响应速度。
网络传输：大量数据的传输会占用网络带宽，可能导致网络拥堵和延迟。
减少返回的数据量可以降低网络传输的负担，提高数据传输效率。。

在 SQL 优化方面，除了已经列举的26个技巧，这里再补充9个技巧，使优化点达到35条。这些补充技巧包含一些更加细化的实践，帮助进一步提升 SQL 查询的效率：

28. 合理利用视图（View）进行复杂查询

如果一个复杂查询需要频繁使用，可以考虑创建视图，以简化查询结构并提高查询效率。
正例：

CREATE VIEW view_user_orders AS
SELECT u.id, u.name, o.order_id, o.amount
FROM user u JOIN orders o ON u.id = o.user_id;
-- 使用视图查询
SELECT * FROM view_user_orders WHERE amount > 100;

29. 使用表分区（Partitioning）优化大表性能

对于数据量较大的表，通过分区可以有效提升查询效率。表分区可以按日期、数值范围等方式进行分割。
正例：


CREATE TABLE sales (
 sale_id INT,
 sale_date DATE,
 amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
 PARTITION p2019 VALUES LESS THAN (2020),
 PARTITION p2020 VALUES LESS THAN (2021),
 PARTITION p2021 VALUES LESS THAN (2022)
);

30. 对频繁变更的数据使用缓存

对于频繁查询的静态或相对稳定的数据，可考虑将查询结果存放到缓存（如 Redis）中，以减轻数据库的负担。
如菜单、按钮、路由等数据

31. 使用适当的隔离级别

在高并发环境中选择适当的事务隔离级别（如 READ COMMITTED），可以避免不必要的锁竞争和阻塞，提升并发效率。

32. 使用合适的数据类型

选择合适的数据类型会节省存储空间，提升处理速度。例如：TINYINT（1字节）代替INT（4字节），VARCHAR(50)代替CHAR(50)，存储长度尽可能精确匹配业务需求。

33. 避免频繁更新索引列

在高并发写操作的场景中，频繁更新索引字段会导致索引重建，影响性能。如果字段变动频繁且无查询需求，建议避免对该字段建立索引。

34. 避免在事务中执行非必要的操作

在事务中应避免执行耗时操作，比如网络请求或复杂计算，以减少锁的持有时间。优先确保事务操作集中在必要的数据变更上。

35. 使用批量更新或删除

对于批量更新或删除数据，避免一次性操作大量记录。可以分批次执行，以减少锁定时间，减轻系统压力。
正例：

-- 分批删除
DELETE FROM orders WHERE status = 'obsolete' LIMIT 1000;

36、区分度不大的数据，不要添加索引
比如性别字段，只有有两种值，查询时，这种索引很难缩小查找范围，可能还不如直接全表扫描快。而且在插入或更新数据时，数据库要频繁维护这个没什么区分度的索引，会有额外的性能损耗

37、尽可能保持逻辑删除
物理删除对索引的影响
当进行物理删除操作时，数据库需要对索引进行大量的维护工作。例如，在一个 B - Tree 索引结构中，如果一条记录被物理删除，索引中的相应节点可能需要重新调整。对于频繁的物理删除操作，索引会频繁地进行节点分裂和合并操作，这会消耗大量的系统资源，包括 CPU 时间和磁盘 I/O。
假设一个包含大量订单记录的表，每次物理删除一个订单，索引中与该订单相关的键值对应的节点都要进行调整。如果删除操作很频繁，这种索引的频繁调整会导致查询性能下降，因为在调整索引节点的过程中，其他查询操作可能需要等待索引维护完成才能继续进行。

38、逆范式设计表
减少表的连接: 在范式化的数据库设计中，数据通常被分散到多个表中，这样在查询时需要进行多次表连接（JOIN）。当数据量较大或者连接操作频繁时，JOIN操作会变得非常耗时。反范式化通过将相关的数据存储在同一张表中，减少了连接操作，从而提升了查询性能。

优化读取操作: 在许多应用场景中，读取操作远多于写入操作。为了优化读取性能，可以在表中引入冗余字段，预先计算并存储一些常用的查询结果，这样可以在查询时直接获取结果，而不需要进行复杂的计算。

39、count(*) count(字段) count(1)
count(1) 和 count(*): 统计所有行的数量，无论行内数据是否为 NULL。
count(字段): 只统计特定字段中非 NULL 的行数量
SQL92推荐count(*)方式，因为它被SQL优化器优化得最好，适用于计算总行

40、对于update和delete 添加limit
在update和delele中使用limit一是保证错误的代码导致误删数据的影响，二是提高执行效率

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,651评论 6赞 501
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,468评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,931评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,218评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,234评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,198评论 1赞 299
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,084评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,926评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,341评论 1赞 311
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,563评论 2赞 333
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,731评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,430评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,036评论 3赞 326
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,676评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,829评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,743评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,629评论 2赞 354