mysql參數優化建議

版權聲明：歡迎轉載，請注明沉默王二原創。 https://blog.csdn.net/qing_gee/article/details/42580913

要想提高mysql的性能，首先就是必須對mysql的配置參數進行了解，在了解了mysql的配置參數後，根據自己的項目需要以及運作環境，再做出相應的調整，那麼以下這些參數是經過對mysql的官方配置參數說明、網上資料查詢，以及自己的各種實驗得出的個人結論。

1. skip-external-locking：跳過外部鎖定。要明白這個參數，必須先了解external-locking（外部鎖定，作用是為MYISAM資料表在多程序【多個服務公用同一個資料庫目錄】通路下鎖定），大多數情況下，我們的mysql服務都是單程序服務的，從mysql官網上看，skip-external-locking參數預設情況下是ON的，

   mysql> show variables like '%skip%';
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | skip_external_locking  | ON    |      

   在配置檔案[mysqld]下開啟這個參數OK。
2. key_buffer_size = 256M：為MYISAM資料表開啟供線程共享的索引緩存。我們的項目中資料表基本上用的是INNODB引擎，是以這個參數暫時不進行調整，有需要的可以參考 http://database.51cto.com/art/201010/229939.htm
3. max_allowed_packet = 16M：服務端最大允許接收的資料包大小。在沒有調整該配置項的時候，服務端預設是4M。當然這個參數和mysql（預設16M）和mysqldump（預設為24M，我已經調整為16M）中的資料包大小有關系，一般情況下1M就可以，官方建議如果使用了blog或者更大的字元串時進行該參數的調整，一般情況下，資料庫會被初始化為net_buffer_length（最小1024byte，最大是1M，預設是16KB）的大小。
4. table_open_cache = 512：所有線程打開表的數目（預設設定大小為1000）。如果opened_tables很大并且不經常使用flush tables，官方建議我們增加該參數的大小。這個值并不是越大越好，需要根據實際情況下open_tables和opened_tables的綜合進行調整，詳細可見 http://www.cnblogs.com/suredandan/p/4010931.html
5. sort_buffer_size = 512K：需要排序會話的緩存大小，是針對每一個connection的，這個值也不會越大越好，預設大小是256kb，過大的配置會消耗更多的記憶體。我個人還沒有測試，可以檢視 http://bbs.chinaunix.net/thread-1805254-1-1.html
6. read_buffer_size = 512K：為需要全表掃描的MYISAM資料表線程指定緩存，也是針對每個connection的，這個參數暫時我也不需要太關注。

   Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes)
   for each table it scans. If you do many sequential scans, you might want to increase this value, which
   defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not
   a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.
   This option is also used in the following context for all search engines:
   • For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
   • For bulk insert into partitions.
   • For caching results of nested queries.
   and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.
   The maximum permissible setting for read_buffer_size is 2GB.
   For more information about memory use during different operations, see Section 8.11.4.1, “How MySQL
   Uses Memory”.      

7. read_rnd_buffer_size = 1M：首先，該變量可以被任何存儲引擎使用，當從一個已經排序的鍵值表中讀取行時，會先從該緩沖區中擷取而不再從磁盤上擷取。預設為256K。

   This variable is used for reads from MyISAM tables, and, for any storage engine, for Multi-Range Read
   optimization.
   When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are
   read through this buffer to avoid disk seeks. See Section 8.2.1.15, “ORDER BY Optimization”. Setting
   the variable to a large value can improve ORDER BY performance by a lot. However, this is a buffer
   Server System Variables
   627
   allocated for each client, so you should not set the global variable to a large value. Instead, change the
   session variable only from within those clients that need to run large queries.
   The maximum permissible setting for read_rnd_buffer_size is 2GB.      

   另外可參見 http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3642777&highlight=
8. thread_cache_size = 18：有多少線程供服務緩存使用。

   How many threads the server should cache for reuse. When a client disconnects, the client's threads
   are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads
   are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is
   a new thread created. This variable can be increased to improve performance if you have a lot of new
   connections. Normally, this does not provide a notable performance improvement if you have a good
   thread implementation. However, if your server sees hundreds of connections per second you should
   normally set thread_cache_size high enough so that most new connections use cached threads. By
   examining the difference between the Connections and Threads_created status variables, you can
   see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”.
   The default value is based on the following formula, capped to a limit of 100:
   8 + (max_connections / 100)
   This variable has no effect for the embedded server (libmysqld) and as of MySQL 5.7.2 is no longer
   visible within the embedded server.      

9. query_cache_size= 8M：配置設定給查詢緩存的記憶體大小。要配合query_cache_type使用，預設是不開啟的。隻從該參數的表面介紹來看，似乎值設定的越大，帶來的效果會更好，但是請注意，查詢緩存的工作原理，一個select語句過來後，資料庫将查詢結果緩存到cache中，等同樣的select查詢過來後，如果這段時間内該查詢結果沒有發生變化時，資料庫将cache中将緩存結果傳回，那麼假如查詢的相關資料表增删改特别多的話，資料表變更的這段時間内，要将cache失效，然後再更新資料，對于增删改來說，花費的時間就很多了，是以要有所權衡，這個參數我會在将來進行相關測試資料整理。

   By default, the query cache is
   disabled. This is achieved using a default value of 1M, with a default for query_cache_type of
   0. (To reduce overhead significantly if you set the size to 0, you should also start the server with
   query_cache_type=0.
   The permissible values are multiples of 1024; other values are rounded down to the nearest multiple.
   Note that query_cache_size bytes of memory are allocated even if query_cache_type is set to 0.
   See Section 8.9.3.3, “Query Cache Configuration”, for more information.
   The query cache needs a minimum size of about 40KB to allocate its structures. (The exact size
   depends on system architecture.) If you set the value of query_cache_size too small, a warning will
   occur, as described in Section 8.9.3.3, “Query Cache Configuration”.      

10. query_cache_type = 1：1表示緩存所有查詢，2表示緩存select sql_cache的查詢，看如下内容（最近在網上看到該參數最好設定為0，也就是說關閉緩存，其性能在實際情況下很糟糕）。

    0 or OFF Do not cache results in or retrieve results from the query cache. Note that
    this does not deallocate the query cache buffer. To do that, you should set
    query_cache_size to 0.
    1 or ON Cache all cacheable query results except for those that begin with SELECT
    SQL_NO_CACHE.
    2 or DEMAND Cache results only for cacheable queries that begin with SELECT SQL_CACHE.      

11. set global max_connections = 500：注意這個是通過指令行設定最大連接配接數，不是配置在配置檔案的，因為我在配置檔案裡面嘗試修改，重新開機mysql服務後并沒有起效，通過該參數設定以後，重新開機服務後，依然沒有起效，如果有朋友知道這個原因的話，請告知。如果說你的項目使用的是spring的連接配接池的時候，我認為spring個connection就對應的這個連接配接。根據你項目的需求而定。
12. log-bin=mysql-bin：開啟二進制日志，并且日志的名稱會按照mysql-bin***之類的依次生成。但是我一直有一些疑問，就是這個二進制日志是否是innodb的事務日志，是傳統所說的事務日志嗎？
13. binlog_format=mixed：二進制日志的格式為mixed，該中模式是statement和row模式的結合體，注意檢視我同僚寫的 http://www.xx566.com/detail/177.html 這篇文章，裡面講解了我們項目在二進制日志設定上遇到的問題和解決辦法，如果遇到類似的問題後，會有所幫助。

    In MySQL 5.7, the default format is STATEMENT.
    You must have the SUPER privilege to set either the global or session binlog_format value.
    The rules governing when changes to this variable take effect and how long the effect lasts are the same
    as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.
    When MIXED is specified, statement-based replication is used, except for cases where only row-based
    replication is guaranteed to lead to proper results. For example, this happens when statements contain
    user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always
    uses statement-based replication for stored functions and triggers.      

14. innodb_buffer_pool_size = 512M：innodb緩存表和索引的記憶體空間，官網建議我們設定為實體記憶體的50-80%，但是請注意是專注于資料服務的機器，如果你的作業系統包含資料庫和其他服務，請考慮其他服務占用的記憶體，對于Linux來說，這個參數會占用swap檔案的大小（14-17項在centOs的環境下，會導緻mysql無法啟動,沒有弄明白為什麼，請注意）。

    The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data.
    The default value is 128MB. The maximum value depends on the CPU architecture; the maximum
    is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems.
    On 32-bit systems, the CPU architecture and operating system may impose a lower practical
    maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting
    innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy
    server.
    The larger you set the innodb_buffer_pool_size value, the less disk I/O is needed to access the
    same data in tables more than once. On a dedicated database server, you might set this to up to 80% of
    the machine physical memory size.      

15. innodb_additional_mem_pool_size = 20M：用來設定innodb存儲資料目錄資訊和其他内部資料結構的記憶體池大小。

    The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal
    data structures. The more tables you have in your application, the more memory you allocate here. If
    InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and
    writes warning messages to the MySQL error log. The default value is 8MB.
    This variable relates to the InnoDB internal memory allocator, which is unused if
    innodb_use_sys_malloc is enabled.      

16. innodb_log_file_size = 128M：用來設定每個日志檔案在記憶體池中的大小，但是請注意，其他資料說“一旦設定了該參數，你就需要把原來的ib_logfile備份删除掉，否則mysql服務在重新開機的時候會出錯"，我個人還沒有進行測試。

    The size in bytes of each log file in a log group. The combined size of log files
    (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that
    is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit
    InnoDB System Variables
    1945
    but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of
    the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint
    flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery
    slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size
    less of a consideration. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.      

17. innodb_log_buffer_size = 16M：為innodb向磁盤重新整理日志檔案的緩存大小，可以相對應減少磁盤的IO。

    The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is
    8MB. A large log buffer enables large transactions to run without a need to write the log to disk before
    the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making
    the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB
    Disk I/O”.      

18. innodb_flush_log_at_trx_commit = 2：設定二進制日志重新整理磁盤的時間點。可參照 http://blog.csdn.net/qing_gee/article/details/42551179 。
19. innodb_lock_wait_timeout = 20：事務等待逾時的時間，預設為50秒。

    The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The
    default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB
    InnoDB System Variables
    1941
    transaction waits at most this many seconds for write access to the row before issuing the following
    error:
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To
    have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout
    option. See also Section 14.19.4, “InnoDB Error Handling”.