数据库基础知识（2）

Mysql

超键 候选键 主键 外键

超键(super key)：在关系中能唯一标识元组的属性集称为关系模式的超键。超键一定包含主键。
候选键(candidate key)：不含多余属性的超键称为候选键。即若在候选键中删除一个属性，那么该属性集就不是键了。
主键(primary key)：用户选作元组标识的一个候选键。
外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

视图

基本语法：

1
2
3
4
5
create view view_name as select_sql;                -- 创建视图
select * from view_name;                            -- 查看视图
create or replace view name as select_sql;          -- 更新视图，不更新表
update view_name;                                   -- 更新视图，也更新表
drop view view_name;                                -- 删除视图

视图是查询表结果的一个临时存储，便于简化复杂的sql操作，隐藏具体的细节，保护数据。视图创建后，可以使用与表相同的方式利用它们。

一般来说，视图只作查询使用，并不能更新，不能被索引，也不能有关联的触发器或默认值。但是对于某些视图(比如未使用联结子查询分组聚集函数 Distinct Union 等)，是可以对其更新的，并且也会对基表进行更新。

视图和表的区别：

  1. 视图是已经编译好的sql语句；而表不是。
  2. 视图没有实际的物理记录；而表有。
  3. 表是内容；视图是窗口。
  4. 表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改。
  5. 表是内模式；视图是外模式。
  6. 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
  7. 表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
  8. 视图的建立和删除只影响视图本身，不影响对应的基本表。

视图和表的联系：
视图是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

drop delete truncate

基本语法：

1
2
3
drop table table_name                           -- 直接删掉表
truncate table table_name                       -- 删除表中数据，再插入时自增长id又从1开始
delete from table_name where_sql                -- 删除表中数据，可以加where字句

  1. 一般而言，drop > truncate > delete
  2. 应用范围：truncate 只能对 table；delete 可以是 table 和 view 。
  3. truncate 和 delete 只删除数据，而 drop 则删除整个表（结构和数据）。
  4. 表和索引所占空间。drop 语句将表所占用的空间全释放掉；truncate 语句将表和索引所占用的空间会恢复到初始大小；而 delete 操作不会减少表或索引所占用的空间。
  5. 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用 delete 且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop；若想保留表而将表中数据删除，如果于事务无关，用 truncate 即可实现。如果和事务有关，或老是想触发 trigger，还是用 delete。
  6. delete 语句为 DML(Data Manipulation Language)，这个操作会被放到 rollback segment 中，事务提交后才生效。如果有相应的 tigger，执行的时候将被触发； truncate、drop 是 DDL(Data Define Language)，操作立即生效，原数据不放到 rollback segment 中，不能回滚。
  7. truncate 在功能上与不带 where 子句的 delete 语句相同——二者均删除表中的全部行。但 truncate 比 delete 速度快，且使用的系统和事务日志资源少。delete 语句每次删除一行，就在事务日志中为所删除的行记录一项，因此可以回滚。truncate 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，因此删除的行是没法回滚的，也不会激活相关的触发器。
  8. truncate 与不带 where 的 delete 只删除数据，而不删除表的结构；而 drop 语句将删除表的结构、被依赖的约束(constrain)、触发器(trigger)、索引(index)，依赖于该表的存储过程/函数将被保留，但其状态会变为 invalid。
  9. truncate 删除表中的所有行，但表结构及其列、约束、索引等保持不变，新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 delete。如果要删除表定义及其数据，请使用 drop。
  10. 对于由 foreign key 约束引用的表，不能使用 truncate，而应使用不带 where 的 delete 语句。

连接的种类

查询分析器中执行：

1
2
3
4
5
6
7
8
9
10
--建表table1,table2：
create table table1(id int,name varchar(10));
create table table2(id int,score int);

insert into table1 select 1,'lee';
insert into table1 select 2,'zhang';
insert into table1 select 4,'wang';
insert into table2 select 1,90;
insert into table2 select 2,100;
insert into table2 select 3,70;

如表:

1
2
3
4
5
6
7
8
-------------------------------------------------
|  table1  |  table2  |
-------------------------------------------------
| id  name | id score |
| 1    lee | 1     90 |
| 2  zhang | 2    100 |
| 4   wang | 3     70 |
-------------------------------------------------

以下均在查询分析器中执行

一、外连接

概念：包括左向外联接、右向外联接和完整外部联接

  1. 左连接：left join或left outer join
    左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select * from table1 left join table2 on table1.id=table2.id
    -----------result------------
    | id  name   id  score |
    -----------------------------
    | 1    lee    1     90 |
    | 2  zhang    2    100 |
    | 4   wang  NULL  NULL |
    -----------------------------
    -- 注释：包含 table1 的所有子句，根据指定条件返回 table2 相应的字段，不符合的以 null 显示

  2. 右连接：right join或right outer join
    右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select * from table1 right join table2 on table1.id=table2.id
    ------------result------------
    | id    name id  score |
    ------------------------------
    |   1    lee  1     90 |
    |   2  zhang  2    100 |
    | NULL  NULL  3     70 |
    ------------------------------
    -- 注释：包含 table2 的所有子句，根据指定条件返回 table1 相应的字段，不符合的以 null 显示

  3. 完整外部联接:full join或full outer join
    完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select * from table1 full join table2 on table1.id=table2.id
    -------------结果-------------
    | id   name   id score |
    ------------------------------
    | 1     lee    1    90 |
    | 2   zhang    2   100 |
    | 4    wang NULL  NULL |
    | NULL NULL    3    70 |
    ------------------------------
    -- 注释：返回左右连接的和（见上左、右连接）
二、内连接

概念：内联接是用比较运算符比较要联接列的值的联接

内连接：join或inner join

1
2
3
4
5
6
7
8
select * from table1 join table2 on table1.id=table2.id
-------------结果-------------
| id  name  id  score |
------------------------------
|  1   lee   1     90 |
|  2 zhang   2    100 |
------------------------------
-- 注释：只返回符合条件的table1和table2的列

等价（与下列执行效果相同）
1
2
3
A: select a.*, b.* from table1 a, table2 b where a.id=b.id
B: select * from table1 cross join table2 where table1.id=table2.id
-- 注：cross join后加条件只能用where, 不能用on

三、交叉连接(完全)

概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）

交叉连接：cross join(不带条件where…)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select * from table1 cross join table2
-------------结果-------------
| id  name  id score |
------------------------------
| 1    lee   1    90 |
| 2  zhang   1    90 |
| 4   wang   1    90 |
| 1    lee   2   100 |
| 2  zhang   2   100 |
| 4   wang   2   100 |
| 1    lee   3    70 |
| 2  zhang   3    70 |
| 4   wang   3    70 |
------------------------------
-- 注释：返回3*3=9条记录，即笛卡尔积

等价

1
A: select * from table1, table2

数据库优化的思路

1. SQL语句优化
  • 应尽量避免在 where 子句中使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描。
  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 
    1
    select id from t where num is null
    可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 
    1
    select id from t where num=0
  • 很多时候用 exists 代替 in 是一个好的选择。
  • 用 Where 子句替换 having 子句 因为 having 只会在检索出所有记录之后才对结果集进行过滤。
2. 索引优化

详见数据库基础-1索引部分

3. 数据库结构优化
  1. 范式优化： 比如消除冗余（节省空间……）
  2. 反范式优化：比如适当加冗余等（减少join）
  3. 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的表可采取此方法。可按月自动建表分区。

  • 拆分其实又分垂直拆分和水平拆分：

    • 垂直拆分：
      解决问题：表与表之间的io竞争
      不解决问题：单表中数据量增长出现的压力

    • 水平拆分：
      解决问题：单表中数据量增长出现的压力
      不解决问题：表与表之间的io争夺

    • 案例： 简单购物系统暂设涉及如下表：

      1. 产品表（数据量10w，稳定）
      2. 订单表（数据量200w，且有增长趋势）
      3. 用户表 （数据量100w，且有增长趋势）
      • 垂直划分方案： 把产品表和用户表放到一个server上，订单表单独放到一个server上
      • 水平划分方案：用户表通过性别拆分为男用户表和女用户表，订单表通过已完成和完成中拆分为已完成订单和未完成订单，产品表未完成订单放一个server上，已完成订单表和男用户表放一个server上，女用户表放一个server上(女的爱购物 哈哈)
4. 服务器硬件优化

这个么，多花钱咯

存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用 EXECUTE 语句调用，而是在用户执行 Transact-SQL 语句时自动触发（激活）执行。

触发器是在一个修改了指定表中的数据时执行的存储过程。

通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。

触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如 UPDATE、INSERT、DELETE 这些操作时，SQL SERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

锁

在Mysql数据库系统中,不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁，BDB采用的是页面锁，也支持表级锁，InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

MySQL中这3种锁的特性可大致归纳如下:

模式 开锁、加锁速度、死锁、粒度、并发性能
行级锁 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁 开销和加锁速度界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
表级锁 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

而每种锁又可分为共享锁和排他锁，共享锁和排他锁的两两组合中，仅有共享锁和共享锁可以互相兼容，即加了共享锁的记录仍可被其他事务以共享读的形式访问。

事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

MySQL InnoDB 中跟数据持久性、一致性有关的日志，有以下几种：

  • Bin Log：是mysql服务层产生的日志，常用来进行数据恢复、数据库复制，常见的mysql主从架构，就是采用slave同步master的binlog实现的
  • Redo Log：记录了数据操作在物理层面的修改，mysql中使用了大量缓存，修改操作时会直接修改内存，而不是立刻修改磁盘，事务进行中时会不断的产生redo log，在事务提交时进行一次flush操作，保存到磁盘中。当数据库或主机失效重启时，会根据redo log进行数据的恢复，如果redo log中有事务提交，则进行事务提交修改数据。
  • Undo Log：除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC

参考链接

c++面试宝典
SQL视图
视图和表的区别
Mysql常见的三种存储引擎
MVCC实现原理