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

当前第2页 返回上一页

反例：

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语句内容请搜索

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

相关阅读 >>

批量执行sql语句的方法

sql存储过程获取汉字拼音头字母函数

sql中select语句的语法结构是什么？

深入了解sql注入和预防措施

sql语句练习实例之五 wms系统中的关于lifo或fifo的问题分析

sql左连接和右连接区别是什么？

sql server简单查询示例汇总

用sql语句创建学生表

mysql语句整理及汇总介绍

一文详解sql中的三值逻辑

更多相关阅读请进入《sql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL优化之如何写出高质量sql语句