30条SQL优化建议SQL优化手册SQL性能调优MySQL关系型数据库

本文链接：https://blog.csdn.net/seeker1994/article/details/138999371

手册下载：https://download.csdn.net/download/liuning940307/89319178https://download.csdn.net/download/liuning940307/89319178

30条SQL优化建议SQL优化手册SQL性能调优MySQL关系型数据库

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

反例子：

select * from employee;

正例子：

select id，name from employee;

理由：

只取需要的字段，节省资源、减少网络开销。

2、不要有超过5个以上的表连接

连表越多，编译的时间和开销也就越大。
把连接表拆开成较小的几个执行，可读性更高。
如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

3、exist & in的合理利用

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

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

这样写等价于：

先查询部门表Bselect 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;

          }

       }

    }

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

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

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

那么，这样写就等价于：

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

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

   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。

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

反例：

king_id` varchar（20） NOT NULL COMMENT '守护者Id'

正例：

`king_id` int(11) NOT NULL COMMENT '守护者Id'`

理由：

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

5、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

6、尽量避免向客户端返回过多数据量。

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

反例：

//一次性查询所有数据回来

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 ;

7、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

反例：

select job，avg（salary） from employee group by job

having job ='president' or job = 'managent'

正例：

select job，avg（salary） from employee

where job ='president' or job = 'managent' group by job；

8、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

反例：

select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

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

9、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

反例：

select * from user where age-1 =10；

正例：

select * from user where age =11；

理由：

虽然age加了索引，但是因为对它进行运算，索引直接迷路了

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

表结构：（有一个联合索引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优化器有关的。