php面試專題---18、MySQL查詢優化考點

一、總結

一句話總結：

慢查詢：查找分析查詢速度慢的原因

資料通路：優化查詢過程中的資料通路

長難句：優化長難的查詢語句

特定類型：優化特定類型的查詢語句

1、mysql中如何查詢哪些慢的查詢？

慢查詢日志：裡面會記錄那些比較慢的日志，可以使用pt-query-digest工具進行分析

explain語句：可以分析單條語句的查詢效率

show profile、show status、show processlist等語句：查詢語句執行慢的各種情況以及消息情況或其它

記錄慢查詢日志：裡面記錄了那些比較慢的查詢

分析查詢日志：不要直接打開慢查詢日志進行分析，這樣比較浪費時間和精力，可以使用pt-query-digest工具進行分析

使用show profile：set profiling=1；開啟，伺服器上執行的所有語句會檢測消耗的時間，存到臨時表中

show profile for query 臨時表ID：可以查詢每條profile臨時表中記錄花費的時間

使用show status：show status會傳回一些計數器，show global status檢視伺服器級别的所有計數

使用show processlist：觀察是否有大量線程處于不正常的狀态或者特征

使用explain：分析單條SQL語句

2、mysql中如何優化查詢過程中的資料通路？

盡量隻取需要的行和列：行方面用索引，列方面用需要的

通路資料太多導緻查詢性能下降

确定應用程式是否在檢索大量超過需要的資料，可能是太多行或列

确認MySQL伺服器是否在分析大量不必要的資料行

是否在掃描額外的記錄

使用explain來進行分析，如果發現查詢需要掃描大量的資料但隻傳回少數的行，可以通過如下技巧去優化：

使用索引覆寫掃描，把所有用的列都放到索引中，這樣存儲引擎不需要回表擷取對應行就可以傳回結果

改變資料庫和表的結構，修改資料表範式

重寫SQL語句，讓優化器可以以更優的方式執行查詢

避免使用如下SQL語句

1、查詢不需要的記錄：使用limit解決

2、多表關聯傳回全部列：指定A.id，A.name，B.age

3、總是取出全部列：SELECT*會讓優化器無法完成索引覆寫掃描的優化

4、重複查詢相同的資料，可以緩存資料，下次直接讀取緩存

3、mysql的SQL語句中我們應該避免做的？

4、不用緩存：重複查詢相同的資料，可以緩存資料，下次直接讀取緩存

4、mysql中如何優化長難的查詢語句？

切分查詢：将一個大的查詢分為多個小的相同的查詢：一次性删除1000萬的資料要比一次删除1萬，暫停一會的方案更加損耗伺服器開銷

分解關聯查詢：可以将一條關聯語句分解成多條SQL來執行

分解關聯查詢

可以将一條關聯語句分解成多條SQL來執行

讓緩存的效率更高

執行單個查詢可以減少鎖的競争

在應用層做關聯可以更容易對資料庫進行拆分

查詢效率會有大幅提升

較少備援記錄的查詢

5、mysql中我們選用一個複雜查詢還是用多個簡單查詢？

内部查詢快，和用戶端互動慢：MySQL内部每秒能掃描記憶體中上百萬行資料，相比之下，響應資料給用戶端就要慢得多

多個簡單查詢有必要：使用盡可能少的查詢是好的，但是有時将一個大的查詢分解為多個小的查詢是很有必要的

6、mysql中如何優化特定類型的查詢語句？

優化count()查詢：count（）中的*會忽略所有的列，直接統計所有列數，是以不要使用count（列名）

優化關聯查詢：确定ON或者USING子句的列上有索引；確定GROUP BY和ORDER BY中隻有一個表中的列，這樣MySQL才有可能使用索引

優化子查詢：盡可能使用關聯查詢來替代

優化GROUP BY和DISTINCT：這兩種查詢均可使用索引來優化，是最有效的優化方法

優化count(*)查詢

count（*）中的*會忽略所有的列，直接統計所有列數，是以不要使用count（列名）

MyISAM中，沒有任何WHERE條件的count（*）非常快；當有WHERE條件，MyISAM的count統計不一定比其他表引擎快

可以使用explain查詢近似值，用近似值替代count（*）

增加彙總表

使用緩存

優化關聯查詢

确定ON或者USING子句的列上有索引

確定GROUP BY和ORDER BY中隻有一個表中的列，這樣MySQL才有可能使用索引

優化子查詢

盡可能使用關聯查詢來替代

優化GROUP BY和DISTINCT

這兩種查詢均可使用索引來優化，是最有效的優化方法

關聯查詢中，使用辨別列進行分組的效率會更高

如果不需要ORDER BY，進行GROUP BY時使用ORDER BY NULL，MySQL不會再進行檔案排序

WITH ROLLUP超級聚合，可以挪到應用程式處理

優化LIMIT分頁

LIMIT偏移量大的時候，查詢效率較低，可以記錄上次查詢的最大ID，下次查詢時直接根據該ID來查詢

優化UNION查詢

UNION ALL的效率高于UNION