MySQL 存储引擎知识讲解

本文主要给大家介绍MySQL 存储引擎知识讲解，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在亿速云行业资讯里面关注我的更新文章的。

1、MySQL键值(限制如何给字段赋值)
1）
索引:类似与“书的目录”树型目录
缺点:减慢写的速度（insert update delete）
占用物理存储空间
优点:加快查询速度
2）
使用索引
使用规则
一个表中可以由多个index字段
字段的值允许由重复，切可以赋NULL
经常把做查询条件的字段设置为index字段
index字段的key标志是MUL

查看索引
desc 表名；

show index for 表名；
#查看索引详细信息

创建索引
create table 表名(
字段 类型，
index(索引名)；
#建表时创建索引
#默认和字段名同名

-> create table t21(
-> name char(10),
-> age int,
-> sex enum('boy','girl'),
-> index(name),
-> index(sex));

#####################################################################

create index 索引名 on 表名(字段);
#已有表创建索引
#索引名可以和字段名相同
#默认使用的索引类型:BRREE（二叉树）

create index name on t3(name)

drop index 索引名 on 表名；
#删除索引

###################################################################
主键 parimary key

使用规则
一个表中只能有一个 parimary key 字段
对应的字段值不允许有重复，且不允许赋值
如果有多个字段都作为 parimary key ，称为复合主键，必须一个创建。
主健字段的KEY标志死PRI
通常与 auto_increment 连用
经常把表中能够唯一标识记录的字段位主键
1）
-> create table t22(            -> create table t22(
-> name char(10),               -> name char(10)primary key ,
-> age int,                 -> age int,
-> likes set('a','b','c'),      -> likes set('a','b','c')0;
-> primary key(name));
#创建表时添加主键

2)
alter table 表名 add primary key(字段)；
#已有表添加主键

3）
alter table 表名 drop primary key;
#删除主键

##########################################################################
复合主键
多个字段一起做主键，字段值无法同时重复。

1）
-> create table 表名(
-> cip char(15),
-> port smallint,
-> status enum('allow','deny') default 'deny',
-> primary key(cip,port));
#创建复合主键，必须一起创建

2）
alter table 表名 drop primary key;
#删除复合主键，必须同时删除

3）
alter table t23 add primary key(cip,port);
#已有表创建复合主键

#############################################################################
主键与 auto_increment 连用，让字段的最大值自动增长 +1 ,并且位数值类型

1）
-> create table t24(
-> id int(2) zerofill primary key auto_increment,
-> name char(10),
-> class char(4),
-> index(name));
#创建表

insert into  t24(name,class) values('tom','1709');
#添加信息 name class ，此时表中 id 字段自动 +1 。

2）
alter table t24  modify id int(2) unsigned zerofill not null;
alter table t24 drop  primary key;
#删除主键要先把 aotu_increment

############################################################################
unique唯一索引
一个表中可以有多个unique字段
对应的字段值不允许有重复
unique字段的key标志是uni
unique字段的值允许为null，当将其修改为不允许为null，则此字段限制与主键相同

############################################################################
外键：foreign key
作用：
限制给字段赋值的。
值必须在指定表中指定字段值的范围内选择。
条件：
表的存储引擎必须是innodb
字段类型要一致
被参照字段必须要是索引类型的一种（primary key)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
1）
foreign key(字段) references 被参考表(字段)
on update cascade on delete cascade
#使用方法，同步更新同步删除

练习：
参考表
mysql> create table jfb(
-> id int(2) primary key auto_increment,
-> name char(10),
-> pay float(7,2)
-> )engine=innodb;

insert into jfb(name,pay) values('bob',20000),('lucy',15000);
+-------+----------+-----------------+
| id | name | pay      |
+-------+----------+-----------------+
|  1 | bob  | 20000.00 |
|  2 | lucy | 15000.00 |
+-------+----------+-----------------+

外键表
mysql> create table xsb(
-> num int(2),
-> name char(10),
-> class char(9),
-> foreign key(num) references jfb(id)
-> on update cascade
-> on delete cascade
-> )engine=innodb;
#num字段参考jfb(id)

insert into xsb values(1,'bob','nsd1709'),(2,'lucy','nsd1709');
#将xsb写入数据
+----------+----------+----------------+
| num  | name | class   |
+----------+-----------+---------------+
|    1 | bob  | nsd1709 |
|    2 | lucy | nsd1709 |
+----------+----------+----------------+

insert into xsb values(3,'tom','nsd1709');
#因为参考表jfb(id)没有3，所以无法创建

insert into xsb values(2,'jerry','nsd1709');
+----------+------------+----------------+
| num  | name  | class   |
+----------+------------+----------------+
|    1 | bob   | nsd1709 |
|    2 | lucy  | nsd1709 |
|    2 | jerry | nsd1709 |
+----------+------------+----------------+

参考表jfb(id)只有两条数据，但是还是添加成功因为
参数 2 存在所以添加成功，但是参考表只有两条数据
为了避免这种情况我们要让 xsb(num) 也具有唯一性
在上面添加主键

delete from xsb where name='jerry';
#删除重复数据

alter table xsb add primary key(num);
#为 xsb(num) 设置主键

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  1 | bob  | 20000.00 |        |    1 | bob  | nsd1709 |
|  2 | lucy | 15000.00 |        |    2 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

update jfb set id=8 where id=2;
#修改 jfb id=2 改为 id=8

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  1 | bob  | 20000.00 |        |    1 | bob  | nsd1709 |
|  8 | lucy | 15000.00 |        |    8 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

delete from jfb where id=1;
#删除 jfb id 为1的数据

jfb                 xfb
+-------+----------+-----------------+  +----------+----------+----------------+
| id | name | pay      |        | num  | name  | class  |
+-------+----------+-----------------+  +----------+-----------+---------------+
|  8 | lucy | 15000.00 |        |    8 | lucy | nsd1709 |
+-------+----------+-----------------+  +----------+----------+----------------+

delete from jfb;
error:被参考的表不能随便被删除的

删除外键
show create table 表名    
#外键名称自动生成。
alter table 表名 drop foreign key 外键名
#删除外键名字

###########################################################################
存储引擎
MySQL 数据库服务软件自带的程序。
不同的存储引擎有不同的功能和数据存储方式
是处理表的处理器
表名.frm 存放数据库结构

1）
show engines;
#查看数据库服务支持的存储引擎

2)
存储引擎的特点
MyISAM：
表名.MYI  索引信息
表名.MYI  数据
表名.frm  表结构
功能：
支持表级锁:锁一张表
不支持事务   事务回滚

InnoDB
表名.MYI  表结构
表名.idb  数据+索引信息
功能：
支持行级锁：只给当前被访问的行加锁
支持事务   事务回滚

锁类型：读锁、     写锁
select insert delete update

接收写操作的表适合使用 InnoDB 存储引擎
接收读操作的表适合使用 MyISAM 存储引擎

锁粒度：表级锁 行级锁

事务：一次数据访问从开始访问到访问结束的过程
事务回滚：一次数据访问过程中任意一步操作错误，都会恢复所有操作
事务特性：一致性   原子性   隔离性

事务日志文件：记录InnoDB存储引擎的表执行过的操作。

3)
在配置文件中写入 /etc/my.cnf
default_storage_engine=存储引擎名
#修改默认引擎

alter table 表名 engine=存储引擎名
#修改表引擎

create table 表名（。。。。）engine=innodb;
#设置表的存储引擎

因当在表中没有存储数据时设置。

###########################################################################################
MySQL 服务体系结构：（8个功能模块）
连接池
sql接口
分析器：分析命令语法
优化器：以最小消耗执行命令
查询缓存：存放曾经查找过的数据
存储引擎
文件系统：硬盘
管理工具：装包后提供的命令

看了以上关于MySQL 存储引擎知识讲解，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。