Mysql--存储引擎（mysql存储引擎常用的有）

Mysql--存储引擎（mysql存储引擎常用的有）

一、存储引擎简介

1.文件系统：

1.1 操作系统组织和存取数据的一种机制。

1.2 文件系统是一种软件。

2.文件系统类型：ext2 3 4 ，xfs 数据

2.1 不管使用什么文件系统，数据内容不会变化

2.2 不同的是，存储空间、大小、速度。

3.MySQL引擎：

可以理解为，MySQL的“文件系统”，只不过功能更加强大。

4.MySQL引擎功能：

除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能

总之，存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。

二.MySQL自带的存储引擎类型

MySQL 提供以下存储引擎:

①InnoDB

②MyISAM

③MEMORY

④ARCHIVE

⑤FEDERATED

⑥EXAMPLE

⑦BLACKHOLE

⑧MERGE

⑨NDBCLUSTER

⑩CSV

还可以使用第三方存储引擎:

①MySQL当中插件式的存储引擎类型

②MySQL的两个分支

③perconaDB

④mariaDB

#查看当前MySQL支持的存储引擎类型

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

#查看innodb的表有哪些

mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';

+--------------+----------------------+--------+

| table_schema | table_name | engine |

+--------------+----------------------+--------+

| mysql | innodb_index_stats | InnoDB |

| mysql | innodb_table_stats | InnoDB |

| mysql | slave_master_info | InnoDB |

| mysql | slave_relay_log_info | InnoDB |

| mysql | slave_worker_info | InnoDB |

+--------------+----------------------+--------+

20 rows in set (0.03 sec)

#查看myisam的表有哪些

mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

+--------------------+---------------------------+--------+

| table_schema | table_name | engine |

+--------------------+---------------------------+--------+

| information_schema | COLUMNS | MyISAM |

| information_schema | EVENTS | MyISAM |

| mysql | help_category | MyISAM |

| mysql | ndb_binlog_index | MyISAM |

+--------------------+---------------------------+--------+

33 rows in set (0.01 sec)

1、innodb和myisam的区别

物理上的区别：

#进入mysql目录

[root@db01~l]# cd /application/mysql/data/mysql

#myisam

[root@db01 mysql]# ll user.*

-rw-rw---- 1 mysql mysql 10684 Mar 6 2017 user.frm

-rw-rw---- 1 mysql mysql 960 Aug 14 01:15 user.MYD

-rw-rw---- 1 mysql mysql 2048 Aug 14 01:15 user.MYI

#进入word目录

[root@db01 world]# cd /application/mysql/data/world/

#innodb

[root@db01 world]# ll city.*

-rw-rw---- 1 mysql mysql 8710 Aug 14 16:23 city.frm

-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd

2.innodb存储引擎的简介

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。

优点:

01）事务安全（遵从 ACID）

02）MVCC（Multi-Versioning Concurrency Control，多版本并发控制）

03）InnoDB 行级别锁定

04）Oracle 样式一致非锁定读取

05）表数据进行整理来优化基于主键的查询

06）支持外键引用完整性约束

07）大型数据卷上的最大性能

08）将对表的查询与不同存储引擎混合

09）出现故障后快速自动恢复

10）用于在内存中缓存数据和索引的缓冲区池



innodb核心特性

重点:

MVCC

事务

行级锁

热备份

Crash Safe Recovery（自动故障恢复）

3.查看存储引擎

1）使用 SELECT 确认会话存储引擎

#查询默认存储引擎

mysql> SELECT @@default_storage_engine;

+--------------------------+

| @@default_storage_engine |

+--------------------------+

| InnoDB |

+--------------------------+

1 row in set (0.00 sec)

2）使用 SHOW 确认每个表的存储引擎

#查看表的存储引擎

mysql> show create table city\G

*************************** 1. row ***************************

...

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> show table status like 'city'\G

*************************** 1. row ***************************

Name: city

Engine: InnoDB

...

3）使用 INFORMATION_SCHEMA 确认每个表的存储引擎

#查看表的存储引擎

mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='world'\G

*************************** 1. row ***************************

table_name: city

engine: InnoDB

1 row in set (0.00 sec)

4.存储引擎的设置

1）在启动配置文件中设置服务器存储引擎

#在配置文件的[mysqld]标签下添加/etc/my-f

[mysqld]

default-storage-engine=innodb

2）使用 SET 命令为当前客户机会话设置

#在MySQL命令行中临时设置

mysql> SET @@storage_engine=myisam;

Query OK, 0 rows affected, 1 warning (0.00 sec)

#查看

mysql> select @@default_storage_engine;

+--------------------------+

| @@default_storage_engine |

+--------------------------+

| MyISAM |

+--------------------------+

1 row in set (0.00 sec)

（3）在 CREATE TABLE 语句指定

#建表的时候指定存储引擎

create table t (i INT) engine = ;

#如：建test1表，指定存储引擎为myisam

mysql> create table test1(id int) engine=myisam;

Query OK, 0 rows affected (0.02 sec)

三.真实企业案例

项目背景：

公司原有的架构：一个展示型的网站，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支持非常完善了。

2、实施过程和注意要素

1）备份生产库数据（mysqldump）

[root@db01 test]# mysql -uroot -p1

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| student4 |

| tlbb2 |

+----------------+

2 rows in set (0.00 sec)

#测试环境（先简单创几个存储引擎为myisam）

mysql> create table test1(id int) engine=myisam;

Query OK, 0 rows affected (0.02 sec)

mysql> create table test2(id int) engine=myisam;;

Query OK, 0 rows affected (0.00 sec)

mysql> create table test3(id int) engine=myisam;;

Query OK, 0 rows affected (0.01 sec)

2）准备一个5.6.44版本的新数据库

#先导库，准备一个环境

[root@db01 test]# mysqldump -uroot -p1 -B test >/tmp/full.sql

3）对备份数据进行处理（将engine字段替换）

#方法一

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

#方法二

[root@db01 ~]# vim /tmp/full.sql

:%s#MyISAM#InnoDB#g

4）将修改后的备份恢复到新库

#方法一

[root@db01 test]# mysql -uroot -p123 -h 10.0.0.52 < /tmp/full.sql

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

#连接

[root@db02 ~]# mysql -uroot -p123

#查看存储引擎

mysql> select @@default_storage_engine;

+--------------------------+

| @@default_storage_engine |

+--------------------------+

| InnoDB |

+--------------------------+

1 row in set (0.00 sec)

6）停应用，将备份之后的生产库发生的新变化，补偿到新库

7）应用割接到新数据库

项目结果：

*解决了”小问题” *

四.Innodb存储引擎——表空间介绍

5.5版本以后出现共享表空间概念

表空间的管理模式的出现是为了数据库的存储更容易扩展

5.6版本中默认的是独立表空间

1、共享表空间

1）查看共享表空间

#物理查看

[root@db01 ~]# ll /application/mysql/data/

-rw-rw---- 1 mysql mysql 79691776 Aug 14 16:23 ibdata1

#命令行查看

mysql> show variables like '%path%';

+-----------------------+------------------------------------+

| Variable_name | Value |

+-----------------------+------------------------------------+

| innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |

| ssl_capath | |

| ssl_crlpath | |

+-----------------------+------------------------------------+

3 rows in set (0.00 sec)

#查看大小

[root@db01 data]# du -sh ibdata1

76M ibdata1

5.6版本中默认存储:

①系统数据

②undo

③临时表

5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置

2）设置方法

#编辑配置文件

[root@db01 ~]# vim /etc/my-f

[mysqld]

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

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%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.01 sec)

企业案例

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

1）拷贝库目录到新库中（先准备一个和原表结构一样的环境）

[root@db01 data]# tar zcf world1.tgz world/

#传到测试环境

[root@db01 data]# scp world1.tgz 172.16.1.52:/application/mysql/data/

#解压

[root@db02 data]# tar xf world1.tgz

2）启动新数据库

[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my-f &

3）登陆数据库查看r

#查看

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| world |

+--------------------+

5 rows in set (0.00 sec)

4）查询表中数据

mysql> select * from city;

ERROR 1146 (42S02): Table 'world.city' doesn't exist （表不存在）

5）找到以前的表结构在新库中创建表

mysql> show create table world.city;

#删掉外键创建语句

CREATE TABLE `city1` (

`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`)

#CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

6）删除表空间文件

#删除表空间文件

mysql> alter table city1 discard tablespace;

#在表的物理结构查看

[root@db02 world]# ll

total 1000

-rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city1.frm

-rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city.frm

-rw-rw---- 1 mysql mysql 589824 Nov 4 10:26 city.ibd

7）拷贝旧表空间文件

[root@db02 world1]# cp city.ibd city1.ibd

8）授权

[root@db01 world]# chown -R mysql.mysql *

9）导入表空间

#查看，会报错

mysql> select * from city1;

#导入表空间

mysql> alter table city_new import tablespace;

#再次查看

mysql> select * from city1;

改表名

#改表名

mysql> alter table city1 rename city;

#再次查看

mysql> show tables;

+------------------+

| Tables_in_world1 |

+------------------+

| city |

| country |

| countrylanguage |

+------------------+

3 rows in set (0.00 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。