MySQL 性能监控与调优

缓存优化

1、应用的持久层（模型层）开启缓存 2、使用 Redis 缓存

慢查询分析

慢查询：未使用索引的查询 和 超过指定时间的查询

慢查询相关参数 slow_query_log：是否开启慢查询日志 slow_query_log_file：慢查询日志路径，必须存在且 mysql 用户有写权限 long_query_time：慢查询阈值时间 log_queries_not_using_indexes：未使用索引的查询是否记入慢查询日志

查看参数：show variables like 'slow_query_log';查看全局参数：show global variables like 'slow_query_log';设置参数：set slow_query_log=on;设置全局参数：set global slow_query_log=on;

慢日志格式

# Time: 执行时间# User@Host: 用户信息# Query_time: 查询耗时  Lock_time: 锁耗时 Rows_sent: 发送行数  Rows_examined: 扫描行数SET timestamp=执行时间戳;SQL语句

慢查询分析工具 mysqldumpslow 耗时统计前三：mysqldumpslow -t 3 /var/lib/mysql/slow.log

慢查询分析工具 pt-query-digestpt-query-digest 包含在 Percona 公司提供的工具套装 percona-toolkit 中，依赖 Perl1、安装 Perlyum install -y perl-DBIyum install -y perl-DBD-MySQLyum install -y perl-Time-HiResyum install -y perl-IO-Socket-SSLyum install -y perl-Digest-MD5yum install -y perl-TermReadKey2、安装 percona-toolkit 到 percona 官网上找到 percona-toolkit 下载对应操作系统的 percona-toolkit.rpm 安装：rpm -ivh percona-toolkit.rpm

前三耗时查询：pt-query-digest --limit 3 /var/lib/mysql/slow.log

执行计划分析

explain 查询语句; 得到该查询语句的执行计划 1、table：涉及的表 2、type：const（唯一索引查找）、eq_rep（唯一索引范围查找）、ref（基于索引的连接查询）、range（基于索引的范围查找）、index（索引扫描）、ALL（表扫描），性能由高到低 3、possible_key：可用索引 4、key：实际使用索引 5、key_len：索引长度，越短越好 6、ref：使用索引具体的列 7、row：必须扫描的行数

查询优化

MySql 的索引由 B+树实现

1、需要建立索引的字段 where 从句、group by 从句、order by 从句、on 从句中的字段 2、索引字段数量不能太多，索引字段长度越小越好 3、select max(age) from people;age 不是索引的话，需要扫描整张表，应该给 age 建立索引 4、用连接查询 代替 嵌套查询（需要建立临时表）5、order by title limit 500,10order by 的字段必须是 索引字段，否则需要扫描整张表；即使是索引字段排序，limit 500,10，前 500 条数据也需要索引扫描，可以改成 where title>前一页的最后一个 title order by title limit 0,106、多字段索引，离散度高（重复少）的字段放在前面 7、联合索引最左匹配原则：key index_abc(a,b,c)，查询时提供最左字段才能用到该联合索引，例如(a>0)；只提供右侧字段则不会用到该联合索引，例如(b>0)、(b>0,c>0)

数据类型优化

1、text 类型放到附加表中，需要的时候才查询 2、用 int 存储时间，用 unix_timestamp()、from_unixtime()来转换 3、用 bigint 存 IP，用 inet_aton()、inet_ntoa()来转换

表设计优化

1、范式设计 2、增加冗余字段，来减少连接查询 3、垂直拆分

系统配置优化

/etc/security/limits.conf

soft nofile 65535   # 最大打开文件数，默认为1024hard nofile 65535

查看限制：ulimit -a

MySQL 配置优化

/etc/my.cnf

Innodb_buffer_pool_size=内存的75%  # 如果系统中只有Innodb表Innodb_buffer_pool_instances=4  # 缓冲池个数Innodb_flush_log_at_trx_commit=2 # 每2次提交，从缓冲区刷新到磁盘，默认为1Innodb_read_io_threads=4  # MySQL很多操作只能单线程，MySQL性能对CUP核数不敏感Innodb_write_io_threads=4  # 根据CPU数和读写比率去掉Innodb_file_per_table=on # 每个表使用独立表空间，默认为OFF（使用共享表空间）

Percona 的 MySQL 配置生成器：https://tools.percona.com/wizard

预防 SQL 注入

1、JDBC 防止 SQL 注入使用 java.sql.Preparedstatement

string sql = "select * from people p where p.id = ? and p.name = ?";PreparedStatement  ps = connection.prepareStatement(sql);ps.setInt(1,id);ps.setString(2,name);ResultSet rs = ps.executeQuery(); 

原理 PreparedStatement 使用了 MySQL 支持的预编译功能，语句和参数分开提交连接池要指定开启预编译：jdbc:mysql://localhost:3306/db1?&useServerPrepStmts=true&cachePrepStmts=true

MySQL 预编译

prepare  statement_1 from 'select * from user where username like ?';set @username='%小宋%';execute statement_1 using @username;

2、MyBatis 防止 SQL 注入

用 #{} 来引用参数，  而非 '${}'。使用'${}'时，要过滤参数里的危险字符#{} 下层调用了PreparedStatement