【MySQL】A01、性能优化-结构设计与配置

本文主要是介绍【MySQL】A01、性能优化-结构设计与配置，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、数据库设计原则

1.1、核心原则

• 不在数据库做运算;

• cpu计算务必移至业务层;

• 控制列数量(字段少而精,字段数建议在20以内);

• 平衡范式与冗余(效率优先；往往牺牲范式)

• 拒绝3B(拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch);

1.2、字段类原则

1. 用好数值类型(用合适的字段类型节约空间);

2. 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能)，比如IP地址;

3. 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);

4. 少用text类型(尽量使用varchar代替text字段);

1.3、索引类原则

1. 合理使用索引(改善查询,减慢更新,索引一定不是越多越好);

2. 字符字段必须建前缀索引;

3. 不在索引做列运算;

4. innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);

5. 不用外键(由程序保证约束);

1.4、SQL类原则

1. sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);

2. 简单的事务;

3. 避免使用trig/func(触发器、函数不用客户端程序取而代之);

4. 不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);

5. OR改写为IN(or的效率是n级别);

6. OR改写为UNION(mysql的索引合并很弱智);

   select id from t where phone = ’159′ or name = ‘john’;=>select id from t where phone=’159′
   union
   select id from t where name=’jonh’

7. 慎用count(*);

8. limit高效分页(limit越大，效率越低);

9. 使用union all替代union(union有去重开销);

10. 打散批量更新;

2、数据库结构优化

2.1、选择合适的数据类型

2.1.1、数据类型选择

        数据类型的选择，重点在于“合适”二字，如何确定选择的数据类型是否合适了？

1. 使用可以存下你的数据的最小的数据类型。（时间类型数据：可以使用varchar类型，可以使用int类型，也可以使用时间戳类型）

2. 使用简单的数据类型，int要比varchar类型在mysql处理上简单。（int类型存储时间是最好的选择）

3. 尽可能的使用not null定义字段。（innodb的特性所决定，非not null的值，需要额外的在字段存储，同时也会增加IO和存储的开销）
4. 尽量少用text类型，非用不可时最好考虑分表。

2.1.2、案例

案例一：int类型存储时间-时间转换

        使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换。

-- 创建表
create table test(
id int auto_increment not null,
timestr int ,
primary key(id)
);-- 导入数据
insert into test (timestr) values (unix_timestamp('2018-05-29 16:00:00'));

-- 对时间进行转换
select from_unixtime(timestr) from test;

1. unix_timestamp()函数是将日期格式的数据转换为int类型

2. FROM_UNIXTIME(timestr)函数是将int类型转换为时间格式

案例二：ip地址的存储

        在我们的外部应用中，都要记录ip地址，大部分场合都是varchar（15）进行存储，就需要15个字节进行存储，但是bigint只需要8个字节进行存储，当数据量很大的时候（千万级别的数据），相差7个字节，但是不能小看这7个字节，给大家算一下。

        一个字段就多这么多，那如果我们这样的字段需要上万个字段了？是需要很多的存储空间的。使用bigint（8）来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换。

-- 创建表
create table sessions(
id int auto_increment not null,
ipaddress bigint,
primary key (id)
);-- 导入数据
insert into sessions (ipaddress)values (inet_aton('192.168.0.1'));-- 转换
select inet_ntoa(ipaddress) from sessions;

2.2、数据表的垂直拆分

2.2.1、定义

        所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。

2.2.2、原则

        通常垂直拆分可以按以下原则进行：

1. 把不常用的字段表单独存放到一个表中。
2. 把大字段独立存放到一个表中。
3. 把经常一起使用的字段放到一起。

例子：以film表为例

        在该表中，title和description这两个字段占空间比较大，况且在使用频率也比较低，因此可以将其提取出来，将上面的一个达标垂直拆分为两个表（film和film_ext）：如下所示：

2.3、数据表的水平拆分

2.3.1、为什么水平拆分

        表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的，以下面的peyment表为例来说明：

-- 查看表结构字段
desc payment;-- 查看建表语句
show create table payment;

2.3.2、水平不拆分原因

        如果单表的数据量达到上亿条，那么这时候我们尽管加了完美的索引，查询效率低，写入的效率也相应的降低。

2.3.3、如何将数据平均分为 N 份

        通常水平拆分的方法为：

1. 对customer_id进行hash运算，如果要拆分为5个表则使用mod（customer_id，5）取出0-4个值。
2. 针对不动的hashid把数据存储到不同的表中。

2.3.4、水平拆分的挑战

1. 跨分区表进行数据查询，例如前端业务统计。业务上给不同的用户返回不同的业务信息，对分区表没有大的挑战。
2. 统计及后台报表操作，但是对后台进行报表统计时，数据量比较大，后台统计时效性比较低，后台就用汇总表，将前后台的表拆分开。

3、数据库系统配置优化

3.1、操作系统配置优化

3.1.1、操作系统的优化

        网络方面的配置，要修改/etc/sysctl.conf

#1、增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535#2、减少断开连接时，资源回收(tcp有连接状态)
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

        TCP是有连接状态，通过netstat查看连接状态，经常会看到timeout状态或者timewait状态连接，为了加快timewait状态的连接回收，就需要调整上面的四个参数，保持TCP连接数在一个适当的状态。

3.1.2、打开文件数限制

        打开文件数的限制，可以使用ulimit –a查看目录的各个限制，可以修改/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制（永久生效）

3.2、MySQL配置优化

3.2.1、MySQL配置文件修改

        Mysql可以通过启动时指定参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/my.cnf 或者是 /etc/mysql/my.cnf在Windows系统配置文件可以是位于C://windows//my.ini文件，MySQL查找配置文件的顺序可以通过以下方法获得。

/usr/sbin/mysqld --verbose --help | grep -A 1 'default options'

3.2.2、常用参数说明

1、连接请求的变量

1.1、max_connections

        MySQL的最大连接数，增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

        数值过小会经常出现ERROR 1040: Too many connections错误，可以过’conn%’通配符查看当前状态的连接数量，以定夺该值的大小。

-- 最大连接数
show variables like 'max_connections';-- 响应的连接数
show status like 'max_used_connections';

说明：理想值设置为多大才合适了？

max_used_connections / max_connections * 100% （理想值≈ 85%）

        如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

1.2、back_log

        MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

        back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。

        当观察你主机进程列表（mysql> show full processlist），发现大量264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了。默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数。

mysql> show full processlist;
+----+------+-----------+--------+---------+------+----------+-----------------------+
| Id | User | Host      | db     | Command | Time | State    | Info                  |
+----+------+-----------+--------+---------+------+----------+-----------------------+
| 51 | root | localhost | NULL   | Sleep   |    3 |          | NULL                  |
| 52 | root | localhost | NULL   | Sleep   |    3 |          | NULL                  |
| 56 | root | localhost | sakila | Query   |    0 | starting | show full processlist |
+----+------+-----------+--------+---------+------+----------+-----------------------+
3 rows in set (0.01 sec)mysql> 

1.3、interactive_timeout

        一个交互连接在被服务器在关闭前等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE 选项的客户。默认数值是28800，可调优为7200。

2、缓冲区变量

2.1、全局缓冲

2.1.1、key_buffer_size

        key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

        key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

举例如下：

-- 查看 key_buffer_size，单位字节
show variables like 'key_buffer_size';-- 查看索引读状态值
show global status like 'key_read%';

        默认配置数值是8388600(8M)，主机有4GB内存，可以调优值268435456(256MB)。计算索引未命中缓存的概率：

#设置在1/1000左右较好
key_cache_miss_rate ＝Key_reads / Key_read_requests * 100%

2.1.2、query_cache_size

        使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。

        如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

        与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。

• query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M

• query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。

-- 查询缓冲相关配置
show global status like 'qcache%';-- 确认Qcache 是否开启
show variables like 'query_cache_type';

MySQL 查询缓存变量
变量名    说明    
Qcache_free_blocks  缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。    
Qcache_free_memory  缓存中的空闲内存。    
Qcache_hits      每次查询在缓存中命中时就增大。    
Qcache_inserts    每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。    
Qcache_lowmem_prunes   缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。    
Qcache_not_cached     不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。    
Qcache_queries_in_cache  当前缓存的查询（和响应）的数量。    
Qcache_total_blocks   缓存中块的数量。

特别注意：MySQL在其最新的8.0版本中，删除了查询缓存(Query Cache)区域，就此，MySQL的Query Cache彻底的退出了历史舞台。在5.7版本中，MySQL已经将Query Cache的选项(query_cache_type)的缺省值设为了关闭，并在5.7.20版本中，将该配置标记为了Deprecated。

查询缓存碎片率= Qcache_free_blocks / Qcache_total_blocks * 100%

        如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率= (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存命中率= (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

        查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。

2.1.3、record_buffer_size

        每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

-- 查询记录缓冲区
SHOW VARIABLES LIKE 'innodb_log_buffer_size';-- 设置新的大小（例如设置为16MB）：
SET GLOBAL innodb_log_buffer_size = 16777216;

2.1.4、read_rnd_buffer_size

        随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。一般可设置为16M

-- 查看随机缓冲区大小
SHOW VARIABLES LIKE 'read_rnd_buffer_size';--  设置大小，方式一：
SET GLOBAL read_rnd_buffer_size = 16777216; -- 设置为 16MB-- 设置大小，方式二：配置文件设置
[mysqld]
read_rnd_buffer_size = 16777216

2.1.5、sort_buffer_size

        个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。

2.1.6、join_buffer_size

        在MySQL中，join_buffer_size是一个用于优化查询性能的参数，它用于存储在执行连接操作时使用的中间结果集（join buffer）。默认情况下，join_buffer_size的值为262144字节（256KB），但是在某些情况下，这个值可能需要根据实际情况进行调整。本文将介绍如何在线调整mysql的join_buffer_size参数。

-- 查询
SHOW VARIABLES LIKE 'join_buffer_size';-- 修改
SET GLOBAL join_buffer_size = <new_value>;-- 应用新的join_buffer_size值
FLUSH TABLES;

2.1.7、table_cache

        表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值 Open_tables 和 Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

        1G内存机器，推荐值是128－256。内存在4GB左右的服务器该参数可设置为256M或384M。

table_cache 
MySQL 5.0升级到5.1，table_cache 改名table_open_cache

2.1.8、max_heap_table_size

        用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

        这个变量和tmp_table_size一起限制了内部内存表的大小。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。

-- 查询
SHOW VARIABLES LIKE 'max_heap_table_size';-- 修改
SET GLOBAL max_heap_table_size = <size>;

方法	查看方式
系统变量	SHOW VARIABLES LIKE 'tmp_table_size';
	SHOW VARIABLES LIKE 'max_heap_table_size';
信息模式	SELECT @@global.max_heap_table_size;
	SELECT @@global.tmp_table_size;
存储引擎	SHOW TABLE STATUS LIKE 'tablename';

2.1.9、tmp_table_size

        通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。

-- 查询
show global status like 'created_tmp%';

        每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

2.1.10、thread_cache_size

        thread_cache_size 是 MySQL 的一个系统变量，用于设置缓存空闲连接线程的数量，以便在新的连接到达时，不必创建新的线程。

        可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections和Threads_created状态的变量，可以看到这个变量的作用。默认值为110，可调优为80。

-- 查询
SHOW VARIABLES LIKE 'thread_cache_size';
SHOW STATUS LIKE 'Threads_cached';-- 在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中设置，然后重启 MySQL 服务。例如，要设置为 20：
[mysqld]
thread_cache_size = 20

2.1.11、thread_concurrency

        推荐设置为服务器 CPU核数的2倍，例如双核的CPU, 那么thread_concurrency的应该为4；2个双核的cpu, thread_concurrency的值应为8。

-- 查询当前的 innodb_thread_concurrency 值非常简单，您只需要在MySQL命令行客户端执行以下指令：
SHOW VARIABLES LIKE 'innodb_thread_concurrency';-- 根据需要调整该参数也很直接。您可以通过以下语句临时（不重启服务）修改它
SET GLOBAL innodb_thread_concurrency = <desired_value>;-- 若要永久更改该值，请在 my.cnf (Linux) 或 my.ini (Windows) 配置文件的 [mysqld] 部分添加或更新以下内容：
[mysqld] innodb_thread_concurrency = <desired_value>

• 默认值为0表示InnoDB会自动管理并发线程数。这对于大多数系统已经足够有效。
• 如果监测到CPU资源未被充分利用，适当增加 innodb_thread_concurrency 值可能有助于提高吞吐量。
• 如果存在大量上下文切换或者CPU竞争，考虑降低这个值。
• 保持 innodb_thread_concurrency 值为CPU核心数的两倍左右通常是一个合理的起点，但这需要根据实际情况调整。
• 长期监控系统的性能指标，并根据观察结果调整参数。

innodb_thread_concurrency和max_connections配置的区别

        尽管 innodb_thread_concurrency 和 max_connections 都与数据库并发相关，但它们在功能和目的上有所不同。max_connections 控制可以同时连接到数据库的最大客户端数目，它主要限制的是网络层面的并发连接数。而 innodb_thread_concurrency 则专注于控制内部处理事务的线程并发数。

2.1.12、wait_timeout

        在 MySQL 中，wait_timeout 是一个控制连接在不活动状态下的超时时间的参数。当连接在一段时间内没有活动时，MySQL 会自动关闭连接。这个参数的默认值是 28800 秒（8 小时），即连接在 8 小时内没有任何活动，就会被自动关闭。

-- 方法一：使用 SHOW VARIABLES
-- MySQL 提供了 SHOW VARIABLES 命令，可以用来查看数据库的各种配置参数，包括 wait_timeout。
SHOW VARIABLES LIKE 'wait_timeout';
-- 1.这会返回一个结果集，其中包含了 wait_timeout 参数的当前值。-- 方法二：查询 INFORMATION_SCHEMA
-- 另一种方法是查询 INFORMATION_SCHEMA 数据库中的表 VARIABLES。
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'wait_timeout';
-- 1.这个语句会返回 wait_timeout 参数的当前值，以及其他相关的信息。-- 方法三：直接查看配置文件
-- wait_timeout 参数可以在 MySQL 的配置文件中进行设置。如果你有访问配置文件的权限，可以直接查看配置文件的内容来获取 wait_timeout 的值。-- 对于 Linux 系统，默认的配置文件路径是 /etc/my.cnf 或 /etc/mysql/my.cnf，对于 Windows 系统，默认的配置文件路径是 C:\Program Files\MySQL\MySQL Server X.X\my.ini。

3、InnoDB 相关参数

3.1、innodb_buffer_pool_size

        innodb_buffer_pool_size 是 MySQL 中 InnoDB 存储引擎的一个重要参数，它定义了 InnoDB 缓冲池的大小。缓冲池是一个内存区域，用于缓存数据库中的数据和索引，以加快数据库的读取操作。以下是 innodb_buffer_pool_size 的作用：

1. 提高读取性能：将常用的数据和索引加载到内存缓冲池中，可以减少磁盘 I/O 操作，从而提高读取性能。因为内存的读取速度远远快于磁盘的读取速度。

2. 减少磁盘访问：通过增大缓冲池的大小，可以将更多的数据和索引存放在内存中，减少对磁盘的访问次数，从而降低磁盘 I/O 的负载。

3. 提高查询性能：由于数据和索引在缓冲池中的存在，查询操作可以更快速地在内存中完成，而不需要频繁地读取磁盘上的数据。

4. 改善并发性能：缓冲池的存在可以减少对磁盘的并发访问，提高并发读取的性能，从而改善数据库的并发处理能力。

        需要注意的是，设置合适的 innodb_buffer_pool_size 大小需要考虑到服务器的可用内存和数据库的工作负载。如果设置过小，可能导致频繁的磁盘 I/O，降低性能；如果设置过大，可能会占用过多的内存资源，导致其他应用程序受影响。因此，需要根据实际情况进行调整和优化。

        InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

-- 通过sql修改，重启后会失效
-- 修改缓冲池大小为4G
SET GLOBAL innodb_buffer_pool_size = 4294967296;

3.2、innodb_flush_log_at_trx_commit

        是指：ib_logfile的刷新方式( ib_logfile：记录的是redo log和undo log的信息，是重做日志写入磁盘的过程。用来控制缓冲区中的数据写入到日志文件，以及日志文件数据刷新到磁盘(flush)的操作时机。对这个参数的设置值，可以对数据库在性能与数据安全之间，进行折中。

• 当参数是0：日志缓冲数据会，每秒一次地写入到日志文件，并且把日志文件刷新到磁盘操作。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
• 当参数是1：每次事务提交时，日志缓冲被写到日志文件，并且对日志文件做磁盘刷新操作，该模式为系统默认。但由于每次事务都需要进行磁盘I/O，所以也最慢。
• 当参数是2：每次事务提交时，日志缓冲被写到日志文件，但不对日志文件做磁盘刷新操作。对日志文件每秒执行一次，刷到磁盘操作。

        当设置 innodb_flush_log_at_trx_commit=1 时， 是默认值，也是最安全的设置，但是在这种模式下性能有一定的损失。 如果设置成0或者2 性能会有所改善，但有数据丢失的风险。
　　设置成0则数据库崩溃的时候，那些没有被写入日志文件的事务丢失，最多丢失1秒钟的事务，是最不安全的，但也是效率最高的。
　　设置成2则只是没有刷新到磁盘，但已经写入日志文件，所以只要操作系统没有崩溃， 那么并没有数据丢失, 比设置成0更安全。在mysql官方中为了确保事务的持久性和复制设置的一致性，都是建议将这个参数值设置为1;

参数值	数据安全性	I/O性能
0	安全最差。当数据库崩溃,有丢失1秒钟的事务风险	最优
1	安全最好。无丢失数据	最差
2	安全折中。当操作系统崩溃, 有丢失1秒钟的事务风险	折中

-- 查看
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

3.3、innodb_log_buffer_size

        innodb_log_buffer_size 决定 innodb 重做日志缓存池的大小，默认是 8MB。对于可能产生大量更新记录的大事务，增加 innodb_log_buffer_size 的大小，可以避免 innodb 在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新，插入或删除大量记录的应用，可以通过增大 innodb_log_buffer_size 来减少日志写磁盘操作，提高事务处理性能。

4、参数分类

4.1、通用日志（general log）

          记录服务端收到的每个命令，由于会记录所有命令，开启会对性能造成影响，默认关闭。可通过以下方式配置使用：

-- 使用命令
-- 查看通用日志相关参数
show global variables like  '%general%'; general_log ：是否开启通用日志general_log_file 通用日志文件位置set global general_log=on; -- 开启
set global general_log=off; -- 关闭
set global general_log_file='/data/mysql/log/general.log' -- 设置日志路径show variables like 'log_output'; -- 查看通用日志记录格式，分为file，与table两种
set global log_output='file'; -- 使用文件方式保存日志
set global log_output='table'; -- 使用表方式保存日志，会保存到mysql库的general_log表中-- 使用配置文件
[mysqld]
general_log = 1  -- 默认为0不开启，1开启
general_log_file = /data/mysql/log/general.log 

4.2、慢查询日志（slow query log）

        开启后会记录超过long_query_time查询时间的sql语句，可通过如下方式使用：

-- 使用命令
show global variables like '%slow%'; -- 查看慢查询日志相关配置log_slow_admin_statements ：ddl慢查询是否开启          log_slow_slave_statements ：从库慢查询是否开启                   slow_launch_time ：创建线程的阈值，超过该时间slow_launch_time+1                            slow_query_log ：慢查询是否开启                      slow_query_log_file：慢查询日志位置show global variables like 'long_query_time'; -- 查看慢查询时间
set global long_query_time=3; -- 设置慢查询时间
set global slow_query_log=on; -- 开启
set global slow_query_log=off; -- 关闭
set global slow_query_log_file='/data/mysql/log/slow.log' -- 设置日志路径show variables like 'log_output'; -- 查看日志记录格式，分为file，与table两种
set global log_output='file'; -- 使用文件方式保存日志
set global log_output='table'; -- 使用表方式保存日志，会保存到mysql库的slow_log表中-- 使用配置文件
[mysqld]
slow_query_log = 1 0不开启，1开启
slow_query_log_file = /data/mysql/log/slow.log 
long_query_time = 3  

4.3、中继日志（relay log）

        用于主从同步，从库读取主库二进制日志并且保存到本地，然后从库线程读取relay log日志内容在从库中执行。日志格式与binlog相同可通过mysqlbinlog读取：

-- 使用命令
show global variables like '%relay%'; -- 查看中继日志相关配置max_relay_log_size ：日志最大值，为0时等于max_binlog_size，不为0时，当日志超过此大小循环写入。      relay_log：中继日志名称，为空是在数据文件目录下，文件名为host_name-relay-bin            relay_log_basename：中继日志文件的基本名称和完整路径    relay_log_index：中继日志索引文件的名称           relay_log_info_file ：设置relay-log.info的位置和名称     relay_log_info_repository ：中继日志类型table或file relay_log_purge：是否自动清空中继日志，默认开启          relay_log_recovery ：从库宕机，relay log损坏，导致一部分中继日志没处理，则放弃所有未执行的relay log，重新从主库获取，使用时需要开启relay_log_purge       relay_log_space_limit ：中继日志的最大空间量   sync_relay_log：刷盘策略，为0时不主动刷盘，依赖操作系统定期刷盘，为1时表示每次写入都刷盘      sync_relay_log_info：与sync_relay_log类似，刷盘时机取决于是否有事务-- 可通过 set global修改对应参数
set global sync_relay_log=1; -- 修改刷盘策略
set global relay_log_purge=off; -- 关闭-- 使用配置文件
[mysqld]
relay_log_purge= 1 0不开启，1开启
sync_relay_log= 1

4.4、回滚日志（undo log）

        innodb 特有，在数据变动时，将对变动进行逆向记录，用来保证事务原子性以及实现mvcc。

show global variables like '%undo%'; innodb_max_undo_log_size ：日志最大值，默认1G，开启innodb_undo_log_truncate后会进行自动回收innodb_undo_directory：日志位置，默认数据目录（datadir）下           innodb_undo_log_truncate：是否开启自动回收。开启后需要innodb_undo_tablespaces大于2才可以回收。   innodb_undo_logs：回滚段数量默认为128，innodb_undo_logs>=35，32个给临时表空间使用，1个给系统表空间使用，2个给自动回收表空间使用，由于每个回滚段由1024个undo segment组成，该值为128则最大事务为 93*1024innodb_undo_tablespaces：表示undolog对应的表空间文件的个数

4.5、重做日志（redo log）

        innodb 特有的引擎层物理日志，用来保证事务持久性，redo log 包括内存缓冲区（innodb_log_buffer_size）与磁盘日志文件（innodb_log_file_size ），在写磁盘日志文件时采用循环写入方式，当写到结尾时，会回到文件头覆盖写入。由于磁盘日志为顺序写入，可以提高性能。

show global variables like '%innodb_flush_log_at_trx_commit%'; -- 查看redo log 刷盘策略，
为0时表示每次事务提交都把写入redo log buffer中
为1时每次事务提交都写入 redo log file中进行刷盘，保证数据一致性
为2时每次提交事务都写入redo log file中但是不进行刷盘，此时数据在磁盘缓存（page chche）中show global variables like '%innodb_flush_log_at_timeout%';
日志刷盘时间，默认1秒show global variables like '%innodb_log%';innodb_log_buffer_size：redo log内存区大小innodb_log_checksums：是否启用重做日志的校验innodb_log_compressed_pages：是否启用压缩innodb_log_file_size：每个日志文件大小innodb_log_files_in_group：日志文件数量，可在innodb_log_group_home_dir下查看，默认两个ib_logfile0，ib_logfile1innodb_log_group_home_dir：日志文件路径innodb_log_write_ahead_size：预写块大小，以字节为单位。避免read-on-write

4.6、二进制日志（binlog）

        server层逻辑日志，记录sql语句的逻辑，在事务执行过程中，先写入到binlog cache，事务提交时再把binlog cache写入到binlog文件中，之后删除binglog cache中内容，当binlog cache不够大时，会先写入磁盘临时文件。binlog文件为追加写入，当文件写到一定大小写下一个文件。

SHOW GLOBAL  VARIABLES LIKE "%binlog%"binlog_cache_size：每个线程binlog cache 大小，必须是4096倍数binlog_checksum：验证算法，默认为crc32，为每一个binlog event写一个校验码，设置为none时只校验长度，不生成校验码。binlog_direct_non_transactional_updates：对非事务表操作是否记入binlog，个人理解用处不大，此参数只在binlog_format为statement时生效。binlog_error_action：默认为ABORT_SERVER写入binlog失败时主动关闭实例，重启时恢复，也可设置为IGNORE_ERROR写入binlog失败时不写入，继续对外提供服务binlog_format：分为statement，row，mixed格式，statement记录sql不保证数据一致性。row记录行记录，但比较费空间，保证数据 一致性。mixed为混合模式，由mysql判断存储sql还是行记录。binlog_group_commit_sync_delay：组提交延迟时间。binlog_group_commit_sync_no_delay_count：组提交延迟数量，累计到延迟数量忽略组提交延迟时间设置。binlog_gtid_simple_recovery：是否开启gtid简单恢复。binlog_order_commits：binlog记录顺序是否与事务顺序相同。binlog_row_image：控制binlog记录详细程度，binglog记录为row格式下生效full ：记录一行数据所有列信息。minimal ：只记录该行唯一表示和被修改的信息noblob ：记录一行数据所有列信息BLOB 和 TEXT类型除外binlog_rows_query_log_events：对于row格式的binlog模式，是否记录binlog对应的SQLbinlog_stmt_cache_size：线程私有，控制事务里有非事务语句缓存区大小。binlog_transaction_dependency_history_size：保存事务哈希值上限max_binlog_cache_size：与binlog_cache_size类似，每个线程binglog cache超过max_binlog_cache_size设置会抛异常。max_binlog_size：binlog日志文件大小，超过此值会打开新文件继续写入。max_binlog_stmt_cache_size：与binlog_stmt_cache_size类似，超过会抛异常sync_binlog：binlog：刷盘策略sync_binlog=0，禁止binlog刷盘，依赖操作系统刷盘。sync_binlog=1，每次提交事务都进行刷盘。sync_binlog=N，写入N次进行刷盘。SHOW GLOBAL  VARIABLES LIKE "%log_bin%"log_bin:是否开启bingloglog_bin_basename：binlog文件路径名称log_bin_index: binglog：索引文件路径名称log_bin_trust_function_creators:是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。log_bin_use_v1_row_events:是否启用版本2日志记录，mysql5.6.6以前使用版本1

这篇关于【MySQL】A01、性能优化-结构设计与配置的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---