MySQL存储引擎

一、存储引擎

mysql的文件系统

1.mysql提供的存储引擎

01)InnoDB
	适合数据增删改查
02)MyISAM
	存储一些只读的数据
03)MEMORY
	支持hash索引
04)ARCHIVE
05)FEDERATED
06)EXAMPLE
07)BLACKHOLE
08)MERGE
09)NDBCLUSTER
10)CSV

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

#查看存储引擎
mysql> show engines;

2.innodb和myisam存储引擎的物理区别

#myisam存储引擎
-rw-rw---- 1 mysql mysql  10684 7月   9 15:10 user.frm		#表结构
-rw-rw---- 1 mysql mysql    980 7月  15 09:14 user.MYD		#用户和密码
-rw-rw---- 1 mysql mysql   2048 7月  15 09:28 user.MYI		#用户

#innodb存储引擎
-rw-rw---- 1 mysql mysql   8710 7月  17 10:59 city.frm		#表结构
-rw-rw---- 1 mysql mysql 950272 7月  17 10:59 city.ibd		#表数据

#查看时用strings

二、innodb存储引擎简介

1.innodb核心特性

# 什么叫做行级锁
行级锁只对用户正在访问的行进行锁定。 
如果该用户正在修改某行,那么其他用户就可以更新同一表中该行之外的数据。
行级锁是一种排他锁,防止其他事务修改此行,但是不会阻止读取此行的操作。

MVCC
事务
行级锁			#innodb支持行级锁,myiasm支持表级锁
热备份			#innodb支持热备,myisam不支持热备
自动故障恢复 Crash Safe Recovery

2.存储引擎相关命令

1)查看当前数据库配置的存储引擎

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

2)查看表的存储引擎

#查看哪些表时innodb存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='innodb';

#查看哪些表时myisam存储引擎
mysql> select TABLE_SCHEMA,TABLE_Name,ENGINE from tables where ENGINE='myisam';

#查看表信息
mysql> select * from information_schema.tables where table_name='test11'\G
#查看指定表的存储引擎
mysql> show create table city;

#查看列信息
mysql> select * from COLUMNS where COLUMN_NAME='name'\G

3)修改存储引擎

#临时设置
mysql> set @@default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

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

#建表时指定存储引擎
mysql> create table innodb(id int) ENGINE=innodb;

3.企业案例

1)项目背景

公司原有的架构:一个展示型的网站,LAMT,MySQL5.1.77版本(MYISAM),50M数据量。

2)升级原因

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

3)解决方案

1.提建议将现有的MYISAM引擎替换为Innodb,将版本替换为5.6.38
	1)如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。
	2)5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。

4)执行计划

1.准备一台新的数据库,版本为5.6.38
2.就数据库备份数据
	[root@db01 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B >/tmp/full.sql
3.将备份的数据库传到新数据库
	scp、rsync、NFS、导出导入、sftp
4.修改sql中的存储引擎
	sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
	mysql < /tmp/full.sql
	source
	\.
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据也倒入新库

三.Innodb存储引擎-表空间介绍

5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
5.6版本中默认的是独立表空间

1.共享表空间(ibdata1)

1)存储的内容

1.系统数据
2.undo			redo、undo日志,事务日志
3.临时表

2)概念

1.优点:
	可以将表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。数据和文件放在一起方便管理。

2.缺点:
	所有的数据和索引存放到一个文件中,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。

3)配置共享表空间

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

4)查看共享表空间

mysql> show variables like '%path%';
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| innodb_data_file_path            | ibdata1:76M;tmp/ibdata2:50M:autoextend |

2.独立表空间

1)概念

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

1.优点:
	1)每个表都有自己独立的表空间
	2)数据分开存储
2.缺点:
	1)单表空间不能过大,不能大于100G

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%';
innodb_file_per_table=ON

3.企业案例

1)模拟断电表损坏

#1.将db03上的数据库数据目录下的 world传到一台新数据库的数据目录下
[root@db03 data]# scp -r world 172.16.1.52:/usr/local/mysql/data/

#2.到db02上授权目录
[root@db02 data]# chown -R mysql.mysql world/

#3.登录数据库查看,数据已损坏
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
11 rows in set (0.00 sec)

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

2)修复数据库表

#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`),
  KEY `inx_name` (`Name`),
  KEY `index_popu` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

#2.移除新表的表空间
mysql> alter table city_new discard tablespace;
Query OK, 0 rows affected (0.09 sec)

#3.损坏表空间的数据文件复制给新表
[root@db02 world]# cp city.ibd city_new.ibd
[root@db02 world]# chown -R mysql.mysql city_new.ibd

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

#5.运维修改数据库名
#删除表空间
mysql> drop table city;
ERROR 1051 (42S02): Unknown table 'world.city'
#物理删除表数据
[root@db02 world]# rm city.ibd 
rm: remove regular file ‘city.ibd’? y
#数据库改名
mysql> alter table city_new rename city;
Query OK, 0 rows affected (0.11 sec)

四.Innodb核心特性-事务

1.什么是事务?

主要针对DML语句(update,delete,insert)
1.一组数据操作执行步骤,这些步骤被视为一个工作单元:
	1)用于对多个语句进行分组
	2)可以在多个客户机并发访问同一个表中的数据时使用
2.所有步骤都成功或都失败
	1)如果所有步骤正常,则执行
	2)如果步骤出现错误或不完整,则取消

2.事务的通俗理解

1.我们理解的“交易”是什么?
	1)物与物的交换(古代)
	2)货币现金与实物的交换(现代1)
	3)虚拟货币与实物的交换(现代2)
	4)虚拟货币与虚拟实物交换(现代3)

2.数据库中的“交易”是什么?
	1)事务又是如何保证“交易”的“和谐”?
	2)ACID

3.事务的演示

1)成功事务

mysql> create table stu(id int,name varchar(10),sex enum('f','m'),money int);
mysql> begin;
mysql> insert into stu(id,name,sex,money) values(1,'zhang3','m',100), (2,'zhang4','m',110);
mysql> commit;

2)事务回滚

mysql> begin;
mysql> update stu set name='zhang3';
mysql> delete from stu;
mysql> rollback;

4.事务的特性(ACID)

Atomic(原子性)
所有语句作为一个单元全部成功执行或全部取消。

Consistent(一致性)
如果数据库在事务开始时处于一致状态,则在执行该。事务期间将保留一致状态。

Isolated(隔离性)
事务之间不相互影响。

Durable(持久性)
事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

5.事务的控制语句

START TRANSACTION(或 BEGIN):显式开始一个新事务
SAVEPOINT:分配事务过程中的一个位置,以供将来引用
COMMIT:永久记录当前事务所做的更改
ROLLBACK:取消当前事务所做的更改
ROLLBACK TO SAVEPOINT:取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT:删除 savepoint 标识符
SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit 模式

6.自动提交

#临时设置关闭
mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)

#永久设置关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0

7.事务周期

1)一个成功事务的生命周期

begin;
sql1
sql2
sql3
...
commit;

2)一个失败事务的生命周期

begin;
sql1
sql2
sql3
...
rollback;

8.事务隐式提交情况

1)自动提交

1.现在版本在开启事务时,不需要手工begin,只要你输入的是DML语句,就会自动开启事务。
2.有些情况下事务会被隐式提交

2)隐式提交

1.在事务运行期间,手工执行begin的时候会自动提交上个事务
2.在事务运行期间,加入DDL、DCL操作会自动提交上个事务
3.在事务运行期间,执行锁定语句(lock tables、unlock tables)
4.load data infile导出数据成一个文件
5.在autocommit=1的时候
posted @ 2020-07-20 15:22  元气少女郭德纲!!  阅读(173)  评论(0编辑  收藏  举报