实战——mysql8.0配置优化以及sql优化方案

配置优化

# 解决Error writing file '/tmp/MYQUn0a7' (Errcode: 28 - No space left on device)所以决定修改下临时目录文件配置。
tmpdir=/data/mysql/tmp
# 注：缓冲池位于主内存中，InnoDB用它来缓存被访问过的表和索引文件，使常用数据可以直接在内存中被处理，从而提升处理速度；根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的75%
innodb_buffer_pool_size=100G
# 注：该参数可以指定系统表空间文件的路径和ibdata1文件的大小。默认大小是10MB，这里建议调整为1GB
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:1G:autoextend
# 该参数代表MySQL数据库的最大连接数
max_connections=2000
# 注：慢查询日志的开关，该参数等于1代表开启慢查询
slow_query_log=1
# 注：慢查询的时间，某条SQL语句超过该参数设置的时间，就会记录到慢查询日志中。单位是秒
long_query_time=20
# 注：表名是否区分大小的参数。默认是值为0。0代表区分大小写，1代表不区分大小写，以小写存储
lower_case_table_names=1
# 线程缓存池大小
thread_cache_size=128
# 临时表大小
tmp_table_size=2G
key_buffer_size=128M

sql优化方案

一、千万级数据量时，limit最后几页过慢优化

# 原sql语句(优化前9s)
SELECT * FROM `sta_gaj_population` LIMIT 5000000, 10;

# 优化后(1.6s)
SELECT * FROM `sta_gaj_population` WHERE id >= (SELECT id FROM (SELECT id FROM `sta_gaj_population` ORDER BY id LIMIT 5000000,1) AS t) LIMIT 10