Mysql--存储引擎
< 返回列表时间: 2020-04-27来源:OSCHINA
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里?>>>
一、存储引擎简介



1.文件系统: 1.1 操作系统组织和存取数据的一种机制。 1.2 文件系统是一种软件。
2.文件系统类型:ext2 3 4 ,xfs 数据 2.1 不管使用什么文件系统,数据内容不会变化 2.2 不同的是,存储空间、大小、速度。
3.MySQL引擎: 可以理解为,MySQL的“文件系统”,只不过功能更加强大。
4.MySQL引擎功能: 除了可以提供基本的存取功能,还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
总之,存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。
二MySQL自带的存储引擎类型 MySQL 提供以下存储引擎: ①InnoDB
②MyISAM
③MEMORY
④ARCHIVE
⑤FEDERATED
⑥EXAMPLE
⑦BLACKHOLE
⑧MERGE
⑨NDBCLUSTER
⑩CSV

还可以使用第三方存储引擎: ①MySQL当中插件式的存储引擎类型
②MySQL的两个分支
③perconaDB
④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目录 [root@db01~l]# cd /application/mysql/data/mysql #myisam [root@db01 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目录 [root@db01 world]# cd /application/mysql/data/world/ #innodb [root@db01 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)
[root@db01 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版本的新数据库
#先导库,准备一个环境 [root@db01 test]# mysqldump -uroot -p1 -B test >/tmp/full.sql
3)对备份数据进行处理(将engine字段替换)
#方法一 [root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql #方法二 [root@db01 ~]# vim /tmp/full.sql :%s#MyISAM#InnoDB#g
4)将修改后的备份恢复到新库
#方法一 [root@db01 test]# mysql -uroot -p123 -h 10.0.0.52 < /tmp/full.sql
5)应用测试环境连接新库,测试所有功能
#连接 [root@db02 ~]# 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)查看共享表空间
#物理查看 [root@db01 ~]# 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) #查看大小 [root@db01 data]# du -sh ibdata1 76M ibdata1 5.6版本中默认存储:
①系统数据
②undo
③临时表
5.7版本中默认会将undo和临时表独立出来,5.6版本也可以独立,只不过需要在初始化的时候进行配置
2)设置方法
#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
​​​​​​​​​​​​​​ 2、独立表空间
对于用户自主创建的表,会采用此种模式,每个表由一个独立的表空间进行管理
查看独立表空间
#物理查看 [root@db01 ~]# 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)拷贝库目录到新库中(先准备一个和原表结构一样的环境)
[root@db01 data]# tar zcf world1.tgz world/ #传到测试环境 [root@db01 data]# scp world1.tgz 172.16.1.52:/application/mysql/data/ #解压 [root@db02 data]# tar xf world1.tgz
2)启动新数据库
[root@db01 ~]# 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; #在表的物理结构查看 [root@db02 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)拷贝旧表空间文件
[root@db02 world1]# cp city.ibd city1.ibd
8)授权
[root@db01 world]# chown -R mysql.mysql *
9)导入表空间
#查看,会报错 mysql> select * from city1; #导入表空间 mysql> alter table city_new import tablespace; #再次查看 mysql> select * from city1;
改表名
#改表名 mysql> alter table city1 rename city; #再次查看 mysql> show tables; +------------------+ | Tables_in_world1 | +------------------+ | city | | country | | countrylanguage | +------------------+ 3 rows in set (0.00 sec)

​​​​​​​
热门排行