DAY13 - MySQL分区优化与运维工具

1

select count(*) from employees where gender='F';

1
2
3
4
5
6
7
8

create table t1 (id int,name varchar(20),age int)
partition by range (age)
(
partition p01 values less than (10),
partition p02 values less than (20),
partition p03 values less than (30),
partition p04 values less than (maxvalue)
);

1
2
3
4
5
6
7

[root@db-01 xiaoQ]# pwd
/data/3306/data/xiaoQ
[root@db-01 xiaoQ]# ll
-rw-r----- 1 mysql mysql 114688 Jul 18 09:33 t1#p#p01.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 09:33 t1#p#p02.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 09:33 t1#p#p03.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 09:33 t1#p#p04.ibd

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

alter table t2
partition by range (year(from_date))
(
partition p01 values less than (1985),
partition p02 values less than (1986),
partition p03 values less than (1987),
partition p04 values less than (1988),
partition p15 values less than (1999),
partition p16 values less than (MAXVALUE)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

create table t2 (id int,cid int,name varchar(20),pos_date datetime)
partition by list(cid)
(
partition p01 values in (1,2,3),
partition p02 values in (4,5,6),
partition p03 values in (7,8,9)
);
修改大表信息转变为list分区表：
alter table t2
partition by list(cid)
(
partition p01 values in (1,2,3),
partition p02 values in (4,5,6),
partition p03 values in (7,8,9)
);

1
2
3

create table t2 (id int,cid int,name varchar(20),pos_date datetime)
partition by hash(cid)
partitions 4;

1
2
3

create table t2 (id int,cid int,name varchar(20),pos_date datetime)
partition by linear hash(cid)
partitions 4;

1
2
3
4
5
6

create table t2 (id int,cid int,name varchar(20),pos_date datetime)
partition by key(cid)
partitions 4;
create table t2 (id int,cid int,name varchar(20),pos_date datetime)
partition by linear key(cid)
partitions 4;

1
2
3
4
5
6
7
8
9

create table t3 (a int,b int,c int)
partition by range columns(a,b)
(
partition p01 values less than (10,10),
partition p02 values less than (10,20),
partition p03 values less than (10,30),
partition p04 values less than (10,maxvalue),
partition p05 values less than (maxvalue,maxvalue)
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table employees (emp_no int,birth_date date,first_name varchar(20),last_name varchar(20),gender char(1),hire_date date) engine=Innodb
partition by range columns(gender,hire_date)
(
partition p01 values less than ('F','1990-01-01'),
partition p02 values less than ('F','2000-01-01'),
partition p03 values less than ('F',maxvalue),
partition p04 values less than ('M','1990-01-01'),
partition p05 values less than ('M','2000-01-01'),
partition p06 values less than ('M',maxvalue),
partition p07 values less than (maxvalue,maxvalue)
);

1
2
3
4
5
6
7
8
9

create table t5 (id int, updateinfo date)
partition by range(year(updateinfo))
subpartition by hash ( to_days(updateinfo))
subpartitions 2
(
partitions p0 values less than (1990),
partitions p1 values less than (2000),
partitions p2 values less than maxvalue
);

1
2
3
4
5
6
7

[root@db-01 xiaoQ]# ll /data/3306/data/xiaoQ/
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p0#sp#p0sp0.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p0#sp#p0sp1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p1#sp#p1sp0.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p1#sp#p1sp1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p2#sp#p2sp0.ibd
-rw-r----- 1 mysql mysql 114688 Jul 18 11:55 t5#p#p2#sp#p2sp1.ibd

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

create table ts (id int, updateinfo date)
partition by range(YEAR(updateinof ))
subpartition by hash(to_days(updateinfo))
(
partition p0 values less than (1990)
)
subpartition s0
data directory = '/disk0/data'
index directory = '/disk0/idx',
subpartition s1
data directory = '/disk1/data'
index directory = '/disk1/idx',
);
# 实战操作演示：
[root@db-01 var]# cd /var
[root@db-01 var]# mkdir {a..d}/{data,idx} -p
[root@db-01 var]# chown -R mysql. {a..d}
-- 准备好相应的存储目录
[root@db-01 var]# cat /etc/my.cnf
[mysqld]
innodb_directories="/var/a/data;/var/a/idx;/var/b/data;/var/b/idx;/var/c/data;/var/c/idx;/var/d/data;/var/d/idx"
-- 在innodb存储引擎中，需要设置innodb_directories的信任目录信息，才能将子分区文件保存在不同的存储上
-- 官方资料参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html
# 基于MyISAM存储引擎操作时；
create table t6 (id int, updateinfo date)
partition by range(YEAR(updateinfo))
subpartition by hash(to_days(updateinfo))
(
partition p01 values less than (1990)
(
subpartition s0
data directory='/var/a/data'
index directory='/var/a/idx',
subpartition s1
data directory='/var/b/data'
index directory='/var/b/idx'
),
partition p02 values less than (2000)
(
subpartition s2
data directory='/var/c/data'
index directory='/var/c/idx',
subpartition s3
data directory='/var/d/data'
index directory='/var/d/idx'
)
);
# 基于Innodb存储引擎操作时；
create table t6 (id int, updateinfo date)
partition by range(YEAR(updateinfo))
subpartition by hash(to_days(updateinfo))
(
partition p01 values less than (1990)
(
subpartition s0
data directory='/var/a/data',
subpartition s1
data directory='/var/b/data'
),
partition p02 values less than (2000)
(
subpartition s2
data directory='/var/c/data',
subpartition s3
data directory='/var/d/data'
)
);
# 子分区后的表文件信息查看
[root@db-01 var]# ll /var/{a..d}/data/oldboy
/var/a/data/oldboy:
总用量 112
-rw-r----- 1 mysql mysql 114688 9月  29 02:58 t6#p#p01#sp#s0.ibd
/var/b/data/oldboy:
总用量 112
-rw-r----- 1 mysql mysql 114688 9月  29 02:58 t6#p#p01#sp#s1.ibd
/var/c/data/oldboy:
总用量 112
-rw-r----- 1 mysql mysql 114688 9月  29 02:58 t6#p#p02#sp#s2.ibd
/var/d/data/oldboy:
总用量 112
-rw-r----- 1 mysql mysql 114688 9月  29 02:58 t6#p#p02#sp#s3.ibd

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

create table t7 (id int, updateinfo date)
partition by range(YEAR(updateinfo))
subpartition by hash(to_days(updateinfo))
(
partition p01 values less than (1990)
(subpartition s0,subpartition s1),
partition p02 values less than (2000)
(subpartition s2,subpartition s3),
partition p03 values less than (maxvalue)
);
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
)' at line 9

1

alter table t3 partition by key(id) partitions 2;

1
2
3
4

alter table t1 drop partition p02;
-- 进行表分区删除
alter table t1 truncate partition p01;
-- 进行表分区数据删除，但表分区还在

1
2
3
4
5
6
7

...
partition by range(YEAR(dob))(
partition p0 values less than (1970),
partition p1 values less than (1980)
)
alter table t1 add partition (partition p2 values less than (1960));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

1
2
3
4
5
6

...
partition by list(data)(
partition p0 values in (5,10,15),
partition p1 values in (6,12,18),
)
alter table t2 add partition (partition p2 values in (4,8,12));

1
2
3
4
5
6
7

alter table t1 reorganize partition p01 into (
partition s0 values less than (5),
partition s1 values less than (10))
-- 表示将p01原有一个分区拆分为s0,s1两个分区
alter table t1 reorganize partition s0,s1 into (
partition p01 values less than (10))
-- 表示将s0,s1原有两个分区合并为p01一个分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# 关闭Numa（早期SMP）
# 关闭方式一：bios级别关闭
参见下图关闭（实现让mysql多使用一些内存空间）
numactl --hardware
available：1 node（0）    -- 如果是2或多个nodes就说明numa没关闭
-- 在bios层面numa关闭时，无论os层面的numa是否关闭，都不会影响数据库服务性能；
# 关闭方式二：os grub级别关闭
[root@xiaoQ ~]# vim /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="spectre_v2=retpoline net.ifnames=0 rhgb quiet numa=off "
GRUB_DISABLE_RECOVERY="true"
[root@xiaoQ ~]# grub2-mkconfig -o /etc/grub2.cfg
-- 重新生成/etc/grub2.cfg配置文件
[root@xiaoQ ~]# reboot
[root@master grub2]# dmesg|grep -i numa
[    0.000000] NUMA turned off
-- 重启系统后确认numa是否关闭
[root@master grub2]# cat /proc/cmdline
BOOT_IMAGE=/vmlinuz-3.10.0-1160.el7.x86_64 root=UUID=f2d5232b-945e-4e7a-b76f-7ec609c35499 ro spectre_v2=retpoline net.ifnames=0 rhgb quiet
LANG=en_US.UTF-8 numa=off
-- 再次核实确认
-- 在OS层numa关闭时，打开bios层的numa会影响性能，QPS会下降15~30%；
# 关闭方式三：数据库级别关闭
mysql> show variables like '%numa%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_numa_interleave | OFF   |
+------------------------+-------+
1 row in set (0.04 sec)
或者
[root@xiaoQ ~]# vim /etc/init.d/mysqld
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
-- 将$bindir/mysqld_safe --datadir="$datadir"这一行修改为：
/usr/bin/numactl --interleave all $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

# 系统内存信息查看
[root@xiaoQ ~]# getconf PAGE_SIZE
4096
-- 默认内存页大小
[root@xiaoQ ~]# grep Huge /proc/meminfo
HugePages_Total:       0
HugePages_Free:        0
Hugepagesize:       2048 kB
-- 默认系统没有分配大页内存
# 禁用大页内存功能
# 临时关闭
[root@xiaoQ ~]# vim /etc/rc.local
[root@xiaoQ ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
-- always 表示一直都在未关闭
[root@xiaoQ ~]# echo never >/sys/kernel/mm/transparent_hugepage/enabled
[root@xiaoQ ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
-- never 表示大页内存功能已关闭
# 永久关闭
[root@xiaoQ ~]# vi /etc/rc.local
-- 在文件末尾添加如下指令：
-- 先判断是否存在这个文件，再进行修改
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
[root@xiaoQ ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@xiaoQ ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
-- 进行脚本执行完毕后检查确认

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

[root@xiaoQ ~]# vim /etc/sysctl.conf
vm.swappiness = 5
-- 也可以设置为0 (/proc/sys/vm/swappiness)  物理内存剩余的百分比之后，使用swap
-- 参数值越大，越积极使用swap空间，参数值越小，越积极使用物理内存
-- 默认值为可以通过cat /proc/sys/vm/swappiness命令查看
vm.dirty_ratio = 20
-- 表示可以用脏数据填充的绝对最大系统内存量，当系统到达此点时，必须将所有脏数据提交到磁盘，
-- 同时所有新的I/O块都会被阻塞，直到脏数据被写入磁盘。
-- 这通常是长I/O卡顿的原因，但这也是保证内存中不会存在过量脏数据的保护机制。
vm.dirty_background_ratio = 10
-- 定义内存中脏页的刷新比例，在指定比例之上就要刷写脏页
net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0
[root@xiaoQ ~]# vim /etc/security/limits.conf
hard  nofile 63000
-- 可打开的文件描述符的最大数(超过会报错);
soft  nofile 63000
-- 可打开的文件描述符的最大数(超过会警告);
lsof |wc -l
-- 查看所有进程打开的文件数信息
/usr/sbin/lsof -p 27419 |wc -l
-- 查看某个进程打开的文件数信息

1
2
3
4
5
6
7
8

[root@xiaoQ-01 ~]# systemctl is-active firewalld
unknown
[root@xiaoQ-01 ~]# systemctl is-enabled firewalld
disabled
-- 查看防火墙服务是否关闭，如果有需要开启时，别忘把数据库服务相关的端口开启即可
[root@xiaoQ-01 ~]# getenforce
Disabled
-- 查看selinux安全策略是否关闭

1
2
3
4
5

# 推荐使用XFS文件系统，并设置数据库的数据为独立分区，不建议使用LVM
挂载点为: /data
挂载参数：defaults，noatime，nodiratime，nobarrier
[root@xiaoQ-01 ~]# vim /etc/fstab
/dev/sdb1    /data        xfs          defaults，noatime，nodiratime，nobarrier      1   2

1
2
3
4
5

#SAS  deadline/SSD&PCI-E: noop
[root@xiaoQ-01 ~]# echo deadline > /sys/block/sda/queue/scheduler
或者
[root@xiaoQ ~]# vim /etc/default/grub
GRUB_CMDLINE_LINUX="spectre_v2=retpoline net.ifnames=0  elevator=deadline rhgb quiet"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 连接层相关优化参数
max_connections=1000
-- 单节点建议不高于3000
max_connect_errors=999999
-- 定义最大连接失败的次数，当超过定义的数值，就会影响正常的连接建立
wait_timeout=600
-- 定义连接会话的超时时间（释放更多的连接数），具体指定sleep连接会话的超时时间
interactive_timeout=3600
-- 定义连接会话的超时时间（释放更多的连接数），定义交互式的超时时间
net_read_timeout=120
net_write_timeout=120
-- 定义网络传输读或写数据包的超时时间；
max_allowed_packet=32M
-- 定义允许的最大数据包大小

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 服务层相关优化参数
sql_safe_updates = 1
-- 设置当使用update或delete命令时，必须加上where才能执行
slow_query_log = ON
slow_query_log_file = /xxx
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10  -- 不走索引的相同索引语句只记录指定的次数
-- 进行数据库慢日志信息相关配置
sort_buffer_size = 262144
join_buffer_size = 262144
read_buffer_size = 131072
read_rnd_buffer_size = 262144
-- 定义session级别的缓冲区大小，不建议设置大小超过8M，因为是根据每个会话进行的缓冲区分配；
tmp_table_size = 16777216
max_heap_table_size = 16777216
-- 生成的临时表空间建议不超过128M
max_execution_time = 28800
-- 当跑大的事务操作时，可以设置事务最大的执行时间，建议再跑批量操作时，可以设置大些
lock_wait_timeout = 60
-- 表示设置锁等待的时间，当锁定时间到达指定时间后，会实现自动解锁（主要针对元数据锁，默认是1年）
lower_case_table_names = 1
-- 表示创建表时，自动将表名的大写信息转化为小写；（必须初始化时进行设置）
thread_cache_size = 64
-- 表示设置线程缓存的个数信息，可以使线程缓存资源进行复用，从而减少CPU工作压力；（比如连接线程就可以应用）
character_set_server = utf8mb4
-- 设置数据库服务端字符集，建议设置为utf8或utf8mb4
log_timestamps = SYSTEM
-- 表示设置日志信息的时间尽量和系统时间信息保持一致
init_connect = '普通用户登录信息'
init_connect='insert into auditdb.access(thread_id,login_time,localname,matchname) values (connection_id(),now(),user(),current_user());'
-- 一般在进行审计时进行使用，表示普通用户登录时，自动进行相应语句的操作
-- 参考链接说明：https://blog.csdn.net/mingli_a/article/details/115351986
event_scheduler = OFF
-- 事件调度信息一般不用使用，关闭即可
secure-file-priv=/tmp
-- 当需要在数据库子系统中把信息导出到当前系统指定目录文件中时，进行使用
expire_logs_days = 10
sync_binlog = 1
log_bin = ON
log_bin_basename = /data/3306/binlog/mysql-bin
log_bin_index = /data/3306/binlog/mysql-bin.index
max_binlog_size = 500M
binlog_format = ROW
max_binlog_cache_size = 2G
max_binlog_stmt_cache_size = 2G
-- 表示和binlog有关的配置信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

# 引擎层相关优化参数
transaction_isolation = "READ-COMMITTED"
-- 设置事务默认隔离级别，基本RC级别即可
innodb_data_home_dir = /xxx
-- 表示定义共享表空间文件ibdate存储路径（了解即可）
innodb_log_group_home_dir = /xxx
-- 表示定义redo日志文件存储路径（了解即可）
innodb_log_file_size = 2048M
-- 表示定义redo日志单个文件大小（建议1~4G）
innodb_log_files_in_group = 3
-- 表示定义redo日志文件的组数（一般可以定义为3~4组）
innodb_flush_log_at_trx_commit = 2
-- 表示定义事务redo日志刷新到磁盘的策略（双一配置中的其中一个），有binlog日志时，可以不用设置为1
innodb_flush_method = O_DIRECT
-- 表示log buffer中的信息是直接写入到磁盘中的，而不经过系统的buffer（建议硬盘配合SSD使用）
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000
-- 表示每次IO可以刷新数据页的数量（SSD盘按照以上配置  SAS盘按照默认即可）
innodb_buffer_pool_size = 64G
-- 表示定义buffer pool的空间大小（基于128G内存配置，建议不要超过75~80%）
innodb_buffer_pool_instances = 4
-- 表示将定义好的buffer pool空间可以拆分为4份，给不同的实例进行使用，避免相同内存空间的争用；
innodb_log_buffer_size = 64M
-- 定义log buffer空间大小，建议不要超过128M；
innodb_max_dirty_pages_pct = 85
-- 控制在buffer pool中脏页数量的比例，当达到指定的比例就进行checkpoint操作，将脏页信息进行落盘
innodb_lock_wait_timeout = 10
-- 主要是控制行锁的等待超时时间，一般控制在10s内
innodb_open_files = 63000
-- 表示定义最多打开文件句柄的个数，数据库每次访问一个表（即打开一个文件），都会占用一定的文件句柄数量
innodb_page_cleaners = 4
-- 表示和线程有关的优化（可以忽略）
innodb_sort_buffer_size = 64M
-- 表示做排序时利用的缓冲区大小
innodb_print_all_deadlocks = 1
-- 表示将死锁的日志全部记录下来
innodb_rollback_on_timeout = ON
-- 表示当到达超时时间，会自动解决死锁事务
innodb_deadlock_detect = ON
-- 表示开启死锁检测功能（默认开启）
-- 表示和死锁检测和分析有关的参数
-- 对于死锁概念的资料参考：https://blog.csdn.net/java1527/article/details/127105144

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

# 主从复制相关优化参数
relay_log= db-01-relay-bin
relay_log_basename = /data/3306/data/db-01-relay-bin
relay_log_index = /data/3306/data/db-01-relay-bin.index
max_relay_log_size = 500M
relay_log_purge = ON
relay_log_recovery = ON
-- 表示和relay log日志相关的配置参数信息
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_trace_level=32
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_no_slave=on
rpl_semi_sync_master_wait_point=after_sync
rpl_semi_sync_slave_enabled=on
rpl_semi_sync_slave_trace_level=32
binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000
-- 表示和半同步复制相关的配置参数
gtid_mode = ON
enforce_gtid_consistency = ON
-- 表示和GTID相关的配置参数信息
master_verify_checksum = ON
-- 表示激活主从复制事件校验机制
sync_master_info = 1
-- 表示每个EVENT都要执行刷盘操作，主要影响master info信息（注意不是每个事务!）
-- 参数参考博文资料：https://blog.csdn.net/weixin_39940344/article/details/113275456
skip-slave-start = 1
-- 表示随着数据库服务的启动，自动启动从库线程
-- 参数参考博文资料：https://blog.csdn.net/csdnhsh/article/details/116355191
# read_only = ON
# super_read_only = ON
-- 表示是否设置从库为只读状态
log_slave_updates = ON
-- 表示指定从库的事务更新操作，是否也记录到从库的binlog日志中
server_id = xx
-- 定义主从的实例标识信息
report_host = xxxx
report_port = xxxx
-- 表示是否允许主库探测从库的网络配置信息，主要影响show slave hosts命令的输出
-- 参数参考博文资料：http://04007.cn/article/527.html
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
-- 表示设置从库的多线程复制，可以对单个事务中的语句进行多线程回放
master_info_repository = TABLE
relay_log_info_repository = TABLE
-- 表示定义master info和relay log info以什么方式记录信息

1
2
3
4
5
6

# 数据库客户端配置
[mysql]
no-auto-rehash  （等价于mysql -A参数作用）
-- 默认每次连接数据库服务会扫描数据库中所有元数据信息，可以利用此参数关闭扫描功能，有效节省扫描过程占用的内存资源；
pager less
-- 开启数据库输出信息过滤功能

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

# 01 去掉不必要的括号
before：
((a AND b) AND c OR (((a AND B)) AND ((c AND d))))
after
(a AND b AND c) OR (a AND b AND c AND d)
# 02 去掉重叠条件
before：
(a<b AND b=c) AND a=5
after
b>5 AND b=c AND a=5
before
(b>=5 AND B=5) OR (b=6 AND 5=5) OR (B=7 AND 5=6)
after
b=5 OR b=6
# 03 避免使用not in、not exists、<>、like %%
# 04 多表连接，小表驱动大表
# 05 减少临时表应用，优化order by、group by、uninon、distinct、join等
# 06 减少语句查询范围，精确查询条件
# 07 多条件，符合联合索引最左原则
# 08 查询条件减少使用函数、拼接字符等条件、条件隐式转换
# 09 union all 替代 union
# 10 减少having子句使用
# 11 如非必要不使用for update语句（会加表级别意向锁）
# 12 update和delete，开启安全更新参数
# 13 减少insert ... select 语句应用
mysql > create table city_bak like city;
mysql > desc city_bak;
mysql > insert into city_bak select * from city;  (会对原有city表进行加锁，建议加条件扫描)
# 14 使用load替代insert录入大数据（会产生大量的意向锁）
# 15 导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer，关闭autocommit、RC级别可以提高效率
# 16 优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
limit 5000000,10
before
select * from t1 where num > 10 limit 5000000,10;
after
select id from t1 where num > 10 limit 5000000,10;
select id from t1 where id;
# 17 DDL执行前要审核
# 18 多表连接语句执行前要看执行计划

1
2
3
4
5

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
index idx_url(url_crc32));

1
2
3
4
5
6

lock tables 表名 read;
-- 该表可以读，不能ddl 和 dml 中增删改，只能读取表数据
lock tables 表名 write;
-- 既不能读，也不能写
mysql> show open tables like '%city%';
-- 共享锁和排他锁查看；

1
2
3
4

select ... lock in share mode
-- 显示设置方法
select * from  performance_schema.data_locks\G
-- 查看方法

1
2
3
4

select ... for update
-- 显示设置方法
select * from  performance_schema.data_locks\G
-- 查看方法

1
2
3
4
5
6
7

create table test (
id int(11) not null,
code int(11) not null,
primary key(id),
key(code)
) engine=InnoDB default charset=utf8mb4;
insert into test(id,code) values(1,1),(10,10);

1

show engine innodb status;

1

select id from test where id=10 for update;

1
2
3
4
5

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> update test set id=2 where id=1;
Quer y OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1
2
3
4
5
6
7
8

mysql> show engine innodb status\G
...
-------------------
TRANSACTIONS
-------------------
---TRANSACTION 8790, ACTIVE 85 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 2
MySQL thread id 9, OS thread handle 140065464329984, query id 74 localhost root

1
2
3

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> update test set id=3 where id=1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> show engine innodb status\G
...
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 4 n bits 72 index PRIMARY of table `oldboy`.`test` trx id 8791 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000000002256; asc     "V;;
2: len 7; hex 01000000bf02aa; asc        ;;
3: len 4; hex 80000001; asc     ;;
------------------

1

select c1 from t where c1 between 10 and 20 for update;

1

select * from table where id=1;

1
2
3
4
5
6
7
8
9

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select * from test where code=10 for update;
+----+------+
| id | code |
+----+------+
| 10 |   10 |
+----+------+
1 row in set (0.00 sec)

1
2
3

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> insert into test values(2,2);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> show engine innodb status\G
...
---TRANSACTION 8794, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 140065464329984, query id 159 localhost root update
insert into test values(2,2)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 5 n bits 72 index code of table `oldboy`.`test` trx id 8794 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc     ;;
1: len 4; hex 8000000a; asc     ;;
------------------

1
2
3
4

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> insert into test values(0,0);
Quer y OK, 1 row affected (0.00 sec)

1
2

mysql> insert into test values (5,5);
Quer y OK, 1 row affected (0.00 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select * from test where code between 1 and 10 for update;
+----+------+
| id | code |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

1
2
3

mysql> begin;
Quer y OK, 0 rows affected (0.00 sec)
mysql> update test set code=4 where code=5;

1
2
3
4
5
6
7
8
9

mysql> select * from test;
+----+------+
| id | code |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

1

(-∞,1] (1,5] (5,10] (10,+∞)

1
2
3
4
5
6
7
8
9

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select * from test where code=5 for update;
+----+------+
| id | code |
+----+------+
|  5 |    5 |
+----+------+
1 row in set (0.00 sec)

1
2
3

mysql> start transaction;
Quer y OK, 0 rows affected (0.00 sec)
mysql> insert into test values(8,8);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# 模拟存储数据信息操作
mysql> source ~/t100w_oldboy.sql
# 查看mutex的互斥争用信息：
mysql> show engine innodb mutex;
+----------+-------------------------------------+------------+
| Type      | Name                                         | Status      |
+----------+-------------------------------------+------------+
| InnoDB | rwlock: dict0dict.cc:2678        | waits=1    |
| InnoDB | rwlock: dict0dict.cc:1184        | waits=13  |
| InnoDB | rwlock: log0log.cc:844             | waits=35  |
| InnoDB | sum rwlock: buf0buf.cc:788    | waits=16  |
+----------+-------------------------------------+------------+
1 row in set (0.01 sec)
-- 表示进行sum统计读写锁的总占用时间，为等待状态16毫秒，并输出造成锁等待的源码文件和位置信息；
-- 通过源码文件的信息，可以推断分析出，可能在写内存信息（buffer pool），可能是某个内存链位置点或内存页信息被占用
# 可以通过第三方工具分析堆栈信息：
pstack -p 'pidof mysqld' > /tmp/aa.txt
pt-pmp  /tmp/aa.txt|more

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

mysql> show engine innodb status\G
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reser vation count 0
OS WAIT ARRAY INFO: signal count 1
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
-- rounds：意思是每次询问旋转的次数；
-- os waits：表示sleep，当突然增长的比较快的时候，说明latch争用的比较严重；

1
2
3
4
5
6

mysql> show variables like '%buffer%';
+--------------------------------------------+----------------+
| Variable_name                                   | Value             |
+--------------------------------------------+----------------+
| innodb_buffer_pool_instances        | 1                    |
+--------------------------------------------+----------------+

1
2
3
4

# 主要的加锁方式为：FTWRL
> flush tables with read lock；
# 主要的解锁方式为：
> unlock tables;

1
2
3
4
5
6
7

mysql> select @@lock_wait_timeout;
+-----------------------------+
| @@lock_wait_timeout |
+-----------------------------+
|                        31536000 |
+-----------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9

# 记录加锁过程信息
mysql> update performance_schema.setup_instruments set ENABLED='Yes', TIMED='Yes' where NAME='wait/lock/metadata/sql/mdl'
-- 激活GRL锁检测功能（8.0版本之后默认激活了）
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;
-- 查看GRL锁定的相关信息，其中granted表示请求到了GRL锁，而若变为pending状态，表示处于阻塞状态；
# 记录加锁过程信息（5.7）
mysql> show processlist;
mysql> select * from sys.schema_table_lock_waits;

1
2
3
4
5
6
7
8

# 会话01：模拟一个大的查询或事务
mysql> select *,sleep(100) from city where id<10 for update;
# 会话02：模拟备份时的FTWRL
mysql> flush tables with read lock;
-- 此时发现命令被阻塞
# 会话03：发起正常查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
mysql> show processlist;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# 会话01：模拟一个大的查询或事务
mysql> select *,sleep(100) from city where id<10;
mysql> show processlist;
# 会话02：模备份时的FTWRL
mysql> flush tables with read lock;
-- 保证命令执行成功
mysql> unlock tables;
-- 解除命令锁定操作后，后续修改和插入操作也可以成功
# 会话03：发起正常查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
mysql> select * from world.city where id=1
-- 要进行的修改操作是不行的，但是所有简单查询操作是可以进行的

1
2
3
4

LOCK TABLE t READ
-- innodb存储引擎会对表t加表级别的S锁
LOCK TABLE t WRITE
-- innodb存储引擎会对表t加表级别的