MySQL-第十三章-核心参数优化（详解）

show variables like 'max_connections';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | max_connections | 151   |
    +-----------------+-------+
show status like 'Max_used_connections';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | Max_used_connections | 101   |
    +----------------------+-------+

（3）修改方式举例

vim /etc/my.cnf 
Max_connections=1024

补充:

    1.开启数据库时,我们可以临时设置一个比较大的测试值

    2.观察show status like 'Max_used_connections';变化

    3.如果max_used_connections跟max_connections相同,

    那么就是max_connections设置过低或者超过服务器的负载上限了，

    低于10%则设置过大.

back_log***

（1）简介

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

back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它

（2）判断依据

show full processlist

发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值

（3）修改方式举例

vim /etc/my.cnf 
back_log=1024

wait_timeout和interactive_timeout****

（1）简介

wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。

wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用

（2）设置建议

如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接，一般希望wait_timeout尽可能低

（3）修改方式举例

wait_timeout=60
interactive_timeout=1200

长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。

一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。

另外还可以使用类外的参数弥补。

key_buffer_size*****

（1）简介

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

《1》此参数与myisam表的索引有关

《2》临时表的创建有关（多表链接、子查询中、union）

     在有以上查询语句出现的时候，需要创建临时表，用完之后会被丢弃

     临时表有两种创建方式：

                        内存中------->key_buffer_size

                        磁盘上------->ibdata1(5.6)

ibtmp1 (5.7）

（2）设置依据

通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> 
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 10    |
| Key_reads         | 2     |
+-------------------+-------+
2 rows in set (0.00 sec)

mysql> 
一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
控制在 5%以内 。
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知：

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> 
通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以内
看以下例子：
在调用mysqldump备份数据时，大概执行步骤如下：
180322 17:39:33       7 Connect     root@localhost on
7 Query       /*!40100 SET @@SQL_MODE='' */
7 Init DB     guo
7 Query       SHOW TABLES LIKE 'guo'
7 Query       LOCK TABLES `guo` READ /*!32311 LOCAL */
7 Query       SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query       show create table `guo`
7 Query       show fields from `guo`
7 Query       show table status like 'guo'
7 Query       SELECT /*!40001 SQL_NO_CACHE */ * FROM `guo`
7 Query       UNLOCK TABLES
7 Quit

其中，有一步是：show fields from `guo`。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。

所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。

（3）配置方法

key_buffer_size=64M

query_cache_size ***

（1）简介：

查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。

SQL层：

select * from t1 where name=:NAME;

select * from t1 where name=:NAME;

1、查询完结果之后，会对SQL语句进行hash运算，得出hash值,我们把他称之为SQL_ID

2、会将存储引擎返回的结果+SQL_ID存储到缓存中。

存储方式：

例子：select * from t1 where id=10; 100次

1、将select * from t1 where id=10; 进行hash运算计算出一串hash值，我们把它称之为“SQL_ID"

2、将存储引擎返回上来的表的内容+SQLID存储到查询缓存中

使用方式：

1、一条SQL执行时，进行hash运算，得出SQLID，去找query cache

2、如果cache中有，则直接返回数据行，如果没有，就走原有的SQL执行流程

一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。

注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。

（2）判断依据

mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031360 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 2002    |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

---------------------状态说明--------------------

Qcache_free_blocks：缓存中相邻内存块的个数。

如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

注：当一个表被更新之后，和它相关的cache

blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks

Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。

Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

Qcache_lowmem_prunes：

多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）

Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；

Qcache_total_blocks：当前Query Cache 中的block 数量；。

Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

    90/         10000             0             90

如果出现hits比例过低，其实就可以关闭查询缓存了。使用redis专门缓存数据库

Qcache_free_blocks    来判断碎片

Qcache_free_memory   +   Qcache_lowmem_prunes 来判断内存够不够

Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

（3）配置示例

mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

-------------------配置说明-------------------------------

以上信息可以看出query_cache_type为off表示不缓存任何查询

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)

query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。

如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=128M

query_cache_type=1

max_connect_errors ***

max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

max_connect_errors=999999

max_allowed_packet *****

（1）简介：

mysql根据配置文件会限制，server接受的数据包大小。

（2）配置依据：

有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数

（3）配置方法：

max_allowed_packet=32M

net_read_timeout

数据读取超时时间。在终止读之前，从一个连接获得数据而等待的时间秒数；当服务正在从客户端读取数据时，net_read_timeout控制何时超时。即客户端执行数据读取，等待多少秒仍未执行成功时自动断开连接

net_write_timeout

数据库写超时时间。和net_read_timeout意义类似，在终止写之前，等待多少秒把block写到连接；当服务正在写数据到客户端时，net_write_timeout控制何时超时

AFTER_COMMIT

master将每个事务写入binlog（sync_binlog=1），传递到slave刷新到磁盘(sync_relay=1)，同时主库提交事务。master等待slave反馈收到relay log，只有收到ACK后master才将commit OK结果反馈给客户端。

AFTER_SYNC

master将每个事务写入binlog , 传递到slave刷新到磁盘(relay log)。master等待slave反馈接收到relay log的ack之后，再提交事务并且返回commit OK结果给客户端。即使主库crash，所有在主库上已经提交的事务都能保证已经同步到slave的relay log中

8.2 Server层

sql_safe_updates=1 *****

如果设置了sql_safe_updates=1，那么update语句必须满足如下条件之一才能执行成功

1)使用where子句,并且where子句中列必须为prefix索引列

2)使用limit

3)同时使用where子句和limit(此时where子句中列可以不是索引列)

delete语句必须满足如下条件之一才能执行成功

1)使用where子句,并且where子句中列必须为prefix索引列

2)同时使用where子句和limit(此时where子句中列可以不是索引列)

slow_query_log=ON

记录SQL执行超过一定时间的参数

slow_query_log_file=/data/3307/slow.log *****

指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

long_query_time=1*****

设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s

log_queries_not_using_indexes=ON *****

不使用索引的慢查询日志是否记录到索引

log_throttle_queries_not_using_indexes = 10*****

设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间

sort_buffer_size= 1M

是系统中对数据进行排序的时候用到的Buffer

我们一般可以通过增大sort buffer的大小来提高order by或者group by的处理性能

（1）简介：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速

ORDER BY

GROUP BY

distinct

union

（2）配置依据

Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。

列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存

（3）配置方法

修改/etc/my.cnf文件，在[mysqld]下面添加如下：

sort_buffer_size=1M

join_buffer_size= 1M

参与join的每一个表都需要一个join buffer，

如果应用中，很少出现join语句，则可以不用太在乎join_buffer_size参数的设置大小。

如果join语句不是很少的话，个人建议可以适当增大join_buffer_size到1MB左右，如果内存充足可以设置为2MB

例如：

select a.name,b.name from a join b on a.id=b.id where xxxx

用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

尽量在SQL与方面进行优化，效果较为明显。

优化的方法：在on条件列加索引，至少应当是有MUL索引

read_buffer= 1M

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享

read_rnd_buffer = 1M

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

注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

tmp_table= 16M

临时表的内存缓存大小

heap_table= 16M

内存表缓存大小

HEAP表是访问数据速度最快的MySQL表，他使用保存在内存中的散列索引。但如果MySQL或者服务器重新启动，表中数据将会丢失.

max_execution_time= 28800

参数max_execution_time用来控制select语句的最大执行时间，单位是毫秒，可以动态修改，分为session和global两种级别，如果设置为0的话，则证明不设限制。

lock_wait_timeout= 60*****

事务锁超时时间

lower_case_table_names=1 *****

此参数不可以动态修改，必须重启数据库

=0 表名存储为给定的大小和比较是区分大小写的

=1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写

=2 表名存储为给定的大小写但是比较的时候是小写的

thread_cache_size =64****

(1)简介

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.

（2）配置依据

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。

服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

试图连接到MySQL(不管是否连接成功)的连接数

mysql> show status like 'threads_%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 8     |

| Threads_connected | 2     |

| Threads_created   | 4783 |

| Threads_running   | 1     |

+-------------------+-------+

4 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected:代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。

Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗cpu SYS资源，可以适当增加配置文件中thread_cache_size值。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

(3)配置方法：

thread_cache_size=32

整理：

Threads_created ：一般在架构设计阶段，会设置一个测试值，做压力测试。

结合zabbix监控，看一段时间内此状态的变化。

如果在一段时间内，Threads_created趋于平稳，说明对应参数设定是OK。

如果一直陡峭的增长，或者出现大量峰值，那么继续增加此值的大小，在系统资源够用的情况下（内存）

log_timestamps =SYSTEM*****

log_timestamps 这个参数主要是控制 error log、slow_log、genera log，等等记录日志的显示时间参数，但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。在查询行的时候，可以使用 CONVERT_TZ() 函数，或者设置会话级别的系统变量 time_zone 来转换成所需要的时区。

该参数全局有效，可以被设置的值有：UTC 和 SYSTEM，默认使用 UTC。它还支持动态设置，不过建议大家在配置文件中就写上，以免重启之后造成不必要的麻烦。

init_connect="set names utf8" *****

功能：1、用户追踪 2、在线人数 3、数据库初始化

注意：1、root超级管理员权限 init_connect 不会执行 (原因:当init_connect设置有误时,root超级管理员可进行修改)

2、用户需要有insert 权限否则登录失败

init_connect='SET AUTOCOMMIT=0'

init_connect='SET NAMES utf8' #强行设置编码为utf8，避免乱码

event_scheduler =OFF

事件调度器，MySQL定时器的开关，类似于windows操作系统的定时任务的概念，指定某个时间点执行一次定时任务，或者每隔一段时间循环执行定时任务

secure-file-priv=/tmp *****

限制LOAD DATA, SELECT ... OUTFILE, and LOAD_FILE()传到哪个指定目录的

当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下

当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制

binlog_expire_logs_seconds =2592000*****

在MySQL8.0 版本中新增参数binlog_expire_logs_seconds，可以精确到秒，而此前版本中expire_logs_days的单位为天，最小值为1，在MySQL8.0 GA中此参数标记为deprecated 。

在MySQL 8.0 GA版本默认使用binlog_expire_logs_seconds，时间为2592000秒，30天

若在启动时binlog_expire_logs_seconds和expire_logs_days参数都设置为非0值则使用binlog_expire_logs_seconds值，expire_logs_days值则失效并对其发出告警信息。

若要关闭自动清除binlog文件的功能则需要显示指定binlog_expire_logs_seconds=0 并且不设置expire_logs_days的值。

sync_binlog =1 *****

sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

log-bin=/data/3307/mysql-bin

binlog二进制日志文件存放的位置点以及前缀名

log-bin-index=/data/3307/mysql-bin.index

存放binlog二进制日志文件名的索引文件位置点

max_binlog_size=500M

指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。

binlog_format=ROW*****

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

bulk_insert_buffer_size = 8M **

批量插入数据缓存大小，可以有效提高插入效率，默认为8M

tokuDB percona

myrocks

RocksDB

TiDB

MongoDB

8.3 存储引擎层

transaction-isolation ="READ-COMMITTED" *****

设置事务的隔离级别为RC

innodb_data_home_dir =/xxx

innodb_data_file_path用来指定innodb 共享表空间文件，如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1

作为innodb tablespace。

虽然不指定innodb_data_home_dir和指定它为空显示是一样的，但是作用确不一样，如果不指定那么所有的innodb tablespace 文件只能存放在datadir目录下。

如果显示指定为空，则可以为innodb tablespace 文件指定不同的路径

innodb_log_group_home_dir =/xxx

放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能

innodb_log_file_size =2048M

设置 ib_logfile0 ib_logfile1

此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

innodb_log_file_size = 100M

innodb_log_files_in_group =3

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

innodb_flush_log_at_trx_commit =2 *****

（1）简介

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。

0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；

1，

每次事务的提交都会引起redo日志文件写入、flush磁盘的操作，确保了事务的ACID；

2，每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

（2）配置依据

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

（3）配置方法

innodb_flush_log_at_trx_commit=1

双1标准中的一个1

innodb_flush_method =O_DIRECT *****

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成

O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

innodb_io_capacity =1000 *****

innodb_io_capacity参数可以动态调整刷新脏页的数量

innodb_io_capacity参数默认是200，单位是页。该参数设置的大小取决于硬盘的IOPS，即每秒的输入输出量（或读写次数）

innodb_io_capacity_max=4000

在压力下，控制当刷新脏数据时MySQL每秒执行的写IO量

解释一下什么叫“在压力下”，MySQL中称为”紧急情况”，是当MySQL在后台刷新时，它需要刷新一些数据为了让新的写操作进来

innodb_buffer_pool_size =64G *****

（1）简介

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。

（2）配置依据：

InnoDB使用该参数指定大小的内存来缓冲数据和索引。

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。

（3）配置方法

innodb_buffer_pool_size=2048M

innodb_buffer_pool_instances=4 *****

可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写

innodb_log_buffer_size=64M *****

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据：

1、大事务：存储过程调用 CALL

2、多事务

innodb_max_dirty_pages_pct =85 *****

是 MySQL InnoDB 存储引擎非常重要的一个参数，用来控制buffer pool中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会启动刷脏页的操作。该参数只控制脏页百分比，并不会影响刷脏页的速度。

innodb_max_dirty_pages_pct 参数可以动态调整，最小值为0，最大值为99.99，默认值为 75

innodb_lock_wait_timeout =10 *****

设置mysql 事务锁超时时间

innodb_open_files =63000 *****

限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。innodb_open_files的大小对InnoDB效率的影响比较小。但是在InnoDBcrash的情况下，innodb_open_files设置过小会影响recovery的效率。所以用InnoDB的时候还是把innodb_open_files放大一些比较合适。

innodb_page_cleaners =4

Innodb中page clean线程将脏数据写入到磁盘，脏数据写盘后相应的redo就可以覆盖，然后达到redo循环使用的目的

为了提升扩展性和刷脏效率，在5.7.4版本里引入了多个page cleaner线程，从而达到并行刷脏的效果。目前Page cleaner并未和buffer pool绑定，其模型为一个协调线程 + 多个工作线程，协调线程本身也是工作线程。因此如果innodb_page_cleaners设置为4，那么就是一个协调线程，加3个工作线程，工作方式为生产者-消费者。工作队列长度为buffer pool instance的个数，使用一个全局slot数组表示。

innodb_sort_buffer_size =64M

在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小。利用这块内存把数据读进来进行内部排序然后写入磁盘。这个参数只会在创建索引的过程中被使用，不会用在后面的维护操作；在索引创建完毕后innodb_sort_buffer会被释放

innodb_print_all_deadlocks =1

当mysql 数据库发生死锁时， innodb status 里面会记录最后一次死锁的相关信息，但mysql 错误日志里面

不会记录死锁相关信息，要想记录，启动 innodb_print_all_deadlocks 参数

innodb_rollback_on_timeout=ON

在MySQL 5.6&5.7中默认值为OFF，当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON，则事务超时后将导致InnoDB中止并回滚整个事务。

innodb_deadlock_detect=ON

该选项使用了禁用MySQL的死锁检测功能的。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度减慢。有时，当发生死锁时，如果禁用了死锁检测则可能会更有效，这样可以依赖innodb_lock_wait_timeout的设置进行事务回滚

innodb_thread_concurrency ***

（1）简介

此参数用来设置innodb线程的并发数量，默认值为0表示不限制。

（2）配置依据

在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：

如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；

如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，

并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，

例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，

性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，

建议设置innodb_thread_concurrency参数为80，以避免影响性能。

如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），

建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），

如果你的目标是将MySQL与其他应用隔离，你可以l考虑绑定mysqld进程到专有的虚拟CPU。

但是需要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，

你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。

在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。

定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。

128   -----> top cpu

设置标准：

1、当前系统cpu使用情况，均不均匀

top

2、当前的连接数，有没有达到顶峰

show status like 'threads_%';

show processlist;

（3）配置方法：

innodb_thread_concurrency=8

方法:

    1. 看top ,观察每个cpu的各自的负载情况

    2. 发现不平均,先设置参数为cpu个数,然后不断增加(一倍)这个数值

    3. 一直观察top状态,直到达到比较均匀时,说明已经到位了.

8.4 主从复制（半同步，多源，无损）

relay_log =/opt/log/mysql/blog/relay

#中继日志存放位置

relay_log_index =/opt/log/mysql/blog/relay.index

#存放中继日志文件名的索引文件

max_relay_log_size =500M

#标记relay log 允许的最大值

relay_log_recovery =ON

#当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启。

rpl_semi_sync_master_enabled =ON

#控制是否在主库上启用半同步复制。要启用或禁用插件，请分别将此变量设置为1或0。默认值是0（关闭）

rpl_semi_sync_master_timeout =1000

#一个以毫秒为单位的值，用于控制主服务器等待来自从服务器的确认提交并恢复到异步复制的时间，超过这个值就是超时。默认值是10000（10秒）。超时之后，就从半同步复制，返回到异步复制

rpl_semi_sync_master_trace_level =32

#这是一个日志打印参数，默认即可

rpl_semi_sync_master_wait_for_slave_count =1

#该变量控制slave应答的数量，默认是1，表示master接收到几个slave应答后才commit。在多从的环境下，设置大于1可以提高数据的可靠性

rpl_semi_sync_master_wait_no_slave =ON

#是否允许master每个事务提交后都要等待slave的接收确认信号。默认为on，每一个事务都会等待。如果为off，则slave追赶上后，也不会开启半同步复制模式，需要手工开启

rpl_semi_sync_master_wait_point =AFTER_SYNC

#控制 master 在哪个环节接收 slave ack，master 接收到 ack 后返回状态给客户端。

此参数一共有两个选项 AFTER_SYNC（default） & AFTER_COMMIT

rpl_semi_sync_slave_enabled =ON

#作用和参数 rpl_semi_sync_master_enabled相似，但是控制的是从库的插件

rpl_semi_sync_slave_trace_level =32

#用于开启半同步复制模式时的调试级别，默认是32

binlog_group_commit_sync_delay =1

#表示binlog提交后等待延迟多少时间再同步到磁盘，默认0，不延迟。设置延迟可以让多个事务在用一时刻提交，提高binlog组提交的并发数和效率，提高slave的吞吐量

binlog_group_commit_sync_no_delay_count =1000

#表示等待延迟提交的最大事务数，如果上面参数的时间没到，但事务数到了，则直接同步到磁盘。若binlog_group_commit_sync_delay没有开启，则该参数也不会开启。

gtid_mode =ON

#开启GTID模式

enforce_gtid_consistency =ON

#当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporary table语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行

skip-slave-start =1

#该参数能够让数据库实例在启动的时候禁止建立主从关系，即禁止开启mysql的io线程和sql线程，用于搭建从库的时候需要，防止数据库启动的时候会自动开启了这两个线程，进而导致破坏了从库

read_only =ON

#开启只读

super_read_only =ON

#如果从库也设置了super_read_only=on,可很好确保了从库不被误写数据，即便是变更时候，不小心在从库执行了super权限用户语句也会失败，不会造成主从不一致

log_slave_updates =ON

# a) M01同步从M02同步数据过来的时候，log_slave_updates参数用来控制M01是否把所有的操作写入到binary log，默认的情况下mysql是关闭的;

b) R01数据的更新需要通过读取到M01的binary log才能进行更新，这个时候M01是没有写binary log的，所以当数据从M02写入的时候，R01也就没有更新了

server_id =2330602

#用来表示某个数据库实例

report_host =xxxx

#将IP地址暴露给主库

report_port =3306

#将端口信息暴露给主库

slave_parallel_type =LOGICAL_CLOCK

slave_parallel_workers =4

#4为设置的并发线程个数，之后根据项目对数据传输的具体要求再更改

#一个 schema 下，slave_parallel_workers中的worker线程并发执行relay log中主库提交的事务

master_info_repository =TABLE

#分别是file和table，该参数决定了slave记录master的状态，如果参数是file，就会创建master.info文件，如果参数值是table，就在mysql中创建slave_master_info的表

relay_log_info_repository =TABLE

#如果relay_log_info_repository=file，就会创建一个realy-log.info，如果relay_log_info_repository=table，就会创建mysql.slave_relay_info表来记录同步的位置信息

8.5 安全参数

Innodb_flush_method=(O_DIRECT, fsync)

1、fsync    ：

（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘

（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘

但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘

2、 Innodb_flush_method=O_DIRECT

（1）在数据页需要持久化时，直接写入磁盘

（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘

但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘

最安全模式：

innodb_flush_log_at_trx_commit=1

innodb_flush_method=O_DIRECT

最高性能模式：

innodb_flush_log_at_trx_commit=0

innodb_flush_method=fsync



一般情况下，我们更偏向于安全。

“双一标准”

innodb_flush_log_at_trx_commit=1        ***************

sync_binlog=1 ***************

innodb_flush_method=O_DIRECT

8.6 其他

客户端配置：

[mysql]

no-auto-rehash # auto-rehash是自动补全的意思，就像我们在linux命令行里输入命令的时候，使用tab键的功能是一样的

9 开发规范

9.1 字段规范

1. 每个表建议在30个字段以内。

2. 需要存储emoji字符的，则选择utf8mb4字符集。

3. 机密数据，加密后存储。

4. 整型数据，默认加上UNSIGNED。

5. 存储IPV4地址建议用bigINT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。

6. 如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。

7. 选择尽可能小的数据类型，用于节省磁盘和内存空间。

8. 存储浮点数，可以放大倍数存储。

9. 每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。

10. 每个列使用not null，或增加默认值

9.2 SQL语句规范

### 1. 去掉不必要的括号

如：      ((a AND b) AND c OR (((a AND b) AND (c AND d))))

修改成    (a AND b AND c) OR (a AND b AND c AND d)

### 2. 去掉重叠条件

如：      (a<b AND b=c) AND a=5

修改成    b>5 AND b=c AND a=5

如：      (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)

修改成    B=5 OR B=6

### 3. 避免使用not in、not exists 、<>、like %%

### 4. 多表连接，小表驱动大表

### 5. 减少临时表应用，优化order by 、group by、union、distinct、join等

### 6. 减少语句查询范围，精确查询条件

### 7. 多条件，符合联合索引最左原则

### 8. 查询条件减少使用函数、拼接字符等条件、条件隐式转换

### 9. union all 替代 union

### 10.减少having子句使用

### 11.如非必须不使用 for update语句

### 12.update和delete，开启安全更新参数

### 13.减少inset ... select语句应用

### 14.使用load 替代insert录入大数据

### 15.导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer、关闭autocommit、RC级别可以提高效率

### 16.优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询

limit 5000000,10     limit 10 , 200

### 17. DDL执行前要审核

### 18. 多表连接语句执行前要看执行计划

10. 索引优化

1. 非唯一索引按照“i_字段名称_字段名称[_字段名]”进行命名。

2. 唯一索引按照“u_字段名称_字段名称[_字段名]”进行命名。

3. 索引名称使用小写。

4. 索引中的字段数不超过5个。

5. 唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。

6. 没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。

7. 唯一键不和主键重复。

8. 索引选择度高的列作为联合索引最左条件

9. ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

10. 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。

查询性能问题无法解决的，应从产品设计上进行重构。

11. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

12. UPDATE、DELETE语句需要根据WHERE条件添加索引。

13. 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。

14. 下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。

CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

url VARCHAR(255) NOT NULL DEFAULT 0,

url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,

index idx_url(url_crc32));

15. 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

16. 合理利用覆盖索引，减少回表。

17. 减少冗余索引和使用率较低的索引

mysql> select * from schema_unused_indexes;

mysql> select * from schema_redundant_indexes\G

11. 锁优化

11.1 全局锁 Global Read lock

11.1.1 介绍

全局读锁。

加锁方法： FTWRL，flush tables with read lock.

解锁方法： unlock tables;

出现场景：

mysqldump --master-data

xtrabackup（8.0之前早期版本）等备份时。

属于类型： MDL（matedatalock）层面锁

影响情况：加锁期间，阻塞所有事务写入，阻塞所有已有事务commit。

MDL，等待时间受 lock_wait_timeout=31536000

11.1.2 检测方法

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

mysql> select * from performance_schema.metadata_locks;

mysql> select OBJECT_SCHEMA ,OBJECT_NAME ,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS ,OWNER_THREAD_ID,OWNER_EVENT_ID from performance_schema.metadata_locks;

mysql> show processlist;
mysql> select * from sys.schema_table_lock_waits;

11.1.3 故障案例

案例1：

5.7 xtrabackup/mysqldump备份时数据库出现hang状态，所有查询都不能进行

session1: 模拟一个大的查询或事务

mysql> select id,sleep(100) from city where id<100 for update ;

session2: 模拟备份时的FTWRL

mysql> flush tables with read lock;

-- 此时发现命令被阻塞

session3: 发起查询，发现被阻塞

mysql> select * from world.city where id=1 for update;

结论：备份时，一定要选择业务不繁忙期间，否则有可能会阻塞正常业务。

案例2：

5.7版本 innobackupex备份全库，进程死了，mysql里就是全库读锁，后边insert 全阻塞了

show processlist ----> select * from performance_schema.metadata_locks; ---> pending ---->granted ----> OWNER_THREAD_ID: 66

----> select * from threads \G ----->processlist_Id----> show processlist -----> kill processlist_Id

11.2 row lock wait

11.2.1 介绍

record lock 、gap、next lock

都是基于索引加锁,与事务隔离级别有关。

11.2.2 行锁监控及分析

## 1. 看有没有锁等待

SHOW STATUS LIKE 'innodb_row_lock%';

## 2. 查看哪个事务在等待(被阻塞了)

USE information_schema

SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

-------------------------

trx_id : 事务ID号

trx_state : 当前事务的状态

trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )

trx_query : 当前被阻塞的操作(一般是要丢给开发的)

-------------------------

## 3. 查看锁源,谁锁的我!

SELECT * FROM sys.innodb_lock_waits;     ## ====>被锁的和锁定它的之间关系

locked_table    : 哪张表出现的等待

waiting_trx_id  : 等待的事务(与上个视图trx_id 对应)

waiting_pid     : 等待的线程号(与上个视图trx_mysql_thread_id)

blocking_trx_id : 锁源的事务ID

blocking_pid    : 锁源的线程号

## 4. 找到锁源的thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=15;

====> 41

## 5. 找到锁源的SQL语句

-- 当前在执行的语句

SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;

-- 执行语句的历史

SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;

得出结果,丢给开发

表信息

被阻塞的

锁源SQL

11.2.3 优化方向

1. 优化索引

2. 减少事务的更新范围

3. RC

4. 拆分语句：

例如： update t1 set num=num+10 where k1 <100; k1 是辅助索引,record lock gap next

   改为:

   select id from t1 where k1 <100; ---> id: 20,30,50

   update t1 set num=num+10   where id in (20,30,50);

11.2.4 死锁监控

show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf 
innodb_print_all_deadlocks = 1

11.3 优化项目:锁的监控及处理

1. 背景:

硬件环境: DELL R720,E系列16核,48G MEM,SAS*900G*6,RAID10

在例行巡检时,发现9-11点时间段的CPU压力非常高(80-90%)

2. 项目的职责

    2.1 通过top详细排查,发现mysqld进程占比达到了700-800%

    2.2 其中有量的CPU是被用作的SYS和WAIT,us处于正常

    2.3 怀疑是MySQL 锁或者SQL语句出了问题

    2.4 经过排查slowlog及锁等待情况,发现有大量锁等待及少量慢语句

    (1) pt-query-diagest 查看慢日志

    (2) 锁等待有没有?

    db03 [(none)]>show status like 'innodb_row_lock%';

    +-------------------------------+-------+

    | Variable_name                 | Value |

    +-------------------------------+-------+

    | Innodb_row_lock_current_waits | 0     |

    | Innodb_row_lock_time          | 0     |

    | Innodb_row_lock_time_avg      | 0     |

    | Innodb_row_lock_time_max      | 0     |

    | Innodb_row_lock_waits         | 0     |

    +-------------------------------+-------+

    情况一:

            有100多个current_waits,说明当前很多锁等待情况

    情况二:

            1000多个lock_waits,说明历史上发生过的锁等待很多

    2.5 查看那个事务在等待(被阻塞了)

    2.6 查看锁源事务信息(谁锁的我)

    2.7 找到锁源的thread_id

    2.8 找到锁源的SQL语句

3. 找到语句之后,和应用开发人员进行协商

    (1)

    开发人员描述,此语句是事务挂起导致

    我们提出建议是临时kill 会话,最终解决问题

    (2)

    开发人员查看后,发现是业务逻辑问题导致的死锁,产生了大量锁等待

    临时解决方案,将阻塞事务的会话kill掉.

    最终解决方案,修改代码中的业务逻辑

项目结果:

    经过排查处理,锁等待的个数减少80%.解决了CPU持续峰值的问题.



锁监控设计到的命令:

show status like 'innodb_rows_lock%'

select * from information_schema.innodb_trx;

select * from sys.innodb_lock_waits;

select * from performance_schema.threads;

select * from performance_schema.events_statements_current;

select * from performance_schema.events_statements_history;

12. 架构优化

高可用架构：

MHA+ProxySQL+GTID

MGR\InnoDB Cluster

PXC

读写分离：

ProxySQL、MySQL-router

NoSQL:

Redis+sentinel,Redis Cluster

MongoDB RS/MongoDB SHARDING Cluster

ES

13. 安全优化

1、使用普通nologin用户管理MySQL

2、合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户可访问。

3、删除数据库匿名用户

4、锁定非活动用户

5、 MySQL尽量不暴露互联网,需要暴露互联网用户需要设置明确白名单、替换MySQL默认端口号、使用ssl连接

6、优化业务代码，防止SQL注入。

CN-FuWei

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL-第十三章-核心参数优化（详解）

目录8. 数据库三层结构及核心参数优化8.1 连接层max_connections=1000 *****back_log***wait_timeout和interactive_timeout****key_buffer_size*****query_cache_size ***max_connect_errors ***max_allowed_packet *****net_read_timeoutnet_write_timeoutAFTER_COMMI.
复制链接

扫一扫