給公司開發培訓的MySql數據庫優化方案

這是給公司培訓的資料，主要是講解數據庫可以優化的點，然后主要說明跟開發相關數據表設計、索引添加、數據庫參數調優。

MySql數據庫優化方案

優化角度

數據庫設計

操作語句

基礎設施

數據庫架構

一、數據庫設計優化

根據業務需求選擇正確的存儲引擎，如果對事務性要求高就用InnoDB（默認），讀多寫少盡量使用MyISAM。

設置合理的字段類型及字段長度，比如說你這個字段就20多個字段你設置成VARCHAR(255)就是對磁盤空間的浪費。

默認值盡可能的使用 NOT NULL，可以通過設置默認值避免NULL值。

盡量少的使用VARCHAR，TEXT，BLOB這三個字段。

添加適當索引(index) [普通索引、主鍵索引、唯一索引unique、全文索引]。

不要濫用索引，大表索引，小表不索引。

表的設計合理化（符合3NF）。

二、SQL語句優化

優化利器 – EXPLAIN

EXPLAIN SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'iacl_cmp' UNION SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'iacl_cmp' UNION SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'iacl_cmp';

運行結果

主要關注4個指標：

type：優化目標至少達到range級別，要求是ref級別，如果可以const最好。

type 性能從高到低

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

key：是查詢使用到的索引，如果此列為空，要么未建立索引，要么索引失效。

rows：是這條SQL語句掃描的行數，越少越好。

extra：此列為擴展列，如果出現臨時表、文件排序則需要優化。

where、= 條件避免類型轉換

入參類型在傳入前轉換成跟字段類型一致

內關聯避免也要避免類型不一致做過濾

避免使用 select *

like引起索引失效，盡量滿足最左匹配原則，建議這樣寫：

where display_name like '開始%';

不要在where條件中帶有函數計算、類型轉換

關于in和exist，如果查詢的倆個表大小一致則性能差別可忽略，如果子查詢表大用exist，否則使用in

預知查詢一條數據要加上limit 1，以便查到數據終止掃描

關聯查詢盡量不要超過 3 個表關聯

數據字段更新頻繁、重復度高不適合建索引

explain中的type至少要達到range，建議為ref

盡量用join代替子查詢

盡量用union all代替union

數據優化常用命令

查看Mysql服務器的鏈接

show full processlist;

查詢慢查詢次數

show status like 'slow_queries';

查看慢查詢設置的時間 ，默認10s

show variables like 'long_query_time';

設置慢查詢時間、

set long_query_time = 1;

查看建表語句

show create table wf_node;

查看表的字段

desc wf_node;

查看表索引

show indexes from wf_node;

創建索引

主鍵索引

alter table wf_node add primary key(id);

唯一索引

alter table wf_node add UNIQUE (gmt_create);

普通索引

alter table wf_node add index idx_display_name (display_name);

復合索引

ALTER TABLE wf_node ADD INDEX idx_display_name_gmt_create (display_name, gmt_create);

刪除索引

drop index idx_display_name on wf_node;

重建索引

OPTIMIZE TABLE wf_node;

三、基礎設施優化

硬件優化

CPU：配置多核心和頻率高的cpu,多核心可以執行多個線程

內存：配置大內存,提高內存,即可提高緩存區容量,因此能減少磁盤I/O時間,從而提高響應速度.

磁盤：配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

數據庫本身參數配置

key_buffer_size：索引緩沖區大小

table_open_cache：指定表高速緩存的大小

query_cache_size和query_cache_type：前者是查詢緩沖區大小,后者是前面參數的開關,0表示不使用緩沖區,1表示使用緩沖區,但可以在查詢中使用SQL_NO_CACHE表示不要使用緩沖區,2表示在查詢中明確指出使用緩沖區才用緩沖區,即SQL_CACHE

sort_buffer_size：排序緩沖區

四、數據庫架構優化

增加緩存

給數據庫增加緩存系統，把熱數據緩存到內存中，如果緩存中有請求的數據就不再去請求MySQL，減少數據庫負載。

主從復制與讀寫分離

在生產環境中，業務系統通常讀多寫少，可部署一主多從架構，主數據庫負責寫操作，并做雙機熱備，多臺從數據庫做負載均衡，負責讀操作

分表

分表分為垂直拆分和水平拆分：

垂直拆分：把原來的一個很多字段的表拆分多個表，解決表的寬度問題。你可以把不常用的字段單獨放到一個表中，也可以把大字段獨立放一個表中，或者把關聯密切的字段放一個表中。

水平拆分：把原來一個表拆分成多個表，每個表的結構都一樣，解決單表數據量大的問題。

分區

分區就是把一張表的數據根據表結構中的字段（如range、list、hash等）分成多個區塊，這些區塊可以在一個磁盤上，也可以在不同的磁盤上，分區后，表面上還是一張表，但數據散列在多個位置，這樣一來，多塊硬盤同時處理不同的請求，從而提高磁盤I/O讀寫性能。

MySQL 云學院 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。