一、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;

二、真实企业案例

1.项目背景

公司原有的架构：一个展示型的网站，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、实施过程和注意要素

2.解决问题

1）准备一个5.6.38版本的新数据库

2）备份数据库数据

[root@db02 mysql]# mysqldump -A -R >/tmp/full.sql

3）批量修改存储引擎

[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#g' /tmp/full.sql

4）将数据推到新数据库

[root@db02 mysql]# scp /tmp/full.sql 10.0.0.51:/tmp/

5）将数据导入新的数据库

mysql> source /tmp/full.sql;

6）应用测试环境连接新库，测试所有功能

7）生产应用割接到新数据库

三、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)

企业案例

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

2）模拟数据库表损坏

#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

3.解决数据表损坏问题

#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;
Query OK, 0 rows affected (0.00 sec)