一、存储引擎
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的时候