# 系统配置优化


<!--more-->


数据库优化维度有四个: 硬件升级, 系统配置, 表结构设计, sql语句及索引

![示意图](https://img.zhaojq.top/20260727091650905.png "示意图")

## 一. 保证从内存中读取数据

MySQL会在内存中保存一定的数据，通过LRU算法将不常访问的数据保存在硬盘文件中。

尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。

扩大**innodb_buffer_pool_size**，能够全然从内存中读取数据。最大限度降低磁盘操作。

```mysql
show global status like'innodb_buffer_pool_pages_%';

Innodb_buffer_pool_pages_data	282
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	187
Innodb_buffer_pool_pages_free	7909
Innodb_buffer_pool_pages_misc	0               #0 表示已经被用光
Innodb_buffer_pool_pages_total	8191

#innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。
```

> 如果是专用的MySQL Server可以禁用SWAP
>
> 关闭所有交换设备和文件. 
>
> ​	swapoff -a 



## 二. 数据预热

默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。

所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。

1. 对于InnoDB数据库，进行数据预热的脚本是

   ```mysql
   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

2. 执行命令

   ```mysql
   mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql
   #保存路径可自定义
   ```

3. 在需要数据预热时,如重启数据库

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



## 三. 降低磁盘写入次数

- 增大redolog，减少落盘次数

  innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

- 通用查询日志、慢查询日志可以不开 ，bin-log开

  生产中不开通用查询日志，遇到性能问题开慢查询日志

- 写redolog策略 innodb_flush_log_at_trx_commit设置为0或2

  0 : 每隔1s写入文件并通知文件系统flush到磁盘, 可能会造成1秒数据丢失

  1: 每次事务提交都会触发 log thread 触发写文件并持久化到磁盘

  2: 每次事务结束,写文件,但因文件系统有缓存,并不一定完全写到磁盘

  如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。

## 四. 提高磁盘读写性能

使用SSD或者内存磁盘