MySQL性能优化手册

# MySQL性能优化手册

作为一个合格的程序员或者DBA，问题一定要多方位进行考虑。这里总结MySQL优化的四个主要维度、MySQL相关开发规范，以及优化其他的考虑方向如架构优化。

# 四个纬度

MySQL优化可以从四个角度入手：SQL语句及索引、表结构设计、系统配置、硬件配置。

# 硬件升级

硬件方面的优化可以从CPU、内存、硬盘、网络入手，但是这个优化手段成本高但效果往往是最差的。这里不再多的赘述，也不推荐。

# 系统配置

# 服务端系统选择

常使用Linux作为服务端的系统，本地开发的话可以随意。Linux系统版本和MySQL版本选择稳定的版本即可。

# 保证从内存读取

MySQL会在内存中保存一定的数据，通过LRU（最近最少使用）算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。这里针对InnoDB的内存中的Buffer Pool进行优化，常用命令以及参数：

# 查看page页大小
show variables like '%innodb_page_size%'; 
# 查看lru list中old列表参数
show variables like '%innodb_old%';
# 查看buffer pool参数
show variables like '%innodb_buffer%';
# 配置文件中建议配置
# innodb_buffer_pool_size设置为总内存大小的60%-80%，
# innodb_buffer_pool_instances可以设置为多个，这样可以避免缓存争夺。

# 系统预热

数据预热相当于将磁盘中的数据提前放入BufferPool内存缓冲池内。一定程度提升了读取速度（一般运维处理，开发不管）。

对于InnoDB，这里提供一份预热SQL脚本：

SELECT DISTINCT
	CONCAT(
		'SELECT ',
		ndxcollist,
		' FROM ',
		db,
		'.',
		tb,
		' ORDER BY ',
		ndxcollist,
		';'
	) SelectQueryToLoadCache
FROM
	(
		SELECT
			ENGINE,
			table_schema db,
			table_name tb,
			index_name,
			GROUP_CONCAT(
				column_name
				ORDER BY
					seq_in_index
			) ndxcollist
		FROM
			(
				SELECT
					B. ENGINE,
					A.table_schema,
					A.table_name,
					A.index_name,
					A.column_name,
					A.seq_in_index
				FROM
					information_schema.statistics A
				INNER JOIN (
					SELECT
						ENGINE,
						table_schema,
						table_name
					FROM
						information_schema. TABLES
					WHERE
						ENGINE = 'InnoDB'
				) B USING (table_schema, table_name)
				WHERE
					B.table_schema NOT IN (
						'information_schema',
						'mysql'
					)
				ORDER BY
					table_schema,
					table_name,
					index_name,
					seq_in_index
			) A
		GROUP BY
			table_schema,
			table_name,
			index_name
	) AA
ORDER BY
	db,
	tb;

脚本保存为loadtomem.sql文件

执行命令：

mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要数据预热时（一般为重启MySQL服务）执行：

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

# 降低磁盘写入次数

增大redo log，减少刷盘次数：redolog是重做日志，用于保证数据的一致。减少落盘相当于减少了系统IO操作。
```
nnodb_log_file_size 建议设置为 0.25 * innodb_buffer_pool_size
```
通用查询日志、慢查询日志可以不开，binlog需要打开：通用查询和慢查询日志也是要落盘的，可以根据实际情况开启，如果不需要使用的话就可以关掉。binlog用于恢复和主从复制，这个可以开启。
```
# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%'; 
```

写redo log策略innodb_flush_log_at_trx_commit设置为0或2：

0：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache，刷盘OS cache-->磁盘文件），最多丢失1秒数据
1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

# 表结构设计优化

设计中间表：设计中间表，一般针对于统计分析功能，或者实时性不高的需求（报表统计，数据分析等系统）。
设计冗余字段：为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）。这里分库分表时较为常用。
拆表：对于字段太多的大表，考虑拆表（比如一个表有100多个字段）对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表。
主键优化：每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（分布式系统的情况下建议雪花算法）
字段的设计：
1. 使用尽量小的存储类型。数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
2. 尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
3. 使用tinyint代替enum
4. 时间字段使用datetime
5. varchar不要过长声明
6. 减少宽表的设计（binlog）
7. 减少使用text等大字段