本文分类了一些优化SQL查询的特殊技术
在本文中，我提供了一些有关优化SQL语句的建议，其中大多数建议在实际开发中进行了总结。 希望对大家有帮助。
我们的目标是使查询的执行情况令人满意，并在以后的合理时间内继续这样做
> Photo by Nick Morrison on Unsplash
更新：我在GitHub Repo中上传了所有优化技巧
1.尽量不要使用select *查询SQL，而是选择特定字段。
反例：
select * from employee;
正面示例：
select id，name from employee;
原因：
· 通过仅使用必填字段，我们可以节省资源并减少网络开销。
· 可能不会使用covered索引，这将导致查询返回到表。
2.如果您知道只有一个查询结果，建议使用限制1
假设有一个雇员表，并且您想找到一个名为jay的人。
CREATE TABLE employee (id int(11) NOT NULL,name varchar(255) DEFAULT NULL,age int(11) DEFAULT NULL,date datetime DEFAULT NULL,sex int(1) DEFAULT NULL,PRIMARY KEY (`id`) );
反例：
select id，name from employee where name='jay';
正面示例：
select id，name from employee where name='jay' limit 1;
原因：
· 添加限制1后，如果找到相应的记录，它将不再继续扫描，效率将大大提高。
3.尝试避免使用或在where子句中加入条件
创建一个新的用户表，它具有公用索引userId，该表的结构如下：
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) NOT NULL, `age` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `idx_userId` (`userId`) )
现在假设您需要查询用户ID为1或18岁的用户，使用以下SQL很容易。
反例：
select * from user where userid = 1 or age = 18;
正面示例：
//Use union all
select * from user where userid=1 union all select * from user where age = 18;
//Or write two separate SQL
select * from user where userid=1;
select * from user where age = 18;
原因：
· 使用或可能会使索引无效，因此需要进行全表扫描。
对于有索引或无索引的情况，假定已采用userId的索引，但是当涉及到年龄查询条件时，它必须执行全表扫描，这需要三步过程：全表扫描 +索引扫描+合并
4.优化限制分页
进行日常分页时，通常使用限制来实现，但是当偏移量特别大时，查询效率会降低。 因为Mysql不会跳过偏移量，而是直接获取数据。
反例：
select id，name，age from employee limit 10000，10;
正面示例：
//Solution 1: Return the largest record (offset) of the last query
select id，name from employee where id>10000 limit 10;
//Solution 2: order by + index
select id，name from employee order by id limit 10000，10;
原因：
· 如果使用优化解决方案1，它将返回上一个查询记录(偏移量)，以便您可以跳过偏移量，效率得到了很大的提高。
· 选项二使用按+索引排序，这也可以提高查询效率。
5.优化喜欢的陈述
在日常开发中，如果使用模糊关键字查询，则很容易想到"赞"，但是"赞"可能会使索引无效。
反例：
select userId，name from user where userId like '%123';
正面示例：
select userId，name from user where userId like '123%';
原因：在这个故事中
6.使用where条件限制要查询的数据，以避免返回多余的行
假设方案为"查询用户是否为成员"。 我已经看到了这样的旧的实现代码。
反例：
List userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = user
Ids.contains(userId);
正面示例：
Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = user
Id！=null;
原因：检查需要哪些数据，避免返回不必要的数据，并节省费用和计算开销
7.您应尽可能避免在where子句中使用！=或<>运算符，否则引擎将放弃使用索引并执行全表扫描
反例：
select age,name from user where age <>18;
正面示例：
//You can consider separate two sql write
select age,name from user where age <18;
select age,name from user where age >18;
原因：使用！=和<>可能会使索引无效
8.如果插入太多数据，请考虑批量插入
如果要插入太多数据，请考虑批量插入
反例：
for(User u :list){ INSERT into user(name,age) values(#name#,#age#) }
正面示例：
//One batch of 500 inserts, carried out in batchesinsert into user(name,age) values (#{item.name},#{item.age})
· 批量插入性能好，省时
打个比方：如果您需要将10,000块砖移动到建筑物的顶部，则可以使用电梯。
电梯一次可以放置适当数量的砖块(最多500块)，您可以选择一次运输一块砖块，也可以一次运输500块砖块。 您认为哪个时间昂贵？
9.谨慎使用distinct关键字
通常，distinct关键字用于过滤重复记录以返回唯一记录。 当用于查询一个或几个字段时，它将为查询带来优化效果。
但是，当字段很多时，将大大降低查询效率。
反例：
SELECT DISTINCT * from user;
正面示例：
select DISTINCT name from user;
原因：具有"不区分大小写"的语句的CPU时间和占用时间比没有"不区分大小写"的语句高。
因为在查询许多字段时，如果使用不重复，数据库引擎将比较数据并过滤出重复的数据。 但是，此比较和筛选过程将消耗系统资源和CPU时间。
10.删除冗余和重复的索引
反例：
KEY `idx_userId` (`userId`) KEY `idx_userId_age` (`userId`,`age`)
正面示例：
//Delete the userId index, because the combined index (A, B) is equivalent to creating the (A) and (A, B) indexes
KEY `idx_userId_age` (`userId`,`age`)
原因：重复的索引需要维护，优化器在优化查询时也需要一一考虑，这会影响性能。
11.如果数据量很大，请优化您的修改/删除语句
避免同时修改或删除太多数据，因为这会导致CPU利用率高，从而影响其他人对数据库的访问。
反例：
//Delete 100,000 or 1 million+ at a time?
delete from user where id <100000;
//Or use single cycle operation, low efficiency and long time
for(User user：list){ delete from user； }
正面示例：
//Delete in batches, such as 500 each timedelete user where id<500;delete product where id>=500 and id<1000；
原因：要一次删除太多数据，可能有一个锁定等待超时超过错误，因此建议分批操作。
12.考虑在where子句中使用默认值而不是null
反例：
select * from user where age is not null;
正面示例：
select * from user where age>0; //Set 0 as default
原因：
· 如果将空值替换为默认值，则通常可以建立索引，与此同时，表达式将相对清晰。
13.尝试用工会全部代替工会
如果搜索结果中没有重复的记录，建议将union替换为union all。
反例：
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 all而不是union，这将提高效率。
14.尽可能使用数字字段。 如果字段仅包含数字信息，请尝试不要将其设计为字符类型。
反例：
`king_id` varchar(20) NOT NULL;
正面示例：
`king_id` int(11) NOT NULL;
原因：与数字字段相比，字符类型会降低查询和连接的性能，并会增加存储开销。
15.尽可能使用varchar / nvarchar代替char / nchar
反例：
`deptName` char(100) DEFAULT NULL
正面示例：
`deptName` varchar(100) DEFAULT NULL
原因：
· 首先，由于可变长度字段的存储空间很小，因此可以节省存储空间。
· 其次，对于查询，在相对较小的字段中进行搜索更为有效。
16.使用说明来分析您的SQL计划
在日常开发中编写SQL时，请养成习惯。 使用说明来分析您编写的SQL，尤其是索引。
explain select * from user where userid = 10086 or age =18;
是的，我们做到了最后。 希望您喜欢它，并对优化和加快SQL查询有个想法
Other stories you'll love* Related to optimization of File Handling - How to do Efficient File Handl
