跳转至

存储引擎

存储引擎:

  • mysql的文件系统

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;

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存储引擎简介

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

存储引擎相关命令

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;

企业案例(fake)

项目背景
公司原有的架构：一个展示型的网站，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支持非常完善了。
执行计划
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版本中默认的是独立表空间
共享表空间（ibdata1）
存储的内容
1.系统数据
2.undo          redo、undo日志，事务日志
3.临时表
概念
1.优点：
    可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。

2.缺点：
    所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
配置共享表空间
[root@db03 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;tmp/ibdata2:50M:autoextend
查看共享表空间
mysql> show variables like '%path%';
+----------------------------------+----------------------------------------+
| Variable_name                    | Value                                  |
+----------------------------------+----------------------------------------+
| innodb_data_file_path            | ibdata1:76M;tmp/ibdata2:50M:autoextend |

独立表空间

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

1.优点：
    1）每个表都有自己独立的表空间
    2）数据分开存储
2.缺点：
    1）单表空间不能过大，不能大于100G
查看独立表空间
#物理查看
[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

企业案例

模拟断电表损坏
#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
修复数据库表
#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核心特性-事务

什么是事务？
主要针对DML语句（update，delete，insert）
1.一组数据操作执行步骤，这些步骤被视为一个工作单元:
    1）用于对多个语句进行分组
    2）可以在多个客户机并发访问同一个表中的数据时使用
2.所有步骤都成功或都失败
    1）如果所有步骤正常，则执行
    2）如果步骤出现错误或不完整，则取消
事务的通俗理解
1.我们理解的“交易”是什么？
    1）物与物的交换（古代）
    2）货币现金与实物的交换（现代1）
    3）虚拟货币与实物的交换（现代2）
    4）虚拟货币与虚拟实物交换（现代3）

2.数据库中的“交易”是什么？
    1）事务又是如何保证“交易”的“和谐”？
    2）ACID
事务的演示
成功事务
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;
事务回滚
mysql> begin;
mysql> update stu set name='zhang3';
mysql> delete from stu;
mysql> rollback;

事务的特性（ACID）
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。

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

Isolated（隔离性）
事务之间不相互影响。

Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
事务的控制语句
START TRANSACTION（或 BEGIN）：显式开始一个新事务
SAVEPOINT：分配事务过程中的一个位置，以供将来引用
COMMIT：永久记录当前事务所做的更改
ROLLBACK：取消当前事务所做的更改
ROLLBACK TO SAVEPOINT：取消在 savepoint 之后执行的更改
RELEASE SAVEPOINT：删除 savepoint 标识符
SET AUTOCOMMIT：为当前连接禁用或启用默认 autocommit 模式
自动提交
#临时设置关闭
mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)
#永久设置关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
事务周期
一个成功事务的生命周期
begin;
sql1
sql2
sql3
...
commit;
一个失败事务的生命周期
begin;
sql1
sql2
sql3
...
rollback;
事务隐式提交情况
自动提交
1.现在版本在开启事务时，不需要手工begin，只要你输入的是DML语句，就会自动开启事务。
2.有些情况下事务会被隐式提交
隐式提交
1.在事务运行期间，手工执行begin的时候会自动提交上个事务
2.在事务运行期间，加入DDL、DCL操作会自动提交上个事务
3.在事务运行期间，执行锁定语句（lock tables、unlock tables）
4.load data infile导出数据成一个文件
5.在autocommit=1的时候