首页 > 数据库 >SQL优化的35个小技巧

SQL优化的35个小技巧

时间：2024-11-01 10:58:48浏览次数：3  
标签：技巧 age 35 索引 user SQL WHERE id SELECT

  1. 避免使用 SELECT *，使用具体字段
    反例：
    SELECT * FROM employee;
    正例：
    SELECT id, name, age FROM employee;
    使用具体字段可以节省资源、减少网络开销，且能避免回表查询。

  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 会导致索引失效并引发全表扫描。

  1. 使用 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 提升查询效率，避免多余的数据返回。

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

  3. 批量操作（插入、删除、查询）
    反例：

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>

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

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

  1. 尽可能使用 NOT NULL 定义字段
    原因：NOT NULL
    可以防止出现空指针问题。
    NULL值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化SQL。
    NULL值有可能会导致索引失效

  2. 避免在索引列上使用内置函数
    反例：
    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);
    原因：索引列上使用函数会导致索引失效。

  3. 避免在 WHERE 子句中对字段进行表达式操作
    反例：
    SELECT * FROM user WHERE age - 1 = 10;

正例：

SELECT * FROM user WHERE age = 11;

  1. 在 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;

  1. 优化 LIKE 语句

反例：

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

正例：

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

原因：% 放在前面会导致索引失效。

  1. 使用小表驱动大表

小表先执行以减少扫描量，如使用 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效果会相对更好一点。

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

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

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

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

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，避免了临时表的创建，查询效果就更佳

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

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

反例：

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

正例：可分为两个查询。

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

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

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

例如联合索引 (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优化器有关的。

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

反例：

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

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

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

  1. 使用覆盖索引

正例：

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

  1. 删除冗余索引

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

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

正例:

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

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

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

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

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

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

  1. 索引数不宜超过5个

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

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

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

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

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

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

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

反例：

SELECT * FROM user WHERE userid = 123;

正例：

SELECT * FROM user WHERE userid = '123';

  1. 避免返回过多数据量

反例：

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 查询的效率：

  1. 合理利用视图（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;

  1. 使用表分区（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)
);

  1. 合理使用存储过程（Stored Procedure）来减少多次 SQL 交互

将多步操作写入存储过程，可以减少客户端与数据库之间的多次交互，提高效率。
正例：

CREATE PROCEDURE update_and_select(IN user_id INT)
BEGIN
UPDATE users SET last_login = NOW() WHERE id = user_id;
SELECT * FROM users WHERE id = user_id;
END;

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

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

  1. 使用适当的隔离级别

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

  1. 使用合适的数据类型

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

  1. 避免频繁更新索引列

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

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

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

  1. 使用批量更新或删除

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

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

这些 SQL 优化技巧涵盖了数据库的方方面面。希望这些补充的优化建议可以帮助进一步提升数据库的整体性能和稳定性。

转载自https://niutech.top/archives/sqlyou-hua-de-35ge-xiao-ji-qiao

标签：技巧,age,35,索引,user,SQL,WHERE,id,SELECT
From： https://www.cnblogs.com/jyccc/p/18519741

相关文章

  • 你还不会用数据库吗？一篇文章带你入门！！！#sql #Mysql
    目录前言一段话简单理解什么是“数据”，“数据库”，“SQL”，“Mysql”一、准备工作MySQL的安装二、打开MySQL方法一：通过下载的MySQL软件直接打开方法二：命令提示符打开（cmd）方法三：连接指定地址数据库服务端方法四：直接打开（前提已经建立过数据库）三、MySQL的一些简单语句（正式实......
  • mysql数据库新增和修改字段
      8.0版本之上--修改旧字段名为新字段名ALTERTABLEbd_material_circuit_breakerRENAMECOLUMNrelease_typeTOrelease_type_zh;ALTERTABLEbd_material_circuit_breakerRENAMECOLUMNcontrol_typeTOcontrol_type_zh;ALTERTABLEbd_material_circ......
  • rk3576 查询npu频率
    rk3576查询npu频率cat/sys/kernel/debug/rknpu/freq设置NPU工作频率：#以RK3588为例#查看NPU可用频率cat/sys/class/devfreq/fdab0000.npu/available_frequencies#设置NPU频率，例如，设置1GHzecho1000000000>/sys/kernel/debug/rknpu/freq   （1）查询NPU......
  • 手动部署MySQL数据库
    步骤一：安装MySQL运行以下命令，更新YUM源。yum-yupdate如果您想下载其他版本的MySQL，请参见MySQL官方文档。sudoyuminstallmysql-server-y运行以下命令，查看MySQL版本号。mysql-V返回结果如下，表示MySQL安装成功。mysqlVer8.0.33forLinuxonx86_64(MySQL......
  • 通过Navicat for MySQL恢复数据库的步骤
    启动NavicatforMySQL在本地主机上启动NavicatforMySQL应用程序。创建新的MySQL连接在菜单栏中选择 连接 > MySQL。在弹出的 MySQL-新建连接 对话框中，输入云虚拟主机的数据库信息，包括主机地址、端口、用户名和密码。单击 连接 按钮以建立连接。打开数据......
  • PMP–一、二、三模、冲刺–分类–8.质量管理–技巧–成本效益分析
    文章目录技巧一模1.引论--阶段关口--为做出进入下个阶段、进行整改或结束项目集或项目的决定，做的阶段未审查。审查一个阶段末的问题，做成本效益分析，以决定下个阶段的项目。39、[单选]在新目标国家推出产品前不久，公司意识到该产品并不完全符合当地的数据隐私法。由于预算......
  • [SWPUCTF 2021 新生赛]easy_sql的write up
    开启NSSCTF靶场，在浏览器中访问链接，看到让我们输入点什么还有标签页名字提示："参数是wllm"直接/?wllm=1访问一下：这里就直接用sqlmap直接爆破了：查看数据库有哪些：pythonsqlmap.py-u"http://node4.anna.nssctf.cn:28961/?wllm=1"--dbs看到test_db就是这个数据库，其......
  • 【AIGC】深入探索『后退一步』提示技巧：激发ChatGPT的智慧潜力
    博客主页：[小ᶻZ࿆]本文专栏:AIGC|ChatGPT文章目录......
  • [SUCTF 2019]EasySQL
    题目链接：https://buuoj.cn/challenges#[SUCTF2019]EasySQL打开环境后，如下所示。尝试输入字符：1。尝试输入字符：0后，发现没有输出结果。尝试输入字符串"aaa"、"bbb"等后，发现都跟输入0的结果一致，而输入123、456等非0的内容，都与输入1一致，这里可以猜测（实际上需要比较......
  • 【Linux篇】常用命令及操作技巧（进阶篇 - 下）
    ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99