優化MySQL哪些地方需要注意

本文主要給大家簡單講講優化MySQL哪些地方需要注意，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望優化MySQL哪些地方需要注意這篇文章可以給大家帶來一些實際幫助。

1、控制適當的數據文件大小：

  數據量越大，數據文件的大小也會越大，索引文件也就越大，從而影響性能，會使性能急劇下降，所以，要限制數據庫單表的數據量和數據文件大小；

2、碎片空洞注意：

 往往我們在刪除表數據的時候，明明已經刪除可是在底層只是被標記為刪除，實際上并沒有釋放空間，所以要定期查看表的碎片，

show  table  status  ---發現表的碎片情況（查看實際數據data_length和空洞數據data_free的比例如果，data_free非常大，則說明該表需要優化了）

優化的方法是：optimize  table  表名；   或者  alter  table  表名 engine=innodb來重建表空間；

3、行存儲格式注意：

  在MySQL5.7.9之后，innodb表的行格式由之前默認的compact變成了dynamic，二者的區別可以查看官方文檔，但是，compact格式會節約20%的空間，同時在存儲UTF8或UTF8MB4數據的時候，compact格式在存儲時會盡量的節約空間，不對其中的空格進行存儲；

4、正確使用索引：

  索引雖然能提升查詢性能，但是會降低MySQL寫數據的速度，同時，也會增大數據文件的大小，加的索引越多，寫入數據越慢，數據文件也就越大。所以在設計索引的時候，會要求主鍵的字段類型一定要用數字類型，并且要盡可能的小，能用INT的，絕對不要用bigint。在設計二級索引的時候，只添加需要的索引，避免添加重復索引和冗余索引，針對長字符串字段，盡量添加前綴索引；

---如何正確使用索引：

①：MySQL在使用索引時，采用的是最左匹配原則，如果是多列索引：idx_a_b_c(a,b,c)，則可以發揮索引功能組合有：a, (a,b)  (a,b,c)

②：MySQL在計算列里無法使用索引

③：MySQL在否定條件中不能使用索引

④：MySQL在join中連接字段類型如果不一致，則不能使用索引

*********************************************************************************

在MySQL中一些比較常用的系統參數：

1、general_log：

  建議在數據庫正常服務時，將該參數關閉，因為他會記錄提交到MySQL的一切東西，即浪費磁盤又影響效率。同時它也是分析問題的利器，可以在數據庫有異常的時候，把它打開，然后截取一段時間的日志，以幫助定位問題；

2、query_cache_size：

  是用來緩存sql語句文本和對應查詢結果的緩存空間。如果表沒有變化，再次查詢會直接返回結果，這是很高效的；如果表變化非常頻繁，則要不斷更新cache中的內容，并且這個時候所粒度非常大，反而會成為瓶頸。所以很多情況下會關閉這個選項，將參數設置0

3、tmp_table_size：

  在group by 或 distinct 的時候，如果sql語句用不到索引，就會使用系統內部臨時表記錄中間狀態。如果tmp_table_size不夠大，則MySQL會自動使用物理磁盤，這會對查詢性能造成很大影響，增加此參數可以降低這種情況發生的概率；（注意：這是占用物理內存的，要考慮實際的內存空閑情況）

4、innodb_buffer_pool_size：

  innodb最只要的緩存，用來緩存innodb索引頁面，undo頁面及其他一些輔助數據，建議配置物理內存的50%--75%

5、innodb_buffer_pool_instances：

  這個參數，把原來一整塊buffer pool分割為多塊內存空間，每個空間獨立管理自己的空閑鏈表、刷新鏈表、LRU及其他數據結構。這大大增加了并發性，能更有效利用緩存；

6、innodb_log_file_size和innodb_log_files_in_group

   這兩個參數結合，決定了redo空間的大小，redo空間越大，可以存儲的增量更新日志越大，有效降低buffer pool臟頁面被淘汰的速度，同時減少了checkpoint的次數，降低磁盤IO置換率，從而提升數據庫的寫入效率。不過也有可能導致數據庫異常退出時，恢復時間被拉長；

7、innodb_old_blocks_pct和innodb_old_blocks_time

  這兩個參數控制buffer pool中緩存數據的過期和移動行為，二者結合設置，可以優化一些全表掃描帶來的大規模更新buffer等問題；

8、innodb_numa_interleave：

  該參數避免出現內存被交換到SWAP分區，影響性能

9、innodb_autoinc_lock_mode：

  在innodb有自增列的情況下，在插入數據的時候，會自動產生自增值，這個參數是控制自增值生成的方式。目前有3個選項：0 1  2 ；使用2即in特人leave的，這樣在insert數據的時候不會用到標記的auto-inc鎖，避免了auto-inc的死鎖問題，在insert...select場景下會極大提升性能，在做普通insert的時候，也會提升并發執行的效率；

 ---注意：這個時候產生的自增值不是連續的，同時binlog格式需要設置為ROW，才能保證數據的安全性和一致性；

10、innodb_flush_method

  innodb刷新數據和日志到磁盤文件的方式，默認為NULL，但其實如果是Unix系統上，默認是fsync；在使用SSD或PCIE類型的存儲時，可以設置為O_DIRECT, 該參數會提升性能；

11、innodb_doublewrite

  如果底層存儲時支持原子寫的，則可以關閉兩次寫，以提升效率；

12、innodb_io_capacity

   如果使用SSD設備的時候，可以適當提高這個參數值，以加速flush的頻率；

13、innodb_thread_concurrency

  在并發量大的實列上，增加這個值，可以降低innodb在并發線程之間切換的花銷，以增加系統的并發吞吐量；

14、innodb_flush_log_at_trx_commit

  該參數的值0 1  2；0性能最好，但不安全； 1 最安全，但性能最差，如果使用的磁盤足夠好，還是建議使用1  ，否則根據實際情況選擇性的設置2

15、sync_binlog

  MySQL同步binlog到磁盤的方式， 1 最安全，但性能最差； 0性能最好，但最不安全。建議設置1

16、binlog_format

  MySQL的日志格式，在MySQL5.7.7以后，官方就默認為ROW了，這也是最建議的設置

17、binlog_order_commits

  事務在提交的時候寫入binlog的順序，這是雙刃劍，如果打開，可以保證事務都以相同的順序寫入二進制文件，如果關閉則可以提升性能。根據實際情況決定吧

18、tx_isolation

  設置MySQL的隔離級別，推薦設置read committed，這在保證性能的前提下，同時設置binlog_format=ROW，確保通過binlog同步數據主從庫的一致性，兼顧安全，滿足絕大多數業務的需求； 

19、slave_parallel_workers

  在進行多線程復制的時候，如果設置此參數為非零值，則可以打開多線程并發執行回放日志的操作，以提升slave的同步性能；

優化MySQL哪些地方需要注意就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。