MySQL优化之如何写出高质量sql语句-巨人网络通讯

主页 > 知识库 > MySQL优化之如何写出高质量sql语句

MySQL优化之如何写出高质量sql语句

前言

关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂。这篇文章就来给大家详细介绍了26条优化建议，下面来一起看看吧

1. 查询SQL尽量不要使用全查 select *，而是 select + 具体字段。

反例：

select * from student;

正例：

select id，name, age from student;

理由：

只取需要的字段，可以节省资源、减少CPU和IO以及网络开销。
select * 进行查询时，无法使用到覆盖索引，就会造成回表查询。
使用具体字段可以减少表结构变动带来的影响。

2. 使用预编译语句进行数据库操作

理由：

预编译语句可以重复使用计划，减少SQL编译所需要的时间
可以解决动态SQL所带来的SQL注入的问题
只传参数，比传递SQL语句更高效
相同语句可以一次解析，多次使用，提高处理效率

3. 禁止使用不含字段列表的 insert 语句

反例：

insert into values ('a', 'b', 'c');

正例：

insert into t(a, b, c) values ('a','b','c');

理由：

不含字段名的 insert 语句，很难区分到底对应的是什么字段，而且只能全值插入，可读性差。
一旦表结构发生改变，很难修改。

4. 尽量避免在 where 子句中使用 or 来连接条件

案例：新建一个user表，它有一个普通索引userId，表结构如下：

CREATE TABLE `user` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`user_id` int(11) NOT NULL,  
`age` int(11) NOT NULL,  
`name` varchar(30) NOT NULL,  
PRIMARY KEY (`id`),  
KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询userid为1 或者年龄为 18 岁的用户

反例:

select id, user_id, age, name from user where userid=1 or age =18

正例：

# 使用union all 
select id, user_id, age, name from user where userid=1 union all select * from user where age = 18
# 或者分开两条sql写
select id, user_id, age, name from user where userid=1; select * from user where age = 18

理由：

使用or可能会使索引失效，从而全表扫描。
对于 or + 没有索引的字段，如上面的 age 这种情况，假设它走了userId 的索引，但是走到 age 查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并，如果它一开始就走全表扫描，直接一遍扫描就完事。
mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

5. 使用 where 条件查询，要限定要查询的数据，避免返回多余的行，同时避免数据类型的隐式转换

假设 id 为 int 类型，查询 id = 1 的数据

反例：

select id, name from student where id = '1';

正例：

select id, name from student where id = 1;

理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。
隐式转换会导致索引失效

6. 静止在 where 子句中对字段进行表达式操作或函数转换，这将导致系统放弃使用索引而进行全表扫描

假设 user 表的 age 字段，加了索引，对其进行数据查询

反例：

select name, age from user where age - 1 = 20;

正例：

select name, age from user where age = 21;

理由：

age 加了索引，但是因为对它进行运算查询，导致索引不生效，大大的降低效率。

7. 尽量避免在 where 子句中使用 != 或 > 操作符，否则将引擎放弃使用索引而进行全表扫描。

（Mysql中适用）

反例：

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;

理由：

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

8. 对查询优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。

反例：

select name, age, address from user where address ='深圳' order by age ;

正例：添加索引再查询

alter table user add index idx_address_age (address,age)

9. where子句中考虑使用默认值代替 null

反例：(这种会全查所有数据)

select user_id, name, age from user where age is not null;

正例：

# 表字段age设置0为默认值代替null
select user_id, name, age from user where age > 0;
1
2

理由：

并不是说使用了 is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。
如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件 !=，> isnull，is not null 经常让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的。
如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

10. 如果查询结果只有一条或者只需要一条记录（可能最大/小值），建议使用 limit 1

假设现在有student学生表，要找出一个名字叫 Tom 的人.

CREATE TABLE `student` (  
`id` int(11) NOT NULL,  
`name` varchar(50) DEFAULT NULL,  
`age` int(11) DEFAULT NULL,  
`date` datetime DEFAULT NULL,  
`sex` int(1) DEFAULT NULL,  
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

反例：

select id，name from student where name='Tom '

正例

select id，name from employee where name='Tom ' limit 1;

理由：

加上 limit 1 分页后，只要找到了对应的一条记录，就不会继续向下扫描了，效率将会大大提高。
如果name是唯一索引的话，是不必要加上 limit 1 了，因为limit的存在主要就是为了防止全表扫描，从而提高性能，如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

11. 优化 limit 分页语句

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下

反例：

select id，name，age from student limit 10000,10

正例：

# 方案一 ：返回上次查询的最大记录(偏移量)
select id，name from student where id > 10000 limit 10;

# 方案二：order by + 索引
select id，name from student order by id  limit 10000,10;

# 方案三：在业务允许的情况下限制页数：

理由：

当偏移量大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量 + 要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
方案二使用 order by+索引，也是可以提高查询效率的。
方案三的话，建议跟业务讨论，有没有必要查这么后的分页。因为绝大多数用户都不会往后翻太多页。

12. 尽量避免向客户端返回过多数据量，使用limit分页

假设业务需求是，用户请求查看自己最近一年观看过的电影数据。

反例：

# 一次性查询所有数据回来
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 ;

13. 优化 like 语句

当用到模糊关键字查询使用 like 时，like很可能让索引失效。

反例：

SELECT * FROM student
WHERE name LIKE '%strive_day';
-- 或者使用 % 包裹
SELECT * FROM student
WHERE name LIKE '%strive_day%';

正例：

SELECT * FROM student
WHERE name LIKE 'strive_day%';

理由：

把 % 放前面，不会走索引查询。
把 % 放关键字后面，会走索引进行查询。
将 % 包裹关键字，也不会走索引查询。
无前置 %，只有后置 % 才会走索引查询

14. 尽量避免在索引列上使用mysql的内置函数

案例：查询最近七天内登陆过的用户(假设 loginTime 字段加了索引)

反例：

SELECT * FROM system_user user
WHERE Date_ADD(user.loginTime,Interval 7 DAY) >= now();

正例：

SELECT * FROM system_user user
WHERE user.loginTime >=Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

索引列上使用mysql的内置函数，索引会失效
如果索引列不加内置函数，会走索引查询

15. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则

假设有一个联合索引 (user_id, age)，user_id 在前，age 在后。

反例：

select user_id, name, age from user where age = 10;

正例：

# 符合最左匹配原则
select user_id, name, age from user where userid = 1 and age = 21;
# 符合最左匹配原则
select user_id, name, age from user where userid = 1;

理由：

当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、（k1,k2）和（k1,k2,k3）三个索引，这就是最左匹配原则。
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

16. 在适当时候，使用覆盖索引。

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

反例：

# like模糊查询，不走索引
select user_id, name, age from user where user_id like '%123%'

# id为主键，那么为普通索引，即覆盖索引。
select user_id, name, age from user where userid like '%123%';

17. 删除冗余和重复索引

反例：

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

正例:

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

理由：

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

18. Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

都满足SQL需求的前提下，优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

反例:

select name, age from tab1 t1 left join tab2 t2  on t1.age = t2.age where t1.id = 2;

正例：

select name, age from (select * from tab1 where id = 2) t1 left join tab2 t2 on t1.age = t2.age;

理由：

如果 inner join 是等值连接，或许返回的行数比较少，所以性能相对会好一点
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少

19. 如果插入数据过多，考虑批量插入

反例：

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>

理由：

批量插入性能好，减少时间损耗。

20. 尽量少用 distinct 关键字

distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

反例：

# 去重多个字段
SELECT DISTINCT * from  user;

正例：

select DISTINCT name from user;

理由：

带 distinct 的语句 cpu 时间和占用时间都高于不带distinct的语句。
因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，cpu时间。

21. 不要有超过5个以上的表连接

理由：

连表越多，编译的时间和开销也就越大
连表可读性差，把连接表拆开成较小的几个执行，可读性更高

22. 数据量大的时候，如何优化更新语句。

数据量大的时候，需要避免同时修改或删除过多数据，同时会造成cpu利用率过高，从而影响别人对数据库的访问。

反例：

# 一次删除10万或者100万+条数据
delete from user where id  1000000;
# 或者采用单一循环操作，效率低，时间漫长
for（User user：list）{delete from user;}

正例：

# 分批进行删除,如每次500   
delete user where id  500
delete user where id >= 500 and id  1000；
...
delete user where id >= 999500 and id  1000000;

理由：

一次性删除/更新太多数据，可能会有 lock wait timeout exceed 的错误，所以建议分批操作。

23. 合理使用 exist 和 in

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工SQL

反例：:

select * from A where deptId in (select deptId from B);

这样写等价于：

先查询部门表B
select deptId from B
再由部门deptId，查询A的员工
select * from A where A.deptId = B.deptId

可以抽象成这样的一个循环语句：

List> resultSet ;    
for(int i = 0; i  B.length; i ++) {
 for(int j = 0; j  A.length; j ++) {
     if(A[i].id == B[j].id) {
         resultSet.add(A[i]);
            break;          
        }       
     }    
 }

我们也可以用exists实现一样的查询功能

select * from A where exists (select 1 from B where A.deptId = B.deptId);

上述代码等价于：

select * from A，先从A表做循环
select * from B where A.deptId = B.deptId，再从B表做循环.

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得以保留。

同理，可以抽象成这样一个循环：

List> resultSet;    
for(int i = 0; i  A.length; i ++) {
 for(int j = 0; j  B.length; j ++) {
     if(A[i].deptId == B[j].deptId) {
         resultSet.add(A[i]);
            break;          
            }       
        }    
    }

理由：

数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反如果每次单独查询，建立了上百万次链接，申请链接释放反复重复
mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用 in，如果B的数据量大于A，即适合选择exist

24. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

反例：

`king_id` varchar（20） NOT NULL COMMENT '123'

正例：

 `king_id` int(11) NOT NULL COMMENT '123'

理由：

相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

25. 尽量用 union all 替换 union

如果检索结果中不会有重复的记录，推荐 union all 替换 union

反例：

select * from user where userid = 1
union
select * from user where age = 20

正例：

select * from user where userid = 1
union all
select * from user where age = 20

理由：

如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。
如果已知检索结果没有重复记录，使用 union all 代替 union，这样会提高效率。

26. 如果字段类型是字符串，where时一定用引号括起来，否则将导致索引失效

反例：

select * from user where userid = 1;

正例：

select * from user where userid ='1';

理由：

第一条语句未加单引号就不走索引，这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

总结

到此这篇关于MySQL优化之如何写出高质量sql语句的文章就介绍到这了,更多相关MySQL优化sql语句内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:

详细聊聊MySQL中慢SQL优化的方向
浅谈MySQL之select优化方案
Mysql纵表转换为横表的方法及优化教程
MySQL千万级数据表的优化实战记录
MySql子查询IN的执行和优化的实现
带你快速搞定Mysql优化
mysql 数据插入优化方法之concurrent_insert
mysql优化之query_cache_limit参数说明
mysql查询优化之100万条数据的一张表优化方案
MYSQL 的10大经典优化案例场景实战

标签：仙桃衡水黄山崇左湘潭湖南铜川兰州

巨人网络通讯声明：本文标题《MySQL优化之如何写出高质量sql语句》，本文关键词；如发现本文内容存在版权问题，烦请提供相关信息告之我们，我们将及时沟通与处理。本站内容系统采集于网络，涉及言论、版权与本站无关。

电销机器人到底如何运作的？

很多人对电销机器人到底如何运作存在着巨大的好奇心，那么今天，小编就带大家看一下机器人的运作流程！电销...

10-24

电话机器人的兴起对电销行业来说是“惊喜”还

随着互联网大数据的普及，许多电销行业如雨后春笋般争先恐后地冒出来。而今电销行业的混乱，让各位涉足电销...

10-24

百应电话机器人有哪些功能？百应电话机器人好

百应智能电话机器人能够协助企业进行呼入接待、外呼回访等服务场景，能够经过不断的培训学习，逐步完善成专...

10-24

客服外呼系统都有哪些优势？

当前呼叫中心电话系统被企业用于业务电销以及客户服务，然而随着企业需求不断扩大，原来传统的自建呼叫中心...

10-24

电销机器人效果怎么样？智能电话机器人可行吗

几百数千年后，或许没人会记得卡斯帕罗夫曾是世界第一的围棋高手，但人们会记得，他败给了电脑深蓝。那虽算不...

10-24

百应智能外呼系统，贷后管理科技升级

去年底开始，接连出台政策和法规加强对消费金融等金融形态进行规范。强监管下，行业走向规范发展，行业竞...

10-24

百应智能外呼系统引领外呼行业新风向

电话销售：您好，我这边是XX地产，给您推荐一下精装酒店式公寓，有兴趣了解一下吗? 客户：具体位置在哪里啊?...

10-24

小贷企业为什么需要电销机器人？

随着信贷行业逐步进入我们的生活中，越来越多人选择借款来处理资金难题。信贷企业不断涌现，业务开展迅猛，行...

10-24

雷霆电话机器人是如何转接人工的？

当我们在拨通移动、联通或电信运营商的客服电话时，总会要先经过一些机械化的选择，然后成功连接人工坐席反馈...

10-24

米卡迪电话机器人效果怎么样？

外呼电话机器人，之所以能智能的与客户无忧交流，因为运用了下面这么多高技术。 ASR 语音识别米卡迪电话机器人...

10-24

灵声电话机器人效果怎么样？好用吗

外呼电话机器人，之所以能智能的与客户无忧交流，因为运用了下面这么多高技术。灵声电话机器人效果怎么样？...

10-24

电销机器人要想效果好，话术定制有多重要

电话机器人是基于智能语音技术的智能营销工具。它会像真人一样主动拨打客户的电话，介绍产品并回答客户的咨询...

10-24

智能电话机器人的实现原理，使用安全吗？

随着科学技术的进步和人工智能的发展，越来越多的智能产品已经进入我们的工作和生活。智能手机、智能电视、智...

10-24

福州哪里有电话机器人研发公司？

人工智能再次成为各行各业关注的焦点，自从这个概念首次提出以来，60年已经过去了。自从AlphaGo以巨大优势击败人...

10-24

电销机器人销售能力怎么样？

自从电话机器人一出现，在电销行业火爆了起来。面对电话机器人的各种优势，企业跃跃欲试，但又害怕效果不理...

10-24

推荐文章

商标转让,知意商标转让网,公司注册、商标注册和品牌注册三者的区别？

微软Windows Server 2019首个预览版Build 17623发布（附下载地址）

python flask开发的简单基金查询工具

网络传播和下载的版权补偿金制度的适用

perl中heredoc使用说明

营销缺位：避暑经济还需发出“好声音”

科大讯飞闪耀世界人工智能大会：人工智能+未来已来

vssvc.exe是什么进程

上一篇：MySQL 慢日志相关知识总结

下一篇：MySQL高级特性——数据表分区的概念及机制详解

一起分享吧

MySQL优化之如何写出高质量sql语句

前言

1. 查询SQL尽量不要使用全查 select *，而是 select + 具体字段。

2. 使用预编译语句进行数据库操作

4. 尽量避免在 where 子句中使用 or 来连接条件

5. 使用 where 条件查询，要限定要查询的数据，避免返回多余的行，同时避免数据类型的隐式转换

6. 静止在 where 子句中对字段进行表达式操作或函数转换，这将导致系统放弃使用索引而进行全表扫描

7. 尽量避免在 where 子句中使用 != 或 > 操作符，否则将引擎放弃使用索引而进行全表扫描。

8. 对查询优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。

9. where子句中考虑使用默认值代替 null

10. 如果查询结果只有一条或者只需要一条记录（可能最大/小值），建议使用 limit 1

11. 优化 limit 分页语句

12. 尽量避免向客户端返回过多数据量，使用limit分页

13. 优化 like 语句

14. 尽量避免在索引列上使用mysql的内置函数

15. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则

16. 在适当时候，使用覆盖索引。

17. 删除冗余和重复索引

18. Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

19. 如果插入数据过多，考虑批量插入

20. 尽量少用 distinct 关键字

21. 不要有超过5个以上的表连接

22. 数据量大的时候，如何优化更新语句。

23. 合理使用 exist 和 in

24. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

25. 尽量用 union all 替换 union

26. 如果字段类型是字符串，where时一定用引号括起来，否则将导致索引失效

总结

QQ咨询

电话咨询

MySQL优化之如何写出高质量sql语句

前言

1. 查询SQL尽量不要使用全查 select *，而是 select + 具体字段。

2. 使用预编译语句进行数据库操作

4. 尽量避免在 where 子句中使用 or 来连接条件

5. 使用 where 条件查询，要限定要查询的数据，避免返回多余的行，同时避免数据类型的隐式转换

6. 静止在 where 子句中对字段进行表达式操作或函数转换，这将导致系统放弃使用索引而进行全表扫描

7. 尽量避免在 where 子句中使用 != 或 > 操作符，否则将引擎放弃使用索引而进行全表扫描。

8. 对查询优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。

9. where子句中考虑使用默认值代替 null

10. 如果查询结果只有一条或者只需要一条记录（可能最大/小值），建议使用 limit 1

11. 优化 limit 分页语句

12. 尽量避免向客户端返回过多数据量，使用limit分页

13. 优化 like 语句

14. 尽量避免在索引列上使用mysql的内置函数

15. 使用联合索引时，注意索引列的顺序，一般遵循 最左匹配原则

16. 在适当时候，使用覆盖索引。

17. 删除冗余和重复索引

18. Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

19. 如果插入数据过多，考虑 批量插入

20. 尽量少用 distinct 关键字

21. 不要有超过5个以上的表连接

22. 数据量大的时候，如何优化更新语句。

23. 合理使用 exist 和 in

24. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

25. 尽量用 union all 替换 union

26. 如果字段类型是字符串，where时一定用引号括起来，否则将导致索引失效

总结

QQ咨询

电话咨询

15. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则

19. 如果插入数据过多，考虑批量插入