目录
一. 存储引擎简介
二. MySQL 自带的存储引擎类型
三. 真实企业案例
四. Innodb 存储引擎 -- 表空间介绍
一. 存储引擎简介
?
1, 文件系统:
1.1 操作系统组织和存取数据的一种机制.
1.2 文件系统是一种软件.
2, 文件系统类型: ext2 3 4 ,xfs 数据
2.1 不管使用什么文件系统, 数据内容不会变化
2.2 不同的是, 存储空间, 大小, 速度.
3,MySQL 引擎: 可以理解为, MySQL 的 "文件系统", 只不过功能更加强大.
4,MySQL 引擎功能: 除了可以提供基本的存取功能, 还有更多功能事务功能, 锁定, 备份和恢复, 优化以及特殊功能
总之, 存储引擎的各项特性就是为了保障数据库的安全和性能设计结构.
二. MySQL 自带的存储引擎类型
MySQL 提供以下存储引擎:
- 01)InnoDB
- 02)MyISAM
- 03)MEMORY
- 04)ARCHIVE
- 05)FEDERATED
- 06)EXAMPLE
- 07)BLACKHOLE
- 08)MERGE
- 09)NDBCLUSTER
- )CSV
还可以使用第三方存储引擎:
01)MySQL 当中插件式的存储引擎类型
02)MySQL 的两个分支
- 03)perconaDB
- 04)mariaDB
- # 查看当前 MySQL 支持的存储引擎类型
- MySQL> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.00 sec)
- # 查看 innodb 的表有哪些
- MySQL> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
- +--------------+----------------------+--------+
- | table_schema | table_name | engine |
- +--------------+----------------------+--------+
- | 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 |
- +--------------+----------------------+--------+
- 20 rows in set (0.03 sec)
- # 查看 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 |
- | MySQL | help_category | MyISAM |
- | MySQL | ndb_binlog_index | MyISAM |
- +--------------------+---------------------------+--------+
- 33 rows in set (0.01 sec)
1,innodb 和 myisam 的区别
物理上的区别:
- # 进入 MySQL 目录
- [[email protected]~l]# cd /application/MySQL/data/MySQL
- #myisam
- [[email protected] MySQL]# ll user.*
- -rw-rw---- 1 MySQL MySQL 10684 Mar 6 2017 user.frm
- -rw-rw---- 1 MySQL MySQL 960 Aug 14 01:15 user.MYD
- -rw-rw---- 1 MySQL MySQL 2048 Aug 14 01:15 user.MYI
- # 进入 Word 目录
- [[email protected] world]# cd /application/MySQL/data/world/
- #innodb
- [[email protected] world]# ll city.*
- -rw-rw---- 1 MySQL MySQL 8710 Aug 14 16:23 city.frm
- -rw-rw---- 1 MySQL MySQL 688128 Aug 14 16:23 city.ibd
2.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(自动故障恢复)
3. 查看存储引擎
1)使用 SELECT 确认会话存储引擎
- # 查询默认存储引擎
- MySQL> SELECT @@default_storage_engine;
- +--------------------------+
- | @@default_storage_engine |
- +--------------------------+
- | InnoDB |
- +--------------------------+
- 1 row in set (0.00 sec)
2)使用 SHOW 确认每个表的存储引擎
- # 查看表的存储引擎
- MySQL> show create table city\G
- *************************** 1. row ***************************
- ...
- ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- MySQL> show table status like 'city'\G
- *************************** 1. row ***************************
- Name: city
- Engine: InnoDB
- ...
3)使用 INFORMATION_SCHEMA 确认每个表的存储引擎
- # 查看表的存储引擎
- MySQL> select table_name,engine from information_schema.tables where table_name='city' and table_schema='world'\G
- *************************** 1. row ***************************
- table_name: city
- engine: InnoDB
- 1 row in set (0.00 sec)
4. 存储引擎的设置
1)在启动配置文件中设置服务器存储引擎
- # 在配置文件的 [mysqld] 标签下添加 / etc/my.cnf
- [mysqld]
- default-storage-engine=innodb
2)使用 SET 命令为当前客户机会话设置
- # 在 MySQL 命令行中临时设置
- MySQL> SET @@storage_engine=myisam;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- # 查看
- MySQL> select @@default_storage_engine;
- +--------------------------+
- | @@default_storage_engine |
- +--------------------------+
- | MyISAM |
- +--------------------------+
- 1 row in set (0.00 sec)
(3)在 CREATE TABLE 语句指定
- # 建表的时候指定存储引擎
- create table t (i INT) engine = <Storage Engine>;
- # 如: 建 test1 表, 指定存储引擎为 myisam
- MySQL> create table test1(id int) engine=myisam;
- Query OK, 0 rows affected (0.02 sec)
三. 真实企业案例
项目背景:
公司原有的架构: 一个展示型的网站, LAMT,MySQL5.1.77 版本(MYISAM),50M 数据量.
小问题不断:
1, 表级锁: 对表中任意一行数据修改类操作时, 整个表都会锁定, 对其他行的操作都不能同时进行.
2, 不支持故障自动恢复(CSR): 当断电时有可能会出现数据损坏或丢失的问题.
如何解决:
1, 提建议将现有的 MYISAM 引擎替换为 Innodb, 将版本替换为 5.6.38
1)如果使用 MYISAM 会产生 "小问题", 性能安全不能得到保证, 使用 innodb 可以解决这个问题.
2)5.1.77 版本对于 innodb 引擎支持不够完善, 5.6.38 版本对 innodb 支持非常完善了.
2, 实施过程和注意要素
1)备份生产库数据(mysqldump)
- [[email protected] test]# MySQL -uroot -p1
- MySQL> show tables;
- +----------------+
- | Tables_in_test |
- +----------------+
- | student4 |
- | tlbb2 |
- +----------------+
- 2 rows in set (0.00 sec)
- # 测试环境(先简单创几个存储引擎为 myisam)
- MySQL> create table test1(id int) engine=myisam;
- Query OK, 0 rows affected (0.02 sec)
- MySQL> create table test2(id int) engine=myisam;;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> create table test3(id int) engine=myisam;;
- Query OK, 0 rows affected (0.01 sec)
2)准备一个 5.6.44 版本的新数据库
- # 先导库, 准备一个环境
- [[email protected] test]# mysqldump -uroot -p1 -B test>/tmp/full.sql
3)对备份数据进行处理(将 engine 字段替换)
- # 方法一
- [[email protected] ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql
- # 方法二
- [[email protected] ~]# VIM /tmp/full.sql
- :%s#MyISAM#InnoDB#g
4)将修改后的备份恢复到新库
- # 方法一
- [[email protected] test]# MySQL -uroot -p123 -h 10.0.0.52 </tmp/full.sql
5)应用测试环境连接新库, 测试所有功能
- # 连接
- [[email protected] ~]# MySQL -uroot -p123
- # 查看存储引擎
- MySQL> select @@default_storage_engine;
- +--------------------------+
- | @@default_storage_engine |
- +--------------------------+
- | InnoDB |
- +--------------------------+
- 1 row in set (0.00 sec)
6)停应用, 将备份之后的生产库发生的新变化, 补偿到新库
7)应用割接到新数据库
项目结果:
解决了 "小问题"
四. Innodb 存储引擎 -- 表空间介绍
?
5.5 版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
5.6 版本中默认的是独立表空间
1, 共享表空间
1)查看共享表空间
- # 物理查看
- [[email protected] ~]# ll /application/MySQL/data/
- -rw-rw---- 1 MySQL MySQL 79691776 Aug 14 16:23 ibdata1
- # 命令行查看
- MySQL> show variables like '%path%';
- +-----------------------+------------------------------------+
- | Variable_name | Value |
- +-----------------------+------------------------------------+
- | innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |
- | ssl_capath | |
- | ssl_crlpath | |
- +-----------------------+------------------------------------+
- 3 rows in set (0.00 sec)
- # 查看大小
- [[email protected] data]# du -sh ibdata1
- 76M ibdata1
5.6 版本中默认存储:
1. 系统数据
2.undo
3. 临时表
5.7 版本中默认会将 undo 和临时表独立出来, 5.6 版本也可以独立, 只不过需要在初始化的时候进行配置
2)设置方法
- # 编辑配置文件
- [[email protected] ~]# VIM /etc/my.cnf
- [mysqld]
- innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
2, 独立表空间
对于用户自主创建的表, 会采用此种模式, 每个表由一个独立的表空间进行管理
1)查看独立表空间
- # 物理查看
- [[email protected] ~]# ll /application/MySQL/data/world/
- -rw-rw---- 1 MySQL MySQL 688128 Aug 14 16:23 city.ibd
- # 命令行查看
- MySQL> show variables like '%per_table%';
- +-----------------------+-------+
- | Variable_name | Value |
- +-----------------------+-------+
- | innodb_file_per_table | ON |
- +-----------------------+-------+
- 1 row in set (0.01 sec)
企业案例
在没有备份数据的情况下, 突然断电导致表损坏, 打不开数据库.
1)拷贝库目录到新库中(先准备一个和原表结构一样的环境)
- [[email protected] data]# tar zcf world1.tgz world/
- # 传到测试环境
- [[email protected] data]# scp world1.tgz 172.16.1.52:/application/MySQL/data/
- # 解压
- [[email protected] data]# tar xf world1.tgz
2)启动新数据库
[[email protected] ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
3)登陆数据库查看 r
- # 查看
- MySQL> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | MySQL |
- | performance_schema |
- | test |
- | world |
- +--------------------+
- 5 rows in set (0.00 sec)
4)查询表中数据
- MySQL> select * from city;
- ERROR 1146 (42S02): Table 'world.city' doesn't exist (表不存在)
5)找到以前的表结构在新库中创建表
- MySQL> show create table world.city;
- # 删掉外键创建语句
- CREATE TABLE `city1` (
- `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`)
- #CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
6)删除表空间文件
- # 删除表空间文件
- MySQL> alter table city1 discard tablespace;
- # 在表的物理结构查看
- [[email protected] world]# ll
- total 1000
- -rw-rw---- 1 MySQL MySQL 8710 Nov 4 10:26 city1.frm
- -rw-rw---- 1 MySQL MySQL 8710 Nov 4 10:26 city.frm
- -rw-rw---- 1 MySQL MySQL 589824 Nov 4 10:26 city.ibd
7)拷贝旧表空间文件
[[email protected] world1]# cp city.ibd city1.ibd
8)授权
[[email protected] world]# chown -R MySQL.MySQL *
9)导入表空间
- # 查看, 会报错
- MySQL> select * from city1;
- # 导入表空间
- MySQL> alter table city_new import tablespace;
- # 再次查看
- MySQL> select * from city1;
10) 改表名
- # 改表名
- MySQL> alter table city1 rename city;
- # 再次查看
- MySQL> show tables;
- +------------------+
- | Tables_in_world1 |
- +------------------+
- | city |
- | country |
- | countrylanguage |
- +------------------+
- 3 rows in set (0.00 sec)
来源: http://www.bubuko.com/infodetail-3282265.html