MySQL-原理篇_表级主键和列级主键的区别

主键（列级和表级）

• 注意：一张表必须有，且只能有一个主键约束！！
• 作用：主键值是这条记录在这张表中的唯一标识（可以看做这条记录的身份证号）；
• 分类

1.根据主键字段的数量划分
单一主键：
复合主键：即表级主键约束（可以理解为两个身份证号粘到一起，仍然算一个主键约束，但违背了三范式）
	primary key(字段1，字段2,...)   // 将这些字段粘到一起，不能为NULL也不能重复
2.根据主键性质划分
自然主键：推荐（跟业务无关）
业务主键：例如拿银行卡号，身份证号当主键，但主键字段最好不要跟业务挂钩（万一业务发生改变，主键值就需要改，但有时修改可能会导致主键重复），最好单纯设置一个编号作为主键
1
2
3
4
5
6
7

• 自增主键： primary key auto_increment

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment, 
	username varchar(255)              
);

insert into t_user(username) values('a'), ('b'), ('c'), ('d');  // 自动为主键id生成1,2,3,4
select * from t_user;

+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
+----+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

存储引擎（mysql默认存储引擎InnoDB，默认字符集utf8）

• 定义：不同存储引擎代表了底层存储数据的不同方式
• 完整的建表语句：

create table tmp(
	id int
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3

• 查看当前mysql支持的存储引擎

show engines \G
1

• 常见存储引擎：

MyISAM：
	优点：MySQL最常用的，用三个文件存储一张表;可以对文件压缩、或改为只读表，提高检索效率；
		表名.frm（存储表结构的文件）
		表名.MYD（存储表数据的文件）
		表名.MYI（存储表索引的文件）
	缺点：不支持事务

InnoDB：
	优点：
		表名.frm（在数据库目录中，每个表以.frm文件表示）
		表空间tablespace（存储表的内容，是一个逻辑概念，因此无法压缩）
		支持事务，行级锁等，保证数据的安全（因此是默认引擎）；
		在MySQL数据库崩溃后可以自动恢复；
		支持外键，级联更新和级联删除（但通常不这么干，如果父表有子表，一般不会直接修改父表）
			级联更新：（正常先增加父表记录，再增加子表记录；但InnoDB支持级联更新，即修改父表的一条记录，则子表中所有使用到该条记录的记录都会同步更新）；
			级联删除：同理。删除一条父表记录，则会同步删除子表中所有使用了该条记录的记录

Memory：
	优点：所有数据和索引存在内存中，且行的内容固定，因此速度最快，适合查询（断电就没了，数据容易丢失）；
	缺点：不支持事务；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 引擎的选择：

需要节省空间：MyISAM
看中安全：InnoDB
追求速度：Memory
1
2
3

事务（commit，rollback，savepoint）：

• 定义：类似于原子操作，一个事务就是一个完整的业务单元，不可再分；一次事务的多条SQL语句要么同时成功，要么同时失败
• 事务过程：开启事务到结束事务（提交或回滚）之间，所做的操作不会直接修改硬盘上的文件数据，而是保存在历史操作中；最后如果commit，那么就把这些操作同步到磁盘上并清空历史操作；如果是rollback，则直接清空历史操作；savepoint可以回滚到某个回滚点，而不是全部回滚；
• 和事务有关的操作：只有insert，delete，update等DML语句需要事务，即需要操作表中数据的语句
• 事务的四大特征ACID：

A：原子性（Atomicity）：事务是最小工作单元，不可再分）
C：一致性（Consistency）:事务必须保证多条DML语句要么同时成功，要么同时失败
I：隔离性（Isolation）：同时有多个用户访问数据库，数据库为每个客户开启的事务，不能受其他事务影响，有4种隔离级别
	读未提交（read uncommitted）：对方事务还未提交，我就可以读取；
		问题：脏读现象（读到了缓存的数据，很不稳定，毕竟他本次事务还未结束）
	读已提交（read committed）：对方提交后的数据，我才可以读取到；解决了脏读
		问题：不可重复读现象（在这个事务还未结束时，如果其他人修改表中数据，并不断提交，那我读到的数据永远是最新的，因此每次读到的都不一样）
	可重复读（repeatable read）：解决了不可重复读问题（在事务开始时，将需要读取的数据保存到一块空间，因此直到本次业务结束期间，每次读到的数据是一样的，不会受外界影响）；是MySQL的默认隔离级别；
		问题：幻读现象（我读到的是幻像，假如我开启事务时，保存了一份数据，在事务期间，文件中的数据已经被其他人修改了，因此我重复读到的是假的数据）
	序列化读（serializable）：解决了所有问题，类似于加锁，一个事务加锁后，其他事务就无法加锁（分为读锁和写锁）。（效率低，需要事务排队）	
D：持久性（Durability）：最终数据必须同步到文件中，事务才算结束
1
2
3
4
5
6
7
8
9
10
11

• 事务语句：

MySQL默认一条SQL语句执行结束，会自动commit；
start tranction; // 开启事务，之后的操作都不会自动commit，直到commit或rollback
insert into 表名() values();
delete from 表名 where...;
update 表名 set 字段1=值1，字段2=值2 where...;

savepoint 回滚名1;    // 保存此时状态，用于之后回滚
...
rollback to 回滚名1; // 会滚到回滚名1处的状态
rollback;           // 全部回滚，只有commit和rollback才算结束这次事务
commit;             // 提交
1
2
3
4
5
6
7
8
9
10
11

索引

• 作用：相当于一本书的目录，缩小了检索的范围，因此能加快检索速度；
• 添加索引的条件：

1.数据量庞大；
2.该字段很少DML操作（insert delete update），因为字段被修改，索引也相应需要维护
3.该字段经常出现在where子句中（即经常需要根据该字段做判断）
1
2
3

• 注意点：

1.主键和具有unique约束的字段会自动添加索引（这就是根据主键查询效率高的原因）
1

• 创建索引

1.
create index emp_sal_index on emp(sal);   // 为表emp的sal字段创建一个索引，索引名是emp_sal_index

2.在创建表时添加索引
create table t_user(
	id int primary key,
	infomation text,
	fulltext key(infomation)
);
1
2
3
4
5
6
7
8
9

• 删除索引

drop index emp_sal_index on emp;   // 删除表emp中的索引emp_sal_index
1

• 索引原理（B+树）
  select ename from emp where ename = ‘SMITH’;
  如果ename字段添加了索引，会被转换为
  select ename from emp where 物理地址 = ‘0x3’;

当ename作为where条件时，会判断ename是否添加了索引，如果没有就全表扫描（效率低）；
如果添加了（在为ename字段create索引时，会生成一个索引对象，先对表记录排序，构造出一棵B+树（跟二叉搜索树同理，左小右大），因此不断缩小区间，最终定位到某一条具体记录（携带有该条记录在表中的物理地址）），我们只要直接访问地址就能找到该条记录；
1
2

• 索引分类：

单一索引：给单一字段添加一个索引
复合索引：多个字段联合起来添加一个索引
主键索引：主键默认添加索引（由于非空且不重复，因此只需要查询一条记录，效率最高！）
唯一索引：unique字段默认添加索引
1
2
3
4

• 索引失效

select ename from emp where ename like '%A%';    // 使用模糊查询，第一个字符是%，就无法确定去哪个分支
1

视图（view）

• 定义：站在不同角度去看待同一张表的数据
• 创建视图

create view 视图名 as select empno, ename from emp; 

1.创建视图时, as后面只能跟select语句
2.但创建好视图后，可以用CRUD语句操作视图
1
2
3
4

• 删除视图

drop view 视图名;
1

• 注意：

1.对视图（增删改查），会影响原表数据（但不是直接操作原表，而是通过视图影响的）
1

• 视图的作用：隐藏实现细节（对一些保密较高的系统，对方只会提供相关视图，我们只能对视图CRUD）

DBA命令（导入导出）

• 导出数据（用于备份）

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -plixu1005..   // 将bjpowernode数据库导出到D:\bjpowernode.sql
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -plixu1005..  // 只导出指定数据库中的emp这张表
1
2

• 导入数据

create database bjpowernode;
use bjpowernode;
source .sql文件所在路径;
1
2
3

三大范式（减少数据冗余）

• 三大范式

第一范式：任何一张表都应该有主键，且每一个字段是原子性的不可再分
	原子性不可再分（例如联系方式字段中填了手机号和邮箱，我们可以分为手机号字段和邮箱字段）
第二范式：所有非主键字段应完全依赖主键，不能部分依赖
	若使用复和主键（字段a和字段b），容易产生部分依赖（有的非主键字段依赖于a，有的依赖于b，会产生数据冗余；若使用单一主键，就不会出现部分依赖）
		多对多？三张表，关系表两个外键：例如一个学生上多个老师的课，一个老师带多个学生，即多对多。应创建三张表（学生表，老师表，关系表），其中关系表有两个外键，只用来表示学生表和关系表的关系
第三范式：所有非主键字段直接依赖主键，不能产生传递依赖
	一对多？两张表，多的表加外键
1
2
3
4
5
6
7

• 注意：

实际开发，满足客户需求为主，有时会拿冗余换速度（毕竟分的表越多，表联查会耗费时间）
1

• 一对一设计：
  1.主键共享（pk + fk）
  2.外键唯一（fk + unique）