企业网站维护兼职,丹阳网站优化,企业文化建设网站建设,wordpress php转html一般来说一个好的程序#xff1a;查询优化#xff0c;索引优化#xff0c;库表结构要同时进行优化。今天我们来讲一下查询优化。
我们需要对MySQL的架构有基本认知#xff0c;所以这里贴一张图大家看看#xff1a; 图片来自于《小林coding》
为什么从查询会慢#xff1…一般来说一个好的程序查询优化索引优化库表结构要同时进行优化。今天我们来讲一下查询优化。
我们需要对MySQL的架构有基本认知所以这里贴一张图大家看看 图片来自于《小林coding》
为什么从查询会慢
查询的生命周期大概可以按照如下顺序来看从客户端到服务器然后在服务器上进行语法解析生成执行计划执行并给客户端返回结果。执行是整个生命周期中最重要的一个阶段其中包括了大量为了检索数据对存储引擎的调用以及调用后的数据处理包括排序分组等。在这些过程中有很多地方需要消耗大量时间例如网络CPU计算生成统计信息和执行计划锁等待互斥等待。因为查询可能会很慢。
慢查询基础优化数据访问
我们需要注意两个点
确认应用程序是否在检索大量且不必要的数据。这通常意味着访问了太多的行但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量不需要的数据行。
是否向数据库请求了不需要的数据
以下是一些典型案例
查询了不需要的数据没有加limit但是前端一个分页只能显示一部分而你把数据全部查了给前端了。多表联接时返回全部列但是实际上只需要返回你需要的列就可以了没有必要的全部。总是取出全部的列每次当你使用SELECT*的时候你都必须思考清楚你是不是真的需要所有列的数据。但是取出全部的列在有的时候也并不是一件坏事我们可能有缓存机制取出全部的列可能会对缓存机制有好处但是你必须知道这样做的代价是什么。查复查询相同的数据遇到常用的数据我们一定要用缓存存起来。
MySQL是否在扫描额外的记录
在MySQL中有3个最简单的衡量查询开销的指标
响应时间扫描的行数返回的行数
这三个指标会被记录到MySQL的慢日志中。
响应时间 响应时间 服务时间 排队时间 服务时间是真正执行查询使用了多少时间排队时间是服务器等待某些资源而没有真正执行查询的时间 – 可能是等待I/O操作完成。
扫描的行数和返回的行数
扫描的行数和返回的行数大多数情况是相同的但是在做一个联接查询的时候服务器必须要扫描多行才能生成结果集中的一行。
扫描的行数和访问类型
我们在评估查询开销的时候有一个语句是非常好的这个语句叫做EXPLAINEXPLAIN语句中的type列反应了访问类型。访问类型有很多种
全表扫描索引扫描范围扫描唯一索引查询常数引用等
这里列出的速度是从慢到快。
我们来把这些查询类型详细的讲解一下因为这个很重要
1. ALL
全表扫描(Full Table Scan), MySQL将遍历全表以找到匹配的行.
mysql explain select * from film where rating G;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 20.00 | Using where |
-----------------------------------------------------------------------------------------------------------film 表中 rating 字段没有索引.
2. index
全索引扫描(Full Index Scan), index 与 ALL 区别为 index 类型只遍历索引树. MYSQL 遍历整个索引来查找匹配的行.
mysql explain select title from film;
-----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | index | NULL | idx_title | 514 | NULL | 1000 | 100.00 | Using index |
-----------------------------------------------------------------------------------------------------------------虽然 where 条件中没有用到索引, 但是要取出的列 title 是索引包含的列, 所以只要全扫描 title 索引即可, 直接使用索引树查找数据.
3. range
索引范围扫描, 常见于 ‘’, ‘’, ‘’, ‘’, ‘between’ 等操作符.
mysql explain select * from film where film_id 100;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | range | PRIMARY | PRIMARY | 2 | NULL | 900 | 100.00 | Using where |
---------------------------------------------------------------------------------------------------------------因为 film_id 是索引, 所以只要查找索引的某个范围即可, 通过索引找到具体的数据.
4. ref
使用非唯一性索引或者唯一索引的前缀扫描, 返回匹配某个单独值的记录行.
mysql explain select * from payment where customer_id 10;
---------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | payment | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | const | 25 | 100.00 | NULL |
---------------------------------------------------------------------------------------------------------------------------customer_id 在 payment 表中是非唯一性索引
5. eq_ref
类似ref, 区别就在使用的索引是唯一索引. 在联表查询中使用 primary key 或者 unique key 作为关联条件.
mysql explain select * from film a left join film_text b on a.film_id b.film_id;
----------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.a.film_id | 1 | 100.00 | Using where |
----------------------------------------------------------------------------------------------------------------------------6. const/system
当 MySQL 对查询某部分进行优化, 并转换为一个常量时, 使用这些类型访问. 如将主键置于 where 列表中, MySQL 就能将该查询转换为一个常量, system 是 const 类型的特例, 当查询的表只有一行的情况下使用 system.
mysql explain select * from film where film_id 1;
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------7. NULL
MySQL 不用访问表或者索引就直接能到结果.
mysql explain select 1 from dual where 1;
--------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
--------------------------------------------------------------------------------------------------------------dual是一个虚拟的表, 可以直接忽略.
MySQL有三种方式使用WHERE
在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。使用索引覆盖直接从索引中过滤不需要的记录并返回命中结果这个过程不需要进行回表查询。从数据表中返回数据然后过滤不满足条件的记录。这是在MySQL服务器层完成的。
如果我们发现查询时需要扫描大量数据但只返回少量数行那么通常可以尝试下面的技巧去优化它。
使用索引覆盖扫描改变库表结构重写复杂查询
重构查询的方法
切分查询
有的时候对于一个大查询我们需要分而治之将大查询切分成小查询每个查询的功能完全一样只完成一小部分每次只返回一小部分查询结果。
例如删除旧数据。定期删除大量数据的时候如果用一个大的语句一次性完成的话则可能需要一次锁住很多数据占慢整个事务日志耗尽系统资源阻塞很多小但是重要的查询。例如这个
DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH)可以优化成这个样子
rows_affected 0;
do {rows_affected do_query(DELETE FROM messages WHERE created DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000);// 每次操作完成之后睡一会儿sleep(10000);
} while (row_affected 0);分解联接查询
对于联接查询可以对每一张表进行一次单表查询然后将结果在应用程序中进行联接。例如下面这个查询
SELECT * FROM tag JOIN tag_post ON tag_post.tag_id tag.id JOIN post ON tag_post.post_id post.id WHERE tag.tag mysql;可以分解成下面这些查询
SELECT * FROM tag WHERE mysql;
SELECT * FROM tag_post WHERE tag_id 1234;
SELECT * FROM post WHERE post.id in (123, 465, 78);为什么要这样做呢
让缓存效率更高。许多应用程序可以方便的缓存单表查询的结果对象。例如上面查询的tag mysql已经被缓存了那么应用就可以跳过第一个查询。在查询分解后执行单个查询可以减少锁的竞争。在应用层做联接可以更容易对数据库进行拆分更容易做到高性能和可拓展。可以减少冗余记录的访问。在应用层做联接查询意味着对于某条记录应用只需要查询一次而在数据库中做联接查询则可能需要重复的访问一部分数据。从这点看这样的重构可能会减少网络和内存的消耗。查询本身的效率可能会提升。在这个例子中使用IN()代替联接查询可以让MySQL按照ID顺序进行查询这可能比随机的联接要更高效。
查询执行的基础
MySQL的客户端/服务器通信协议
MySQL的客户端和服务器之间的通信协议是半双工的这意味着在同一时刻要么是服务器向客户端发送数据要么是客户端向服务器发送数据这两个动作不能同时发生。这样带来的问题就是无法进行流量控制。这里有一个小细节 多数连接MySQL的库函数都可以获得全部结果集并将结果缓存到内存里还可以逐行获取需要的数据。默认一般是获得全部结果集并将他们缓存到内存中。MySQL需要等所有的数据全部发送完毕才能释放这条查询所占用的资源。 查询状态
Sleep
线程正在等待客户端发送新的请求。
Query
线程正在执行查询或者正在将结果发送给客户端。
Locked
在MySQL服务器层该线程正在等待表锁。在存储引擎级别实现的锁例如InnoDB的行锁并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态但在其他没有行锁的引擎中也经常会出现。
Analyzing and statistics
线程正在收集存储引擎的统计信息并生成查询的执行计划。
Copying to tmp table [on disk]
线程正在执行查询并且将其结果集都复制到一个临时表中这种状态一般要么是在做GROUP BY操作要么是文件排序操作或者是UNION操作。如果这个状态后面还有“on disk”标记那表示MySQL正在将一个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进行排序。
Sending data
这表示多种情况线程可能在多个状态之间传送数据或者在生成结果集或者在向客户端返回数据。
语法解析器和预处理
MySQL通过关键字将SQL语句进行解析并生成一颗对应的解析树。这里会检测语法错误是否使用了错误的关键字关键字的顺序是否正确引号是否能够前后匹配等。
然后预处理器检查生成的解析树检查数据表和数据列是否存在还是解析名字和别名看看是否有歧义等
下一步预处理器会验证权限。
查询优化器
到这里为止解析树是已经完全合法的了优化器会将其转换成查询执行计划。一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到最好的执行计划。
MySQL使用基于成本的优化器。它将尝试预测一个查询使用某种执行计划时的成本并选择其中成本最小的一个。最初成本的最小单位是随机读取一个4KB数据页的成本后来引入了一些因子来进行估算。巨复杂虽然查询优化是可能有问题的但是读者不要为了这个可能产生的问题费尽心思因为您不一定可以比查询优化器做的更好了。
优化策略可以分成两种
静态优化动态优化
静态优化在第一次完成之后就一直有效即使使用不同的参数重复执行查询页不会发生变化可以认为是一种编译时优化。而动态优化跟上下文有关可以理解为运行时优化。
下面是MySQL能够处理的一些优化类型 重新定义联接表的顺序 将外联接转换成内联接 使用代数等价变换规则。例如 (5 5 AND b c) AND a 5
可以改写成
a 5就是类似这样的优化。 优化COUNT(), MIN(), MAX()。例如我想要找某一列的最小值只需要查询对应B-Tree索引最左端的记录MySQL可以直接获取第一行记录最大值就可以直接获取最后一行记录。 预估并转换为常数表达式。 覆盖索引优化 子查询优化 提取终止查询。当遇到LIMIT子句的时候MySQL会自动中止等 等值传播。如果两列的值可以通过等式联接那么MySQL能够把其中一列的WHERE条件 列表IN()的比较。列表IN()的比较。在很多数据库系统中IN()完全等同于多个OR条件的子句因为这两者是完全等价的。在MySQL中这点是不成立的MySQL将IN()列表中的数据先进行排序然后通过二分查找的方式来确定列表中的值是否满足条件这是一个Olog n复杂度的操作等价地转换成OR查询的复杂度为On对于IN()列表中有大量取值的时候MySQL的处理速度将会更快。
表和索引的统计信息
服务器层有查询优化器但是数据和索引的统计信息是在存储引擎层实现的。因此MySQL查询执行计划的时候需要向存储引擎层获取响应的统计信息。常见的统计信息有
每个表或者索引有多少个页面每个表的每个索引的基数是多少数据行和索引的长度是多少索引的分布信息等
MySQL如何执行联接查询
MySQL认为每一个查询都是联接 – 不仅仅是匹配两张表中对应的查询而是每一个查询每一个片段包括子查询甚至基于单表的SELECT都是联接。
对于UNION查询MySQL先将一系列的单个查询结果放到一个临时表中然后重新读出临时表中的数据来完成UNION查询。在MySQL概念中每个查询都是一次联接所以读取临时表的结果也是一次联接。
MySQL的联接执行策略是MySQL对任何联接都执行嵌套循环连接操作即MySQL先在一个表中循环读取出单条数据然后再嵌套循环到下一个表中寻找匹配的行。依次下去直到找到所有表中匹配的行为止。最后根据各个表匹配的行返回查询中需要的各列。
执行计划
我们用一张图来表示MySQL的联接查询执行计划 联接查询优化器
联接优化器会尝试在所有的联接顺序中选择一个成本最低的来生成执行计划树。如果可能优化器会遍历每一个表然后逐个做嵌套循环计算执行每一棵可能的计划树的成本最后返回一个最优的执行计划。但是这个代价可能有点大所以优化器选择使用贪婪搜索的方式查找最优的联接顺序。当需要联接的表超过optimizer_search_depth的限制的时候就会选择贪婪搜索模式了。
排序优化
排序是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候MySQL需要自己排序如果数据量小的话在内存中进行如果数据量大则需要使用磁盘。如果需要排序的数据量小于排序缓冲区MySQL使用内存进行快速排序操作。如果内存不够排序那么MySQL会先将数据分块对每个独立的块使用快速排序进行排序并将各个块的排序结果存放在磁盘上然后将各个排序好的块进行合并。
查询执行引擎
调用存储引擎实现的接口来完成这些接口就是我们说的handler API的接口。查询中的每一个表都由一个handler的实例表示。如果一个表在查询中出现了三次服务器就会创建三个handler对象handler里面存储了表的所有列名索引统计信息等等。
将结果返回给客户端
MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如对于关联操作一旦服务器处理完最后一个关联表开始生成第一条结果时MySQL就可以开始向客户端逐步返回结果集了。
MySQL查询优化器的局限性
等值传递
有些时候等值传递会带来一些意想不到的额外消耗。例如一列上的巨大IN()列表优化器知道它将等于其他表中的一些列这是由于WHERE, ON 或者 USING子句使列彼此相等。
优化器通过将列表复制到所有相关表中的相应列来“共享”列表。通过因为各个表新增了过滤条件所以优化器可以高效地从存储引擎过滤记录。但是如果这个列表非常大则会导致优化和执行都会变慢。
并行执行
MySQL无法利用多核特定来并行执行查询。
在同一个表中查询和更新
MySQL不允许对一张表同时进行查询和更新。
优化特定类型的查询
优化COUNT()查询
COUNT()有两个不同的作用
统计某个列值的数量即统计某列值不为NULL的个数。统计行数。
当使用COUNT(*)时统计的是行数它会忽略所有的列而直接统计所有的行数。而在括号中指定了一个列的话则统计的是这个列上值不为NULL的个数。 可以考虑使用索引覆盖扫描或增加汇总表对COUNT()进行优化。
优化LIMIT和OFFSET子句
处理分页会使用到LIMIT当翻页到非常靠后的页面的时候偏移量会非常大这时LIMIT的效率会非常差。例如对于***LIMIT 1000020***这样的查询MySql需要查询10020条记录将前面10000条记录抛弃只返回最后的20条。这样的代价非常高如果所有的页面被访问的频率都相同那么这样的查询平均需要访问半个表的数据。 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列。然后根据需要与原表做一次关联操作返回所需的列。对于偏移量很大的时候这样的效率会提升非常大。考虑下面的查询
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;如果这个表非常大那么这个查询最好改写成下面的这样子
SELECT film.film_id, film.description FROM sakila.film
INNER JOIN
(SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) AS lim
USING(film_id);注意优化中关联的子查询因为只查询film_id一个列数据量小使得一个内存页可以容纳更多的数据这让MySQL扫描尽可能少的页面。在获取到所需要的所有行之后再与原表进行关联以获得需要的全部列。 LIMIT的优化问题其实是OFFSET的问题它会导致MySql扫描大量不需要的行然后再抛弃掉。可以借助书签的思想记录上次取数据的位置那么下次就可以直接从该书签记录的位置开始扫描这样就避免了使用OFFSET。可以把主键当做书签使用例如下面的查询
SELECT * FROM sakila.rental ORDER BY rental_id DESC LIMIT 20;假设上面的查询返回的是主键为16049到16030的租借记录那么下一页查询就可以直接从16030这个点开始
SELECT * FROM sakila.rental WHERE rental_id 16030
ORDER BY rental_id DESC LIMIT 20;该技术的好处是无论翻页到多么后面其性能都会很好。 此外也可以用关联到一个冗余表的方式提高LIMIT的性能冗余表只包含主键列和需要做排序的数据列。
优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中以便优化器可以充分利用这些条件进行优化例如直接将这些子句冗余地写一份到各个子查询。
除非确实需要服务器消除重复的行否则就一定要使用UNION ALL这一点很重要。如果没有ALL关键字MySQL会给临时表加上DISTINCT选项这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字MySQL仍然会使用临时表存储结果。事实上MySQL总是将结果放入临时表然后再读出再返回给客户端。
子查询优化
MySql的子查询实现的非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。 应该尽可能用关联替换子查询可以提高查询效率。
排序优化
应该尽量让MySql使用索引进行排序。当不能使用索引生成排序结果的时候MySql需要自己进行排序。如果数据量小于“排序缓冲区”的大小则MySql使用内存进行“快速排序”操作。如果数据量太大超过“排序缓冲区”的大小那么MySql只能采用文件排序而文件排序的算法非常复杂会消耗很多资源。 无论如何排序都是一个成本很高的操作所以从性能角度考虑应尽可能避免排序。所以让MySql根据索引构造排序结果非常的重要。
临时表
上面提到在MySql中任何一个查询实质上都是一个关联查询。那么对于子查询或UNION查询是如何实现关联操作的呢。 对于UNION查询MySql先将每一个单表查询结果放到一个临时表中然后再重新读出临时表数据来完成UNION查询。MySql读取结果临时表和普通表一样也是采用的关联方式。 当遇到子查询时先执行子查询并将结果放到一个临时表中然后再将这个临时表当做一个普通表对待。 MySql的临时表是没有任何索引的在编写复杂的子查询和关联查询的时候需要注意这一点。 临时表也叫派生表。
用IN()取代OR
在MySql中IN()先将自己列表中的数据进行排序然后通过二分查找的方式确定列的值是否在IN()的列表中这个时间复杂度是O(logn)。如果换成OR操作则时间复杂度是O(n)。所以对于IN()的列表中有大量取值的时候用IN()替换OR操作将会更快。
优化MAX()和MIN()
在MySql中IN()先将自己列表中的数据进行排序然后通过二分查找的方式确定列的值是否在IN()的列表中这个时间复杂度是O(logn)。如果换成OR操作则时间复杂度是O(n)。所以对于IN()的列表中有大量取值的时候用IN()替换OR操作将会更快。
