Mysql 8.3.0中Innodb相关配置记录

运行时查看Innodb配置

-- 查看运行时内存分配情况
SELECT SUBSTRING_INDEX(event_name, '/', 2) AS code_area,
FORMAT_BYTES(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name, '/', 2)
ORDER BY SUM(current_alloc) DESC;

-- Innodb统计信息涉及两张表
SHOW TABLES FROM mysql LIKE 'innodb%';

-- 显示表的统计信息，可以查看主键索引占用页数大小和二级索引总占用页数大小
SELECT * FROM mysql.innodb_table_stats;
-- 查看索引统计信息，详细信息看官方文档
SELECT * FROM mysql.innodb_index_stats；

-- 统计索引占用大小
SELECT SUM(stat_value) pages, index_name,
       SUM(stat_value)*@@innodb_page_size size
       FROM mysql.innodb_index_stats WHERE table_name='test'-- 指定表
       AND stat_name = 'size' GROUP BY index_name;

-- 通过sys性能表查看Innodb Buffer Pool内存占用统计
SELECT *
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory/innodb/buf_buf_pool';

运行时Innodb事务和锁信息

--查看Innodb当前每一个事务，包含事务的状态
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查看当前持有锁的事务
select * from performance_schema.data_locks;
--查看当前等待锁的事务
select * from performance_schema.data_lock_waits;

-- 查看持有锁的事务之间的关系
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       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

-- 简化版
SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

Innodb Buffer Pool配置(关于页缓存的简单说明)

Buffer Pool是一个内存缓存，默认为128MB，使用LRU算法淘汰数据。Buffer Pool中最小单元为'页'(page)，定位到一条数据那也只能定位到那条数据所在的页。如果Buffer Pool过小，存储页少，会频繁触发IO读取硬盘中的页数据，Buffer Pool尽量设置大点，Mysql专用机器一般为总内存的50%~80%，运行了其他软件适当调整。
Buffer Pool可以直接通过变量innodb_buffer_pool_size设置，该变量由innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances共同组成。

引用资料：

1. 《Mysql是如何运行的：从根上理解Mysql》
2. 大数据量Count过慢，设置二级索引来减少页缓存，缩短查询时间
3. 官方文档Innodb统计信息表
4. 官方文档Innodb事务与锁以及案例分析