DevOps开发运维
成长之路

MySQL存储引擎表级查看及修改

查看默认存储引擎

select @@default_storage_engine;

修改默认存储引擎(不代表生产操作)

会话级别(仅影响当前会话):退出登录之后,所有参数均失效.
set default_storage_engine=myisam;
全局级别(仅影响新会话):退出登录之后,所有参数均失效.
set global default_storage_engine=myisam;
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

确认每个表的存储引擎

方法1:逐库查看

show databases;
use world;
show table status from world;

方法2:全局查看

select table_schema,table_name,engine 
from information_schema.tables 
where table_schema 
not in ('sys','mysql','information_schema','performance_schema');

修改一个表的存储引擎

修改表的时候,表会被锁,所以需要和前端业务确认这张表访问是否频繁,在数据库业务不繁忙期间修改,DDL语句,会锁表,
对业务影响大。
create table tt(id int)engine=myisam;
show table status from world;
alter table world.tt engine innodb;
注意:此命令我们经常使用他,进行innodb表的碎片整理(碎片是因为业务经常有delete操作)
碎片的根本解决办法:
alter table world.tt engine innodb; 
optimize table t1;每周一次或每月一次即可,只对特定的表运行。在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
降低高水位线。
做归档表:历史表把历史数据清掉,需要按照月delete原来的数据,后来将表按月进行分表,把大表分为小表, truncace掉小表,立即回收空间。

批量修改表的语法

select concat("alter table",table_schema,".",table_name,"engine=innodb;") 
from information_schema.tables 
where table_schema='world';

需求:将zabbix库中的所有表,innodb替换为tokudb

select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

MySQL碎片处理问题

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表按月进行分表(partition,中间件)或者归档表(pt-archive)
业务替换为truncate方式
赞(0)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图