SQL优化小技巧_select * 回表-优快云博客

SQL优化小技巧

一、避免使用 select *

使用select * 会一次查询表中所有的列数据，但是再实际场景中我们只需要其中几列数据，浪费了数据库库的很多资源，像内存和CPU等；

此外查询的数量通过万网络IO传输过程也会增加数据传输时间；select * 不会走覆盖索引，会出先大量的回表操作，导致SQL性能很低；

因此SQL语句尽量避免查询多余的列；

二、使用union all代替union

union：会对查询结果进行并集操作，去重，同进行默认的规则排序；但是这些操作需要遍历、排序和比较、非常耗费时间、CPU资源；

union all没有上面的去重和排序，相对会降低数据引擎的压力；但是会有重复数据；

三、小表驱动大表

使用小数据集去驱动查询大数据集；

in与exists使用；

select * from table1 where cloume in (select cloume from table2); 
-- table1大表，table2小表
select * from table2 where exists (select cloume from table2)
-- table1小表，table2大表

in中查询语句会先执行，若查询的结果数据集少，然后去查table表中大量的数据，查询数据会很快；

exists 中查询语句会后执行，做前边table1中查出来的数据小，然后再去匹配table2中大量数据，查询数据会很快；

in 适用左边大表，右边小表；
exists 适用左边小表，右边大表；

四、使用批量操作

	有大量数据需要插入时，要使用批量操作；否则每次都要请求数据库，性能会很差；

--mysql
insert into order(colume1,colume2,...)
values (value1,value2,...),(value3,value4),...

批量操作每次数量不要太大，建议每次控制再500条以内，进行分页操作

五、多用limit

根据某个条件查询数据的第一条时，不要查询所有数据，要机上limit 1，减少不必要的查询

六、使用增量查询

当操作数据量很大时，可以使用增量查询，比如按时间，每次查询500条记录，记录最后一条记录的时间，进行分页查询

七、高效的分页

查询数据为了避免一次返回过多数据造成接口性能，会对数据查询接口进行分页；一般使用limit关键字；

select id, name from user limit 1000000,500;

当查询数据量很多时就会出现性能问题；如上面的例子，mysql会查询出1000500条数据，然后会扔掉前面的1000000条，只给出后面的500条数据，很浪费时间；可以使用增量查询，如下所示：

select id, name from user where id > 1000000 limit 500;
select id, name from user where id between 1000000 and 1000500;
-- between and 要在唯一索引上分页，否则会出现每页大小不一致问题

八、使用链接查询代替子查询

查询数据需要从两张以上表中查询数据时，一般会有两种实现方式，1、子查询，2、链接查询

-- 子查询
select * from table1 where id in (select id from table2 where status = '0');

子查询都是通过嵌套查询来实现，句子简单、机构化，但是需要建立临时表，查询结束后会删除这些临时表，会有额外的性能消耗；因此可以使用链接查询，如下

select T1.* from table1  T1
inner join table2 T2 on T1.id = T2.id 
where T2.status = '0';

另外join表的数量的要太多，一般不要超过三个，若join表多了，可能会引起选错索引，导致性能下降；

除了上面的内连接（inner join），还有左连接（left join），右链接（right join）；使用内连接时，MySQL数据库引擎会自用使用小表去驱动大表，性能不会有太大问题；使用左连接时默认使用左边的表去驱动右边的表，所以注意左边的表要使用小表，右边的使用大表；右链接相反；

所以尽量使用内链接（inner join）

九、控制索引数量

索引可以提高查询的效率，但是并不是索索引越多越好；因为索引也很占用内存空间，当则增加或者删除数据时，相应的也要为其创建、删除索引，消耗性能，一般索引不超过5个；

当索引需求增大时，可以选择使用复合索引，因此在高并发的环境尽量使用复合索引；

十、选择合理的字段类型

	1.  能使用数字类型，就不使用字符串，字符串处理比数字慢
	2.  尽可能使用较小的类型，如用bit存布尔值，tinyint寸枚举类型值
	3.  长度固定的字符串，使用char类型
	4.  长度不固定字符串，使用varchar类型
	5.  金额字段使用decimal，避精度度丢失问题

十一、提升group by的效率

group by主要用于去重和分组，通常会和having一种使用，表示分组后在在根据一定条件过滤数据

select * from id,name from table1 group by id having i > 10;

上面的效率会比较底，可以使用where先缩小范围在进行分组

select * from id,name from table1 where i > 10 group by id;

十二、索引优化

查询MySQL执行计划：

explain select * from id,name from table1 where i > 10 group by id;

常见索引失效原因：

MySQL会进行索引选择，有时你建立了索引，但是可能不会用，可以使用 force index来强制查询sql走某个索引

@@@普通索引和主键索引

主键索引是聚集索引，普通索引是非聚集索引

聚集索引只能有一个，而非聚集索引可以有多个；

聚集索引在物理存储中是连续，非聚集索引在物理存储中不是连续的；

聚集索引掺入数据时较慢，在物理存储排序上花费时间较长

主键索引数的叶子节点时存储的是对应的数据，普通索引树的叶子节点是主键索引的id，通过这个id再去主键索引树查找对应的数据，这个动作也叫做回表；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S5Y9TNTs-1638089452859)(C:\Users\cxl\AppData\Roaming\Typora\typora-user-images\image-20211128145401835.png)]

	聚集索引掺入数据时较慢，在物理存储排序上花费时间较长

[外链图片转存中…(img-S5Y9TNTs-1638089452859)]

https://mp.weixin.qq.com/s/mGCzq-x5HoLLvl4jv56GfA