随笔- 383 文章- 10 评论- 21 阅读- 14万

mysql优化参数

-- 隔离级别
select * from `performance_schema`.variables_by_thread where variable_name='transaction_isolation';
-- 全局级别和回话级参数  作用于
set session binlog_rows_query_log_events=on;
set GLOBAL binlog_rows_query_log_events=on;
 
 
-- 缓冲区大小
set global sort_buffer_size=16*1024*1024;
-- 对设置之后的会话有效，对之前连接的会话无效
SELECT @@global.sort_buffer_size,@@session.sort_buffer_size;
 
 
SELECT  @@session.max_join_size;
 
set @@session.max_join_size=DEFAULT;-- 设置后为mysql内置默认值，而不是配置文件里设置的值
set @@session.max_join_size=@@global.max_join_size;-- 会话级别设置全局
 
-- 查看连接字符集
show variables like '%_connection';
 
-- 计算负载高峰时占用的总内存，这个记过通常偏大，因为所有的线程都同时用到设定内存分配的最大值的情况几乎不存在，每个线程如果只是处理简单的工作，大约需要256KB的内存，
 
SELECT
    ((
            @@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@binlog_cache_size + @@max_connections
            ) * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@thread_stack + @@tmp_table_size )/(
            1024 * 1024 * 1024
            ) 
    ) AS max_memery_gb;
 
-- mysql实际使用的内存总和
SELECT * from sys.memory_global_total;
 
-- 确认是否开启了所有的内存监控项
select * from performance_schema.setup_instruments where name like '%memory%' and enabled!='YES';
 
sys.memory_global_total 统计值不准有三个原因
 
1)没有在实例启动时就开启所有的内存监控项
 
MySQL 8.0 之前版本默认只开启了performance_schema相关内存时间监控项。其他内存监控项默认都未启用，如需启用需要在配置文件中显示指定，可以通过如下方案启动所有内存监控项：
 
在配置文件中添加 performance-schema-instrument='memory/%=ON' 重启实例

查看慢查询是否开启
select @@slow_query_log;
慢查询时间
select @@long_query_time;
开始慢查询
set global slow_query_log=1;
慢查询文件记录
select @@slow_query_log_file;<br><br>select @@log_slow_extra;mysql 8.0.14版本之后才有，当他为true时，它将记录执行sql额外的信息

select * from sys.statement_analysis limit 1\G;
 
找出总计执行时间最长的sql
如果某个sql就执行一次，花的时间比较长，就不属于这种情况不需要优化，要是多次执行的总时间消耗最长就需要优化（默认就是降序排序）
select * from sys.statement_analysis;

1 2	`查找运行时间最长的5%的语句` `select * from sys.statements_with_runtimes_in_95th_percentile;`

查询平均时间最长的语句：
 
select * from `performance_schema`.events_statements_summary_by_digest order by avg_timer_wait desc limit 1\G;

查找行数最多的sql语句，这种sql通常消耗对多磁盘的读写
select * from `performance_schema`.events_statements_summary_by_digest order by sum_rows_examined desc limit 1\G;
 
 
执行次数最多的sql,通常对整体系统性能影响最大
select * from `performance_schema`.events_statements_summary_by_digest order by count_star desc limit 1\G;