关于mysql:MySQL再学习笔记

3次阅读

共计 14273 个字符，预计需要花费 36 分钟才能阅读完成。

非结构化数据，各种文档、图片、视频 / 音频等都属于非结构化数据。对于这类数据，咱们个别间接整体进行存储，而且个别存储为二进制的数据格式（如文件、图片、视频、语音等需存入文件系统中）
结构化数据，结构化的数据是指能够应用关系型数据库示意和存储，体现为二维模式的数据。个别特点是：数据以行为单位，一行数据表示一个实体的信息，每一行数据的属性是雷同的（如行数据等需存入关系型数据库中）
半结构化数据，半结构化数据是结构化数据的一种模式，它并不合乎关系型数据库或其余数据表的模式关联起来的数据模型构造，但蕴含相干标记，用来分隔语义元素以及对记录和字段进行分层。因而，它也被称为自描述的构造。（常见的半构造数据有 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.sql
Enter 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 -uroot -p123456 < /data/mysqlDump/mydb.sql

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

mysql> source /data/mysqlDump/mydb.sql

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

仅从 MySQL 数据库参数配置动手，探索设置正当的参数值来晋升 MySQL 数据库性能。

从 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/ref…

从其作用能够看出，当零碎的 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/ref…

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/ref…

当 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/ref…
双 1 就是 sync_binlog= 1 和 innodb_flush_log_at_trx_commit=1。这两个参数线上要保障为 1，前者保障 binlog 的平安，后者保障 redo 的平安，它们在数据库 crash recovery 的时候起到了要害做用，不设置为双 1 可能导致数据失落。

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/zengk…
https://dev.mysql.com/doc/ref…

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/ref…

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/ref…

https://dev.mysql.com/doc/ref…

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/w892824…

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/blogD…
https://dev.mysql.com/doc/ref…

show global status like ‘%Innodb_buffer_pool_wait_free%’;
如果值很大，则须要减少 innodb_page_cleaners 值，同时减少写线程。
参考文档

https://www.jianshu.com/p/699…
https://www.jianshu.com/p/ddb…

https://dev.mysql.com/doc/ref…

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

https://blog.csdn.net/kai404/…

https://dev.mysql.com/doc/ref…

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

[mysqld]

########basic settings########

server-id = 11
port = 3306
user = mysql
#bind_address = 10.166.224.32   #依据理论状况批改
autocommit = 1   #5.6.X 装置时，须要正文掉，装置实现后再关上
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /home/mysql/data      #依据理论状况批改, 倡议和程序拆散寄存
transaction_isolation = READ-COMMITTED
#explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_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 = 1800
wait_timeout = 1800
read_buffer_size = 16777216
read_rnd_buffer_size = 33554432
sort_buffer_size = 33554432
########log settings########

log_error = /home/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /home/mysql/logs/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 60
long_query_time = 1
min_examined_row_limit = 100
lower_case_table_names = 1 
########replication settings########

master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = /home/mysql/binlog/bin.log
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log = /home/mysql/relaylog/relay.log
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors

########innodb settings########

#innodb_page_size = 8192
innodb_buffer_pool_size = 24G    #依据理论状况批改
innodb_buffer_pool_instances = 12
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /home/mysql/redolog/  #依据理论状况批改
innodb_undo_directory = /home/mysql/undolog/      #依据理论状况批改
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 8G               #依据理论状况批改
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_flush_log_at_trx_commit = 1

[mysqld-5.7]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 12
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

MySQL 数据库服务器装置 fio

http://freshmeat.sourceforge….

[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=mytest

direct=1 测试过程绕过机器自带的 buffer，使测试后果更实在
rw=randrw 测试随机混合写和读的 I /O
ioengine=psync io 引擎应用 pync 形式
bs=16k 单次 io 的块文件大小为 16k
numjobs=10 本次的测试线程为 10

mytest: (g=0): rw=randrw, bs=16K-16K/16K-16K/16K-16K, ioengine=psync, iodepth=1
...
fio-2.1.10
Starting 10 threads
mytest: 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=1

Run 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=10061msec

Disk 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=1103…
https://www.cnblogs.com/Jiang…
redolog 与 binlog 的区别
https://blog.csdn.net/wanbin6…
InnoDB 脏页刷新机制 Checkpoint
https://www.cnblogs.com/olinu…
https://www.jianshu.com/p/0b1…
https://blog.csdn.net/qq_1831…

验证一个我的项目接口性能测试时继续呈现数据库死锁，本我的项目基于 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/dev…

https://mp.weixin.qq.com/s/l-…
https://www.cnblogs.com/wangd…
https://blog.csdn.net/yimengl…
https://cloud.tencent.com/dev…
http://blog.itpub.net/2891601…

正文完

mysql

发表至： mysql

2021-07-23

0

关于mysql:数据库SQL-server数据实时同步到mysql

关于mysql:可能是最贴心的MySQL笔记了

关于mysql:求求你不要再用offset和limit了

关于mysql:再一次学习-MySQL-索引

关于linux:阿里云低延时直播RTS能力升级让直播推流效果更佳

关于mysql:MySQL再学习笔记

数据结构

简介

特点

架构

装置形式

常用命令

DDL

DML

存储引擎

备份

备份所有数据库

备份指定数据库

还原 mysql 备份内容

根底优化

innodb_page_size

innodb_buffer_pool_size

sync_binlog

innodb_flush_log_at_trx_commit

innodb_lru_scan_depth

innodb_io_capacity

innodb_io_capacity_max

innodb_max_dirty_pages_pct

innodb_flush_neighbors

innodb_page_cleaners

innodb_log_file_size

优化后果

其余一些概念

问题汇总

优化参考文档

站内搜索