MySQL常用命令

MySQL

视图相关

创建视图

mysql> create view v_t as select * from t where id<10;

视图定义中的 WITH CHECK OPTION就是针对于可更新的视图的,即更新的值是否需要检查

mysql> alter view v_t as select * from t where id<10 with check option;

SHOW TABLES命令把表和视图都显示出来

mysql> show tables;

若用户只想查看当前架构下的基表,可以通过 information_schema架构下的 TABLE表来查询,并搜索表类型为BASE TABLE的表

mysql> select * from information_schema.TABLES where table_type='BASE TABLE' and table_schema=database();

要想查看视图的一些元数据(meta data),可以访问 information_schema架构下的VIEWS表,该表给出了视图的详细信息,包括视图定义者(definer)，定义内容,是否是可更新视图、字符集等

mysql> select * from information_schema.VIEWS where table_schema=database()\G

分区表相关

用SHOW PLUGINS;查看mysql是否支持表分区，查看 partition 是否处于 active 状态，MySQL8中SHOW PLUGINS命令和INFORMATION_SCHEMA.PLUGINS表中不再显示与分区相关的变量信息

SHOW PLUGINS;

不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分,唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列

mysql> create table t1(
    -> col1 int not null,
    -> col2 date not null,
    -> col3 int not null,
    -> col4 int not null,
    -> unique key (col1,col2,col3,col4)
    -> ) partition by hash(col3) partitions 4;

通过查询 information_schema架构下的 PARTITIONS表来查看每个分区的具体信息

mysql> select * from information_schema.PARTITIONS where table_schema=database() and table_name='t'\G

删除分区

mysql> alter table sales drop partition p2008;

通过 EXPLAIN PARTITION命令查看选中分区

mysql> explain partitions select * from sales where date>='2008-01-01' and date<='2009-01-01'\G

配置文件相关

在关闭时,参数 innodb_fast_shutdown影响着表的存储引擎为 InnoDB的行为。该参数可取值为0、1、2,默认值为1。0表示在 MySQL数据库关闭时, InnoDB需要完成所有的 full purge和 merge insert buffer,并且将所有的脏页刷新回磁盘。这需要一些时间,有时甚至需要几个小时来完成。如果在进行 InnoDB升级时,必须将这个参数调为0,然后再关闭数据库。1是参数 innodb_fast_shutdown的默认值,表示不需要完成上述的 full purge和merge insert buffer操作,但是在缓冲池中的一些数据脏页还是会刷新回磁盘。2表示不完成 full purge和 merge insert buffer操作,也不将缓冲池中的数据脏页写回磁盘,而是将日志都写入日志文件。这样不会有任何事务的丢失,但是下次MySQL数据库启动时,会进行恢复操作(recovery)。

mysql> show variables like 'innodb_fast_shutdown';

参数 innodb_force_recovery影响了整个 InnoDB存储引擎恢复的状况。该参数值默认为0,代表当发生需要恢复时,进行所有的恢复操作,当不能进行有效恢复时,如数据页发生了 corruption, MySQL数据库可能发生宕机( crash),并把错误写入错误日志中去。参数 innodb_force_recovery还可以设置为6个非零值:1~6。大的数字表示包含了前面所有小数字表示的影响。具体情况如下:
1( SRV_FORCE_IGNORE_CORRUPT):忽略检查到的 corrupt页。
2( SRV_FORCE_NO_BACKGROUND):阻止 Master Thread线程的运行,如 Master Thread线程需要进行 full purge操作,而这会导致 crash
3( SRV_FORCE_NO_TRX_UNDO):不进行事务的回滚操作。
4( SRV_FORCE_NO_IBUF_MERGE):不进行插入缓冲的合并操作。
5( SRV_FORCE_NO_UNDO_LOG_SCAN):不查看撤销日志( Undo log), InnoDB存储引擎会将未提交的事务视为已提交。
6( SRV_FORCE_NO_LOG_REDO):不进行前滚的操作。

mysql> show variables like 'innodb_force_recovery';

用以下命令可以査看当MySQL数据库实例启动时，会在哪些位置査找配置文件，如果几个配置文件中都有同一个参数，MySQL数据库会以读取到的最后一个配置文件中的参数为准。

mysql --help | grep my.cnf

在UNIX系统下本地连接 MySQL可以采用UNIX域套接字方式,这种方式需要一个套接字(socket)文件

mysql> SHOW VARIABLES LIKE 'socket';

当 MySQL实例启动时,会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件可由参数pid_fle控制,默认位于数据库目录下,文件名为主机名.pid

mysql> show variables like 'pid_file';

配置文件中有一个参数datadir该参数指定了数据库所在的路径。在Linux操作系统下默认datadir为/usr/local/mysql/data,用户可以修改该参数。

mysql> show variables like 'datadir';

innodb表的数据是否独立存储，OFF表示表的数据放在系统共享表空间，也就是跟数据字典放在一起，ON表示每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。MySQL 5.6.6版本开始，默认值ON

-- 察看当前设置
mysql> show global variables like 'innodb_file_per_table';  
-- 关闭innodb_file_per_table
mysql> set @@global.innodb_file_per_table=off;

InnoDB采用将存储的数据按表空间(tablespace)进行存放的设计。在默认配置下会有一个初始大小为10MB,名为 ibdata1的文件。该文件就是默认的表空间文件(tablespace file),用户可以通过参数 innodb_data_file_path对其进行设置

mysql> show variables like 'innodb_data_file_path';

查看当前主机名称

mysql> show variables like 'hostname';

查看临时目录

mysql> show variables like 'tmpdir';

数据库服务所在位置

mysql> show variables like 'basedir';

查看MySQL在使用innodb引擎的时候页大小，默认是16384 （16K）

mysql> SHOW GLOBAL STATUS like 'Innodb_page_size';

数据页的checksum值的计算方法依赖参数innodb_checksum_algorithm,可设置的值有: innodb、crc32、none、 strict_innodb、 strict_crc32、 strict_none，crc32为 MySQL5.6.6版本引进的新的 checksum算法,该算法较之前的 innodb有着较高的性能。但是若表中所有页的 checksum值都以 strict算法保存,那么低版本的 MySQL数据库将不能读取这些页。none表示不对页启用 checksum检查strict*，正如其名,表示严格地按照设置的 checksum算法进行页的检测。因此若低版本 MySQL数据库升级到 MySQL5.6.6或之后的版本,启用 strict_crc32将导致不能读取表中的页。启用 strict_crc32方式是最快的方式,因为其不再对 innodb和crc32算法进行两次检测。故推荐使用该设置。若数据库从低版本升级而来,则需要进行 mysql_upgrade操作.

mysql> show variables like 'innodb_checksum_algorithm';

连接相关

MySQL的最大连接数

-- 最大连接数
mysql> show variables like 'max_connections';
-- 响应的连接数
mysql>show  status like 'max_used_connections';

事务相关

查看当前事务隔离级别

mysql> show variables like 'transaction_isolation';

提交事务并自动启动下一个事务

mysql> commit work and chain

completion_type,该参数默认为0,表示没有任何操作。在这种设置下 COMMIT和 COMMIT WORK是完全等价的，值为1时, COMMIT WORK等同于 COMMIT AND CHAIN，为2时,COMMIT WORK等同于 COMMIT AND RELEASE。在事务提交后会自动断开与服务器的连接

mysql> show variables like 'completion_type';

查找持续时间超过60s的事务

mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

设置事务隔离级别

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

启动事务确保这个语句执行完就可以得到一个一致性视图

-- 等价begin with consistent snapshot
mysql> START TRANSACTION WITH CONSISTENT SNAPSHOT;

com_commit执行commit的次数，不会记录隐式提交的事务，handle_commit包含了内部的commit的次数

mysql> show global status like 'com_commit';

com_rollback执行rollback的次数，不会记录隐式回滚的事务，handler_rollback包含了内部的commit的次数，计算TPS的方法是(com_commit+com_rollback)/time

mysql> show global status like 'com_rollback';

通过参数 innodb_support_xa可以查看是否启用了XA事务，在MySQL 5.7.10版本不启用，因为它使主备复制不安全和降低了与二进制日志group commit相关的性能。在MySQL 8.0中这个选项被移除掉了

mysql> show global variables like 'innodb_support_xa';

查询最大执行时间

查看select语句的最大执行时间，单位是毫秒

mysql> show variables like 'max_execution_time';

设置select语句的最大执行时间，session级别

mysql> set session max_execution_time=10000;

设置select语句的最大执行时间，全局级别

mysql> set max_execution_time=3000;

设置select语句的最大执行时间，单条语句

mysql> select /*+ max_execution_time(3000)*/ count(*) from t where id>267;

索引相关

重建索引

-- 等价alter table t engine=innodb,ALGORITHM=inplace; Online 方式
-- alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁
mysql> alter table T engine=InnoDB;
-- 强制拷贝表，期间不可更新表
mysql> alter table t engine=innodb,ALGORITHM=copy;

ALGORITHM指定了创建或删除索引的算法，INPLACE表示索引创建或删除操作不需要创建临时表。 DEFAULT表示根据参数 old_alter_table来判断是通过 INPLACE还是COPY的算法,该参数的默认值为OFF,表示采用 INPLACE的方式

mysql> show variables like 'old_alter_table';

强制使用索引

mysql> select * from t force index(a) where a between 10000 and 20000;

查看表索引信息

mysql> show index from t;

给表字段加索引

-- 前缀索引
mysql> alter table t add index idx_email(email(6));
-- 这个过程是inplace的，但会阻塞增删改操作，是非Online的
mysql> alter table t add FULLTEXT(field_name);

innodb_online_alter_log_max_size这个参数是mysql 5.6.6引入的，默认值134217728（128MB），因为在online ddl过程中需要保持delete、update、insert这些数据，所以需要一个日志去保持，这个参数就是限制这个日志的最大大小，当ddl过程中需要的这个日志的大小比这个限制还大的时候就会报错。

mysql> show variables like 'innodb_online_alter_log_max_size';

InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引（AHI）

mysql> show variables like 'innodb_adaptive_hash_index';

当表中有多个非空唯一索引时, InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是,主键的选择根据的是定义索引的顺序,而不是建表时列的顺序，如何查看哪个为主键

mysql> select a,b,c,d,_rowid from t;

重新统计索引信息，影响explain命令看到的rows和索引信息的Cardinality值

mysql> analyze table t;

innodb_stats_method用来判断如何对待索引中出现的NULL值记录。该参数默认值为 nulls_equal,表示将NULL值记录视为相等的记录。其有效值还有 nulls_unequal, nulls_ignored,分别表示将NULL值记录视为不同的记录和忽略NULL值记录。

mysql> show variables like 'innodb_stats_method';

是否将命令 ANALYZE TABLE计算得到的 Cardinality值存放到磁盘上。若是,则这样做的好处是可以减少重新计算每个索引的 Cardinality值,例如当 MySQL数据库重启时。此外,用户也可以通过命令 CREATE TABLE和 ALTER TABLE的选项 STATS PERSISTENT来对每张表进行控制。默认值:OFF，MySQL8为ON。

mysql> show variables like 'innodb_stats_persistent';

当通过命令 SHOW TABLE STATUS、 SHOW INDEX及访问INFORMATION_SCHEMA架构下的表 TABLES和 STATISTICS时,是否需要重新计算索引的 Cardinality值。默认值:OFF

mysql> show variables like 'innodb_stats_on_metadata';

若参数 innodb_stats_persistent设置为ON,该参数表示 ANALYZE TABLE更新 Cardinality值时每次采样页的数量。默认值:20

mysql> show variables like 'innodb_stats_persistent_sample_pages';

该参数用来取代之前版本的参数 innodb_stats_sample_pages,表示每次采样页的数量。默认值为:8

mysql> show variables like 'innodb_stats_transient_sample_pages';

约束相关

约束的创建可以采用以下两种方式:1表建立时就进行约束定义、2利用 ALTER TABLE命令来进行创建约束、3对 Unique Key(唯一索引)的约束,用户还可以通过命令 CREATE UNIQUE INDEX来建立。对于主键约束而言,其默认约束名为PRIMARY。而对于 Unique Key约束而言,默认约束名和列名一样,当然也可以人为指定 Unique Key约束的名字。innoDB存储引擎在外键建立时会自动地对该列加一个索引。

mysql> alter table u add unique keyuk_id_card (id_card);

查找约束

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='mytest' and table_name='u';

对于 Foreign Key的约束的命名,用户还可以通过查看表 REFERENTIAL_CONSTRAINTS,并且可以详细地了解外键的属性

mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='mytest'\G

对错误数据的约束
MySQL的sql_mode模式

mysql> show variables like 'sql_mode';

ENUM和SET约束

mysql> create table a( id int, sex enum('male','female'));

触发器与约束

mysql> create table usercash_err_log(
    -> userid int not null,
    -> old_cash int unsigned not null,
    -> new_cash int unsigned not null,
    -> user varchar(30),
    -> time datetime);
Query OK, 0 rows affected (0.02 sec)
 
mysql> delimiter $$
mysql> create trigger tgr_usercash_update before update on usercash
    -> for each row
    -> begin
    -> if new.cash-old.cash > 0 then
    -> insert into usercash_err_log
    -> select old.userid,old.cash,new.cash,user(),now();
    -> set new.cash = old.cash;
    -> end if;
    -> end;
    -> $$
Query OK, 0 rows affected (0.05 sec)
 
mysql> delimiter ;

外键约束，一般来说,称被引用的表为父表,引用的表称为子表。外键定义时的 ON DELETE和 ON UPDATE表示在对父表进行DELETE和UPDATE操作时,对子表所做的操作,可定义的子表操作有:CASCADE表示当父表发生 DELETE或 UPDATE操作时,对相应的子表中的数据也进行 DELETE或 UPDATE操作。 SET NULL表示当父表发生 DELETE或 UPDATE操作时,相应的子表中的数据被更新为NULL值,但是子表中相对应的列必须允许为NULL值。 NO ACTION表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。 RESTRICT表示当父表发生 DELETE或 UPDATE操作时,抛出错误,不允许这类操作发生。如果定义外键时没有指定 ON DELETE或 ON UPDATE,RESTRICT就是默认的外键设置。

CREATE TABLE  `filedb`.`tblfile` (
  `FileID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FileOwner` varchar(50) DEFAULT NULL COMMENT '外键，引用用户表'，
  PRIMARY KEY (`FileID`),
  CONSTRAINT `FK_tblfile_1` FOREIGN KEY (`FileOwner`) REFERENCES `tbluser` (`UserID`) ON DELETE SET NULL ON UPDATE CASCADE 
)

对于参照完整性约束,外键能起到一个非常好的作用。但是对于数据的导入操作时,外键往往导致在外键约束的检查上花费大量时间。因为 MySQL数据库的外键是即时检查的,所以对导入的每一行都会进行外键检查。但是用户可以在导入过程中忽视外键的检查

mysql> SET foreign key checks =0;
mysql> LOAD DATA ...
mysql> SET foreign key checks =1;

锁相关

加全局读锁FTWRL，数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句会被阻塞

mysql> flush tables with read lock;

表锁lock tables … read/write，用unlock tables主动释放锁

-- 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，
-- 也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
mysql> lock tables t1 read, t2 write; 

alter table语句里面设定等待时间（MariaDB）

mysql> ALTER TABLE tbl_name NOWAIT add column …
mysql> ALTER TABLE tbl_name WAIT N add column …

查看死锁检测开关，默认on

mysql> show variables like 'innodb_deadlock_detect';

查看锁最大等待时间，默认50s

mysql> show variables like 'innodb_lock_wait_timeout';

select语句如果加锁，也是当前读

-- 读锁（S锁，共享锁）
mysql> select k from t where id=1 lock in share mode;
-- 写锁（X锁，排他锁）
mysql> select k from t where id=1 for update;

查看表上已存在的锁

mysql> select * from t sys.innodb_lock_waits where locked_table='test'.'t'\G

MySQL自增锁模式innodb_autoinc_lock_mode参数，0：traditonal （每次都会产生表锁）1：consecutive（mysql的默认模式，会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入）2：interleaved （不会锁表，来一个处理一个，并发最高）

mysql> show variables like 'innodb_autoinc_lock_mode';

显示InnoDB mutex和 rw-lock统计信息

mysql> SHOW ENGINE INNODB MUTEX;

用户可以通过将参数 innodb_locks_unsafe_for_binlog设置为1来显式地关闭Gap Lock，MySQL8中已移除，READ COMMITTED可以提供相似的功能，而且可以在会话和全局级别使用该特性(支持动态修改)，这些优点都是innodb_locks_unsafe_for_binlog所不具备的。

mysql> show variables like 'innodb_locks_unsafe_for_binlog';

读写相关

将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样，需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

mysql> select SQL_CACHE * from T where ID=10；

查看数据库读写状态

mysql> show global variables like 'read_only';

设置库为只读模式

mysql> set global readonly=true;

重写查询语句

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();
-- 测试一条查询语句，使用show warnings 验证

show engine innodb status 到底展示了多少信息，background thread、semaphores、Latest detected Deadlock、transactions、file i/o、insert buffer and adaptive hash index、log、buffer pool and memory、individual buffer pool info、Row operations

mysql> show engine innodb status;

SHOW ENGINES 语句查看当前使用的 MySQL 数据库所支持的存储引擎

mysql> show engines;

安全更新模式set [global] SQL_SAFE_UPDATES = 1,在update操作中：当where条件中列（column）没有索引可用且无limit限制时会拒绝更新。where条件为常量且无limit限制时会拒绝更新。在delete操作中： 当①where条件为常量，②或where条件为空，③或where条件中 列（column）没有索引可用且无limit限制时拒绝删除。

mysql> set sql_safe_updates=on;

要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置

-- 前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

tmp_table_size控制内存临时表大小，默认是16M。

mysql> show variables like 'tmp_table_size';
mysql> set global tmp_table_size=16*1024*1024;

防止对group by字段排序，在MySQL8中GROUP BY子句中的ASC或DESC被移除，想要排序需要配合ORDER BY使用

mysql> select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

group by 优化方法，group by的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的id%100的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的就可以防止临时表。在MySQL 5.7版本支持了generated column机制，用来实现列数据的关联更新。

mysql> alter table t1 add column z int generated always as(id % 100), add index(z);
mysql> select z, count(*) as c from t1 group by z;

在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

mysql> select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

innodb_rows_read: 平均每秒从innodb表读取的行数

mysql> show status like 'innodb_rows_read';

将一个表复制成另一个表

mysql > CREATE TABLE mytest Engine = MyISAM AS SELECT * FROM salaries;

通过命令 SHOW TABLE STATUS LIKE’ table_name’来查看当前表使用的行格式

mysql> SHOW TABLE STATUS like 'table_name'\G;

错误日志

错误日志文件对 MySQL的启动、运行、关闭过程进行了记录。通过命令SHOW VARIABLES LIKE 'log_error’来定位该文件

mysql> SHOW VARIABLES LIKE 'log_error';

查询日志

查询日志记录了所有对 MySQL数据库请求的信息,无论这些请求是否得到了正确的执行。默认文件名为:主机名.log

-- 开关
mysql> show variables like 'general_log';
-- 文件位置
mysql> show variables like 'general_log_file';

慢查询相关

慢查询日志的阈值，0表示这个线程接下来的语句都会被记录入慢查询日志中，默认值为10,代表10秒。

mysql> set long_query_time=0;

慢查询日志开关，默认为ON

mysql> SHOW VARIABLES LIKE 'slow_query_log';

慢查询日志位置

mysql> SHOW VARIABLES LIKE 'slow_query_log_file';

log_queries_not_using_indexes,如果运行的SQL语句没有使用索引,则 MySQL数据库同样会将这条SQL语句记录到慢查询日志文件，默认OFF

mysql> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

参数 log_throttle_queries_not_using_indexes,用来表示每分钟允许记录到 slow log的且未使用索引的SQL语句次数。该值默认为0,表示没有限制。

mysql> SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';

得到执行时间最长的10条SQL语句,可以运行如下命令

mysqldumpslow -s al -n l0 david.log

参数 log_output指定了慢查询输出的格式,默认为FILE,可以将它设为 TABLE,然后就可以查询 mysql架构下的slow_log表了

mysql> show variables like 'log_output';

binlog相关

查看到binlog日志开关状态，默认ON

mysql> show variables like 'log_bin';

参数 max_binlog_size指定了单个二进制日志文件的最大值,如果超过该值,则产生新的二进制日志文件,后缀名+1,并记录到 index文件(同目录下有个同名.index文件)，默认值为1073741824,代表1G

mysql> show variables like 'max_binlog_size';

参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。默认值：32768 – 即 32K

mysql> show global status like 'binlog_cache_size';

binlog_format参数十分重要,它影响了记录二进制日志的格式,该参数可设的值有 STATEMENT、ROW和 MIXED,默认ROW

mysql> show variables like 'binlog_format';

列出服务器上的binlog日志文件

mysql> SHOW BINARY LOGS;

查看主库当前binlog情况

mysql> show master status;

查看某个binlog日志内容show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

mysql> show binlog events in 'master.000001';

mysqlbinlog工具，用下面这个命令解析和查看binlog中的内容

#显示这个事务的binlog是从8900这个位置开始的，所以可以用start-position参数来指定从这个位置的日志开始解析
mysqlbinlog  -vv data/master.000001 --start-position=8900;

使用mysqlbinlog 恢复数据

#将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -uuser -ppwd;

write 和fsync的时机，是由参数sync_binlog控制的，sync_binlog=0的时候，表示每次提交事务都只write，不fsync；sync_binlog=1的时候，表示每次提交事务都会执行fsync；sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

mysql> show variables like 'sync_binlog';

binlog_group_commit_sync_delay 参数解释：等待多少时间后才进行组提交

mysql> show global status like 'binlog_group_commit_sync_delay';

binlog_group_commit_sync_no_delay_count表示等待延迟提交的最大事务数，如果上面参数的时间没到，但事务数到了，则直接同步到磁盘。

mysql> show global status like 'binlog_group_commit_sync_no_delay_count';

参数 binlog_max_flush_queue_time用来控制 Flush阶段中等待的时间,即使之前的一组事务完成提交,当前一组的事务也不马上进入Sync阶段,而是至少需要等待一段寸间。这样做的好处是 group commit的事务数量更多,然而这也可能会导致事务的响应时间变慢。该参数的默认值为0,且推荐设置依然为0

mysql> show variables like 'binlog_max_flush_queue_time';

redolog相关

在默认情况下,在 InnoDB存储引擎的数据目录下会有两个名为 ib_logfile0和ib_logfile1的文件,参数 innodb_log_file_size指定每个重做日志文件的大小

mysql> show variables like 'innodb_log_file_size';

每个 InnoDB存储引擎至少有1个重做日志文件组( group),每个文件组下至少有2个重做日志文件，在日志组中每个重做日志文件的大小一致,并以循环写入的方式运行，innodb_log_files_in_group指定了日志文件组中重做日志文件的数量,默认为2。

mysql> show variables like 'innodb_log_files_in_group';

为了得到更高的可靠性,用户可以设置多个的镜像日志组(mirrored log groups),将不同的文件组放在不同的磁盘上,以此提高重做日志的高可用性，参数innodb _mirrored_log_groups指定了日志镜像文件组的数量,默认为1,表示只有一个日志文件组,没有镜像。MySQL8已移除

mysql> show variables like 'innodb_mirrored_log_groups';

参数 innodb_log_ group_home_dir指定了日志文件组所在路径,默认为./,表示在 MySQL数据库的数据目录下

mysql> show variables like 'innodb_log_group_home_dir';

为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数，它有三种可能取值：设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。

mysql> show variables like 'innodb_flush_log_at_trx_commit';

undolog相关

InnoDB支持最大rollback segment的个数，默认128，每个回滚段种记录了1024个undo log segment,而在每个 undo log segment段中进行undo页的申请

mysql> show variables like 'innodb_rollback_segments';

参数innodb_undo_directory用于设置rollback segment文件所在的路径，该参数的默认值为“./”,表示当前 InnoDB存储引擎的目录

mysql> show variables like 'innodb_undo_directory';

参数innodb_undo_tablespaces用来设置构成 rollback segment文件的数量,默认2，这样 rollback segment可以较为平均地分布在多个文件中

mysql> show variables like 'innodb_undo_tablespaces';

参数 innodb_purge_batch_size用来设置每次 purge操作需要清理的undo page数量，默认300

mysql> show variables like 'innodb_purge_batch_size';

参数 innodb_max_purge_lag用来控制 history list的长度,若长度大于该参数时,其会“延缓”DML的操作。该参数默认值为0,表示不对history list做任何限制

mysql> show variables like 'innodb_max_purge_lag';

参数 innodb_max_purge_lag_delay,其用来控制delay的最大毫秒数，避免由于purge操作缓慢导致其他SQL线程出现无限制的等待

mysql> show variables like 'innodb_max_purge_lag_delay';

数据备份

mysqldump方法

使用mysqldump命令将数据导出成一组INSERT语句。

mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

这条命令中，主要参数含义如下：

• –single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
• –add-locks设置为0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
• –no-create-info的意思是，不需要导出表结构；
• –set-gtid-purged=off表示的是，不输出跟GTID相关的信息；
• –result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。

如果你希望生成的文件中一条INSERT语句只插入一行数据的话，可以在执行mysqldump命令时，加上参数–skip-extended-insert。

通过下面这条命令，将这些INSERT语句放到db2库里去执行。

mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

导出CSV文件

直接将结果导出成.csv文件。MySQL提供了下面的语法，用来将查询结果导出到服务端本地目录

mysql> select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

得到.csv导出文件后，你就可以用下面的load data命令将数据导入到目标表db2.t中。

mysql> load data infile '/server_tmp/t.csv' into table db2.t;

授权相关

创建用户

-- 用户名ua在任意主机，用户名(user)+地址(host)才表示一个用户，密码pa
mysql> create user 'ua'@'%' identified by 'pa';

查看用户在用户表中状态

-- 用户ua在user表中的状态
mysql> select * from mysql.user where user='ua'\G

给用户ua赋一个最高权限

mysql> grant all privileges on *.* to 'ua'@'%' with grant option;

回收上面的grant语句赋予的权限，你可以使用下面这条命令：

mysql> revoke all privileges on *.* from 'ua'@'%';

让用户ua拥有库db1的所有权限，可以执行下面这条命令

mysql> grant all privileges on db1.* to 'ua'@'%' with grant option;

查看用户ua在db表中的状态

mysql> select * from mysql.db where user='ua'\G

授权列权限

mysql> GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

flush privileges命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。

mysql> flush privileges;

合并创建用户加授权，如果用户’ua’@’%'不存在，就创建这个用户，密码是pa；如果用户ua已经存在，就将密码修改成pa。MySQL8无法再用GRANT命令来创建用户了，只能通过CREATE USER命令

mysql> grant super on *.* to 'ua'@'%' identified by 'pa';

主备相关

在备库上执行show slave status命令，它的返回结果里面会显示seconds_behind_master

mysql> show slave status;

参数slave_parallel_workers并行消费relay log线程数

mysql> show variables like 'slave_parallel_workers';

MySQL5.7通过参数–slave-parallel-type=type进行控制并行复制的方式，可选值有DATABASE（默认）和LOGICAL_CLOCK

mysql> show variables like 'slave_parallel_type';

sql_slave_skip_counter主从复制跳过一个事务

mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

设置slave_skip_errors参数，直接设置跳过指定的错误

mysql> show variables like 'slave_skip_errors;

等主库位点,在从库执行select master_pos_wait(file, pos[, timeout])参数file和pos指的是主库上的文件名和位置；timeout可选，设置为正整数N表示这个函数最多等待N秒。这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。如果执行期间，备库同步线程发生异常，则返回NULL；如果等待超过N秒，就返回-1；如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0。

mysql> SELECT MASTER_POS_WAIT('mysql-bin.000001', 51635123,60);

等待GTID,WAIT_FOR_EXECUTED_GTID_SET(GTID_SET [, TIMEOUT])，直到这个库执行的事务中包含传入的gtid_set，返回0；超时返回1。

mysql> select WAIT_FOR_EXECUTED_GTID_SET(‘46293b73-58db-11e4-8b9c-38eaa78f3a78:1-6′,64);

MySQL在执行事务后，返回包中带上GTID,将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可

mysql> show variables like 'session_track_gtids';

查看GTID的状态

mysql> show master status\G;

复制过滤CHANGE REPLICATION FILTER

-- 让从库只同步指定的表
mysql> change replication filter replicate_do_table = (tbl_name) ;

通过 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟

-- 延迟一小时
mysql> CHANGE MASTER TO MASTER_DELAY = 3600

线程相关

show processlist 是显示用户正在运行的线程，如果有 SUPER 权限，则可以看到全部的线程，否则，只能看到自己发起的线程，只列出前100条，如果想全列出请使用show full processlist;

-- 前100条
mysql> show processlist;
-- 全部线程
mysql> show full processlist;

按客户端 IP 分组，看哪个客户端的链接数最多

mysql> select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

mysql> select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

mysql> select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

设置innodb_thread_concurrency参数的目的是，控制InnoDB的并发线程上限。也就是说，一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程退出。默认值是0，表示不限制并发线程数量。thread_concurrency变量是针对于Solaris 8及低版本的系统，这个参数在MySQL 5.6.1中已经被标记为过时，在5.7.2版本的MySQL中被移除。

mysql> set global innodb_thread_concurrency=3;

使用 innodb_read_io_threads 和innodb_write_io_threads参数进行调整IO线程，默认都是4个

mysql> show variables like 'innodb_%io_threads';

Purge Thread 用来回收已经使用并分配的 undo 页

mysql> show variables like 'innodb_purge_threads';

参数 innodb_use_native_aio用来控制是否启用 Native AIO,在 Linux操作系统下,默认值为ON

mysql> show variables like 'innodb_use_native_aio';

内存相关

Multi-Range Read优化可以通过参数 optimizer_switch中的标记(flag)来控制。当mrr为on时,表示启用 Multi-Range Read优化。 mrr_cost_based标记表示是否通过 cost based的方式来选择是否启用mrr。若将mrr设为on, mrr_cost_based设为off,则总是启用 Multi-Range Read优化

mysql> show variables like 'optimizer_switch';

JOIN BUFFER 是 MySQL 用来缓存JOIN KEY 有索引，二级索引和JOIN KEY 无索引这两类 JOIN 检索的一个 BUFFER 内存区域块。

mysql> set session join_buffer_size = 1024 * 1024 * 1024;

read_rnd_buffer_size这个参数用在sort查询之后 ，以保证获取以顺序的方式获取到查询的数据。如果你有很多order by 查询语句，增长这值能够提升性能。因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这，就是MRR优化的设计思路。此时，语句的执行流程变成了这样：1、根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;2、将read_rnd_buffer中的id进行递增排序；3、
排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

mysql> SET GLOBAL read_rnd_buffer_size = 1024*1024;

随机预读是一种技术，它根据缓冲池中已有的页面来预测何时可能需要使用页面，而不管这些页面的读取顺序如何。如果在缓冲池中找到相同范围的13个连续页面，则 InnoDB异步发出请求以预取该范围的其余页面，Innodb_buffer_pool_read_ahead_rnd：随机预读的次数

mysql> show global status like 'Innodb_buffer_pool_read_ahead_rnd';

Innodb_buffer_pool_read_ahead：预读的次数

mysql> show global status like 'Innodb_buffer_pool_read_ahead';

Innodb_buffer_pool_read_ahead_evicted 预读的页数，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率

mysql> show global status like 'Innodb_buffer_pool_read_ahead_evicted';

Innodb_buffer_pool_read_requests 从缓冲池中读取页的次数

mysql> show global status like 'Innodb_buffer_pool_read_requests';

Innodb_buffer_pool_reads 表示从物理磁盘读取的页数

mysql> show global status like 'Innodb_buffer_pool_reads';

Innodb_data_pending_reads innodb当前等待的读的次数

mysql> show global status like 'Innodb_data_pending_reads';

Innodb_data_read 总共读入的字节数

mysql> show global status like 'Innodb_data_read';

Innodb_data_reads innodb完成的读的次数

mysql> show global status like 'Innodb_data_reads';

Innodb_pages_read 物理读数据页数

mysql> show global status like 'Innodb_pages_read';

Innodb_rows_read 物理读数据行数

mysql> show global status like 'Innodb_rows_read';

可以通过以下公式计算InnoDB缓存池的命中率，通常 InnoDB存储引擎的缓冲池的命中率不应该小于99%，如果命中率太低，则应考虑扩充内存，增加innodb_buffer_pool_size的值

(1-innodb_buffer_pool_reads/inodb_buffer_pool_read_request)*100

缓冲池的配置通过参数 innodb_buffer_pool_size 来设置

mysql> show variables like 'innodb_buffer_pool_size';

允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竟争，增加数据库的并发处理能力。可以通过参数 innodb_buffer_pool_instances来进行配置，该值默认为1

mysql> show variables like 'innodb_buffer_pool_instances';

通过 information_schema 架构下的表innodb_buffer_pool_stats来观察缓冲的状态

mysql> select pool_id,pool_size, free_buffers,database_pages from innodb_buffer_pool_stats\G

innodb_old_blocks_pct参数是控制进入到sublist of old blocks区域的数量，初始化默认是37

mysql> show variables like 'innodb_old_blocks_pct';

innodb_old_blocks_time ，用于表示页读取到 mid 位置后需要等待多久才会被加入到 LRU 列表的热端，默认值1000

mysql> set global innodb_old_blocks_time=1000;

通过表 INNODB_BUFFER_POOL_STATS 来观察缓冲池的运行状态,

mysql> select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats;

通过表 INNODB_BUFFER_PAGE_LRU 来观察每个 LRU 列表中每个页

mysql> SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM information_schema.INNODB_BUFFER_PAGE_LRU WHERE SPACE = 1;

通过 information_ schema 架构下的表 INNODB_BUFFER_PAGE_LRU 来观察unzip_LRU 列表中的页

mysql> select table_name,space,page_number,compressed_size from innodb_buffer_page_lru where compressed_size <>0;

重做日志缓冲可由配置参数 innodb_log_buffer_size 控制，默认为8MB

mysql> show variables like 'innodb_log_buffer_size';

通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024

mysql> show variables like 'innodb_lru_scan_depth';

innodb_purge_batch_size参数用来设置每次purge操作需要清理的undo log page的数量。默认300

mysql> show VARIABLES like 'innodb_purge_batch_size';
mysql> set global innodb_purge_batch_size=50;

innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大占比，默认25，最大50

mysql> show variables like 'innodb_change_buffer_max_size';

脏页相关

查看脏页比例

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’;
mysql> select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’;
mysql> select @a/@b;

可以通过元数据表 INNODB_BUFFER_PAGE_LRU 来查看脏页，TABLE_NAME 为 NULL表示该页属于系统表空间。

mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where oldest_modification>0;

动态调整刷新脏页的数量，innodb_io_capacity参数默认是200

mysql> SET GLOBAL innodb_io_capacity = 2000;

innodb_flush_neighbors 参数是InnoDB用来控制buffer pool刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，设置为0时，表示刷脏页时不刷其附近的脏页。设置为1时，表示刷脏页时连带其附近毗连的脏页一起刷掉。设置为2时，表示刷脏页时连带其附近区域的脏页一起刷掉。1与2的区别是2刷的区域更大一些。默认值：5.7版本为1， 8.0版本为0

mysql> show variables like 'innodb_flush_neighbors';

innodb_max_dirty_pages_pct 是 MySQL InnoDB 存储引擎非常重要的一个参数，用来控制buffer pool中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB会启动刷脏页的操作，默认值75

mysql> SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';

执行系统命令

在MySQL环境下执行bash命令只需要在前面加一个system即可，如：

mysql> system sudo ls -lh /usr/local/mysql/data

如何确定一个排序语句是否使用了临时文件

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出,number_of_tmp_files表示的是，排序过程中使用的临时文件数 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值,表示整个执行过程扫描行数*/
select @b-@a;

两个语句做连接查询，关联字段类型不同不能利用索引解决办法

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

主备健康检测

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();