MySQL3高级

表关系

存储关系：通过外键并遵从3范式（商业用3范式通常足以）来实现E-R关系的表格化，结合业务读写特点优化设计表格
对于关系的处理：1对1（关系字段放在哪张表都可以）
1对多（如学生对成绩是1对多，成绩对学生是一对一：
1:n，关系字段students.id（外键）存储在成绩表中）：：1主键→n外键
多对多：新建一张表来存储关系（如学生对科目是1对多，科目对学生是1对多：m:n，关系字段（stuid，subid，两个外键）存储在另一表(scores)中）

其他优化：如常访问的数据可以作为一张表，不一定非要按范式要求设计表格
关系表的关系不能闭合，会造成冗余的：如（a中引用b主键，b中引用c主键，c中引用a主键）→（要根据业务查询需要考虑断开哪个关系）

E-R模型+三范式→表结构和关系（使用外键），+业务特点→最终表
学生表：students(id,sname,…)
成绩表：scores(id,score,学生stuid(students.id)，科目subid(subjects.id))
科目表：subjects(id,stitle)

外键操作：

添加：
新建表后修改添加：
alter table 表名 add constraint 外键名 foreign key(本表字段) references 外表(外表字段);
建表时建立外键：
create table tname(
xxx xxx…
foreign key(本表字段) references 外表(外表字段)
)

删除：逻辑删除，或者添加联级
外表的联级操作：
alter table 表名 add constraint 外键名 foreign key(本表字段) references 外表(外表字段) on delete cascade;
级联操作的类型包括：（从上到下覆盖操作）
restrict（限制）：默认值，抛异常
cascade（级联）：如果主表的记录删掉，则从表（此表）中相关联的记录都将被删除
set null：。。。。。。将外键设置为空
no action：。。。。。。什么都不做

别名

表别名、字段别名： as ‘简写名’ 或者 ‘简写名’

关联查询

内连接 table1 inner join table2……on……：查询两表的笛卡尔积中满足要求的数据，相当于连接查询
左外链接 table1 left join table2……on……：查询1中所有数据并连接2中符合条件的数据，没有符合的则补为null
右外链接 table1 right join table2……on……：。。。。
连接查询：select * from table1,table2 where table1.id=table2.id;
关联查询：查询每个学生每个科目的分数
select students.sname,subjects.stitle,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

“相比于子查询，关联查询性能会低一点，但代码不会太乱”

关联查询：需求→确认实体→确认关系→建表：
“确定实体间是否有关系→确定是几对几的关系→确定在哪个实体中建立字段”

自关联表结构和自关联查询

自关联表结构：外键引用本表主键，如 省-市-区。物理表由多张逻辑表组成。
table1（pid，省），table2（sid，市，pid），table3（aid，区，sid）→（id，区域，上级区域id）→areas（id,区域，pid）。
数据如（1，湖北，null）（1001，武昌，1）
create table areas(
id int primary key,
atitle varchar(20),
pid int,
foreign key(pid) references areas(id)
);

自关联查询：通过别名使用同一表进行关联查询
查询上海有多少个区县：
select city.，count() from areas as city inner join areas as province on province.id = city.pid where province.atitle = ‘上海’
查询有多少个省：select * from areas where pid is null;
更多表相比于更多列，更占用数据库资源

视图

视图view（封装的只是select语句,原数据更新后这个语句查出的数据也会更新(view虚表的数据也会更新)）
创建视图：CREAT VIEW viewName AS SELECT子句;
使用视图：SELECT * FROM viewName;
删除视图：DROP VIEW viewName;
视图名要见名晓意：如：v_stu_sub_sco
show tables 可以查看到视图

事务

事务：一个业务逻辑需多个sql完成，其中某sql出错，则希望整个操作都退回。使用事务可以完成退回的功能“保证业务逻辑的完整正确性”；
事务的四大特性（ACID）

• 原子性(Atomicity)：事务中的全部操作构成一个最小原子（要么全完成，要么全不执行）
• 一致性(Consistency)：并行执行的事务，整体的执行结果与按某顺序串行的结果一致（并行的事务不会因为之间顺序不同而结果不同）
• 隔离性(isolation)：事务的执行是独立的，不会因为操作的是同一个逻辑对象而导致数据错乱（锁）
• 持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
  要求：表引擎必须为InnoDB或bdb，才能使用事务：不同引擎由于底层数据结构和算法不同，有不同的读写速度。
  ——————innodb引擎支持行级锁，MyISAM引擎支持到表级锁。
  锁：全局锁、表级锁（表锁、元数据锁）、行级锁：https://www.jianshu.com/p/71cc84fa4183
  查看表的创建语句：show create table tableName;
  更改表引擎：alter table tableName engine=innodb;
  事务操作流程：begin;——sql语句——commit;、rollback;
  ————begin后，对表的写会导致这个表对应的行(MyISAM引擎是锁表)被锁住，其他终端操作该行的语句都会被堵塞，
  ————————直到commit;或rollback;后其他终端操作该行的语句才会执行。
  ————其他终端操作如果不是操作该行的数据则不会受影响

索引

查询语句的优化（一般应用系统的读写比例是10:1，写基本不会有性能问题，所以需要考虑读取优化）
索引：数据量大时查找数据会变得很慢，创建索引能提高数据访问性能→按照主键、索引进行查询
数据物理存储的顺醋是按照主键的顺序的。主键也是一个索引（索引名PRIMARY）。
选择索引的字段：占用空间、数据复杂度和比较复杂度
————————数据类型占空间越小越好（在磁盘、内存和CPU缓存中占用空间更小，处理起来更快）；
————————简单的数据类型更好：整形比字符串，处理起来开销和复杂度都更小
————————避免null：null使索引、索引的统计信息及运算更加复杂，要用0或者空字符串代替空值，字段约束为not null。
对where后面的字段建索引：不适合对限制范围的字段建索引、不适合对or语句的字段建索引。
————————将等值限制的字段放在逻辑判断前面，统一建索引，以防索引被其他逻辑判断阻断
索引操作：
创建索引：create index 索引名 ON 表名(column1(length),…)
查看索引：SHOW INDEX FROM 表名;
删除索引：DROP INDEX [索引名] ON 表名;
联合索引：把where的多个字段建立成为一个索引

缺点：更新表(insert update delete)时MySQL要更新数据也要更新索引，所以过多的索引会降低写入速度。而且索引文件占用磁盘空间。
profiling性能监控工具:
开启运行时间检测：set profiling = 1;
执行sql语句;
查看执行时间：show profiles;
sql语句
查看执行时间：show profiles;