mysql参数优化建议

╰+哭是因爲堅強的太久メ 2021-08-04 12:09 584阅读 0赞

要想提高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引擎，所以这个参数暂时不进行调整，
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的综合进行调整，
5.  **sort\_buffer\_size = 512K**：需要排序会话的缓存大小，是针对每一个connection的，这个值也不会越大越好，默认大小是256kb，过大的配置会消耗更多的内存。我个人还没有测试，
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=][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模式的结合体，
    
        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][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”.