1. 前言

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

2. 優(yōu)化方式

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

數(shù)據(jù)庫層面的優(yōu)化

1. 表的結構是否正確？三范式
2. 是否有正確的索引來提高查詢效率？索引
3. 是否為每個表使用了適當?shù)拇鎯σ?？存儲引?/li>
4. 每個表是否使用適當?shù)男懈袷?？字段壓縮方式
5. 是否使用了適當?shù)逆i策略？事務的隔離級別
6. 用于緩存的所有內(nèi)存區(qū)域的大小是否正確？buffpool

硬件層面的優(yōu)化

1. 硬盤
2. cpu
3. 內(nèi)存寬帶

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

3. 慢日志查詢

知道了優(yōu)化點，那我們?yōu)槭裁匆獌?yōu)化呢？肯定是執(zhí)行時間太慢，并發(fā)能力上不去。所以，我們需不需要優(yōu)化就需看我們執(zhí)行的時間是否滿足我們的需求。那我們怎么知道執(zhí)行時間是否滿足我們的需要呢？這個就要看我們的慢日志了。

慢日志參數(shù)：

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

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

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

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語句優(yōu)化

Explain執(zhí)行計劃

建立索引建立在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。
  這個我們需要優(yōu)化到 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 不是索引，沒有對應的索引樹一般數(shù)據(jù)量大的情況下是需要優(yōu)化的

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

Using filesort: 排序沒有走到索引

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

Using index condition 索引條件下推

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

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

Using where 掃描出來的數(shù)據(jù)需要進行where匹配

order by優(yōu)化

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

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

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

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

count優(yōu)化

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

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

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

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

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

Limit優(yōu)化

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

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

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

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

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

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

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

分庫分表

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

5. 總結

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