有多个方面可以优化mysql的性能,例如架构、硬件、配置参数、索引、查询语句优化等
架构优化
可以使用主从架构,配置读写分离,一般情况下都是查询语句数量大于插入语句数量,可以通过配置一主多从来分摊数据库的读写压力,从而达到提升性能的效果,还可以增加缓存,例如redis,对数据库性能的提升也有很大的帮助
硬件优化
cpu、内存、磁盘读写速度、网络带宽等都会对数据库性能产生影响,一般来说硬件的提升对数据库性能提升是最大的,主要是监控cpu、内存、磁盘读写速度、网络带宽这几个因素,看看哪一项接近瓶颈,就需要考虑去提升一下配置
配置参数优化
一般搭建的数据库都是使用的默认参数,并不会去配置参数的优化,这样并不能完全发挥数据库的最大性能,下面将会对参数进行一下优化
确保表使用正确的存储引擎:MySQL支持多种存储引擎,每种引擎都有自己的优缺点。例如,InnoDB引擎适用于高并发的OLTP应用,而MyISAM引擎适用于读密集型应用。因此,选择正确的存储引擎可以显著提高数据库性能
innodb_buffer_pool_size:这是InnoDB存储引擎使用的缓存池大小。建议将其设置为物理内存的70%-80%。例如,如果服务器有16GB的内存,则可以将innodb_buffer_pool_size设置为12GB
innodb_log_file_size:这是InnoDB存储引擎使用的日志文件大小。建议将其设置为1GB-2GB。较小的日志文件大小可能会导致频繁的检查点,从而影响性能
innodb_flush_log_at_trx_commit:这是InnoDB存储引擎写入日志文件的策略。建议将其设置为1,表示每次事务提交时都将日志文件刷新到磁盘。这可以确保事务的持久性,但会影响性能。如果可以容忍一定的数据丢失,可以将其设置为2或0,以提高性能,
0:设置为0表示每秒写入一次日志,此选项新能最好,但是如果数据库挂掉会丢失1秒的数据
2:设置为2表示只把日志写入到系统缓存区,再每秒同步到磁盘,此项性能较好,而且只有系统崩溃才会丢失1秒的数据
query_cache_size:这是查询缓存的大小。建议将其设置为256MB-512MB。较大的查询缓存可以提高查询性能,但会占用大量的内存
max_connections:这是允许的最大连接数。建议根据服务器的硬件配置和应用程序的负载来设置,建议通过多次配置并进行压测和观察硬件瓶颈来确定,默认值为151
查看最大连接数:show variables like 'max_connections';
查看响应的连接数:show status like 'max_used_connections';
一般根据以下公式确定最大连接数配置比较理想的值
max_used_connections/max_connections*100=85%左右
当这两个数值相等时表示最大连接数配置过小
tmp_table_size和max_heap_table_size:这是临时表的大小。建议将其设置为较大的值,例如256MB。这可以减少创建和删除临时表的次数,从而提高性能
key_buffer_size:这是MyISAM存储引擎使用的索引缓存大小。建议将其设置为物理内存的25%。例如,如果服务器有16GB的内存,则可以将key_buffer_size设置为4GB
innodb_flush_method:这是InnoDB存储引擎使用的刷新方法。建议将其设置为O_DIRECT,以避免双重缓存
innodb_thread_concurrency:这是InnoDB存储引擎使用的线程并发数。建议将其设置为服务器CPU核心数的2倍
innodb_io_capacity和innodb_io_capacity_max:这是InnoDB存储引擎使用的I/O容量。建议将其设置为磁盘的最大IOPS值
innodb_read_io_threads和innodb_write_io_threads:这是InnoDB存储引擎使用的I/O线程数。建议将其设置为服务器CPU核心数的2倍
innodb_adaptive_flushing:这是InnoDB存储引擎使用的自适应刷新策略。建议将其设置为ON,以自动调整刷新策略
innodb_stats_on_metadata:这是InnoDB存储引擎使用的元数据统计信息。建议将其设置为OFF,以避免在每次查询时收集统计信息
thread_cache_size:这是线程缓存的大小。建议将其设置为服务器连接数的10%-20%