MySQL存储引擎

存储引擎是什么?

show engines\G  #查看所有引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据

MyISAM引擎

mysam： 拥有较高的插入，查询速度，但不支持事务

mysql> create table t4(id int,name char(10)) engine=myisam;

MRG_MYISAM引擎(只能基于MYISAM引擎)

create table t10(id int,name char(10)) engine=myisam; 创建基表t10

create table t20(id int,name char(10)) engine=myisam; 创建基表t20

create table t30(id int,name char(10)) union=(t10,t20) engine=mrg_myisam; MRG表T30

create table t40(id int,name char(10)) union=(t10,t20) INSERT_METHOD=first engine=mrg_myisam; MRG表指定插入表

MEMORY引擎

所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

mysql> create table t3(id int,name char(10)) engine=memory;

BLACKHOLE引擎（黑洞引擎）

黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继

mysql> create table t3(id int,name char(10)) engine=blackhole;

INNODB引擎

5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定

1.外键

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。

 外键的使用条件：
　　　 1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
　　　2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立； 
　　　3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

create table dpt(did int primary key,dname char(10));

create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) );



insert into emp values(1,'tong',10);

insert into emp values(2,'tom',30);



alter table emp drop foreign key emp_ibfk_1;

alter table emp add foreign key (did) references dpt(did);

alter table emp add CONSTRAINT emp_dpt_fk foreign key (did) references dpt(did);

级联删除,级联更新

mysql> create table emp(id int,name char(10),did int,foreign key (did) references dpt(did) ON DELETE CASCADE on update cascade);

2.表级锁和行级锁

行级锁：对正在被修改的行进行锁定，其它的用户可以访问被锁定的行以外的行。表级锁：锁定整个表，限其他用户对表的访问

3.事物的支持

事务处理是一种机制,用来管理成批执行的sql语句,以保证数据库不包含不完整的操作结果,他们或者为整体执行完成,或者完全不执行(如果没有错误发生整组语句提交到数据库,如果发生错误,则进行回退,以保证数据的安全)

1·必须是innodb引擎

2事物针对会话生效(只在当前会话生效)

3事物的结束要么commit要么rollback

4事物是一次性的,只要commit或者rollback,在使用事物时需要重启开启

5·事物只针对DML语句,如遇到DDL DCL自动commit6事物中可以使用savepoint,可以根据savepoint回滚,但不结束事物

7.savepoint只能往后回滚,不能往前

事务 transaction 指定一组sql语句

回退 rollback 撤销指定的sql语句(只能回退insert delete update语句)

提交 commit 提交未存储的sql语句

保留点 savepoint 事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)

create table t11(id int,name char(10)) engine=innodb;

mysql> insert into t11 values(1,'tong')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t11 values(1,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t11 values(3,'jerry');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
+------+-------+
3 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t11;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t11;
Empty set (0.00 sec)

mysql> rollback
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
+------+-------+
3 rows in set (0.00 sec)

提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(2,'dog');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
+------+-------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

保留点

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(2,'dog');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
+------+-------+
4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t11 values(5,'haha');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
|    5 | haha  |
+------+-------+
5 rows in set (0.00 sec)

mysql> delete from t11 where id=1;
Query OK, 2 rows affected (0.00 sec)

mysql> savepoint delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    3 | jerry |
|    2 | dog   |
|    5 | haha  |
+------+-------+
3 rows in set (0.00 sec)

mysql> delete from t11;
Query OK, 3 rows affected (0.00 sec)

mysql> savepoint delete2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
Empty set (0.00 sec)


mysql> rollback to delete1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    3 | jerry |
|    2 | dog   |
|    5 | haha  |
+------+-------+
3 rows in set (0.00 sec)


mysql> rollback to insert1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
|    5 | haha  |
+------+-------+
5 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t11;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tong  |
|    1 | tom   |
|    3 | jerry |
|    2 | dog   |
+------+-------+
4 rows in set (0.00 sec)

savepoint查询 只能查有几个savepoint

[root@mail db]# vim /etc/my.cnf

[mysqld]

show_compatibility_56 = 1



[root@mail db]# systemctl restart mysqldd


mysql> select * from information_schema.SESSION_STATUS where VARIABLE_NAME like "%POINT%";
+----------------------------+----------------+
| VARIABLE_NAME              | VARIABLE_VALUE |
+----------------------------+----------------+
| COM_RELEASE_SAVEPOINT      | 0              |
| COM_ROLLBACK_TO_SAVEPOINT  | 0              |
| COM_SAVEPOINT              | 0              |
| HANDLER_SAVEPOINT          | 0              |
| HANDLER_SAVEPOINT_ROLLBACK | 0              |
+----------------------------+----------------+
5 rows in set, 1 warning (0.00 sec)

mysql>

 

mysql> select @@autocommit; #查询

当autocommit=0时 mysql默认开启事务:

当autocommit=1时 mysql默认关闭事务,需要手动开启 start transaction 默认1

mysql> set autocommit=0; #设置

永久生效,修改配置文件

[mysqld]

autocommit=0 #设置默认开启事物

skip-grant-tables #重置密码

socket=/var/lib/mysql/mysql.sock #套接字文件位置 必须是mysql可用创建文件的目录







[mysqld] 
socket=/var/lib/mysql/mysql.sock 

[mysql] 
socket=/var/lib/mysql/mysql.sock 
user=root 
password=123

Innodb数据文件的物理架构

emp.frm 表结构文件

emp.ibd 独立表空间(独立数据文件)

 

mysql的配置文件:/etc/my.cnf /usr/my.cnf /usr/local/mysql/my.cnf

独占表空间和共享表空间

innodb_file_per_table=1 独立

innodb_file_per_table=0 共享

mysql视图

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。

单表视图

mysql> create view new_emp as select * from emp;

mysql> create view new_emp1 as select emp_id,emp_name,emp_money from emp;
Query OK, 0 rows affected (0.00 sec)

修改视图=修改源表

多表视图

create table tt1(id int,name char(10),did int);

create table tt2(did int,dname char(10));


mysql> create view emp_dpt_v as select emp_id,emp_name,dpt_name from emp,dpt where emp.dpt_id=dpt.dpt_id;


DROP view emp_dpt_v;

MyISAM

MyISAM是默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。（注意MySQL 5.1不支持ISAM）。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名 为.MYD （MYData）。索引文件的扩展名是.MYI （MYIndex）。 MyISAM文件的格式是平台无关的，这意味着你可以将数据和索引文件从一个intel服务器上拷贝到一台PowerPC或者Sun SPARC上，而不会出任何问题。

主要区别：

　　MyISAM是非事务安全型的，而InnoDB是事务安全型的。

　　MyISAM锁的粒度是表级，而InnoDB支持行级锁定。

　　MyISAM支持全文类型索引，而InnoDB不支持全文索引。

　　MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

　　MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

　　InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表(alter table tablename engine=innodb)。