一、 優(yōu)化思路
- 選擇合適的數(shù)據(jù)庫(kù)引擎：詳見第二點(diǎn)
- 配置優(yōu)化：見第三點(diǎn)
- Sql優(yōu)化：性能瓶頸定位、show status命令、慢查詢?nèi)罩?、explain分析查詢、profiling分析查詢
- 索引優(yōu)化：見第四點(diǎn)
- 優(yōu)化排序：見第五點(diǎn)
- 讀寫分離：見第六點(diǎn)
- 表結(jié)構(gòu)優(yōu)化：水平拆分、垂直拆分和逆規(guī)范化，見第七點(diǎn)
- 硬件升級(jí)：是用RAID10磁盤陣列，RAID10兼具RAID1的可靠性和RAID0的優(yōu)良并發(fā)讀寫性能
- 使用表分區(qū)： 跨多個(gè)磁盤來(lái)分散查詢，能獲得更大的吞吐量，需要一定的硬件條件
二、常見數(shù)據(jù)庫(kù)引擎對(duì)比
MyISMA是MySQL的默認(rèn)存儲(chǔ)引擎。MyISMA不支持事務(wù)，不支持外鍵，優(yōu)勢(shì)是訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本上都可以使用MyISMA引擎。比較適合Web、數(shù)據(jù)倉(cāng)儲(chǔ)等場(chǎng)景。
InnoDB存儲(chǔ)引擎提供具有提交、回滾和崩潰恢復(fù)的事務(wù)安全，支持外鍵。對(duì)數(shù)據(jù)一致性要求比較高或更新比較頻繁的的應(yīng)用可以選擇InnoDB。比較適合類似計(jì)費(fèi)和財(cái)務(wù)系統(tǒng)等準(zhǔn)確度要求比較高的系統(tǒng)。
MEMORY存儲(chǔ)引擎-內(nèi)存數(shù)據(jù)庫(kù)，服務(wù)重啟數(shù)據(jù)會(huì)丟失。適用于那些內(nèi)容變化不頻繁的代碼表（常量表），或者作為統(tǒng)計(jì)結(jié)果的中間結(jié)果表。修改的數(shù)據(jù)不會(huì)寫入磁盤。
MERGE存儲(chǔ)引擎是一組MyISMA表的組合，這些MyISMA表的結(jié)構(gòu)必須完全相同，MERGE表本身沒(méi)有數(shù)據(jù)，對(duì)MERGE表的操作實(shí)際上是對(duì)內(nèi)部的MyISMA表進(jìn)行的。較適合數(shù)據(jù)倉(cāng)儲(chǔ)。
-- 查看數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎
show engines;
三、數(shù)據(jù)庫(kù)配置
| 配置參數(shù) | 配置說(shuō)明 |
|---|---|
| innodb_buffer_pool_size | 這是你安裝完InnoDB后第一個(gè)應(yīng)該設(shè)置的選項(xiàng)。緩沖池是數(shù)據(jù)和索引緩存的地方：這個(gè)值越大越好，這能保證你在大多數(shù)的讀取操作時(shí)使用的是內(nèi)存而不是硬盤。典型的值是5-6GB(8GB內(nèi)存)，20-25GB(32GB內(nèi)存)，100-120GB(128GB內(nèi)存) |
| log_bin | 如果你想讓數(shù)據(jù)庫(kù)服務(wù)器充當(dāng)主節(jié)點(diǎn)的備份節(jié)點(diǎn)，那么開啟二進(jìn)制日志是必須的。如果這么做了之后，還別忘了設(shè)置server_id為一個(gè)唯一的值。就算只有一個(gè)服務(wù)器，如果你想做基于時(shí)間點(diǎn)的數(shù)據(jù)恢復(fù)，這（開啟二進(jìn)制日志）也是很有用的：從你最近的備份中恢復(fù)（全量備份），并應(yīng)用二進(jìn)制日志中的修改（增量備份）。二進(jìn)制日志一旦創(chuàng)建就將永久保存。所以如果你不想讓磁盤空間耗盡，你可以用 PURGE BINARY LOGS 來(lái)清除舊文件，或者設(shè)置 expire_logs_days 來(lái)指定過(guò)多少天日志將被自動(dòng)清除。配置log_bin時(shí)必須指定server-id，否則無(wú)法啟動(dòng) |
| innodb_log_file_size | 這是redo日志的大小。（前提是打開log_bin）redo日志被用于確保寫操作快速而可靠并且在崩潰時(shí)恢復(fù)。一直到MySQL 5.1，它都難于調(diào)整，因?yàn)橐环矫婺阆胱屗髞?lái)提高性能，另一方面你想讓它更小來(lái)使得崩潰后更快恢復(fù)。幸運(yùn)的是從MySQL 5.5之后，崩潰恢復(fù)的性能的到了很大提升，這樣你就可以同時(shí)擁有較高的寫入性能和崩潰恢復(fù)性能了。一直到MySQL 5.5，redo日志的總尺寸被限定在4GB(默認(rèn)可以有2個(gè)log文件)。這在MySQL 5.6里被提高。一開始就把innodb_log_file_size設(shè)置成512M(這樣有1GB的redo日志)會(huì)使你有充裕的寫操作空間。如果你知道你的應(yīng)用程序需要頻繁的寫入數(shù)據(jù)并且你使用的時(shí)MySQL 5.6，你可以一開始就把它這是成4G |
| max_connections | 如果你經(jīng)?？吹健甌oo many connections'錯(cuò)誤，是因?yàn)閙ax_connections的值太低了。這非常常見因?yàn)閼?yīng)用程序沒(méi)有正確的關(guān)閉數(shù)據(jù)庫(kù)連接，你需要比默認(rèn)的151連接數(shù)更大的值。max_connection值被設(shè)高了(例如1000或更高)之后一個(gè)主要缺陷是當(dāng)服務(wù)器運(yùn)行1000個(gè)或更高的活動(dòng)事務(wù)時(shí)會(huì)變的沒(méi)有響應(yīng)。在應(yīng)用程序里使用連接池或者在MySQL里使用進(jìn)程池有助于解決這一問(wèn)題 |
| skip_name_resolve | 當(dāng)客戶端連接數(shù)據(jù)庫(kù)服務(wù)器時(shí)，服務(wù)器會(huì)進(jìn)行主機(jī)名解析，并且當(dāng)DNS很慢時(shí)，建立連接也會(huì)很慢。因此建議在啟動(dòng)服務(wù)器時(shí)關(guān)閉skip_name_resolve選項(xiàng)而不進(jìn)行DNS查找。唯一的局限是之后GRANT語(yǔ)句中只能使用IP地址了，因此在添加這項(xiàng)設(shè)置到一個(gè)已有系統(tǒng)中必須格外小心。加上這個(gè)配置就可以不通過(guò)用戶直接連接數(shù)據(jù)庫(kù)了 |
MySql官方已經(jīng)決定在以后的版本中取消緩存技術(shù)，太雞肋，可以使用第三方緩存技術(shù)（ehcache或者redis等），所以緩存配置就不要看了
四、索引優(yōu)化
索引設(shè)計(jì)原則：
- 最適合索引的列是在where子句中的列，或連接子句中的列，而不是出現(xiàn)在select關(guān)鍵字后的列
- 使用唯一索引?？紤]某列中值的分布。索引列的基數(shù)越大，效果越好（一列中相同的數(shù)據(jù)越少，索引越好）
- 使用短索引。如果對(duì)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度。這樣可以節(jié)省索引空間和磁盤IO。（alter tableName add key indexName (columnName(7)) --給表tableName的columnName字段的前7位建立前綴做引，索引名字為indexName）
- 利用最左前綴。比如創(chuàng)建了一個(gè)多列索引 index_c1_c2_c3 (c1,c2,c3)，相當(dāng)于創(chuàng)建了(c1)單列索引，(c1,c2)的組合做引以及(c1,c2,c3)的組合索引。根據(jù)這個(gè)原則，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求 ，where子句中使用最頻繁的一列要放在索引的最左邊。
- 不要過(guò)度索引。索引過(guò)多，會(huì)導(dǎo)致磁盤占用較高，insert和update操作耗時(shí)增加，查詢優(yōu)化效率會(huì)變低。
以下不會(huì)使用索引的幾種情況：
- 以%開頭的like查詢不能使用索引
- 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的不能使用索引。數(shù)據(jù)INT類型，而用varchar查詢
- 復(fù)合索引的情況下，假如查詢條件不包含索引列最左邊部分，不使用索引
- 如果MySQL估計(jì)使用索引比全表掃描慢，不使用索引
- 用or分隔開的條件，如果or前的列中有索引，而后邊的列中沒(méi)有索引，不會(huì)使用索引。（or的所有條件必須全部使用索引字段才會(huì)走索引
五、排序優(yōu)化
MySql排序算法的執(zhí)行方式： 將取得的數(shù)據(jù)在sort_buffer_size系統(tǒng)變量設(shè)置的內(nèi)存排序區(qū)中進(jìn)行排序，如果內(nèi)存裝載不下，它就會(huì)將磁盤上的數(shù)據(jù)進(jìn)行分塊，再對(duì)各個(gè)塊進(jìn)行排序，然后將各個(gè)塊合并成有序的結(jié)果集。
優(yōu)化方案：
- 盡量減少額外的排序，通過(guò)索引直接返回有序數(shù)據(jù)
- 適當(dāng)加大max_length_for_sort_data系統(tǒng)變量，讓更多的SQL可以在內(nèi)存中完成排序，減少磁盤I/O操作。（因?yàn)榕判騾^(qū)是每個(gè)線程獨(dú)占的，設(shè)置過(guò)大會(huì)導(dǎo)致服務(wù)器SWAP嚴(yán)重）
- 盡量只使用必要的字段，select具體的字段名字，而不是select *，這樣可以減少排序區(qū)的使用，提高SQL性能
- MySQL會(huì)對(duì)GROUP BY后的所有字段排序，group by a1,a2,a3相當(dāng)于后邊默認(rèn)加了order by a1,a2,a3 ，如果要避免排序帶來(lái)的消耗，可以使用order by null禁止排序
六、讀寫分離配置
- 修改master配置文件：
log-bin=mysql-bin #slave會(huì)基于此log-bin來(lái)做replication
server-id=1 #master的標(biāo)示
binlog-do-db = amoeba_study #用于master-slave的具體數(shù)據(jù)庫(kù)
- 添加專門用于replication的用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO username@host IDENTIFIED BY 'password';
- 如果庫(kù)中已有數(shù)據(jù)，需要記下file和position
mysql> flush tables with read lock;
mysql> show master status; # 得到file和position
mysql> unlock tables;
- 編輯slave的配置文件，添加server-id
server-id=2 #slave的標(biāo)示,需要唯一
- 配置生效后，配置與master的連接：
mysql> CHANGE MASTER TO
-> MASTER_HOST='masterhost',
-> MASTER_USER='2中的username',
-> MASTER_PASSWORD='2中的password',
-> MASTER_LOG_FILE='3中的file',
-> MASTER_LOG_POS='3中的posiition';
mysql> start slave; # 啟動(dòng)從庫(kù)，開始同步數(shù)據(jù)
- 安裝amoeba，按照官方文檔進(jìn)行配置，這里就不詳細(xì)介紹了
- 配置服務(wù)端直接連接到amoeba即可
七、表結(jié)構(gòu)優(yōu)化
垂直拆分
把主鍵和一些常用的字段放到一個(gè)表中，把主鍵和其他的字段放到另一個(gè)表中。
優(yōu)點(diǎn)：垂直拆分可以使一個(gè)數(shù)據(jù)頁(yè)放更多的數(shù)據(jù)，可以較少IO次數(shù)。
缺點(diǎn)：查詢所需的數(shù)據(jù)可能需要通過(guò)JOIN來(lái)查詢。
適用場(chǎng)景：表過(guò)寬，包含text或blob字段，可以將不常用的列或text/blob列放到另外的表中存儲(chǔ)。比如文章表可以將文章內(nèi)容拆分到另外的表中。
水平拆分
根據(jù)某一列的值把數(shù)據(jù)放到多個(gè)獨(dú)立的表中，比如歷史數(shù)據(jù)放到另一張表里。
優(yōu)點(diǎn)：減少大多數(shù)查詢讀取的數(shù)據(jù)量，降低索引層數(shù)，提高查詢速度。
缺點(diǎn)：增加查詢復(fù)雜度，查詢多個(gè)表需要使用UNION，或者通過(guò)MERGE表。
適用場(chǎng)景：表中數(shù)據(jù)量過(guò)大，歷史數(shù)據(jù)查詢次數(shù)很少，比如訂單信息、操作記錄等。
逆規(guī)范化
增加冗余列：在多個(gè)表中具有相同的列，避免聯(lián)合查詢
增加派生列：增加的列來(lái)自其他表的計(jì)算結(jié)果，可避免使用函數(shù)
重新組表：將經(jīng)常聯(lián)合查詢的表組成一個(gè)表，減少聯(lián)合查詢
