1. 前言

今天，小編給大家分享點干貨，工作中都能用到的東西。是什么呢?那就是Mysql的性能優化，我們一起來看看吧。

2. 優化方式

好的，現在我們先來看看有哪些優化方式，然后再這些優化方式當中，我們程序員需要掌握其中的哪些優化方式。

數據庫層面的優化

1. 表的結構是否正確？三范式
2. 是否有正確的索引來提高查詢效率？索引
3. 是否為每個表使用了適當的存儲引擎？存儲引擎
4. 每個表是否使用適當的行格式？字段壓縮方式
5. 是否使用了適當的鎖策略？事務的隔離級別
6. 用于緩存的所有內存區域的大小是否正確？buffpool

硬件層面的優化

1. 硬盤
2. cpu
3. 內存寬帶

上面這些優化方式，是mysql官網里面有的。針對這些優化點，對于我們程序員來說，是不是只需要關注數據庫層面的優化。數據庫層面的優化中我們是不是只需要著重關注索引的優化，所以今天小編會分享一些索引方面的優化點。

3. 慢日志查詢

知道了優化點，那我們為什么要優化呢？肯定是執行時間太慢，并發能力上不去。所以，我們需不需要優化就需看我們執行的時間是否滿足我們的需求。那我們怎么知道執行時間是否滿足我們的需要呢？這個就要看我們的慢日志了。

慢日志參數：

• long_query_time: 超過多少秒進入慢查

SELECT @@long_query_time;--默認是10單位S
SET GLOBAL long_query_time=1;--設置超過1s就算慢查

• min_examined_row_limit: 檢索查詢的數量的行如果低于這個值，不進入慢查。

SELECT @@min_examined_row_limit;--默認是0

• log_output: 慢日志保存方式

SELECT @@1og_output;--慢查存在哪里
SET GLOBAL log_output='table,file'; -- table:表 file:文件

如果是file，那么保存的文件路徑為slow_query_log_file。

SELECT @aslow_query_log_file; -- 查詢慢日志存放路徑
SET GLOBAL slow_query_log_file=''; -- 設置慢日志存放路徑

如果是table，則保存在mysql.slow_log表中。

• slow_query_log:  慢日志開關

SELECT @@slow_query_log; -- 查詢慢日志開關
SET GLOBAL slow_query_log=1; -- 開啟慢查

4. sql語句優化

Explain執行計劃

建立索引建立在where、orderby、groupby的字段上面，提升查詢性能；但是就算加了也不一定能走到索引，所以要學會Explain分析。

Explain輸出字段

這些字段里面，我們只關注里面幾個就行了。

• type列:  這一列顯示了訪問類型，即MySQL決定如何查找表中的行。
  依次從好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。
  這個我們需要優化到 range 級別。

EXPLAIN select * FROM product WHERE id=1; -- const 只有一個匹配行并且id是主鍵
EXPLAIN select product_price FROM product WHERE product_price=55 -- ref 查詢非唯一索引或主鍵的索引值
EXPLAIN select * FROM product WHERE id in(1,2); -- range 范圍掃描
EXPLAIN SELECT * FROM product INNER JOIN product_price on product_new.id=product.product_id; -- eq_ref 組合查詢中，用到了主鍵或者唯一索引
EXPLAIN SELECT product_price FROM product ORDER BY product_price; -- index類型 掃描索引樹 比all相對來講要快
EXPLAIN SELECT product_tag FROM product ORDER BY product_tag; -- all 不是索引，沒有對應的索引樹一般數據量大的情況下是需要優化的

• possible_keys: 可以選擇的索引查詢，如果為null則沒有索引可以供選擇。
• key: 真正使用的索引
• rows: 執行查詢必須掃描的行數，對于InnoDB來講，這個是個預估值，不是非常準確，但是行數越少,性能肯定越好。
• Extra

Using filesort: 排序沒有走到索引

Using index 在索引樹中能遍歷到想要的數據（覆蓋索引）

Using index condition 索引條件下推

Using index for group-by group by分組基于索引檢索

Using temporary 是否使用臨時表，一般在 group by與order by場景

Using where 掃描出來的數據需要進行where匹配

order by優化

如果讓orderby的字段走索引，那么排序流程直接可以在索引樹完成，如果排序的字段不走索引，整個排序流程必須先把數據放到內存，在內存實現排序。

怎么判斷是否orderby用到了索引？

如果輸出Extra的列 EXPLAIN 不包含 Using filesort，則使用了索引

如果輸出Extra列 EXPLAIN 包含 Using filesort，則沒有使用索引

count優化

count()是一個聚合函數，對于返回的結果集 的一個統計，一行一行去判斷，如果count括號里的不是null，那么計值+1，否則不加，最后返回一個累計的總數。

count(*)：* 是整條數據，也進行了優化，因為整條數據肯定不會為null。所以也不需要去判斷

count(1)：1 是掃描到數據 掃描到了就固定返回一個1，肯定不為null，不會做null判斷。

count(id)：id 主鍵id，肯定不為null，也不會去判斷null,但是相對于count(1)來講，要去解析ID。稍微慢點，但是也可以忽略不計。

count(字段)：這個就有影響了，因為掃描行出來，需要判斷字段是否為空。

Limit優化

limit m，n ; 掃描m+n條數據，然后過濾掉前面的m條數據，當m越大，那么需要掃描的數據也就越多，性能也會越來越慢。

EXPLAIN SELECT * FROM product LIMIT 100000,10 --很慢很慢

針對這種情況，有以下幾種方案可以進行一定的優化。

• 如果id是趨勢遞增的，那么每次查詢都可以返回這次查詢最大的ID，然后下次查詢，加上大于 上次最大id的條件，這樣會通過主鍵索引去掃描，并且掃描數量會少很多很多。因為只需要掃描where條件的數據

SELECT * FROM product WHERE id > 100000 ORDER BY id LIMIT 10 -- 根據id查詢，并且使用where過濾

• 先limit出來主鍵ID，然后用主表跟查詢出來的ID進行inner join 內連接，這樣，也能一定上提速，因為減少了回表，查詢ID只需要走聚集索引就行。

SELECT * FROM product INNER JOIN(
 SELECT id FROM product ORDER BY id LIMIT 100000,10
) a
ON product_new.id=a.id

分庫分表

如果sql語句用到了索引，但是查詢還是很慢，那么看看數據庫表中的數據是否過多或者并發是不是很高。如果并發很高，那么我們可以考慮分庫，比如order和product，把業務細化。如果表數據過多，那就需要分表，當然我們也可以引入第三方組件解決數據過多的問題，比如Elasticsearch。

5. 總結

對于上面的優化，基本上都是基于索引的。基于索引的 SQL 優化旨在通過合理設計和使用索引來提升查詢性能。索引可以加速數據檢索，減少全表掃描，特別是在處理大量數據時。優化策略包括選擇合適的索引類型（如單列索引、復合索引）、避免冗余索引、使用覆蓋索引來避免訪問表數據、并通過 EXPLAIN 分析查詢執行計劃來確保索引的有效使用。合理設計索引不僅能加速查詢，還能減少數據庫負擔，提高整體性能。