MySQL配置优化总结

最新推荐文章于 2024-09-21 15:28:01 发布

攀哥

最新推荐文章于 2024-09-21 15:28:01 发布

阅读量689

点赞数

分类专栏： MySQL优化知识整理

本文链接：https://blog.csdn.net/Yp5185423/article/details/8150505

版权

MySQL优化知识整理专栏收录该内容

12 篇文章 0 订阅

订阅专栏

基本知识

配置设置作用域：全局域会话域（修改会话域的值只会在当前连接有效，连接关闭就失效了） SHOW VARIABLES语句查看系统变量及其值

服务器运行时设置的是动态变量，在MySQL关闭后就会失效

在对配置进行优化的时候，应该先对查询和结构进行优化

MySQL内存调优

                               1.MySQL能使用的内存绝对上限（不能耗尽服务器内存）
             2.每个连接使用的内存（包括堆栈（默认64KB），连接缓冲区，结果缓冲区需要一定缓冲区执行查询）
                               3.操作系统的内存（预留一部分，防止发生swap。如果是MyISAM，操作系统缓存的内存会大于key_buffer_size，使用操作系统缓存数据文件，通常大于索引文件）
                               4.MySQL的缓存

每个连接使用的内存

MyISAM每条连接内存：

per_connection=(sort_buffer_size +read_rnd_buffer_size + join_buffer_size +read_buffer_size +thread_stack )

sort_buffer_size：每个排序线程分配的缓冲区的大小。只有在查询需要排序的时候分配内存。增加该值可以加快ORDER BY或GROUP BY操作

read_rnd_buffer_size：当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。只在查询需要时才分配内存，只分配所需的内存。设置为较大的值可以改进ORDER BY的性能

join_buffer_size：用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)

read_buffer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。只在查询需要时分配

thread_stack：每个线程的堆栈大小

InnoDB每条连接内存：

per_connection = (sort_buffer_size +join_buffer_size +thread_stack + binlog_cache_size + read_buffer_size + read_rnd_buffer_size)

binlog_cache_size：在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(--log-bin选项)的前提下为每个客户端分配的内存

MySQL缓存

                       1.MyISAM键缓存
                       2.InnoDB缓存池
                       3.查询缓存（未整理）

MyISAM缓存：key_buffer_size +query_cache_size

key_buffer_size：MyISAM表的索引块分配了缓冲区，主要运行MySQL的机器剩余内存的25%-50%，只会在操作系统实际用到的时候才会分配。上限4GB，没有使用MyISAM也要保存少量内存，一般16-32MB

query_cache_size：为缓存查询结果分配的内存的数量。服务器启动时一次性分配。更新变量会删除原来的所有缓存的查询

InnoDB缓存：innodb_buffer_pool_size+query_cache_size

innodb_buffer_pool_size：保存了索引，行数据，自适应的哈希索引，插入缓冲，锁以及其他内部结构。InnoDB严重依赖缓池，innodb_buffer_pool_size必须分配足够内存，可以达到机器内存80%

影响InnoDB的变量

innodb_additional_mem_pool_size：InnoDB 用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多，你需要在这里分配越多的内存。如果InnoDB 用光了这个池内的内存，InnoDB 开始从操作系统分配内存，并且往MySQL 错误日志写警告信息。

innodb_lock_wait_timeout： Mysql 可以自动的监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理。默认值是50 秒。

innodb_thread_concurrency ：InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制，系统上处理器和磁盘的个数之和

innodb_file_io_threads ：InnoDB中文件I/O线程的数量，默认值是4，在Unix上，InnoDB总是使用默认值

线程缓存

thread_cache_size：线程缓存保存与当前连接无关的线程，观察Threads_connected确定

表缓存

table_cache 表缓存存储了表示表的对象，适用于MyISAM（表的数据或索引文件描述符），观察Opened_tables 增加值设置，与max_connections 有关。需设置open_files_limit增加允许保持开启的文件数量

InnoDB数据字典

innodb自己每个表自己的缓存，不可配置的

MyISAM总内存：MyISAM缓存+ max_connections *per_connection或者MyISAM缓存+（max_connections *per_connection）/3

InnoDB总内存：InnoDB缓存+max_connections *per_connection或者InnoDB缓存+（max_connections *per_connection）/3

MySQL I/O调优

MyISAM IO

                                每次写入后就把索引改变刷洗到磁盘上。对一个表做很多处理，可以组成批处理：LOCK TABLES 写入延迟到对表解锁
                                delay_key_write 延迟索引的写入（修改过键缓冲区只有在表关闭时候才会被写入磁盘）
                               小的表可以开启myisam_recover检查恢复，大表最好用CHECK TABLES / REPAIR TABLES

Innodb I

InnoDB事务日志

使用日志来减少事务提交的开销，记录了事务，事务对数据和索引改变会记录到表空间的随机位置，写到磁盘上会引起随机IO。通过自身日志把随机IO转化为顺序IO。以循环方式写日志

日志大小和缓存
日志总体大小由innodb_log_file_size和innodb_log_files_in_group

innodb_log_file_size ：在日志组里每个日志文件的大小，在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间

innodb_log_files_in_group：在日志组里日志文件的数目

innodb_log_buffer_size ：InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小。通常8-16MB 就足够了。InnoDb改变数据时候，把改动的记录写到日志缓冲里。
刷写日志缓冲方法

innodb_flush_log_at_trx_commit
                                           0：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。（最多丢失一秒的事务，不安全，但效率最高）
                                           1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。（最安全，性能最低）
                                         2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。（比0安全一些）

打开和清洗日志和数据文件

innodb使用fsync()函数刷写数据和日志文件（导致更多IO操作，操作系统会缓存一些数据在自己缓存中）

innodb打开并清洗日志和数据文件 fdatasync（默认值） 0_DIRECT(使用fsync()把文件刷写到磁盘，但操作系统不缓存数据，不提前读取（不能禁止RAID卡的），禁止操作系统的缓存并使读写动作直接到存储设备，避免了双缓冲） 0_SYNC（不会禁止操作系统缓存（但不会使用缓存））

innodb表空间

数据保存在表空间，跨越磁盘上的虚拟文件系统，innodb_data_file_path

双缓冲

表空间特殊地方（最近写入页面的备份）页面（16KB）从缓冲池写到磁盘时，先写入双缓冲，再写入磁盘