c2c网站代表有哪些,怎样制作个人网站,无锡招聘网最新招聘,神马搜索推广优质博文#xff1a;IT-BLOG-CN 如果把查询看作是一个任务#xff0c;那么它由一些列子任务组成#xff0c;每个子任务都会消耗一定的时间。如果要优化查询#xff0c;实际上要优化其子任务#xff0c;要么消除其中一些子任务#xff0c;要么减少子任务的执行次数。通常… 优质博文IT-BLOG-CN 如果把查询看作是一个任务那么它由一些列子任务组成每个子任务都会消耗一定的时间。如果要优化查询实际上要优化其子任务要么消除其中一些子任务要么减少子任务的执行次数。通常来说查询的生命周期大致可以按照顺序来看从客户端到服务器然后在服务器上进行解析生成执行计划执行并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段其中包括大量为了检索数据到存储引擎的调用以及调用后的数据处理包括排序、分组等。上述操作会在网络、CPU计算、生成统计信息和执行计划、锁等待互斥等待等操作上花费时间尤其是向底层存储引擎检索数据的调用操作。根据存储引擎的不同可能还会产生大量的上下文切换以及系统调用。
一、是否请求了不需要的数据
查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效查询可以通过如下两个步骤来分析总是有效 【1】确定应用程序是否在检索大量超过需要的数据。意味着访问了太多的行或者太多的列。 【2】确定 MySQL 服务器是否在分析大量超过需要的数据行。
有些查询会请求超过实际需要的数据然后这些多余的数据会被应用程序丢弃。这会给 MySQL 服务器带来额外的负担并增加网络开销【应用服务器和数据库不再同一台服务器上】另外也会消耗应用服务器的 CPU和内存资源。通常企业不允许使用 SELECT * 语句进行查询。
二、是否扫描了额外的记录
在确定查询只返回需要的数据以后接下来应该查看查询是否扫描了过多的数据。对于 MySQL最简单的衡量查询开销的三个指标是响应时间、扫描的行数、返回的行数这三个指标都会记录到慢日志【SHOW VARIABLES LIKE “%slow%”;】中。 【1】响应时间 服务时间和排队时间之和服务时间是指数据库处理这个查询真正花费的时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间等待I/O操作或锁等等。遗憾的是无法将响应时间细分到上面这些部分。 【2】扫描的行数和返回的行数 分析查询时查看该查询扫描的行数是非常有帮助的。但并不是所有的行的访问代价都是相同的。较短的行访问速度快内存中的行也比磁盘中的行的访问速度要快很多。理想情况下扫描的行数和返回的行数应该是相同的。但这种情况并不多。例如在做一个关联查询时服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小以便在1:1和10:1之间不过有时候这个值也可能非常非常大。 【3】扫描的行数和访问类型 在评估查询开销的时候需要考虑一下从表中找到某一行数据的成本。MySQL 有好几种查询方式可以查找并返回一行结果。有些访问方式可能需要扫描多行才能返回一行结果也有些访问方式可能无需扫描就能返回结果。在EXPLAN 语句中的 type 列反映了访问类型。从全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等。速度从慢到快扫描的行数也是从多到少。如果查询没有办法找到合适的访问类型那么解决的最好办法就是添加一个合适的索引。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。 【4】如果发现查询需要扫描大量的数据但只返回少数行 通常可以使用如下技巧去优化它①、使用索引覆盖扫描把所有需要的列都放到索引中这样存储引擎无需回表获取对应行就可以返回结果了。②、改变表结构。例如使用单独的汇总表。③、重写这个复杂的查询让 MySQL 优化器能够以更优化的方式执行这个查询。
三、一个复杂查询 OR 多个简单查询 有时候可以将查询转换一种写法让其返回一样的结果但是性能更好。但也可以通过修改应用代码用另一种方式完成查询达到最后的目的。 设计查询的时候需要考虑一个重要问题是否需要将一个复杂的查询分成多个简单的查询。在传统的实现中总是强调需要数据库层完成尽可能多的工作这样做逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。对于MySQL 并不适用MySQL 从设计上让连接和断开连接都是轻量级 在返回一个小的查询结果很高效。现在的网络速度比以前也快很多无论是宽带还是延迟。即使一个通用的服务器上也能够运行每秒超过10万的查询。
四、切分查询
有时候对于一个大查询我们需要 “分而治之” 将大查询切分成小查询每个查询功能完全一样只是完成一小部分每次只返回一小部分查询结果。删除旧的数据就是一个很好的例子。定期地清除大量数据时如果用一个大的语句一次性完成的话则可能需要一次性锁住很多数据、占满整个事务日志耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE 切分成多个较小的查询可以尽可能小地影响 MySQL 性能同时还可以减少 MySQL 的复制延迟。一秒删除一万行数据一般来说是一个比较高效而且对服务器影响也比较小的做法。如果每次删除数据后都暂停一会儿再做下一次删除这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中就可以大大降低对服务器的影响还可以大大降低删除时锁的持有时间。
五、分解关联查询
很多高性能的应用都会对关联查询进行分解。可以对每一个表进行一次单表查询然后将结果在应用程序中进行关联。如下
SELECT * FROM teacher t
JOIN student s ON t.id s.t_id
JOIN class c ON t.id c.t_id
WHERE t.nameLi;
--拆分后
SELECT * FROM teacher t WHERE t.nameLi;
SELECT * FROM student s WHERE s.id 12;
SELECT * FROM class c WHERE c.id IN (13,45,65);用分解关联查询的方式重构查询有如下的优势 【1】让缓存的效果更高许多应用程序可以方便地缓存单表查询对应的结果对象。例如上面的 teacher 已经被缓存了那么应用就跳过了第一个查询再例如应用程序中已经缓存了 ID 为 12、45 的内容那么第三个查询的 IN() 中就可以少几个 ID。另外对于MySQL 的查询缓存来说如果关联中某个表发生了变化那么就无法使用查询缓存了而拆分后如果某个表很少改变那么基于该表的查询就可以重复利用查询缓存结果了。 【2】将查询分解后执行单个查询就可以减少锁的竞争。 【3】在应用层做关联可以更容易对数据库进行拆分更容易做到高性能和可扩展。 【4】查询本身效率也可能有所提升。这个例子中使用 IN() 代替关联查询可以让 MySQL 按照ID 顺序进行查询这可能比随机的关联要更高效。 【5】可以减少冗余记录的查询。在应用层做关联查询意味着对于某条记录应用只需要查询一次而在数据库中做关联查询则可能需要重复地访问一部分数据。这样的重构还可能会减少网络和内存的消耗。 【6】这样做相当于在应用中实现了哈希关联而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联效率要高很多。
六、UNION 的限制
MySQL 无法将外层限制条件延续到内层这使得原本可以返回部分结果的条件无法应用到内部查询的优化上。如果希望 UNION 的各个子句根据 LIMIT 只取部分结果集或者希望能够先排好序再合并结果集的话就需要在 UNION 的各个子句中分别使用这些子句。例如想将两个子查询结果联合起来然后再取前20条记录那么MySQL 会将两个表都存放到同一个临时表中然后再取出前20行记录
--UNION 操作符选取不同的值。如果允许重复的值请使用 UNION ALL
(SELECT first_name,last_name FROM people_A ORDER BY last_name)
UNION ALL
(SELECT first_name,last_name FROM people_B ORDER BY last_name)
LIMIT 20;这条查询将会把 people_A 中的所有记录和 people_B 的所有记录放在一个临时表中然后再从临时表中取出前20条。可以通过在 UNION 的两个子查询中分别加上一个 LIMIT 20来减少临时表中的数据
(SELECT first_name,last_name FROM people_A ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name,last_name FROM people_B ORDER BY last_name LIMIT 20)
LIMIT 20;现在中间的临时表只会包含40条记录除了性能考虑之外在这里还需要注意一点从临时表中取出数据的顺序并不是一定的所以如果想获得正确的顺序还需要加上一个全局的 ORDER BY 和 LIMIT 操作。
MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询。除非确定需要服务器消除重复的行否则就一定要使用 UNION ALL如果没有 ALL 关键字MySQL 会给临时表加上 DISTINCT 选项这会导致给整个临时表做唯一性检查。代价非常高。就是有 ALL 关键字MySQL 仍然会使用临时表存储结果。事实上MySQL 总是把结果放入临时表然后再读出来再返回给客户端。
七、优化 COUNT() 查询
COUNT() 可以统计某个列值的数量也可以统计行数。在统计列值时要求列值是非空的不统计NULL。如果在COUNT() 的括号中制定了列或者表达式则统计的就是这个表达式有值的结果数。COUNT()的另一个作用是统计行数当MySQL确认括号内的表达式值不可能为空的时候实际上就是在统计行数。最简单的就是当我们使用 COUNT(*) 的时候这种情况它会忽略所有的列直接统计所有的行数。 MyISAM 的 COUNT() 函数总是非常快前提是没有任何 WHERE 条件。因为无需实际计算表的行数。MySQL 可以利用存储引擎的特性直接获取这个值。如果 MySQL 知道某个列 col 不可能为 NULL 值那么内部会将 COUNT(col) 转换成COUNT(*)。 【简单优化】 有时候可以使用 MyISAM 在 COUNT(*) 全表非常快的这个特性来加速一些特定条件的 COUNT() 查询。比如
SELECT COUNT(*) FROM city WHERE ID5;通过 SHOW STATUS 的结果可以看到该查询需要扫描 5000行数据。如果将条件反转先查找ID小于等于5的城市然后用总城市减就能获得同样的结果却可以将扫描数减少到5行以内。
--ID 是索引所以会去前5行数据
SELECT (SELECT COUNT(*) FROM city)-COUNT(*) FROM city WHERE ID5;通常来说COUNT() 都需要扫描大量的行才能获得精准的结果因为是很难优化的。在MySQL 层面还能做的就只有索引覆盖扫描了。如果还不够就需要考虑修改应用的架构可以增加汇总表或者增加类似 memcached 缓存系统。
八、优化 LIMIT 分页
在进行分页操作的时候通常会使用 LIMIT 加上偏移量的办法实现同时加上合适的 ORDER BY 子句。如果有对应的索引效率会不错否则MySQL 要做大量的文件排序操作。有一个问题当偏移量非常大的时候例如 LIMIT 10 000,20 这样的查询这是需要查询10 020条记录然后只返回 20条前面的10 000条记录都将被抛弃这样代价太高。优化此类分页查询的最简单办法就是尽可能地使用覆盖索引扫描而不是查询所有列。对于偏移量大的时候这样做的效率会提升非常大。例如
SELECT id,description FROM tab ORDER BY title LIMIT 10000,20;
--使用覆盖索引优化后的语句如下
SELECT f.id,f.description FROM tab f
INNER JOIN (SELECT id FROM tab ORDER BY title LIMIT 10000,20) t
USING(id);这里的 “延迟关联” 将大大提升查询效率它让 MySQL 扫描尽量少的页面获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的 LIMIT 子句。
九、排序优化
排序是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。如果数据量小于 “排序缓冲区” 则在内存中排序如果数据量大于 “排序缓冲区” 则使用磁盘进行排序 。MySQL 将这一过程统称为 “文件排序filesort”前提没有使用索引。 MySQL 使用内存进行 “快速排序” 操作。如果内存不够排序那么 MySQL 会先将数据分块对每个队列的块使用 “快速排序” 进行排序并将各个块的排序结果存放在磁盘上然后将各个排好序的块进行合并merger最后返回排序结果。
【1】两次传输排序旧版本使用 读取行指针和需要排序的字段对其进行排序然后再根据排序结果读取所需要的数据行。需要进行两次传输既需要从数据表中读取两次数据。第二次读取数据的时候因为是读取排序列进行排序后的所有记录这会产生大量的随机 I/O所以两次数据传输的成本非常高。 【2】单次传输排序新版本使用 先读取排序所需要的列然后再根据给定的列进行排序最后直接返回排序结果。因为不需要从数据表中读取两次数据对于I/O 密集型的应用这样做的效率高了很多。另外相比两次传输排序这个算法只需要一次顺序 I/O 读取所有的数据而无需任何随机 I/O。缺点是如果需要返回的数据非常多非常大会额外占用大量空间而这些列对排序本身并没有任何作用。很难说那个算法效率高当查询需要所有列的总长度不操作参数 max_length_for_sort_data 时MySQL 使用单次传输排序可以通过调整该参数来影响 MySQL 排序算法的选择。
MySQL 在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。在关联查询需要排序时会分为两种情况来处理这样的文件排序。如果 ORDER BY 子句中的所有列都来自关联的一个表那么 MySQL 在关联处理第一个表的时候就进行了文件排序。使用 EXPLAN 查看时看到 Extra 字段会有 “Using filesort” 。另一种情况是 MySQL 都会先将结果存放在一张临时表中然后在所有关联都结束后再进行文件排序。EXPLAN 结果是 “Using temporaryUsing filesort”如果包含 LIMIT 的话LIMIT 也会在排序之后应用。在 MySQL5.6 之后。当使用 LIMIT 子句时MySQL 不会对所有结果进行排序而是根据实际情况选择抛弃不满足条件的结果然后进行排序。
十、查询状态
在分析查询性能的时候对于一个 MySQL 连接来说可以通过查看它的状态来观察它正在做什么。最简单的方式是 SHOW FULL PROCESSLIST 命令该命令返回结果中的 Command 列表示当前的状态。在一个查询的生命周期中状态会变化多次。MySQL 官方手册中对这些状态值的含义有最权威的解释如下 【1】Sleep 线程正在等待客户端发送新的请求 【2】Query 线程正在执行查询或将结果发送给客户端 【3】Locked 在 MySQL 服务器层该线程正在等待表锁。InnoDB的行锁并不会体现在线程状态中 【4】Analyzing and statistics 线程正在收集存储引擎的统计信息并生成查询的执行计划。 【5】Copying to tmp table [on disk] 线程正在执行查询将结果都复制到一个临时表这种状态一般要么再过 GROUP BY要么是文件排序操作或者是 UNION 操作。“on disk” 标记表示 MySQL 正在讲一个内存临时表放到磁盘上。 【6】Sorting result 线程正在对结果进行排序 【7】Sending data 表示多种情况线程可能在多种状态之间传送数据。
