當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql性能调优精简版

發布時間：2023/12/10 数据库 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql性能调优精简版小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

大家好，我是烤鴨：
?? ?這是根據官方文檔提煉出的mysql性能優化總結。

? ? 想看完整翻譯版的請看??https://blog.csdn.net/Angry_Mills/article/details/87720396

1. 成本優化

?? ?成本包含： IO 和 CPU 從硬盤讀取的花費
?? ?模型包含：全表掃描（IO成本：表中的pages * IO阻塞讀取成本 ?CPU成本：行 * 行計算成本）和范圍索引掃描（IO成本：范圍中的行 * IO阻塞讀取成本 IO成本：范圍中的行 * 行計算成本）

2. 利用工具監視sql

?? ?MySQL Enterprise Monitor (MEM), Query Analyzer
?? ?Performance schema 執行計劃
?? ??? ?events_statements_history,events_statements_history_long
?? ??? ??? ?大部分最近執行的statement
? ? ? ??events_statements_summary_by_digest
?? ??? ??? ?總結相似操作（相同的statement合并）
?? ??? ?file_summary_by_event_name
?? ??? ??? ?Interesting event: wait/io/file/innodb/innodb_data_file
?? ??? ?table_io_waits_summary_by_table
?? ??? ?table_io_waits_summary_by_index_usage
? ? ? ? ?統計存儲引擎的每個表和索引

EXPLAIN/EXPLAIN FORMAT=JSON

? ?調試查詢計劃：

SELECT trace FROM information_schema.optimizer_trace INTO OUTFILE <filename> LINES TERMINATED BY ''; SET optimizer_trace="enabled=off";

3.數據訪問和索引優化

?? ?使用索引/添加索引/復合索引/索引合并
?? ??? ?注意低選擇性的索引!(字段長度或者變量比較少,比如sex int 1)

4.連接優化

?? ?找到最好的連接順序
?? ??? ?使用 STRAIGHT_JOIN
?? ?優化連接順序
?? ??? ?對非索引列進行過濾
?? ??? ?部分表強制提前處理無需暗示
?? ??? ?將派生表合并到外部查詢中
?? ??? ?無臨時表

5.子查詢優化

?? ?IN 轉換為 EXISTS
?? ?例子：

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM actor WHERE name=“Bullock”) => SELECT title FROM filmWHERE EXISTS (SELECT 1 FROM actor WHERE name=“Bullock” AND film.film_id = actor.film_id)

? ? ?優勢：子查詢將計算更少的記錄

?? ? Semi-join 半連接
?? ? ?? ?將子查詢轉換為內連接,但需要一些方法去重
? ? ? ? ? ? ??匹配優先（等價于IN—>EXISTS的方式）
? ? ? ? ? ? ??懶掃描（索引掃描，跳過重復）
? ? ? ? ? ? ??實體化??MatLookup(像子查詢實體化)，MatScan（實體化表在連接順序的第一位）
? ? ? ? ? ? ??去重（用唯一索引將半連接的行插入臨時表；重復列將會被拒絕。無論連接順序）
?? ??? ?優勢：有更多優化"連接順序"的選擇
?? ??? ?限制：如果子查詢包含union（并集）或者aggregation（聚合）不能使用半連接

?? ?子查詢實例化：
?? ??? ?執行一次子查詢并在臨時表中存結果,表有唯一索引可以快速查找并去重
?? ??? ?執行外部查詢并檢查臨時表中的匹配項

?? ?派生表：
? ? 例子：

SELECT AVG(o_totalprice) FROM ( SELECT * FROM orders ORDER BY o_totalprice DESC LIMIT 100000 ) td;?

? ? ? ? mysql 5.6 以前 : 單獨執行并將結果存儲在臨時表中（實現）
?? ??? ?mysql 5.7 處理類似于視圖的派生表：可以與外部查詢塊合并

?? ??? ?注意：用外部連接合并派生表,基于GROUP BY，DISTINCT，LIMIT或聚合函數的派生表將不會合并

?? ?外部查詢與派生表MERGE
?? ??? ?NO_MERGE: 可用于覆蓋默認行為??

SELECT /*+ NO_MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;

? ? ? MERGE: 強制合并? ? ? ? ? ??

SELECT /*+ MERGE(dt) */ * FROM t1 JOIN (SELECT x, y FROM t2) dt ON t1.x = dt.x;

? ? ?也可以使用MERGE / NO_MERGE暗示查看視圖??

SELECT /*+ NO_MERGE(v) */ * FROM t1 JOIN v ON t1.x = v.x;

?

6.排序

?? ?通常的解決方案；"FileSort"
?? ??? ?在排序之前將查詢結果存儲在臨時表中
?? ??? ?如果數據量很大，可能需要在磁盤上使用中間存儲進行多次傳遞排序
?? ?優化
?? ??? ?利用索引按排序順序生成查詢結果
?? ??? ?對于"LIMIT n查詢，保留內存中n個頂級項的優先級隊列而不是文件排序。

?? ?執行計劃
?? ??? ?可從執行計劃中對每個語句進行排序
?? ?優化方式
?? ??? ?增加排序緩沖區 SET sort_buffer_size = 8*1024*1024;
?? ??? ?使用索引避免排序
?? ??? ?重新研究案例

7.影響的優化器

?? ?增加索引
?? ??? ?強制使用特定索引使用索引，強制索引，忽略索引
?? ?強制特定的關聯順序
?? ??? ?STRAIGHT_JOIN?? ?
?? ?調整會話變量?

set optimizer_switch="index_merge=off";set sort_buffer=8*1024*1024;set optimizer_search_depth = 10;

?? ?mysql 5.7 優化
?? ??? ?(BKA) 批量key訪問?? ?(BNL) 阻塞嵌套循環算法
?? ??? ?(Multi-Range Read ) 多范圍讀取 (表的索引)
?? ??? ?連接和子查詢（策略）
?? ??? ?命中索引的情況
?? ??? ?范圍優化（索引）?
?? ??? ?阻塞查詢

?? ?未來 mysql 8.0 語法可能的優化：
?? ??? ?啟用/禁用視圖和派生表的合并
?? ??? ?MERGE() NO_MERGE()?
?? ??? ?連接順序

?? ??? ?考慮添加的暗示：
?? ??? ??? ?強制/忽略index_merge替代方案
?? ??? ??? ?重新實現新語法中的索引暗示
?? ??? ??? ?暫時為一個查詢設置會話變量

?? ?mysql 5.7 重寫查詢插件
?? ??? ?無需更改應用程序即可重寫有問題的查詢
?? ??? ??? ?更新連接順序... 更多
?? ??? ??? ??? ?向表中添加重寫規則
?? ??? ? 可調成本常量(不建議使用)

總結

以上是生活随笔為你收集整理的mysql性能调优精简版的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。