MySQL查询优化

一、MySQL基本架构
1、基本架构组成
Server层：包括连接器、查询缓存、分析器、优化器、执行器等。涵盖了mysql大多数核心服务功能，以及所有内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎：负责数据的存储和提取，插件式架构模式。

2、连接器
连接器负责跟客户建立连接、获取权限、维持和管理连接。用户连接里的权限，都依赖于登陆时用户名、密码通过后，在权限表里查出的用户权限，也就意味着，即使管理员账号对用户权限做了修改，也不会影响已经存在连接的权限。

客户如果长时间没有动静，连接器就会自动将它断开，这个时间由参数wait_timeout控制，默认8小时。当用户全部使用长连接后，你可能会发现，由些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开时才释放。

解决方案：1.定期断开长连接。或者程序里判断执行一个占用内存的大查询后，断开连接，之后查询再重连。2.MySQL5.7以上版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和重做权限认证。

3、查询缓存
连接建立后，执行逻辑就会来到第二步：查询缓存。之前执行过的语句及其结果会以key-value的方式缓存在内存中，key是查询语句，value是查询结果。

查询缓存的失效非常频繁，只要对一个表的更新，这个表上所有的查询缓存都会被清空，所以静态表（比如系统配置表）适合查询缓存（二级缓存？），对于更新压力大的数据库来说，缓存命中率会非常低。

可以将参数query_cache_type设置成demand，这样对默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以使用SQL_CACHE显示指定。Select SQL_CACHE * from …

需要注意的是Mysql8.0版本直接将查询缓存整块功能删除掉了。

4、分析器
分析器会先做词法分析，MySQL需要识别出你输入的SQL字符串代表什么。Selcet识别出来是查询语句，字符串T识别成表名T…，判断表是否存在，字段是否存在。然后做语法分析，根据语法规则，判断你输入的语法是否满足MySQL语法，相当于java的编译器。

5、优化器
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在语句有多表关联的时候，决定各表的连接顺序。也就是MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就可以进入执行器阶段。

6、执行器
开始执行的时候，要判断一下你对这个表T有没有执行查询的权限。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

二、查询性能优化
1、为什么查询速度会慢
通常来说，查询的生命周期大致可以分为以下顺序：从客户端，到服务器，然后在服务器上进行解析，优化后生成执行计划，执行，并返回结果给客户端。其中执行可以认为是整个生命周期最重要的阶段，这其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序和分组。

在完成这些任务的时候，查询需要在不同的地方花费时间,包括网络、CPU计算，生成统计信息和执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作。优化和查询的目的就是减少和消除这些操作所花费的时间。

2、慢查询的基础：优化数据访问
查询性能低下的最基本原因是访问的数据太多，大部分性能低下的查询可以通过减少访问的数据量的方式进行优化：

确认程序是否在检索大量超过需要的数据，这通常意味着访问太多行，但有时也可能是访问太多列。
确认MySQL服务器层是否在分析大量超过需求的数据。
2.1 是否向数据库请求了不需要的数据

（1）查询不需要的记录

例如查询select查询大量的结果，然后获取前面N条结果后关闭结果集。分页查询使用逻辑分页也是一样，查询所有数据，只返给页面所需N条记录。最简单有效的方法就是在这样的查询中使用Limit。

（2）多表关联时返回全部列

“select * from…”总是取出全部列，会让优化器无法完成索引覆盖扫描这列优化，还会为服务器带来额外的I\O，内存和CPU消耗。因此一些DBA严格禁止SELECT * 的写法。但是在许多实际开发中，查询返回超过需要的数据也不总是坏事，因为这种有点浪费数据库资源的方式可以简化开发，提高相同代码片段的复用性（一组数据结果可供多个接口使用）。获取并缓存所有列的查询，相比多个独立的只获取部分列的查询可能更有好处。

（3）重复查询相同的数据。

在程序中很容易出现这样的逻辑错误——不断执行相同的查询，并返回相同数据。例如，博客中用户评论功能中，需要查询用户头像，那么用户多次评论的时候，可能会反复查询头像数据。比较好的方案是，在初次查询的时候就将头像数据缓存起来。

2.2 MySQL是否在扫描额外记录

在确定查询只返回需要的数据后，接下来应该看看为了需要的结果是是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销三个指标如下：

响应时间
扫描行数
返回行数
这三个指标都会记录在MySQL慢日志中。

（1）响应时间

响应时间是两部分之和：服务时间和排队时间（一般常见的是等待I/0操作、行锁等等）。响应时间可能是单个查询的问题，也可能是服务器问题等等。

（2）扫描的行数和访问类型

MySQL有好几种访问方式可以查找并返回一行结果。有些方式可能需要扫描很多行才会返回一行结果，也有些访问方式可能无须扫描就返回结果。

在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描，索引扫描，范围扫描、唯一索引查询、常数引用等。
一般MySQL可以用如下三种方式应用where条件，从好到坏依次是：

在索引中使用where条件来过滤不匹配的记录，这是在存储引擎完成的。
使用索引覆盖扫描（在extra列中出现了Using Index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成，但无需再回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录（在extra列中出现了Using Where）,这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。
所以好的索引可以让查询使用适合的访问类型，尽可能地只扫描需要的数据行。如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试以下的方式去优化它：

使用索引覆盖扫描，把所有需要用的列都放在索引中（比如多字段的聚合索引），这样存储引擎无须回表获取对应的行就能返回结果。
改变库表结构，例如使用单独的汇总表。
重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询。
3、重构查询方式
3.1 一个复杂的查询还是多个简单的查询

在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的原因在于以前总是认为，网络通信、查询解析和优化是一件代价很高的事情。

但是这对MySQL并不适用。MySQL连接和断开都很轻量级，在返回一个小的查询结果集方面很高效。现在的网络速度比以前快很多，无论是带宽还是延迟。

在一个通用的服务器上，MySQL能运行每秒超过10万条查询，即使1000兆网卡，也能轻松满足每秒超过2000次查询。MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多。在其他条件相同的情况下，使用尽可能少的查询当然更好，但有时将一个大查询分解成多个小查询也是有必要的。

3.2 切分查询

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧数据就是一个很好的例子，如果一个大的语句一次性完成的话，则可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻碍很多小的但重要的查询。将一个大的delete语句切分成多个较小的查询语句可以尽可能小的影响MySQL性能，同时还可以减少复制的延迟，
3.3 分解关联查询

可以对每个表进行一次单表查询，然后将结果在应用程序中关联。

好处：

让缓存效率更高。许多应用程序可以方便的缓存单表查询的结果集，另外，关联中的某个表发生了变化，那么就无法使用查询缓存了，拆分后，单表之间不受影响。
执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库拆分，更容易做到高性能和扩展性。
查询效率本身可能会有所提升。
可减少冗余记录的查询。在应用层做关联查询，意味着对某条记录应用只需要查询一次，而在数据库中做关联查询可能需要重复地访问一部分数据。
更进一步，这样做相当于在应用层实现了哈希关联，而不是MySQL的嵌套循环关联。