《MySQL技术内幕-InnoDB存储引擎》第2版 系统相关SQL语句

纯手打，已验证SQL。
数字前缀为章节,如1.5:第一章第5节。

系统变量

show variables like "datadir";
1.1 数据库所在路径
show variables like "socket";
1.5 unix域socket文件路径
show engines;
1.4 查看当前MySQL支持的存储引擎

show variables like "innodb_version";
show variables like "innodb_%io_threads";
2.3 查看innodb版本 和 查看IO线程数。 
show engine innodb status \G
2.3 查看innodb引擎最近一段时间的状态信息
select version();
2.3 查看MySQL版本
show variables like "innodb_purge_threads";
2.3 查看undo页回收的线程数
show variables like 'innodb_log_buffer_size';
2.3 查看日志缓冲区大小(Byte)
show variables like 'innodb_lru_scan_depth';
2.4 查看LRU列表中可用页的数量
show variables like 'innodb_max_dirty_pages_pct';
2.4 查看强制刷新缓冲池脏页到磁盘的百分比
show variables like 'innodb_purge_batch_size';
2.5 查看控制每次full purge回收的Undo页数量
show variables like 'innodb_change_buffer_max_size';
2.6 查看Change Buffer最大使用内存数，25表示1/4缓冲池内存空间，最大值为50。
show global status like 'innodb_dblwr%';
2.6 查看doublewrite的总共写入 和 实际写入，比例64:1为压力正常。
show variables like 'innodb_adaptive_hash_index';
2.6 查看AHI(自适应哈希索引)是否开启，默认开启。
show variables like 'innodb_use_native_aio';
2.6 查看Native AIO是否开启，默认开启。
show variables like 'innodb_flush_neighbors';
2.6 查看刷新邻接页是否开启(固态硬盘本身超高IOPS建议关闭此特性)
show variables like 'innodb_fast_shutdown';
2.7 查看数据库关闭时脏页刷回到硬盘模式(0:全部刷回，默认为1:刷回缓冲池的，2:不刷回，写日志启动时进行recovery)
show variables like 'innodb_force_recovery';
2.7 查看InnoDB重启恢复模式(默认0进行所有恢复 3不进行事务回滚)

show variables like 'log_error';
3.2 查看错误日志文件位置
show variables like 'long_query_time';
3.2 查看慢查日志记录的时间阈值，小于该秒则记录。(秒)
show variables like 'log_queries_not_using_indexes';
3.2 查看没有使用索引的慢查，默认关闭是因为slow log会不断增加。
show variables like 'log_output';
3.2 查看慢查输出格式，默认'FILE'，可修改为'TABLE'通过MyISAM引擎的表存慢查。
show variables like 'binlog_cache_size';
3.2  事务未提交时存入binlog文件的缓冲大小(session)，超过则写入临时文件。
show variables like 'binlog_format';
3.2  查看binlog记录格式(STATEMENT指记录逻辑SQL语句、ROW指记录表更改情况、MIXED指混合使用)
show variables like 'pid_file';
3.4  查看MySQL实例进程ID位置
show variables like 'innodb_file_per_table';
3.6  每个用户是否单独的.idb独立表空间文件。
show variables like 'innodb_log_file_size';
3.6  每个重做日志的大小。
show variables like 'innodb_log_files_in_group';
3.6  日志文件组中 重做日志文件的数量。
show variables like 'innodb_mirrored_log_groups';
3.6  日志镜像文件组 的数量。
show variables like 'innodb_log_group_home_dir';
3.6  日志文件组 的所在路径,默认"./"为数据库目录下。

show variables like 'sql_mode'; 
4.6 数据库模式，配合约束使用(strict_trans_tables 严格模式、NO_ENGINE_SUBSTITUTION 宽松模式 等等)
show variables like 'foreign_key_checks'; 
4.6 是否忽视外键的即时检查机制。
show plugins;
4.7 查看插件(partition是否启用分区表)
show index from MY_TABLE;

5.4 观察MY_TABLE表上的所有索引(Cardinality唯一数目估值，小于1则可删除)
analyze table MY_TABLE
5.4 更新MY_TABLE上的Cardinality值。
show variables like 'optimizer_switch'; 
5.6 查询系统标记(mrr=on,mrr_cost_based=off则开启MRR优化)
show variables like 'read_rnd_buffer_size'; 
5.6 查看控制键值的缓冲区大小，大于该值执行器对已缓存的数据根据RowID排序来取得行数据。
show variables like 'innodb_ft_cache_size'; 
5.8 查看全文检索索引缓存(FTS Index Cache)的大小，超过则同步到辅助表(Auxiliary Table)中。
show variables like 'innodb_ft_num_word_optimize'; 
5.8 限制optimize table每次实际删除FTS中的分词数量。
show variables like 'innodb_ft_min_token_size';
show variables like 'innodb_ft_max_token_size';
5.8 全文索引word的字符长度的区间 min～max。

show engine innodb mutex;
6.2 查看latch闩锁的信息(os_waits操作系统等待次数)。
show variables like 'innodb_autoinc_lock_mode';
6.3 查看自增长模式(0传统模式，1混合mutex和传统模式，2mutex模式)
show variables like 'innodb_lock_wait_timeout';
6.6 查看锁兼容带来的事务阻塞时间(秒)。
show variables like 'innodb_rollback_on_timeout';
6.6 查看阻塞超时后是否进行事务回滚。

show variables like 'innodb_flush_log_at_trx_commit';
7.2 查看重做日志刷回磁盘策略(0不做fsnc操作 1事务提交调用一次fsync操作 2仅写入内存)
show variables like 'innodb_undo_directory';
7.2 undo的rollback segment文件所在路径("."表示当前InnoDB存储引擎目录)
show variables like 'innodb_undo_logs';
7.2 设置的rollback segment个数
show variables like 'innodb_undo_tablespaces';
7.2 构成rollback segment文件的数量
show variables like 'innodb_max_purge_lag_delay';
7.2 控制delay的最大毫秒数
show global status like 'com_commit';
show global status like 'com_rollback';
7.5 统计显式的commit/rollback数
show global status like 'handler_commit';
show global status like 'handler_rollback';
7.5 粗略统计隐式的commit/rollback数
select @@global.tx_isolation;
7.6 查看全局会话的隔离级别。
select @@tx_isolation;
7.6 查看当前会话的隔离级别。
show full processlist;

8.7 查看所有线程的状态。
show master status;
show slave status;
8.7 查看主从服务器的当前延迟等信息。

show global status like 'innodb%read%';
Innodb_buffer_pool_read 从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead 预读的次数
Innodb_buffer_pool_read_ahead_evicted 预读的页，一般用来判断预读效率。
Innodb_buffer_pool_read_requests 从缓冲池中读取页的次数
Innodb_data_read 总共读如的字节数
Innodb_data_reads 发起读取请求的次数，每次读取可能需要读取多个页
9.2 当前服务器的状态参数

系统表

select host,user,password from mysql.user;
1.5 连接到MySQL实例时检查的权限表
select value from information_schema.innodb_ft_default_stopword;
5.8 查看全文索引中的 stopword 列表(无意义无需分词的列表)。
select * from information_schema.INNODB_TRX \G
6.2 当前事务信息。
select * from information_schema.INNODB_LOCKS \G
6.2 当前锁的信息
select * from information_schema.INNODB_LOCK_WAITS \G
6.2 查看当前事务的等待

缓冲池相关

show variables like 'innodb_buffer_pool_size'; 
2.3 查看缓冲池大小(Byte)
show variables like 'innodb_buffer_pool_instances';
2.3 查看缓冲池实例数
select pool_id,pool_size,free_buffers,database_pages from information_schema.innodb_buffer_pool_stats;   
2.3 查看缓冲池实例使用状态
select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.INNODB_BUFFER_POOL_STATS\G
2.3 缓冲池运行状态

LRU列表管理相关

show variables like 'innodb_old_blocks_pct'; 
2.3 新页插入midpoint的位置百分比(插到LRU列表尾端37%这个位置)
show variables like 'innodb_old_blocks_time';
2.3 页读到midpoint位置后等多久加入到LRU热端的时间(1000毫秒后加入)
set global innodb_old_blocks_time=0;
2.3 页读到midpoint位置后无需等待，立刻加入LRU热端。
set global innodb_old_blocks_pct=20;
2.3 设置midpoint中心位指向20，即冷热(新旧)数据占比8/2。
select table_name,space,page_number,page_type from information_schema.innodb_buffer_page_lru where space = 1; 
2.3 观察每个LRU列表中每个页信息 
select table_name,space,page_number,compressed_size from information_schema.innodb_buffer_page_lru where compressed_size <> 0;	
2.3 查看unzip_LRU列表中的页
select table_name,space,page_number,page_type from information_schema.innodb_buffer_page_lru where oldest_modification > 0;
2.3  查看LRU列表中存在的脏页