概要

MySQL性能优化是一个持续的过程，需要从硬件配置、数据库参数配置、SQL语句优化、性能监控等几方面进行。

优化建议和方法

1、优化SQL查询：

减少查询中的不必要的JOIN操作；
使用LIMIT来限制返回结果的数量；
选择性地使用索引；
避免使用SELECT *，而是只选择需要的字段；
使用预编译语句 (Prepared Statements) 以避免SQL注入攻击，并提高性能。

2、优化数据库表结构：

合理使用数据类型，如INT、VARCHAR、DATETIME等；
为经常被查询的列创建索引，提高查询速度；
使用InnoDB存储引擎，它具有行级锁定和事务支持，适用于并发访问较高的场景；
尽量避免使用NULL字段，因为它们需要额外的处理时间。

3、调整Mysql配置:

调整内存缓存参数，如增加innodb_buffer_pool_size以减少磁盘I/O；
调整innodb_log_file_size和innodb_log_buffer_size以优化日志写入性能；
启用慢查询日志(slow query log)，找出需要优化的SQL语句；
设置合适的max_connections值，以防止过多的并发连接导致性能下降。

4、优化硬件资源

使用SSD硬盘，提高I/O性能；
根据实际需求增加CPU和内存；
使用高速网络连接以降低延迟。

5、数据库复制与分区

使用主从复制（Master-Slave Replication）将读取负载分散到多台服务器；
使用分区表（Partitioning）将大型表分成更小的、更易于管理的部分。

6、使用缓存机制

在应用层使用缓存服务器，如Redis或Memcached，减少对数据库的访问。

7、监控与分析

定期监控MySQL性能，使用工具如MySQLTuner、Percona Toolkit等；
使用慢查询日志和性能分析工具，如MySQL Workbench、Percona Monitoring and Management (PMM)等，定位和优化瓶颈。

mysql性能参数设置详解：

innodb_buffer_pool_size：InnoDB存储引擎的缓冲池大小。这是MySQL中最重要的性能调优参数之一，因为它影响到InnoDB如何将数据和索引缓存在内存中。适当地增加此值可以减少磁盘I/O，从而提高性能。一般建议将其设置为服务器总内存的50-80%。
innodb_log_file_size：InnoDB存储引擎的日志文件大小。较大的日志文件可以提高数据写入性能。一般情况下，将其设置为innodb_buffer_pool_size的1/4是个不错的选择。但需要注意的是，在修改此参数时需要正确地调整日志文件，否则可能导致数据库启动失败。
innodb_log_buffer_size：InnoDB存储引擎的日志缓冲区大小。增加此值可以减少日志写入磁盘的频率，从而提高写入性能。通常可以将其设置在16MB到128MB之间，根据实际写入负载进行调整。
max_connections：服务器允许的最大并发连接数。默认值通常是151。将其设置得过高可能会导致系统资源耗尽，设置得过低则会限制并发能力。可以根据实际负载情况和服务器资源来调整此值。
query_cache_size：查询缓存大小。查询缓存可以加速重复查询的执行速度。但在高并发场景下，查询缓存可能导致性能下降。因此，在很多情况下，建议将查询缓存禁用（将query_cache_size设置为0）。
sort_buffer_size：每个线程用于排序的缓冲区大小。较大的缓冲区可以加速排序操作，但会增加内存使用。通常将其设置在256KB到2MB之间。
join_buffer_size：用于完成JOIN操作的缓冲区大小。如果有复杂的JOIN查询，可以适当增加此值以提高性能。但请注意，增加此值会导致每个线程使用更多内存。
tmp_table_size 和 max_heap_table_size：这两个参数控制内存中临时表的最大大小。较大的值可以减少磁盘临时表的使用，从而提高性能。但是，增加这些值会增加每个连接的内存使用。通常可以将这两个参数设置为相同的值，如16MB或32MB。

Mysql主从复制配置方法

MySQL主从复制配置允许将一个MySQL服务器（主服务器）上的数据自动复制到一个或多个其他MySQL服务器（从服务器）。

mysql主从复制架构图

以下是主从复制配置的参考步骤：

主服务器配置

1、编辑MySQL配置文件（例如：/etc/my.cnf 或 /etc/mysql/my.cnf）。

2、在[mysqld]部分添加以下配置：

server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync_binlog = 1

server-id是唯一的服务器标识符，确保每个MySQL服务器在复制环境中具有唯一的ID。
log-bin启用二进制日志功能，并设置日志文件名前缀。
binlog-format设置二进制日志的格式。建议使用ROW。
sync_binlog确保每个事务在提交时都将二进制日志写入磁盘。

3、重启MySQL服务使配置生效。

4、使用管理员帐户登录MySQL，创建用于复制的专用用户并授权：

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

5、记录主服务器的二进制日志文件名和位置，稍后将在从服务器配置中使用：

SHOW MASTER STATUS;

从服务器配置

1、编辑MySQL配置文件（例如：/etc/my.cnf 或 /etc/mysql/my.cnf）。

2、在[mysqld]部分添加以下配置：

server-id = 2

server-id是唯一的服务器标识符，确保其与主服务器上的值不同。

3、重启MySQL服务使配置生效。

4、使用管理员帐户登录MySQL，配置从服务器连接到主服务器：

CHANGE MASTER TO
  MASTER_HOST = '主服务器IP',
  MASTER_USER = 'replication_user',
  MASTER_PASSWORD = 'your_password',
  MASTER_LOG_FILE = '之前记录的二进制日志文件名',
  MASTER_LOG_POS = 之前记录的二进制日志位置;

5、启动从服务器的复制进程：