热门标签
热门文章
- 1pandas中的滚动窗口rolling函数和扩展窗口expanding函数_pandas rolling()的参数
- 2Python3对一个class类型的list按class中某一属性排序_python如何按照class的某个值排序
- 3TransReID: Transformer-based Object Re-Identification 介绍
- 4php如何实现在线直播系统，设计方案以及简单实现（此方案 app与pc端一致）---》第一篇
- 5PyTorch 最新安装教程_pytorch最新版本安装
- 6【软件安装】CUDA的卸载_cuda卸载
- 7新东方人工智能中台建设和AI部门管理经验分享_新东方数据中台
- 8迭代器详解
- 9假如C++是一只箭，你会用它来射哪只雕？| 就业岗位分享
- 10SqlSugar ORM 入门（简介和增删查改）
当前位置: article > 正文
MySQL最全知识点_mysql的命令知识点
作者：羊村懒王 | 2024-02-21 08:37:13
赞
踩
mysql的命令知识点
##MySQL数据库相关## yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm yum install qpress -y #json提取字段 select JSON_EXTRACT(coupons,"$.prize_items[0].serial_no")from vouchers; 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。 读取顺序： /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 其他自定义路径下的my.cnf #抓包 tcpdump -i eth0 -s 65535 -l -w - dst port 3306 | strings #### 't1'@'%'中包含't1'@'127.0.0.1'，如果开启skip_name_resolve参数，则't1'@'%'中定义的密码可用于't1'@'127.0.0.1'的登录，如果没有开启该参数，则't1'@'127.0.0.1'会转化为't1'@'localhost'登录，此时't1'@'%'定义的密码并不适用。 #调整OOM优先级 #查出pid ps -ef | grep -w mysqld|grep -v grep |awk -F ' ' {'print $2'} echo -17 > /proc/PID/oom_score_adj（输入-17，禁止被OOM机制处理） #编译安装(8.0要求gcc和cmake版本有变化) cmake3 . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DWITH_INNOBASE_STORAGE_ENGINE=1 -DFORCE_INSOURCE_BUILD=1 -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=3306 -DWITH_DEBUG=1 -DWITH_BOOST=/usr/local/mysql/mysql-8.0.18/boost #sysbencg压测 sysbench /usr/local/sysbench/sysbench-1.0.18/src/lua/oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password='970125' --mysql-db=sbtest --tables=1 --table-size=10000000 --auto_inc=off --report-interval=2 --threads=50 prepare sysbench /usr/local/sysbench/sysbench-1.0.18/src/lua/oltp_read_only.lua --mysql-host='rm-wz91mpe8d64971ii7.mysql.rds.aliyuncs.com' --mysql-port=3306 --mysql-user=dtstack_user --mysql-password='xxxxxxx' --mysql-db=sbtest --tables=5 --table-size=6000000 --auto_inc=off --report-interval=2 --mysql-ignore-errors=1062,1213 --db-ps-mode=disable --skip-trx=on --max-time=300 --threads=20 run #tpcc压测 tpcc_load -h rm-bp1lrs086rd53kc0lao.mysql.rds.aliyuncs.com -P 3306 -d tpcctest -u root -p ZIjie970125 -w 1 tpcc_start -h rm-bp1lrs086rd53kc0lao.mysql.rds.aliyuncs.com -P 3306 -d tpcctest -u root -p ZIjie970125 -w 1 -c 10 -r 300 -l 300 -i 2 -f report_file MySQL基于'max_join_size'的值确定查询是否是“大选择”。如果查询可能必须检查超过此行数，则会将其视为“大选择”。使用'show variables'查看最大连接大小的值。 使用索引和特别好的where子句将防止出现此问题。 SQL_BIG_SELECTS用于防止用户意外执行过大的查询。可以在mysql.cnf中将其设置为ON或在启动时使用命令行选项。 您可以在my.cnf或服务器启动时设置SQL_BIG_SELECTS。它也可以在会话的基础上设置为 SET SESSION SQL_BIG_SELECTS=1 #seconds_behind_master 表示slave上SQL thread与IO thread之间的延迟 #闪回binlog2sql(8.0要注意pymysql版本 否则会报255 因为字符集长度问题) 2.7 pip install pymysql pip install mysql-replication binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'970125' -dsbtest -tt1 --start-file='mybinlog.000029' --start-datetime='2019-11-28 16:30:00' --stop-datetime='2019-11-28 16:40:00' binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'970125' -dsbtest -tt1 --start-file='mybinlog.000029' --start-datetime='2019-11-28 16:30:00' --stop-datetime='2019-11-28 16:40:00' -B 报错Attempted to open a previously opened tablespace的解决办法： 1、在配置文件添加： innodb_force_recovery = 1; 重启数据库 登陆mysql，mysqldump导出 备份然后删除ibdata1、ib_logfile0、ib_logfile1、undo文件 注释掉第一步的配置：innodb_force_recovery = 1 重启数据库 登陆mysql，重建数据库，导入备份 innodb force recovery的6种设置： innodb_force_recovery=1，即使发现了损坏页面也继续让服务器继续运行，这个选项对于备份或者转存当前数据尤为有用 innodb_force_recovery=2，阻止恢复主线程的运行，如果清除操作会导致服务器挂掉 innodb_force_recovery=3，恢复后不回滚事务 innodb_force_recovery=4，如果插入到缓冲区的合并操作会导致系统崩溃，将不会被执行 innodb_force_recovery=5，启动数据库时，忽略撤消日志 innodb_force_recovery=6，启动数据库时，忽略与恢复相关的前滚日志 1 (SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服 务运行。一般设置为该值即可，然后 dump 出库表进行重建。 2 (SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用 该值。 3 (SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。 4 (SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。 不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提 交。此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。 6 (SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。使数据 库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。 show global status where Variable_name in ( "Com_select", "Com_insert", "Com_update", "Com_delete", "Innodb_buffer_pool_read_requests", "Innodb_buffer_pool_reads", "Innodb_rows_inserted", "Innodb_rows_updated", "Innodb_rows_deleted", "Innodb_rows_read", "Threads_running", "Threads_connected", "Threads_cached", "Threads_created", "Bytes_received", "Bytes_sent", "Innodb_buffer_pool_pages_data", "Innodb_buffer_pool_pages_free", "Innodb_buffer_pool_pages_dirty", "Innodb_buffer_pool_pages_flushed", "Innodb_data_reads", "Innodb_data_writes", "Innodb_data_read", "Innodb_data_written", "Innodb_os_log_fsyncs", "Innodb_os_log_written", "Slow_queries", "Created_tmp_disk_tables", "Created_tmp_tables", "Open_tables", "Open_files", "Table_locks_immediate", "Table_locks_waited" ); show global status where Variable_name in ('Connections','com_commit','com_rollback','com_delete', 'com_insert', 'com_select', 'com_update', 'com_replace', 'com_update_multi', 'com_replace_select', 'com_insert_select', 'com_delete_multi', 'innodb_rows_deleted', 'innodb_rows_inserted', 'innodb_rows_read', 'innodb_rows_updated', 'bytes_received', 'bytes_sent', 'Innodb_data_read','Innodb_data_reads','Innodb_data_writes','Innodb_data_written', 'innodb_buffer_pool_read_requests', 'innodb_buffer_pool_write_requests', 'innodb_log_writes', 'innodb_os_log_fsyncs', 'created_tmp_disk_tables', 'questions', 'insert', 'innodb_buffer_pool_pages_dirty', 'innodb_buffer_pool_pages_data', 'innodb_buffer_pool_pages_flushed', 'innodb_buffer_pool_reads', 'innodb_buffer_pool_pages_free', 'innodb_buffer_pool_pages_total', 'Innodb_replication_delay', 'Innodb_dml_delay_ms', 'Innodb_read_is_safe', 'slow_queries', 'open_files', 'innodb_row_lock_waits', 'innodb_row_lock_time', 'innodb_row_lock_time_avg', 'innodb_row_lock_time_max', 'Select_scan','Queries', 'Sort_rows', 'Innodb_data_fsyncs', 'Innodb_log_write_requests', 'Innodb_os_log_written', 'Open_tables', 'Tokudb_rows_inserted', 'Tokudb_rows_read', 'Tokudb_rows_deleted', 'Tokudb_rows_updated', 'Tokudb_OVERALL_NODE_COMPRESSION_RATIO', 'Key_blocks_unused', 'Key_blocks_used', 'Key_reads', 'Key_read_requests', 'Key_writes', 'Key_write_requests', 'Threads_connected', 'Threads_running','Threads_cached','Threads_created','Rpl_semi_sync_master_tx_waits','Rpl_semi_sync_master_tx_wait_time','Rpl_semi_sync_master_wait_sessions','Innodb_replication_delay','Innodb_dml_delay_ms','Innodb_buffer_pool_wait_free','Innodb_log_waits','Innodb_pages_read','Innodb_pages_written','Com_alter_table','Com_create_index','Com_create_table','Com_drop_index','Com_drop_table','Com_truncate','Queries','Innodb_log_write_lsn','Innodb_log_checkpoint_lsn','Aborted_connects','Binlog_cache_disk_use','Binlog_cache_use','Created_tmp_files','Created_tmp_tables','Prepared_stmt_count','Threads_rejected','Uptime','Sort_scan','Threads_active','Table_locks_waited','Maximum_protection_mode','Rpl_semi_sync_master_status','Rpl_semi_sync_slave_status','Rpl_semi_sync_master_clients','Rpl_semi_sync_master_no_tx','Rpl_semi_sync_master_no_times','Com_begin') 更改数据库编码(字符集)：ALTER DATABASE DATABASE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 更改表编码(字符集)（注意：这里修改的是表的字符集，表里面字段的字符集并没有被修改）：ALTER TABLE TABLE_NAME DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 更改表编码(字符集)和表中所有字段的编码(字符集)： ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; #零点 23点 SELECT DATE_FORMAT(curdate(),'%Y-%m-%d %H:%i:%s'); SELECT DATE_FORMAT( DATE_ADD(curdate(), INTERVAL 1 DAY),'%Y-%m-%d %H:%i:%s') #测试数据 CREATE TABLE `t` ( `id` int(11) NOT NULL,`a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoDB; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=100000)do insert into t values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); #备份binlog 以前备份binlog时，都是先在本地进行备份压缩，然后发送到远程服务器中。但是这其中还是有一定风险的，因为日志的备份都是周期性的，如果在某个周期中，服务器宕机了，硬盘损坏了，就可能导致这段时间的binlog就丢失了。 而且，以前用脚本对远程服务器进行备份的方式，有个缺点：无法对MySQL服务器当前正在写的二进制日志文件进行备份。所以，只能等到MySQL服务器全部写完才能进行备份。而写完一个binlog的时间并不固定，这就导致备份周期的不确定。 从MySQL5.6开始，mysqlbinlog支持将远程服务器上的binlog实时复制到本地服务器上。 mysqlbinlog的实时二进制复制功能并非简单的将远程服务器的日志复制过来，它是通过MySQL 5.6公布的Replication API实时获取二进制事件。本质上，就相当于MySQL的从服务器。与普通服务器类似，主服务器发生事件后，一般都会在0.5~1秒内进行备份。 mysqlbinlog --read-from-remote-server --raw --host=192.168.244.145 --port=3306 --user=repl --password=repl --stop-never mysql-bin.000001 解释如下： --read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。 --raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。 --user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。 --stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。 mysql-bin.000001：代表从哪个binlog开始复制。 除了以上选项外，还有以下几个选项需要注意： --stop-never-slave-server-id：在备份远程服务器的binlog时，mysqlbinlog本质上就相当于一个从服务器，该选项就是用来指定从服务器的server-id的。默认为-1。 --to-last-log：代表mysqlbinlog不仅能够获取指定的binlog，还能获取其后生成的binlog，获取完了，才终止。如果指定了--stop-never选项则会隐式打开--to-last-log选项。 --result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql-bin.000001，如果指定--result-file=/test/backup-，则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意：如果将--result-file设置为目录，则一定要带上目录分隔符“/”。譬如--result-file=/test/，而不是--result-file=/test，不然保存到本地的文件名为/testmysql-bin.000001。 #Myflash，解析结果为binlog格式，使用mysqlbinlog恢复 flashback --sqlTypes='INSERT' --maxSplitSize --binlogFileNames=/data/mysqlbackup/logs/mybinlog.000002 #脚本闪回 mysqlbinlog --no-defaults -vvv mysql-bin.000458 --start-datetime="2020-04-18 16:15:00" | grep -i -B 40 AUTH_GROUP_STOCK_T|less mysqlbinlog --no-defaults -vvv mysql-bin.001405 | perl parse_binlog_preimage.pl > out.sql #查看碎片 SELECT CONCAT(table_schema, '.', table_name) AS TABLE_NAME ,engine AS TABLE_ENGINE ,table_type AS TABLE_TYPE ,table_rows AS TABLE_ROWS ,CONCAT(ROUND(data_length / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE ,CONCAT(ROUND(index_length / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE ,CONCAT(ROUND((data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE ,CASE WHEN data_length =0 THEN 0 ELSE ROUND(index_length / data_length, 2) END AS TB_INDX_RATE ,CONCAT(ROUND( data_free / 1024 / 1024,2), 'MB') AS TB_DATA_FREE ,CASE WHEN (data_length + index_length) = 0 THEN 0 ELSE ROUND(data_free/(data_length + index_length),2) END AS TB_FRAG_RATE FROM information_schema.TABLES WHERE ROUND(DATA_FREE/1024/1024,2) >=50 ORDER BY data_free DESC; #查询数据库中的存储过程和函数 select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' //存储过程 select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' //函数 show procedure status; //存储过程 show function status; //函数 #查看存储过程或函数的创建代码 show create procedure proc_name; show create function func_name; #查看视图 SELECT * from information_schema.VIEWS //视图 SELECT * from information_schema.TABLES //表 #查看触发器 SHOW TRIGGERS [FROM db_name] [LIKE expr] SELECT * FROM triggers T WHERE trigger_name=”mytrigger” \G ##备份8.0## 1 xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表； 2 innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份。 #mixed 采用默认隔离级别REPEATABLE-READ，那么建议binlog_format=ROW。如果你是READ-COMMITTED隔离级别，binlog_format=MIXED和binlog_format=ROW效果是一样的，binlog记录的格式都是ROW #全备 xtrabackup --defaults-file=/etc/my8.cnf --user=root --password=970125 --host=127.0.0.1 --port=3307 --backup --parallel=2 --target-dir=/data/backup/&>>/data/log #一次增备 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --backup --parallel=2 --target-dir=/data/backupincr --incremental-basedir=/data/backup&>>/data/incrlog #二次增倍 xtrabackup --defaults-file=/etc/my.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --backup --parallel=2 --target-dir=/data/backupincr2 --incremental-basedir=/data/backupincr&>>/data/incrlog #应用增备日志 xtrabackup --prepare --apply-log-only --target-dir=/data/backup xtrabackup --prepare --apply-log-only --target-dir=/data/backup --incremental-dir=/data/backupincr xtrabackup --prepare --target-dir=/data/backup --incremental-dir=/data/backupincr2 xtrabackup --prepare --target-dir=/data/backup #进行恢复 xtrabackup --defaults-file=/etc/my3307.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --copy-back --target-dir=/data/backup/ touch -f error.log && chown -R mysql:mysql dir ##备份5.7## xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的 表，也不能备份数据表结构； innobackupex：是将xtrabackup进行封装的perl脚本，可以备份和恢复MyISAM create user backup@'127.0.0.1' identified by 'backup'; grant RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT on *.* to backup@'127.0.0.1'; #全备 innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 /data/backup/ --no-timestamp --parallel=2 &>>/data/log #一次增备 innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --incremental-basedir=/data/backup --incremental /data/backupincr --no-timestamp --parallel=2 &>>/data/incrlog #二次增倍 innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --incremental-basedir=/data/backupincr --incremental /data/backupincr2 --no-timestamp --parallel=2 &>>/data/incrlog #应用增备日志 innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --apply-log --redo-only /data/backup innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --apply-log --redo-only /data/backup --incremental-dir=/data/backupincr innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --apply-log /data/backup --incremental-dir=/data/backupincr2 innobackupex --defaults-file=/etc/my57.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --apply-log /data/backup #进行恢复 innobackupex --defaults-file=/etc/mybackup.cnf --user=root --password=970125 --host=127.0.0.1 --port=3306 --copy-back /data/backup/ touch error.log && chown -R mysql:mysql dir mysqld_safe --defaults-file=/etc/my.cnf & #5.6初始化 ./scripts/mysql_install_db --defaults-file=my.cnf --user=admin ./bin/mysqladmin -u root -S /data/my3306/run/mysql.sock password 'cGSALLEfAbiA' #mysqldump备份 mysqldump --defaults-file=/etc/my.cnf -uroot -p970125 --socket=/data/mysql57/data/mysql57.sock --default-character-set=utf8mb4 --single-transaction --master-data=2 --skip-add-drop-table -e --triggers --routines --events --flush-logs --all-databases >all.sql #mysqldump部分备份 mysqldump --defaults-file=/etc/my.cnf -uroot -p970125 --socket=/data/mysql57/data/mysql57.sock --default-character-set=utf8mb4 --single-transaction --master-data=2 --skip-add-drop-table -e --skip-tz-utc --flush-logs --databases=test --tables=t1 --where>test.sql --set-gtid-purged=OFF more xtraback_binlog_info #binlog增量恢复 mysqlbinlog -vv --base64-output=decode-rows mysql-bin.007902 --start-datetime="2019-11-28 12:00:00" --stop-datetime="2019-11-28 12:20:00" mysqlbinlog mysql-bin.000688 mysql-bin.000689 --start-position=531167 --stop-datetime="16-05-16 18:05:03" | mysql -uroot -p970125 -P3306 -h127.0.0.1 如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，那么就要使用该参数。如果不带该参数的话，是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了，根据 GTID 特性，一个 GTID 信息在一个数据库只能执行一次，所以不会恢复成功。 #分析慢日志 mysqldumpslow -s c slow.log>/tmp/slow_report.txt -s 按照那种方式排序 c：访问计数 l：锁定时间 r:返回记录 al：平均锁定时间 ar：平均访问记录数 at：平均查询时间 -t 是top n的意思，返回多少条数据。 -g 可以跟上正则匹配模式，大小写不敏感。 ##传输表空间## 1.MySQL 5.6及以上版本 2.打开innodb_file_per_table 3.Linux中开启lower_case_table_names 4.MySQL 5.6中有些版本不支持分区表的传输表空间，5.7支持 5.源库和目标库的page size必须一致 6.设置foreign_key_checks=0. 7. ALTER TABLE ... IMPORT TABLESPACE 不强制检查主外键关系。如果表中有外键，应该同时导出主表和子表。 8.源库和目标库的版本最好一致 SHOW VARIABLES WHERE variable_name IN ('version','innodb_file_per_table','lower_case_table_names','innodb_page_size'); 1.目标端创建同样的表结构 2.目标端卸载表空间 alter table test.t1 discard tablespace; 3.源端对表加一个读锁(会话不要关) flush table test.test for export; 4.拷贝.cfg和.ibd文件到目标端数据文件位置 scp test.ibd root@192.168.10.101:/usr/local/mysql/data/test scp test.cfg root@192.168.10.101:/usr/local/mysql/data/test 5.源端释放锁 unlock tables; 6.目标端文件赋予权限 chown -R mysql:mysql * chmod -R 755 * 7.目标端导入表 alter table test.t1 import tablespace; #innotop的使用 #进入仪表盘，？进入帮助 innotop -uroot -p970125 shift+A: shift+B:显示有关InnoDB缓冲池，页面统计，插入缓冲，自适应哈希索引。这些数据来自展示InnoDB的状态。 shift+C:汇总表通过提取STATUS_VARIABLES变量。变量必须是数字，必须匹配给定的cmd_filter配置变量的前缀。百分比列是在表中的所有变量总数的百分比，所以你可以看到变数的相对比例。默认的前缀是“Com_”。你可以选择的‘s‘键改变过滤前缀。 shift+D:在过去InnoDB的死锁中涉及的事务。第二个表显示等待事务。 shift+F:显示最后InnoDB的外键的错误信息,没有就不显示 shift+I:显示InnoDB的I/O信息，包括I/O线程，挂起的I/O，文件I/O，日志统计。 shift+K:显示锁等待信息 shift+L:显示了当前锁的信息。目前只支持InnoDB，并在默认情况下你只看到那些锁等待的事务 shift+M:输出了SHOW SLAVE STATUS和SHOW MASTER STATUS的信息成3张表，前两个显示从的sql和I/O的状态，最后显示主的状态 shift+O:来自SHOW OPEN TABLES命令的输出，默认情况下，筛选出正由一个或多个查询使用的表，这样可以快速得到哪些表是‘热‘。也可以猜测哪些表可能被锁。 shift+Q:此模式显示从SHOW FULL PROCESSLIST的信息，就像mytop的查询列表模式。有一个信息头，显示有关服务器的一般状态信息。可以用‘h‘切换开或关。默认情况下，innotop隐藏不活动的进程和它自己的进程。您可以切换和关闭这些用‘i‘和‘a‘键。按e并输入thread ID显示执行计划或者按f显示完整sql语句，或者按o显示系统优化过的语句. shift+R:显示InnoDB行操作、row operation miscellaneous、信号、innodb等待信息 shift+S:每秒查询的统计信息。您可以显示绝对值之间，或增量值。‘s‘ 键是没一定时间打印数字到前面.‘g‘ 键是打印图像.‘v‘ 键是以一个表格的形式显示，定期刷新值。 shift+T:从innodb监视器中输出事务。你可以杀掉一个查询或进程，通过‘k‘和‘x‘键，也可以explain一个查询，通过‘e‘或‘f‘键。不会输出所有的查询事务，如果查询太长被截断。信息头可以用‘h‘键打开或关闭，默认情况下，innotop隐藏不活动的进程和它自己的进程。您可以切换和关闭这些用‘i‘和‘a‘键。 shift+U:显示用户信息 a Toggle the innotop process(切换innotop进程) k Kill a query‘s connection(杀死一查询的连接) c Choose visible columns (可见列选择) n Switch to the next connection(切换到下一个连接) d Change refresh interval (更改刷新间隔) p Pause innotop (暂停innotop) e Explain a thread‘s query (说明线程的查询) q Quit innotop (退出) f Show a thread‘s full query(显示线程的完整查询) r Reverse sort order (反向排序) h Toggle the header on and off(头切换和关闭) s Change the display‘s sort column(更改显示的排序列) i Toggle idle processes (切换空闲进程) x Kill a query (杀死一查询) TAB切换到下一个服务器组 /快速筛选所看到的内容 ! 许可 =切换聚合 #选择/创建服务器组 @选择/创建服务器连接 $编辑配置设置 \清除快速筛选 #查找数据库中存在某列的表 SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'columnName' AND TABLE_SCHEMA='dbName' AND TABLE_NAME NOT LIKE 'vw%'; #查找createtime带有on update CURRENT_TIMESTAMP等系列检索 SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'modify_time' and extra!='on update CURRENT_TIMESTAMP'; #查找数据库中不存在某列的表 select table_schema,table_name from information_schema.tables where table_schema not in ('information_schema','mysql','sys','performance_schema') and TABLE_NAME NOT IN(SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'modify_time'); # 查看数据库大小 SELECT table_schema "DB Name",Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 1) "DB Size in GB" FROM information_schema.tables GROUP BY table_schema; # 查看表大小 SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024 / 1024), 2) `Size in GB` FROM information_schema.TABLES where table_schema = 'test' ORDER BY (data_length + index_length) DESC ; 1、显示mysql当前状态 mysqladmin -uroot -p status Uptime: 182341 Threads: 7 Questions: 2831137 Slow queries: 0 Opens: 1536 Flush tables: 3 Open tables: 1171 Queries per second avg: 15.526 Uptime: 182341 MySQL服务器已经运行的秒数 Threads: 7 活跃线程（客户）的数量 Questions: 2831137 从mysqld启动起来自客户查询的数量 Slow queries: 0 已经超过long_query_time的查询数量 Opens: 1536 已经打开了多少表 Flush tables: 3 Open tables: 1171 现在被打开的表数量 Queries per second avg: 15.526 查询平均用时 2、连接数max_connections sql> show variables like '%connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 2000 | | max_user_connections | 0 | | mysqlx_max_connections | 100 | +------------------------+-------+ 如果连接数达到最大连接数，那不管剩余多少资源，用户的连接请求都会阻塞在外面。 max_connections，最大连接数，默认100，一般经验设置3000。win服务器连接数支持1500-1800，linux服务器可以支持8000个左右。 另外设置max_user_connections=0，表示不限制用户的最大连接数，其最大值可以等于max_connections sql> show global status like 'max_used_connections'; 检查曾经使用最大的连接数，这个值在max_connections的85%左右比较合适，过高，则会系统使用连接数过少，系统负荷过高。 3、查看 Mysql 连接数、状态、最大并发数 sql> show status like '%max_connections%'; ##mysql最大连接数 sql> set global max_connections=1000 ##重新设置 sql> show variables like '%max_connections%'; ##查询数据库当前设置的最大连接数 sql> show global status like 'Max_used_connections'; ##服务器响应的最大连接数 sql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 34 | | Threads_connected | 32 | | Threads_created | 66 | | Threads_running | 2 | +-------------------+-------+ rows in set 参数说明： Threads_cached 34 ##mysql管理的线程池中还有多少可以被复用的资源 Threads_connected 32 ##打开的连接数 Threads_created 66 ##代表新创建的thread（根据官方文档，如果thread_created增大迅速，需要适当调高 thread_cache_size）。 Threads_running 2 ##激活的连接数，这个数值一般远低于connected数值，准确的来说，Threads_running是代表当前并发数 sql> show variables like 'thread_cache_size'; sql> set global thread_cache_size=60; #mysql收集统计信息 一、手动 执行Analyze table innodb和myisam存储引擎都可以通过执行“Analyze table tablename”来收集表的统计信息，除非执行计划不准确，否则不要轻易执行该操作，如果是很大的表该操作会影响表的性能。 二、自动触发 以下行为会自动触发统计信息的收集 1.第一次打开表的时候 2.表修改的行超过1/16或者20亿条时 3.当有新的记录插入时 4.执行show index from tablename或者执行show table stauts、查询information_schema.tables\statistics 时 三、开启参数innodb_stats_on_metadata 当开启参数innodb_stats_on_metadata后访问以下表也会触发统计信息的收集 在访问以下表时，innodb表的统计信息可自动收集 information_schema.TABLES information_schema.STATISTICS information_schema.PARTITIONS information_schema.KEY_COLUMN_USAGE information_schema.TABLE_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS information_schema.table_constraints 参数说明： 相关参数： 持久化： innodb_stats_persistent：on(1) innodb_stats_persistent_sample_pages：20 非持久化： innodb_stats_sample_pages:8 Innodb_stats_sample_pages：每次收集统计信息时采样的页数，默认为20 innodb_stats_persistent：默认on，将analyze table产生的统计信息保存于磁盘，直至下次analyze table为止，此举避免了统计信息动态更新，保证了执行计划的稳定，对于大表也节省了收集统计信息的所需资源; #查看临时表空间占用具体表 select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO; #查会话 show full processlist where command<> 'SLEEP' and time>10 #kill会话 select concat('KILL ',id,';') from information_schema.processlist where user!='monitor'; #查看元数据锁 SELECT * FROM performance_schema.setup_instruments; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl'; select * from performance_schema.metadata_locks\G #查看当前事务号及权重 SELECT `trx_id`,`trx_weight` FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID(); #查看事务等待状况 SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; select * from innodb_trx a left join processlist b on b.id = a.trx_mysql_thread_id where a.trx_id in ( select blocking_lock_id from INNODB_LOCK_WAITS where blocking_lock_id not in ( select requesting_trx_id from INNODB_LOCK_WAITS)) order by b.time desc #查看更具体的事务等待状况 SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id FROM information_schema.INNODB_LOCK_WAITS a LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id ORDER BY a.requesting_trx_id; #5.6查看未关闭的事务 SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT FROM information_schema.INNODB_TRX a LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep' LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID; #查看某段时间以来未关闭事务： SELECT trx_id, trx_started, trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_started < date_sub(now(), INTERVAL 1 MINUTE) AND trx_operation_state IS NULL AND trx_query IS NULL; #查找没有主键的表 select table_schema,table_name from information_schema.tables where (table_schema,table_name) not in( select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI' ) and TABLE_TYPE='BASE TABLE' and table_schema not in ('sys','mysql','information_schema','performance_schema'); #查看数据库所有表的字段数据类型 select t.TABLE_NAME ,t.TABLE_COMMENT ,c.COLUMN_NAME ,c.COLUMN_TYPE ,c.COLUMN_COMMENT from information_schema.`COLUMNS` c ,information_schema.`TABLES` t where c.TABLE_NAME = t.TABLE_NAME and t.table_schema not in ('sys','mysql','information_schema','performance_schema'); #查看是否存在非主键/唯一键表 SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN information_schema.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE T1.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') AND T2.TABLE_TYPE='BASE TABLE' GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI'; #全库count select count(*) from tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); select concat('select ', 'count(*) from ',table_schema,'.',table_name,';' ) from tables where table_schema not in ('sys','mysql','information_schema','performance_schema'); #查询重复及冗余索引 SELECT a.TABLE_SCHEMA AS '数据名', a.TABLE_NAME AS '表名', a.INDEX_NAME AS '索引1', b.INDEX_NAME AS '索引2', a.COLUMN_NAME AS '重复列名' FROM information_schema.STATISTICS a JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX AND a.COLUMN_NAME = b.COLUMN_NAME WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME; #反映表的读写压力 SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number_of_bytes_read + sum_number_of_bytes_write) AS total FROM performance_schema.file_summary_by_instance ORDER BY sum_number_of_bytes_read+ sum_number_of_bytes_write DESC; #反映文件的延迟 SELECT (file_name) AS file, count_star AS total, CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') AS total_latency, count_read, CONCAT(ROUND(sum_timer_read / 1000000000000, 2), 's') AS read_latency, count_write, CONCAT(ROUND(sum_timer_write / 3600000000000000, 2), 'h')AS write_latency FROM performance_schema.file_summary_by_instance ORDER BY sum_timer_wait DESC; #表的读写延迟 SELECT object_schema AS table_schema, object_name AS table_name, count_star AS total, CONCAT(ROUND(sum_timer_wait / 3600000000000000, 2), 'h') as total_latency, CONCAT(ROUND((sum_timer_wait / count_star) / 1000000, 2), 'us') AS avg_latency, CONCAT(ROUND(max_timer_wait / 1000000000, 2), 'ms') AS max_latency FROM performance_schema.objects_summary_global_by_type ORDER BY sum_timer_wait DESC; #查看表操作频度 SELECT object_schema AS table_schema, object_name AS table_name, count_star AS rows_io_total, count_read AS rows_read, count_write AS rows_write, count_fetch AS rows_fetchs, count_insert AS rows_inserts, count_update AS rows_updates, count_delete AS rows_deletes, CONCAT(ROUND(sum_timer_fetch / 3600000000000000, 2), 'h') AS fetch_latency, CONCAT(ROUND(sum_timer_insert / 3600000000000000, 2), 'h') AS insert_latency, CONCAT(ROUND(sum_timer_update / 3600000000000000, 2), 'h') AS update_latency, CONCAT(ROUND(sum_timer_delete / 3600000000000000, 2), 'h') AS delete_latency FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC ; #索引状况 SELECT OBJECT_SCHEMA AS table_schema, OBJECT_NAME AS table_name, INDEX_NAME as index_name, COUNT_FETCH AS rows_fetched, CONCAT(ROUND(SUM_TIMER_FETCH / 3600000000000000, 2), 'h') AS select_latency, COUNT_INSERT AS rows_inserted, CONCAT(ROUND(SUM_TIMER_INSERT / 3600000000000000, 2), 'h') AS insert_latency, COUNT_UPDATE AS rows_updated, CONCAT(ROUND(SUM_TIMER_UPDATE / 3600000000000000, 2), 'h') AS update_latency, COUNT_DELETE AS rows_deleted, CONCAT(ROUND(SUM_TIMER_DELETE / 3600000000000000, 2), 'h')AS delete_latency FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ORDER BY sum_timer_wait DESC; #全表扫描情况 SELECT object_schema, object_name, count_read AS rows_full_scanned FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NULL AND count_read > 0 ORDER BY count_read DESC; #没有使用的index SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 AND object_schema not in ('mysql','v_monitor') AND index_name <> 'PRIMARY' ORDER BY object_schema, object_name; #糟糕的sql问题摘要 SELECT (DIGEST_TEXT) AS query, SCHEMA_NAME AS db, IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan, COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, (SUM_TIMER_WAIT) AS total_latency, (MAX_TIMER_WAIT) AS max_latency, (AVG_TIMER_WAIT) AS avg_latency, (SUM_LOCK_TIME) AS lock_latency, format(SUM_ROWS_SENT,0) AS rows_sent, ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_examined, ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, SUM_CREATED_TMP_TABLES AS tmp_tables, SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables, SUM_SORT_ROWS AS rows_sorted, SUM_SORT_MERGE_PASSES AS sort_merge_passes, DIGEST AS digest, FIRST_SEEN AS first_seen, LAST_SEEN as last_seen FROM performance_schema.events_statements_summary_by_digest d ORDER BY SUM_TIMER_WAIT DESC limit 20; #主从 5.7.6开始可以在线切换 gtid-mode的几种状态说明： off :不产生gtid，基于binlog+position，slave也不能接受gtid的日志; off_permissive:不产生gtid，但做为slave可以识别gtid事务也可以识别非gtid事务; on_permissive:产生gtid事务，slave可以处理gtid事务和非gtid事务; on:产生gtid事务，slave只接受gtid事务。 传统--->GTID 1.所有的Server执行 set @@global.enforce_gtid_consistency = warn; 特别注意： 这一步是关建的一步使用不能出现警告。 2.所有的server上执行 set @@global.enforce_gtid_consistency = on; 3.所有的Server上执行（要执行完） set @@global.gtid_mode = off_permissive; set @@global.gtid_mode=on_permissive; #产生gtid的日志，这个步骤号称是不关心任何节点，但从管理上推荐在slave上先执行，然后再去master上执行。 4.传统的binlog复制完成确认 show status like 'ongoing_anonymous_transaction_count'; 需要所有的节点都确认为0. 所有的节点也可以执行一下: flush logs; 用于切换一下日志。 5. 所有的节点启用gtid_mode set @@global.gtid_mode=on; 6.配置文件修改 gtid_mode=on enforce_gtid_consistency=on 7.启用gtid的自动查找节点复制 stop slave for channel 'master-3306101'; change master to master_auto_position=1 for channel 'master-3306101'; start slave for channel 'master-3306101' GTID--->传统 就是上面的反向过程 1.停止复制 stop slave for channel 'master-3306101'; show slave status\G;查看Exec_Master_Log_Pos的位置 change master to master_host='10.20.30.101',master_user='repl',master_password='repl4slave',master_port=3306,master_auto_position=0,master_log_file='mysql-bin.000008',master_log_pos=83942 for channel 'master-3306101'; start slave for channel 'master-3306101' 2.主库先更改日志格式，然后从库更改 set @@global.gtid_mode=on_permissive; set @@global.gtid_mode=off_permissive; 3. select @@global.gtid_owned; 要为空才正常 4.动态修改参数 set @@global.gtid_mode=off; set @@global.enforce_gtid_consistency = off; 5.my.cnf修改 相关表：slave_master_info、slave_relay_log_info、gtid_executed #读取的主库binlog Master_Log_File #读取主库binlog位点 Read_Master_Log_Pos #SQL线程执行到哪个文件 Relay_Master_Log_File #SQL形成执行到的位点 Exec_Master_Log_Pos #GTID接受到的集合 Retrieved_Gtid_Set #执行的集合 Executed_Gtid_Set ##查看GTID状态 show master status; #查看binlog show master logs; #查看UUID select @@server_uuid; #查看binlog show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; #建立主从 change master to master_host='127.0.0.1',master_user='repl',master_password='repl',master_port=3306,master_log_file='mybinlog.000008',master_log_pos=80711; CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1; ##GTID## #停止复制 mysql>stop slave; #然后执行设置一个事务GTID来跳过，就是要跳过这个事务的意思 mysql>SET gtid_next = '09cb91bf-2669-11e7-8b70-00163e0835ff:3648451'; #注入空事务 mysql>BEGIN;COMMIT; #把GTID设置回自动模式 mysql>SET gtid_next = 'AUTOMATIC'; #重新开启复制 mysql>START SLAVE; #当然跳过了，并不代表这个数据就不修改了，还是要你手动去修改一下，这样就一切回归正常了 mysql>update tables set 。。。。。。。 #停止复制 mysql>stop slave; #直接设置上面的GTID值+1 mysql>SET @@GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-83648452'; #重新开启复制 mysql>START SLAVE; #当然跳过了，并不代表这个数据就不修改了，还是要你手动去修改一下，这样就一切回归正常了 mysql>update tables set 。。。。。。。 ##传统模式## #停止复制 mysql>stop slave; #设定跳过一个事务 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #重新开启复制 mysql>start slave; #这样就正常了，但是，当然还是要把数据修改上去 mysql>update tables set 。。。。。。。 RESET MASTER： 删除binlog索引文件中列出的所有binlog文件 清空binlog索引文件 创建一个新的binlog文件 清空系统变量gtid_purged和gtid_executed 在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 mysql.gtid_executed数据表 RESET SLAVE： 清除slave 复制时的master binlog的位置 清空master info, relay log info 删除所有的relay log文件，并创建一个新的relay log文件。 重置复制延迟(CHANGE MASTER TO 的 MASTER_DELAY参数指定的)为0 大事物延迟，延迟略为2执行时间，状态为:reading event from the relay log 大表DDL延迟，延迟略为1执行时间，状态为:altering table 长期未提交的事物延迟，会造成延迟的瞬时增加 表上没有主键或者唯一键，状态为:system lock或者reading event from the relay log Innodb层锁造成延迟，状态为:system lock或者reading event from the relay log 从库参数设置如sync_binlog，sync_relay_log，Innodb_flush_log_at_trx_commit等参数 从库出现system lock 应该视为正在干活，而不是名称看到的“lock”，这是由于slave端不存在语句（row格式）的执行，都是Event的直接apply，状态没有切换的机会，也可以认为是slave端状态划分不严谨，其实做一个pstack就能完全看出问题。下面是产生的必要条件： 由于大量的小事物，比如如UPDATE/DELETE table where处理一行数据，这会出现只包含一行数据库的DML event的语句，如果table是一张大表，则会加剧这种可能。 这个表上没有主键或者唯一键，问题加剧。 由于类似Innodb lock堵塞，也就是slave从库修改了数据同时和sql_thread也在修改同样的数据，问题加剧。 确实I/O扛不住了，可以尝试修改参数。 如果是大量的表没有主键或者唯一键可以考虑修改参数slave_rows_search_algorithms 如果一个小事物只有一条DML event的场景下逻辑如下： ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->进入system lock状态 ->Innodb层进行查找和修改数据 如果是一个大事物则包含了多条DML event的场景逻辑如下: ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->进入system lock状态 ->Innodb层进行查找和修改数据 ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->Innodb层进行查找和修改数据 ->进入reading eventfrom the relay log状态 ->读取一条event(参考next_event函数) ->Innodb层进行查找和修改数据 ....直到本事物event执行完成 因此对于一个小事物我们的sql_thread会在加system lock的情况下进行对数据进行查找和修改。 如果是Innodb层锁造成的sqlthread堵塞也会在持有system lock的状态下。但是对于一个大事物则不一样，虽然出现同样的问题，但是其状态是reading event from the relay log。 MySQL从库show processlist出现system lock的原因以及解决方法有哪些？ 由于大量的小事物如UPDATE/DELETE table where一行数据，这种只包含一行DML event的语句，table是一张大表。 1、这个表上没有主键或者唯一键，可以考虑尝试修改参数slave_rows_search_algorithms。 2、由于类似innodb lock堵塞，也就是slave从库修改了数据同时和sql_thread也在修改同样的数据。 3、确实I/O扛不住了，修改sync_binlog/innodb_flush_log_at_trx_commit或者提高IO子系统的IO能力 #对应字节 varchar(50)，指的是50字符，无论存放的是数字、字母还是UTF8中文（每个中文3字节），都可以存放50个 存储限制 需要额外占用字节存放字符的长度:小于255为1个字节，大于255则要2个字节 编码限制 gbk :每个字符最多占用2个字节 utf8:每个字符最多占用3个字节 utf8mb4 每个字符最多占用4个字节,中文占3个字节，emoji表情符号 占用4个字节 长度限制 MySQL定义行的长度不能超过65535，该数值限制了列的数目，比如char(128) utf8字符集,最多有65535/(128*3)=170个汉字。 字符型： char(n)：4n个字节 varchar(n)：4n+2个字节 数值型： tinyint：1个字节 int：4个字节 bigint：8个字节 时间型： date：3个字节 datetime：5个字节+秒精度字节 timestamp：4个字节+秒精度字节 秒精度字节(最大6位)： 1~2位：1个字节 3~4位：2个字节 5~6位：3个字节 MySQL默认的pagesize为16KB = 16384字节。B+树叶子节点中刨除数据库系统使用的空间之后，可供数据使用的空间大约为15212字节。B+树非叶子节点中刨除数据库系统使用的空间之后，可供数据使用的空间大约为16241字节。 假设org_info的varchar都是用满的状态，那么整个行的大小约为1155字节（算上record header等数据库附加的信息），根据以下公式可以算出每个叶子节点可以容纳的行数： N * 1155 + (N / 4) * 2 = 15212 推算出N大约等于13 id char(36)是主键，在B+树里大概用46字节，根据以下公式可以算出每个非叶子节点可以容纳的行数： N * 46 + (N / 4) * 2 = 16241 推算出N大约等于349 所以3层的B+树，org_info最少能存放的数据为349*349*13=1583413行。 由于varchar不一定能够用满，所以现在1726764行仍然是3层，但已经超过了1583413的警戒值。需要考虑拆表。 1、DML命令执行过程中文件排序(file sort)操作生成的临时文件，存储目录由参数tmpdir控制，以MY开头 2、超大事务提交生成binary log过程中用于数据缓存的临时文件，存储目录由参数tmpdir控制，以ML开头 3、使用ROW_FORMAT=COMPRESSED创建的压缩InnoDB临时表，存储目录由参数tmpdir控制，以frm和idb结尾 4、没用ROW_FORMAT=COMPRESSED创建的非压缩压缩InnoDB临时表，存储在由参数innodb_temp_data_file_path指定的共享临时文件中 5、使用COPY模式的Onine DDL创建的临时文件，存储在操作表相同目录下，以sql-开头，以frm和idb结尾 6、使用INPLACE模式的Onine DDL创建的临时文件，存储在操作表相同目录下，以sql-和sql-ib开头，以frm和idb结尾 7、Online DDL操作过程中排序操作使用的临时文件，存储目录由参数tmpdir控制，以ib开头 8、MySQL InnoDB存储引擎层使用的内部临时表，存储在由参数innodb_temp_data_file_path指定的共享临时文件中 事物开启： 执行dml语句，在dml语句第一次执行的时候会分配内存空间binlog cache。 执行dml语句期间生成的event不断写入到binlog cache。 如果binlog cache的空间已经满了，则将binlog cache的数据写入到binlog临时文件，同时清空binlog cache。如果binlog临时文件的大小大于了max_binlog_cache_size的设置则抛错ERROR 1197 (HY000)。 事物提交，整个binlog cache和binlog临时文件数据全部写入到binlog file中进行固化，释放binlog cache和binlog临时文件。但是注意此时binlog cache的内存空间留用供下次事物使用，但是binlog临时文件被截断为0，保留文件描述符。其实也就是IO_CACHE(参考后文)保留，并且保留IO_CACHE中的分配的内存空间，和物理文件描述符。 断开连接，这个过程会释放IO_CACHE同时释放其持有的binlog cache内存空间以及持有的binlog 临时文件。 #删除自增主键 1、alter table t9 modify id int(11); #这里用的modify，只改变数据类型，也可以用change，改变列名的同时输入新的数据类型。 2、alter table t9 drop primary key; #删除主键 ###优化### 1、not exist 改写left join 右表关联列is null，inner join根据驱动表改写未STRAIGHT_JOIN 2、exist 改写为inner join 3、延迟关联 4、覆盖索引 5、in和join互相改写 6、自连接 7、嵌套子查询 8、能加limit最好有limit 9、先外层后内层，外层驱动表最好要小 能尝试，多实践 gmt_created>= DATE_FORMAT(curdate(), '%Y-%m-%d %H:%i:%s') and gmt_created< DATE_FORMAT(DATE_ADD(curdate(), INTERVAL 1 DAY), '%Y-%m-%d %H:%i:%s') #profile SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS } #mysql开启trace SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; #追踪mysql执行语句 strace -o /tmp/zst_strace.log -T -tt -ff -p `pidof mysqld` & 根据`select PROCESSLIST_ID,name,THREAD_OS_ID from performance_schema.threads;`找到对应的THREAD_OS_ID文件。 #查看mysql的所有线程 ps -mp mysqlpid -o THREAD,tid,time #查看mysql打开的所有文件 lsof -p mysqlpid #开启自启MySQL 1、将服务文件拷贝到init.d下，并重命名为mysql cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 2、赋予可执行权限 chmod +x /etc/init.d/mysqld 3、添加服务 chkconfig --add mysqld 4、显示服务列表 chkconfig --list ▲：如果看到mysql的服务如上图所示2,3,4,5都是开的话则成功，默认级别是2345 chkconfig --level 2345 mysqld on #物理恢复文件 ps -ef | grep mysql 然后在/proc里面找到这个PID对应的文件夹的fd目录里面（文件名=pid） 先执行flush tables with readlock;再执行flush logs; 然后将文件拷回之后授权 #使用information_schema.optimize_trace看问题 set optimizer_trace="enabled=on"; select * from information_schema.optimizer_trace; MDL等待: -- 临时：UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; -- 永久：[mysqld]performance-schema-instrument='wait/lock/metadata/sql/mdl=ON' SELECT locked_schema, locked_table, locked_type, waiting_processlist_id, waiting_age, waiting_query, waiting_state, blocking_processlist_id, blocking_age, substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query, sql_kill_blocking_connection FROM ( SELECT b.OWNER_THREAD_ID AS granted_thread_id, a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID ) t1, ( SELECT thread_id, group_concat(CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) t2 WHERE t1.granted_thread_id = t2.thread_id; 事务锁等待： select r.trx_isolation_level, r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_trx_thread, r.trx_state waiting_trx_state, lr.lock_mode waiting_trx_lock_mode, lr.lock_type waiting_trx_lock_type, lr.lock_table waiting_trx_lock_table, lr.lock_index waiting_trx_lock_index, r.trx_query waiting_trx_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_trx_thread, b.trx_state blocking_trx_state, lb.lock_mode blocking_trx_lock_mode, lb.lock_type blocking_trx_lock_type, lb.lock_table blocking_trx_lock_table, lb.lock_index blocking_trx_lock_index, b.trx_query blocking_query, substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query, concat('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id inner join information_schema.innodb_locks lb on lb.lock_trx_id = w.blocking_trx_id inner join information_schema.innodb_locks lr on lr.lock_trx_id = w.requesting_trx_id inner join performance_schema.threads tr on tr.PROCESSLIST_ID=b.trx_mysql_thread_id inner join ( SELECT thread_id, group_concat(CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) t2 on tr.THREAD_ID=t2.THREAD_ID; #### select trx_id,trx_started,TRX_MYSQL_THREAD_ID from information_schema.innodb_trx where trx_lock_structs >= 5 or -- 超过5把锁 trx_rows_locked >= 100 or -- 超过100行被锁 trx_rows_modified >= 100 or -- 超过100行被修改 time_to_sec(timediff(now(),trx_started)) > 100; #死锁 1. 0: len 8; hex 800000000000ac75; asc u;;//聚集索引值 2. 1: len 6; hex 00012d4a4145; asc -JAE;;//事务ID 3. 2: len 7; hex 520001400f0dcb; asc R @ ;;//undo回滚段指针 #define LOCK_TABLE 16 /* table lock */ #define LOCK_REC 32 /* record lock */ /* Precise modes */ #define LOCK_ORDINARY 0 #define LOCK_GAP 512 #define LOCK_REC_NOT_GAP 1024 #define LOCK_INSERT_INTENTION 2048 一、连接相关 查看某用户连接的会话级别参数设置及状态变量，用于观测其它会话连接行为，辅助定位连接类问题 例：查看用户连接 ID 为 19 的字符集设置，也可不指定 PROCESSLIST_ID 条件，查看所有用户连接 SELECT T1.VARIABLE_NAME, T1.VARIABLE_VALUE, T2.PROCESSLIST_ID, concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST), T2.PROCESSLIST_DB, T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID AND T1.VARIABLE_NAME LIKE 'character%' AND PROCESSLIST_ID ='19';+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) | PROCESSLIST_DB | PROCESSLIST_COMMAND |+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+| character_set_client | gbk | 19 | root@localhost | db | Query || character_set_connection | gbk | 19 | root@localhost | db | Query || character_set_database | utf8mb4 | 19 | root@localhost | db | Query || character_set_filesystem | binary | 19 | root@localhost | db | Query || character_set_results | gbk | 19 | root@localhost | db | Query || character_set_server | utf8mb4 | 19 | root@localhost | db | Query |+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+6 rows in set (0.01 sec) 例：发现用户 ID 为 254 的连接关闭了 sql_log_bin 设置 SELECT T1.VARIABLE_NAME, T1.VARIABLE_VALUE, T2.PROCESSLIST_ID, concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host', T2.PROCESSLIST_DB, T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID AND T1.VARIABLE_NAME LIKE 'sql_log_bin';+---------------+----------------+----------------+------------------+----------------+---------------------+| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | User@Host | PROCESSLIST_DB | PROCESSLIST_COMMAND |+---------------+----------------+----------------+------------------+----------------+---------------------+| sql_log_bin | OFF | 254 | root@localhost | NULL | Sleep || sql_log_bin | ON | 256 | root@localhost | NULL | Sleep || sql_log_bin | ON | 257 | root@10.211.55.2 | NULL | Sleep || sql_log_bin | ON | 258 | root@10.211.55.2 | NULL | Sleep || sql_log_bin | ON | 259 | root@localhost | NULL | Query || sql_log_bin | ON | 261 | root@localhost | NULL | Sleep |+---------------+----------------+----------------+------------------+----------------+---------------------+4 rows in set (0.00 sec) 例：查看用户连接 ID 为 24 的网络流量变化 SELECT T1.VARIABLE_NAME, T1.VARIABLE_VALUE, T2.PROCESSLIST_ID, concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host', T2.PROCESSLIST_DB, T2.PROCESSLIST_COMMANDFROM PERFORMANCE_SCHEMA.STATUS_BY_THREAD T1, PERFORMANCE_SCHEMA.THREADS T2WHERE T1.THREAD_ID = T2.THREAD_ID AND T2.PROCESSLIST_USER = 'root' AND PROCESSLIST_ID= 24 AND VARIABLE_NAME LIKE 'Byte%';+----------------+----------------+----------------+----------------+----------------+---------------------+| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | User@Host | PROCESSLIST_DB | PROCESSLIST_COMMAND |+----------------+----------------+----------------+----------------+----------------+---------------------+| Bytes_received | 224 | 24 | root@127.0.0.1 | NULL | Sleep || Bytes_sent | 182 | 24 | root@127.0.0.1 | NULL | Sleep |+----------------+----------------+----------------+----------------+----------------+---------------------+2 rows in set (0.00 sec) 二、长事务 事务开启后，超过 5s 未提交的用户连接 SELECT trx_mysql_thread_id AS PROCESSLIST_ID,NOW(), TRX_STARTED,TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME , USER,HOST,DB, TRX_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE trx_mysql_thread_id != connection_id() AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 5 ; 三、元数据锁 MySQL 5.7 开启元数据锁追踪，以便追踪定位元数据锁相关的阻塞问题 // 临时开启，动态生效UPDATE performance_schema.setup_consumersSET ENABLED = 'YES'WHERE NAME ='global_instrumentation';UPDATE performance_schema.setup_instrumentsSET ENABLED = 'YES'WHERE NAME ='wait/lock/metadata/sql/mdl';// 配置文件中添加，重启生效performance-schema-instrument = wait/lock/metadata/sql/mdl=ON 场景 1：杀掉持有 MDL 锁的会话，使 DDL 语句顺利执行。 DDL 语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此，查找 kill 掉事务运行时间大于 DDL 运行时间的会话即可使 DDL 语句顺利下发，SQL 语句如下： // 查找事务运行时间 >= DDL等待时间的线程SELECT trx_mysql_thread_id AS PROCESSLIST_ID, NOW(), TRX_STARTED, TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME , USER, HOST, DB, TRX_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE trx_mysql_thread_id != connection_id() AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= (SELECT MAX(Time) FROM INFORMATION_SCHEMA.processlist WHERE STATE='Waiting for table metadata lock' AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| PROCESSLIST_ID | NOW() | TRX_STARTED | TRX_LAST_TIME | User | Host | DB | TRX_QUERY |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| 253 | 2019-12-24 01:42:11 | 2019-12-24 01:41:24 | 47 | root | localhost | NULL | NULL |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+1 row in set (0.00 sec)// kill掉长事务，释放持有的MDL资源kill 253; 注：因 MySQL 元数据信息记录有限，此处可能误杀无辜长事务，且误杀无法完全避免。 当 kill 掉阻塞源后，可能存在 DDL 语句与被阻塞的 SQL 语句同时加锁的情况，此时会出现事务开始时间等于 DDL 开始时间连接，此类事务也需 kill。 //查找事务开始时间 = DDL语句事务开始时间的线程SELECT trx_mysql_thread_id AS PROCESSLIST_ID, NOW(), TRX_STARTED, TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME , USER, HOST, DB, TRX_QUERYFROM INFORMATION_SCHEMA.INNODB_TRX trxJOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.idWHERE trx_mysql_thread_id != connection_id() AND trx_started = (SELECT MIN(trx_started) FROM INFORMATION_SCHEMA.INNODB_TRX GROUP BY trx_started HAVING count(trx_started)>=2) AND TRX_QUERY NOT LIKE 'alter%table%' OR TRX_QUERY IS NULL;+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| PROCESSLIST_ID | NOW() | TRX_STARTED | TRX_LAST_TIME | User | Host | DB | TRX_QUERY |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+| 255 | 2019-12-24 01:42:44 | 2019-12-24 01:42:33 | 11 | root | localhost | NULL | NULL |+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+1 row in set (0.00 sec)//杀掉阻塞源kill 255; 场景 2：kill 掉下发 DDL 语句的用户连接，取消 DDL 语句下发，保障业务不被阻塞。 // 查找DDL语句所在用户连接SELECT *FROM INFORMATION_SCHEMA.PROCESSLISTWHERE INFO LIKE 'ALTER%TABLE%';+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+| 254 | root | localhost | NULL | Query | 730 | Waiting for table metadata lock | alter table db.t1 add index (id) |+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+1 row in set (0.00 sec)// 杀掉DDL语句所在用户连接kill 254; 四、锁等待 查看锁等待相关的阻塞线程、被阻塞线程信息及相关用户、IP、PORT SELECT locked_table, locked_index, locked_type, blocking_pid, concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)", blocking_lock_mode, blocking_trx_rows_modified, waiting_pid, concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)", waiting_lock_mode, waiting_trx_rows_modified, wait_age_secs, waiting_queryFROM sys.x$innodb_lock_waits T1LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.IDLEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+| locked_table | locked_index | locked_type | blocking_pid | blocking(user@ip:port) | blocking_lock_mode | blocking_trx_rows_modified | waiting_pid | waiting(user@ip:port) | waiting_lock_mode | waiting_trx_rows_modified | wait_age_secs | waiting_query |+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+| `db`.`t1` | PRIMARY | RECORD | 228 | dks@127.0.0.1:56724 | X | 1 | 231 | root@127.0.0.1:50852 | S | 0 | 1 | insert into db.t1(id) values(2) |+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+1 row in set, 3 warnings (0.00 sec) 若不关心阻塞相关的用户、IP、PORT，可直接查看 innodb_lock_waits 表信息。 select * from sys.x$innodb_lock_waits\G*************************** 1. row *************************** wait_started: 2019-12-23 02:14:22 wait_age: 00:00:32 wait_age_secs: 32 locked_table: `db`.`t1` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 7204404 waiting_trx_started: 2019-12-23 02:14:18 waiting_trx_age: 00:00:36 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 213 waiting_query: delete from db.t1 where id=200 waiting_lock_id: 7204404:1994:3:4 waiting_lock_mode: X blocking_trx_id: 7204394 blocking_pid: 207 blocking_query: select * from sys.x$innodb_lock_waits blocking_lock_id: 7204394:1994:3:4 blocking_lock_mode: X blocking_trx_started: 2019-12-23 02:10:06 blocking_trx_age: 00:04:48 blocking_trx_rows_locked: 1 blocking_trx_rows_modified: 1 sql_kill_blocking_query: KILL QUERY 207sql_kill_blocking_connection: KILL 2071 row in set, 3 warnings (0.00 sec) 影响锁等待超时的参数 技术分享 | 一些 MySQL DBA 实用 SQL 语句 五、全局读锁 PERFORMANCE_SCHEMA.METADATA_LOCKS 表 LOCK_DURATION 列为 EXPLICIT 状态表示 FTWRL 语句添加，OBJECT_TYPE 出现 COMMIT 状态表示已经加锁成功 场景 1：杀掉添加 FTWRL 的会话，恢复业务运行 SELECT processlist_id, mdl.OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUSFROM performance_schema.metadata_locks mdlINNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_idAND processlist_id <> connection_id()AND LOCK_DURATION='EXPLICIT';+----------------+-------------+---------------+-------------+-----------+---------------+-------------+| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |+----------------+-------------+---------------+-------------+-----------+---------------+-------------+| 231 | GLOBAL | NULL | NULL | SHARED | EXPLICIT | GRANTED || 231 | COMMIT | NULL | NULL | SHARED | EXPLICIT | GRANTED |+----------------+-------------+---------------+-------------+-----------+---------------+-------------+2 rows in set (0.00 sec)// 杀掉添加FTWRL的用户连接kill 231; 场景 2：杀掉语句执行时间大于 FTWRL 执行时间的线程，确保 FTWRL 下发成功 SELECT T2.THREAD_ID, T1.ID AS PROCESSLIST_ID, T1.User, T1.Host, T1.db, T1.Time, T1.State, T1.Info, T3.TRX_STARTED, TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIMEFROM INFORMATION_SCHEMA.processlist T1LEFT JOIN PERFORMANCE_SCHEMA.THREADS T2 ON T1.ID=T2.PROCESSLIST_IDLEFT JOIN INFORMATION_SCHEMA.INNODB_TRX T3 ON T1.id=T3.trx_mysql_thread_idWHERE T1.TIME >= (SELECT MAX(Time) FROM INFORMATION_SCHEMA.processlist WHERE INFO LIKE 'flush%table%with%read%lock') AND Info IS NOT NULL;+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+| THREAD_ID | PROCESSLIST_ID | User | Host | db | Time | State | Info | TRX_STARTED | TRX_LAST_TIME |+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+| 284 | 246 | root | localhost | NULL | 364 | User sleep | select * from db.t1 where sleep(1000000000) | 2019-12-23 14:57:23 | 364 || 286 | 248 | root | 10.211.55.2:55435 | NULL | 232 | Waiting for table flush | flush table with read lock | NULL | NULL |+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+2 rows in set (0.00 sec) 六、内存使用监控 默认只对 performance_schema 库进行内存统计，对全局内存统计需要手工开启 //动态开启，开启后开始统计update performance_schema.setup_instruments setenabled = 'yes' where name like 'memory%';//配置文件中添加，重启生效performance-schema-instrument='memory/%=COUNTED' 查看实例内存消耗分布，sys 库下有多张 memory 相关视图用于协助用户定位分析内存溢出类问题 SELECT event_name, current_allocFROM sys.memory_global_by_current_bytesWHERE event_name LIKE 'memory%innodb%'; 七、分区表 查看实例中的分区表相关信息 SELECT TABLE_SCHEMA, TABLE_NAME, count(PARTITION_NAME) AS PARTITION_COUNT, sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS, CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH, CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH, CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZEFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND PARTITION_NAME IS NOT NULLGROUP BY TABLE_SCHEMA, TABLE_NAMEORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;+--------------+------------------+-----------------+------------------+-------------+--------------+------------+| TABLE_SCHEMA | TABLE_NAME | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |+--------------+------------------+-----------------+------------------+-------------+--------------+------------+| db | t1 | 365 | 0 | 5.70M | 17.11M | 22.81M || db | t2 | 391 | 0 | 6.11M | 0.00M | 6.11M || db | t3 | 4 | 32556 | 2.28M | 0.69M | 2.97M || db | t4 | 26 | 0 | 0.41M | 2.44M | 2.84M || db | t5 | 4 | 0 | 0.06M | 0.00M | 0.06M || db | t6 | 4 | 0 | 0.06M | 0.00M | 0.06M |+--------------+------------------+-----------------+------------------+-------------+--------------+------------+6 rows in set (1.04 sec) 查看某分区表具体信息，此处以库名为 db、表名为 e 的分区表为例 SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_METHOD, PARTITION_DESCRIPTION, TABLE_ROWS, CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH, CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH, CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZEFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema') AND PARTITION_NAME IS NOT NULL AND TABLE_SCHEMA='db' AND TABLE_NAME='e';+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+| db | e | p0 | id | RANGE | 50 | 4096 | 0.20M | 0.09M | 0.30M || db | e | p1 | id | RANGE | 100 | 6144 | 0.28M | 0.13M | 0.41M || db | e | p2 | id | RANGE | 150 | 6144 | 0.28M | 0.13M | 0.41M || db | e | p3 | id | RANGE | MAXVALUE | 16172 | 1.52M | 0.34M | 1.86M |+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+4 rows in set (0.00 sec) 八、数据库信息概览 统计实例中各数据库大小 SELECT TABLE_SCHEMA, round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB, round(SUM(data_length)/1024/1024,2) AS DATA_MB, round(SUM(index_length)/1024/1024,2) AS INDEX_MB, COUNT(*) AS TABLESFROM INFORMATION_SCHEMA.tablesWHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'INFORMATION_SCHEMA', 'performance_schema')GROUP BY TABLE_SCHEMAORDER BY 2 DESC;+--------------+----------+---------+----------+--------+| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |+--------------+----------+---------+----------+--------+| cloud | 229.84 | 223.02 | 6.83 | 41 || db | 66.42 | 30.56 | 35.86 | 31 || dks | 14.41 | 9.70 | 4.70 | 621 || test | 0.06 | 0.06 | 0.00 | 4 || db2 | 0.03 | 0.03 | 0.00 | 2 |+--------------+----------+---------+----------+--------+5 rows in set, 1 warning (0.91 sec) 统计某库下各表大小 SELECT TABLE_SCHEMA, TABLE_NAME TABLE_NAME, CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length, CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length, CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size, engineFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 'performance_schema', 'sys', 'mysql') AND TABLE_SCHEMA='db'ORDER BY (data_length + index_length) DESC LIMIT 10;+--------------+-----------------------+-------------+--------------+------------+--------+| TABLE_SCHEMA | table_name | data_length | index_length | total_size | engine |+--------------+-----------------------+-------------+--------------+------------+--------+| db | t1 | 5.70M | 22.81M | 28.52M | InnoDB || db | t2 | 15.19M | 9.59M | 24.78M | InnoDB || db | t3 | 6.11M | 0.00M | 6.11M | InnoDB || db | t4 | 2.28M | 0.69M | 2.97M | InnoDB || db | t5 | 0.41M | 2.44M | 2.84M | InnoDB || db | t6 | 0.17M | 0.00M | 0.17M | InnoDB || db | t7 | 0.17M | 0.00M | 0.17M | InnoDB || db | t8 | 0.02M | 0.11M | 0.13M | InnoDB || db | t9 | 0.08M | 0.00M | 0.08M | InnoDB || db | t10 | 0.05M | 0.02M | 0.06M | InnoDB |+--------------+-----------------------+-------------+--------------+------------+--------+10 rows in set, 1 warning (0.01 sec) 查看某库下表的基本信息 SELECT TABLE_SCHEMA, TABLE_NAME, table_collation, engine, table_rowsFROM INFORMATION_SCHEMA.tablesWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 'sys', 'mysql', 'performance_schema') AND TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='db'ORDER BY table_rows DESC ;+--------------+-----------------------+--------------------+--------+------------+| TABLE_SCHEMA | table_name | table_collation | engine | table_rows |+--------------+-----------------------+--------------------+--------+------------+| db | t1 | utf8_general_ci | InnoDB | 159432 || db | t2 | utf8mb4_general_ci | InnoDB | 32556 || db | t3 | utf8mb4_general_ci | InnoDB | 2032 |...| db | t100 | utf8mb4_general_ci | InnoDB | 0 || db | t101 | utf8mb4_general_ci | InnoDB | 0 |+--------------+-----------------------+--------------------+--------+------------+25 rows in set, 1 warning (0.01 sec) 九、长时间未更新的表 UPDATE_TIME 为 NULL 表示实例启动后一直未更新过 SELECT TABLE_SCHEMA, TABLE_NAME, UPDATE_TIMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') AND TABLE_TYPE='BASE TABLE'ORDER BY UPDATE_TIME ;+--------------+-----------------------+---------------------+| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME |+--------------+-----------------------+---------------------+| db | t1 | NULL || db | t2 | NULL || db | t3 | NULL || db | t4 | 2019-12-16 07:45:29 || db | t5 | 2019-12-16 16:52:01 |+--------------+-----------------------+---------------------+22 rows in set, 1 warning (0.01 sec) 十、主键、索引 无主键、唯一键及二级索引基表 MySQL Innodb 存储引擎为索引组织表，因此设置合适的主键字段对性能至关重要 SELECT T1.TABLE_SCHEMA, T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') AND T2.TABLE_TYPE='BASE TABLE' AND T1.TABLE_SCHEMA='db'GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING MAX(COLUMN_KEY)=''; 无主键、唯一键，仅有二级索引表 该类型表因无高效索引，因此从库回放时容易导致复制延迟 SELECT T1.TABLE_SCHEMA, T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') AND T2.TABLE_TYPE='BASE TABLE' AND T1.COLUMN_KEY != ''GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI'; 仅有主键、唯一键表 该类型表结构因无二级索引，可能导致应用 SQL 语句上线后频繁全表扫描出现性能抖动 SELECT T1.TABLE_SCHEMA, T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') AND T2.TABLE_TYPE='BASE TABLE' AND T1.COLUMN_KEY != '' AND T1.TABLE_SCHEMA='db'GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'MUL'; 无主键、唯一键表 SELECT T1.TABLE_SCHEMA, T1.TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAMEWHERE T1.TABLE_SCHEMA NOT IN ('SYS', 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')AND T2.TABLE_TYPE='BASE TABLE'GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI'; 十一、存储引擎 存储引擎分布 SELECT TABLE_SCHEMA, ENGINE, COUNT(*)FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 'PERFORMANCE_SCHEMA', 'SYS', 'MYSQL') AND TABLE_TYPE='BASE TABLE'GROUP BY TABLE_SCHEMA, ENGINE; 非 INNODB 存储引擎表 SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION, ENGINE, TABLE_ROWSFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' , 'SYS', 'MYSQL', 'PERFORMANCE_SCHEMA') AND TABLE_TYPE='BASE TABLE' AND ENGINE NOT IN ('INNODB')ORDER BY TABLE_ROWS DESC ; 十二、实时负载 while truedomysqladmin -uroot -pxxxxxxx extended-status -r -i 1 -c 30 --socket=/mysqldata/mysqld.sock 2>/dev/null|awk -F"|" "BEGIN{ count=0 ;}"'{ if($2 ~ /Variable_name/ && ++count == 1){\ print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\ print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";\}\else if ($2 ~ /Queries/){queries=$3;}\else if ($2 ~ /Com_select /){com_select=$3;}\else if ($2 ~ /Com_insert /){com_insert=$3;}\else if ($2 ~ /Com_update /){com_update=$3;}\else if ($2 ~ /Com_delete /){com_delete=$3;}\else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\else if ($2 ~ /Uptime / && count >= 2){\ printf(" %s |%9d",strftime("%H:%M:%S"),queries);\ printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\ printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\ printf("|%10d %11d\n",innodb_lor,innodb_phr);\}}'done (1)QPS(每秒Query量) QPS = Questions(or Queries) / seconds mysql > show global status like 'Question%'; (2)TPS(每秒事务量) TPS = (Com_commit + Com_rollback) / seconds mysql > show global status like 'Com_commit'; mysql > show global status like 'Com_rollback'; (3)key Buffer 命中率 mysql>show global status like 'key%'; key_buffer_read_hits = (1-key_reads / key_read_requests) * 100% key_buffer_write_hits = (1-key_writes / key_write_requests) * 100% (4)InnoDB Buffer命中率 mysql> show status like 'innodb_buffer_pool_read%'; innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100% (5)Query Cache命中率 mysql> show status like 'Qcache%'; Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%; (6)Table Cache状态量 mysql> show global status like 'open%'; 比较 open_tables 与 opend_tables 值 (7)Thread Cache 命中率 mysql> show global status like 'Thread%'; mysql> show global status like 'Connections'; Thread_cache_hits = (1 - Threads_created / connections ) * 100% (8)锁定状态 mysql> show global status like '%lock%'; Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重 Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的 Innodb_row_lock_current_waits:当前正在等待锁的数量； Innodb_row_lock_time:从系统启动到现在锁定总时间长度； Innodb_row_lock_time_avg：每次等待所花平均时间； Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度； Innodb_row_lock_waits:系统启动到现在总共等待的次数； (9)复制延时量 mysql > show slave status 查看延时时间 (10) Tmp Table 状况(临时表状况) mysql > show status like 'Create_tmp%'; Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大， 可能是排序句子过多或者是连接句子不够优化 (11) Binlog Cache 使用状况 mysql > show status like 'Binlog_cache%'; 如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小 (12) Innodb_log_waits 量 mysql > show status like 'innodb_log_waits'; Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待 1. tps/qps tps: Transactions Per Second，每秒事务数； qps: Queries Per Second每秒查询数； 通常有两种方法计算tps/qps： 方法1：基于 com_commit、com_rollback 计算tps，基于 questions 计算qps。 TPS = Com_commit/s + Com_rollback/s 其中， Com_commit /s= mysqladmin extended-status --relative --sleep=1|grep -w Com_commit Com_rollback/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_rollback QPS 是指MySQL Server 每秒执行的Query总量，通过Questions (客户的查询数目)状态值每秒内的变化量来近似表示，所以有： QPS = mysqladmin extended-status --relative --sleep=1|grep -w Questions 仿照上面的方法还可以得到，mysql每秒select、insert、update、delete的次数等，如： Com_select/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_select Com_select/s：平均每秒select语句执行次数 Com_insert/s：平均每秒insert语句执行次数 Com_update/s：平均每秒update语句执行次数 Com_delete/s：平均每秒delete语句执行次数 方法2: 基于com_%计算tps ,qps tps= Com_insert/s + Com_update/s + Com_delete/s qps=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s 2. 线程状态 threads_running：当前正处于激活状态的线程个数 threads_connected：当前连接的线程的个数 3. 流量状态 Bytes_received/s：平均每秒从所有客户端接收到的字节数，单位KB Bytes_sent/s：平均每秒发送给所有客户端的字节数，单位KB 4. innodb文件读写次数 innodb_data_reads：innodb平均每秒从文件中读取的次数 innodb_data_writes：innodb平均每秒从文件中写入的次数 innodb_data_fsyncs：innodb平均每秒进行fsync()操作的次数 5. innodb读写量 innodb_data_read：innodb平均每秒钟读取的数据量，单位为KB innodb_data_written：innodb平均每秒钟写入的数据量，单位为KB 6. innodb缓冲池状态 innodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数 innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数（逻辑读请求数） innodb_buffer_pool_write_requests: 平均每秒向innodb缓冲池的写次数 innodb_buffer_pool_pages_dirty: 平均每秒innodb缓存池中脏页的数目 innodb_buffer_pool_pages_flushed: 平均每秒innodb缓存池中刷新页请求的数目 innodb缓冲池的读命中率 innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100 Innodb缓冲池的利用率 Innodb_buffer_usage = ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100 7. innodb日志 innodb_os_log_fsyncs: 平均每秒向日志文件完成的fsync()写数量 innodb_os_log_written: 平均每秒写入日志文件的字节数 innodb_log_writes: 平均每秒向日志文件的物理写次数 innodb_log_write_requests: 平均每秒日志写请求数 8. innodb行 innodb_rows_deleted: 平均每秒从innodb表删除的行数 innodb_rows_inserted: 平均每秒从innodb表插入的行数 innodb_rows_read: 平均每秒从innodb表读取的行数 innodb_rows_updated: 平均每秒从innodb表更新的行数 innodb_row_lock_waits: 一行锁定必须等待的时间数 innodb_row_lock_time: 行锁定花费的总时间，单位毫秒 innodb_row_lock_time_avg: 行锁定的平均时间，单位毫秒 9. MyISAM读写次数 key_read_requests: MyISAM平均每秒钟从缓冲池中的读取次数 Key_write_requests: MyISAM平均每秒钟从缓冲池中的写入次数 key_reads : MyISAM平均每秒钟从硬盘上读取的次数 key_writes : MyISAM平均每秒钟从硬盘上写入的次数 10. MyISAM缓冲池 MyISAM平均每秒key buffer利用率 Key_usage_ratio =Key_blocks_used/(Key_blocks_used+Key_blocks_unused)*100 MyISAM平均每秒key buffer读命中率 Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100 MyISAM平均每秒key buffer写命中率 Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100 11. 临时表 Created_tmp_disk_tables: 服务器执行语句时在硬盘上自动创建的临时表的数量 Created_tmp_tables: 服务器执行语句时自动创建的内存中的临时表的数量 Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，可能是排序句子过多或者连接句子不够优化 12. 其他 slow_queries: 执行时间超过long_query_time秒的查询的个数（重要） sort_rows: 已经排序的行数 open_files: 打开的文件的数目 open_tables: 当前打开的表的数量 select_scan: 对第一个表进行完全扫描的联接的数量 此外，还有一些性能指标不能通过mysqladmin extended-status或show global status直接得到，但是十分重要。 13. response time: 响应时间 Percona提供了tcprstat工具统计响应时间，此功能默认是关闭的，可以通过设置参数query_response_time_stats=1打开这个功能。 有两种方法查看响应时间： （1）通过命令SHOW QUERY_RESPONSE_TIME查看响应时间统计； （2）通过INFORMATION_SCHEMA里面的表QUERY_RESPONSE_TIME来查看。 http://www.orczhou.com/index.php/2011/09/thanks-percona-response-time-distribution/comment-page-1/（参考文章） 14. Slave delay: 备库延迟 可以在slave节点上执行show slave status\G命令，Seconds_Behind_Master项的值即为slave当前的延时量，单位秒。 ################ #包含所有前台和后台线程的processlist信息，默认按照进程等待时间和最近一个语句执行完成的时间降序排序。数据来源：performance_schema的threads、events_waits_current、events_statements_current、events_stages_current 、events_transactions_current 、session_connect_attrs表和 sys 系统库的 x$memory_by_thread_by_current_bytess视图 #这些视图列出了进程相关的较为详细的信息，比SHOW PROCESSLIST语句和INFORMATION_SCHEMA PROCESSLIST表更完整，且对该视图的查询是非阻塞的（因为不是从information_schema.processlist表中获取数据的，对processlist表查询是阻塞的） SELECT pps.thread_id AS thd_id, pps.processlist_id AS conn_id, IF(pps.name = 'thread/sql/one_connection', CONCAT(pps.processlist_user, '@', pps.processlist_host), REPLACE(pps.name, 'thread/', '')) user, pps.processlist_db AS db, pps.processlist_command AS command, pps.processlist_state AS state, pps.processlist_time AS time, sys.format_statement(pps.processlist_info) AS current_statement, IF(esc.end_event_id IS NULL, sys.format_time(esc.timer_wait), NULL) AS statement_latency, IF(esc.end_event_id IS NULL, ROUND(100 * (estc.work_completed / estc.work_estimated), 2), NULL) AS progress, sys.format_time(esc.lock_time) AS lock_latency, esc.rows_examined AS rows_examined, esc.rows_sent AS rows_sent, esc.rows_affected AS rows_affected, esc.created_tmp_tables AS tmp_tables, esc.created_tmp_disk_tables AS tmp_disk_tables, IF(esc.no_good_index_used > 0 OR esc.no_index_used > 0, 'YES', 'NO') AS full_scan, IF(esc.end_event_id IS NOT NULL, sys.format_statement(esc.sql_text), NULL) AS last_statement, IF(esc.end_event_id IS NOT NULL, sys.format_time(esc.timer_wait), NULL) AS last_statement_latency, sys.format_bytes(mem.current_allocated) AS current_memory, ewc.event_name AS last_wait, IF(ewc.end_event_id IS NULL AND ewc.event_name IS NOT NULL, 'Still Waiting', sys.format_time(ewc.timer_wait)) last_wait_latency, ewc.source, sys.format_time(etc.timer_wait) AS trx_latency, etc.state AS trx_state, etc.autocommit AS trx_autocommit, conattr_pid.attr_value as pid, conattr_progname.attr_value as program_name FROM performance_schema.threads AS pps LEFT JOIN performance_schema.events_waits_current AS ewc USING (thread_id) LEFT JOIN performance_schema.events_stages_current AS estc USING (thread_id) LEFT JOIN performance_schema.events_statements_current AS esc USING (thread_id) LEFT JOIN performance_schema.events_transactions_current AS etc USING (thread_id) LEFT JOIN sys.x$memory_by_thread_by_current_bytes AS mem USING (thread_id) LEFT JOIN performance_schema.session_connect_attrs AS conattr_pid ON conattr_pid.processlist_id=pps.processlist_id and conattr_pid.attr_name='_pid' LEFT JOIN performance_schema.session_connect_attrs AS conattr_progname ON conattr_progname.processlist_id=pps.processlist_id and conattr_progname.attr_name='program_name' ORDER BY pps.processlist_time DESC, last_wait_latency DESC; 一、关于一个SQL的简单的工作过程 1、工作前提描述 1、启动MySQL，在内存中分配一个大空间innodb_buffer_pool(还有log_buffer) 2、多用户线程连接MySQL，从内存分配用户工作空间(其中排序空间) 3、磁盘上有数据库文件、ib_logfile、tmp目录、undo 2、SQL的简易流程 1、DQL操作 1、首先进行内存读 2、如果buffer pool中没有所需数据，就进行物理读 3、物理读数据读入buffer pool，再返回给用户工作空间 2、DML操作(例update) 1、内存读，然后进行物理读，读取所需修改的数据行 2、从磁盘调入undo页到buffer pool中 3、修改前的数据存入undo页里，产生redo 4、修改数据行(buffer pool中数据页成脏页)，产生redo 5、生成的redo先是存于用户工作空间，择机拷入log_buffer中 6、log线程不断的将log_buffer中的记录写入redo logfile中 7、修改完所有数据行，提交事务，刻意再触发一下log线程 8、待log_buffer中的相关信息都写完，响应事务提交成功 至此，日志写入磁盘，内存脏块还在buffer pool中(后台周期写入磁盘，释放buffer pool空间)。 二、影响SQL执行性能的因素，及具体看方式 1、大量物理读 mysql> show global status like 'i%read%'; | Innodb_buffer_pool_reads | 647 | | Innodb_data_read | 48402944 | | Innodb_data_reads | 2996 | | Innodb_pages_read | 2949 | | Innodb_rows_read | 1002172 | 1、Innodb_buffer_pool_reads：物理读次数 2、Innodb_data_read：物理读数据字节量 3、Innodb_data_reads：物理读IO请求次数 4、Innodb_pages_read：物理读数据页数 5、Innodb_rows_read：物理读数据行数 2、Log写性能 复制代码 mysql> show engine innodb status \G --- LOG --- Log sequence number 144064129 //已经生成的日志量(累计值)/单位：字节 Log flushed up to 144064129 //已经写入的日志量(累计值) Pages flushed up to 144064129 //已经写入的脏页量(累计值) Last checkpoint at 144064120 //检查点 0 pending log flushes, 0 pending chkp writes 92 log i/o's done, 0.00 log i/o's/second 复制代码 关于redo log的写入： 1、Innodb_os_log_written：日志刷盘的字节数，如果在commit不怎么变化的情况下，这个值出现暴增，说明系统出现大事务了（处理：kill线程，必要情况kill掉mysql进程）； 2、Innodb_log_writes：日志写的次数。 3、磁盘排序 复制代码 mysql> show status like 'Sort_merge_passes'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | +-------------------+-------+ 1 row in set (0.00 sec) 复制代码 用户所需数据，如果没有内存buffer pool中，就发生物理读； 如果需要过滤掉很多数据，就会影响物理读和内存读，因为返回很多的数据（物理读），在内存中需要过滤掉很多数据（内存读）； 如果涉及到group/order by，会在用户工作空间完成排序等，如果结果集过大，用户空间过小，进行磁盘排序，Sort_merge_passes>0 ，这就很影响数据库性能了。 三、MySQL线程及其工作 MySQL的工作机制是单进程多线程：IO线程=一个log线程+四个read线程+四个write线程 复制代码 mysql> show engine innodb status \G -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) 复制代码 1、读操作：innodb_read_io_threads 1、发起者：用户线程发起读请求 2、完成者：读线程执行请求队列中的读请求操作 3、如何调整读线程的数量 复制代码 mysql> show variables like 'innodb_read_io_threads'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_read_io_threads | 4 | +------------------------+-------+ 1 row in set (0.01 sec) 复制代码 默认是开启4个读线程，静态参数，修改至配置文件中 4、如何确定是否需要增加读线程的数量 查看线程的状态：I/O thread 2 state: waiting for i/o request (read thread) 2、写操作：innodb_write_io_threads 1、发起者：page_cleaner线程发起 2、完成者：写线程执行请求队列中的写请求操作 3、如何调整写线程的数量 复制代码 mysql> show variables like 'innodb_write_io_threads'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_write_io_threads | 4 | +-------------------------+-------+ 1 row in set (0.01 sec) 复制代码 默认是开启4个写线程，静态参数，修改至配置文件中 4、如何确定是否需要增加写线程的数量 查看线程的状态：I/O thread 6 state: waiting for i/o request (write thread) 关于innodb_purge_threads：page cleaner 线程 作用： 1、负责对 undo 数据页的清空 2、数据页中 delete 标志行的清除 3、清理 innodb buffer pool，负责把内存中的脏页发起写请求，write 线程负载把脏页刷新到磁盘上。 3、日志线程 3.1、只有一个日志线程 1、是否繁忙 I/O thread 1 state: waiting for i/o request (log thread)：闲 2、日志写性能 复制代码 mysql> show global status like 'Innodb_log_waits'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Innodb_log_waits | 0 | +------------------+-------+ 1 row in set (0.00 sec) 复制代码 如果log buffer太小，就很容易满，导致无法写入，产生日志等待。 3、日志写压力 1、每秒吞吐量 复制代码 mysql> show global status like 'Innodb_os_log_written'; #redo log写的字节数 +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Innodb_os_log_written | 57856 | +-----------------------+-------+ 1 row in set (0.01 sec) 复制代码 2、每秒写入次数 复制代码 mysql> show global status like 'Innodb_log_writes'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Innodb_log_writes | 59 | +-------------------+-------+ 1 row in set (0.01 sec) 3.2、对于日志监控来说，三个经典参数 1、Innodb_log_waits #redo写入的等待次数 2、Innodb_log_writes #redo写入的次数 3、Innodb_os_log_written #写入redo logfile中的字节量 3.3、日志写入异常判断 mysql> show engine innodb status \G Pending flushes (fsync) log: 0; mysql> show global status like 'Innodb_os_log_pending_fsyncs'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | Innodb_os_log_pending_fsyncs | 0 | +------------------------------+-------+ 1 row in set (0.00 sec) 1、fsync：绕过文件系统缓存，直接将内存中的数据写入存储中，实现数据真正写入可靠的介质磁盘里。(对于redo log来说，通过fsync方式写入磁盘才是可靠的保证，因为写入文件系统缓存的提交成功响应并不是真正的将redo写入磁盘的logfile中)(sync:同步) 2、pending：挂起(写不动)，redo写入存储cache过程中，某种原因io繁忙，cache被占满，超时响应，就会被挂起； 3、>0就说明系统IO出现问题，=0说明is OK。 四、log buffer调整依据 log buffer：日志缓存，一般都很小，调整100M足够使用 mysql> show variables like "%log_buffer%"; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | innodb_log_buffer_size | 16777216 | +------------------------+----------+ 5.7默认是16M，5.6默认是8M 1、文件中(log file)：每次写的时候全局都写，不会挑着捡着写 1、日志写线程每一秒redo日志缓冲刷新到重做日志文件 2、每个事务提交时会将重做日志缓冲刷新到重做日志文件 3、每当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。 2、在企业中往往设置50-100M，最多设置为2、300M，调整依据： 1、内存空间足够大 2、日志产生量大，系统io阻塞了，系统的io占用的是一个带宽，log_writes线程被阻塞，log buffer满了，数据库会hang住。 3、Innodb_log_waits（状态值） The number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
- 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
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
- 700
- 701
- 702
- 703
- 704
- 705
- 706
- 707
- 708
- 709
- 710
- 711
- 712
- 713
- 714
- 715
- 716
- 717
- 718
- 719
- 720
- 721
- 722
- 723
- 724
- 725
- 726
- 727
- 728
- 729
- 730
- 731
- 732
- 733
- 734
- 735
- 736
- 737
- 738
- 739
- 740
- 741
- 742
- 743
- 744
- 745
- 746
- 747
- 748
- 749
- 750
- 751
- 752
- 753
- 754
- 755
- 756
- 757
- 758
- 759
- 760
- 761
- 762
- 763
- 764
- 765
- 766
- 767
- 768
- 769
- 770
- 771
- 772
- 773
- 774
- 775
- 776
- 777
- 778
- 779
- 780
- 781
- 782
- 783
- 784
- 785
- 786
- 787
- 788
- 789
- 790
- 791
- 792
- 793
- 794
- 795
- 796
- 797
- 798
- 799
- 800
- 801
- 802
- 803
- 804
- 805
- 806
- 807
- 808
- 809
- 810
- 811
- 812
- 813
- 814
- 815
- 816
- 817
- 818
- 819
- 820
- 821
- 822
- 823
- 824
- 825
- 826
- 827
- 828
- 829
- 830
- 831
- 832
- 833
- 834
- 835
- 836
- 837
- 838
- 839
- 840
- 841
- 842
- 843
- 844
- 845
- 846
- 847
- 848
- 849
- 850
- 851
- 852
- 853
- 854
- 855
- 856
- 857
- 858
- 859
- 860
- 861
- 862
- 863
- 864
- 865
- 866
- 867
- 868
- 869
- 870
- 871
- 872
- 873
- 874
- 875
- 876
- 877
- 878
- 879
- 880
- 881
- 882
- 883
- 884
- 885
- 886
- 887
- 888
- 889
- 890
- 891
- 892
- 893
- 894
- 895
- 896
- 897
- 898
- 899
- 900
- 901
- 902
- 903
- 904
- 905
- 906
- 907
- 908
- 909
- 910
- 911
- 912
- 913
- 914
- 915
- 916
- 917
- 918
- 919
- 920
- 921
- 922
- 923
- 924
- 925
- 926
- 927
- 928
- 929
- 930
- 931
- 932
- 933
- 934
- 935
- 936
- 937
- 938
- 939
- 940
- 941
- 942
- 943
- 944
- 945
- 946
- 947
- 948
- 949
- 950
- 951
- 952
- 953
- 954
- 955
- 956
- 957
- 958
- 959
- 960
- 961
- 962
- 963
- 964
- 965
- 966
- 967
- 968
- 969
- 970
- 971
- 972
- 973
- 974
- 975
- 976
- 977
- 978
- 979
- 980
- 981
- 982
- 983
- 984
- 985
- 986
- 987
- 988
- 989
- 990
- 991
- 992
- 993
- 994
- 995
- 996
- 997
- 998
- 999
- 1000
- 1001
- 1002
- 1003
- 1004
- 1005
- 1006
- 1007
- 1008
- 1009
- 1010
- 1011
- 1012
- 1013
- 1014
- 1015
- 1016
- 1017
- 1018
- 1019
- 1020
- 1021
- 1022
- 1023
- 1024
- 1025
- 1026
- 1027
- 1028
- 1029
- 1030
- 1031
- 1032
- 1033
- 1034
- 1035
- 1036
- 1037
- 1038
- 1039
- 1040
- 1041
- 1042
- 1043
- 1044
- 1045
- 1046
- 1047
- 1048
- 1049
- 1050
- 1051
- 1052
- 1053
- 1054
- 1055
- 1056
- 1057
- 1058
- 1059
- 1060
- 1061
- 1062
- 1063
- 1064
- 1065
- 1066
- 1067
- 1068
- 1069
- 1070
- 1071
- 1072
- 1073
- 1074
- 1075
- 1076
- 1077
- 1078
- 1079
- 1080
- 1081
- 1082
- 1083
- 1084
- 1085
- 1086
- 1087
- 1088
- 1089
- 1090
- 1091
- 1092
- 1093
- 1094
- 1095
- 1096
- 1097
- 1098
- 1099
- 1100
- 1101
- 1102
- 1103
- 1104
- 1105
- 1106
- 1107
- 1108
- 1109
- 1110
- 1111
- 1112
- 1113
- 1114
- 1115
- 1116
- 1117
- 1118
- 1119
- 1120
- 1121
- 1122
- 1123
- 1124
- 1125
- 1126
- 1127
- 1128
- 1129
- 1130
- 1131
- 1132
- 1133
- 1134
- 1135
- 1136
- 1137
- 1138
- 1139
- 1140
- 1141
- 1142
- 1143
- 1144
- 1145
- 1146
- 1147
- 1148
- 1149
- 1150
- 1151
- 1152
- 1153
- 1154
- 1155
- 1156
- 1157
- 1158
- 1159
- 1160
- 1161
- 1162
- 1163
- 1164
- 1165
- 1166
- 1167
- 1168
- 1169
- 1170
- 1171
- 1172
- 1173
- 1174
- 1175
- 1176
- 1177
- 1178
- 1179
- 1180
- 1181
- 1182
- 1183
- 1184
- 1185
- 1186
- 1187
- 1188
- 1189
- 1190
- 1191
- 1192
- 1193
- 1194
- 1195
- 1196
- 1197
- 1198
- 1199
- 1200
- 1201
- 1202
- 1203
- 1204
- 1205
- 1206
- 1207
- 1208
- 1209
- 1210
- 1211
- 1212
- 1213
- 1214
- 1215
- 1216
- 1217
- 1218
- 1219
- 1220
- 1221
- 1222
- 1223
- 1224
- 1225
- 1226
- 1227
- 1228
- 1229
- 1230
- 1231
- 1232
- 1233
- 1234
- 1235
- 1236
- 1237
- 1238
- 1239
- 1240
- 1241
- 1242
- 1243
- 1244
- 1245
- 1246
- 1247
- 1248
- 1249
- 1250
- 1251
- 1252
- 1253
- 1254
- 1255
- 1256
- 1257
- 1258
- 1259
- 1260
- 1261
- 1262
- 1263
- 1264
- 1265
- 1266
- 1267
- 1268
- 1269
- 1270
- 1271
- 1272
- 1273
- 1274
- 1275
- 1276
- 1277
- 1278
- 1279
- 1280
- 1281
- 1282
- 1283
- 1284
- 1285
- 1286
- 1287
- 1288
- 1289
- 1290
- 1291
- 1292
- 1293
- 1294
- 1295
- 1296
- 1297
- 1298
- 1299
- 1300
- 1301
- 1302
- 1303
- 1304
- 1305
- 1306
- 1307
- 1308
- 1309
- 1310
- 1311
- 1312
- 1313
- 1314
- 1315
- 1316
- 1317
- 1318
- 1319
- 1320
- 1321
- 1322
- 1323
- 1324
- 1325
- 1326
- 1327
- 1328
- 1329
- 1330
- 1331
- 1332
- 1333
- 1334
- 1335
- 1336
- 1337
- 1338
- 1339
- 1340
- 1341
- 1342
- 1343
- 1344
- 1345
- 1346
- 1347
- 1348
- 1349
- 1350
- 1351
- 1352
- 1353
- 1354
- 1355
- 1356
- 1357
- 1358
- 1359
- 1360
- 1361
- 1362
- 1363
- 1364
- 1365
- 1366
- 1367
- 1368
- 1369
- 1370
- 1371
- 1372
- 1373
- 1374
- 1375
- 1376
- 1377
- 1378
- 1379
- 1380
- 1381
- 1382
- 1383
- 1384
- 1385
- 1386
- 1387
- 1388
- 1389
- 1390
- 1391
- 1392
- 1393
- 1394
- 1395
- 1396
- 1397
- 1398
- 1399
- 1400
- 1401
- 1402
- 1403
- 1404
- 1405
- 1406
- 1407
- 1408
- 1409
- 1410
- 1411
- 1412
- 1413
- 1414
- 1415
- 1416
- 1417
- 1418
- 1419
- 1420
- 1421
- 1422
- 1423
- 1424
- 1425
- 1426
- 1427
- 1428
- 1429
- 1430
- 1431
- 1432
- 1433
- 1434
- 1435
- 1436
- 1437
- 1438
- 1439
- 1440
- 1441
- 1442
- 1443
- 1444
- 1445
- 1446
- 1447
- 1448
- 1449
- 1450
- 1451
- 1452
- 1453
- 1454
- 1455
- 1456
- 1457
- 1458
- 1459
- 1460
- 1461
- 1462
- 1463
- 1464
- 1465
- 1466
- 1467
- 1468
- 1469
- 1470
- 1471
- 1472
- 1473
- 1474
- 1475
- 1476
- 1477
- 1478
- 1479
- 1480
- 1481
- 1482
- 1483
- 1484
- 1485
- 1486
- 1487
- 1488
- 1489
- 1490
- 1491
- 1492
- 1493
- 1494
- 1495
- 1496
- 1497
- 1498
- 1499
- 1500
- 1501
- 1502
- 1503
- 1504
- 1505
- 1506
- 1507
- 1508
- 1509
- 1510
- 1511
- 1512
- 1513
- 1514
- 1515
- 1516
- 1517
- 1518
- 1519
- 1520
- 1521
- 1522
- 1523
- 1524
- 1525
- 1526
- 1527
- 1528
- 1529
- 1530
- 1531
- 1532
- 1533
- 1534
- 1535
- 1536
- 1537
- 1538
- 1539
- 1540
- 1541
- 1542
- 1543
- 1544
- 1545
- 1546
- 1547
- 1548
- 1549
- 1550
- 1551
- 1552
- 1553
- 1554
- 1555
- 1556
- 1557
- 1558
- 1559
- 1560
- 1561
- 1562
- 1563
- 1564
- 1565
- 1566
- 1567
- 1568
- 1569
- 1570
- 1571
- 1572
- 1573
- 1574
- 1575
- 1576
- 1577
- 1578
- 1579
- 1580
- 1581
- 1582
- 1583
- 1584
- 1585
- 1586
- 1587
- 1588
- 1589
- 1590
- 1591
- 1592
- 1593
- 1594
- 1595
- 1596
- 1597
- 1598
- 1599
- 1600
- 1601
- 1602
- 1603
- 1604
- 1605
- 1606
- 1607
- 1608
- 1609
- 1610
- 1611
- 1612
- 1613
- 1614
- 1615
- 1616
- 1617
- 1618
- 1619
- 1620
- 1621
- 1622
- 1623
- 1624
- 1625
- 1626
- 1627
- 1628
- 1629
- 1630
- 1631
- 1632
- 1633
- 1634
- 1635
- 1636
- 1637
- 1638
- 1639
- 1640
- 1641
- 1642
- 1643
- 1644
- 1645
- 1646
- 1647
- 1648
- 1649
- 1650
- 1651
- 1652
- 1653
- 1654
- 1655
- 1656
- 1657
- 1658
- 1659
- 1660
- 1661
- 1662
- 1663
- 1664
- 1665
- 1666
- 1667
- 1668
- 1669
- 1670
- 1671
- 1672
- 1673
- 1674
- 1675
- 1676
- 1677
- 1678
- 1679
- 1680
- 1681
- 1682
- 1683
- 1684
- 1685
- 1686
- 1687
- 1688
- 1689
- 1690
- 1691
- 1692
- 1693
- 1694
- 1695
- 1696
- 1697
- 1698
- 1699
- 1700
- 1701
- 1702
- 1703
- 1704
- 1705
- 1706
- 1707
- 1708
- 1709
- 1710
- 1711
- 1712
- 1713
- 1714
- 1715
- 1716
- 1717
- 1718
- 1719
- 1720
- 1721
- 1722
- 1723
- 1724
- 1725
- 1726
- 1727
- 1728
- 1729
- 1730
- 1731
- 1732
- 1733
- 1734
- 1735
- 1736
- 1737
- 1738
- 1739
- 1740
- 1741
- 1742
- 1743
- 1744
- 1745
- 1746
- 1747
- 1748
- 1749
- 1750
- 1751
- 1752
- 1753
- 1754
- 1755
- 1756
- 1757
- 1758
- 1759
- 1760
- 1761
- 1762
- 1763
- 1764
- 1765
- 1766
- 1767
- 1768
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/羊村懒王/article/detail/122083
推荐阅读
相关标签
