mysql 優化

1、mysql較慢的原因

1）慢SQL消耗了70%~90%的資料庫CPU資源；

2）SQL語句獨立于程式設計邏輯，相對于對程式源代碼的優化，對SQL語句的優化在時間成本和風險上的代價都很低；

3）SQL語句可以有不同的寫法；

2、比較常見的sql優化方法

1）不使用子查詢

例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

子查詢在MySQL5.5版本裡，内部執行計劃器是這樣執行的：先查外表再比對内表，而不是先查内表t2，當外表的資料很大時，查詢速度會非常慢。

在MariaDB10/MySQL5.6版本裡，采用join關聯方式對其進行了優化，這條SQL會自動轉換為

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

注意：優化隻針對SELECT有效，對UPDATE/DELETE子查詢無效，固生産環境應避免使用子查詢

2）避免函數索引

例：SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那樣支援函數索引，即使d字段有索引，也會直接全表掃描。

應改為----->

SELECT * FROM t WHERE d >= '2016-01-01';

3）用IN來替換OR

低效查詢

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

----->

高效查詢

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

4）

LIKE雙百分号無法使用到索引

SELECT * FROM t WHERE name LIKE '%de%';

SELECT * FROM t WHERE name LIKE 'de%';

目前隻有MySQL5.7支援全文索引（支援中文）

5）讀取适當的記錄LIMIT M,N

SELECT * FROM t WHERE 1;

SELECT * FROM t WHERE 1 LIMIT 10;

6）避免資料類型不一緻

SELECT * FROM t WHERE id = '19';

SELECT * FROM t WHERE id = 19;

7）分組統計可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

預設情況下，MySQL對所有GROUP BY col1，col2...的字段進行排序。如果查詢包括GROUP BY，想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

8）避免随機取記錄

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL不支援函數索引，會導緻全表掃描

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;  

9）禁止不必要的ORDER BY排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

10）批量INSERT插入

INSERT INTO t (id, name) VALUES(1,'Bea');

INSERT INTO t (id, name) VALUES(2,'Belle');

INSERT INTO t (id, name) VALUES(3,'Bernice');

INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');

3、mysql調優政策

1）硬體層相關優化

修改伺服器BIOS設定

選擇Performance Per Watt Optimized(DAPC)模式，發揮CPU最大性能。

Memory Frequency（記憶體頻率）選擇Maximum Performance（最佳性能）

記憶體設定菜單中，啟用Node Interleaving，避免NUMA問題

2）磁盤I/O相關

使用SSD硬碟

如果是磁盤陣列存儲，建議陣列卡同時配備CACHE及BBU子產品，可明顯提升IOPS。

raid級别盡量選擇raid10，而不是raid5.

3）檔案系統層優化

使用deadline/noop這兩種I/O排程器，千萬别用cfq

使用xfs檔案系統，千萬别用ext3；ext4勉強可用，但業務量很大的話，則一定要用xfs；

檔案系統mount參數中增加：noatime, nodiratime, nobarrier幾個選項（nobarrier是xfs檔案系統特有的）；

4）核心參數優化

修改vm.swappiness參數，降低swap使用率。RHEL7/centos7以上則慎重設定為0，可能發生OOM

調整vm.dirty_background_ratio、vm.dirty_ratio核心參數，以確定能持續将髒資料重新整理到磁盤，避免瞬間I/O寫。産生等待。

調整net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設定為1，減少TIME_WAIT，提高TCP效率。

5）MySQL參數優化建議

建議設定default-storage-engine=InnoDB，強烈建議不要再使用MyISAM引擎。

調整innodb_buffer_pool_size的大小，如果是單執行個體且絕大多數是InnoDB引擎表的話，可考慮設定為實體記憶體的50% -70%左右。

設定innodb_file_per_table = 1，使用獨立表空間。

調整innodb_data_file_path = ibdata1:1G:autoextend，不要用預設的10M,在高并發場景下，性能會有很大提升。

設定innodb_log_file_size=256M，設定innodb_log_files_in_group=2，基本可以滿足大多數應用場景。

調整max_connection（最大連接配接數）、max_connection_error（最大錯誤數）設定，根據業務量大小進行設定。

另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以設定大約為max_connection的10倍左右大小。

key_buffer_size建議調小，32M左右即可，另外建議關閉query cache。

mp_table_size和max_heap_table_size設定不要過大，另外sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等設定也不要過大。

本文轉自 sykmiao 51CTO部落格，原文連結：http://blog.51cto.com/syklinux/1759085，如需轉載請自行聯系原作者