MySQL基础day03_存储引擎和外键-MySQL 5.6

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

MySQL基础day03_存储引擎和外键-MySQL 5.6


外键的条件

1,表的存储引擎为innodb存储引擎

2,表中外键字段的类型要与参考表的字段类型一致

3,外键字段要是索引类型中的一种


MySQL数据库的存储引擎

存储引擎:是mysql结构中的组成部分

mysql体系结构:

由8部分组成:连接池、Sql接口、分析器、优化器、缓存和缓冲、存储引擎、管理工具、物理存储设备

1.客户端:mysql

2.连接器: NativeC API 本地C语言接口、JDBCJAVA的数据库连接器、ODBC开放式数据库互联,非常底层 Oracle,SOLServer都支持

3.连接池(conetcionpool):它的后方是MYSQL的真正功能

4.SQL接口:用户通过sql客户端发过来的命令,由sql接口接收(DML数据操作语言:查询,修改,升级数据等DDL数据定义语言:创建一个新的数据库,新的索引,删除一个用户等存储过程视图触发器)

5.Parser:分析器(查询 事务 对象权限)

6.optimizer:(访问路径 、 生成执行树)

7.caches&buffers:缓存和缓存 (优化里最重要的是缓存的优化)

8.pluggablestroage  engines存储引擎(插件式):将逻辑结构转换为物理结构的程序

9.ManagementServices  & Utilites工具:备份,恢复,安全,移植,集群等,这些工具一般和文件系统打交道,不需要和mysql-server打交道,它们对应的都是命令。


查看当前mysql服务器所支持的存储引擎:show engines;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show engines;
+ --------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+ --------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance  Schema                                              NO            NO    NO          |
| CSV                | YES     | CSV storage engine                                             |  NO            NO    NO          |
| MRG_MYISAM         | YES     | Collection  of  identical MyISAM 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          |
| MEMORY             | YES     | Hash based, stored  in  memory, useful  for  temporary  tables      |  NO            NO    NO          |
| ARCHIVE            | YES     | Archive storage engine                                         |  NO            NO    NO          |
| InnoDB             |  DEFAULT  | Supports transactions, row- level  locking,  and  foreign  keys     | YES          | YES  | YES        |
| FEDERATED          |  NO       | Federated MySQL storage engine                                 |  NULL          NULL  NULL        |
+ --------------------+---------+----------------------------------------------------------------+--------------+------+------------+
rows  in  set  (0.00 sec)
mysql>



指定存储引擎:

1,建表时指定存储引擎

create table 表名(字段列表)engine=存储引擎名;

注:在指定之前需要用showengine;查看所支持的引擎,Support列为是否支持!

2,建表之后修改使用的存储引擎,建表时没有指定则为默认的:

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


查看一个表的存储引擎:(即查看表的创建过程)

show create table 表名;


存放表结构的文件为/var/lib/mysql/库名/表名.frm

存放表数据的文件为/var/lib/mysql/库名/表名.ibd


事务:对表中数据的一次操作,从开始到结束的过程称作事务;

事务回滚:如果操作没有成功,则恢复到之前的状态;

InnoDB支持事务回滚;


锁机制:(解决多个用户对数据库记录并发访问问题)

所得类型:读锁、写锁

读锁(共享锁):不会引起数据内容的改变;

写锁(互斥锁/排它锁),锁定的程度为锁粒度;

注:写锁的优先级高于读锁;


锁粒度:表锁、行锁、页锁

表锁:myisam存储引擎支持表锁

行锁:innodb存储引擎支持行锁

页级锁定:查询的数据存放存的存储单位为页(单位默认为M)


row-level-locking 行级锁定(行锁);

写锁的优先级高于读锁;


myisam存储引擎

特点:

支持表锁,不支持行锁;

不支持事务;

独享表空间;(每一个表都有相应的三个相关文件存储)

多用在查询、插入操作较多的表。

相关文件:表名.frm(表结构文件)、表名.MYI(存放索引)、表名.MYD(存放表记录)


InoDB存储引擎

特点:

支持表锁、行锁;

支持事务、支持外键;

共享表空间;()

相关文件:

表名.frm(表结构文件)、表名.ibd(索引和表记录文件)

   ibdata1(支持回滚)

   ib_logfile0、ib_logfile1


设置mysql服务器默认支持的存储引擎

在配置文件/etc/my.cnf

在mysqld字段添加:

default-storage-engine=存储引擎

default-storage-engine=myisam(默认的为innodb)


查看当前的锁状态

即检查Table_lock开头的变量,%作为通配符;

show status like'table_lock%';

1
2
3
4
5
6
7
8
9
mysql> show status  like  'table_lock%' ;
+ -----------------------+-------+
| Variable_name         | Value |
+ -----------------------+-------+
| Table_locks_immediate | 70    |
| Table_locks_waited    | 0     |
+ -----------------------+-------+
rows  in  set  (0.00 sec)
mysql>



外键的条件

1,表的存储引擎为innodb存储引擎

2,表中外键字段的类型要与参考表的字段类型一致

3,外键字段要是索引类型中的一种


创建外键字段的语法格式

foreign key(字段名) references 参考表的表名(字段名)


同步更新、同步删除

在建表的时候指定同步更新、删除:

ON  UPDATE  CASCADE 同步更新

ON  DELETE CASCADE  同步删除


示例过程

create  database engine;   //创建一个名为engine的数据库


create table  engine.yg(            //在engine库中创建yg表

yg_id int(3)  primary key auto_increment,

name  varchar(10) not null

)engine=innodb;           //指定存储引擎为innodb


create table  engine.gz(         //在engine库中创建yg表

gz_id  int(3),

gz  float(7,2) not null default 0.00,

foreign  key(gz_id) references engine.yg(yg_id)  //指定外键字段为gz_id和yg的ge_id对应

on update  cascade on delete cascade         //指定同步更新,同步删除

)engine=innodb;


use  engine;                        //切换到engine数据库


1:要求:

在yg表中插入名字为jim、tom两个名字的条目;

再插入一个名字为tom的条目。

desc yg;

insert into  yg(name)values('jim');

insert into  yg(name)values('tom');

insert into  yg(name)values('tom');


2:要求:

在gz表中添加第2和第3个人的工资为10000,和12000;

desc gz;

insert into  gz values(2,10000);

insert into  gz values(3,12000);


3:要求:

在yg表中添加一个员工,名字为lucy;

在gz表中插入lucy的工资为4000;

删除yg表中lucy的员工信息;注:删除之后gz表中将没有gz_id=4的工资信息

使用select * from  gz;查看gz中是否还有gz_id为4的条目。

insert into  yg(name)values('lucy');

insert into  gz vlaues(4,4000);

delete from  yg where yg_id=4;

select *  from gz;


4:要求

更新yg表中的yg_id为2的员工信息;

update yg set  yd_id=8 where yg_id=2;


5,要求:

删除gz表中的外键;

注:在删除外键的时候,首先使用show  create table from gz;来查看一下外键的名字(注:在创建外键的时候使用的字段名为gz_id,gz_id并不是外键名)。

show create  table from gz;          //首先查看建表过程查找froeign key字段

alter  table gz drop foreign key gz_ibfk_1;    //删除外键

show create  table from gz;             //再次查看外键已经被删除


总结:想要创建一个外键,必须先了解一下MySQL的存储引擎,只有innodb存储引擎才支持创建外键,在MySQL5.6里面,默认的存储引擎为innodb!!!



本文转自 murongqingqqq  51CTO博客,原文链接:http://blog.51cto.com/murongqingqqq/1378306

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
2月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
81 7
|
4月前
|
存储 SQL 关系型数据库
MySQL存储引擎
本文介绍了数据库优化的多个方面,包括选择合适的存储引擎、字段定义原则、避免使用外键和触发器、大文件存储策略、表拆分及字段冗余处理等。强调了从业务层面进行优化的重要性,如通过活动设计减少外部接口调用,以及在高并发场景下的流量控制与预处理措施。文章还提供了具体的SQL优化技巧和表结构优化建议,旨在提高数据库性能和可维护性。
145 1
MySQL存储引擎
|
3月前
|
存储 缓存 关系型数据库
【赵渝强老师】MySQL的MyISAM存储引擎
在MySQL5.1版本之前,默认存储引擎为MyISAM。MyISAM管理非事务表,提供高速存储和检索,支持全文搜索。其特点包括不支持事务、表级锁定、读写互阻、仅缓存索引等。适用于读多、写少且对一致性要求不高的场景。示例代码展示了MyISAM存储引擎的基本操作。
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
3月前
|
存储 关系型数据库 MySQL
【赵渝强老师】MySQL的Memory存储引擎
MySQL 的存储引擎层负责数据的存储和提取,支持多种存储引擎,如 InnoDB、MyISAM 和 Memory。InnoDB 是最常用的存储引擎,从 MySQL 5.5.5 版本起成为默认引擎。Memory 存储引擎的数据仅存在于内存中,重启后数据会丢失。示例中创建了使用 Memory 引擎的 test3 表,并展示了数据在重启后消失的过程。
|
4月前
|
存储 SQL 缓存
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
|
5月前
|
存储 缓存 关系型数据库
MySQL高级篇——存储引擎和索引
MyISAM:不支持外键和事务,表锁不适合高并发,只缓存索引,内存要求低,查询快MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构;表名.MYD 存储数据 (MYData);
MySQL高级篇——存储引擎和索引
|
4月前
|
Ubuntu 关系型数据库 MySQL
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
102 4
|
5月前
|
关系型数据库 MySQL 测试技术
MySQL外键使用的考量与建议
综上所述,虽然MySQL的外键提供了一种强大的工具来维护数据之间的一致性和完整性,但在决定是否使用外键时,需要权衡其带来的好处和潜在的性能影响。通过仔细的规划和测试,可以最大化地利用外键的优势,同时避免一些常见的陷阱。
56 3