MySQL的一些命令

    记录一些MySQL的命令，包括一些查看和设置参数之类的命令。注意，这里使用table_name代表的表名。例如表名是test，则将'table_name'替换成'test'，将table_name替换成test。

1、设置隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

1、范围：

• session：会话级别，该设置只在当前会话中生效；
• global：全局，修改整个库的配置。

2、级别：

• READ UNCOMMITTED：读未提交，可以读到其他事务没有提及的数据，可能产生脏读、不可重复读、幻读；
• READ COMMITTED：读已提交，也叫不可重复读。只会读其他事务提交过的数据，但是如果事务处理过程中，其他事务修改并提交了本事务相关的数据，会出现前后两次查询查到不同数据的情况，即不可重复读。可能产生不可重复读、幻读；
• REPEATABLE READ：可重复读，MySQL的默认级别，不允许其他事务修改当前事务正在使用的行，所以不会出现不可重复读的问题，但是可能产生幻读。但是InnoDB通过MVCC（多版本并发控制）避免了幻读的问题；
• SERIALIZABLE：串行化，强制要求全部事务串行执行，但是会导致非常严重的锁争用问题，并发性能极差。

2、查看表状态

SHOW TABLE STATUS LIKE 'table_name'

示例：show table status like 'vendor'

结果及字段说明：

字段名	字段值	说明
Name	vendor	表名
Engine	InnoDB	存储引擎
Version	10	版本
Row_format	Dynamic	行的格式
Rows	781	表中的行数
Avg_row_length	608	平均每行的字节数
Data_length	475136	表的数据大小，字节数
Max_data_length	0	表数据的最大容量，和存储引擎有关
Index_length	0	索引的大小，字节数
Data_free	4194304	已分配但未使用的行
Auto_increment	4642	下一个auto_increment的值
Create_time	31/10/2019 17:54:58	表创建时间
Update_time	21/11/2019 18:53:15	表数据最新的更新时间
Check_time		使用check table最后一次检查的时间
Collation	utf8_general_ci	表的默认字符集和字符列排序规则
Checksum		如果启用，会保整个表的实时校验和
Create_options		创建表时指定的其他选项
Comment	供应商基础信息表	备注信息

3、转换表的引擎

    转换表的存储引擎的有2种常见方法：

1、直接修改：

 ALTER TABLE table_name ENGINE = InnoDB;

2、导出数据，创建新表，导入数据：

创建新表时指定引擎：

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

4、查看创建表的语句

SHOW CREATE TABLE table_name

5、慢查询日志

1、开启慢查询日志：

SET PROFILING = 1;

2、查询全部日志：

SHOW PROFILES;

    执行结果：

3、根据变化查询特定查询语句的详情：

SHOW PROFILE FOR QUERY 5;

    结果及部分说明：

Status	Duration	字段说明
starting	0.000142
checking permissions	0.000084	检查权限；遍历数据库下所有的frm文件，并获取相关信息
Opening tables	0.000103	尝试打开一个表。这个过程会很快，除非受到干扰，比如实际线程数远高于表支持的并发数
init	0.000101
System lock	0.000094	一个线程想请求或者正在等待一个表的内部或者外部的系统锁
optimizing	0.000085
statistics	0.000087
preparing	0.000093
Creating tmp table	0.000106	创建临时表
Sorting result	0.000083
executing	0.000255
Sending data	0.000141	从这个临时表，把数据发送给用户
Creating sort index	0.000134
end	0.000084
query end	0.000083
removing tmp table	0.000087
query end	0.000089
closing tables	0.000081
removing tmp table	0.000082
closing tables	0.000081
freeing items	0.000095
cleaning up	0.000094

    备注：可能还有些这个查询不会涉及到的操作没有展示出来，比如 Coping to tmp table。

4、查询语句在其他方面的开销

SHOW PROFILE ALL FOR QUERY 5;
SHOW PROFILE CPU FOR QUERY 5;
SHOW PROFILE BLOCK IO FOR QUERY 5;

    ALL指的是显示全部信息，这些信息包括：

• BLOCK IO                           显示块IO相关开销 
• CONTEXT SWITCHES      上下文切换相关开销 
• CPU                                    显示CPU相关开销信息 
• IPC                                      显示发送和接收相关开销信息 
• MEMORY                            显示内存相关开销信息 
• PAGE FAULTS                    显示页面错误相关开销信息 
• SOURCE                            显示和Source_function，Source_file，Source_line相关的开销信息 
• SWAPS                               显示交换次数相关开销的信息 

6、查询计数器

1、查询当前连接的会话级别的计数器：

SHOW STATUS;

2、查询服务器级别的从服务启动开始计时的计数器：

SHOW GLOBAL STATUS;

3、查询特定的计数器：

SHOW STATUS WHERE Variable_name LIKE 'Com%';
SHOW STATUS LIKE 'Com%';

    两种写法都可以。

    show status大部分结果都是计数器，可以统计一些操作（例如读索引）的频繁程度，但是无法提供具体的时间。

4、常用的会话级操作：

SHOW  STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%';

    结果：

Variable_name	Value
Created_tmp_disk_tables	0
Created_tmp_files	25500
Created_tmp_tables	0
Handler_commit	0
Handler_delete	0
Handler_discover	0
Handler_external_lock	0
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	0

7、字符集和校对规则

1、查看字符集

show characterset;

2、查看校对规则

show collation;

3、设置字符集

CREATE TABLE my_table(id INT, name char(3)) DEFAULT CHARSET=utf-8;