7.innodb存储引擎(mysql版本8.0.20)-1

1. 介绍

相当于Linux中的文件系统.存储引擎就是MySQL中的文件系统.负责和"磁盘"打交道

2. 类型

支持多种存储引擎.插件式.针对不同表可以使用不同的存储引擎.
5.5 版本开始,用户自定义表,默认是InnoDB.
show engines;

3. InnoDB存储引擎核心特性

事务
行锁
热备
自动故障恢复
MVCC
聚簇索引
AHI
change buffer
多缓冲区支持
外键

4. 彩蛋: 同源产品的存储引擎

Mariadb , percona : TokuDB
a. 高压缩比
b. Insert 性能高

参考:
Additional features unique to TokuDB include:
Up to 25x Data Compression
Fast Inserts
Eliminates Slave Lag with Read Free Replication
Hot Schema Changes
Hot Index Creation - TokuDB tables support insertions, deletions and queries with no down time while indexes are being added to that table
Hot column addition, deletion, expansion, and rename - TokuDB tables support insertions, deletions and queries without down-time when an alter table adds, deletes, expands, or renames columns
On-line Backup

参考内容：
https://www.jianshu.com/p/898d2e4bd3a7
https://mariadb.com/kb/en/installing-tokudb/
https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html

案例分享:

案例1:
环境:
zabbix监控系统: 监控了2000+,使用了 centos 7.x + mariadb 5.5版本
问题:
每个3-5个月,zabbix 监控展示很慢, ibdata1超大. 需要重新搭建系统.

分析:
1. zabbix 版本有bug .
2. mariadb 5.5 ,默认所有数据都在ibdata共享表空间
3. InnoDB引擎,压缩比不高.
4. InnoDB插入性能不高

建议:

1. 升级zabbix到较新版本
2. 升级数据库版本 mariadb 10.x,数据就会独立存储出来
3. 使用Tokudb引擎.
4. 分区表.

案例2:
information_schema.tables应用案例-1
背景: 历史遗留问题,几十张表是MyISAM引擎.
问题: 业务稍微忙一些的时候,导致业务网站卡住.断电情况下,会有部分数据(索引)损坏.主从1年多没同步了.
分析问题:
a. 确认版本5.5版本
select version();
b. 确认业务表的引擎
select table_schema,table_name,engine from information_schema.tables
where table_schema not in (‘mysql’,‘information_schema’);
# 查询业务库下的非InnoDB的表
select table_schema,table_name,engine from information_schema.tables
where table_schema not in (‘mysql’,‘information_schema’) and engine !=‘innodb’;
# 确认是否是从低版本升级过来.

c.监控锁的情况
show status like ‘%lock%’
发现有很多table_lock信息.

d.检查主从状态
show slave status \G
处理方案:
a. 将所有非InnoDB表,查出来.
select table_schema,table_name,engine from information_schema.tables
where table_schema not in (‘mysql’,‘information_schema’) and engine !=‘innodb’;

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and engine !='innodb';

b. 将所有非InnoDB表,替换成InnoDB

SELECT CONCAT(“ALTER TABLE “,table_schema,”.”,table_name," ENGINE=INNODB;")
FROM information_schema.tables
WHERE table_schema
NOT IN (‘mysql’,‘information_schema’,‘performance_schema’,‘sys’)
AND ENGINE !=‘innodb’
INTO OUTFILE ‘/tmp/alter.sql’;

案例3：
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM

解决方案:

1. 升级MySQL 5.6.1x版本
2. 升级迁移所有表到新环境，调整存储引擎为InnoDB
3. 开启双1安全参数
4. 重构主从

5. 基础管理

5.1 查看
mysql> show engines;
mysql> select @@default_storage_engine;
mysql> show engine innodb status ;

5.2 修改
create table t1 (id int) engine=innodb;
alter table t1 engine=innodb ;
功能: 修改引擎,整理碎片.

5.3 存储引擎批量管理

将所有非InnoDB表,查出来.
mysql> select table_schema,table_name ,engine
from information_schema.tables
where
table_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’)
and engine !=‘innodb’;
将所有非InnoDB表,替换成InnoDB
mysql> select concat(“alter table “,table_schema,”.”,table_name," engine=innodb;") from information_schema.tables where table_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’) and engine !=‘innodb’ into outfile ‘/tmp/a.sql’;
mysql> source /tmp/a.sql

6. InnoDB存储引擎体系结构 ******
   6.1 On-Disk

6.1.1 表空间TBS
a. 共享表空间

介绍:

5.5版本出现的功能. ibdataN…
各版本存储的内容:
5.5 : 系统数据字典,undo ,tmp,DWB,change buffer,用户数据
5.6 : 将用户数据默认独立了.
5.7 : tmp独立了,undo可以在初始化时,手工独立出来
8.0.19之前 : 系统数据字典取消,只剩余change buffer和DWB
8.0.19之后 : DWB也独立了.只剩余change buffer.

管理

aa. 查看配置
mysql> select @@innodb_data_file_path;

bb. 后加
vim /etc/my.cnf
innodb_data_file_path=ibdata1:76M;ibdata2:128M:autoextend

cc. 重启数据库

dd. 初始化时设定
[root@db01 data]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

生产建议:
1-4G ,2-3个
b. 独立表空间
5.6 版本后,默认用来存储用户数据的.
8.0之前:
一张表:
t1.ibd
t1.frm
ibdataN
mysql.统计信息…
各种日志

8.0 之后
t1.ibd
mysql.统计信息…
各种日志

区: extent
页 : page

配置:
mysql> select @@innodb_file_per_table;
±------------------------+
| @@innodb_file_per_table |
±------------------------+
| 1 |
±------------------------+

c. undo表空间
#介绍
记录回滚日志.
8.0之前不调配,默认在ibdata里面.生产建议,初始化手工独立undo表空间.一般2-4个,默认大小1G

#管理
8.0之前:
#########官方文档说明############
Important
The number of undo tablespaces can only be configured
when initializing a MySQL instance and is fixed for the life of the instance.
#################################

SELECT @@innodb_undo_tablespaces; ---->3-5个 #打开独立undo模式，并设置undo的个数。
SELECT @@innodb_max_undo_log_size; #undo日志的大小，默认1G。
SELECT @@innodb_undo_log_truncate; #开启undo自动回收的机制（undo_purge）。
SELECT @@innodb_purge_rseg_truncate_frequency; #触发自动回收的条件，单位是检测次数。

8.0 之后
mysql> create undo tablespace oldguo add datafile ‘oldguo.ibu’;
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
-> WHERE FILE_TYPE LIKE ‘UNDO LOG’;
mysql> alter undo tablespace oldguo set inactive;
mysql> drop undo tablespace oldguo;
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE ‘UNDO LOG’;

d. temp表空间

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

6.1.2 日志文件

a. undo 日志
存放至undo表空间,undu_001,002 ibu
功能:存储回滚日志

b. redo 日志
存放重做日志. 默认48M
ib_logfile0
ib_logfile1

设置建议:
512M-4G,和TPS ,3-5组

select @@innodb_log_file_size ;
select @@innodb_log_files_in_group ;
select @@innodb_log_group_home_dir ;

注意:
生产中修改redo配置,必须优雅关闭数据库,重启数据库.
set global innodb_fast_shutdown=0;

6.1.3 其他结构
a. ib_buffer_pool
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_at_shutdown

b. DWB (double write buffer)

#ib_16384_0.dblwr
#ib_16384_1.dblwr

6.2 In-Memory

6.2.1 IBP InnoDB Buffer pool
MySQL中最大的共享内存区域.
缓冲和缓存数据页和索引页

mysql> select @@innodb_buffer_pool_size;
生产建议: 物理内存的50-75%
mysql> set global innodb_buffer_pool_size=268435456;
Query OK, 0 rows affected (0.00 sec)

[root@db01 data]# vim /etc/my.cnf
innodb_buffer_pool_size=268435456

data buffer
AHI
Change buffer

6.2.2 ILB InnoDB log buffer 日志缓冲区
缓冲redo log的内存区域
mysql> set global innodb_log_buffer_size=16777216;

512M-1G

8c 16G
8c 32G

2-4G
16c 64G
32c 128G

本文地址：https://blog.csdn.net/xiaoleinb/article/details/111997802