3、查询性能优化
一、查询变慢的原因
Mysql执行查询时会有很多子任务，查询的声明周期大致可以分为：
从客户端，到服务器，然后在服务器上进行解析，生产执行计划，执行，并返回结果给客户端。
其中最重要的一环是“执行”，这其中包含了大量为检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
查询性能低下最根本的原因是访问的数据太多。
某些查询不可避免地需要筛选大量的数据，但大多数情况，性能低下的查询都可以通过减少访问数据量的方式进行优化。
1.1.对于性能低下的查询，一个有效的手段是通过下面两步来进行分析：
- 确认应用程序是否在检索超过需要的数据，通常意味着访问太多的行，但也会有时候访问太多的列
- 确认mysql服务器层是否在分析大量超过需要的数据行。
请求不需要的数据会造成多项资源的浪费，如网络开销、CPU以及内存资源等。一定不要有select * 这样的语句。
1.2.判断mysql是否做了大量额外的扫描
有三个指标衡量其开销：
1、 响应时间
响应时间 = 服务时间 + 排队时间。服务时间是查询真正话费的时间，排队时间是由于资源竞争，如IO或锁，等待执行的时间。目前的mysql无法精确计算各个部分的时间，因此，在不同类型的应用压力下，响应时间没有统一的规律或公式。
2、 扫描行数
3、 返回行数
分析扫描行数和返回行数，在一定程度上说明了查询效率高不高
理想状态，扫描行数和返回行数是相同的，但更常见的情况是，扫描的行数和返回的行数比值在1：1到10：1之间，有时会更大。
1.3.扫描的行数和访问类型
一个合适的索引。可以让查询使用合适的访问类型，尽可能只扫描需要的数据行。
当我们发现了查询需要扫描大量的数据但是只返回了少数的行，那么通常可以尝试下面的技巧优化：
1、 使用索引覆盖扫描。把所有需要的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
2、 改变库表结构。例如使用单独的汇总表
3、 重写这个复杂的查询
二、重构查询的方式
在优化有问题的查询时，目标应该是找到一个更优的方法获取实际需要的结果，而不一定总是需要从mysql获取一模一样的结果集。
设计查询时，一个需要考虑的重要问题是，复杂查询或多个简单查询。传统实现中，总是强调在数据库中完成尽可能多的工作，原因在于减少网络通信、查询解析和优化等被认为是代价很高的事情。在其他条件都相同的情况下，尽量减少查询当然是好的。但有时候，将一个大查询分解为多个小查询也是必要的，
2.1切分查询
切分查询是指，将大查询切分成小查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
例如，delete删除旧数据，如果一次删除大量数据，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但很重要的查询
Delete from message where created < date_sub(now(), interval 3 month);
如果切分成多个小的执行语句，并设置适当的时间间隔，就可以大大降低对服务器的影响。
2.2分解关联查询
很多高性能的应用都会对关联查询进行分解
简单的说，就是每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
这样做的好处有一下几点
1、 执行单个查询可以减少锁的竞争
2、 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
3、 查询本身效率也可能有所提升
4、 可以减少冗余记录的查询。应用层做关联，意味着某些记录只需要查询一次，而在数据库中做关联，则可能需要反复地访问这些记录。这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。
使用场景
1、 应用能够方便地缓存单个查询结果
2、 数据分布于多个mysql服务器上
3、 能够使用in（）的方式代替关联查询。形如select xxx from x where id in （select 。。。）这样的子查询
4、 当查询中使用同一个数据表。自关联
分解关联查询举例
|
Select * from tag Join tag_post on tag_post.tag_id = tag.id Join post on tag_post_tag_id = post_id Where tag.tag = ‘myql’; |
可以分解为
|
Select * from tag where tag = ‘mysql’ Select * from tag_post where tag_id = 1234 Select * from post where post_id in (123,456,567,9098,8904) |
三、mysql如何执行关联查询
https://manzb.top/2020/02/29/mysql-join/，更多参考
小结
循环嵌套
Mysql关联执行的策略很简单：mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后再根据各个表匹配的行，返回查询中需要的各个列。Mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，mysql返回到上一层次关联表，看是否能够找到更多的匹配记录，一次类推迭代执行。
关联子查询优化
其实很多的关联子查询是非常糟糕的，最糟的是 where 后面跟 in的子查询，一般像这样的子查询建议使用left join来实现
在以上例子中，mysql每次执行外层筛选都会执行一次子查询，也就是说mysql会将查询改写成如下：
在优化关联查询的时候需要注意的几点是：
1、确保ON 或 USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建立索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他原因，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2、确保任何的GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才能有可能使用索引来优化这个过程。
对最大值，最小值的优化，
很多情况下，对一个字段求他的最大值，最小值，我们会用函数去做，但是这样的情况并不是最好的，比如有些场景，就是我们可以知道这个查询字段如果是索引的话，那么索引本身就是有排序的，那么我们只需要把他排序好，取limit 1 比用 函数会好很多。
对count的优化
最好的查询结果的行数的方式就是count(*),因为底层做了优化的。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以尽量使用 count(*)。
提前终止查询。
在发现已经满足查询需求时，MySQL总是能立刻终止查询。一个典型是，使用了LIMIT子句。除此之外，MySQL还有几种情况，例如发现一个不成立的条件，MySQL就会立刻返回空结果。如果发现某些特殊条件，则会提前终止查询。当存储引擎需要检索“不同取值”或判断存在性的时候。
四、优化局限性
union的限制
对于union查询，mysql先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成union查询。在mysql概念中，每个查询都是一次关联，所以读取临时表也是一次关联。
MySQL有时候无法将外层限制条件“下推”到内层，使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
如果希望UNION 的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再进行合并的话，就需要在UNION的各个子句中分别使用这些子句。
现在中间的临时表只会包含40条记录了。除了性能考虑之外，在这里还需要注意一点，从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的orderby和limit操作。
五、优化特定类型的查询的
对count的优化
最好的查询结果的行数的方式就是count(*),因为底层做了优化的。
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以尽量使用 count(*)。
对groupby的优化
如果没有通过orderby子句显式地指定排序，当查询groupby子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用orderby null，让mysql 不再进行文件排序。也可以在groupby子句中直接使用desc或者asc关键字，使分组的结果集按需要的方向排序。
优化后
优化limit分页
我们都知道在偏移量非常大的时候，例如limit1000，20这样的查询，这时需要查询1020条记录然后返回20条，前面1000条都将被抛弃。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么在页面中限制分页的数量，要么是优化大偏移量的性能。
方法1
尽可能地使用索引覆盖扫描，而不是查询所有列。
方法2
如果表非常大，则建议使用延迟关联，延迟关联首先通过覆盖索引找到需要的id，再通过id从主键索引找需要的结果。
优化union查询
使用union时，除非确实需要服务器消除重复的行，否则就一定要使用union all。如果没有all关键字，mysql会给临时表加上distinct关键字，这会导致对整个临时表的数据做唯一性检查，代价非常高。即使有all关键字，mysql仍然会使用临时表存储结果。事实上，mysql总是将结果放入临时表，然后再读出，再返回给客户端。
提前终止查询。
在发现已经满足查询需求时，MySQL总是能立刻终止查询。一个典型是，使用了LIMIT子句。除此之外，MySQL还有几种情况，例如发现一个不成立的条件，MySQL就会立刻返回空结果。如果发现某些特殊条件，则会提前终止查询。当存储引擎需要检索“不同取值”或判断存在性的时候。
