怎么獲取有功能問題的SQL

1、經過用戶反應獲取存在功能問題的SQL

2、經過慢查詢日志獲取功能問題的SQL

3、實時獲取存在功能問題的SQL

運用慢查詢日志獲取有功能問題的SQL

首要介紹下慢查詢相關的參數

1、slow_query_log 發動定制記載慢查詢日志

設置的辦法，能夠經過MySQL指令行設置set global slow_query_log=on

或許修正/etc/my.cnf文件，增加slow_query_log=on

2、slow_query_log_file 指定慢查詢日志的存儲途徑及文件

主張日志存儲和數據存儲分隔存儲

3、long_query_time 指定記載慢查詢日志SQL履行時刻的閾值

① 記載一切契合條件的SQL

② 數據修正句子

③ 包含查詢句子

④ 現已回滾的SQL

留意：

時刻能夠精確到微秒，存儲的單位是秒，默許值為10秒，例如咱們想查詢1微秒的值，這兒就要設置成0.001秒

4、log_queries_not_using_indexes 是否記載未運用索引的SQL

5、log_output 設置慢日志查詢的保存格局(假如需求保存為文件請修正成FILE)

慢查詢運用日志中記載的信息

1、榜首行記載的信息為運用sbtest做的測驗

2、第二行記載的信息為慢查詢日志的時刻

3、第三行記載的信息為所運用鎖的時刻

4、第四行記載的信息為回來的數據行數

5、第五行記載的信息為掃描數據的行數

6、第六行記載的信息為時刻戳

7、第七行記載的信息為查詢的SQL句子

運用慢查詢獲取有功能問題的SQL

常運用的慢查詢日志剖析東西(mysqldumpslow)

介紹：匯總除查詢條件外其他完全相同的SQL，并將剖析成果依照參數中所指定的次序輸出

慢查詢日志實例

慢查詢的相關裝備設置

指令行履行參數檢查剖析的成果

]# cd /var/lib/mysql/log]# mysqldumpslow -s r -t 10 slow-mysql

常運用的慢查詢日志剖析東西(pt-query-digest)

運用東西前，需求先裝置該東西，假如已有，可略過下面的裝置進程

1、perl模塊

]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5

2、切換至src目錄下載rpm包

]# cd /usr/local/src

]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm

3、裝置東西包

]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm

履行指令剖析慢查詢日志

]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep

剖析的成果如下

MySQL服務器處理查詢懇求的整個進程

1、客戶端發送SQL懇求給服務器

2、服務器檢查是否存在在緩存服務器中射中該SQL

3、服務器端進行SQL解析，預處理，再由優化器對應履行計劃

4、依據履行計劃，調用存儲引擎API來查詢數據

5、將成果回來給客戶端

查詢緩存對SQL功能的影響

1、優先檢查整個查詢是否射中查詢緩存中的數據

2、經過一個對大小寫靈敏的哈希查找完成的

查詢緩存的優化參數

query_cache_type 設置查詢緩存是否可用

ON，OFF，DEMAND

留意：DEMAND表明只要在查詢句子中運用SQL——CACHE和SQL_NO_CACHE來操控是否需求緩存

query_cache_size 設置查詢緩存的內存大小

query_cache_limit 設置查詢緩存可用存儲的最大值

query_cache_wlock_invalidate 設置數據表被鎖后是否回來緩存中的數據(默許是封閉的，主張也是封閉的此選項)

query_cache_min_res_unit 設置查詢緩存分配的內存塊最小的值

會形成MySQL生成過錯的履行計劃的原因

1、計算信息不精確

2、履行計劃中的本錢預算不等同于實踐的履行計劃的本錢

3、MySQL優化器所以為的最優可能與你所以為的最優不一樣

4、MySQL從不考慮其他并發的查詢，這可能會影響當時查詢數據

5、MySQL有時候也會依據一些固定的規矩來生成履行計劃

6、MySQL不會考慮不受其操控的本錢

MySQL優化器可優化的SQL類型

1、從頭界說表的相關次序

優化器會依據計算信息來決議表的相關次序

2、將外鏈接轉換成內銜接

where條件和庫表結構等

3、運用等價改換規矩

(5=5 and a > 5)將會被改寫成 a > 5

4、優化count(), min()和max()

select tables optimized away

優化器現已從履行計劃中移除了該表，并以一個常數取而代之

5、將一個表達式轉換為常數表達式

6、運用等價改換規矩

7、子查詢優化

8、對in()條件進行優化

怎么確認查詢處理各個階段所耗費的時刻

運用profile set profiling = 1;

履行查詢： showprofiles; show profile forqueryN; 查詢的每個階段所耗費的時刻

運用profile檢查句子所耗費的時刻

特定的SQL查詢優化

1、運用主從切換的原理進行大表的表結構修正，例如，現在從服務器上修正，修正結束今后，進行主從切換，再在本來老的主上進行大表的修正，存在必定的危險。2、在主服務器上創建于一個新的表，表結構便是即將修正大表后表結構，再把老表的數據從頭導入到新表中，并在老表中樹立一系列的觸發器，把老表的數據同步更新到新表中，當老表中的數據悉數同步到新表今后，再對老表加排它鎖，把新表改成老表的稱號，刪去重命名的老表，如下圖所示

運用pt-online-schema-change指令來修正大表，具體操作如下圖所示

上圖的參數解說

--alter 所運用的sql句子

--user 數據庫的登錄用戶

--password 登錄用戶的暗碼

D 指定一切修正表的數據庫稱號

t 表的稱號

--charset 指定數據庫的字符串

--excute 履行

作者：demon_gdy