1. 前言

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

2. 優(yōu)化方式

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

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

1. 表的結(jié)構(gòu)是否正確？三范式
2. 是否有正確的索引來(lái)提高查詢效率？索引
3. 是否為每個(gè)表使用了適當(dāng)?shù)拇鎯?chǔ)引擎？存儲(chǔ)引擎
4. 每個(gè)表是否使用適當(dāng)?shù)男懈袷剑孔侄螇嚎s方式
5. 是否使用了適當(dāng)?shù)逆i策略？事務(wù)的隔離級(jí)別
6. 用于緩存的所有內(nèi)存區(qū)域的大小是否正確？buffpool

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

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

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

3. 慢日志查詢

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

慢日志參數(shù)：

• long_query_time: 超過(guò)多少秒進(jìn)入慢查

SELECT @@long_query_time;--默認(rèn)是10單位S
SET GLOBAL long_query_time=1;--設(shè)置超過(guò)1s就算慢查

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

SELECT @@min_examined_row_limit;--默認(rèn)是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=''; -- 設(shè)置慢日志存放路徑

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

• slow_query_log:  慢日志開(kāi)關(guān)

SELECT @@slow_query_log; -- 查詢慢日志開(kāi)關(guān)
SET GLOBAL slow_query_log=1; -- 開(kāi)啟慢查

4. sql語(yǔ)句優(yōu)化

Explain執(zhí)行計(jì)劃

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

Explain輸出字段

這些字段里面，我們只關(guān)注里面幾個(gè)就行了。

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

EXPLAIN select * FROM product WHERE id=1; -- const 只有一個(gè)匹配行并且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類型 掃描索引樹(shù) 比all相對(duì)來(lái)講要快
EXPLAIN SELECT product_tag FROM product ORDER BY product_tag; -- all 不是索引，沒(méi)有對(duì)應(yīng)的索引樹(shù)一般數(shù)據(jù)量大的情況下是需要優(yōu)化的

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

Using filesort: 排序沒(méi)有走到索引

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

Using index condition 索引條件下推

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

Using temporary 是否使用臨時(shí)表，一般在 group by與order by場(chǎng)景

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

order by優(yōu)化

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

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

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

如果輸出Extra列 EXPLAIN 包含 Using filesort，則沒(méi)有使用索引

count優(yōu)化

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

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

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

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

count(字段)：這個(gè)就有影響了，因?yàn)閽呙栊谐鰜?lái)，需要判斷字段是否為空。

Limit優(yōu)化

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

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

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

• 如果id是趨勢(shì)遞增的，那么每次查詢都可以返回這次查詢最大的ID，然后下次查詢，加上大于 上次最大id的條件，這樣會(huì)通過(guò)主鍵索引去掃描，并且掃描數(shù)量會(huì)少很多很多。因?yàn)橹恍枰獟呙鑧here條件的數(shù)據(jù)

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

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

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

分庫(kù)分表

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

5. 總結(jié)

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