存储引擎
存储引擎
一.存储引擎简介

- 1、文件系统：
- 1.1 操作系统组织和存取数据的一种机制。
- 1.2 文件系统是一种软件。
- 2、文件系统类型：ext2 3 4 ，xfs 数据
- 2.1 不管使用什么文件系统，数据内容不会变化
- 2.2 不同的是，存储空间、大小、速度。
- 3、MySQL引擎：
- 3.1 可以理解为，MySQL的“文件系统”，只不过功能更加强大。
- 4、MySQL引擎功能：
- 4.1 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能
总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。
二.MySQL自带的存储引擎类型
MySQL 提供以下存储引擎:
01）InnoDB
02）MyISAM
03）MEMORY
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE
09）NDBCLUSTER
10）CSV
还可以使用第三方存储引擎:
01）MySQL当中插件式的存储引擎类型
02）MySQL的两个分支
03）perconaDB
04）mariaDB
#InnoDB 支持hash算法 但是你创建一个hash算法成功 还是会用BTREE算法 MEMORY可以创建
mysql> alter table stu add index idx_ha using hash (id);
mysql> show index from stu;
+-------+------------+----------+--------------+-------------+-----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
+-------+------------+----------+--------------+-------------+-----------+
| stu | 1 | idx_ha | 1 | id | A |
+-------+------------+----------+--------------+-------------+-----------+
+-------------+----------+--------+------+------------+
| Cardinality | Sub_part | Packed | Null | Index_type |
+-------------+----------+--------+------+------------+
| 3 | NULL | NULL | YES | BTREE |
+-------------+----------+--------+------+------------+
三、MySQL实践
1.查看存储引擎
1）查看当前MySQL支持的存储引擎类型
mysql> show engines;
2）查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
+--------------+----------------------+--------+
| table_schema | table_name | engine |
+--------------+----------------------+--------+
| db | student | InnoDB |
| linux50 | course | InnoDB |
| linux50 | score | InnoDB |
| linux50 | student | InnoDB |
| linux50 | teacher | InnoDB |
| mysql | innodb_index_stats | InnoDB |
| mysql | innodb_table_stats | InnoDB |
| mysql | slave_master_info | InnoDB |
| mysql | slave_relay_log_info | InnoDB |
| mysql | slave_worker_info | InnoDB |
| oldboy | oldboy | InnoDB |
| test | score | InnoDB |
| test | stu | InnoDB |
| test | student | InnoDB |
| test | student2 | InnoDB |
| test | xiangqin | InnoDB |
| test | yinshi | InnoDB |
| world | city | InnoDB |
| world | country | InnoDB |
| world | countrylanguage | InnoDB |
+--------------+----------------------+--------+
3）查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------------+---------------------------+--------+
| table_schema | table_name | engine |
+--------------------+---------------------------+--------+
| information_schema | COLUMNS | MyISAM |
| information_schema | EVENTS | MyISAM |
| information_schema | OPTIMIZER_TRACE | MyISAM |
| information_schema | PARAMETERS | MyISAM |
| information_schema | PARTITIONS | MyISAM |
| information_schema | PLUGINS | MyISAM |
| information_schema | PROCESSLIST | MyISAM |
| information_schema | ROUTINES | MyISAM |
| information_schema | TRIGGERS | MyISAM |
| information_schema | VIEWS | MyISAM |
| mysql | columns_priv | MyISAM |
| mysql | db | MyISAM |
| mysql | event | MyISAM |
| mysql | func | MyISAM |
| mysql | help_category | MyISAM |
| mysql | help_keyword | MyISAM |
| mysql | help_relation | MyISAM |
| mysql | help_topic | MyISAM |
| mysql | ndb_binlog_index | MyISAM |
| mysql | plugin | MyISAM |
| mysql | proc | MyISAM |
| mysql | procs_priv | MyISAM |
| mysql | proxies_priv | MyISAM |
| mysql | servers | MyISAM |
| mysql | tables_priv | MyISAM |
| mysql | time_zone | MyISAM |
| mysql | time_zone_leap_second | MyISAM |
| mysql | time_zone_name | MyISAM |
| mysql | time_zone_transition | MyISAM |
| mysql | time_zone_transition_type | MyISAM |
| mysql | user | MyISAM |
+--------------------+---------------------------+--------+
4）使用 SELECT 确认会话存储引擎
#查询默认存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
5）使用 SHOW 确认每个表的存储引擎
#查看表的存储引擎
SHOW CREATE TABLE City\G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
2.存储引擎的设置
1）在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>
2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>
3）在 CREATE TABLE 语句指定
#建表的时候指定存储引擎
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
3.innodb和myisam的物理区别
#MyISAM
[root@db01 /application/mysql/data/mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 Nov 25 14:24 user.frm #表结构 (字段啊 字段类型啥的)
-rw-rw---- 1 mysql mysql 772 Dec 3 20:34 user.MYD #表空间
-rw-rw---- 1 mysql mysql 2048 Dec 3 20:45 user.MYI
#InnoDB
[root@db01 /application/mysql/data/test]# ll student.*
-rw-rw---- 1 mysql mysql 8692 Dec 3 16:32 student.frm #表结构
-rw-rw---- 1 mysql mysql 131072 Dec 3 16:32 student.ibd #表空间
4.innodb存储引擎的简介
在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点:
01）事务安全（遵从 ACID）
02）MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
03）InnoDB 行级别锁定
04）Oracle 样式一致非锁定读取
05）表数据进行整理来优化基于主键的查询
06）支持外键引用完整性约束
07）大型数据卷上的最大性能
08）将对表的查询与不同存储引擎混合
09）出现故障后快速自动恢复
10）用于在内存中缓存数据和索引的缓冲区池
innodb核心特性
重点:
MVCC
事务
行级锁
热备份
Crash Safe Recovery（自动故障恢复）
#行级锁：
你去公共厕所，进去之后你把大门锁了，自己一个人在里面蹲一排，别人都进不去，这就是表级锁
你没锁大门，进去之后找了个小坑，把自己小坑的门锁了，别人可以去别的坑，这就是行级锁
#客户都喜欢行级锁
比如微信修改头像，如果是表级锁，那么别人在修改的同时你就得排队，什么时候别人用完你才能用
行级锁就可以同时进行修改
5.myisam
四、企业案例
项目背景：
公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。
小问题不断：
- 1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
- 2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。
如何解决：
提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
1.准备新环境，使用mysql5.6.44
最好使用二进制装 比较快
2.备份导出数据
#全备 这个时候不能-A 因为会把系统自带的程序库也导出来 后面修改引擎就会报错
mysqldump -B 程序库> /tmp/full.sql
3.修改存储引擎
sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
vim /tmp/full.sql :%s#MyISAM#InnoDB#g
mysql
4.将备份的数据导入新环境
#可以scp过去 然后导入
mysql < /tmp/full.sql
**5.修改代码，连接数据库的IP **
6.停库
#停掉现在用的问题库
systemctl stop mysqld
7.截取全备，到停库之间的新数据
8.恢复到新数据库，开启业务
简历案例---zabbix监控系统架构整改
环境: zabbix 3.2 mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本
2. 数据库版本
3. zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到5.7版本,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.参数调整...----->安全性参数关闭,提高性能.
五、innodb-表空间
1.共享表空间（5.5版本以后出现共享表空间概念）
只存这三个东西
- 系统数据
- 临时表
- undo log（事务日志）
#ibdata1就是共享表空间
[root@db01 /application/mysql/data]# ll
-rw-rw---- 1 mysql mysql 56 Dec 2 10:00 auto.cnf
drwx------ 2 mysql mysql 58 Dec 3 09:50 db
-rw-rw---- 1 mysql mysql 33962 Dec 4 11:52 db01.err
-rw-rw---- 1 mysql mysql 5 Dec 4 11:52 db01.pid
-rw-rw---- 1 mysql mysql 79691776 Dec 4 18:16 ibdata1
-rw-rw---- 1 mysql mysql 52428800 Dec 4 11:52 ibdata2
-rw-rw---- 1 mysql mysql 50331648 Dec 4 18:16 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Dec 2 09:51 ib_logfile1
drwx------ 2 mysql mysql 166 Dec 2 19:47 linux50
drwx------ 2 mysql mysql 4096 Nov 25 14:24 mysql
drwx------ 2 mysql mysql 56 Dec 3 20:20 oldboy
drwx------ 2 mysql mysql 4096 Nov 25 14:24 performance_schema
drwxr-xr-x 2 mysql mysql 238 Dec 4 18:16 test
drwx------ 2 mysql mysql 144 Dec 4 09:34 world
#共享表空间（默认大小12M）
mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath | |
| ssl_crlpath | |
+-----------------------+------------------------+
autoextend：自动扩展
#查看共享表空间大小
[root@db01 data]# du -sh
ibdata1 76M ibdata1
#共享表空间的切割
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/application/mysql-5.6.40/data/db01.pid).
[root@db01 /application/mysql/data]# du -sh ibdata1
76M ibdata1
[root@db01 /application/mysql/data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
[root@db01 /application/mysql/data]# /etc/init.d/mysqld start
Starting MySQL... SUCCESS!
#这个时候你的数据就会存在ibdata2中 不会再存在1中了
报错原因：设置共享表空间的大小，50M小于实际表空间大小76M
解决方法：修改配置文件中的50M，76M innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
注意：ibdata1 已产生的共享表空间大小一定要与配置文件中的大小一致，不能多，也不能少
2.独立表空间
- 生产数据，用户数据
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
#独立表空间
[root@db01 /application/mysql/data/world]# ll
-rw-rw---- 1 mysql mysql 671744 Dec 4 09:34 city.ibd
-rw-rw---- 1 mysql mysql 196608 Dec 3 16:43 country.ibd
-rw-rw---- 1 mysql mysql 229376 Dec 2 09:09 countrylanguage.ibd
#开启独立表空间
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
六、企业案例
在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。
解决思路：
1.准备新环境
2.将旧数据导入到新环境
3.需要知道建表语句（表结构）
# 1.准备新环境
53上看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
# 2.将旧数据导入到新环境
[root@db01 /application/mysql/data]# scp -r world 172.16.1.53:/application/mysql/data
[root@db03 /application/mysql/data]# chown -R mysql.mysql world
53上看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| world |
+--------------------+
mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist
有库有表但是没有数据！因为你的表损坏了
# 3.找开发要建表语句
mysql> show create table world.city;
#生产环境中库不行了是没有办法show create的 只能问开发要创表语句
CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `idx_pop` (`Population`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
# 4.在新环境里建表
CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `idx_pop` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
ERROR 1215 (HY000): Cannot add foreign key constraint
#报错 先把外键干掉 还有个逗号别忘了
mysql> CREATE TABLE `city_new` (
-> `ID` int(11) NOT NULL AUTO_INCREMENT,
-> `Name` char(35) NOT NULL DEFAULT '',
-> `CountryCode` char(3) NOT NULL DEFAULT '',
-> `District` char(20) NOT NULL DEFAULT '',
-> `Population` int(11) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`ID`),
-> KEY `CountryCode` (`CountryCode`),
-> KEY `idx_pop` (`Population`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.28 sec)
# 5.删除新表的表空间
mysql> alter table world.city_new discard tablespace;
# 6.拷贝旧表空间
[root@db03 /application/mysql/data/world]# cp -a city.ibd city_new.ibd
[root@db03 /application/mysql/data/world]# chown -R mysql.mysql city_new.ibd
忘记cp -a参数的话 给个权限
# 7.导入表空间
mysql> select * from world.city_new;
ERROR 1814 (HY000): Tablespace has been discarded for table 'city_new'
报错是因为前面把表空间删了
mysql> alter table world.city_new import tablespace;
# 8.删除旧表 新表改名
[root@db03 /application/mysql/data/world]# rm -rf city.frm
[root@db03 /application/mysql/data/world]# rm -rf city.ibd
mysql> alter table world.city_new rename world.city;
浙公网安备 33010602011771号