mysql 性能查询优化

Mysql  /  houtizong 发布于 3年前   141

1 时间到底花在哪了？  mysql在执行查询的时候需要执行一系列的子任务，这些子任务包含了整个查询周期最重要的阶段，这其中包含了大量为了  检索数据列到存储引擎的调用以及调用后的数据处理，包括排序、分组等。在完成这些任务的时候，查询需要在不同的地方  花费时间，包括网络、cpu计算、生成统计信息和执行计划、锁等待等。尤其是向底层存储引擎检索数据的调用操作。这些调用需要在内存操作、  cpu操作和内存不足时导致的IO操作上消耗时间，很可能还会因为存储引擎的不同，产生大量的上下文切换以及系统调用。    在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外的重复了很多次，  某些操作执行的太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。    查询新能低下的最基本原因是访问的数据太多。对于低效的查询，我们可以通过下面两个步骤来分析总是很有效。  1 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但是有时候也可能是访问了太多的咧。  2 确认mysql服务器是否在分析大量超过需要的数据行。    最常见的错误是，先使用select语句查询大量的结果，然后获取前面的n行后关闭结果集，他们认为mysql  会执行查询，并只返回给他们10条记录然后停止查询。实际情况是mysql会查询出全部的结果集，客户端也会接受全部的结果集，  然后抛弃其中的大部分数据。这种思想给mysql服务器带来了额外的负担，并增加网络开销，另外也会消耗应用服务器的cpu和内存资源。  最简单有效的解决办法是在这样的查询后面加上limit.  2 重构查询的方式  1 一个复杂查询还是多个简单查询    将大查询分为更小的查询  2 切分查询     一次性删除100万数据，将会锁住很多数据、沾满整个事务日志、耗尽系统资源、阻塞很多小但是很重要的查询。   可以将这部分查询分割成很多小的操作，并且不在   3 分解关联查询    很多join的联合查询当然没有分部执行的小查询速度更快。这里有太多的原因，从客户端程序到mysql服务器。3 查询执行的基础  当希望mysql能够以更高的性能运行查询时，最好的办法就是弄清楚mysql是如何优化和执行查询的。一旦理解了这一点，很多查询  优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。  4 查询缓存  在解析一个查询时，如果查询缓存是打开的，mysql会优先检查是否命中查询缓存中的数据。  如果命中缓存，查询不会被解析，不用生成执行计划，不会被执行。  5 查询优化处理  查询的生命周期的下一步是将一个sql转换成一个执行计划，mysql再按照这个执行计划和存储引擎交互。这个阶段包括解析sql/  预处理、优化sql执行计划。    mysql 使用基于成本的优化器，她将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。  例子：select sql_no_cache count(*) from table;  6 mysql 能够处理的优化类型  1 重新定义关联表的顺序    数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。  2 将外连接转化为内连接    并不是所有的outer join 语句都必须以外连接的方式执行。很多因素例如where条件、库表结构都可能让外连接等价于一个内连接。mysql能够识别并重写查询。  3 使用等价变换规则    mysql可以使用一些等价变换来简化并规范表达式。他可以合并和减少一些比较，还可以移除一些恒成立和恒不成立的判断。  4 优化count() min() max()    索引和列是否为空通常可以帮助mysql优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一条记录。在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中，优化器将会把这个表达式作为一个常数对待。类似的，如果要查找一个最大值，也只需读取B-Tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在explain中就可以看到"select tables optimized away". 从字面意思可以看出，他表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。  5 预估并转化为常数表达式    MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。在优化阶段，有时候甚至能把一个查询也能够转化为一个常数。一个例子是在索引列上执行min函数。甚至主键或者唯一键查找语句也可以转换为常数。如果where子句中使用了该类索引的常数条件，MySQL可以在查询开始阶段就先查找到这些值，这样优化器就能够知道并转换为常数表达式。  6 索引覆盖扫描    当索引当中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无需查询对应的数据行。  7 子查询优化  8 提前终止查询    典型应用是limit,其他事条件不满足或者错误。  9 等值传播    例如select film.film_id from film    inner join film_acter using(film_id)where film.film_id >500;mysql 在这里使用film_id 进行等值关联，mysql知道where子句中film_id不仅适用于film 而且适用于film_acter;  10 in()列表比较    在mysql中，mysql会先排序in列表中的数据，然后使用二分查找来判断列表中的数据是否符合要求，复杂度在O(logn)所以in列表中有大量取值的时候，mysql处理的速度将会更快。mysql在在这一点不同于其他数据库，其他数据库是使用OR关联in列表当中的取值。优化器所做的工作远远不止上面这些，优化器还会做大量的其他工作，智能化和复杂性远远难以想象。千万不要自以为自己比优化器要更聪明，这一点一定要记住。7 mysql如何执行关联查询  mysql对于关联的理解不局限于表与表之间，每一个查询、每一个片段、甚至是单表的select操作都可能被mysql看做关联。  所以理解mysql如何执行关联查询至关重要。  mysql优化器会不是根据关联的顺序来判断最优执行计划而是根据需要读取的数据页来预估最优的执行计划。  关联查询都会生成查询计划树，MySQL不会生成平衡查询计划树，而是生成嵌套查询计划树，MySQL的计划树通常是  左侧深度优先的树，MySQL会遍历每一张表然后逐个做嵌套循环计算每一棵可能的计划树，采用嵌套和回溯操作  不过如果有超过n个表的关联，那么需要检查n的阶层种关联顺序。假如有10张表关联，那么共有3628800种  不同的关联顺序，所以必须控制表的关联规模。  我们可以查看last_query_cost来检查关联成本。  8 排序优化  无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行  排序。数据量较小的时候，mysql会在内存中完成排序，数据量较大需要使用磁盘，mysql将这个过程统一称为文件排序，即使完全使用内存排序  也是如此。如果避免不了排序，则尽可能的利用索引排序。  在内存中排序的时候，如果内存不够，mysql会先将数据分块，然后对每个独立的块进行快速排序，并将每个块的排序结果放在磁盘上，然后将  各个排好序的块进行merge,最后返回排序结果。    mysql 排序有两种算法：两次传输排序和单次传输排序    由于两次传输排序存在于旧版本，所以这里只讨论单次传输排序。    其操作思想是 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。相对于两次传输排序，这个算法只需要一次顺序IO读取所有的数据而无需任何的随机IO。mysql在进行文件排序时所需要的临时存储空间可能比想象的要大很多，原因在于mysql在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间足以容纳其中最长的字符，例如varchar 列，mysql会分配器完整长度。    mysql5.6版本做了很多改进，当只需要返回部分排序结果的时候，例如使用了limit子句，mysql不再对所有的结果进行排序，而是根据实际情况，选择抛弃不需要的数据，再对结果进行排序。