柚子快報激活碼778899分享：數(shù)據(jù)庫 MySQL之性能調(diào)優(yōu)

http://yzkb.51969.com/

想要發(fā)揮 MySQL 的最佳性能，需要遵循 3 個基本使用原則。

讓MySQL回歸存儲的基本職能：MySQL 數(shù)據(jù)庫只用于數(shù)據(jù)的存儲，不進行數(shù)據(jù)的復雜計算，不承載業(yè)務邏輯，確保存儲和計算分離； 查詢數(shù)據(jù)時，盡量單表查詢，減少跨庫查詢和多表關(guān)聯(lián)； 杜絕大事務、大 SQL、大批量、大字段等一系列性能殺手。

大事務：運行步驟較多，涉及的表和字段較多，容易造成資源的爭搶，甚至形成死鎖。一旦事務回滾，會導致資源占用時間過長。 大 SQL：復雜的SQL意味著過多的表的關(guān)聯(lián)，MySQL 數(shù)據(jù)庫處理關(guān)聯(lián)超過3張表以上的SQL時，占用資源多，性能低下。 大批量：多條SQL一次性執(zhí)行完成，可以減少一條條執(zhí)行SQL產(chǎn)生的額外開銷，但必須確保進行充分的測試，并且在業(yè)務低峰時段或者非業(yè)務時段執(zhí)行。 大字段：blob、text類型的大字段要盡量少用，必須要用時，盡量與主業(yè)務表分離，減少對這類字段的檢索和更新。

1.使用Explain進行分析：

Explain 用來分析 SELECT 查詢語句，開發(fā)人員可以通過分析 Explain 結(jié)果來優(yōu)化查詢語句。

比較重要的字段有：

select_type：查詢類型（簡單查詢、聯(lián)合查詢、子查詢） key:使用的索引 rows：掃描的行數(shù)

2.優(yōu)化數(shù)據(jù)訪問：

1.減少請求的數(shù)據(jù)量：

只返回必要的列：最好不要使用SELECT*語句。 只返回必要的行：使用LIMIT語句來限制返回的數(shù)據(jù)。 緩存重復查詢的數(shù)據(jù)：使用緩存可以避免在數(shù)據(jù)庫中進行重復查詢。

2.減少服務器端掃描的行數(shù)：使用索引覆蓋來查詢。

3.重構(gòu)查詢方式：

1.切分大查詢：

一個大查詢?nèi)绻淮涡詧?zhí)行的話，可能一次鎖住很多數(shù)據(jù)、占滿整個事務日志、耗盡系統(tǒng)資源、阻塞很多小的但重要的查詢。

#大查詢

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

?

#切分塊

rows_affected = 0

do {

rows_affected = do_query(

"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")

} while rows_affected > 0

2.分解大連接查詢：將一個大連接查詢分解成對每一個表進行一次單表查詢，然后將結(jié)果在應用程序中進行關(guān)聯(lián)，這樣做的好處有:

讓緩存更高效。對于連接查詢，如果其中一個表發(fā)生變化，那么整個查詢緩存就無法使用。而分解后的多個查詢，即使其中一個表發(fā)生變化，對其它表的查詢緩存依然可以使用。 分解成多個單表查詢，這些單表查詢的緩存結(jié)果更可能被其它查詢使用到，從而減少冗余記錄的查詢。 減少鎖競爭； 在應用層進行連接，可以更容易對數(shù)據(jù)庫進行拆分，從而更容易做到高性能和可伸縮。 查詢本身效率也可能會有所提升。例如下面的例子中，使用 IN() 代替連接查詢，可以讓 MySQL 按照 ID 順序進行查詢，這可能比隨機的連接要更高效。

#大連接查詢

SELECT * FROM tab

JOIN tag_post ON tag_post.tag_id=tag.id

JOIN post ON tag_post.post_id=post.id

WHERE tag.tag='mysql';

?

#分解后

SELECT * FROM tag WHERE tag='mysql';

SELECT * FROM tag_post WHERE tag_id=1234;

SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

4.為搜索字段建立索引：

索引并不一定就是給主鍵或唯一的字段。如果在表中，有某個字段經(jīng)常用來搜索，就需要加入索引。

5.盡可能使用Enum而不是varchar：

節(jié)省存儲空間:Enum字段只需要占用1或2個字節(jié)的存儲空間,而Varchar字段需要根據(jù)實際數(shù)據(jù)長度來分配存儲空間。對于固定長度的數(shù)據(jù),Enum更加節(jié)省存儲。 提高查詢效率:Enum字段的值是預定義的,查詢時只需要比較整數(shù)值,效率更高。Varchar需要字符串比較,相對來說查詢速度會慢一些。 數(shù)據(jù)校驗:Enum字段的值受限于預定義的集合,可以有效避免輸入無效數(shù)據(jù)。Varchar沒有這種內(nèi)置的校驗機制。 索引優(yōu)化:Enum字段的索引更小、更緊湊,可以提高索引的性能。Varchar索引由于存儲的是字符串,相對更占用存儲空間。

6.硬件資源優(yōu)化

根據(jù)業(yè)務負載合理配置CPU、內(nèi)存、磁盤等硬件資源。 使用SSD等更快的存儲介質(zhì)可以大幅提高I/O性能。

7.配置參數(shù)優(yōu)化

根據(jù)硬件環(huán)境調(diào)整MySQL的配置參數(shù),如緩存大小、并發(fā)線程數(shù)等。 可以使用性能調(diào)優(yōu)工具如mysqltuner、pt-query-digest等進行參數(shù)分析和優(yōu)化。

8.讀寫分離

將讀操作和寫操作分離到不同的數(shù)據(jù)庫實例,可以提高整體的吞吐量。 可以使用MySQL主從復制實現(xiàn)讀寫分離。

參考博文：

Python-100-Days/Day91-100/93.MySQL性能優(yōu)化.md at master · jackfrued/Python-100-Days · GitHubMySQL - 性能優(yōu)化 | Java 全棧知識體系 ?

柚子快報激活碼778899分享：數(shù)據(jù)庫 MySQL之性能調(diào)優(yōu)

http://yzkb.51969.com/

文章鏈接