mysql查询性能优化

mysql执行查询的过程

如图：
1）客户端首先通过客户端/服务器通信协议与mysql服务器建立起连接
2）客户端发送一条sql语句
判断是否为查询语句，如果是查询语句，则先在查询缓存区哈希查找对应sql的数据，如果未找到，则需要调用解析器解析、预处理、再由优化器生成对应的查询执行计划
3）mysql根据优化器生成的执行计划，调用存储引擎的API执行查询
4）将查询到的结果缓存到查询缓存中，在从查询缓存中返回数据给优化器

基础模块详解：
1）mysql客户端/服务器通信协议
半双工通信协议，即在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。
优点：简单快速
缺点：一旦一端开始发送消息，另一端要接受完完整消息才能响应它
客户端不断地从服务器接收数据，客户端没办法让服务器停下来，可以通过加limit限制条件

2）查询缓存
mysql查询将获得全部结果集并缓存到内存中。
目的：mysql通常需要等待所有的数据都已经发送给了客户端才能释放这条查询所占用的资源，接受结果并将其放到缓存中可以较少服务器的压力，让查询早点结束释放资源

3）查询优化处理
查询优化处理包括解析SQL、预处理、优化SQL执行计划，产生语法错误将终止查询
SQL解析：通过关键字将SQL语句进行解析，生成一棵对应的“解析树”。解析过程中解析器会使用MySQL语法规则验证。
预处理器：进一步检查解析树是否合法，如数据表和数据列书否存在，解析名字和别名是否有歧义。验证权限
查询优化器：将SQL转化为执行计划，并找到最好的执行计划（一条查询可以有多种执行方式，最终都返回相同结果），基于成本选择最好（即成本最小）的执行计划
导致MySQL优化器选择错误的执行计划的几种情况：
①：统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本。InnoDB因为其MVCC架构，不能维护一个数据表的行数的精确统计信息
②：执行计划中的成本估算不等于实际执行的成本，因为优化器在评估成本的时候不考虑任何层面的缓存，都是假设需要读取一次磁盘I/O，所以如果有些数据被还缓存在内存中其访问成本更低，但是统计信息无法知道这一点
③：MySQL最优可能并不是查询时间最短的，因为其是基于成本模型选择执行计划的
④：不考虑并发查询
⑥：并不是任何时候都基于成本的优化，有时候基于一些固定的规则，比如如果有全文索引，则会使用全文索引
⑦：不考虑不受其控制的操作的成本，例如执行存储过程或用户自定义函数

MySQL通过优化策略生成一个最优的执行计划，优化策略可以分为静态优化和动态优化
静态优化：编译时优化，在第一次完成以后一直有效。静态优化可以直接对解析树进行分析，然后完成优化
动态优化：运行时优化，在每次查询时都需要重新评估

MySQL能够处理的优化：
①重新定义关联表的顺序。并不是按照在SQL中指定的顺序进行的，由优化器决定关联的顺序
②将外连接转化为内连接。并不是所有的OUT JOIN语句都是以外连接的方式进行的where条件、库表结构可能会让外连接变为内连接。
③使用等价变换规则。移除恒成立和横不成立的判断，并通过简单变换来简化并规范表达式（个人觉得这个步骤在写SQL语句的时候就应该尽量避免）
④优化COUNT()、MIN()、MAX()。
⑤预估并转化为常数表达式。如在索引上使用MIN()函数，在主键上或者唯一键查找语句会转化为常数表达式
⑥覆盖索引扫描
⑦子查询优化
⑧提前终止查询。如Limit，或发现不成立的条件
⑨等值传播
⑩列表IN()的比较。在mysql中IN()不等同于OR()条件，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表的值是否满足条件，对于IN()列表中有大量取值的时候，处理速度将更快。OR()的查询复杂度为O(n),IN()的查询复杂度为O(log n)

缓存优化器的性能：
如果使用查询缓存，在进行读写操作时会带来额外的资源消耗，消耗主要体现在以下几个方面
1）查询的时候会检查是否命中缓存，这个消耗相对较小
2）如果没有命中查询缓存，MYSQL会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存
3）如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。
对于InnoDB而言，事物的一些特性还会限制查询缓存的使用。当在事物A中修改了B表时，因为在事物提交之前，对B表的修改对其他的事物而言是不可见的。为了保证缓存结果的正确性，InnoDB采取的措施让所有涉及到该B表的查询在事物A提交之前是不可缓存的。如果A事物长时间运行，会严重影响查询缓存的命中率

查询缓存的空间不要设置的太大。
因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况。（那可以将查询缓存空间设置为0，在应用层交给redis进行处理？或者在应用程序中并不会出现这些性能不佳的问题？）

查询优化的实质
优化查询就是遵循一些原则让优化器能够按照预想的合理的方式运行

为什么会出现慢查询
1.向数据库请求了不需要的数据（请求超过实际需要的数据，然后这些多余的数据被应用程序丢弃）
常见案列：
1)使用select *。取出全部列，会让优化器无法使用覆盖索引进行优化，消耗更多的内存资源、cpu、I/O。但是对于缓存，查询出所有的列是一个好的操作。并且也有人认为，这种有点浪费数据库资源的方式可以简化开发，因为能够提高代码片段的复用性。
2）重复查询相同的数据，即反复调用相同的sql语句。对于同步调用相同的数据可以使用缓存。

衡量查询开销的指标
1）响应的时间 = 服务时间+排队时间
服务时间是指数据库处理这个查询正真执行的时间，排队时间是指服务器因为等待某些资源而没有正真执行的时间，如等待锁释放或等待I/O操作
2）扫描的行数
一般是可以用来衡量查询的效率，扫描的行数越多，其查询效率就越低。因为如果扫描的是很短的行或者是内存中的数据，即使扫描很多的行，其查询效率相对来说还是挺高的
3）返回的行数
这三个指标将被记录在慢查询日志中

mysql如何应用where
where是用来过滤不匹配的条件，删选掉不需要的数据，mysql会在不同的层次使用where条件进行过滤。性能由好到坏
1）在索引中使用where过滤不匹配的几率。在存储引擎中完成
2）使用覆盖扫描（extra:using index）来返回记录，直接从索引中过滤返回满足条件的数据。在mysql服务层中完成。无需回表
3）在数据表中返回数据（extr：using where）在mysql服务层中完成。mysql需要先从数据表中读出记录然后过滤。

优化需要扫描大量行但只返回少数行的sql语句
1）建立覆盖索引
2）改变库表结构，如使用单独的汇总表
3）重写查询语句

如何重构查询
1）切分查询，将一个大查询分为多个小查询，每个查询功能完全一样，只完成一部分，每次只完成一部分查询结果
例子：一次性删除多条数据
如果一次性删除很多数据，则可能一次性需要锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小但很重要的查询。需要将一份delete语句切分成多个较小的查询，尽可能小的影响mysql性能，比如如下sql语句：

delete from message where create < date_sub(now(),interval 3 month);

2）分解关联查询
将关联查询进行分解，转化为对每一个表进行一次单表查询，然后将结果在应用程序中进行关联
分解关联查询的优势：
1）让缓存的效率更高。许多应用程序可以方便的缓存单表查询对应的结果对象
2）相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联
3）执行单个查询可以减少锁的竞争
4）在应用层做关联，更容易对数据库进行拆分，更容易做到高性能和可扩展性
5）查询本身效率将提升
6）减少冗余记录的查询
应用场景：
1）应用能够方便的缓存单个查询的结果
2）当可以将数据分布到不同的mysql服务上时
3）能够使用in()方式代替关联查询时