MySQL查詢性能優(yōu)化

1.為什么查詢速度會慢纽竣？

? 首先對一個查詢的生命周期簡化分析，大致可以按照順序分為：從客戶端、到服務(wù)器蜓氨、然后在服務(wù)器進(jìn)行解析聋袋、生成執(zhí)行計劃、執(zhí)行穴吹、并返回結(jié)果給客戶端幽勒。其中的執(zhí)行可以被認(rèn)為是查詢的生命周期中最重要的部分，執(zhí)行主要包括了大量為了檢索數(shù)據(jù)到存儲引擎的調(diào)用以及調(diào)用后數(shù)據(jù)的排序港令、分組等處理代嗤。

? 在完成生命周期的這些任務(wù)的時候，查詢需要在不用的地方消耗時間缠借，包括網(wǎng)絡(luò)、CPU計算宜猜、生成執(zhí)行計劃泼返、鎖等待等操作，尤其是向底層存儲引擎檢索數(shù)據(jù)的調(diào)用操作姨拥，這些調(diào)用操作需要在內(nèi)存操作绅喉、CPU操作和內(nèi)存不足時導(dǎo)致的I/O操作上消耗時間，根據(jù)存儲引擎不同叫乌，可能還會產(chǎn)生大量的上下文切換以及系統(tǒng)調(diào)用小號的時間柴罐。所有的這些任務(wù)消耗的時間共同構(gòu)成了響應(yīng)時間，在每一個消耗大量時間的查詢操作中憨奸，我們都能看到一些不必要的額外操作革屠，某些操作被額外的重復(fù)了很多次，某些操作執(zhí)行太慢等排宰，優(yōu)化查詢的目的就是減少和消除這些額外操作所花費的時間似芝。

2.慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

? 查詢性能低下的最根本原因就是訪問的數(shù)據(jù)太多。雖然某些查詢可能不可避免的需要篩選大量的數(shù)據(jù)板甘，但這并不常見党瓮。大部分性能低下的查詢都可以通過減少訪問數(shù)據(jù)量的方式進(jìn)行優(yōu)化⊙卫啵可以通過以下兩步進(jìn)行分析：

? 1.確認(rèn)是否存在檢索大量超過需要的數(shù)據(jù)寞奸。這通常意味著訪問了太多的行，但有時候也可能是因為訪問了太多的列在跳。

? 2.確認(rèn)MySQL服務(wù)器層是否存在分析大量超過需要的數(shù)據(jù)行枪萄。

2.1是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)？

  • 查詢不需要的記錄
  • 多表關(guān)聯(lián)時返回全部的列
  • 總是取出全部列

針對以上三點我這里試舉幾個典型案例：

? 1.查詢不需要的記錄

? 前端分頁就是經(jīng)典例子硬毕，查詢實際需求顯示第一頁10條呻引， 卻返回100萬條數(shù)據(jù)，性能低下顯而易見吐咳，建議后端分頁 LIMIT 0, 10

? 2.多表關(guān)聯(lián)時返回全部的列

? 假如你想查詢所在電影大話西游中出現(xiàn)的演員逻悠，千萬不能按照如下的方式查詢元践，這將返回這三個表全部的列：

SELECT * FROM actor
    INNER JOIN film_actor USING(actor_id)
    INNER JOIN film USING(film_id)
    WHERE film.title = '大話西游';

? 正確的做法是：

-- 規(guī)定只查詢 actor 表的列
SELECT actor.* from actor ...

? 3.總是取出全部的列

? 每次看到 SELECT * 的時候都要用懷疑的眼光審視，是不是真的需要返回全部的列童谒？很可能是不必須的单旁，取出全部列，會讓優(yōu)化器無法完成索引掃描這類優(yōu)化饥伊，還會為服務(wù)器帶來額外的 I/O象浑、內(nèi)存和CPU消耗。

2.2 MySQL是否在掃描額外的記錄琅豆？

? 在確定查詢只返回需要的數(shù)據(jù)后愉豺，接下來應(yīng)該看看查詢?yōu)榱朔祷亟Y(jié)果是否掃描了過多的數(shù)據(jù)。對于MySQL而言茫因，最簡單的衡量查詢開銷的三個指標(biāo)如下：

  • 響應(yīng)時間
  • 掃描的行數(shù)
  • 返回的行數(shù)

? 沒有哪個指標(biāo)能夠完美的衡量查詢的開銷蚪拦，但是他們大致反映了MySQL內(nèi)部執(zhí)行查詢時候需要訪問多少數(shù)據(jù)，并可以大概推算出查詢運行的時間冻押。這三個指標(biāo)都會記錄到MySQL的慢日志中驰贷，所以檢查慢日志記錄是找出掃描行數(shù)過多的查詢的好辦法。

下面通過一個例子來查看一下慢日志：

  • 存在一個部門表t_dept洛巢，包括100萬條部門記錄括袒，現(xiàn)在查詢部門名稱為事詢部的所有部門
-- 沒建立索引的情況下，100萬條數(shù)據(jù)中查詢大概0.8秒
SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事詢部';
  • 首先我將慢日志記錄時間設(shè)置為0.5秒稿茉，然后指定了慢日志位置
-- 將慢日志時間設(shè)置為0.5
set long_query_time=0.5;

-- 設(shè)置慢日志位置
set global slow_query_log_file='E:\mysql_slow_query.log';
  • 執(zhí)行上面的查詢操作锹锰，之后查看慢日志
圖1

  • 從慢日志中可以看到上面說到的主要指標(biāo)：查詢事件、返回行數(shù)漓库、掃描行數(shù)

  • 問題來了城须，返回行數(shù)899行，卻掃描了整個表100萬條數(shù)據(jù)米苹，當(dāng)然效率低下了糕伐，明顯用到了全表掃描，EXPLAIN執(zhí)行計劃中的type列說明了訪問類型蘸嘶。執(zhí)行看一下：

-- 執(zhí)行查詢計劃
EXPLAIN SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事詢部';
圖2

? 可以看到type訪問類型ALL全表掃描良瞧，預(yù)估訪問97萬行數(shù)據(jù)，Using Where代表MySQL將通過WHERE條件來篩選存儲引擎返回的記錄训唱。

  • 一般MySQL通過如下三種方式應(yīng)用WHERE條件褥蚯，從好到壞依次是：

    1.在索引中使用WHERE條件來過濾不匹配的記錄，這是在存儲引擎層完成的

    2.使用索引覆蓋掃描（在Extra中出現(xiàn)Using index）來返回記錄况增，直接從索引中過濾不需要的記錄并返回命中結(jié)果赞庶，這是在MySQL服務(wù)器層完成的。

    3.從表中返回數(shù)據(jù)，然后過濾不滿足條件的記錄（在Extra中顯示Using Where）再試在MySQL服務(wù)器層完成歧强，MySQL需要先從數(shù)據(jù)表讀出數(shù)據(jù)然后過濾澜薄。

2.3 加一個索引試試

-- 給部門表的部門名添加一條索引
ALTER TABLE t_dept ADD INDEX index_department(department);
-- 將慢sql記錄時間調(diào)整成0秒
set long_query_time=0
-- 再次執(zhí)行前面的查詢
SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事詢部';
-- 查看執(zhí)行計劃
EXPLAIN SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事詢部';
圖3
  • 可以看到訪問類型已經(jīng)從全表掃描ALL變成了非唯一性索引掃描ref,預(yù)計訪問行數(shù)由97萬行變成了899行，Extra顯示了WHERE條件使用了索引覆蓋掃描摊册。
  • 下面看一下慢日志肤京，注意要將慢日志記錄時限改成0秒，不然記錄不下來：
圖4
  • 由原來的的0.8秒變成了0.000562秒茅特，正確合適的使用索引優(yōu)化速度顯而易見忘分。

總結(jié)：

如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)行，而只返回其中少量的數(shù)據(jù)行白修，這樣就可以嘗試這樣優(yōu)化：

1.使用索引覆蓋掃描妒峦，把所有需要用的列放到索引中，這樣存儲引擎無需會標(biāo)獲取對應(yīng)的行就可以返回結(jié)果

2.改變表結(jié)構(gòu)兵睛，例如使用單獨的匯總表

3.重寫這個復(fù)雜查詢舟山，讓MySQL優(yōu)化器能夠以更優(yōu)化的方式執(zhí)行這個查詢

3.重構(gòu)查詢的方式

3.1 一個復(fù)雜查詢還是多個簡單查詢？

? 在傳統(tǒng)的實現(xiàn)中卤恳，總是強調(diào)需要數(shù)據(jù)庫層完成盡可能多的工作，這樣做的邏輯在于以前總是認(rèn)為網(wǎng)絡(luò)通信寒矿、查詢解析和優(yōu)化是一件代價很高的事情突琳。但是MySQL從設(shè)計上讓連接和斷開都很輕量級，現(xiàn)代網(wǎng)絡(luò)和速度比以前快很多符相，所以無論是帶寬還是延遲拆融，都支持MySQL在必要時可以將一個大的復(fù)雜查詢轉(zhuǎn)換成多個小的查詢。

? 雖然在其他條件都相同的情況下啊终，使用盡可能少的查詢當(dāng)然是更好的镜豹，但是有時候，將一個大的查詢分解成多個小的查詢是很有必要的蓝牲。

3.2 切分查詢

? 有時候?qū)τ谝粋€大查詢我們需要“分而治之”趟脂，將大查詢切分成小查詢，沒個查詢功能完全一樣例衍，只完成一小部分昔期，每次只返回一小部分查詢結(jié)果。

例如：

? 刪除舊的數(shù)據(jù)就是一個很好的例子佛玄，定期的清除大量數(shù)據(jù)的時候硼一，如果一個大的語句一次性完成的話，則可能需要一次鎖住很多數(shù)據(jù)梦抢、占滿整個事務(wù)日志般贼、耗盡系統(tǒng)資源、阻塞很多小的重要的查詢。因此切分成很多小的查詢多次完成哼蛆，則可以盡可能小的影響MySQL的性能蕊梧，同時還可以減少MySQL復(fù)制的延遲。例如：我們需要每個月運行一次下面的查詢：

-- 從messages表中刪除3個月之前創(chuàng)建的數(shù)據(jù)
DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

? 我們可以將這個查詢分成每次刪除10000條數(shù)據(jù)人芽，然后暫停一會再做下次刪除望几，這樣可以將服務(wù)器上原本一次性的壓力分散到一個很長的時間段中，可以降低對服務(wù)器的影響萤厅，減少刪除時鎖的持有時間橄抹。如下：

-- 分成多次執(zhí)行
DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000;

3.3分解關(guān)聯(lián)查詢

? 很多高性能應(yīng)用都會對關(guān)聯(lián)查詢進(jìn)行分解。簡單來說惕味，可以對每一個表進(jìn)行一次單表查詢楼誓，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，例如下面這個查詢：

SELECT
    * 
FROM
    t_user t1
    JOIN t_dept t2 USING ( id )
    JOIN t_address t3 USING ( id ) 
WHERE
    t1.fname = '6Yn'

-- 可以分解成下面這些查詢來代替：
SELECT * FROM t_user t WHERE t.fname = '6YN';
SELECT * FROM t_dept t WHERE t.id = 23456;
SELECT * FROM t_address t WHERE t.id in (12, 234, 23456, 100000);

這么做的優(yōu)勢是什么呢名挥？

  • 1.讓緩存更高效武通。許多應(yīng)用程序可以方便的緩存單表查詢對應(yīng)的結(jié)果對象。例如革答，上面的查詢中fname已經(jīng)被緩存了缕贡，那么應(yīng)用程序就可以跳過第一個查詢。再例如救湖，應(yīng)用中已經(jīng)緩存了ID為12愧杯， 34的內(nèi)容，那么第三個查詢的IN()中就可以少幾個ID鞋既。

  • 2.將查詢分解后力九，執(zhí)行單個查詢可以減少鎖的競爭。

  • 3.在應(yīng)用層做關(guān)聯(lián)邑闺，可以更容易對數(shù)據(jù)庫進(jìn)行拆分跌前，更容易做到高性能和可擴(kuò)展。

  • 4.查詢本身效率也可能會有所提升陡舅。

  • 5.可以減少冗余記錄的查詢抵乓。在應(yīng)用層做關(guān)聯(lián)查詢，意味著對于某條記錄應(yīng)用只需要查詢一次靶衍，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢臂寝，則可能需要重復(fù)的訪問一部分?jǐn)?shù)據(jù)。從這點看摊灭，這樣的重構(gòu)還可能會減少網(wǎng)絡(luò)和內(nèi)存消耗咆贬。

  • 6.這樣做相當(dāng)于在應(yīng)用中實現(xiàn)了哈希關(guān)聯(lián)，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)帚呼。

4.查詢執(zhí)行的基礎(chǔ)

圖5

4.1查詢緩存

  • 在解析一個查詢語句之前掏缎，如果查詢緩存是打開的皱蹦，那么MySQL會優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)。
  • 這個檢查是通過一個對大小寫敏感的哈希查找的眷蜈。查詢和緩存中的查詢即使只有一個不同沪哺，也不會匹配緩存結(jié)果。
  • 如果命中緩存酌儒，那么在返回結(jié)果前MySQL會檢查一次用戶權(quán)限辜妓，有權(quán)限則跳過其他步驟直接返回數(shù)據(jù)。

緩存何時失效忌怎？

? 在表的結(jié)構(gòu)或數(shù)據(jù)發(fā)生改變時籍滴，查詢緩存中的數(shù)據(jù)不再有效。有這些INSERT榴啸、UPDATE孽惰、 DELETE、TRUNCATE鸥印、ALTER TABLE勋功、DROP TABLE或DROP DATABASE會導(dǎo)致緩存數(shù)據(jù)失效。所以查詢緩存適合有大量相同查詢的應(yīng)用库说，不適合有大量數(shù)據(jù)更新的應(yīng)用狂鞋。

如何清理查詢緩存？

  • FLUSH QUERY CACHE; // 清理查詢緩存內(nèi)存碎片潜的。
  • RESET QUERY CACHE; // 從查詢緩存中移出所有查詢骚揍。
  • FLUSH TABLES; //關(guān)閉所有打開的表，同時該操作將會清空查詢緩存中的內(nèi)容夏块。

4.2 查詢優(yōu)化器

? MySQL的查詢優(yōu)化器是一個非常復(fù)雜的部件，它使用了很多優(yōu)化策略來生成一個最優(yōu)的執(zhí)行計劃纤掸。下面是一些MySQL能夠處理的優(yōu)化類型：

  • 重新定義關(guān)聯(lián)表的順序
  • 將外連接轉(zhuǎn)化成內(nèi)連接
  • 使用等價變換規(guī)則
  • 優(yōu)化COUNT()脐供、MIN()和MAX()
  • 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式
  • 覆蓋索引掃描
  • 子查詢優(yōu)化
  • 提前終止查詢
  • 等值查詢
  • 列表IN()的比較

上面列舉的并不是MySQL優(yōu)化器的全部，MySQL還會做大量其他的優(yōu)化借跪，列舉這些主要是讓大家明白優(yōu)化器的復(fù)雜性和智能型政己。如果說從上面這也例子中我們應(yīng)該學(xué)到些什么，那就是不要自認(rèn)為比優(yōu)化器更聰明掏愁，你可能會占點便宜歇由，但是更有可能會使查詢變得更加復(fù)雜而難以維護(hù)，而最終受益為零果港。作為開發(fā)人員而不是DBA來說沦泌，讓優(yōu)化器按照他的方式工作就可以了。

4.3 排序優(yōu)化

? 無論如何排序都是一個成本很高的操作辛掠，所以從性能角度考慮谢谦，應(yīng)盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進(jìn)行排序释牺。但是通常情況下，排序是不可避免的回挽，所以就要考慮如何優(yōu)化排序了没咙。

? 這里給出的建議是通過索引進(jìn)行排序。

? 當(dāng)不能使用索引生成排序結(jié)果的時候千劈，MySQL需要自己進(jìn)行排序祭刚，如果數(shù)據(jù)量小則在內(nèi)存中，數(shù)據(jù)量大則需要使用磁盤墙牌，不管是內(nèi)存還是磁盤中進(jìn)行排序都統(tǒng)稱為文件排序（filesort）涡驮。MySQL文件排序的時候需要使用臨時存儲空間可能會比想象的大得多。原因是：在MySQL進(jìn)行排序的時候憔古，對于每一個排序記錄都會分配一個足夠長的定長空間來存放遮怜，這個定長空間必須足夠長以容納其中最長的字符串，例如鸿市，如果是VARCHAR列則需要分配其完整長度锯梁；如果使用UTF-8字符集，那么MySQL將會為每個字符預(yù)留三個字節(jié)焰情。

5.關(guān)于MySQL層面優(yōu)化的一些建議

總結(jié)到 SQL 優(yōu)化中陌凳，就如下三點：

  • 最大化利用索引。
  • 盡可能避免全表掃描内舟。
  • 減少無效數(shù)據(jù)的查詢合敦。

理解 SQL 優(yōu)化原理 ，首先要搞清楚 SQL 執(zhí)行順序验游。

5.1 MySQL的執(zhí)行順序

MySQL的語法順序如下：

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP bY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_condition>

MySQL的執(zhí)行順序：

FROM
<表名> #選取表充岛，將多個表數(shù)據(jù)通過笛卡爾積變成一個表
ON
<篩選條件> #對笛卡爾積的虛表進(jìn)行篩選
JOIN #指定join類型
<join表> #用于添加數(shù)據(jù)到on之后的虛表中
WHERE 
<where條件> #對上述虛表進(jìn)行篩選
GROUP BY
<分組條件> #分組
<SUM()等聚合函數(shù)> #用于having進(jìn)行判斷，在書寫上這類聚合函數(shù)是寫在having判斷里面的
HAVING
<分組篩選> #對分組后的結(jié)果進(jìn)行聚合篩選
SELECT
<返回數(shù)據(jù)列表> #返回的單列必須在group by子句中耕蝉，聚合函數(shù)除外
DISTINCT
#數(shù)據(jù)去重
ORDER BY
<排序條件> #排序
LIMIT
<行數(shù)限制>

5.2 優(yōu)化建議

注意：以下 SQL 優(yōu)化策略適用于數(shù)據(jù)量較大的場景下崔梗，如果數(shù)據(jù)量較小一共幾百幾千條，屬實沒有必要考慮垒在。

5.2.1 避免不走索引的場景

  • 盡量避免字段開頭的模糊查詢蒜魄，會導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描
-- 查看執(zhí)行計劃
EXPLAIN SELECT
    * 
FROM
    t_user 
WHERE
    fname LIKE '%a%'
圖6
-- 查看執(zhí)行計劃
EXPLAIN SELECT
    * 
FROM
    t_user 
WHERE
    fname LIKE 'a%'
圖7

如果需求是要在前面使用模糊查詢：

1.使用 MySQL 內(nèi)置函數(shù) INSTR（str，substr）來匹配场躯，作用類似于 Java 中的 indexOf()谈为，查詢字符串出現(xiàn)的角標(biāo)位置。

2.使用 FullText 全文索引踢关，用 match against 檢索伞鲫。

3.數(shù)據(jù)量較大的情況，建議引用 ElasticSearch 億級數(shù)據(jù)量檢索速度秒級签舞。

4.當(dāng)表數(shù)據(jù)量較少（幾千條兒那種）榔昔，別整花里胡哨的驹闰，直接用 like '%xx%'。

  • 使用 or 必須 全部有索引撒会，否則全表掃描嘹朗，建議使用 union all 代替
#前提：fname帶索引，lname不帶索引
-- 因為lname沒有索引诵肛，會造成全表掃描
select * from t_user where fname = 'a' or lname = 'b';

-- 由于id和fname都有索引屹培，所以不會全表掃描
select * from t_user where id = 1 or fname = 'a';

  • 最佳左前綴原則

    例如我給t_user 表創(chuàng)建了聯(lián)合索引 index_fname_lname_age

-- 不走索引，全表掃描
select * from t_user where lname = 'v';
select * from t_user where age = '3';
select * from t_user where lname = 'b' and age = 3;
-- 走索引
select * from t_user where fname = 'v';
select * from t_user where fname = 'b' and lname = 'b' and age = 3;
-- 僅fname字段走索引
select * from t_user where fname = 'b' and age = 3;

實際上相當(dāng)于創(chuàng)建了fname索引怔檩、fname_lname索引 褪秀、fname_lname_age索引

總結(jié)：聯(lián)合索引必須保證有最左索引字段，中間字段不能斷薛训，直到最后字段媒吗，才能全部用上索引。

  • 索引值不會包含有NULL值的列

    ? 只要列中包含有NULL值都將不會被包含在索引中乙埃，復(fù)合索引中只要有一列含有NULL值闸英，那么這一列對于此復(fù)合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為NULL介袜。

  • 不要在列上進(jìn)行運算

-- 將在每個行上進(jìn)行運算甫何，導(dǎo)致掃描
select * from t_user where YEAR(create_time) < 2020;
-- 可以改寫成
select * from t_user where create_time < '2020-01-01';

  • 不使用not in和 <>操作

    ? NOT IN 和 <>都將導(dǎo)致不使用索引而全表掃描。NOT IN可以使用NOT EXISTS代替遇伞，id<>3可以使用id>3 or id<3代替

5.2.2 UNION和UNION ALL的區(qū)別

  • UNION 對兩個結(jié)果集進(jìn)行并集操作辙喂，會過濾掉重復(fù)的行，并且會進(jìn)行默認(rèn)規(guī)則的排序鸠珠；
  • UNION ALL 對兩個結(jié)果集進(jìn)行并集操作巍耗，不過濾重復(fù)的行，也不排序渐排。

如果你想取兩個結(jié)果集的前20條操作炬太，mysql會把兩個結(jié)果集都放到臨時表中，再取前20條飞盆，你可以分別取出兩個結(jié)果集的前20條進(jìn)行并集操作娄琉，然后再LIMIT 20

-- 注意次乓，這里沒有考慮排序問題吓歇，主要講的是減少臨時表中的數(shù)據(jù)
(select fname, lname
from t_user 
limit 20)
union all
(select fname, lname
from t_student
limit 20
) limit 20

? 實際上，MySQL總是使用創(chuàng)建并填充臨時表的方式來執(zhí)行UNION查詢票腰，因此很多優(yōu)化策略在UNION中沒法很好的使用城看，經(jīng)常需要手工的將where、limit杏慰、order by等子句下推到UNION的各個子查詢中测柠，以便優(yōu)化器可以充分的利用這些條件進(jìn)行優(yōu)化炼鞠。

? 除非確實需要服務(wù)器消除重復(fù)的行，否則一定要使用UNION ALL,這一點很重要轰胁，如果沒有All關(guān)鍵字谒主，MySQL會給臨時表加上distinct，這會導(dǎo)致對真?zhèn)€臨時表做唯一數(shù)據(jù)檢查赃阀，之后進(jìn)行排序霎肯，這樣做的代價非常高。即使有ALL關(guān)鍵字榛斯，mysql仍會使用臨時表存儲結(jié)果观游。

5.2.3 COUNT的使用

  • 例如：業(yè)務(wù)中需要根據(jù)一個或者多個條件查詢是否存在記錄，不關(guān)心有多少條記錄的時候驮俗，有人會這么寫：
-- 這條sql的確定是懂缕，不管有多少條滿足，會查出所有的行數(shù)王凑，
-- 而前提是我們并不關(guān)心行數(shù)搪柑，只關(guān)心是否存在
select count(*) from table where a = "param1" and b = "param2";

-- 優(yōu)化之后的寫法
-- sql不使用count而是使用limit 1限制查到一條就返回，不在繼續(xù)查找了荤崇，業(yè)務(wù)直接判斷是否非空即可
select 1 from table where a = param1 and b = param2 limit 1;
  • COUNT(*) / COUNT(1) / COUNT(列名) / COUNT(DISTINCT 列名) 的區(qū)別
-- 返回表中的記錄數(shù)（包括所有列）拌屏，相當(dāng)于統(tǒng)計行數(shù)(不會忽略列值為NULL的記錄)
count(*) 

-- 忽略所有列，1表示一個固定值术荤，也可以用count(2)倚喂、count(3)代替(不會忽略列值為NULL的記錄)
count(1) 

-- 返回列名指定列的記錄數(shù)，在統(tǒng)計結(jié)果的時候瓣戚，會忽略列值為NULL的記錄（不包括空字符串和0）端圈，即列值為NULL的記錄不統(tǒng)計在內(nèi)
count(列名)

-- 只包括列名指定列，返回指定列的不同值的記錄數(shù)子库，在統(tǒng)計結(jié)果的時候舱权，會忽略列值為NULL的記錄（不包括空字符串和0），即列值為NULL的記錄不統(tǒng)計在內(nèi)仑嗅。
count(distinct 列名)

count(*)&count(1)&count(列名)執(zhí)行效率比較：

  • 如果列為主鍵宴倍，count(列名)效率優(yōu)于count(1)
  • 如果列不為主鍵，count(1)效率優(yōu)于count(列名)
  • 如果表中存在主鍵仓技，count(主鍵列名)效率最優(yōu)
  • 如果表中只有一列鸵贬，則count(*)效率最優(yōu)
  • 如果表有多列，且不存在主鍵脖捻，則count(1)效率優(yōu)于count(*)

5.2.4 Limit的優(yōu)化使用

? 在系統(tǒng)中需要進(jìn)行分頁的時候阔逼，通常會使用LIMIT加上偏移量的方式實現(xiàn)，同時加上ORDER BY子句地沮。如果有對應(yīng)的索引嗜浮，通常效率會不錯羡亩，否則，MYSQL需要進(jìn)行大量的文件排序操作危融。

? 一個非常典型的問題是畏铆，在偏移量非常大的情況下，可能需要 limit 2000000,10 需要查詢兩百萬加20條數(shù)據(jù)然后丟棄兩百萬吉殃，只返回20條及志，這樣做的代價非常高。

  • 優(yōu)化方法：
-- 例如原本的sql是
select * from t_user order by fname limit 2000000, 20;

-- 使用延遲關(guān)聯(lián)優(yōu)化
-- 原理是：通過使用覆蓋索引查詢返回需要的主鍵寨腔，再根據(jù)這些主鍵關(guān)聯(lián)原表獲取需要的行速侈。這可以減少mysql掃描那些需要丟棄的行。
select * from t_user
inner join (
    select id from t_user order by fname limit 2000000, 20
) as t1 USING (id)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末迫卢，一起剝皮案震驚了整個濱河市倚搬，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌乾蛤，老刑警劉巖每界，帶你破解...
    沈念sama閱讀 218,525評論 6贊 507
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異家卖，居然都是意外死亡眨层，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,203評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門上荡，熙熙樓的掌柜王于貴愁眉苦臉地迎上來趴樱，“玉大人，你說我怎么就攤上這事酪捡∪鳎” “怎么了？”我有些...
    開封第一講書人閱讀 164,862評論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵逛薇，是天一觀的道長捺疼。 經(jīng)常有香客問我，道長永罚，這世上最難降的妖魔是什么啤呼？ 我笑而不...
    開封第一講書人閱讀 58,728評論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮呢袱，結(jié)果婚禮上官扣，老公的妹妹穿的比我還像新娘。我一直安慰自己产捞，他們只是感情好醇锚，可當(dāng)我...
    茶點故事閱讀 67,743評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布哼御。 她就那樣靜靜地躺著坯临，像睡著了一般焊唬。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上看靠，一...
    開封第一講書人閱讀 51,590評論 1贊 305
  • 城市分裂傳說
    那天赶促，我揣著相機(jī)與錄音，去河邊找鬼挟炬。 笑死鸥滨，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的谤祖。 我是一名探鬼主播婿滓，決...
    沈念sama閱讀 40,330評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼粥喜！你這毒婦竟也來了凸主？” 一聲冷哼從身側(cè)響起，我...
    開封第一講書人閱讀 39,244評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤额湘，失蹤者是張志新（化名）和其女友劉穎卿吐，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體锋华，經(jīng)...
    沈念sama閱讀 45,693評論 1贊 314
  • ?護(hù)林員之死
    正文 獨居荒郊野嶺守林人離奇死亡嗡官，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,885評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了毯焕。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片衍腥。...
    茶點故事閱讀 40,001評論 1贊 348
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖纳猫，靈堂內(nèi)的尸體忽然破棺而出紧阔，到底是詐尸還是另有隱情，我是刑警寧澤续担，帶...
    沈念sama閱讀 35,723評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布擅耽，位于F島的核電站，受9級特大地震影響物遇，放射性物質(zhì)發(fā)生泄漏乖仇。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 41,343評論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一询兴、第九天 我趴在偏房一處隱蔽的房頂上張望乃沙。 院中可真熱鬧，春花似錦诗舰、人聲如沸警儒。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,919評論 0贊 22
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽蜀铲。三九已至边琉，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間记劝，已是汗流浹背变姨。 一陣腳步聲響...
    開封第一講書人閱讀 33,042評論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工， 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留厌丑，地道東北人定欧。 一個月前我還...
    沈念sama閱讀 48,191評論 3贊 370
  • 代替公主和親
    正文 我出身青樓，卻偏偏與公主長得像怒竿，于是被迫代替她去往敵國和親砍鸠。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 44,955評論 2贊 355