共計 2757 個字符，預計需要花費 7 分鐘才能閱讀完成。
這篇文章主要為大家展示了“MySQL 中 SQL 語句分析與查詢優化的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領大家一起研究并學習一下“MySQL 中 SQL 語句分析與查詢優化的示例分析”這篇文章吧。
如何獲取有性能問題的 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;
執行查詢：
show profiles;
show profile for query N;
查詢的每個階段所消耗的時間
使用 profile 查看語句所消耗的時間
特定的 SQL 查詢優化
1、利用主從切換的原理進行大表的表結構修改，例如，現在從服務器上修改，修改完畢以后，進行主從切換，再在原來老的主上進行大表的修改，存在一定的風險。
2、在主服務器上創建于一個新的表，表結構就是將要修改大表后表結構，再把老表的數據重新導入到新表中，并在老表中建立一系列的觸發器，把老表的數據同步更新到新表中，當老表中的數據全部同步到新表以后，再對老表加排它鎖，把新表改成老表的名稱，刪除重命名的老表，如下圖所示
使用 pt-online-schema-change 命令來修改大表，具體操作如下圖所示
上圖的參數解釋
–alter 所使用的 sql 語句
–user 數據庫的登錄用戶
–password 登錄用戶的密碼
D 指定所有修改表的數據庫名稱
t 表的名稱
–charset 指定數據庫的字符串
–excute 執行
以上是“MySQL 中 SQL 語句分析與查詢優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
