學習MySQL如何最佳化查詢速度

前面章節我們介紹如何選擇最佳化的資料類型、如何有效率的使用索引，這些對於高效能的MySQL來說是不可或缺的。但這些還完全不夠，還需要合理的設計查詢。如果查詢寫的很糟糕，即使表結構再合理、索引再合適，也是無法實現高效能的。

談到MySQL效能最佳化，查詢最佳化作為最佳化的源頭，它也是最能體現一個系統是否更快。本章以及接下來的幾章將會著重講解關於查詢效能優化的內容，從中會介紹一些查詢優化的技巧，幫助大家更深刻地理解MySQL如何真正地執行查詢、究竟慢在哪裡、如何讓其快起來，並且明白高效率和低效的原因何在，這更有助於你更好的來最佳化查詢SQL語句。

相關學習推薦：mysql影片教學

#本章從「為什麼查詢速度這麼慢」開始談起，讓你能夠清楚的知道查詢可能會慢在哪些環節，這將有助於你更好的最佳化查詢，做到心中有數，高人一籌。

一、慢在哪

真正衡量查詢速度的是回應時間。如果把查詢看成是一個任務，那麼它是由一系列子任務組成的，每個任務都會消耗一定的時間。如果要最佳化查詢，實際上要優化其子任務，那麼消除其中一些子任務，那麼減少子任務的執行次數，要麼讓子任務運行的更快。

MySQL在執行查詢的時候，有哪些子任務，哪些子任務花費的時間最多？這就需要藉助一些工具，或是一些方法(如： 執行計劃)對查詢進行剖析，來定位發現究竟慢在哪。

通常來說，查詢的生命週期大致可以按照順序來看： 從客戶端到伺服器，然後在伺服器上進行解析，產生執行計劃，執行，並傳回結果給客戶端。其中，「執行」可以認為是整個生命週期中最重要的階段，這包括了大量為了檢索資料到儲存引擎的呼叫以及呼叫後的資料處理，包括排序、分組等。

在完成這些任務的時候，查詢需要在不同階段的不同地方花費時間，包括網路、CPU計算，產生統計資訊和執行計劃、鎖定等待等操作，尤其是向底層儲存引擎檢索數據的呼叫操作，這些呼叫需要在記憶體操作、CPU操作，也可能產生大量的上下文切換以及系統呼叫。

在上述這些操作中，都會消耗大量的時間，其中會存在一些不必要的額外操作，其中有些操作可能被額外地重複執行了很多次、某些操作執行的很慢等等。這也就是查詢真正可能慢的地方， 優化查詢的目的就是減少和消除這些操作所花費的時間 。

透過上面的分析，我們對查詢的過程有了整體的了解，能夠清楚的知道查詢可能在哪些地方會存在問題，最終導致整個查詢很慢，為實際查詢優化提供方向。

換言之，查詢最佳化可以從以下兩個角度來出發：

• 減少子查詢次數
• 減少額外、重複的動作

#查詢效能低下常見的原因是存取的資料太多。在資料量小的時候，查詢速度還不錯，一旦資料量上來，查詢速度將會發生巨變，讓人抓狂、體驗極差。針對查詢最佳化方面，可以從以下方面進行排查：

• 是否查詢了不需要的資料
• 是否掃描了額外的記錄

二、是否查詢了不需要的數據

在實際查詢中很多時候，會查詢了實際需要的數據，然後這些多餘的數據會被應用程序丟棄。這對MySQL來說是額外的開銷，同時也會消耗應用程式伺服器的CPU和記憶體資源。
一些典型案例如下：

1. 查詢不需要的記錄

這是常見的錯誤，常常會誤以為MySQL只會回傳所需的數據，實際上MySQL卻是先回傳全部結果集再進行計算。

開發者習慣性的先使用SELECT語句查詢大量的結果，然後由應用查詢或前端展示層再獲取前面的N行數據，例如，在新聞網站中查詢100條記錄，但是只是在頁面上顯示前10條。

最有效的解決方法是需要多少記錄就查詢多少記錄，通常會在查詢後面加上LIMIT，即： 分頁查詢。

2. 多表關聯時返回全部列

如果你想查詢所有在電影Academy Dinosaur中出現的演員，千萬不要按下面的方式來進行查詢：

select * fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

這樣將會傳回三張表的全部資料列，而實際需求是要查詢演員訊息，正確的寫法應該是：

select a.* fromt actor a
inner join film_actor fa.actorId = a.actorId
inner join film f f.filmId = fa.filmId
where fa.title = 'Academy Dinosaur';

3. 总是查询出全部列

每次看到select *的时候一定要用异样的目光来审视它，是不是真的需要返回全部数据列？

在大部分情况下，是不需要的。 select *会导致进行全表扫描，会让优化器无法完成索引扫描这类优化，过多的列还会为服务器带来额外的I/O、内存和CPU的消耗。 即使真的需要查询出全部列，应该逐个罗列出全部列而不是*。

4. 重复查询相同的数据

如果你不太留意，很容易出现这样的错误： 不断地重复执行相同的查询，然后每次都返回完全相同的数据。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复来查询这个数据。 比较好处理方法是，在初次查询的时候将这个数据缓存起来，后续使用时直接从缓存中取出。

三、是否扫描了额外的记录

确 定查询只查询了需要的数据以后，接下来应该看看查询过程中是否扫描了过多的数据。 对于MySQL，最简单衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完全来衡量查询的开销，但它们能够大致反映MySQL内部执行查询时需要访问多少数据，并可以大概推算出查询运行的实际。 这三个指标都会记录到MySQL的慢日志中，所以 检查慢日志记录是找出扫描行数过多查询的办法 。

慢查询： 用于记录在MySQL中响应时间超过阈值(long_query_time,默认10s)的语句，并会将慢查询记录到慢日志中。 可通过变量slow_query_long来开启慢查询，默认是关闭状态，可以将慢日志记录到表slow_log或文件中，以供检查分析。

1. 响应时间

响应时间是两个部分之和： 服务时间和排队时间。 服务时间是指数据库处理这个查询真正花费了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间，可能是等待I/O操作，也可能是等待 行 锁等等。

在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。 诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间，所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

2. 扫描的行数和返回的行数

在分析查询时，查看该查询扫描的行数是非常有帮助的，在此之上也能够分析是否扫描了额外的记录。

对于找出那些糟糕查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。 较短的行的访问速度相当快，内存中的行也比磁盘中的行的访问速度要快的多。

理想的情况下，扫描的行数和返回的行数应该是相同的。 但实际上这种美事并不多，例如在做一个关联查询的时候，扫描的行数和对返回的行数的比率通常都很小，一般在1:1和10：1之间，不过有时候这个值也可能非常大。

3. 扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 这些访问方式可能需要访问很多行才能返回一条结果，也有些访问方式可能无需扫描就能返回结果。

在执行计划EXPLAIN语句中的type列反映了访问类型。 访问类型有很多种，从全表扫描到索引扫描，范围扫描，唯一索引，常数索引等。 这里列的这些，速度是从慢到快，扫描的行数也是从多到少。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引，这也是我们之前讨论索引的问题。 现在应该明白为什么索引对于查询优化如此重要了。 索引让MySQL以最高效，扫描行数最少的方式找到需要的记录 。

如果发现查询扫描了大量的数据但只返回少数的行，通常可以尝试下面的技巧去优化它：

• 使用索引覆寫掃描，把所有需要用的欄位都放到索引中，這樣儲存引擎就不需要回表取得對應的資料列就可以回傳結果了。
  
• 優化表結構。例如使用單獨的總計表來完成查詢。
• 重寫複雜查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢。

相關推薦：程式設計影片課程

#

以上是學習MySQL如何最佳化查詢速度的詳細內容。更多資訊請關注PHP中文網其他相關文章！