获取主键
SHOW KEYS from users where KEY_NAME ='PRIMARY'
导入SQL文件
mysql -u root -p --default-character-set=utf8
use dbname
source /root/newsdata.sql
设置 GROUP_CONCAT长度限制
SET GLOBAL group_concat_max_len = 10
设置 最大PACKET
set global max_allowed_packet = 2*1024*1024*10
查询key缓存区大小
show variables like 'key_buffer%';
设置key缓存区大小(可设置为总内存的1/4,够用就行)
set global key_buffer_size=128*1024*1024;
查看缓存使用率
show global status like 'key_blocks_u%';
查看库状态
show global status
查看表状态(参考其中key长度总计)
show table status
缓冲区配置查询:
show variables like 'innodb_buffer_pool%';
缓冲区配置结果:
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_filename ib_buffer_pool
#线程数
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_abort OFF
innodb_buffer_pool_load_at_startup OFF
innodb_buffer_pool_load_now OFF
#缓冲区大小,默认128M
innodb_buffer_pool_size 134217728
缓存池状态查询:
show global status like 'Innodb_buffer_pool_pages_%';
示例结果:
Innodb_buffer_pool_pages_data 8140
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 5456291
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_misc 52
Innodb_buffer_pool_pages_total 8192
优化标准:Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total > 95% 增大缓冲区
设置缓冲区办法:
1.更新my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系统内存%80
innodb_buffer_pool_instances = 大于8的情况下,每个缓冲池实例至少1GB
2.更新配置
set GLOBAL innodb_buffer_pool_size=268435456
可能风险:
1238 - Variable 'innodb_buffer_pool_size' is a read only variable
原因:
5.7以后支持在线及时修改,5.6不支持,可以选择运行一下命令查询版本号
select version()
停止服务
mysqladmin -u root -p shutdown
启动
mysqld
#或
net start mysql