一.Explain

Explain命令在解決數(shù)據(jù)庫性能上是第一推薦使用命令，大部分的性能問題可以通過此命令來簡單的解決，Explain可以用來查看SQL語句的執(zhí)行效 果，可以幫助選擇更好的索引和優(yōu)化查詢語句，寫出更好的優(yōu)化語句。

Explain語法：explain select … from … [where …]

例如：explain select * from news;

輸出： +----+-------------+-------+-------+-------------------+---------+---------+-------+------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------

下面對各個屬性進(jìn)行了解：

1、id：這是SELECT的查詢序列號

2、select_type：select_type就是select的類型，可以有以下幾種：

SIMPLE：簡單SELECT(不使用UNION或子查詢等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二個或后面的SELECT語句

DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢

UNION RESULT：UNION的結(jié)果。

SUBQUERY：子查詢中的第一個SELECT

DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢

DERIVED：導(dǎo)出表的SELECT(FROM子句的子查詢)

3、table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

4、type：這列最重要，顯示了連接使用了哪種類別,有無使用索引，是使用Explain命令分析性能瓶頸的關(guān)鍵項(xiàng)之一。

結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref，否則就可能會出現(xiàn)性能問題。

a.ALL：Full Table Scan 全表掃描，MySQL 將遍歷全表以找到匹配的行。 b.index：Full Index Scan 全索引掃描，index 與 ALL 區(qū)別為 index 類型只遍歷索引樹 c. range：索引范圍掃描，對索引的掃描開始于某一點(diǎn)，返回匹配值域的行。顯而易 見的索引范圍掃描是帶有 between 或者 where 子句里帶有<, >查詢。當(dāng) mysql 使用索引 去查找一系列值時，例如 IN()和 OR 列表，也會顯示 range（范圍掃描）,當(dāng)然性能上面是 有差異的。 d. ref_or_null：該聯(lián)接類型如同 ref,但是添加了 MySQL 可以專門搜索包含 NULL 值的 行。 e. index_merge：該聯(lián)接類型表示使用了索引合并優(yōu)化方法。 f. unique_subquery：該類型替換了下面形式的 IN 子查詢的 ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) 。unique_subquery 是一個索引查 找函數(shù),可以完全替換子查詢,效率更高。 g. index_subquery：該聯(lián)接類型類似于 unique_subquery?？梢蕴鎿Q IN 子查詢,但只 適 合 下 列 形 式 的 子 查 詢 中 的 非 唯 一 索 引 : value IN (SELECT key_column FROM single_table WHERE some_expr) h.ref：就是連接程序無法根據(jù)鍵值只取得一條記錄，使用索引的最左前綴或者索引不 是 primary key 或 unique 索引的情況。當(dāng)根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時，這 就是一個不錯的連接類型。 i.eq_ref：類似 ref，區(qū)別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有 一條記錄匹配，簡單來說，就是多表連接中使用 primary key 或者 unique key 作為關(guān)聯(lián) 條件. j.const、system：當(dāng) MySQL 對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個常量時，使用這 些類型訪問。如將主鍵置于 where 列表中，MySQL 就能將該查詢轉(zhuǎn)換為一個常量。 注：system 是 const 類型的特例，當(dāng)查詢的表只有一行的情況下，使用 system k.NULL：MySQL 在優(yōu)化過程中分解語句，執(zhí)行時甚至不用訪問表或索引，例如從一 個索引列里選取最小值可以通過單獨(dú)索引查找完成。

5、possible_keys：列指出MySQL能使用哪個索引在該表中找到行

6、key：顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL

7、key_len：顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好

8、ref：顯示使用哪個列或常數(shù)與key一起從表中選擇行。

9、rows：顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。

10、Extra：包含MySQL解決查詢的詳細(xì)信息，也是關(guān)鍵參考項(xiàng)之一。

二.慢查詢?nèi)罩?/p>

當(dāng)查詢超過一定的時間沒有返回結(jié)果的時候，才會記錄到慢查詢?nèi)罩局小ＤJ(rèn)不開啟。

采樣的時候手工開啟。可以幫助我們找出執(zhí)行慢的 SQL 語句 1、查看慢 SQL 日志是否啟用（on 表示啟用）: show variables like 'slow_query_log'; 2、查看執(zhí)行慢于多少秒的 SQL 會記錄到日志文件中 show variables like 'long_query_time'; 3、可以使用模糊搜索，查看所有含有 query 的變量信息 show variables like '%query%';

4、是否開啟慢查詢?nèi)罩?/p>

slow_query_log=1

5、指定保存路徑及文件名，默認(rèn)為數(shù)據(jù)文件目錄，

slow_query_log_file="bxg_mysql_slow.log" #

--指定多少秒返回查詢的結(jié)果為慢查詢 long_query_time=1

6、記錄所有沒有使用到索引的查詢語句

log_queries_not_using_indexes=1

7、記錄那些由于查找了多于 1000 次而引發(fā)的慢查詢

min_examined_row_limit=1000

8、記錄那些慢的 optimize table，analyze table 和 alter table 語句

log_slow_admin_statements=1

9、記錄由 Slave 所產(chǎn)生的慢查詢

log_slow_slave_statements=1

命令行修改配置方式不需要不重啟即可生效，但重啟之后會自動失效。

1、set global slow_query_log=1; 2、set global slow_query_log_file='bxg_mysql_slow.log'; 3、set long_query_time=1; 4、set global log_queries_not_using_indexes=1; 5、set global min_examined_row_limit=1000; 6、set global log_slow_admin_statements=1; 7、set global log_slow_slave_statements=1;

三.緩存

關(guān)閉緩存有兩種放法，一種臨時的，一種永久的。臨時的直接在命令行執(zhí)行

set global query_cache_size=0; set global query_cache_type=0; --如果配置文件中為關(guān)閉緩存的話，不能通過命令開啟緩存

永久的修改配置文件 my.cnf ,添加下面的配置即可。

query_cache_type=0 query_cache_size=0

另外，我們還可以通過 sql_no_cache 關(guān)鍵字在 sql 語句中直接禁用緩存，在開啟緩存

的情況下我們對 sql 語句做一些改動

Select sql_no_cache count() from pythonlearn.lianjia; -- 不緩存 Select sql_cache count() from pythonlearn.lianjia; -- 緩存（也可以不加，默認(rèn)緩存已經(jīng)開啟了）

四.準(zhǔn)備測試數(shù)據(jù)

-- 用戶表

CREATE TABLE person ( id bigint(20) unsigned NOT NULL, fname varchar(100) NOT NULL, lname varchar(100) NOT NULL, age tinyint(3) unsigned NOT NULL, sex tinyint(1) unsigned NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8

--用戶部門表

CREATE TABLE department ( id bigint(20) unsigned NOT NULL, department varchar(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 用戶住址表

CREATE TABLE address ( id bigint(20) unsigned NOT NULL, address varchar(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 創(chuàng)建存儲過程，用于批量添加測試數(shù)據(jù)

DELIMITER $$ DROP PROCEDURE IF EXISTS generate$$ CREATE PROCEDURE generate(IN num INT) BEGIN DECLARE chars VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

DECLARE fname VARCHAR(10) DEFAULT '';

DECLARE lname VARCHAR(25) DEFAULT '';

DECLARE id INT UNSIGNED;

DECLARE len INT;

SET id=1;

DELETE FROM person;

WHILE id <= num DO

? ? SET len = FLOOR(1 + RAND()*10);

? ? SET fname = '';

? ? WHILE len > 0 DO

? ? ? ? SET fname = CONCAT(fname,SUBSTRING(chars,FLOOR(1 + RAND()*62),1));

? ? ? ? SET len = len - 1;

? ? END WHILE;

? ? SET len = FLOOR(1+RAND()*25);

? ? SET lname = '';

? ? WHILE len > 0 DO

? ? ? ? SET lname = CONCAT(lname,SUBSTR(chars,FLOOR(1 + RAND()*62),1));

? ? ? ? SET len = len - 1;

? ? END WHILE;

? ? INSERT INTO person VALUES (id,fname,lname, FLOOR(RAND()*100), FLOOR(RAND()*2));

? ? SET id = id + 1;

END WHILE;

END $$ DELIMITER ;

執(zhí)行存儲過程

-- 停掉事務(wù) SET autocommit = 0; -- 調(diào)用存儲過程 CALL generate(1000000); SET autocommit = 1; -- 重啟事務(wù)

五.Profiling 的使用

要想優(yōu)化一條 Query，就須要清楚這條 Query 的性能瓶頸到底在哪里，是消耗的 CPU 計算太多，還是需要的 IO 操作太多？要想能夠清楚地了解這些信息，可以通過 Query Profiler 功能得到。 Query Profiler 是 MYSQL 自帶的一種 query 診斷分析工具，通過它可以分析出一條 SQL 語句的性能瓶頸在什么地方。通常我們是使用的 explain,以及 slow query log 都無法 做到精確分析，但是 Query Profiler 卻可以定位出一條 SQL 語句執(zhí)行的各種資源消耗情況， 比如 CPU，IO 等，以及該 SQL 執(zhí)行所耗費(fèi)的時間等。

用法

（1）通過執(zhí)行“set profiling”命令，可以開啟關(guān)閉 QueryProfiler 功能 mysql> SET global profiling=on;（set profiling=1） （2）查看相關(guān)變量 show VARIABLES like '%profiling%'; （3）設(shè)置保存數(shù)量默認(rèn) 15 條，最大值為 100 mysql> set profiling_history_size=100; （4）在開啟 Query Profiler 功能之后，MySQL 就會自動記錄所有執(zhí)行的 Query 的 profile 信息，下面執(zhí)行 n 條 Query 作為測試 select * from person limit 10000,100; （3）獲取當(dāng)前系統(tǒng)中保存的多個 Query 的 profile 的概要信息 mysql> show profiles; （4）針對單個 Query 獲取詳細(xì)的 profile 信息。 可以根據(jù)概要信息中的 Query_ID 來獲取某個 Query 在執(zhí)行過程中詳細(xì)的 profile 信 息。例如查看 cpu 和 io 的詳細(xì)信息 show profile cpu,block io for query 501; show profile ALL for query 501; ALL ：顯示所有信息 |BLOCK IO ：塊設(shè)備 IO 輸入輸出次數(shù) |CONTEXT SWITCHES：上下文切換相關(guān)開銷 |CPU：用戶和系統(tǒng)的 CPU 使用情況 |IPC：顯示發(fā)送和接收消息的相關(guān)消耗 |MEMORY：內(nèi)存消耗情況（該版本 is not currently implemented） |PAGE FAULTS：顯示主要和次要頁面故障相關(guān)的開銷 |SOURCE：顯示和 Source_function,Source_file,Source_line 相關(guān)的開銷信息 |SWAPS：顯示交換次數(shù)相關(guān)的開銷 注意：profiling 被應(yīng)用在每一個會話中，當(dāng)前會話關(guān)閉后，profiling 統(tǒng)計的信息將丟失。

六.sql 語句優(yōu)化

常用 sql 優(yōu)化建議

1、避免 SELECT * 從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會變得越慢。并且如果你的數(shù)據(jù)庫服務(wù)器和

WEB 服務(wù)器是兩臺獨(dú)立的服務(wù)器的話，這還會增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。 select * from person where lname='x8RJWmQX'; select id from person where lname='x8RJWmQX'; 2、避免在 where 子句中使用!=或<>操作符 應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎放棄使用索引而進(jìn)行全表 掃描。 EXPLAIN select * from person where fname != 'sss’ ; 3、 盡量避免全表掃描 對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的 列上建立索引。 4、用 UNION 來代替 OR 采用 OR 語句： select * from person where fname ='LVc1oJjd' or fname='bjRdlVo'; 采用 UNION 語句，返回的結(jié)果同上面的一樣，但是速度要快些： select * from person where fname ='LVc1oJjd' Union select * from person where fname='bjRdlVo'; 分別對這兩個 sql 進(jìn)行 explain 分析： OR 語句的結(jié)果 UNION 語句的結(jié)果 我們來比較下重要指標(biāo)，發(fā)現(xiàn)主要差別是 type 和 ref 這兩項(xiàng)。type 顯示的是訪問 類型，是較為重要的一個指標(biāo)，結(jié)果值從好到壞依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL UNION 語句的 type 值為 一般為 ref，OR 語句的 type 值為 range，可以看到這 是一個很明顯的差距。 UNION 語句的 ref 值為 const，OR 語句的 type 值為 null，const 表示是常量值 引用，非?？臁?這兩項(xiàng)的差距就說明了 UNION 要優(yōu)于 OR，從我們的直觀感覺上也可以理解，雖然 這兩個方式都用到了索引，但 UNION 是用一個明確的值到索引中查找，目標(biāo)非常明確， OR 需要對比兩個值，目標(biāo)相對要模糊一些，所以 OR 在恍惚中落后了。 5、 like 語句避免前置百分號 前置百分號會導(dǎo)致索引失效 select * from person where fname like '%LVc1o%' ; 下面走索引 select * from person where fname like 'LVc1o%' ; 6、 避免在 where 子句中對字段進(jìn)行表達(dá)式操作 應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而 進(jìn)行全表掃描。如： select id from t where num/2=100 應(yīng)改為: select id from t where num=100*2 7、 避免在 where 子句中對字段進(jìn)行函數(shù)操作 應(yīng)盡量避免在 where 子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行 全表掃描。如： select id from t where substring(name,1,3)=’abc’ 應(yīng)改為: select id from t where name like ‘a(chǎn)bc%’ 8、盡量使用數(shù)字型字段 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型，這會降低查詢 和連接的性能，并會增加存儲開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時會 逐個比較字符 串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。

9、大數(shù)據(jù)量的分頁優(yōu)化 使用 limit 進(jìn)行分頁，翻到 10000 多頁后效率低。原因在于 limit offset 會逐行查找， 是先查詢再跳過。 select * from person limit 999900,100; -- 慢了，大概需要 0.4 秒多 a. 從業(yè)務(wù)邏輯優(yōu)化 不允許翻過 100 頁，例如百度一般可以翻到 70 頁左右 b. 技術(shù)優(yōu)化方法一 select * from person where id>999900 limit 100; 這樣就非常快，0.001s 左右，因?yàn)槭褂昧?id 索引 但這樣用有前提，id 是連續(xù)的，中間的數(shù)據(jù)不能刪，否則 id 為 999900 的并不是第 999900 個記錄。 c. 技術(shù)優(yōu)化方法二 如果必須用 limit offset 查詢，就用延遲關(guān)聯(lián) select id from person limit 999900 ,100; 這樣只查詢 id 列，實(shí)現(xiàn)了索引覆蓋，就會很快 select p.* from person p inner join (select id from person limit 999900 ,100) as tmp on p.id=tmp.id; 通過內(nèi)連接再獲取分頁后每條記錄的詳細(xì)信息

————————————————

版權(quán)聲明：本文為CSDN博主「Java工程師time」的原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。

原文鏈接：https://blog.csdn.net/qq_30398499/article/details/90452766