• 生活的道路一旦选定,就要勇敢地走到底,决不回头。——左拉
  • 坚强的信心,能使平凡的人做出惊人的事业。——马尔顿
  • 人不可有傲气,但不可无傲骨。 --徐悲鸿
  • 古之立大志者,不惟有超世之才,亦必有坚韧不拔之志。 --苏轼
  • 时间像海绵里的水,只要你愿意挤,总还是有的。 --鲁迅

MySQL的存储引擎

MySQL zkinogg 1年前 (2020-08-09) 145次浏览 0个评论

一、存储引擎

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


#1. InnoDB 支持事务,MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一.

#2. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;  

#3. InnoDB 是聚集索引,MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 

#4. InnoDB 不保存表的具体行数,执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;    

#5. InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;

3.第三方存储引擎

PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB

第三方的存储引擎:
RocksDB MyRocks TokuDB
压缩比较高,数据的插入性能高.其他功能和InnoDB没差.

二、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

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将数据迁移过程中新生成的数据也倒入新库

4.企业案例2

InnoDB个MyISAM存储引擎的替换(客户案例)
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.

问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数


平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工truncate表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式
定期进行碎片整理

三.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的时候

极客公园 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL的存储引擎
喜欢 (0)
[17551054905]
分享 (0)

您必须 登录 才能发表评论!