mysql_5_1什么是存储引擎
标签（空格分隔）： mysql
mysql内置的文件系统
1.mysql内置的文件系统。
2.与linxu文件系统打交道的层次结构。
存储引擎种类
1.mysql
| InnoDB |
| MRG_MYISAM |
| MyISAM |
| BLACKHOLE |
| PERFORMANCE_SCHEMA |
| CSV |
| ARCHIVE |
| MEMORY |
| FEDERATED |
| Aria |
2.其他版本 percona MariaDB
percona XtraDB
MariaDB InnoDB默认
3.其他引擎
TokuDB
压缩比高
适用于 业务当中有大量插入或者删除操作的场景
适用于 数据量较大的业务
MYROCKS
mariadb 5,5 和mysql 5.6 都会将数据放置到idbdata1 文件里 所以这个文件会变得很大。
对于zabbix版本
建议将mariadb 升级到10.1+ zabbix升级到更高版本 5.7要比5.5版本性能高2-3倍
存储引擎修改为tokudb
监控数据按月份切割 对zabbix 数据保留机制重写
关闭binlog和双1
参数调整
innodb和myisam
INNOBD 核心特性
mvcc 多版本并发控制
clustered index 聚集索引
多缓冲区池
事务
行级锁
外键
更多复制特性
支持热备
change buffer 更新缓冲
自适应hash索引 AHI
自动故障恢复
实例
centos 5.8 mysql5.0 myisam 网站业务LNMP 数据量50G左右
业务压力大的时候，非常卡，经历过死机，会有部分数据丢失。
myisam存储引擎表级锁，在高并发时，会有高锁等待
myisam存储引擎不支持事物，在断电时，会有可能丢入数据
监控锁的情况 有很多的表锁等待
存储引擎查看：所有默认表时myisam
解决方案
升级5.6.10
迁移所有表到新环境
开启双1安全参数
一些命令
查看等待
show status like "%wait%"
查看锁
show status like "%lock%"
存储引擎的管理命令
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
存储引擎是表级别的，每个表创建时可以指定不同的存储引擎，但是建议统一为innodb。
确定每个表的存储引擎是什么？
show create table xG;
show table status like 'xx'G;
information_schema 也可以查询
修改一个表的存储引擎
alter table T1 engine = innodb
碎片处理
平常处理过的mysql问题--碎片处理 infomation schema data_free 碎片 delete删除产生的碎片
环境：centos7.4 mysql 5.7.20 INNOdb存储引擎
业务特点：数据量级比较大 ，需要按月删除历史数据
磁盘空间占用很大，不释放
命令 可以整理碎片
alter table T1 engine = innodb
完全整理 处理方法：
将数据逻辑到处，手动drop表，然后导入进去
现在：
对表进行按月进行分表（partition，中间件）或则归档表
业务替换为truncate方式
如何批量修改默认引擎
select concat（”alter table zabbix“,table_name,"engine tokudb;"） from information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql'
MYISAM 和 INNODB 的不同
myisam
数据文件和索引文件是分离的。 非聚集索引
frm 数据表的结构
myisam.MYI 索引字段
MYD 数据
myisam的叶子节点存放磁盘文件数据的指针 查记录需要回表一次。
innodb
行锁
用户表空间
frm 数据表结构
ibd 索引加表数据
系统表空间、共享表空间
ibdata1 UNDOLOG DOUBLE WRITE 磁盘区域 事务回滚日志
CHANGE BUFFER 磁盘区域
5.5 ibdata1 临时表数据 + 用户数据 数据行+索引
5.6 ibdata 临时表数据
8.0 数据字典信息取消存储 UNDO也独立了
日志文件
事务日志文件
临时表空间
ibtmp1 临时表空间文件 子查询 分表连接 缓冲区不够的时候 备份 排序 分组
ib_buffer_pool
热数据 正常关闭库的时候 存储缓冲区的热数据 加快反应速度
事物日志
ib_logfile0 innodb事物重做日志 redo log
ib_logfile1
支持事务
INNO微观结构不同
磁盘
什么是表空间
类似于linux下的lvm功能。
tablespace
表空间概念是引入于Oracle数据库
起初是为了解决存储空间扩展的问题。mysql5.5版本引入了共享表空间模式
mysql表空间类型
共享表空间
在5.5版本引入了共享表空间ibdata1，作为默认存储方式
用来存储：系统数据，日志，undo，临时表，用户数据和索引。
独立表空间：5.6版本默认独立表空间模式，单表单表空间
普通表空间：完全和oracle一致的表空间管理模式
undo表空间：存储undo logs 回滚日志
临时表空间：存储临时表。5.7默认毒粒
表空间管理
用户数据默认的存储方式，独立表空间模式。独立表空间和共享表空间是可以互相转化的。
-- 查看默认表空间
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-> set global innodb_file_per_table
修改完成之后，只影响新创建的表。
如果扩展共享表空间大小和个数。
说明：通常是在初始化数据时，就设定好参数。
方法1：初始化之前，需要在my.cnf 加入以下配置即可。
innodb_data_file_path = ildata1:1G;ibdata2:1G:autoextend
方法2：已运行的数据库上扩展多个ibdata文件
错误的方式：
innodb_data_file_path=ibdata1:128M;ibdata2:128M;ibdata3:128M:autoextend
错误日志
vim /data/3306/db01.err
解决方法：在设置innodb_data_file_path参数时，已有的ibdata1文件大小应该和磁盘上真实大小一致，而不是随便指定的
正确的调整方式：
innodb_data_file_path=ibdata1:76M;ibdata2:128M;ibdata3:128M:autoextend
段 区 页
表 ---》 表空间 ---》段 ----》多个区----》连续页 ---》 连续的block ----》 连续的扇区
日志
redo log重做日志
文件位置：/data/3306/ib_logfile
控制参数：
innodb_log_file_size = 50331648
innodb_log_file_in_group = 2
innodb_log_group_home_dir=./
功能：
用来存储，mysql insert update delete 操作时的数据页变化以及版本号LSN。属于物理日志。
默认两个文件存储redo，是循环覆盖使用的。
undo logs回滚日志
show variables like "%undo%";
1.文件文字
ibdata N
ibtmp1
2.参数
innodb_rollback_segments=128 #回滚段的个数
3.功能：
提供了每次操作的反操作，属于逻辑日志，可以理解为记录了每次操作的反操作，属于逻辑日志。
存储回滚日志的。提供innodb多版本读写
提供回滚功能，通过记录的反操作
内存
数据内存区域：
共享内存区域
Innodb_buffer_pool_size
buffer_pool 缓冲区池
参数：
select @@innodb_buffer_pool_size;
功能：
缓冲 数据页+索引页
会话内存区域
join_buffer_size
key_buffer_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
日志缓冲
innodb_log_buffer_size
功能： 负载redo日志的缓冲
日志
各种buffer 查看
show variables like '%buffer%'
| aria_pagecache_buffer_size | 134217728 |
| aria_sort_buffer_size | 268434432 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| join_buffer_space_limit | 2097152 |
| key_buffer_size | 67108864 |
| mrr_buffer_size | 262144 |
| myisam_sort_buffer_size | 16777216 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1048576 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 1048576 |
| sql_buffer_result | OFF |
innodb_log_buffer_size 日志缓冲
案例
故障描述：
断电了， 重启完成后“/”只读
fsck 重启，系统成功启动，mysql启动不了。
结果：confulence库在，jira库不在了
怎么修复
表空间迁移
建表语句 和 ibd文件 可以恢复数据
1.建立一个一模一样的t1空表
create table t1 一样的语句
2. 删掉空表的表空间 ibd
alter table confulence.t1 discard tablespace;
3.拷贝准备好的ibd文件，修改对应权限，导入表空间的t1表
alter table confulence.t1 import tablespace;
1.创建107和原来一模一样的表
set foreign_key_checks = 0 跳过外检检查
2.表空间删除
select concat("alter table ",table_schema,".",table_name," discard tablespace;") from information_schema.tables where table_schema="库";
3.拷贝生产中的库所有表的ibd文件拷贝到准备好的环境中
select concat("alter table ",table_schema,".",table_name," import tablespace;") from information_schema.tables where table_schema="库";
4.验证数据
表都可以访问了
死锁
可以通过 show engine innodb status
一个事务修改1
另一个事务修改1-5
第一个事务没有提交
第二个事务锁等待
第一个事务再对1-5 进行修改
就发生了死锁
MYSQL 会检测 并防止他的发生
乐观锁
没有人会跟我抢 ，我可以不上锁。
悲观锁
认为一定是有人和他抢的，不管用不用 都先锁上。
2亿行的表，想要删除其中的1000w 怎么做
1.如果两亿行数据 还没生成 设计表的时候 采用分区表的方式（按月range） 然后删除时truncate
2.已经存在了 使用pt-archive 工具进行归档 删除无用数据。