DBAmysql数据存储引擎_万金油_新浪博客

数据存储引擎：

定义：什么是数据存储引擎

作为插拔式的插件提供，是数据库 mysql 服务软件自带功能程序，是用来处理表的处理器。

每一种的存储引擎都有不同的功能和数据存储方式

1.1查看当前数据库服务，支持的存储以

mysql> show engines;

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

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

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

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

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

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

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

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

9 rows in set (0.00 sec)

1.2修改数据库默认的数据存储引擎

[root@host50 ~]# vim   /etc/my.cnf

[mysqld]

default-storage-engine=myisam

:wd

[root@host50 ~]# 

[root@host50 ~]# systemctl  stop mysqld

[root@host50 ~]# systemctl  restart  mysqld

[root@host50 ~]# mysql  -uroot -p123456

mysql> show engines;

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

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

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

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

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

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

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |

| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

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

9 rows in set (0.00 sec)

DEFAULT 是默认数据引擎的意思

1.3修改表的存储引擎：

alter table  表名 engine=存储引擎名；

1.4建表的时候一起创建：

create  table 表名 engine=存储引擎名；

create  table t2(id int) engine=存储引擎名;

mysql> create table t2(id int) engine=memory;

Query OK, 0 rows affected (0.03 sec)

mysql> create table t3(id int) engine=innodb;

Query OK, 0 rows affected (0.18 sec)

1.5查看存储引擎：

show create table 表名；

mysql> show  create table t1;

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

| Table | Create Table                                                                           |

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

| t1    | CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

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

1 row in set (0.01 sec)

 Transactions事务的意思

InnoDB  默认使用的数据存储引擎

工作中建表时如何决定表使用存储引擎？

常用存储引擎的特点？（myisam innodb)

myisam  支持表级锁

（客户端连接数据库服务器后，对表的数据做访问时，若表的存储引擎是innodb 的话，会对表中被访问行加锁）

不支持事务和事务回滚

锁类型——这个是mysql自带的，是由存储引擎决定的。

读锁：（共享锁）当一张表进行查询（select)操作时，会加读锁——数据不会改变

写锁：(排他锁或互斥锁）当一张表进行执行写(insert,update, delete)操作时，会加写锁

insert update delete 写锁

mysql >lock  tables 表名   read|write

unlock tables 表名；

执行写操作多的表适合使用innodb 存储引擎，这样可以并发访问量；

执行查询操作多的表适合使用myissam 存储引擎，这样可以节省存储空间；

每个表对应3个表文件

表.frm  表结构数据

表.MYD  表数据

表.MYI 表的index的索引信息

 innodb的特点？

支持行级锁

支持外键  事务和事务回滚

每个表对应2个表文件

表.frm   表结构数据

表.idb 表数据和表的index的索引信息

事务Transactions ：一次sql 操作从建立连接到操作完成断开连接的访问过程叫做事务

支持事务的可以做事务回滚：一次sql 操作有任意一步没有执行成功会恢复所有操作。（对innodb表存储的表访问时必须任意一步都要成功，才能完成操作）

典型的事务操作类型是：银行的转账和汇款业务

innodb存储引擎的表有对应的事务文件记录所有的SQL的命令

 [root@host50 ~]# cd /var/lib/mysql

[root@host50 mysql]# ls

ib_buffer_pool——记录成功的执行的记录

ib_logfile0

ib_logfile1——记录没有执行成功记录

——不能用系统命令查看，是用二进制的方式记录

myisam的就不会记录的这几个文件当中

事务的特性：ACID 

原子性：事务的整个操作是一个整体，不可分割，要么全部完成，要么全部失败

隔离性

一致性

持久性

事务的自动提交功能：

mysql> show variables like "autocommit";——查看事务自动提交的开关

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

| Variable_name | Value |

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

| autocommit    | ON    |

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

1 row in set (0.01 sec)

mysql> set autocommit=off;——关闭事务的自动提交功能

Query OK, 0 rows affected (0.00 sec)

mysql>commit;手动提交

mysql> show variables like "autocommit";——查看事务自动提交的开关

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

| Variable_name | Value |

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

| autocommit    | OFF   |

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

1 row in set (0.01 sec)

commit;手动提交

rollback ：回滚——数据回从新恢复

mysql>create database db55;

mysql> create table t5(name char(10));

mysql> insert into  t5 values("bob");

Query OK, 1 row affected (0.00 sec)

mysql> insert into  t5 values("bob");

Query OK, 1 row affected (0.00 sec)

mysql> insert into  t5 values("bob");

Query OK, 1 row affected (0.00 sec)

mysql> insert into  t5 values("bob");

Query OK, 1 row affected (0.00 sec)

mysql> insert into  t5 values("bob");

Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;

+------+

| name |

+------+

| bob  |

| bob  |

| bob  |

| bob  |

| bob  |

+------+

5 rows in set (0.00 sec)

mysql>  select * from t5;——另一个终端测试的结果

Empty set (0.00 sec)

mysql> commit;——提交后再去另一个终端检测

Query OK, 0 rows affected (0.03 sec)

再一次删除， 再回滚（rollback）进行实验