當MySQL單表記錄數(shù)過大時，增刪改查性能都會急劇下降，所以我們本文會提供一些優(yōu)化參考，大家可以參考以下步驟來優(yōu)化：

1、字段

盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類型而非INT，如果非負則加上UNSIGNED；

VARCHAR的長度只分配真正需要的空間；

使用枚舉或整數(shù)代替字符串類型；

盡量使用TIMESTAMP而非DATETIME；

單表不要有太多字段，建議在20以內(nèi)；

避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間；

用整型來存IP。

2、索引

索引并不是越多越好，要根據(jù)查詢有針對性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描；

應盡量避免在WHERE子句中對字段進行NULL值判斷，否則將導致引擎放棄使用索引而進行全表掃描；

值分布很稀少的字段不適合建索引，例如“性別”這種只有兩三個值的字段；

字符字段只建前綴索引；

字符字段最好不要做主鍵；

不用外鍵，由程序保證約束；

盡量不用UNIQUE，由程序保證約束；

使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引。

3、查詢SQL

可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL；

不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導致表掃描，它包括數(shù)據(jù)庫教程函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊；

sql語句盡可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫；

不用SELECT *；

OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別，IN的個數(shù)建議控制在200以內(nèi)；

不用函數(shù)和觸發(fā)器，在應用程序?qū)崿F(xiàn)；

避免%xxx式查詢；

少用JOIN；

使用同類型進行比較，比如用'123'和'123'比，123和123比；

盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描；

對于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；

列表數(shù)據(jù)不要拿全表，要使用LIMIT來分頁，每頁數(shù)量也不要太大。

4、引擎

目前廣泛使用的是MyISAM和InnoDB兩種引擎：

MyISAM

MyISAM引擎是MySQL 5.1及之前版本的默認引擎，它的特點是：

不支持行鎖，讀取時對需要讀到的所有表加鎖，寫入時則對表加排它鎖；

不支持事務；

不支持外鍵；

不支持崩潰后的安全恢復；

在表有讀取查詢的同時，支持往表中插入新紀錄；

支持BLOB和TEXT的前500個字符索引，支持全文索引；

支持延遲更新索引，極大提升寫入性能；

對于不會進行修改的表，支持壓縮表，極大減少磁盤空間占用。

InnoDB

InnoDB在MySQL 5.5后成為默認索引，它的特點是：

支持行鎖，采用MVCC來支持高并發(fā)；

支持事務；

支持外鍵；

支持崩潰后的安全恢復；

不支持全文索引。

總體來講，MyISAM適合SELECT密集型的表，而InnoDB適合INSERT和UPDATE密集型的表。

5、系統(tǒng)調(diào)優(yōu)參數(shù)

可以使用下面幾個工具來做基準測試：

• sysbench：一個模塊化，跨平臺以及多線程的性能測試工具。https://github.com/akopytov/sysbench
• iibench-mysql：基于Java的MySQL / Percona / MariaDB 索引進行插入性能測試工具。https://github.com/tmcallaghan/iibench-mysql
• tpcc-mysql：Percona開發(fā)的TPC-C測試工具。https://github.com/Percona-Lab/tpcc-mysql

重要聲明：本文來自SQL數(shù)據(jù)庫開發(fā)，經(jīng)授權(quán)轉(zhuǎn)載，版權(quán)歸原作者所有，不代表銳成觀點，轉(zhuǎn)載的目的在于傳遞更多知識和信息。