作為一名軟件開發人員，尤其是服務端開發，數據庫已經是一個必備的技能了，MySQL也是這些年使用最廣泛的數據庫之一，小編平時也是開發工作中也是基于mysql數據庫的，經常跟mysql打交道。使用數據庫的人都會碰到性能問題，隨著業務的發展，對于數據庫的性能要求也會越來越高，那么平時遇到性能問題我們應該怎么優化，或者說怎么預防數據庫出現性能問題以及在出現性能問題時如何更快速的發現問題和解決問題，今天咱們一起來來看看關于Mysql的性能優化過程。

mysql

數據庫優化的目的

1、提高系統的穩定性

低效的sql查詢會導致系統經常處于卡頓甚至崩潰狀態

阻塞造成數據提交失敗

2、增強用戶體驗

系統訪問的流暢度

穩定且良好的系統功能體驗

數據庫優化的層級

1、sql語句及索引優化

2、數據庫表設計即數據表結構

3、數據庫系統配置

4、服務器硬件配置

這個數據庫優化層級，自上而下，成本越來越高，然而效果越來越不明顯，也就是說，sql語句及索引的優化成本最低產生的效果最明顯，也是開發人員使用最多的優化手段。接下來咱們就從上到下來看看怎么優化。

一、sql語句及索引的優化

1、mysql慢查詢日志及未使用索引查詢日志開啟

show variables like '%log%'; ——查看log相關的變量列表及狀態，忘記變量名可以查看

show variables like 'slow_query_log'; ——查看是否開啟sql慢查詢日志

set global slow_query_log=on/off; ——開啟或關閉sql慢查詢日志

show variables like 'log_queries_not_using_indexes'; ——查看是否開啟未使用索引查詢日志

set global log_queries_not_using_indexes=on/off; ——開啟未使用索引查詢日志

還要設置慢查詢的時間，查看變量long_query_time的值，show variables like 'long_query_time'; set global long_query_time=1; 單位s，如果設置成0的話，那所有查詢都會被記錄。

show variables like 'slow_query_log_file'; ——查看慢查詢日志位置，查到日志位置后就可以去查看記錄的慢查詢，如下圖：

配置sql慢查詢日志

2、慢查詢日志分析工具——mysqldumpslow

安裝了mysql，這個工具也會隨之安裝進來，所以可以直接使用，使用時可以mysqldumpslow -h查看命令參數。

mysqldumpslow工具

-s 按照那種方式排序

c：訪問計數
l：鎖定時間
r:返回記錄
a:查詢時間
al：平均鎖定時間
ar：平均訪問記錄數
at：平均查詢時間

-t 是top n的意思，返回多少條數據。
-g 可以跟上正則匹配模式，大小寫不敏感。

大家可以自行摸索一下，可以幫助大家更好的分析慢查詢的sql語句的問題出在哪里。

3、explain分析sql的執行計劃

explain分析執行計劃

select_type：查詢的類型（SIMPLE簡單查詢，PRIMARY最外層的查詢等）
table：數據表，顯示這行數據是哪張表
type：連接類型，從最好到最差(const，eq_ref，ref，range，index，all)，這個很重要
possible_keys：可能用到的索引，如果為null則無可用索引
key：實際使用到的索引，如果為null則沒有使用索引
key_len：使用到索引的長度，短點較好
ref：索引的哪一列被使用了，顯示常數最好
rows：用來返回請求結果需要遍歷到的行數，越少越好
extra：擴展列，其他信息use index表示覆蓋索引掃描，using filesort使用文件排序，需要優化，排序有問題，using temporary使用臨時表，也需要優化了，一般對不同列集合進行order by排序。

4、索引的優化

這個小節的內容在小編往期的文章中有詳細介紹，請移步往期文章《Mysql查詢優化器，再也不會因為該什么時候建立"索引"發愁了》https://www.toutiao.com/i6759772160329253379/

二、數據表結構的優化

1、選擇合適的數據類型，如下：

使用可以滿足使用條件的最小數據類型
使用簡單的數據類型，比如int比varchar在mysql上的處理上簡單，時間戳，IP地址等
定義字段時使用not null
盡量不要使用Text、MediumText等text數據類型，可以考慮分表存儲數據

2、表的垂直拆分

把一個很多列的表拆分為多個表，有效解決表寬度問題

不常用的字段單獨存放到一個表
經常用的字段單獨存放到一個表
把較大字段單獨存放到一個表，比如產品的描述信息不建議放在產品主表

三、系統配置優化

mysql服務器是基于操作系統的，因此操作系統的配置也會影響到mysql數據系統，下面列出一些比較常見的系統配置項。

系統

1、服務器網絡方向，修改/etc/sysctl.conf文件

net.ip4.tcp_max_syn_backlog=65535 ——增加tcp支持的隊列數
net.ip4.tcp_max_tw_buckets=10000
net.ip4.tcp_tw_reuse=1
net.ip4.tcp_tw_recycle=1
net.ip4.tcp_fin_timeout=10 ——上面這四項，斷開連接時減少資源回收

2、服務器打開文件限制數

可以使用ulimit -a查看目錄限制，修改/etc/security/limits.conf增加下面內容來修改打開文件數量的限制。

soft nofile 65535 ——單個用戶可用的最大進程數量(軟限制)
hard nofile 65535 ——單個用戶可用的最大進程數量(硬限制)

3、mysql配置

mysql配置文件一般位于/etc/my.conf或/etc/mysql.conf

/etc/mysql/my.conf

innodb_buffer_pool_size ——配置innodb的緩沖池，如果只有Innodb引擎的表，建議設置為總內存的75%左右，如果還有myIsam表，那可以減小一點。
innodb_buffer_pool_instances ——5.5后新增參數，設置緩沖池的個數，默認一個緩沖池
innodb_log_buffer_size ——innodb的log緩沖的大小，一遍設置小點即可
innodb_flush_log_at_trx_commit ——對innodb的I/O效率影響很大，默認1，可以設置【0,1,2】，建議設置2，如果對數據安全性要求很高設置默認1。
innodb_read_io_threads ——讀的I/O進程數，默認4，5.5可根據cpu核數及系統負載調整讀的并發數
innodb_write_io_threads ——寫的I/O進程數，默認4，5.5可根據cpu核數及系統負載調整寫的并發數
innodb_file_per_table ——控制每個表使用獨立的空間，默認off，建議設置on，增加并發的讀寫效率