sql table access inmemory full 优化 sql常用优化

4.2.常见SQL使用细节

4.2.1. 查询语句中不要使用select *

sql语句查询时，只查需要用到的列，多余的列根本无需查出来。

4.2.2. 尽量减少子查询，使用关联查询（left join,right join,inner join）替代

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和 连接查询。

子查询的例子如下：

select * from order
where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

这时可以改成连接查询。join的表不宜过多, 根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。
如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。

并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。所以我们应该尽量控制join表的数量。

如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。

不过之前有些ERP系统，并发量不大，但业务比较复杂，需要join十几张表才能查询出数据。所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

join使用最多的是left join和inner join。

left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。

如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。

4.2.3. 小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。时如果想查一下，所有有效的用户下过的订单列表。可以使用in关键字实现：

select * from order where user_id in (select id from user where status=1)

也可以使用exists关键字实现：

select * from order where exists (select 1 from user where order.user_id = user.id and status=1)

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

4.2.4.where子语句

在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移

4.2.4.1.(🌟索引失效🌟)减少在where子句中对字段进行null值判断:

mysql会自动判断数据的分布情况 判断数据中 null 多 还是 not null 多, 然后决定走不走索引.

如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，

然后这样查询： select id from t where num=0

4.2.4.2.(🌟索引失效🌟)避免在where子句中对字段进行表达式操作:

select  uid from user_test  WHERE uid*10=40;

-- 上面的sql对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

select  uid from user_test  WHERE uid=40/10;

4.2.5. in 里的数据不要太多

4.2.6. (🌟索引失效🌟)or 的查询尽量用 union或者union all 代替

如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描

4.2.7.用union all代替union

我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。

4.2.8.(🌟索引失效🌟)应尽量避免在 where 子句中使用!=或<>操作符，

否则将引擎放弃使用索引而进行全表扫描。

4.2.9.order by 排序优化

排序时,使用有索引的字段进行排序

使用order by排序时,会出现两种情况 (explain查看Extra字段)

1: using fileSort : 全表扫描,读取出数据,然后再排序缓冲区进行排序. (排序字段没有索引)

2: using index: 通过索引直接返回有序的数据. 不需要额外排序(有索引,效率高)

4.2.10.批量插入操作

Mybatis-plus 中 方法:

orderMapper.insertBatch(list):

insert into order(id,code,user_id) 
values(123,'001',100),(124,'002',100),(125,'003',101);
这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。

这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。

但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

4.2.11. 多用limit

使用limit 1，只返回满足条件的一条数据即可。

此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。

4.2.12.高效的分页

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。在mysql中分页一般用的limit关键字.如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

select id,name,age 
from user limit 1000000,20;

mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。优化sql：

select id,name,age 
from user where id > 1000000 limit 20;

先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

还能使用between优化分页。

select id,name,age 
from user where id between 1000000 and 1000020;

需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。

4.2.13.控制索引的数量

众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。

因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。

阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。

mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。

4.2.13.1.指定查询的索引

当sql查询的字段有多个索引的时候,mysql优化器会自动选择一个索引进行查询,我们也可以通过sql字段进行自定义,

-- use index(索引): 推荐使用指定的索引  (最终用不用该索引,还需要mysql自己判断)
select * from use index(索引A)  

-- ignore index(索引) : 忽略掉这个索引
select * from  ignore index(索引A)

-- force index(索引): 强制使用该索引
select * from  force index(索引A)

4.2.14.选择合理的字段类型

4.2.14.1.char 与 varchar

char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。
varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。

varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度（memory 引擎也一样）。
对效率要求高用 char，对空间使用要求高用 varchar。

我们在选择字段类型时，应该遵循这样的原则：

能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。
…

4.2.15.count 优化 速度:count(*)>count(1)>count(字段)

Innodb引擎的使用如下 (MyISAM默认存了数据总数,所以效率最高)

1: count(字段):遍历整张表 会把每一行的字段值取出来,然后返回

2: count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加

3: count(*): Innodb引擎,特意做了优化,不会取出值,直接服务层进行累加

但 在判断有无符合条件的记录时建议不要用 count

4.2.16.update优化 (避免出现表锁)

InnoDB引擎使用update时,会有行锁/表锁两种模式, 如果where 字段没有索引的时候会升级成表锁

4.2.17.创建表时使用同一的编码

MySQL多表联查时,如果表的字符集不一样,会有一个数据类型转换的过程.

例如 utf8 与 utf8mb4 前者是3字节unicode编码，后者是4字节unicode编码. 此时如果多表查询, 则索引会失效

4.2.18.(🌟索引失效🌟)不满足最左前缀原则

对于复合索引来说，要遵守最左前缀法则

例如组合索引( student_weight, student_height, student_sex ) 使用的时候,

可以student_weight 或者student_weight , student_height. 禁止直接student_height , 或者 student_sex . 会导致联合索引失败

注意:student_weight, student_height, student_sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.

前三条sql都能命中索引,

中间两条由于不符合最左匹配原则,索引失效.

最后一条sql 由于有最左索引student_weight 所以索引部分成功

CREATE INDEX weight_height_index ON student ( student_weight,student_height, student_sex );

explain select * from  student where student_weight=10  ; 
explain select * from  student where student_weight=10 and student_height=10; 
explain select * from  student where  student_height=10 and student_sex=0 and student_weight=10 ;

explain select * from  student where student_height=10 and student_sex=0;
explain select * from  student where student_height=10; 

explain select * from  student where student_weight=10 and student_sex=0;

4.2.19.(🌟索引失效🌟)like 查询左边有%

不建议使用%前缀模糊查询:

建立 student_sn (编号) 索引

CREATE INDEX sn_index ON student (student_sn);

例如 : LIKE “%name” 或者 LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

如:

explain select * from  student where  student_sn like  '%0000';

运行结果: 没有启动索引

再运行

explain select * from  student where  student_sn like  '93599%';

运行结果 : 启动 索引

4.2.20.(🌟索引失效🌟)条件查询的字段和值的类型不一致, 则索引不生效

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

不断补充