幫助優(yōu)化MySQL數(shù)據(jù)庫性能的7個(gè)技巧

譯者注： 隨著尺寸和負(fù)載的增長，MySQL的性能會(huì)趨于下降。記住這些訣竅，便可保持MySQL的流暢運(yùn)行。

?

測量應(yīng)用程序的方法之一是看性能。而性能的指標(biāo)之一便是用戶體驗(yàn)，通俗的說法就是“用戶是否需要等待更長的時(shí)間才能得到他們想要的東西”。

這個(gè)指標(biāo)在不同的應(yīng)用場合而有所改變。對(duì)于移動(dòng)購物應(yīng)用，響應(yīng)時(shí)間不能超過幾秒鐘。對(duì)于員工的人力資源頁面，可能需要多花幾秒鐘的時(shí)間。

有很多關(guān)于性能如何影響用戶行為的研究：

79%的客戶不太可能回到慢速網(wǎng)站

47%的消費(fèi)者希望網(wǎng)頁在2秒或更短的時(shí)間內(nèi)完成加載

40%的用戶在網(wǎng)站加載時(shí)間超過3秒時(shí)會(huì)放棄

頁面加載時(shí)間的1秒延遲可能會(huì)導(dǎo)致7%的損失，頁面瀏覽量減少11%

無論采用何種標(biāo)準(zhǔn)，都必須保持良好的應(yīng)用性能。否則，用戶會(huì)抱怨(或者更糟的是，轉(zhuǎn)到不同的應(yīng)用程序)。影響應(yīng)用程序性能的因素之一是數(shù)據(jù)庫性能。應(yīng)用程序、網(wǎng)站和數(shù)據(jù)庫之間的交互對(duì)于建立應(yīng)用程序性能的好壞至關(guān)重要。

這種交互的一個(gè)核心組件是應(yīng)用程序如何查詢數(shù)據(jù)庫以及數(shù)據(jù)庫如何響應(yīng)請(qǐng)求。無論如何，MySQL都是最受歡迎的數(shù)據(jù)庫管理系統(tǒng)之一。在生產(chǎn)環(huán)境中，越來越多的企業(yè)正在轉(zhuǎn)向使用MySQL(和其他開源數(shù)據(jù)庫)作為數(shù)據(jù)庫解決方案。

有許多配置MySQL的方法可以幫助確保數(shù)據(jù)庫對(duì)查詢作出快速響應(yīng)，并使應(yīng)用程序性能降低到最低限度。

以下是幫助優(yōu)化MySQL數(shù)據(jù)庫性能的一些基本技巧。

優(yōu)化技巧 #1：學(xué)習(xí)如何使用 EXPLAIN

使用任何數(shù)據(jù)庫所做的兩個(gè)最重要的決定是設(shè)計(jì)應(yīng)用程序?qū)嶓w之間的關(guān)系如何映射到表(數(shù)據(jù)庫模式)，以及設(shè)計(jì)應(yīng)用程序如何以所需的格式獲得所需的數(shù)據(jù)(查詢)。

復(fù)雜的應(yīng)用程序可以有復(fù)雜的模式和查詢。如果想得到應(yīng)用程序所需要的性能和擴(kuò)展性，不能僅僅依靠直覺來理解如何執(zhí)行查詢。

應(yīng)該學(xué)習(xí)如何使用EXPLAIN命令，而不是隨意的猜測和想象。此命令展示了如何執(zhí)行查詢，并讓您了解所期望的性能，以及查詢將如何隨著數(shù)據(jù)大小的變化而伸縮。

有許多工具–比如MySQLWorkbench–可以可視化EXPLAIN輸出，但仍然需要理解基礎(chǔ)知識(shí)才能理解它。

EXPLAIN命令提供輸出的有兩種不同的格式：老式的表格式和更現(xiàn)代的結(jié)構(gòu)化JSON文檔，它提供了更多的細(xì)節(jié)(如下所示)：

mysql> explain format=json select avg(k) from sbtest1 where id between 1000 and 2000 G *************************** 1. row *************************** EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “762.40” }, “table”: { “table_name”: “sbtest1”, “access_type”: “range”, “possible_keys”: [ “PRIMARY” ], “key”: “PRIMARY”, “used_key_parts”: [ “id” ], “key_length”: “4”, “rows_examined_per_scan”: 1874, “rows_produced_per_join”: 1874, “filtered”: “100.00”, “cost_info”: { “read_cost”: “387.60”, “eval_cost”: “374.80”, “prefix_cost”: “762.40”, “data_read_per_join”: “351K” }, “used_columns”: [ “id”, “k” ], “attached_condition”: “(`sbtest`.`sbtest1`.`id` between 1000 and 2000)” } } }

應(yīng)該查看的一個(gè)組件是“query cost”。query cost是指MySQL根據(jù)查詢執(zhí)行的總開銷來考慮這個(gè)特定查詢的代價(jià)，并且基于許多不同的因素。

簡單查詢的查詢開銷通常小于1，000。開銷在1，000到100，000之間的查詢被認(rèn)為是中等開銷的查詢，而且如果每秒只運(yùn)行數(shù)百個(gè)這樣的查詢(而不是數(shù)萬個(gè))，通常會(huì)比較快。

開銷超過100，000的查詢可以當(dāng)作是昂貴的。通常，當(dāng)您是系統(tǒng)上的單個(gè)用戶時(shí)，這些查詢?nèi)詴?huì)快速運(yùn)行，但您應(yīng)該仔細(xì)考慮在交互式應(yīng)用程序中使用此類查詢的頻率(尤其是隨著用戶數(shù)量的增長)。

當(dāng)然，這些數(shù)字只是性能的一個(gè)大概的體現(xiàn)，但它們展示了一般原則。您的系統(tǒng)可能更好地處理查詢工作負(fù)載，也可能更糟，這取決于其體系結(jié)構(gòu)和配置。

決定查詢開銷的主要因素是查詢是否正確使用索引。EXPLAIN 命令可以告訴您查詢是否使用索引(通常是因?yàn)樗饕侨绾卧跀?shù)據(jù)庫中創(chuàng)建的，或者查詢本身是如何設(shè)計(jì)的)。這就是為什么學(xué)會(huì)使用 EXPLAIN 是如此重要。

優(yōu)化技巧 #2：創(chuàng)建正確的索引

索引通過減少查詢必須掃描的數(shù)據(jù)庫中的數(shù)據(jù)量來提高查詢效率。MySQL中的索引用于加速數(shù)據(jù)庫中的訪問，并幫助執(zhí)行數(shù)據(jù)庫約束(如 UNIQUE和FOREIGN KEY )。