第七章· MySQL的存储引擎

Posted by

一:存储引擎

1.如何查看MySQL中支持的存储引擎

#查看当前MySQL支持的存储引擎类型
mysql> show engines;

#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';

#查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

2.innodb

在MySQL5.5版本之后,默认的存储引擎,提供高可靠性和高性能。


innodb核心特点

MVCC
事务
行级锁
热备份
Crash Safe Recovery(自动故障恢复)

3.查看存储引擎

1)使用 SELECT 确认会话存储引擎

mysql> select @@default_storage_engine;

2)使用 SHOW 确认每个表的存储引擎

mysql> show create table world.city;
mysql> show table status like 'city'\G

3)使用 INFORMATION_SCHEMA 确认每个表的存储引擎

select table_name,engine from information_schema.tables where table_name=’查询表名’ and table_schema=’表所在库名\G

mysql> SELECT TABLE_NAME,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='city' AND TABLE_SCHEMA='world'\G

企业案例:

背景:公司原有架构:一个展示型的LNMP架构网站,MySQL5.1.77版本,默认存储引擎为myisam,50G数据存储量

​ 问题:

​ 1.)myisam是表级锁,表级锁在对表中任一行进行数据修改操作时,会锁定整张表,使表其它行也被锁定,不能同时进行操作

​ 2.)myisam不支持故障自动恢复:当断电时有可能会出现数据损坏或丢失的问题。

​ 解决:提议将现有的myisam引擎替换为innodb,并将MySQL版本升级到5.6.38以上

​ 1)如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。

​ 2)5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。(5.7版本因为加入了其它功能可用会出现不兼容)

​ 3)测试(必须要做),上线提供服务

环境

#建立一个数据库,并建议一个myisam存储引擎的表
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`),
  KEY `idx_name` (`Name`)
) ENGINE=myisam AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 ;

#查看表的存储引擎
mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='engine'\G
*************************** 1. row ***************************
table_name: city
    engine: MyISAM
1 row in set (0.00 sec)

解决方案:

#导出数据库
[root@db01 ~]# mysqldump -uroot -p3308 -S /data/3308/data/mysql.sock engine city > /tmp/city.sql

#修改库文件的存储引擎
[root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/city.sql

#创建相同的数据库,并进入
mysql> create database engine;
mysql> use engine

#导入数据文件
mysql> source /tmp/city.sql

#查看表的存储引擎,此时已修改为InnoDB
mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='engine'\G
*************************** 1. row ***************************
table_name: city
    engine: InnoDB
1 row in set (0.00 sec)

注意:

​ MySQL系统库使用的是MyISAM存储引擎,我们在对数据库导出修改存储引擎是,一定不要使用 A 参数去一次性导出所有库,然后修改全部的存储引擎,如修改了系统的库的存储引擎可能会导致系统不能正常运行

三:表空间(存放MySQL所有数据):表空间的管理模式的出现是为了数据库的存储更容易扩展

​ 1.)共享表空间

​ 2.)独立表空间

1.共享表空间

1)查看共享表空间

[root@db01 mysql]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Mar 22 19:59 /application/mysql/data/ibdata1

#SQL查看
mysql> show variables like '%path%';
innodb_data_file_path ibdata1:12M:autoextend
共享表空间   表空间名:默认大小:自动扩容

共享表空间存储:

​ 临时表

​ 系统数据

​ undo(事务日志)

2)扩展多个共享表空间

[root@db01 data]# du -sh ibdata1
76M ibdata1

[root@db01 data]# vim /etc/my.cnf
[mysqld]
******
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
******

[root@db01 data]# /etc/init.d/mysqld restart 
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 

[root@db01 data]# ll -h ./ibdata*
-rw-rw---- 1 mysql mysql 76M Mar 23 00:09 ./ibdata1
-rw-rw---- 1 mysql mysql 50M Mar 23 00:09 ./ibdata2

注意:

​ 在my.cnf中修改配置文件添加多个共享表空间时,首先需要计算出当前的共享表空间的大小

​ innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

​ 当添加该配置文件后,ibdata1将不再使用,之后会使用ibdata2并初始给其50M空间,并且会自动扩张,随着不断使用大小不断增大

​ 添加时注意,配置文件中前面固定的表空间大小需跟数据目录中的实际表空间大小相一致,否则MySQL重启失败

为什么要做多个表空间:

​ 随着MySQL的不断使用表空间也在不断内存也在扩大,当内容太大影响磁盘性能,这时就对表空间做切割处理,这时前面的表空间大小固定,最后添加的新的表空间再给其初始容量并让其自动扩容。就如同给日志文件做切割。

2.独立表空间

1)查看独立表空间

[root@db01 world]# ll /application/mysql/data/world/
******
-rw-rw---- 1 mysql mysql 884736 Mar 22 04:28 city.ibd
******

#SQL查看
mysql> show variables like '%per_table%';
innodb_file_per_table  ON 
独立表空间       开启

独立表空间

​ myisam没有独立表空间

​ 对用户自主创建的表,会采用此种模式,每个表有一个独立的表空间进行管理

企业案例

在没有备份数据的情况下,突然断电导致表损坏,打不开数据库

环境模拟:

[root@db01 data]# pwd
/data/3307/data
#复制库目录到新库中
[root@db01 data]# cp -r /application/mysql/data/world/ ./

#授权
[root@db01 data]# chown mysql.mysql world/

#启动数据库
[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &

#登录数据库查看
mysql> show databases;
mysql> use world
mysql> show tables;

#查看表中数据,此时会出现报错(模拟成表结构损坏导致无法正常访问数据)
mysql> select * from city;
ERROR 1017 (HY000): Can't find file: './world/city.frm' (errno: 13 - Permission denied)


解决思路:

方法一:用新库作为提供服务

​ 1.向开发要建表语句

​ 2.在新环境数据库创建新库,拿建表语句建表

​ 3.删除建表时产生的表空间文件(此时的表空间并没有数据)

​ 4.把故障数据库中的库的表空间数据复制到新库

​ 5.导入表空间

方法二:解决故障库的表结构损坏问题

​ 1.准备新环境,在新库上恢复数据

​ 2.导出数据库,再导回故障的数据库

建议:建议使用第一种方法,节省恢复数据,提供服务所需的时间

恢复方案一:

#这里在以前的表结构中查找表结构,生产中可以向开发要
mysql> show create table city;

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`),
  KEY `idx_name` (`Name`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 

#创建相同的数据库
mysql> create database world;
#进入数据库
mysql> use world;
#用获取的表结构创建相同的表
mysql> 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`),
    ->   KEY `idx_name` (`Name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 ;
 #查看数据目录,此时有表空间   
[root@db01 world]# cd /data/3308/data/world/
[root@db01 world]# ll
total 208
-rw-rw---- 1 mysql mysql   8710 Mar 23 04:46 city.frm
-rw-rw---- 1 mysql mysql 147456 Mar 23 04:46 city.ibd
-rw-rw---- 1 mysql mysql     61 Mar 23 04:45 db.opt

#删除创建目录时创建的表空间,此时的表空间并没有数据
mysql> alter table world.city discard tablespace;

#此时数据目录中表空间不存在
[root@db01 world]# ll
total 16
-rw-rw---- 1 mysql mysql 8710 Mar 23 04:46 city.frm
-rw-rw---- 1 mysql mysql   61 Mar 23 04:45 db.opt

#从故障库主机中的数据目录拷贝需要的表空间
[root@db01 world]# cp -a /data/3307/data/world/city.ibd ./
[root@db01 world]# ll
total 880
-rw-rw---- 1 mysql mysql   8710 Mar 23 04:46 city.frm
-rw-r----- 1 mysql mysql 884736 Mar 23 01:15 city.ibd
-rw-rw---- 1 mysql mysql     61 Mar 23 04:45 db.opt

#导入从故障库拷贝过来的表空间
mysql> alter table city import tablespace;

#此时查看表的数据,数据被恢复成功
mysql> select * from city;


恢复方案二:(在方案完成的基础上,暂停服务的导入数据)

#导出新数据库中修复过的数据文件
[root@db01 ~]# mysqldump -uroot -p3308 -S /data/3308/data/mysql.sock world city > /tmp/city.sql

[root@db01 ~]# vim /tmp/city.sql
#修改表名
:%s#city#city_new#g

#进入故障库
[root@db01 ~]# mysql3307

#导入新库
mysql> source /tmp/city.sql

#删除损坏的表
[root@db01 world]# rm -rf city.frm city.ibd 

#修改表名为之前的表名
mysql> alter table city_new rename city;

#查询数据,恢复成功
mysql> select * from city;