mysql查询最大版本记录_MySQL 8.0 版本 SQL查询优化

1、尽量避免使用子查询

例:

SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='Yoona');

子查询在MySQL5.5版本里，内部执行计划器是这样执行的:

先查外表再匹配内表，而不是我们认为的先查出整个内表t2，作为临时表给外表使用。

( 先从t1表中取出一条记录，查询内表，从内表查询中的结果判断此次取出的外表的记录是否符合要求，依次一条一条取、一条一条查，循环N遍，浪费时间资源 )

有以下子查询示例：

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);

你肯定认为这个 SQL 会这样执行：

1、SELECT t2.b FROM t2 WHERE id < 10;

2、得到结果: 1,2,3,4,5,6,7,8,9

3、select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);

但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中，优化器认为这样效率更高。也就是说，优化器会将上面的 SQL 改写成这样：

select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);

因此，当外表的数据很大时，查询速度会非常慢。

MySQL子查询优化的技术或优化策略，包括三种，分别为：

semi-join：半连接优化技术，本质上是把子查询上拉到父查询中，与父查询的表做 semi-join 的操作。关键词是“上拉”。

MySQL提供5种优化策略，来进一步优化semi-join操作，分别是：

- DUPS_WEEDOUT/重复剔除

- LOOSE_SCAN/松散扫描

- FIRST_MATCH/首次匹配

- MATERIALIZE_LOOKUP/索引式物化

- MATERIALIZE_SCAN/扫描式物化

这5种子优化策略，需要通过代价估算完成最优选择。

Materialization：物化子查询，子查询的结果通常缓存在内存或临时表中

EXISTS strategy：把半连接转换为EXISTS操作。本质上是把父表的条件下推到子查询中关键词是“下推”。(如上例)

子查询格式

可选的优化策略

IN / = / ANY

Semi-join， Materialization，EXISTS strategy

NOT IN / <> / ALL

Materialization， EXISTS strategy

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略，会以 exists 方式执行，优化的方法也很简单，改成 join 即可(这里是 delete，不用担心重复行问题)：

DELETE biz_customer_incoming_path

FROM biz_customer_incoming_path a

JOIN biz_customer_incoming b

WHERE a.bizCustomerIncoming_id=b.id and b.cid='7Ex46Dz22Fqq6iuPCLPlzQ';

延伸：为什么子查询比连接查询(LEFT JOIN)效率低

示例：

SELECT goods_id,goods_name

FROM goods

WHERE goods_id = (select max( goods_id ) from goods );

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

优化方式：

可以使用连接查询(JOIN)代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

2、用IN来替换OR

低效查询

SELECT * FROMt WHERE LOC_ID= 10ORLOC_ID= 20 ORLOC_ID= 30;

高效查询

SELECT * FROM t WHERE LOC_IN IN(10,20,30);

对于许多数据库服务器而言，IN( )列表不过是多个OR语句的同义词而已，因为IN和OR在逻辑上是等同的。不仅是在MySQL数据库服务器，对于许多其他的数据库服务器使用到IN查询时，都是按照如下方式处理的:

对IN列表中的数值进行排序。

对于查询的匹配，每次使用二分查找去匹配IN列表的数值。

所以对于第2步，每次比较的算法复杂度大概为O(log n)。相反，对于同样逻辑的OR列表，每次都要遍历，所以OR相应的算法复杂度为O(n)(因此对于遍历非常大的OR列表，会很缓慢！)。

因此，在了解了IN和OR的区别之后，每次优化，我们可以采用如下方式：

尽量将能使用IN来代替OR查询。

对IN列表中的数据，写SQL的时候就排好序，避免MySQL来做这个工作。

延伸：同理，对于连续的数字能用between就不要用in了，between只需要比对两个数字，而in全都要比对。

3、读取适当的记录LIMIT M,N，而不要读多余的记录

SELECT * FROM t WHERE 1;

----->

SELECT * FROM t WHERE 1 LIMIT 10;

以及快速定位范围：

select id,name

from table_name limit 866613, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

select id,name from table_name

where id> 866612 limit 20

据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引(假设为id)：

由于普通搜索是全表搜索，适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索，大大缩小搜索的范围，从而提高搜索效率。

这个优化思路就是告诉数据库：「你别数了，我告诉你，第10001条数据是这样的，你直接去拿吧。」

4、避免数据类型不一致

SELECT COUNT(*)

FROM p_video_circle_relation a

LEFT JOIN p_video_info b

ON a.video_id = b.work_id

WHERE a.circle_id = 212307047 ;

SQL关联查询消耗662ms

仔细观察发现关联字段video_id和work_id的数据类型并不一致，video_id是bigint类型，work_id是varchar类型，关联查询时必须将关联字段转换成相同的类型才能进行比较，数据越多，转换需要的时间越长

将work_id修改为bigint类型后，查询仅需13ms

5、总和查询可以禁止排重用union all

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据(业务上需要不重复)。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

另外，如果排序字段没有用到索引，就尽量少排序。

6、批量INSERT插入

常用的插入语句如：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0', 'userid_0', 'content_0', 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)

VALUES ('1', 'userid_1', 'content_1', 1);

修改成：

INSERT INTO`insert_table`(`datetime`,`uid`,`content`,`type`)

VALUES('0','userid_0','content_0',0),('1','userid_1','content_1',1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量(MySQL的binlog和innodb的事务让日志)减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

7、尽量不用select *

SELECT *增加很多不必要的消耗(cpu、io、内存、网络带宽)；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。

8、区分in和exists

select * from 表A

where id in ( select id from 表B )

上面sql语句相当于

select * from 表A

where exists ( select * from 表B where 表B.id=表A.id )

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)

如果是exists，那么以外层表为驱动表，先被访问，然后根据外表的数据去和内表进行比较和判断；

而如果是IN，那么先执行子查询，再将内表的数据拿去和外表进行比较与判断。

因此，如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

延伸：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

② 数据库表结构的优化：使得数据库结构符合三大范式与BCNF

③ 系统配置的优化

④ 硬件的优化