MySql數據庫優化常用設置

單個文件的大小直接影響數據的寫入及讀取，我們可以給每個數據表設置一個獨立的文件存儲

innodb_file_per_table=ON

開啟數據庫本地的查詢緩存機制，我們可以設置緩存數據的大小，數據庫會根據查詢參數自動變更緩存的內容。查看是否開啟緩存命令

show VARIABLES like '%query_cache%';

如果query_cache_type為OFF，說明緩存沒有開啟，哪我們需要設置如下

query_cache_type=1 query_cache_limit=2M query_cache_size=256M

query_cache_type 設置為1表示開啟緩存

query_cache_limit 每次查詢能使用的緩存大小

query_cache_size 緩存大小

開啟MySql慢日志記錄，這個建議針對開發、測試、模擬環境設置，生產環境建議關閉以便提高性能

查看慢查詢是否開啟

show VARIABLES like '%slow%';

slow_query_log 如果為ON則為慢日志開啟

slow_query_log_file 日志文件路徑開

啟慢日志

slow_query_log=on #判斷標準超過3秒記錄 long_query_time=3 slow_query_log_file=/cmp/mysql-wsrep/logs/slow.log

long_query_time 慢查詢閾值，單位秒，只要查詢超過這個時間就會記錄到日志

數據庫事務隔離級別決定鎖的范圍大小，直接影響到數據庫的性能，下面是常見數據庫隔離級別

隔離級別從上到下依次提高，但性能依次降低，所以，我們要根據具體的業務選擇合理的數據庫隔離級別，達到既滿足業務又提高性能的效果。

常見的數據庫比如：oracle、sqlserver 默認使用 RC，Mysql默認RR級別

沒有特殊要求，我們可以把Mysql的隔離級別設置為RC

transaction-isolation=READ-COMMITTED

設置合適的數據表引擎，我們在開發中常用的數據庫引擎為innodb，很少使用myisam，其實在不需要事務的需求下，我們可以把數據庫引擎或者數據表引擎設置為myisam

ALTER TABLE access_log ENGINE = MyISAM; OPTIMIZE TABLE access_log;

OPTIMIZE TABLE 命令可以實現空間。碎片清理

其它常用設置如下，基本上都加了注釋，可以根據需要合理設置

[client] port = 3306 # 設置mysql客戶端連接服務端時默認使用的端口 socket = /mysql/mysql.sock default-character-set = utf8 # 默認字符編碼為utf8 [mysqld] open_files_limit = 65535 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION port = 3306 # mysql服務端默認監聽的TCP/IP端口 socket = /cmp/mysql/mysql.sock pid-file = /cmp/mysql/mysql.pid basedir = /cmp/mysql # 基準路徑，其他路徑都相對于這個路徑 datadir = /cmp/data/mysql/data # mysql數據庫文件所在目錄 tmpdir = /cmp/data/mysql/data # mysql數據庫臨時文件目錄 # explicit_defaults_for_timestamp = true innodb_flush_method = O_DIRECT # 直接寫入磁盤，禁止系統Cache innodb_io_capacity = 800 # 將其設置為磁盤子系統可以處理的寫入iops數。 character-set-server = utf8 # 服務端默認使用的字符集 key_buffer_size = 1000M # 鍵高速緩存 max_allowed_packet = 128M # 更新插入server接受的數據包大小 table_open_cache = 2000 # 表高速緩存的大小 sort_buffer_size = 2M read_buffer_size = 2M # MySql讀入緩沖區大小。 read_rnd_buffer_size = 8M # MySql的隨機讀（查詢操作）緩沖區大小。 myisam_sort_buffer_size = 64M # MyISAM表發生變化時重新排序所需的緩沖 thread_cache_size = 2000 # 服務器線程緩存這個值表示可以重新利用保存在緩存中線程的數量。 query_cache_type = 0 # 只要query_cache_type沒有關閉，sql查詢總是會使用查詢緩存，如果緩存沒有命中則開始查詢的執行計劃到表中查詢數據 max_heap_table_size = 128M # 這個變量定義了用戶可以創建的內存表(memory table)的大小.這個值用來計算內存表的最大行數值。 bulk_insert_buffer_size = 64M # 批量插入數據緩存大小，可以有效提高插入效率，默認為8M myisam_sort_buffer_size = 128M # MyISAM表發生變化時重新排序所需的緩沖 myisam_max_sort_file_size = 20G # MySQL重建索引時所允許的最大臨時文件的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).如果文件大小比此值更大,索引會通過鍵值緩沖創建(更慢) myisam_repair_threads = 1 # 如果一個表擁有超過一個索引 myisam_recover-options # connect max_connections = 2000 # MySQL允許最大的進程連接數，如果經常出現Too Many Connections的錯誤提示，則需要增大此值 max_connect_errors = 999999 # 設置每個主機的連接請求異常中斷的最大次數，當超過該次數，MYSQL服務器將禁止host的連接請求，直到mysql服務器重啟或通過flush hosts命令清空此host的相關信息 slow_launch_time = 1 # 捕獲所有執行時間超過1秒的查詢 skip-name-resolve # 禁用dns解析,加快mysql登錄過程,但是,這樣不能在mysql的授權表中使用主機名了,只能使用IP # log log-error = /cmp/data/mysql/log/error.log general_log_file = /cmp/data/mysql/log/general_log.log # 有的查詢語句都可以在general log文件中以可讀的方式得到 binlog_format = row log_bin = /cmp/data/mysql/binlog/mysql-bin.log relay_log = /cmp/data/mysql/binlog/mysql-relay-bin.log max_binlog_size = 100M # 每份binlog日志大小,如果日志達到達到這個大小時,mysql會創建一份新的binlog日志 log_slave_updates # 主從復制重要參數,防止數據不同步 expire_logs_days = 3 # 二進制日志自動刪除/過期的天數.默認值為0,表示“沒有自動刪除” slow_query_log_file = /cmp/data/mysql/log/slow.log # 慢查詢日志 slow_query_log = on long_query_time = 1 server-id = 1 replicate_ignore_db = mysql # 主從同步的環境中，replicate-ignore-db用來設置不需要同步的庫 replicate_wild_ignore_table = mysql.% # 主從同步中,過濾的規則 sync_binlog = 1 binlog_checksum = none binlog_format = mixed # innodb innodb_data_home_dir = /cmp/data/mysql/data # 這是InnoDB表的目錄共用設置。如果沒有在 my.cnf 進行設置，InnoDB 將使用MySQL的 datadir 目錄為缺省目錄。如果設定一個空字串,可以在 innodb_data_file_path 中設定絕對路徑。 innodb_data_file_path = ibdata1:100M:autoextend # 指定 InnoDB 只建立一個最初大小為 100 MB 并且當表空間被用盡時以 8MB 每塊增加的數據文件 innodb_log_group_home_dir = /cmp/data/mysql # InnoDB 日志文件的路徑。必須與 innodb_log_arch_dir 設置相同值。 如果沒有明確指定將默認在 MySQL 的 datadir 目錄下建立兩個 5 MB 大小的 ib_logfile... 文件 innodb_buffer_pool_size = 2G # InnoDB 用來高速緩沖數據和索引內存緩沖大小。 更大的設置可以使訪問數據時減少磁盤 I/O。在一個專用的數據庫服務器上可以將它設置為物理內存的 80 %。 不要將它設置太大，因為物理內存的使用競爭可能會影響操作系統的頁面調用 innodb_log_file_size = 256M # 日志組中的每個日志文件的大小(單位 MB)。如果 n 是日志組中日志文件的數目，那么理想的數值為 1M 至下面設置的緩沖池(buffer pool)大小的 1/n。較大的值，可以減少刷新緩沖池的次數，從而減少磁盤 I/O。但是大的日志文件意味著在崩潰時需要更長的時間來恢復數據。 日志文件總和必須小于 2 GB，3.23.55 和 4.0.9 以上為小于 4 GB innodb_log_buffer_size = 64M # InnoDB 將日志寫入日志磁盤文件前的緩沖大小。理想值為 1M 至 8M。大的日志緩沖允許事務運行時不需要將日志保存入磁盤而只到事務被提交(commit)。 因此，如果有大的事務處理，設置大的日志緩沖可以減少磁盤I/O innodb_flush_log_at_trx_commit = 0 # 通常設置為 1，意味著在事務提交前日志已被寫入磁盤， 事務可以運行更長以及服務崩潰后的修復能力。如果你愿意減弱這個安全，或你運行的是比較小的事務處理，可以將它設置為 0 ，以減少寫日志文件的磁盤 I/O。這個選項默認設置為 0 innodb_log_files_in_group = 4 # 日志組中的日志文件數目。InnoDB 以環型方式(circular fashion)寫入文件。數值 3 被推薦使用 innodb_max_dirty_pages_pct = 90 # 用來控制在 InnoDB Buffer Pool 中可以不用寫入數據文件中的Dirty Page 的比例(已經被修但還沒有從內存中寫入到數據文件的臟數據)。這個比例值越大，從內存到磁盤的寫入操作就會相對減少，所以能夠一定程度下減少寫入操作的磁盤IO innodb_open_files = 2000 # 限制Innodb能打開的表的數據,默認值為300 innodb_doublewrite = 1 # 為了解決 partial page write 問題 ，當mysql將臟數據flush到data file的時候, 先使用memcopy 將臟數據復制到內存中的double write buffer ，之后通過double write buffer再分2次，每次寫入1MB到共享表空間，然后馬上調用fsync函數，同步到磁盤上，避免緩沖帶來的問題，在這個過程中，doublewrite是順序寫，開銷并不大，在完成doublewrite寫入后，在將double write buffer寫入各表空間文件，這時是離散寫入。如果發生了極端情況（斷電），InnoDB再次啟動后，發現了一個Page數據已經損壞，那么此時就可以從doublewrite buffer中進行數據恢復了 innodb_file_per_table # 修改InnoDB為獨立表空間模式，每個數據庫的每個表都會生成一個數據空間,開啟方法innodb_file_per_table= 1,查詢方法show variables like '%per_table%'; innodb_read_io_threads = 16 # 配置io線程數量 innodb_write_io_threads = 32 # 配置io線程數量 innodb_thread_concurrency = 16 # 限制并發線程的數量，一旦執行線程的數量達到這個限制，額外的線程在被放置到對隊列中之前，會睡眠數微秒，可以通過設定參數innodb_thread_sleep_delay來配置睡眠時間,innodb_thread_concurrency的默認值為0，它表示默認情況下不限制線程并發執行的數量 innodb_purge_threads = 1 # 開啟獨立的碎片回收進程 [mysqldump] quick # 加快mysql命令行下導出數據 max_allowed_packet = 16M # 服務器發送和接受的最大包長度 [mysql] auto-rehash # 自動補全命令 [myisamchk] key_buffer_size = 256M # 鍵高速緩存 sort_buffer_size = 256M # 一次性分配的內存大小 read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout

喜歡的朋友，別忘個給個關注

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。