Innodb优化

use sys;
show status like '%Handler_read%';
select object_type, object_schema, object_name, index_name, count_star, count_read, COUNT_FETCH
from performance_schema.table_io_waits_summary_by_index_usage
where object_name = '{table_name}';

select * from sys.schema_unused_indexes where  object_name = '{table_name}';

show global status like '%Handler_read%';
select *
from performance_schema.table_io_waits_summary_by_index_usage
where object_name = '{table_name}';

## 查看性能较差的索引
SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage` where OBJECT_SCHEMA = '{db_name}' ORDER BY MAX_TIMER_WAIT DESC;

## 查询未使用过的索引
SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage` where OBJECT_SCHEMA = '{table_name}' and COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0;
SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage` where object_name = '{table_name}' and COUNT_STAR = 0 AND SUM_TIMER_WAIT = 0;

## 查询表是否存在不适用索引的情况
SELECT * FROM `performance_schema`.`table_io_waits_summary_by_index_usage` WHERE OBJECT_NAME = '{table_name}' AND INDEX_NAME IS NULL;
## 整体查询表的IO量
select * from sys.io_global_by_file_by_bytes;

use mysql;
show global variables like 'innodb_page_size';

## 查询所有线程
select name, type,thread_id , processlist_id from performance_schema.threads;

## 查询前台线程
select name, type,thread_id , processlist_id from performance_schema.threads where TYPE='FOREGROUND';

## performance_schema 低级事件监听
show engines;
select * from information_schema.ENGINES where ENGINE ='performance_schema';
select table_name from information_schema.TABLES where TABLE_SCHEMA='performance_schema' and ENGINE='performance_schema';
show tables from performance_schema;
show tables from performance_schema like 'events%';
show tables from performance_schema like 'memory%';
select * from performance_schema.events_waits_current limit 1 ;
select * from performance_schema.events_waits_history limit 1 ;

## 启动等待事件监听，需要开启consumers
select * from performance_schema.setup_instruments where NAME like 'wait/synch%';
update performance_schema.setup_instruments set ENABLED=true,TIMED='yes' where NAME like 'wait/synch%';
##启动consumers
select * from performance_schema.setup_consumers where NAME like '%wait%';
update performance_schema.setup_consumers set ENABLED=true where NAME like '%wait%';

## 查看表大小
select concat(round(sum(DATA_LENGTH / 1024 / 1024), 2), 'MB') as data
from information_schema.TABLES
where table_schema = '{db_name}'
  and TABLE_NAME = '{table_name}';

## 查看表索引大小
select concat(round(sum(TABLES.INDEX_LENGTH / 1024 / 1024), 2), 'MB') as data
from information_schema.TABLES
where table_schema = '{db_name}'
  and TABLE_NAME = '{table_name}';

show  variables like 'innodb_buffer_pool_size';
## 缓存实例，当innodb_buffer_pool_size>1G时，建议增加innodb_buffer_pool_instances
show  variables like 'innodb_buffer_pool_instances';

## 该crc32选项使用更快的校验和算法，建议用于快速存储系统。
show  variables like 'innodb_checksum_algorithm';
## 该选项可优化旋转存储设备的I/O，禁用它用于非旋转存储或旋转和非旋转存储的混合。
show  variables like 'innodb_flush_neighbors';
## 对于低端非旋转存储设备，默认设置200通常就足够了。对于高端，总线连接的设备，请考虑更高的设置，如1000。
show  variables like 'innodb_io_capacity';
## 默认值为2000，适用于使用非旋转存储的工作负载。对于高端，总线附加的非旋转存储设备，考虑更高的设置，如2500。
show  variables like 'innodb_io_capacity_max';
## 如果重做日志在非旋转存储器上，请考虑禁用此选项以减少日志记录。
show  variables like 'innodb_log_compressed_pages';
## 如果重做日志在非旋转存储上，请配置此选项以最大化缓存和写入组合。
show  variables like 'innodb_log_file_size';
## 页大小
show  variables like 'innodb_page_size';
## 如果二进制日志在非旋转存储上，并且所有表都具有主键，请考虑将此选项设置minimal为减少日志记录。确保为你的操作系统启用了TRIM支持。通常默认情况下启用。
show  variables like 'innodb_page_size';
## 1、Innodb buffer pool 缓存池中包含数据的页的数目，包括脏页。单位是 page

show global status like 'Innodb_buffer_pool_pages_data';
## 2、innodb buffer pool 的页总数目。单位是page

show global status like 'Innodb_buffer_pool_pages_total';
## 3、编译的 InnoDB 页大小 (默认 16KB)。

show global status like 'Innodb_page_size';
##调优方法：计算 val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total *100% 的值
## 如果 val > 95% 则考虑增大 innodb_buffer_pool_size， 建议设置为：物理内存的75%

## 如果 val < 95% 则考虑减小 innodb_buffer_pool_size，建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

## 命令行设置命令
# 查看
show global variables like 'innodb_buffer_pool_size';
# 设置
set global innodb_buffer_pool_size = 2147483648;
# 缓冲池字节大小，单位kb，如果不设置，默认为128M

innodb_buffer_pool_size = 2147483648  # 设置2G

## 调整innodb_buffer 结束

## 查询redolog繁忙情况
set global innodb_monitor_enable = 'log_lsn_%';
SELECT name,count from information_schema.INNODB_METRICS where name in ('log_lsn_current','log_lsn_last_checkpoint');
SELECT * from sys.metrics where Variable_name in ('log_lsn_current','log_lsn_last_checkpoint');
show variables  like 'innodb_log_file_size';
show variables  like 'innodb_log_files_in_group';
## MRR 的rowid缓冲区，MRR: 二级索引与主键顺序不一致，MRR会在索引查完之后进行rowid的排序操作，然后回表查询数据【随机IO-->顺序IO】
show variables  like 'read_rnd_buffer_size';

## limit扫描全表数据进行查询，可以使用子查询的方式来提高性能。
explain
select *
from table_name  limit 500 OFFSET  2553486;

explain
SELECT * FROM `table_name` WHERE pid >= (SELECT pid FROM
`table_name` ORDER BY pid LIMIT 2553486 , 1) LIMIT 500;