一、硬體層的最佳化

1 CPU:

個數/核數/頻率/執行緒數/一級cache/二級cache

2 記憶體:

容量與64-bits/頻寬

3 I/O:

seek(>100次/秒)/read/write(>10–20MB/s)

4網路:

頻寬/傳輸協議

二、儲存引擎最佳化

1 InnoDB：

1) innodb_buffer_pool_size ：caching data and indexes in memory，可配置為可用記憶體的（非實體記憶體）的60%--80%

2) innodb_buffer_pool_instances ：配合innodb_buffer_pool_size使用，把buf·分割槽，增加併發度

2 MyISAM：

1) key_buffer_size：Index blocks for MyISAM tables are buffered and are shared by all threads，可配置為可用記憶體的10-%-20%

2) table_open_cache：The number of open tables for all threads. 使用“SHOW GLOBAL STATUS LIKE 'Opened_tables';” 命令檢查“Opened_tables”的值，太小則改大。

磁碟類資料庫，瓶頸在IO；

必須優先調整和IO有關的引數。

解決IO瓶頸的方法，就是快取；

必須優先調整和快取相關的引數。

三、表設計最佳化

1 表的儲存引擎選擇：事務型選InnoDB，非事務型選MyISAM等

2 表的壓縮選擇：壓縮的表IO少，CPU空閒IO瓶頸大課採取壓縮

3 表結構符合第三正規化：更新類應用可讓表的個數多些單表的列少一些；分析類的應用可讓

表個數少些單表的列多些

4 表資料物理分佈策略：儘量讓表的資料分散在不同的物理儲存裝置上。利用表空間技術把

資料分散

5 表資料邏輯分佈策略：利用分割槽技術把資料從邏輯上即分開

6 表的資料型別選擇：數字型別優於字元型別；長度小的優於長度大的。變長的VARCHAR

優於定長的CHAR。BLOB型別用BINARY VARCHAR替代，替代不了則用單獨的表存放。

如果比較BLOB類，則新建欄位其值等於用MD5() 處理後的結果。BOLB型別甚至不存放到

資料庫內部，資料庫只儲存BLOB的路徑。

7 啟用完整性約束：使用NOT NULL標識欄位; 設定default value。

8 其他：列名不超過18個字元。使用sample character set（如用latin1，儘量少用utf-8

等，因為utf-8等佔用的空間是latin1的3倍）

四、InnoDB最佳化

1 單表容量最佳化：OPTIMIZE TABLE statement to reorganize the table and

compact any wasted space。

2 單表統計資料最佳化： ANALYZE TABLE tpch.customer;

3 啟用壓縮：測試各級壓縮哪種有效

4 應用盡量使用短事務減少使用長事務：應用程式控制

5 事務管理：

5.1 寫操作多：SET AUTOCOMMIT=0 or a START TRANSACTION statement,

followed by a COMMIT statement after making all the changes.

5.2 讀操作多：SET AUTOCOMMIT=1

6 加大日誌檔案和日誌快取：innodb_log_buffer_size + innodb_log_file_size

7 主鍵建立：使用最重要且是最常用的列作主鍵，務必建立主鍵而不是使用 InnoDB預設方式

8 主鍵列的資料型別不要太長：浪費儲存空間和記憶體，浪費其他索引的空間和記憶體

9 有效建立索引：除主鍵外，儘量建立聯合索引而不是多個單列上分別建立 secondary index

10 刪除資料釋放空間：Use TRUNCATE TABLE to empty a table, not DELETE FROM tbl_name.

11 資料刷出的方式：In some versions of GNU/Linux and Unix, flushing files to

disk with the Unix fsync() call (which InnoDB uses by default) and similar methods

is surprisingly slow. If database write performance is an issue, conduct

benchmarks with the innodb_flush_method parameter set to O_DSYNC.

其他刷出方式參考：optimization.html#optimizing-innodb-storage-layout

五、庫最佳化

1 同一個庫中表不要太多：設定table_open_cache 和 max_connections來調整。

2 啟用查詢快取： 適用於特定場景.

If you often have recurring queries for tables that are not updated frequently,

enable the query cache:

[mysqld]

query_cache_type = 1

query_cache_size = 10M

3 使用長連線： 避免頻繁使用短連線.

設定thread_cache_size ： 8 + (max_connections / 100) <--default value

4 主從架構： 複製技術，master完成寫操作，slave完成讀操作.

1) 最佳化讀寫操作

2) 提高備份速度，減少對master的影響

六、資料獲取方式的最佳化

1 一次獲取的資料儘量少：查詢獲取資料,儘量帶WHERE條件精確指定獲取範

圍,且一次獲取的資料量要少（應用層開發階段必須注意）

資料獲取，遵循的基本原則：

---少：不要全表掃描。要用什麼取什麼

---準：帶where條件獲取，用誰取誰

---快：加索引到where條件涉及的列上，用誰則快速取到誰

---減少關聯：沒有直接聯絡，不要硬拉郎配。減少耦合，減少關聯。

2 不用select *： 臭名昭著，遠遠避之。

獲取什麼列就明確指定列名。

查詢語句中出現的列名，最好是索引的一部分。

3 LIMIT x：在滿足應用需求的情況下可限制元組個數

七、利用索引最佳化

l檢視索引的使用情況

show status like ‘Handler_read%’; 大家可以注意：

handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

handler_read_rnd_next:這個值越高，說明查詢低效。

1 正確使用索引：每條查詢，使用EXPLAIN驗證

2 索引列做條件不參與運算：index_col <op> expression

如: col IN (value1, value2,...), col是索引列，才可以利用索引加快資料

獲取速度. 運算子一側的物件必須是不參與運算的索引列.

3 精確化查詢條件：單表不做全部資料讀取，多表連線不做全表掃描，務必帶有WHERE子句限制資料，WHERE子句中列物件有合適的索引可用

4 等式/不等式的一些推理人工完成：

a>b AND b>3 ==> a>3 AND b>3 AND a>b

a列上有索引且選擇率低

5 求最值建索引：單表求MIN/MAX/COUNT(*)的操作，最好在對應列上建立索引

6 GROUPBY、ORDERBY、DISTINCT作用在索引列上：

6.1 利用索引進行GROUPBY、ORDERBY、DISTINCT 操作（考慮在這些謂詞

後面的物件上建立索引）

6.2 避免使用隨機數等不確定的物件做排序分組操作，如不用：ORDER BY RAND()

7 建立主外來鍵關係：儘管MySQL目前不支援帶有主外來鍵關係的表連線最佳化（好處：符合第三正規化，表明實體已經被拆分為小表，有利於減少讀取的資料量）

8 引入新列：在表上增加新列，並在其上建立索引

SELECT * FROM tbl_name

WHERE hash_col=MD5(CONCAT(col1,col2))

AND col1='constant' AND col2='constant';

BLOB的比較，也可以使用同樣的方式。

9 存在範圍查詢，建立Btree索引：預設情況下是Btree。

10 索引型別：儘量使用主鍵索引/唯一索引。

11 建立索引: 索引的鍵值型別越短越好。在數值型列上建立索引最好。

12 少建索引：對InnoDB，主鍵不可用時，儘量用聯合索引替換多個單列的second index。

13 刪除索引：刪除使用很少的索引。

14 只讀索引：Covering Indexes，覆蓋索引。 查詢語句中出現的物件儘量限制在單個索引的全部列中。

15 字首索引：儘量使用索引的字首部分。

16 萬用字元：字元型索引列參與比較，另外各一個運算子前不用萬用字元。key LIKE ‘%abc’

17 強制索引：特定情況強制使用指定的索引

index_hint:

USE {INDEX|KEY}

[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

| IGNORE {INDEX|KEY}

[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

| FORCE {INDEX|KEY}

[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

18 索引誤區：

1) 不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進行全表掃描。

2) 索引不會包含有NULL值的列

複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

八、暫存中間結果的三種最佳化方式

1 利用物化檢視臨時儲存確定的查詢結果/統計結果：

create algorithm=temptable view my_view as

select * from t1, t2;

select a, b from my_view where ...;

2 或者把結果永久儲存到特定的表中:

create table my_result select * from t1, t2;

3 或者把結果暫時存在臨時表中:

sql_buffer_result：If set to 1, sql_buffer_result forces results from SELECT stat

ements to be put into temporary tables. This helps MySQL free the table locks earl

y and can be beneficial in cases where it takes a long time to send results to the client.

九、控制查詢最佳化器

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

batched_key_access={on|off}

block_nested_loop={on|off}

engine_condition_pushdown={on|off}

firstmatch={on|off}

index_condition_pushdown={on|off}

index_merge={on|off}

index_merge_intersection={on|off}

index_merge_sort_union={on|off}

index_merge_union={on|off}

loosescan={on|off}

materialization={on|off}

mrr={on|off}

mrr_cost_based={on|off}

semijoin={on|off}

subquery_materialization_cost_based={on|off}

use_index_extensions={on|off}

十、連線技術

1 避免子查詢：使用子查詢上拉技術，人工最佳化子查詢。

WHERE子句中的IN/ALL/ANY/SOME子查詢可以交給最佳化器最佳化

2 慎用不同的連線語義：慎用各種外連線、巢狀連線

3 明確連線條件:

ON子句指名連線列,儘量在主鍵和唯一鍵上做等值連線

WHERE子句儘量利用索引中的主鍵索引和唯一索引

4 控制表的連線個數:

7表連線,可交給最佳化器處理

7表以上的連線,考慮連線的必要性和連線條件

十一、其他

1 使用語義最佳化技術：熟悉表結構和應用需求，利用語義最佳化技術書寫查詢語句（設計階段就需要開始考慮）

2 使用hint: 強迫最佳化器變更行為（最佳化器不是萬能的，多數時候可信）

3 查詢語句中慎用函式：特別檢察查詢語句中是否使用了函式，尤其是值不穩定的函式（對於每行元組，值總在變化），儘量不用。

MySQL最佳化

相關文章