SQL拓展笔记

⼀、知识点拓展

1. 主键 超键 候选键 外键

• 主 键：数据库表中对储存数据对象予以唯⼀和完整标识的数据列或属性的组合。⼀个数据列只能有⼀个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 超 键：在关系中能唯⼀标识元组的属性集称为关系模式的超键。⼀个属性可以为作为⼀个超键，多个属性组合在⼀起也可以作为⼀个超键。超键包含候选键和主键。
• 候选键：是最⼩超键，即没有冗余元素的超键。
• 外 键：在⼀个表中存在的另⼀个表的主键称此表的外键。

2. 数据库事务的四个特性及含义

数据库事务transanction正确执⾏的四个基本要素。ACID,原⼦性(Atomicity)、⼀致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

• 原⼦性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执⾏过程中发⽣错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执⾏过⼀样。
• ⼀致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
• 隔离性:隔离状态执⾏事务，使它们好像是系统在给定时间内执⾏的唯⼀操作。如果有两个事务，运⾏在相同的时间内，执⾏ 相同的功能，事务的隔离性将确保每⼀事务在系统中认为只有该事务在使⽤系统。这种属性有时称为串⾏化，为了防⽌事务操作间的混淆，必须串⾏化或序列化请 求，使得在同⼀时间仅有⼀个请求⽤于同⼀数据。
• 持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3 .数据库优化的思路

3.1 SQL语句优化

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

3.2 索引优化

推荐: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

3.3.数据库结构优化

1）范式优化： ⽐如消除冗余（节省空间。。）
2）反范式优化：⽐如适当加冗余等（减少join）
3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据⽂件⾥。这样，当对这个表进⾏查询时，只需要在表分区中进⾏扫描，⽽不必进⾏全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，⼀个精⼼设置的分区可以将数据传输对磁盘I/O竞争
均匀地分散开。对数据量⼤的时时表可采取此⽅法。可按⽉⾃动建表分区。
4）拆分其实又分垂直拆分和⽔平拆分： 案例： 简单购物系统暂设涉及如下表：

• 1.产品表（数据量10w，稳定）
• 2.订单表（数据量200w，且有增长趋势）
• 3.⽤户表 （数据量100w，且有增长趋势） 以mysql为例讲述下⽔平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万
• 垂直拆分：
  • 解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压⼒
  • ⽅案： 把产品表和⽤户表放到⼀个server上 订单表单独放到⼀个server上
• ⽔平拆分：
  • 解决问题：单表中数据量增长出现的压⼒ 不解决问题：表与表之间的io争夺
  • ⽅案： ⽤户表通过性别拆分为男⽤户表和⼥⽤户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放⼀个server上 已完成订单表盒男⽤户表放⼀个server上 ⼥⽤户表放⼀个server上(⼥的爱购物 哈哈)

3.4. 服务器硬件优化

这个么多花钱咯！