java面经查缺补漏之三十天（继续学习查询优化）

怼烎@ 2023-07-17 14:13 12阅读 0赞

相比于数据库索引优化，查询优化更加复杂，受mysql版本的限制，以及具体情况的限制，有时候要具体问题具体分析，用explain去分析解释语句。

**1.数据库汇总，相关的指令与关键字？**

参考：[https://blog.csdn.net/qq\_37969433/article/details/102637226?depth\_1-utm\_source=distribute.pc\_relevant.none-task&utm\_source=distribute.pc\_relevant.none-task][https_blog.csdn.net_qq_37969433_article_details_102637226_depth_1-utm_source_distribute.pc_relevant.none-task_utm_source_distribute.pc_relevant.none-task]

**2.介绍一下你知道的数据库的相关知识？**

创建修改表，增删改查，内外连接，行表锁，数据库索引优化，数据库查询优化，innodb和mylasm引擎的区别，ACID，隔离级别，三范式，乐观锁，悲观锁，主从复制，B+树。

**3.查询优化之mysql优化器能够处理的优化类型？**

（1）重新定义关联查询的顺序（最重要的一部分）：通过评价不同顺序时的成本来选择代价最小的关联查询顺序，它是由嵌套循环关联实现的。关联的不能太多，否则计算成本压力太大。

（2）将外连接转成内链接：有时候外连接由于一些where条件之类的，使得外连接等价于内连接，数据库能识别这些。

（3）使用等价变换原则：使用等价变换来简化某些规范表达式，例如某些计算什么的。

（4）min和max，count的优化：min和max如果索引包含查找条件的话，那么最左端和最右端就是最小和最大。mylasm用的是一个变量来存放数据表的行数。

（5）预估并转化为常数表达式：用户的自定义变量在查询中没有发生改变就换转成一个常数来处理。

（6）覆盖索引扫描：当索引包含查询条件的时候，无需查询对应的数据行。

（7）提前终止查询：在发现已经满足查询需求的时候，它总是能立刻停止查询。例如：limit或者发现了一个不成立的条件。

（8）等值传播：SELECT film 表一 INNER JOIN 表二 USING（filmid） WHERE filmid>500.那么这个大于500不仅适用于表一也适用于表二。

（9）优化器还会向存储引擎要一些统计信息作为参照。

总而言之，不要自以为比优化器更聪明。

**4.查询优化之mysql优化器的局限性？**

（1）UNION的限制：它是用临时表实现的。子句UNION子句limit 20.这样它会将两个子句查询出的东西放到临时表当中，再取20，效率低。所以我们可以，（子句limit20UNION子句limit20）limit 20，也就是分别限制20，再总体限制20.

（2）并行执行限制：无法利用多核特性来并行执行查询。

（3）等值传递限制：当in中的列表很大，关联using的时候，需要将in列表复制到各个表中，使得优化和执行很慢。

（4）不支持松散查询限制：无法按照不连续的方式扫描一个索引，他做的是定义一个起点，定义一个终点，即使需要的只是其中的几条数据，他也要扫描每一个条目。

（5）最大最小值优化限制：索引包含查询的字段，这个才会起到优化的作用。

（6）在同一个表上不能同时执行修改和查询操作限制。

（7）关联子查询限制：最糟糕的是where条件中包含in的子查询语句，它会将外部的条件压入到内部，然后内部还处理不了，又要回去扫描全表。（可以将子查询修改成关联查询）

比如：

![20200322192120605.png][]

修改成

![20200322192200589.png][]

**5.查询优化之优化特定的查询？**

（1）COUNT()的一个优化。

它的作用是返回某个列不为空的数量，count（列），也可以返回结果集的行数，count（\*），这里的\*不是扩展到所有列，而是忽略所有列，直接统计行数。

它的一个优化是，有时候不需要准确的记录行数，我们可以用explain来估算近似值，它不需要真正的去执行，成本很低。

（2）优化子查询。

尽量用关联查询去替代。

（3）优化limit分页。

最主要的问题就是offset偏移引起的扫描又抛弃，比如limit 10000 20

一个解决方法就是索引覆盖，另一个方法就是利用书签记录上次取数据的位置，然后再回溯。

（4）UNION的优化

一个是前面提到的在子句中都加入limit，另一个就是除非确实需要服务器消除重复的行，否则一定要使用UNION ALL，没有ALL，会加上distinct选项，代价很高。

[https_blog.csdn.net_qq_37969433_article_details_102637226_depth_1-utm_source_distribute.pc_relevant.none-task_utm_source_distribute.pc_relevant.none-task]: https://blog.csdn.net/qq_37969433/article/details/102637226?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
[20200322192120605.png]: /images/20230528/6663894f7ea5497487f809b09f94131c.png
[20200322192200589.png]: /images/20230528/beedd4b2e2a049d3bb5bfbd2aeee8357.png