02： MySQL 索引类型 、 MySQL 存储引擎
day02一、mysql索引
二、MySQL存储引擎
+++++++++++++++++++++++++++++++++++
一、mysql索引
1.1 索引介绍 ：相当于“书的目录”
5000页
1~200 目录信息
拼音排序
部首排序
笔画排序
201~5000正文
1.2 索引的优点与缺点？
优点 加快查询的速度
缺点占用物理存储空间,减慢写的速度。
姓名 性别 班级 年龄
jim
jim
NULL
1.3 使用普通索引index：（在表中的字段上创建索引）
使用规则？
查看desc 表名； key ----> MUL
show index from表名；
Table: t23
Key_name: name
Column_name: name
Index_type: BTREE ( B+TREE Hash)
二叉树
1-10
1-5 6-10
1-2.52.6-5
创建index 索引：
创建表时 指定索引字段
createtable 表名(
字段名列表，
index(字段名1),
index(字段名2),
);
createtabledb1.t23(
namechar(10) ,
sex enum("boy","girl") default"boy" ,
age tinyint(2) unsignednotnulldefault18,
index(name),
index(sex)
);
descdb1.t23;
showindexfromdb1.t23\G;
在已有表里创建index索引字段
createindex 索引名on表名(字段);
删除index索引 drop index 索引名on表名;
二、主键primarykey的使用
2.1使用规则？
2.2查看
desc 表名； key ----> MUL
show index from表名；
2.3 创建
2.3.1 建表时创建
createtablet25(
stu_idchar(9) primary key,
name char(10),
sex enum("boy","girl"),
age tinyint(2) unsigned
);
createtablet24(
stu_idchar(9),
name char(10),
sex enum("boy","girl"),
age tinyint(2) unsigned,
primary key(stu_id)
);
2.3.2 在已有表里创建
altertable 表名add primary key(字段名);
2.4 删除主键> 2.5复合主键的使用（表中多个字段一起做主键 ，复合主键字段的值不允许同时重复,要一起创建）
PRI PRI
name > jim nsd1710 yes
jim nsd1712 no
建表时创建
createtable db1.xfb(
namechar(10),
classchar(7),
pay enum("yes","no")default"no",
primary key(name,class)
);
验证
insert intodb1.xfb values("jim","nsd1710","yes");
insert intodb1.xfb values("jim","nsd1710","yes");
insert intodb1.xfb values("jim","nsd1710","no");
insert intodb1.xfb values("bob","nsd1710","yes");
insert intodb1.xfb values("bob","nsd1703","no");
删除复合主键 > 在已有表里添加复合主键。
altertable 表名 addprimarykey(字段名列表);
++++++++++++++++++++++++++++++++++++++++++
2.6primary key与 auto_increment连用
字段的值自动增长i++i=$i+1
数值类型
primary key
idname age sex
1 jim 21 boy
2 tom 19 boy
createtable db1.t26(
idint(2)zerofillprimary keyauto_increment,
namechar(10),
agetinyint(2) unsigned,
sexenum("boy","girl","no")
);
insert intodb1.t26(name,age,sex)values("bob",21,"boy");
insert intodb1.t26(name,age,sex)values("bob",21,"boy");
insert intodb1.t26(name,age,sex)values("bob",21,"boy");
select* from db1.t26;
唯一索引 unique
pri pri
姓名 护照编号 驾驶证号
null null
使用规则？
查看desc 表名； key ----> UNI
创建
建表时创建
createtable db1.t27(
namechar(10),
hz_idchar(5),
jsz_id char(5),
unique(hz_id),
unique(jsz_id)
);
descdb1.t27;
insert intodb1.t27values("jim","aaa","bbb");
insert intodb1.t27values("jim","aaa","bbb");
insert intodb1.t27values("jim","aaab","bbbc");
insert intodb1.t27values("jim",null,null);
createtable db1.t28(
namechar(10),
hz_idchar(5) notnull,
jsz_id char(5),
unique(hz_id),
unique(jsz_id)
);
descdb1.t28;
在已有表里创建unique
create unique index 索引名on表名(字段名);
删除dropindex 索引名on表名；
+++++++++++++++++++++++++++++++++++++++
三、外键的使用
外键作用？
外键的使用规则？
创建外键：
foreign key(字段名)references表名(字段名)
onupdate cascade ondelete cascade
jfb 缴费表
学号
jfb_idname pay
create tabledb1.jfb(
jfb_id int(2)primary keyauto_increment,
name char(10),
pay float(7,2)default 20000
)engine=innodb;
insert into db1.jfb(name)values("bob"),("tom");
bjb 班级表
外键
学号
bjb_id name pay
create tabledb1.bjb(
bjb_id int(2),
name char(10),
pay float(7,2)default 20000,
foreign key(bjb_id)referencesjfb(jfb_id)
onupdate cascade ondelete cascade
)engine=innodb;
alter table bjbadd primary key(bjb_id);
use db1;
show createtablebjb;
验证外键？
insert into bjbvalues(1,"bob",20000);
insert into bjbvalues(3,"lucy",20000);
insert intojfb(name)values("lucy");
insert into bjbvalues(3,"lucy",20000);
update表名set 字段名=值where 条件;
updatejfbset jfb_id=8 where jfb_id=2;
select from jfb;
select frombjb;
delete from表名where条件;
delete fromjfbwhere jfb_id=3;
select from jfb;
select frombjb;
使用要注意的事项？
删除外键> showcreatetablebjb;
altertablebjbdrop foreignkeybjb_ibfk_1;
在已有表里创建外键：
altertablebjbaddforeign key(bjb_id)referencesjfb(jfb_id)
onupdate cascade ondelete cascade;
+++++++++++++++++++++++++++++++++++
四、MySQL存储引擎
4.1MySQL存储引擎介绍：是数据库服务自带的功能程序，
处理表的处理器
每种存储引擎的功能和数据存储方式都不同
4.2查看
表使用的存储引擎 show createtable表名；
数据服务使用的存储引擎
show engines;
InnoDB | DEFAULT
4.3 修改
表使用的存储引擎?
altertable 表名engine=存储引擎名；
建表时指定表使用的存储引擎?
createtable表名(
字段名列表
.....
)engine=存储引擎名；
数据服务使用的存储引擎?
vim /etc/my.cnf
default-storage-engine=myisam
.....
:wq
#systemctl restartmysqld
++++++++++++++++++++++++++++++++
4.4 生产环境中常用存储引擎及特点
myisam特点
支持表级锁
不支持外键 、事务、事务回滚
数据存储方式.frm .MYI .MYD
表结构 索引 数据
innodb特点
支持行级锁、 外键 、事务、事务回滚
数据存储方式.frm .ibd
表结构 索引+数据
锁的作用：解决并发访问的冲突问题
锁类型：读锁（共享锁）写锁（排它锁）
锁粒度：表锁行锁(页锁)
事务：一次sql操作从连接到断开连接的过程称为事务。要么全部执行成功，任意一步错误，执行都失败。
ATM
插卡
转账：对方卡号 11111
汇款金额 50000
转账中。。。。。
退卡
事务回滚：事务执行过程，任意一步执行不成功，会恢复所有的操作。
innodb存储引擎的表使用事务文件记录执行过的sql操作。
cd/var/lib/mysql/db1/t1.*
ls
ib_logfile0
ib_logfile1
ibdata1
insert intot1values(101),(102);
4.5 建表时如何决定表使用哪种存储引擎
接收查访问多的表，适合使用myisam存储引擎，节省系统资源。
接收写访问多的表，适合使用innodb存储引擎，并发访问量大。
页:
[1]