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%';
show engine innodb status\G;
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(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;
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)
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 -uroot -p123456 < /data/mysqlDump/mydb.sql
mysql> source /data/mysqlDump/mydb.sql
存储引擎的选择、字段设计、索引、SQL 语句等都是影响 MySQL 性能的重要因素，本次暂不详细讨论。
仅从 MySQL 数据库参数配置入手，探究设置合理的参数值来提升 MySQL 数据库性能。
请考虑使用与磁盘的内部扇区大小匹配的页面大小。早期 SSD 器件通常具有 4KB 扇区大小。某些较新的设备具有 16KB 扇区大小。默认 InnoDB 页面大小为 16KB。使页面大小接近存储设备块大小可最大限度地减少重写到磁盘的未更改数据量。
参考文档
https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html
从其作用可以看出，当系统的 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=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=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
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
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
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_flush_neighbors 参数是 InnoDB 用来控制 buffer pool 刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，在传统的机械硬盘时代，打开这个参数能够减少磁盘寻道的开销，显著提升性能。
取值范围：0，1，2
默认值：5.7 版本为 1， 8.0 版本为 0
含义：
如果 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
show global status like '%Innodb_buffer_pool_wait_free%';
如果值很大，则需要增加 innodb_page_cleaners 值，同时增加写线程。
参考文档
https://www.jianshu.com/p/6991304a8e26
https://www.jianshu.com/p/ddb24f9afae0https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors
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
[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
[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%
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
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
