程序员编写高级 SQL 的 30 个终极技巧

terry 2年前 (2023-09-25) 阅读数 51 #后端开发

来源 |摘蜗牛的小男孩（ID：gh_51e0e901a289）

前言

本文将结合 30 个改进 SQL 的技巧和演示示例。大部分都是从实际开发中总结出来的，希望对大家有帮助。

1。查询SQL时尽量不要使用select*，而是使用select自己的域。

比较示例：员工的

select*；

好的示例：

员工的select ID、姓名；♶只需取必要的字段，节省资源并减少网络开销。
select * 查询时，很可能不会使用封闭索引，从而导致表返回查询。

2。如果你知道只有一个答案或者只有一个最大/最小记录，建议使用限制1

假设现在有一个员工表，你想找到一个叫jay的人。

创建表 `user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`year` int(11) DEFAULT,‶ ` datetime DEFAULT NULL 。 where name='jay'

很好的例子

selectID，员工姓名='jay' limit 1;

原因：A找到匹配记录就不会一直往下走，性能会得到很大的提升。限制主要是为了避免全表查找，从而提高性能。

3。尽量不要在where子句中使用任何一个来链接条件

创建一个新的用户表，该表具有自定义的userId索引。表结构如下：

CREATE TABLE `user` (
` id` int(11) NOT NULL AUTO_INCRMENT,
`userId` int`age ♶ int`n(11) NOT
(11) NOT NULL,
`name` varchar(255) NOT NULL,
主键 (`id`),
KEY `idx_userId` (`userId`) ENG. CHARSET=utf8 ;

现在假设需要询问1个用户的用户是否18岁，很容易有SQL

反例：

其中用户select*=1或年龄=18

好例子：

//使用所有年份
select* from user with id =1
union all
//或者将两条sql分开写：
select* from user who userid=1
select* from user who age = 18

原因：

停止使用全索引，扫描表

对于没有索引的or+age，假设我们需要的索引为userId，但是如果我们谈论的是年龄查询的状态，我们仍然需要查看全表，这意味着我们需要三个步骤。：全表扫描+索引扫描+ 如果合并是从全表扫描开始的，那么只需一次扫描即可完成。 MySQL 有一个优化器。从性能和成本来看，索引遇到 OR 条件时可能会失败，这看起来很合理。

4。优化分页限制

在进行日常页面请求时，应用程序经常会使用限制。但是，当偏移量较大时，查询性能会变低。

比较示例：

员工限制 10000 中的select ID、姓名、年龄，10

好示例：

停止记录最大值 select ID、名称，来自 id>10000 限制 10 的员工。
//选项2：order by +索引
selectID，name from worker order by id limit 10000, 10
//选项3：在业务中如果可以限制页数：

原因：

当offset最大时，查询性能会比较低，因为Mysql不会跳过offset直接取后面的数据，而是先加上offset+取的项数，丢弃前面offset中的数据返回之前的部分。
如果使用优化方案1，返回最大请求记录（offset），这样就可以跳过offset，性能会有很大提升。
方案2采用sort by+index，也可以提高查询性能。
对于第三种方案，建议和公司商量一下是否需要检查最后的分页。因为大多数用户不会重新加载太多页面。

5。构建你最喜欢的语句

在日常开发中，如果你使用模糊的关键字问题，很容易想像，但它会让你的索引无效。

匹配示例：

select userId，用户id为“%123”的用户的用户名；

好例子：

select userId，用户 ID 为“123%”的用户的名称；

原因：

把%放在前面，不会被索引，如下：
放在关键字后面，仍然会被索引。如下：

6。使用任何条件来限制要查询的数据，以避免重复行

考虑这样的业务案例：询问用户是否是会员。我看到旧的应用程序代码如下所示。。。ICodmeter: stlist Userids = sqlmap.queryList("select Userid from UserWhere Isvip = 1"); Long userId = sqlMap.queryObject("来自用户的select userId，其中 userId='userId' and isVip='1'")

boolean isVip = userId! =null;

原因：

检查您需要的数据，避免不必要的数据并节省额外费用。？登录用户 where Date_ADD(登录时间,7 DAY INTERVAL) >=today ();

好例子：

澄清select用户 ID，登录时间来自登录用户 where 登录时间 >= Date_ADD( TODAY() 原因：如果索引列使用了mysql内置函数，则索引无效如果不将内部字符串插入索引列，索引仍然会运行。 8.尝试一下避免对where子句中的字段进行注释操作，导致系统放弃使用索引而进行全表扫描例如： select* from user who Age-1 = 10 ；好例子： 11 岁用户的select*；原因：虽然年份是索引，但是因为计算的缘故，索引直接丢失了…… 9。内连接、左连接、右连接、内连接优先。如果允许join，左表结果尽量小。 Inner join 内连接，当两个表进行join查询时，只保留这两个表。精确匹配结果左连接当两个表连接进行查询时，即使右表中没有匹配的记录，也会返回左表中的所有行。 next right 当查询两个表之间的关系时，即使左表中没有匹配的记录，也会返回右表中的所有行。假设满足所有SQL要求，建议先使用Inner join。如果要使用left，左表的数据结果应该尽可能小。如果可以的话，尽量放在左侧进行加工。匹配示例： select* from tab1 t1 from tab2 t2 by t1.size = t2.size where t1.id>2; 好示例： from in table Confu: where id > 2) t1 剩余在 tab2 t2 in t1.size = t2.size; 原因：如果内部条目相等，则返回的行数可能会较少，因此性能会降低。会更好。同样，使用左连接时，左表中的数据结果要尽可能小，条件尽量在左处理，这意味着返回的行数可能相对较小。 10。尽量避免在where子句中使用!=或运算符，否则引擎将无法使用索引并执行全表扫描。对比示例： select年龄，年龄18的用户的名字；好示例： //用户名中可以写两个名字♶，年龄 select年龄，年龄>18的用户姓名; 原因：使用!=和可能会导致索引无效。强 11.使用普通索引时，要注意索引列的顺序，一般遵循最左匹配规则。表结构：（包含用户的idxuseridage公共索引，第一个用户，去年） CREATE TABLE `users` ( `id` int(11) CREATE TABLE `users' ` ( `id` int(11) NOT NULL INC. int (11) NOT NULL, `age` int(11) DEFAULT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`),_age (`userId`,`age`) USING BTREE ) ENGINE=InnoDB AUTO_INCRMENT=2 CHARSET DEFAULT=utf8; 匹配示例：来自年龄 = ” select = //匹配最左边匹配规则 select* from user where userid=10 and age =10; //匹配最左边匹配规则 select* from user with userid=10 and age =10; =10; 原因：当我们创建一个普通索引，比如(k3),k2就等于创建了三个索引(k1),(k1,k2)和(k1,k2,k3)。这样是最左边的匹配规则，不过也和Mysql优化器有关。 12。为了优化查询，应该考虑在位置和顺序包含的列上创建索引，并尽量避免全表搜索。 13.如果需要输入的数据太多，可以考虑分批输入。比较示例： for(User u :list){ INSERT into user values(name,age)(#name#,#year#) } 示例：❀ } //一次插入500个系列，按系列插入用户（姓名，年龄）值 (#{item.name},#{item.age}) 原因：批量输入效果好，节省时间需要将一万块砖移动到房子的顶部。你有电梯。电梯一次可以放置匹配的砖块（最多 500 个）。您可以选择一次带一块砖或一次带 500 块砖。您认为谁花费的时间更多？14.适当时使用封面参考。覆盖索引可以让你的SQL语句不需要返回表。只需访问索引即可获取所有必需的数据，这提高了查询的性能。对比示例： //类似模糊问题，没有来自id为'%123%'的用户的 select*

那么标准指数，即是，覆盖的索引出现。

select id，用户名，用户类似于“%123%”；

15。谨慎使用 unique 关键字

unique 关键字通常用于过滤掉重复记录以返回非重复记录。当用于单个字段查询或很少字段时，它可能会对查询优化产生负面影响。但是，当域很多时使用它会大大降低应用程序的性能。

匹配示例：

SELECT DISTINCT * from user;

好示例：

select DISTINCT name from user;♶ ment with different cpu 两者都高于时间，工作时间不是特殊声明。因为在查询多个域时，如果使用unique，数据库引擎会对数据进行比较，过滤掉重复数据。但这种比较和过滤会占用系统资源和CPU时间。？具体示例： //删除userId索引，因为组合索引(A,B)与创建索引(A)和(A,B)相同 KEY `idx_userId_age`, `age ` ）原因：需要维护重复引用，优化器在优化查询时需要单独考虑，这会影响性能。 17。如果数据量很大，请调整您的编辑/删除语句。避免一次更改或删除太多数据，因为这可能会导致CPU占用率过高，影响其他人对数据的访问。反例： //一次性删除10万还是100万+？ delete on user with id //或者使用单个闪存操作，该操作会失败并永远持续 for (user user: list) { delete on the雇主; } 很好的例子： // 批量删除，比如每个 500 个删除 idisnull、isnotnull 被视为空引用。事实上，这是因为一般情况下，请求的成本很高，优化器会自动离开索引。如果将空值替换为正常值，通常可以进行注入。同时，意思也会很清楚。 19。不要超过5个表连接连接的表越多，编译时间越快，越高。将关系表拆分为较小的执行，以使其更易于阅读。如果你必须连接很多表来获取数据，那就意味着糟糕的设计。 20。合理使用exist&in 假设A表代表某公司的员工表，B表代表部门表。查询所有部门的所有员工，很容易有如下 SQL： select* from A where is deptId (select deptId from B); 写法相当于：♽t 查询表department B 来自 B 的select deptId 然后使用部门 deptId 查询来自 A 的 A select* 中的员工，其中 A.deptId = B.♽ : List resultSet ; for(int i=0;i