测试基础 MySQL 再学习笔记

wholegale39 · 2021年07月23日 · 1671 次阅读

数据结构

非结构化数据，各种文档、图片、视频/音频等都属于非结构化数据。对于这类数据，我们一般直接整体进行存储，而且一般存储为二进制的数据格式（如文件、图片、视频、语音等需存入文件系统中）
结构化数据，结构化的数据是指可以使用关系型数据库表示和存储，表现为二维形式的数据。一般特点是：数据以行为单位，一行数据表示一个实体的信息，每一行数据的属性是相同的（如行数据等需存入关系型数据库中）
半结构化数据，半结构化数据是结构化数据的一种形式，它并不符合关系型数据库或其他数据表的形式关联起来的数据模型结构，但包含相关标记，用来分隔语义元素以及对记录和字段进行分层。因此，它也被称为自描述的结构。（常见的半结构数据有 XML 和 JSON，可存入 NoSQL 数据库中）

简介

关系型数据库,瑞典基于 C++ 语言开发
小巧、实用、性能高
其他数据库如 Oracle(甲骨文)、SQLServer(微软)、DB2(IBM)

特点

开源
社区版免费
跨平台
安全性高
成本低
支持各种开发语言
支持强大的内置函数
数据存储量大

架构

安装方式

可使用 xdja_centos7.4 裁剪版自带 MySQL5.7 安装包一键安装
可使用以下脚本设置连接权限

常用命令

连接数据库

mysql -uroot -p

显示数据库

show databases;

选择数据库

use xxx(databasename);

显示数据库表

show tables;

查看表描述

DESC xxx(datatable);

显示数据库版本、时间等

SELECT VERSION(),CURRENT_DATE(),CURRENT_TIME();

显示运行的进程

show processlist;

查看配置项

show variables like '%tx_isolation%';

查看 innodb 状态

show engine innodb status\G;

DDL

DDL(Data Definition Languages):数据定义语言，定义不同的数据段、数据库、表、列、索引等数据库对象，常用的关键字包括 create、drop、alter 等
创建数据库 test1

create database test1;

删除指定数据库

drop database test1;

修改表字段

alter table emp modify ename varchar(20);

增加、删除表字段

alter table emp add column age int(3);alter table emp drop column age;

DML

DML(Data Manipulation Language):数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的关键字主要包括 insert、delete、update 和 select 等
插入记录

insert into emp(ename,sal,deptno) values('zhangsan','2015-08-01','2000',1);insert into emp(ename,sal,deptno) values('lisi','2015-08-01','3000',1);create table dept(deptno int(3),deptname varchar(20);insert into dept values(1,'tech'),(2,'sales'),(3,'fin');

更新、删除记录

update emp set sal=4000 where ename='lisi';delete from emp where ename='lisi';

查询指定列、查询不重复记录

select ename,hiredate,sal,deptno from emp;select distinct deptno from emp;

条件查询与排序

select * from emp where deptno =1 and sal<3000;select * from emp order by sal;

分组统计

select count(1) from emp;select deptno,count(1) as empnum from emp group by deptno

分组统计 + 条件过滤

select deptno,count(1) as empnum from emp group by deptno with rollup;select deptno,count(1) as empnum from emp group by deptno having count(1)>1;

聚合函数与多表关联查询

select sum(sal),max(sal),min(sal) from emp;select ename,deptname from emp,dept where emp.deptno=dept.deptno;

存储引擎

存储引擎就是如何存储数据、如何为存储的数据建立缩影和如何更新、查询数据等技术的实现方法；
在关系数据中数据的存储是以表的形式存储，所以存储引擎也可以称为表类型（即存储和操作此表的类型）；
类型有 MyISAM、InnoDB、MERGE、MEMORY(HEAP) 等；

show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.03 sec)

InnoDB

数据和索引合并存储为一个文件，.frm(描述表的结构) .ibd(表数据文件)
支持外键，事务处理
行锁定
具有提交、回滚和崩溃恢复能力的事务安全
并行读写，适用于大量的写操作的表

备份

备份所有数据库

mysqldump -uroot -p --all-databases > itsca.sql

输入密码后即可备份所有库

[root@xdja wch]# mysqldump -uroot -p --all-databases > itsca.sqlEnter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

备份指定数据库

mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

还原 mysql 备份内容

在系统命令行中，输入如下实现还原：

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

在登录进入 mysql 系统中,通过 source 指令找到对应系统中的文件进行还原：

mysql> source /data/mysqlDump/mydb.sql

基础优化

存储引擎的选择、字段设计、索引、SQL 语句等都是影响 MySQL 性能的重要因素，本次暂不详细讨论。

仅从 MySQL 数据库参数配置入手，探究设置合理的参数值来提升 MySQL 数据库性能。

innodb_page_size

从 InnoDB1.2.x 版本开始，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有表中页的大小都为 innodb_page_size，不可以对其再次修改，否则启动会报错，踩过坑。
默认是 16k
Consider using a page size that matches the internal sector size of the disk. Early-generation SSD devices often have a 4KB sector size. Some newer devices have a 16KB sector size. The default InnoDB page size is 16KB. Keeping the page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.
请考虑使用与磁盘的内部扇区大小匹配的页面大小。早期 SSD 器件通常具有 4KB 扇区大小。某些较新的设备具有 16KB 扇区大小。默认 InnoDB 页面大小为 16KB。使页面大小接近存储设备块大小可最大限度地减少重写到磁盘的未更改数据量。
参考文档

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

innodb_buffer_pool_size

从其作用可以看出，当系统的 IO 比较空闲的时候，可以适当将这个参数设大，当 IO 吃紧时，需要适当减小，一般设置为内存总大小的 50%-75%
Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size variable, which has a default of 128 MB.
参考文章

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

sync_binlog

sync_binlog=0，当事务提交之后，MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘，而让 Filesystem 自行决定什么时候来做同步，或者 cache 满了之后才同步到磁盘。
当 sync_binlog=1 时, MySQL 在写 1 次二进制日志 binary log 时, 会使用 fdatasync() 函数将二进制 binary log 同步到 disk 中去 (安全性最高的配置)。
sync_binlog=n，当每进行 n 次事务提交之后，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。
参考文档

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog

innodb_flush_log_at_trx_commit

当 innodb_flush_log_at_trx_commit=1 时 (默认)，每次事务提交时, MySQL 会把 log buffer 的数据写入 log file, 并且将 log file flush 到硬盘中。这样做的好处是数据安全性最佳，不足之处在于每次提交事务，都要进行磁盘写入的操作。在大并发的场景下，过于频繁的磁盘读写会导致 CPU 资源浪费，系统效率变低。（效率低，安全性高）
设置为 0 表示每隔 1 秒将数据写入日志，并将日志写入磁盘；（效率高，安全性低）
设置为 2 表示每次提交事务的时候都将数据写入日志，但是日志每间隔 1 秒写入磁盘。（效率中，安全性中）
参考文档

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

双 1 就是 sync_binlog=1 和 innodb_flush_log_at_trx_commit=1。这两个参数线上要保证为 1，前者保证 binlog 的安全，后者保证 redo 的安全，它们在数据库 crash recovery 的时候起到了关键做用，不设置为双 1 可能导致数据丢失。

innodb_lru_scan_depth

innodb_lru_scan_depth 是 5.6 新增加的参数，根据官方文档描述，它会影响 page cleaner 线程每次刷脏页的数量，这是一个每 1 秒 loop 一次的线程。在 Innodb 内部，这个参数对应变量为 srv_LRU_scan_depth
page cleaner 线程刷脏页的长度，从尾部开始刷 srv_LRU_scan_depth
默认值为 1024，当 IO 吃紧时，应适当降低此值
参考文档

https://www.cnblogs.com/zengkefu/p/5692803.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth

innodb_io_capacity

The default setting of 200 is generally sufficient for a lower-end non-rotational storage device. For higher-end, bus-attached devices, consider a higher setting such as 1000.
参数 :innodb_io_capacity :数据库落盘脏页个数 ,配置压力和磁盘的性能相关，如果过大，IO 能力不足，则出现卡顿。
innodb_io_capacity 默认是 200，单位是页，该参数的设置大小取决于硬盘的 IOPS，即每秒的输入输出量 (或读写次数)
参考文档

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

innodb_io_capacity_max

If you specify an innodb_io_capacity setting at startup but do not specify a value for innodb_io_capacity_max, innodb_io_capacity_max defaults to twice the value of innodb_io_capacity or 2000, whichever value is greater.
参考文档

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-configuring-io-capacity.html

innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct 是 MySQL InnoDB 存储引擎非常重要的一个参数，用来控制 buffer pool 中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB 会启动刷脏页的操作。该参数只控制脏页百分比，并不会影响刷脏页的速度。
默认值：75%
除了脏页百分比达到阈值 innodb_max_dirty_pages_pct 触发刷脏页以外，还有很多条件，也会触发刷脏页，主要包括：

REDO 日志快满的时候。
为了保证 MySQL 中的空闲页面的数量，会从 LRU 链表尾部淘汰一部分页面作为空闲页。如果对应的页面是脏页的话，就需要先将页面刷到磁盘。
MySQL
MySQL 实例正常关闭时候。

参考文档

https://blog.csdn.net/w892824196/article/details/107411404

innodb_flush_neighbors

innodb_flush_neighbors 参数是 InnoDB 用来控制 buffer pool 刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，在传统的机械硬盘时代，打开这个参数能够减少磁盘寻道的开销，显著提升性能。
取值范围：0，1，2
默认值：5.7 版本为 1， 8.0 版本为 0
含义：

设置为 0 时，表示刷脏页时不刷其附近的脏页。
设置为 1 时，表示刷脏页时连带其附近毗连的脏页一起刷掉。
设置为 2 时，表示刷脏页时连带其附近区域的脏页一起刷掉。1 与 2 的区别是 2 刷的区域更大一些。

如果 MySQL 服务器磁盘是传统的 HDD 存储设备，打开该参数，能够减少 I/O 磁盘寻道的开销，提高性能，而对于 SSD 设备，寻道时间的性能影响很小，关闭该参数，反而能够分散写操作，提高数据库性能。由于 SSD 设备的普及，MySQL 8.0 将该参数的默认值由 1 调整为 0。
参考文档

https://www.mytecdb.com/blogDetail.php?id=117
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

innodb_page_cleaners

show global status like '%Innodb_buffer_pool_wait_free%';
如果值很大，则需要增加 innodb_page_cleaners 值，同时增加写线程。
参考文档

https://www.jianshu.com/p/6991304a8e26
https://www.jianshu.com/p/ddb24f9afae0

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors

innodb_log_file_size

Redo log 的空间通过innodb_log_file_size和innodb_log_files_in_group（默认 2）参数来调节，将这俩参数相乘即可得到总的可用 Redo log 空间。
可以使用 MySQL 监控 PMM 来进行详细分析，具体参考以下文章
参考文档

https://blog.csdn.net/kai404/article/details/80242262

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size

优化结果

为我司安装 MySQL 默认参数，需根据不同机器硬件配置进行调整

[mysqld]########basic settings########server-id = 11port = 3306user = mysql#bind_address = 10.166.224.32   #根据实际情况修改autocommit = 1   #5.6.X安装时，需要注释掉，安装完成后再打开character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir = /home/mysql/data      #根据实际情况修改,建议和程序分离存放transaction_isolation = READ-COMMITTED#explicit_defaults_for_timestamp = 1join_buffer_size = 134217728tmp_table_size = 67108864tmpdir = /tmpmax_allowed_packet = 16777216sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd_buffer_size = 33554432sort_buffer_size = 33554432########log settings########log_error = /home/mysql/logs/error.logslow_query_log = 1slow_query_log_file = /home/mysql/logs/slow.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 60long_query_time = 1min_examined_row_limit = 100lower_case_table_names = 1 ########replication settings########master_info_repository = TABLErelay_log_info_repository = TABLElog_bin = /home/mysql/binlog/bin.logsync_binlog = 1gtid_mode = onenforce_gtid_consistency = 1log_slave_updatesbinlog_format = rowrelay_log = /home/mysql/relaylog/relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors########innodb settings#########innodb_page_size = 8192innodb_buffer_pool_size = 24G    #根据实际情况修改innodb_buffer_pool_instances = 12innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 5innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_log_group_home_dir = /home/mysql/redolog/  #根据实际情况修改innodb_undo_directory = /home/mysql/undolog/      #根据实际情况修改innodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 8G               #根据实际情况修改innodb_log_buffer_size = 16777216innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 67108864innodb_flush_log_at_trx_commit = 1[mysqld-5.7]innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 12innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=MURMUR32show_compatibility_56=on

MySQL 数据库服务器安装 fio

http://freshmeat.sourceforge.net/projects/fio/

[root@xdja wch]# tar -zxvf fio-2.1.10.tar.gz [root@xdja wch]# cd fio-2.1.10/[root@xdja fio-2.1.10]# ./configure[root@xdja fio-2.1.10]# make[root@xdja fio-2.1.10]# make Install[root@xdja fio-2.1.10]# cd ..[root@xdja wch]# fio -filename=test0628 -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytestdirect=1 测试过程绕过机器自带的buffer，使测试结果更真实rw=randrw 测试随机混合写和读的I/Oioengine=psync io引擎使用pync方式bs=16k 单次io的块文件大小为16knumjobs=10 本次的测试线程为10

mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K/16K-16K, ioengine=psync, iodepth=1...fio-2.1.10Starting 10 threadsmytest: Laying out IO file(s) (1 file(s) / 500MB)Jobs: 10 (f=10): [mmmmmmmmmm] [100.0% done] [1328KB/896KB/0KB /s] [83/56/0 iops] [eta 00m:00s]mytest: (groupid=0, jobs=10): err= 0: pid=23464: Mon Jun 28 12:52:55 2021  read : io=11632KB, bw=1156.2KB/s, iops=72, runt= 10061msec    clat (msec): min=1, max=273, avg=84.36, stdev=55.83     lat (msec): min=1, max=273, avg=84.36, stdev=55.83    clat percentiles (msec):     |  1.00th=[    5],  5.00th=[   12], 10.00th=[   18], 20.00th=[   31],     | 30.00th=[   48], 40.00th=[   62], 50.00th=[   77], 60.00th=[   94],     | 70.00th=[  113], 80.00th=[  130], 90.00th=[  159], 95.00th=[  186],     | 99.00th=[  249], 99.50th=[  258], 99.90th=[  273], 99.95th=[  273],     | 99.99th=[  273]    bw (KB  /s): min=   56, max=  191, per=10.04%, avg=116.07, stdev=29.91  write: io=11424KB, bw=1135.5KB/s, iops=70, runt= 10061msec    clat (usec): min=277, max=205220, avg=54698.78, stdev=44032.60     lat (usec): min=277, max=205221, avg=54699.64, stdev=44032.65    clat percentiles (usec):     |  1.00th=[  828],  5.00th=[ 1272], 10.00th=[ 1672], 20.00th=[ 3472],     | 30.00th=[23168], 40.00th=[37632], 50.00th=[53504], 60.00th=[64768],     | 70.00th=[79360], 80.00th=[92672], 90.00th=[112128], 95.00th=[134144],     | 99.00th=[171008], 99.50th=[193536], 99.90th=[205824], 99.95th=[205824],     | 99.99th=[205824]    bw (KB  /s): min=   29, max=  206, per=10.19%, avg=115.64, stdev=38.23    lat (usec) : 500=0.21%, 750=0.21%, 1000=0.69%    lat (msec) : 2=4.93%, 4=5.27%, 10=3.68%, 20=5.55%, 50=18.18%    lat (msec) : 100=34.35%, 250=26.44%, 500=0.49%  cpu          : usr=0.01%, sys=0.06%, ctx=1492, majf=0, minf=7  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%     issued    : total=r=727/w=714/d=0, short=r=0/w=0/d=0     latency   : target=0, window=0, percentile=100.00%, depth=1Run status group 0 (all jobs):   READ: io=11632KB, aggrb=1156KB/s, minb=1156KB/s, maxb=1156KB/s, mint=10061msec, maxt=10061msec  WRITE: io=11424KB, aggrb=1135KB/s, minb=1135KB/s, maxb=1135KB/s, mint=10061msec, maxt=10061msecDisk stats (read/write):    dm-2: ios=719/1496, merge=0/0, ticks=60164/81876, in_queue=142913, util=100.00%, aggrios=727/1144, aggrmerge=0/370, aggrticks=61291/63730, aggrin_queue=125075, aggrutil=100.00%  sda: ios=727/1144, merge=0/370, ticks=61291/63730, in_queue=125075, util=100.00%

可以看到读写的 iops 基本在 70 左右，根据以上基本参数解释，现有服务器（Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz 4 核 8 线程，24G 内存，叠瓦式机械硬盘）进行以下参数设置，可以明显降低甚至消除接口性能测试过程中的 TPS 抖动（MySQL 数据库刷盘导致，可以从 error.log 日志看到刷盘信息）

innodb_buffer_pool_size =6G
innodb_buffer_pool_instances = 1
innodb_lru_scan_depth = 200
innodb_io_capacity = 100
innodb_io_capacity_max = 200
innodb_log_file_size = 4G
innodb_page_cleaners= 1

以上参数只是针对特定机器匹配服务相对最优的参数

其他一些概念

刷脏页机制
https://www.ywnds.com/?p=11039&viewuser=489
https://www.cnblogs.com/JiangLe/p/7419835.html
redolog 与 binlog 的区别
https://blog.csdn.net/wanbin6470398/article/details/81941586
InnoDB 脏页刷新机制 Checkpoint
https://www.cnblogs.com/olinux/p/5196139.html
https://www.jianshu.com/p/0b19e1cd5e8c
https://blog.csdn.net/qq_18312025/article/details/78597681

问题汇总

验证一个项目接口性能测试时持续出现数据库死锁，本项目基于 arm 架构麒麟系统，mysql 为源码编译，transaction_isolation = REPEATABLE-READ

Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

尝试查看项目之前的 mysql 服务，默认为 transaction_isolation = READ-COMMITTED，调整之后再次验证未再出现死锁问题
参考文档

https://cloud.tencent.com/developer/article/1710549

优化参考文档

3 个赞

暂无回复。

需要登录后方可回复, 如果你还没有账号请点击这里注册。