mysql 查看用户默认表空间

1.什么是存储引擎

相当于MySQL内置的文件系统。与 Linux中的文件系统打交道的层次结构。

2. MySQL存储引擎种类

mysql> show engines;
 MRG_MYISAM         
 CSV                
 MyISAM             
 BLACKHOLE          
 PERFORMANCE_SCHEMA 
 InnoDB MySQL5.5版本以后默认的存储引擎。99%以上的业务表是InnoDB            
 ARCHIVE            
 MEMORY             
 FEDERATED

2.1其他分支

percona  : XtraDB
MariaDB  : InnoDB
其他的引擎:
TokuDB , MyRocks 
Compression: 25x for high compression
Fast Insertions and Deletions
适合于，业务当中有大量插入或者删除操做的场景。
应用于，数据量较大的业务。

2.2实际案例

环境: zabbix 3.2  +  centos 7.3  + mariaDB 5.5 InnoDB引擎  ,zabbix系统 监控了2000多个节点服务
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本 
2. 数据库版本
3. zabbix数据库500G,存在一个文件里ibdata1，手工删除1个月之前的数据，空间不释放。

优化建议:
1.数据库版本升级到percona 5.7+ 版本 mariadb 10.1+,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

原因：

1. 原生态支持TokuDB,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍
2. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.

3. InnoDB 核心特性介绍 

1 Clustered index 
 2 Change buffer 
 3 自适应hash索引：AHI 
 4 MVCC  
 5 多缓冲区池      
 6 事务   
 7 行级锁粒度         
 8 外键
 9 更多复制特性 
10 支持热备
11 自动故障恢复

生产项目

小项目： 
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责:
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数

4. 存储引擎的基础管理命令

1 使用 SELECT 确认会话存储引擎
SELECT @@default_storage_engine;

2 默认存储引擎修改(不代表生产操作)
set default_storage_engine=myisam;

3 全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.

4 永久生效
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

4.1 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE x\G;
SHOW TABLE STATUS LIKE 'xx'\G

4.2 INFORMATION_SCHEMA 确认每个表的存储引擎

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

4.3修改一个表的存储引擎

mysql> alter table x engine=innodb;
注意：此命令我们经常使用他，进行innodb表的碎片整理

平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎

业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放

处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去

现在:
对表进行按月进行分表(partition,中间件)或者归档表(pt-archive)
业务替换为truncate方式

4.4扩展:如何批量修改

需求1:将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';

需求2：将所有非InnoDB业务表查询出来，并修改为InnoDB
select concat("alter table ",table_schema,".",table_name," engine=innodb;" )
from information_schema.tables 
where engine != 'InnoDB'
and table_schema not in ('sys','performance_schema','information_schema','mysql')
into outfile '/tmp/alter.sql' ;

5. MySQL 存储引擎体系结构

5.1宏观结构

5.1.1 MyISAM

myt.frm  ： 数据字典信息(列的定义和属性)
myt.MYD  ： 数据行
myt.MYI  ： 索引

5.1.2 InnoDB

city.frm ： 数据字典信息(列的定义和属性)
city.ibd(独立表空间文件) ： 数据行和索引
ibdata1 (共享表空间文件) ： 数据字典信息，UNDO（事务回滚日志）
double write 磁盘区域，change buffer磁盘区域

说明： 不同版本ibdata1中存储的数据不一样
5.5 ： ibdata1 中还会存储临时表数据 +用户数据(数据行+索引)
5.6 ： ibdata1 中还会存储临时表数据 5.1.2
8.0 ： ibdata1取消存储 数据字典信息，undo 独立了。

你可以理解为，MySQL在慢慢瘦身ibdata1共享表空间，把比较关键的数据独立出来了。
ib_logfile0~ib_logfileN: InnoDB 事务重做日志（redo log）
ibtmp1: 临时表空间文件（排序，分组，多表连接，子查询，逻辑备份等。）
ib_buffer_pool：正常关库的时候，存储缓冲区的热数据。
所以： InnoDB的表，仅仅是拷贝ibd 和 frm 文件到新的数据库，是无法正常使用。

 

5.2 InnoDB 微观结构

5.2.1 磁盘

数据存储：
(1) 表空间
    1>. 什么是表空间？
     表空间概念是引入于Oracle数据库。
     起初为了解决存储空间扩展的问题。MySQL5.5 版本引入了共享表空间模式。
    2>. MySQL 表空间类型
     共享表空间： 
     在5.5 版本引入了共享表空间(ibdata1)，作为默认存储方式。
     用来存储：系统数据，日志，undo，临时表，用户数据和索引。
     独立表空间： 5.6 版本默认独立表空间模式。单表单表空间。
     普通表空间： 完全和Oracle一致的表空间管理模式
     undo表空间： 存储undo logs（回滚日志）
     临时表空间   存储临时表。5.7 默认独立。
      3>. 表空间管理
    用户数据默认的存储方式，独立表空间模式。独立表空间和共享表空间是可以互相切换的。
    -- 查看默认表空间模式？
    mysql> select @@innodb_file_per_table;
    说明： 
    1代表独立表空间
    0代表共享表空间模式
    -- 如何切换 ？
    临时：
    mysql> set global innodb_file_per_table=0;
    重新登陆会话：
    永久： 
    vim /etc/my.cnf
    innodb_file_per_table=0
    -- 验证
    mysql> select @@innodb_file_per_table;
    说明: 修改完成之后，只影响新创建的表。
    -- 如何扩展共享表空间大小和个数？ 
    说明： 通常是在初始化数据时，就设定好参数。
    方法1：初始化之前，需要在my.cnf 加入以下配置即可：
    innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
    方法2：已运行的数据库上扩展多个ibdata文件

 

5.2.2事务日志

redo log 重做日志
(1)文件位置： 
    /data/3306/ib_logfile0~ib_logfileN  
(2)控制参数： 
innodb_log_file_size=50331648      # 设置文件大小
innodb_log_files_in_group=2        # 设置文件个数
innodb_log_group_home_dir=./       # 设置存储位置
(3)功能：
   用来存储，MySQL在做修改类（DML）操作时的《数据页变化》过程及版本号（LSN），属于物理日志。
   默认两个文件存储redo，是循环覆盖使用的。

undo logs 回滚日志 
(1) 文件位置：（5.7 默认位置）
     ibdataN
     ibtmp1
(2) 参数：
innodb_rollback_segments=128   # 回滚段的个数

(4) 功能： 
    用来存储回滚日志，可以理解为记录了每次操作的反操作，属于逻辑日志。
    1. 使用快照功能 提供InnoDB多版本并发读写。
    2. 通过记录的反操作，提供回滚功能。

 

 5.2.2 内存

(1)数据内存区域：
(1.1)共享内存缓冲区 
buffer pool 缓冲区池：
参数： 
mysql> select @@innodb_buffer_pool_size;
功能： 
缓冲 数据页 + 索引页 
(1.2) 会话内存缓冲区
join_buffer_size
key_buffer_size
read_buffer_size         
read_rnd_buffer_size     
sort_buffer_size  

 
(2) 日志
innodb_log_buffer_size=16777216    
功能: 负责redo日志的缓冲