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掉小表，立即回收空间。
数据库中有些表使用delete删除了一些行后，发现空间并未释放
产生原因：类比Oracle的高水位线产生原理

delete 不会释放文件高水位 
truncate会释放 ，实际是把.ibd文件删掉了，再建一个。
delete + alter engine=innodb会释放， 相当于重建表。
对应解决方案：

执行 OPTIMIZE TABLE 表名：只对MyISAM, BDB和InnoDB表起作用，而且会锁表！
写一SQL，创建新表，删除旧表，新表重命名：生产环境中不停机情况下，数据比较难处理
考虑用表分区，过期的表分区直接删除，不存在高水位问题，推荐。

批量修改表的语法

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方式