【數(shù)據(jù)庫(kù)優(yōu)化】MySQL性能優(yōu)化基礎(chǔ)

mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------------------+| Variable_name       | Value                                                |+---------------------+------------------------------------------------------+| slow_query_log      | OFF                                                  || slow_query_log_file | D:\mysql-5.7.27-winx64\data\DESKTOP-E9F062A-slow.log |+---------------------+------------------------------------------------------+

slow_query_log 慢查詢開啟狀態(tài) OFF 未開啟 ON 為開啟slow_query_log_file 慢查詢?nèi)罩敬娣诺奈恢茫ㄟ@個(gè)目錄需要MySQL的運(yùn)行帳號(hào)的可寫權(quán)限，一般設(shè)置為MySQL的數(shù)據(jù)存放目錄）

mysql> set global slow_query_log = 1; //設(shè)置開啟或者關(guān)閉，0為關(guān)閉，1為開啟mysql> set global long_query_time = 3;  //設(shè)置慢的闕值時(shí)間，默認(rèn)10秒

[mysqld]slow_query_log = 1 #開啟slow_query_log_file = /mysql-5.7.27-winx64/data/mysql-slow.log #默認(rèn)host_name_show.loglong_query_time = 3 #默認(rèn)10秒（查詢超過(guò)多少秒才記錄）log-queries-not-using-indexes = on #如果值設(shè)置為ON，則會(huì)記錄所有沒(méi)有利用索引的查詢，一般在性能調(diào)優(yōu)的時(shí)候會(huì)暫時(shí)開啟。log_output = 'FILE,TABLE' #輸出的格式(FILE:文本, TABLE:表中, FILE,TABLE:同時(shí)輸出到文本和表中)

mysql> select sleep(5);

mysql> show global status like '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries  | 1     |+---------------+-------+

mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log  //得到返回記錄集最多的10個(gè)SQLmysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到訪問(wèn)次數(shù)最多的10個(gè)SQL mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log  //得到按照時(shí)間排序的前10條里面含有做了連接的查詢SQLmysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more  //另外建議在使用這些命令時(shí)結(jié)合|和more使用，否則有可能出現(xiàn)爆屏情況

mysqlsla -lt slow -sort t_sum -top 1000  /tmp/slow_query.log

結(jié)果選項(xiàng)說(shuō)明：總查詢次數(shù) (queries total),去重后的sql數(shù)量 (unique),輸出報(bào)表的內(nèi)容排序(sorted by),最重大的慢sql統(tǒng)計(jì)信息(包括 平均執(zhí)行時(shí)間, 等待鎖時(shí)間, 結(jié)果行的總數(shù), 掃描的行總數(shù))Count, sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比.Time, 執(zhí)行時(shí)間, 包括總時(shí)間, 平均時(shí)間, 最小, 最大時(shí)間, 時(shí)間占到總慢sql時(shí)間的百分比.95% of Time, 去除最快和最慢的sql, 覆蓋率占95%的sql的執(zhí)行時(shí)間.Lock Time, 等待鎖的時(shí)間.95% of Lock , 95%的慢sql等待鎖時(shí)間.Rows sent, 結(jié)果行統(tǒng)計(jì)數(shù)量, 包括平均, 最小, 最大數(shù)量.Rows examined, 掃描的行數(shù)量.Database, 屬于哪個(gè)數(shù)據(jù)庫(kù)Users, 哪個(gè)用戶,IP, 占到所有用戶執(zhí)行的sql百分比Query abstract, 抽象后的sql語(yǔ)句Query sample, sql語(yǔ)句mysqlsla常用參數(shù)說(shuō)明：-log-type (-lt) type logs:通過(guò)這個(gè)參數(shù)來(lái)制定log的類型，主要有slow, general, binary, msl, udl,分析slow log時(shí)通過(guò)制定為slow-sort:t_sum:按總時(shí)間排序(默認(rèn))，c_sum:按總次數(shù)排序c_sum_p: sql語(yǔ)句執(zhí)行次數(shù)占總執(zhí)行次數(shù)的百分比。-top:顯示sql的數(shù)量，默認(rèn)是10,表示按規(guī)則取排序的前多少條–statement-filter (-sf) [+-][TYPE]:過(guò)濾sql語(yǔ)句的類型，比如select、update、drop，[TYPE] 有SELECT, CREATE, DROP, UPDATE, INSERT，例如”+SELECT,INSERT”，不出現(xiàn)的默認(rèn)是-，即不包括。-db：要處理哪個(gè)庫(kù)的日志：

# 舉個(gè)例子，只取funsion數(shù)據(jù)庫(kù)的select語(yǔ)句，并按照總時(shí)間排序，取前1000條數(shù)據(jù)# 保存到當(dāng)前目錄下的 slow_query.pretty.log文件中mysqlsla -lt slow  -sort t_sum  -sf "+select"  -db funsion  -top 1000  /tmp/slow_query.log > ./slow_query.pretty.log

# 分析最近12小時(shí)內(nèi)的查詢：pt-query-digest  --since=12h  slow.log > slow_report2.log

pt-query-digest [OPTIONS] [FILES] [DSN]    --create-review-table  當(dāng)使用--review參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒(méi)有表就自動(dòng)創(chuàng)建。    --create-history-table  當(dāng)使用--history參數(shù)把分析結(jié)果輸出到表中時(shí)，如果沒(méi)有表就自動(dòng)創(chuàng)建。    --filter  對(duì)輸入的慢查詢按指定的字符串進(jìn)行匹配過(guò)濾后再進(jìn)行分析    --limit    限制輸出結(jié)果百分比或數(shù)量，默認(rèn)值是20,即將最慢的20條語(yǔ)句輸出，如果是50%則按總響應(yīng)時(shí)間占比從大到小排序，輸出到總和達(dá)到50%位置截止。    --host  mysql服務(wù)器地址    --user  mysql用戶名    --password  mysql用戶密碼    --history 將分析結(jié)果保存到表中，分析結(jié)果比較詳細(xì)，下次再使用--history時(shí)，如果存在相同的語(yǔ)句，且查詢所在的時(shí)間區(qū)間和歷史表中的不同，則會(huì)記錄到數(shù)據(jù)表中，可以通過(guò)查詢同一CHECKSUM來(lái)比較某類型查詢的歷史變化。    --review 將分析結(jié)果保存到表中，這個(gè)分析只是對(duì)查詢條件進(jìn)行參數(shù)化，一個(gè)類型的查詢一條記錄，比較簡(jiǎn)單。當(dāng)下次使用--review時(shí)，如果存在相同的語(yǔ)句分析，就不會(huì)記錄到數(shù)據(jù)表中。    --output 分析結(jié)果輸出類型，值可以是report(標(biāo)準(zhǔn)分析報(bào)告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于閱讀。    --since 從什么時(shí)間開始分析，值為字符串，可以是指定的某個(gè)”yyyy-mm-dd [hh:mm:ss]”格式的時(shí)間點(diǎn)，也可以是簡(jiǎn)單的一個(gè)時(shí)間值：s(秒)、h(小時(shí))、m(分鐘)、d(天)，如12h就表示從12小時(shí)前開始統(tǒng)計(jì)。    --until 截止時(shí)間，配合—since可以分析一段時(shí)間內(nèi)的慢查詢。

mysql> explain select * from user_info where id = 2\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: user_info   partitions: NULL         type: constpossible_keys: PRIMARY          key: PRIMARY      key_len: 8          ref: const         rows: 1     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.06 sec)

各列的含義如下:
id: SELECT 查詢的標(biāo)識(shí)符. 每個(gè) SELECT 都會(huì)自動(dòng)分配一個(gè)唯一的標(biāo)識(shí)符.select_type: SELECT 查詢的類型.SIMPLE, 表示此查詢不包含 UNION 查詢或子查詢PRIMARY, 表示此查詢是最外層的查詢UNION, 表示此查詢是 UNION 的第二或隨后的查詢DEPENDENT UNION, UNION 中的第二個(gè)或后面的查詢語(yǔ)句, 取決于外面的查詢UNION RESULT, UNION 的結(jié)果SUBQUERY, 子查詢中的第一個(gè) SELECTDEPENDENT SUBQUERY: 子查詢中的第一個(gè) SELECT, 取決于外面的查詢. 即子查詢依賴于外層查詢的結(jié)果.table: 查詢的是哪個(gè)表partitions: 匹配的分區(qū)type: join 類型type字段比較重要, 它提供了判斷查詢是否高效的重要依據(jù)依據(jù). 通過(guò)type字段, 我們判斷此次查詢是 全表掃描 還是 索引掃描 等.system: 表中只有一條數(shù)據(jù). 這個(gè)類型是特殊的 const 類型.const: 針對(duì)主鍵或唯一索引的等值查詢掃描, 最多只返回一行數(shù)據(jù). const 查詢速度非常快, 因?yàn)樗鼉H僅讀取一次即可.eq_ref: 此類型通常出現(xiàn)在多表的 join 查詢, 表示對(duì)于前表的每一個(gè)結(jié)果, 都只能匹配到后表的一行結(jié)果. 并且查詢的比較操作通常是 =, 查詢效率較高.ref: 此類型通常出現(xiàn)在多表的 join 查詢, 針對(duì)于非唯一或非主鍵索引, 或者是使用了 最左前綴 規(guī)則索引的查詢.range: 表示使用索引范圍查詢, 通過(guò)索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個(gè)類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過(guò) ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數(shù)據(jù).ALL: 表示全表掃描, 這個(gè)類型的查詢是性能最差的查詢之一. 通常來(lái)說(shuō), 我們的查詢不應(yīng)該出現(xiàn) ALL 類型的查詢, 因?yàn)檫@樣的查詢?cè)跀?shù)據(jù)量大的情況下, 對(duì)數(shù)據(jù)庫(kù)的性能是巨大的災(zāi)難.possible_keys: 此次查詢中可能選用的索引表示 MySQL 在查詢時(shí), 能夠使用到的索引. 注意, 即使有些索引在 possible_keys 中出現(xiàn), 但是并不表示此索引會(huì)真正地被 MySQL 使用到. MySQL 在查詢時(shí)具體使用了哪些索引, 由 key 字段決定.key: 此字段是 MySQL 在當(dāng)前查詢時(shí)所真正使用到的索引.key_len: 表示查詢優(yōu)化器使用了索引的字節(jié)數(shù). 這個(gè)字段可以評(píng)估組合索引是否完全被使用, 或只有最左部分字段被使用到.ref: 哪個(gè)字段或常數(shù)與 key 一起被使用rows: 顯示此查詢一共掃描了多少行. 這個(gè)是一個(gè)估計(jì)值.rows 也是一個(gè)重要的字段. MySQL 查詢優(yōu)化器根據(jù)統(tǒng)計(jì)信息, 估算 SQL 要查找到結(jié)果集需要掃描讀取的數(shù)據(jù)行數(shù), 這個(gè)值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好.filtered: 表示此查詢條件所過(guò)濾的數(shù)據(jù)的百分比extra: EXplain 中的很多額外的信息會(huì)在 Extra 字段顯示Using filesort: 當(dāng) Extra 中有 Using filesort 時(shí), 表示 MySQL 需額外的排序操作, 不能通過(guò)索引順序達(dá)到排序效果. 一般有 Using filesort, 都建議優(yōu)化去掉, 因?yàn)檫@樣的查詢 CPU 資源消耗大.Using index: “覆蓋索引掃描”, 表示查詢?cè)谒饕龢渲芯涂刹檎宜钄?shù)據(jù), 不用掃描表數(shù)據(jù)文件, 往往說(shuō)明性能不錯(cuò)Using temporary: 查詢有使用臨時(shí)表, 一般出現(xiàn)于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優(yōu)化.type 類型的性能比較通常來(lái)說(shuō), 不同的 type 類型的性能關(guān)系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < systemALL 類型因?yàn)槭侨頀呙? 因此在相同的查詢條件下, 它是速度最慢的.而 index 類型的查詢雖然不是全表掃描, 但是它掃描了所有的索引, 因此比 ALL 類型的稍快.后面的幾種類型都是利用了索引來(lái)查詢數(shù)據(jù), 因此可以過(guò)濾部分或大部分?jǐn)?shù)據(jù), 因此查詢效率就比較高了.

看看當(dāng)前的MySQL版本是否支持: show variables like 'profiling';或show variables like 'profiling%';

mysql> show variables like 'profiling%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| profiling              | OFF   || profiling_history_size | 15    |+------------------------+-------+

mysql> set profiling = 1;