SQL优化/MySQL优化干货

SQL优化

(1)select *、insert *改为具体的字段

1.减少回表（结合索引调整之后形成覆盖索引，一方面有效加快查询速度，另一方面有效避免回表导致的索引失效问题）；

2.减小服务器和网络资源开销；

3.减少resultMap和Date层实体类不匹配问题（select * 当表结构改动[如增加字段]时，需要同步修改resultMap和实体类，否则会报错）；

(2)索引调整

主旨：

1.符合索引设计原则

2.深度结合业务了解——最左匹配原则+覆盖索引

索引设计原则：

1.最左匹配原则

2.索引字段：

需要建立索引的字段：查询条件、分组、排序操作的字段以及常进行关联查询的字段

不应该建立索引的字段：区分度不大的字段、频繁增删改的字段

3.限制索引的数目：一般不超过五个

4.尽量使用数据量小的索引，索引字段值很长时，使用前缀索引

(3)针对索引失效问题的sql优化：

注意点：业务场景、数据量、修改前后效率对比

1.隐式转换导致的索引失效

2.在where条件中对索引字段进行了函数式操作（8.0版本后支持）

简单的例子：日期时间型函数操作

解决方案：借助逻辑，例如where YEAR(updateTime)=2023年判断某条记录是否是今年的，可以变成where updateTime >= YEAR(now())

3.in子查询问题

产生临时表，结果集无法使用索引，当结果集较大时，性能很差，引发慢查询

解决方案：

①符合小表驱动大表的原则

②通常是使用连接查询代替子查询（有时有效，有时没效果，需要结合业务，实际开发中注意，多分析、多测试）

4.or、is not null、not in、not exists导致索引失效

规范表设计，增加not null约束等。

5.模糊查询以%开头的情况

6.违背最左匹配原则

7.MySQL优化器认为全表扫描更快时

(4)针对一些糟糕的逻辑进行的优化

1.exist和in的子查询，符合小表驱动大表的原则

针对in子查询优化为join关联查询的例子

2.针对数据量确定的查询使用limit，而不是查出结果集后使用for循环过滤

3.减少不必要的排序

4.合并结果集时，无重复数据则使用union all 代替union

5.强制索引的使用

(5)关于join关联查询的思考（分库分表情况下）

数据库是最底层的，瓶颈往往是数据库。建议数据库只是作为数据存储的工具，而不要添加业务上去。JOIN本质上是嵌套查询，小表驱动大表，且通过索引字段进行关联，所以一旦数据量很大关联查询的效率就很难保证。推荐根据索引从单表取数据，然后关联关系放在程序里实现。

1.不推荐使用join的原因

①DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；

②分布式的分库分表情况下不建议跨库join。目前MySQL的分布式中间件，跨库join表现不良。

③针对需要修改表结构的时，对应修改单表查询的SQL比较容易，而join写的SQL语句要修改，当系统比较大时，成本较高，不易维护。

2.适用场景

①可以方便的缓存单个查询结果的时候

②可以将数据分布到不同的MySQL服务器上的时候

③join逻辑能够使用in子查询的方式进行代替的时候

④针对并发场景多，DB查询频繁，进行了分库分表的情况

3.解决方案：

在业务层，单表查询出数据后，作为条件给下一个单表查询。

4.应用层关联的优势

①提高缓存的效率，有效避免缓存失效

②将查询分解后，执行单个查询可以减少锁的竞争。

③更容易对数据库进行拆分，更容易做到高性能和可扩展。

④提高查询效率。查询id集的时候，使用in子查询代替关联查询，可以让MySQL按照ID顺序进行查询，这比随机的关联要更高效。

⑤可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需

要重复地访问一部分数据。

⑥将MySQL的嵌套循环关联，改为在应用中实现哈希关联。某些场景哈希关联的效率要高很多。

(6)关于大表(100w+数据量)的分页查询优化（LIMIT优化）

在翻页比较少的情况下，LIMIT是不会出现任何性能上的问题的，当查询偏移量大于10万之后，查询的时间明显增加。

大表分页查询的本质问题在于：

对于SQL：select * from xxx limit 1000000, 10;

LIMIT分页查询会从数据库的第一条记录开始扫描——先查出前1000010条记录，然后抛弃前面1000000条。

优化方法：

old：select * from xxx limit 1000000, 10;

主旨：借助索引，在索引列上完成分页操作

方法3、5相对更可取

1.使用覆盖索引——有效的减少回表

2.使用id限定优化——极大地优化查询速度

这种方法需要数据表满足其id是连续递增的。

better：select * from xxx where id >= 1000001 limit 10;

3.使用子查询优化——借助主键索引定位偏移

先定位偏移位置的id，然后再往后查询，适用于id递增的情况。

better：select * from xxx where id >= (select id from xxx limit 1000000,1) limit 10;

4.延迟关联优化——借助主键索引确定分页数据

better：select * from xxx t1 join (select id from xxx limit 1000000, 10) t2 on t1.id = t2.id;

5.记录上次查询结束的位置

使用一个变量记录记录上次分页查询结束位置的id，下次分页时直接从这个id开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作。