03Mysql 06 存储引擎

最新推荐文章于 2023-01-16 08:22:11 发布

睡神之首

最新推荐文章于 2023-01-16 08:22:11 发布

阅读量247

点赞数

分类专栏： Linux # Linux 数据库文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/mcywww/article/details/121652066

版权

Linux 同时被 2 个专栏收录

37 篇文章 0 订阅

订阅专栏

Linux 数据库

6 篇文章 0 订阅

订阅专栏

mysql存储引擎

mysql程序结构：
1.连接层：
	验证身份
	提供两种连接方式
	建立与SQL层的交互
2.SQL层:
	接收连接层传过来的sql语句
	验证语法，验证语义
	解析器
	优化器
	执行器
		跟存储引擎层建立交互
	写缓存
	记录日志
3.存储引擎层：
	接收SQL层传来的sql语句
	与磁盘进行交互，获取数据
	跟SQL层建立交互
	
存储引擎就是mysql的文件系统

1.MySQL提供的存储引擎

01）InnoDB
	数据经常添加，查询，修改，删除，使用InnoDB存储引擎（事务），适合涉及钱或者数据准确性高的公司
02）MyISAM
	只读取或者添加，可以使用MyISAM存储引擎
03）MEMORY
	支持hash索引
04）ARCHIVE
05）FEDERATED
06）EXAMPLE
07）BLACKHOLE
08）MERGE
09）NDBCLUSTER
10）CSV

#还可以使用第三方存储引擎:
01）MySQL当中插件式的存储引擎类型
MySQL的两个分支
02）perconaDB
03）mariaDB

#查看当前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';

#查看表的属性
mysql> select * from tables where table_name='city'\G

2.innodb和myisam的物理区别

#myisam存储引擎的文件：
-rw-rw---- 1 mysql mysql  10684 Apr 13 20:27 user.frm			#表结构
-rw-rw---- 1 mysql mysql   1240 Apr 21 15:59 user.MYD			#用户密码
-rw-rw---- 1 mysql mysql   2048 Apr 21 15:59 user.MYI			#完整用户

#innodb存储引擎的文件：
-rw-rw---- 1 mysql mysql   8698 Apr 21 16:03 xuesheng.frm		#表结构
-rw-rw---- 1 mysql mysql  98304 Apr 21 16:07 xuesheng.ibd		#表数据

3. innodb 核心特性

MVCC			#多版本并发控制
事务
行级锁			 #innodb支持行级锁，myisam支持表级锁
热备份			 #innodb支持热备，myisam不支持热备
自动故障恢复（CSR）Crash Safe Recovery

4.存储引擎相关命令

1）查看当前的存储引擎

#查看数据库当前存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

#查看表的存储引擎
mysql> show create table city;
mysql> select * from tables where table_name='city'\G

2）修改默认的存储引擎

#临时修改
mysql> set @@default_storage_engine=myisam;

#永久修改
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
[root@db02 ~]# systemctl restart mysql

3）建表的时候设置存储引擎

mysql> create table lhd(id int) engine=myisam;

Innodb 存储引擎的表空间

1.共享表空间

1）存储的内容

#存储内容：
1.系统数据
2.undo        redo log，undo log 事务的日志
3.临时表

2）查看共享表空间

mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
5 rows in set (0.00 sec)

3）配置共享表空间

#1.编辑配置文件
[root@db02 mysql]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

#2.重启数据库
[root@db01 data]# systemctl restart mysql

#3.启动失败，报错
[root@db02 ~]# /etc/init.d/mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql-5.6.46/data/db02.pid).

#4.看日志
[ERROR] InnoDB: Data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file 3200 pages!

#5.修改配置文件
[root@db02 mysql]# vim /etc/my.cnf
[mysqld]
#修改ibdata1跟实际的ibdata1文件一样大小
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

#6.再次重启
[root@db02 ~]# /etc/init.d/mysqld start

2.独立表空间

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

1）查看独立表空间

#1.物理层面查看独立表空间
[root@db01 lhd]# pwd
/service/mysql/data/lhd
[root@db01 lhd]# ll
total 220
-rw-rw---- 1 mysql mysql    61 Apr 14 14:43 db.opt
-rw-rw---- 1 mysql mysql  8640 Apr 22 12:21 qiudao.frm
-rw-rw---- 1 mysql mysql 98304 Apr 22 12:21 qiudao.ibd
-rw-rw---- 1 mysql mysql  8854 Apr 22 12:21 student.frm
-rw-rw---- 1 mysql mysql 98304 Apr 22 12:21 student.ibd

#2.mysql里查看独立表空间
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

企业案例—数据库版本升级

项目背景

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量

1、小问题不断

1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

2、提出解决方案

1、提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
	1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
	2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
2、实施过程和注意要素
	升级的方法，升级的时间，升级中会出现的问题，升级后出现的问题

解决问题

1、#准备一台新机器，安装mysql-5.6版本
	源码包安装，二进制安装
2、#在旧数据库上备份数据（除了系统库以外的数据）
	mysqldump -uroot -p123 -R --trigers -B world --master-data=2 > /tmp/123.sql
3、#将备份的数据传到新的数据库服务器上
	scp  rsync 硬件设备  NFS
4、#修改存储引擎
	sed -i.bak 's#MyISAM#Innodb#g' /tmp/123.sql
5、#将修改的数据导入新的数据库
	mysql -uroot -p123 < /tmp/123.sql
	source /tmp/123.sql
	\. /tmp/123.sql
6、#将测试环境连接数据库的配置修改为新的地址
	为了测试功能和业务
7、#将测试环境数据清除，切换生产环境连接数据库配置为新的地址
	修改web端代码包的配置文件，修改数据库IP，端口（代码包里面写主机名，去web服务器修改hosts）
8、#通过binlog将升级过程中新写入的数据提取出来备份并导入到新库

企业案例—数据库表损坏

在没有备份数据的情况下，突然断电导致表空间或表结构损坏，打不开数据库。

模拟数据库表损坏

#1.将db01的数据目录下的world目录打包
[root@db01 ~]# cd /service/mysql/data
[root@db01 data]# tar zcf world.tar.gz world

#2.将压缩包拷贝到db04
[root@db01 data]# scp world.tar.gz 10.0.0.54:/tmp

#3.将压缩包解压到db04的mysql数据目录下
[root@db04 ~]# cd /service/mysql/data
[root@db04 data]# tar xf /tmp/world.tar.gz -C ./

#4.登录数据库查看
mysql> show databases;
mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> select * from city;
ERROR 1146 (42S02): Table 'world.city' doesn't exist

解决数据表损坏问题

#1.找到建表语句
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`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

#2.移除新表的表空间内数据
mysql> use world
mysql> alter table city_new discard tablespace;
Query OK, 0 rows affected (0.00 sec)

#3.物理拷贝city的表数据
[root@db04 world]# cp city.ibd city_new.ibd
[root@db04 world]# chown -R mysql.mysql city_new.ibd

#4.新表读取表数据
mysql> select * from city_new;
ERROR 1814 (HY000): Tablespace has been discarded for table 'city_new'
mysql> alter table city_new import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)

#5.查看表数据
mysql> select * from city_new;

#6.物理删除旧的表结构和表数据
[root@db04 world]# rm -rf city.frm city.ibd

#7.修改表名
mysql> alter table city_new rename city;
Qu ery OK, 0 rows affected (0.00 sec)

mysql> rename table city_new to city;

存储引擎总结

1.什么是存储引擎

mysql的文件系统，主要就是与磁盘进行交互

innodb和myisam

2.innodb和myisam的区别

1.InnoDB是行级锁，MyISAM是表级锁
2.InnoDB支持事务，MyISAM不支持事务
3.InnoDB支持CSR，MyISAM不支持CSR

3.表空间

1.共享表空间		ibdata1
	系统数据
	undo log
	临时表
2.独立表空间		.ibd
	用户创建数据库的数据

4.事务的日志

redo log：重做的日志 记录的是数据的变化，帮助在内存里面完成数据的修改
undo log：不做日志	记录的是是否执行了commit，执行数据修改前的状态，为了帮我们回滚数据

5.事务的锁

共享锁：在有事务操作数据时该数据也可以被查询
排它锁：在一个事务修改数据时，该数据无法被其他事物执行
乐观锁：多事务同时操作时，谁先提交以谁修改的数据为准
悲观锁：多事务同时操作时，数据只有一个人可以修改

6.隔离级别

1.RU：未提交读，一个事务还没有提交，其他事务就可以查看已经修改的值
2.RR：可重复读
3.RC：允许查看其他事务已提交的数据
4.串行化：将事务完全隔离

企业案例一：

背景：公司的mysql数据版本为5.1.7，存储引擎为myisam，总出现问题

1.升级数据库方案

#1.提出升级的方案
	升级的方法，升级的时间，升级终会出现的问题，升级后出现的问题
#2.准备一台新机器，安装mysql-5.6版本
	源码包安装，二进制安装
#3.在旧数据库上备份数据（除了系统库以外的数据）
	mysqldump -uroot -p123 -R --trigers -B world --master-data=2 > /tmp/123.sql
#4.将备份的数据传到新的数据库服务器上
	scp  rsync 硬件设备  NFS
#5.修改存储引擎
	sed -i.bak 's#MyISAM#Innodb#g' /tmp/123.sql
#6.将修改的数据导入新的数据库
	mysql -uroot -p123 < /tmp/123.sql
	source /tmp/123.sql
	\. /tmp/123.sql
#7.将测试环境连接数据库的配置修改为新的地址
	为了测试功能和业务
#8.将测试环境数据清除，切换生产环境连接数据库配置为新的地址
	修改web端代码包的配置文件，修改数据库IP，端口（代码包里面写主机名，去web服务器修改hosts）
#9.通过binlog将升级过程中新写入的数据提取出来备份并导入到新库

企业案例二：

背景：数据库突然断电，导致数据库表损坏

1.解决数据库表损坏

#1.找到建表语句
CREATE TABLE `country_new` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` float(3,1) DEFAULT NULL,
  `GNP` float(10,2) DEFAULT NULL,
  `GNPOld` float(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#2.移除新表的表数据
mysql> alter table country_new discard tablespace;
Query OK, 0 rows affected (0.00 sec)

#3.物理拷贝country的表数据
[root@db04 world]# cp -a country.ibd country_new.ibd

#4.新表读取新的表空间
mysql> alter table country_new import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)

#5.物理删除旧的表数据
mysql> drop table country;
ERROR 1051 (42S02): Unknown table 'world.country'
[root@db04 world]# rm country.ibd 

#6.修改表名
mysql> alter table country_new rename country;
Query OK, 0 rows affected (0.00 sec)

睡神之首

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
03Mysql 06 存储引擎

mysql存储引擎mysql程序结构：1.连接层：验证身份提供两种连接方式建立与SQL层的交互2.SQL层: 接收连接层传过来的sql语句验证语法，验证语义解析器优化器执行器跟存储引擎层建立交互写缓存记录日志3.存储引擎层：接收SQL层传来的sql语句与磁盘进行交互，获取数据跟SQL层建立交互存储引擎就是mysql的文件系统1.MySQL提供的存储引擎01）InnoDB 数据经常添加，查询，修改，删除，使用InnoDB存储引擎（事
复制链接

扫一扫

专栏目录