如何优化mysql的查询性能

Question

如何优化mysql的查询性能，如何从3秒查询变为0.1秒，有哪些优化方案

Answer 1

一、是否请求了不需要的数据

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效查询可以通过如下两个步骤来分析总是有效：
【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.name='Li';
--拆分后
SELECT * FROM teacher t WHERE t.name='Li';
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 ID>5;

通过 SHOW STATUS 的结果可以看到该查询需要扫描 5000行数据。如果将条件反转，先查找ID小于等于5的城市，然后用总城市减就能获得同样的结果，却可以将扫描数减少到5行以内。

--ID 是索引，所以会去前5行数据
SELECT (SELECT COUNT(*) FROM city)-COUNT(*) FROM  city WHERE ID<=5;

通常来说，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 temporary；Using 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：表示多种情况，线程可能在多种状态之间传送数据。