博罗网站设计,网站跟app的区别,近期军事新闻,wordpress分页调用代码查询性能优化 1、为什么查询这么慢2、慢查询基础#xff1a;优化数据访问2.1 是否向数据库请求了不需要的数据2.2 MySQL是否存在扫描额外的记录 3、重构查询方式3.1 一个复杂查询还是多个简单查询3.2 切分查询3.3 分解联接查询 4、查询执行的基础4.1 MySQL的客户端/服务器通信… 查询性能优化 1、为什么查询这么慢2、慢查询基础优化数据访问2.1 是否向数据库请求了不需要的数据2.2 MySQL是否存在扫描额外的记录 3、重构查询方式3.1 一个复杂查询还是多个简单查询3.2 切分查询3.3 分解联接查询 4、查询执行的基础4.1 MySQL的客户端/服务器通信协议4.2 查询状态4.3 查询优化处理 5、MySQL查询优化器的局限性5.1 UNION的限制5.2 并行执行5.3 同一个表中查询和更新 6、优化特定类型的查询6.1 优化count()查询6.2 使用WITH ROLLUP优化GROUP BY6.3 优化limit和offset子句6.4 优化SQL CALC FOUND ROWS6.5 优化UNION查询 如有侵权请联系 如有错误也欢迎批评指正 本篇文章大部分是来自学习《高性能MySQL》的笔记 1、为什么查询这么慢
查询优化、索引优化、库表结构优化需要齐头并进一个不落
快速查询真正重要的是响应时间。如果把查询当做一个任务那么这个任务会由一系列的子任务组成每个子任务都需要消耗一定的时间。优化查询其实就是优化子任务要么消除一些子任务要么减少某些子任务的执行次数要么让子任务执行的更快。
在完成这些任务的时候查询需要在不同的地方花费时间包括网络、CPU计算、生成统 计信息和执行计划、锁等待(互斥等待)等操作尤其是向底层存储引擎检索数据的调用 操作这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。
在每一个消耗大量时间的查询案例中我们都能看到一些不必要的操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。
2、慢查询基础优化数据访问
查询性能很差的最常见原因访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的 方式进行优化。对于低效的查询我们发现通过下面两个步骤来分析总是很有效:
确认应用程序是否在检索大量且不必要的数据。访问了太多的行或者列确认MySQL服务器层是否在分析大量不需要的数据行
2.1 是否向数据库请求了不需要的数据
有些查询会请求超过实际需要的数据然后这些多余的数据会被应用程序丢弃。这会给 MySQL服务器带来额外的负担并增加网络开销另外这也会消耗应用服务器的CPU和内存资源。
查询了不需要的数据多表联查的时候返回了所有的列总是取所有的列select *重复相同的查询
2.2 MySQL是否存在扫描额外的记录
在确定查询只返回需要的数据以后接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL最简单的衡量查询开销的三个指标如下:响应时间、扫描的行数、返回的行数。
3、重构查询方式
优化查询的目标找到获得实际需要的结果的替代方法。不一定和优化前的数据完全一致可以是相同结果的等价形式。
3.1 一个复杂查询还是多个简单查询
设计查询的时候一个需要考虑的重要问题是否需要将一个复杂的查询转换为多个简单的查询。传统的实现认为网络通信、查询解析和优化都是代价比较高的操作所以让数据库做尽可能多的工作。 但是这对MySQL不适用原因
MySQL从设计上就将连接和断开连接都变的很轻量在返回一个小的查询结果方面很高效。并且现代网络也快很多。在某些版本的MySQL中即使在一台通用服务器上也能够运行每秒超 过10万次的简单查询。在MySQL内部每秒能够扫描内存中上百万行的数据相比之下MySQL响应数据给客户端就慢得多了。
所以将一个大查询分解为多个小查询是很有必要的。但也不是一个查询就能完成的非要转换为多个查询。
3.2 切分查询
对于一个大查询分而治之将一个大查询转换为多个小查询每个小查询的功能完全一样每次只返回一小部分结果。例如删除旧数据不要一次删除所有数据这样可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。需要注意如果每次删除数据后 都暂停一会儿再做下一次删除也可以将服务器上原本一次性的压力分散到一个很长的时 间段中可以大大降低对服务器的影响还可以大大减少删除时锁的持有时间。
3.3 分解联接查询
很多高性能的应用都会对联接查询进行分解。即对每个表进行单次查询然后在应用程序中进行联接。这样做的好处
让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。减少锁竞争在应用层做联接可以更容易对数据库进行拆分更容易做到高性能和可扩展。可以减少对冗余记录的访问。在应用层做联接查询意味着对于某条记录应用只需要查询一次而在数据库中做联接查询则可能需要重复地访问一部分数据。从这 点看这样的重构还可能会减少网络和内存的消耗。
4、查询执行的基础
4.1 MySQL的客户端/服务器通信协议
MySQL的客户端和服务器之间的通信协议是“半双工”的这意味着在任何时刻要么是由服务器向客户端发送数据要么是由客户端向服务器发送数据这两个动作不能同时发生。 这种协议让MySQL通信变得简单快速但是也从很多地方限制了MySQL。一个明显的限制是这意味着没法进行流量控制。一旦一端开始发送消息另一端要接收完整个消息才能响应它。 客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候参数max_allowed_packet就特别重要了。一旦客户端发送了请求它能做的事情就只是等待结果了。 一般的服务器响应给用户的数据通常很多由多个数据包组成。当服务器开始响应客户端请求时客户端必须完整地接收整个返回结果而不能简单地只取前面几条结果 然后让服务器停止发送数据。
4.2 查询状态
对于一个MySQL连接或者一个线程任何时刻都有一个状态该状态表示了MySQL当 前正在做什么。有很多种方式能查看当前的状态最简单的是使用SHOW FULL PROCESSLIST命令(该命令返回结果中的Command列其就表示当前的状态)
Command描述Sleep线程正在等待客户端发送新的请求Query线程正在执行查询或者正在将结果发送给客户端Locked在MySQL服务器层该线程正在等待表锁。在存储引擎级别实现的锁例如InnoDB 的行锁并不会体现在线程状态中。Analyzing and statistics线程正在检查存储引擎的统计信息并优化查询。Sorting result线程正在对结果集进行排序Copying to tmp table [on disk]线程正在执行查询并且将其结果集复制到一个临时表中这种状态一般要么是在做 GROUP BY操作要么是在进行文件排序操作或者是在进行UNION操作。如果这 个状态后面还有“on disk”标记那表示MySQL正在将一个内存临时表放到磁盘上。
在一个繁忙的服务器上通过查看线程状态可能会看到大量的不正常的状态例如statistics正占用大量的时间。这 通常表示某个地方有异常了。
4.3 查询优化处理
语法解析器和预处理 MySQL通过关键字将SQL语句进行解析并生成一棵对应的“解析树”。MySQL解 析器将使用MySQL语法规则验证和解析查询。例如它将验证是否使用了错误的关键字使用关键字的顺序是否正确或者它还会验证引号是否能前后正确匹配。 预处理器检查生成的解析树以查找解析器无法解析的其他语义例如这里将检查数据表和数据列是否存在还会解析名字和别名看看它们是否有歧义。下一步预处理器会验证权限。这通常很快除非服务器上有非常多的权限配置。
查询优化器 由优化器将其转化成查询执行计划。一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。 MySQL使用基于成本的优化器它将尝试预测一个查询使用某种执行计划时的成本并 选择其中成本最小的一个。最初成本的最小单位是随机读取一个4KB数据页的成本后来成本计算公式变得更加复杂并且引入了一些“因子”来估算某些操作的代价如执行一次WHERE条件比较的成本。优化器在评估成本的时候并不考虑任何层面的缓存带来的影响它假设读取任何数据都需要一次磁盘I/O。
MySQL的查询优化器是一个非常复杂的软件它使用了很多优化策略来生成一个最优的 执行计划。优化策略可以简单地分为两种一种是静态优化一种是动态优化。 静态优化可以直接对解析树进行分析并完成优化。例如优化器可以通过一些简单的代数变换将 WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效即使使用不同的参数重复执行 查询也不会发生变化可以认为这是一种“编译时优化”。 动态优化则和查询的上下文有关也可能和很多其他因素有关例如WHERE条件 中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估可以认 为这是“运行时优化”。
MySQL可以优化的类型但不限于
优化类型描述重新定义联接表的顺序数据表的联接并不总是按照在查询中指定的顺序进行。决定联接的顺序是优化器很重要的一个功能将外联接转化成内联接并不是所有的OUTER JOIN语句都必须以外联接的方式执行。诸多因素例如 WHERE条件、库表结构都可能会让外联接等价于一个内联接。MySQL能够识别这一 点并重写查询让其可以调整联接顺序。使用代数等价变换规则MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较还可以移除一些恒成立和一些恒不成立的判断。优化COUNT()、MIN()和MAX()索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如要找到某一列的 最小值只需要查询对应B-tree索引最左端的记录MySQL可以直接获取索引的第一 行记录。在优化器生成执行计划的时候就可以利用这一点在B-tree索引中优化器 会将这个表达式作为一个常数对待。预估并转化为常数表达式当MySQL检测到一个表达式可以转化为常数的时候就会一直把该表达式作为常数 进行优化处理。在优化阶段有时候一个查询也能够转化为一个常数。select film.film.film_id,film_actor.actor_id from film join film_actor using film_id where film.film_id1 第一步先从film表找到 需要的行。因为在film_id列上有主键索引所以MySQL优化器知道这只会返回一行数 据优化器在生成执行计划的时候就已经通过索引信息知道将返回多少行数据了。因为 查询优化器已经明确知道有多少个值(WHERE条件中的值)需要做索引查询所以这里 的表访问类型是const。在执行计划的第二步时MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完成后该值就会是明确的了。注意正如在第一步中一样使用film_actor字段对表的访问类型也是const。索引覆盖当索引中的列包含所有查询中需要使用的列的时候MySQL就可以使用索引返回需 要的数据而无须查询对应的数据行子查询优化MySQL在某些情况下可以将子查询转换为一种效率更高的形式从而减少多个查询 多次对数据进行访问。提前终止查询在发现已经满足查询需求的时候MySQL总是能够立刻终止查询。一个典型的例子 就是当使用了LIMIT子句的时候。除此之外MySQL在其他几类情况下也会提前终 止查询例如发现了一个不成立的条件这时MySQL可以立刻返回一个空结果。等值传播如果两列的值可通过等式联接那么MySQL能够把其中一列的WHERE条件传递到另一列上.select film.film.film_id,film_actor.actor_id from film join film_actor using film_id where film.film_id1列表IN()的比较在很多数据库服务器中IN()完全等同于多个OR条件的子句因为这两者是完全等 价的。在MySQL中这点是不成立的MySQL将IN()列表中的数据先进行排序然后 通过二分查找的方式来确定列表中的值是否满足条件这是一个O(logn)复杂度的 操作
MySQL如何执行联接查询 MySQL中使用的术语“联接”(对应英文为Join)的范围可能比你熟悉的更广泛。总的来 说MySQL认为每一个查询都是联接——不仅是匹配两张表中对应行的查询而是每一 个查询、每一个片段(包括子查询甚至基于单表的SELECT)都是联接。 UNION查询MySQL将一系列的单个查询结果放在一个临时表中然后重新从临时表中读取数据完成UNION。
MySQL的联接执行策略很简单:MySQL对任何联接都执行嵌套循环联接操作即 MySQL先在一个表中循环取出单条数据然后再嵌套循环到下一个表中寻找匹配的行 依次下去直到找到所有表中匹配的行为止。最后根据各个表匹配的行返回查询中需要 的各列。MySQL会尝试在最后一个联接表中找到所有匹配的行如果最后一个联接表无 法找到更多的行MySQL返回到上一层次的联接表看是否能够找到更多的匹配记录 依此类推迭代执行.
执行计划 MySQL不像其他其他关系型数据库生成字节码来执行查询而是通过生成一个查询指令树然后通过查询执行引擎执行完成这棵指令树并返回结果。 联接查询优化器 MySQL查询优化器最重要的一部分就是联接查询优化器它决定了多个表联接时的顺 序。通常多表联接的时候可以有多种不同的联接顺序来获得相同的执行结果。联接查询 优化器通过评估不同顺序时的成本来选择一个成本最低的联接顺序。
排序优化 无论如何排序都是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。 当不能使用索引生成排序结果的时候MySQL需要自己进行排序如果数据量小则在内存中进行如果数据量大则需要使用磁盘不过MySQL将这个过程统一称为文件排序 (filesort)。 如果需要排序的数据量小于“排序缓冲区”MySQL使用内存进行快速排序操作。如果内存不够排序那么MySQL会先将数据分块对每个独立的块使用“快速排序”进行排序 并将各个块的排序结果存放在磁盘上然后将各个排好序的块进行合并(merge)最后 返回排序结果。
排序类型分两种单次传输排序【一次全量数句读取包含了所有的列占用空间】和两次传输排序【第一次只读取主见和排序字段第二次对排序的记录进行随机IO性能差】。
在联接查询的时候如果需要排序MySQL会分两种情况来处理这样的文件排序。如果 ORDER BY子句中的所有列都来自联接的第一个表那么MySQL在联接处理第一个表的 时候就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段 会有“Using filesort”字样。除此之外的所有情况MySQL都会先将联接的结果存放到一个 临时表中然后在所有的联接都结束后再进行文件排序。在这种情况下在MySQL的 EXPLAIN结果的Extra字段可以看到“Using temporary;Using filesort”字样。如果查询中有 LIMIT的话LIMIT也会在文件排序之后应用所以即使需要返回较少的数据临时表和 需要排序的数据量仍然会非常大。
查询执行引擎 MySQL只是简单地根据执行计划给 出的指令逐步执行。在根据执行计划逐步执行的过程中有大量的操作需要通过调用存储 引擎实现的接口来完成这些接口也就是我们称为“handler API”的接口。查询中的每一个表都由一个handler的实例表示。如果一个表在查询中出现了三次服务器会创建三个 handler对象。前面我们有意忽略了这一点实际上MySQL在优化阶段就为每个表创建 了一个handler实例优化器根据这些实例的接口可以获取表的相关信息包括表的所有列名、索引统计信息等等。
将结果返回给客户端 MySQL将结果集返回客户端是一个增量且逐步返回的过程。例如我们回头看看前面的联接操作一旦服务器处理完最后一个联接表开始生成第一条结果时MySQL就可以 开始向客户端逐步返回结果集了。这样处理有两个好处:服务器端无须存储太多的结果 也就不会因为要返回太多结果而消耗太多内存。另外这样的处理也可让MySQL客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送再通过TCP协议进行传输在TCP传输的过程中可能对MySQL的封 包进行缓存然后批量传输。
5、MySQL查询优化器的局限性
5.1 UNION的限制
有时MySQL无法将限制条件从UNION的外层“下推”到内层这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。 如果希望UNION的各个子句能够根据LIMIT只取部分结果集或者希望能够先排好序再合并结果集的话就需要在UNION的各个子句中分别使用这些子句。例如想将两个子查询结果联合起来然后再取前20条记录那么MySQL会将两个表存放到同一个临时表 中然后再取出前20行记录:
(select actor_name from film_actor)
UNION ALL
(select user_name from user)
limit 20(select actor_name from film_actor limit 20)
UNION ALL
(select user_name from user limit 20)
limit 205.2 并行执行
MySQL无法利用多核特性来并行执行查询。很多其他的关系数据库能够提供这个特性 但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。
5.3 同一个表中查询和更新
MySQL不允许对一张表同时进行查询和更新。这其实并不是优化器的限制。
update film as out_film
set actor_count (select count(*) from film as inner_filmwhere out_film.film_name inner_film.film_name
);6、优化特定类型的查询
6.1 优化count()查询
COUNT()是一个特殊的函数有两种非常不同的作用:它可以统计某列的值的数量也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括 号中指定了列或者列的表达式则统计的就是这个表达式有值的结果数。COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为 空时实际上就是在统计行数COUNT(*)。
使用近似值 某些业务场景并不要求完全精确的统计值此时可以用近似值来代替。 EXPLAIN出来的优化器估算的行数就是一个不错的近似值执行EXPLAIN并不需要真正 地去执行查询所以成本很低。
优化联接查询
确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到联接的顺序。当表A和表B用列c联接的时候如果优化器的联接顺序是B、A那么就不需 要在B表的对应列上建索引。没有用到的索引只会带来额外的负担确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列这样MySQL 才有可能使用索引来优化这个过程。当升级MySQL的时候需要注意:联接语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通联接的地方可能会变成笛卡儿积不同类型的联接可能会 生成不同的结果甚至会产生语法错误。
6.2 使用WITH ROLLUP优化GROUP BY
with rollup是MySQL对返回的分组结果再做一次超级聚合使用group by进行分组之后with rollup会新增加一行对所有的分组数据再进行一次聚合。
6.3 优化limit和offset子句
在系统中需要进行分页操作的时候我们通常会使用LIMIT加上偏移量的办法实现同时 加上合适的ORDER BY子句。如果有对应的索引通常效率会不错否则MySQL需要 做大量的文件排序操作。 一个非常常见又令人头疼的问题是在偏移量非常大的时候例如可能是LIMIT 100020这样的查询这时MySQL需要查询10020条记录然后只返回最后20条前面10 000条记录都将被抛弃这样的代价非常高。如果所有的页面被访问的频率都相同那么 这样的查询平均需要访问半个表的数据。要优化这种查询要么是在页面中限制分页的数量要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列【延迟联接】。在偏移量很大的时候这样做的效率会有非常大的提升。
select film_id,film_name from film order by title limit 50,5;修改为
select film_id,film_name from film
join(select film_id from film order by title limit 50,5
) as temp
on film.film_id temp.film_id;它允许服务器在不访问行的情况下检查索引中尽可能少的数据然后一旦找到所需的行就将它们与整个表联接以从该行中检索其他列。 有时候也可以将LIMIT查询转换为已知位置的查询让MySQL通过范围扫描获得对应的结果。LIMIT和OFFSET的问题其实是OFFSET的问题它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置那么下次就可以直接从该书签 记录的位置开始扫描这样就可以避免使用OFFSET。
6.4 优化SQL CALC FOUND ROWS
分页的时候另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示 (hint)这样就可以获得去掉LIMIT以后满足条件的行数因此可以作为分页的总数。 看起来MySQL做了一些非常“高深”的优化像是通过某种方法预测了总行数。但实际 上MySQL只有在扫描了所有满足条件的行以后才会知道行数所以加上这个提示以 后不管是否需要MySQL都会扫描所有满足条件的行然后再抛弃掉不需要的行而 不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。
// 获取数据记录
mysql select sql_calc_found_rows * from user limit 2;
// 获取总数即select count(*) from user
mysql select found_rows();一个更好的设计是将具体的页数换成“下一页”按钮假设每页显示20条记录那么我们每 次查询时都是用LIMIT返回21条记录并只显示20条如果第21条存在那么就显示“下一 页”按钮否则就说明没有更多的数据也就无须显示“下一页”按钮了。
6.5 优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询因此很多优化策略在UNION查询中都没法很好地被使用。经常需要手工地将WHERE、LIMIT、ORDER BY等 子句“下推”到UNION的各个子查询中以便优化器可以充分利用这些条件进行优化(例 如直接将这些子句冗余地写一份到各个子查询)。
除非你确实需要服务器消除重复的行否则一定要使用UNION ALL这一点很重要。如 果没有ALL关键字MySQL会给临时表加上DISTINCT选项这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字MySQL仍然会使用临时表 存储结果。事实上MySQL总是将结果放入临时表然后再读出再返回给客户端虽 然很多时候这样做是没有必要的(例如MySQL可以直接把这些结果返回给客户端)。
