基础知识

执行顺序图片.png

select 查询的字段 ⑦ from 表1 别名 ① 连接类型 join 表2 别名 ③ on 连接条件 ② where 筛选条件 ④ group by 分组的字段 ⑤ having 分组后的筛选条件 ⑥ distinct 列名 ⑧ 之后 union order by 字段或表达式 ⑨ limit 偏移 ,条目数 ⑩

五种 join 的 sql 编写

图片.png

  1. select * from tableA a
  2. inner join tableB b
  3. on a.k = b.k

图片.png

  1. select * from tableA a
  2. left join tableB b
  3. on a.k = b.k

图片.png

  1. select * from tableA a
  2. left join tableB b
  3. on a.k = b.k
  4. where b.k is null

图片.png

  1. // MySQL 不支持全外连接，固此种运行报错
  2. select * from tableA a
  3. full outer join tableB b
  4. on a.k = b.k
  6. // A 全部 + B 全部 再去重
  7. select * from tableA a
  8. left join tableB b
  9. on a.k = b.k
  10. union
  11. select * from tableA a
  12. right join tableB b
  13. on a.k = b.k

图片.png

  1. // MySQL 不支持全外连接，固此种运行报错
  2. select * from tableA a
  3. full outer join tableB b
  4. on a.k = b.k
  5. where a.k is null 
  6. (或者 b.k is null)
  8. // A 独有 + B 独有
  9. select * from tableA a
  10. left join tableB b
  11. on a.k = b.k
  12. where b.k is null
  13. union
  14. select * from tableA a
  15. right join tableB b
  16. on a.k = b.k
  17. where a.k is null

事务 & 隔离级别

事务的四大特性 (ACID)

原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

  • 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样

  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

    ACID 里的 AID 都是数据库的特征，也就是依赖数据库的具体实现。而唯独这个 C，实际上它依赖于应用层，也就是依赖于开发者。这里的一致性是指：系统从一个正确的状态，迁移到另一个正确的状态。当前的状态满足预定的约束就叫做正确的状态。而事务具备 ACID 里 C 的特性是说：通过事务的 AID 来保证我们的一致性

  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

    摘抄自菜鸟教程 MySQL

并发事务带来的问题

脏读：事务 a 读取到了事务 b 已修改但未提交的数据
不可重复读：事务 a 读取到了事务 b 已经提交的修改数据，不符合隔离性
幻读：事务 a 读取到了事务 b 提交的新增数据，不符合隔离性

事务的隔离级别

SQL 标准定义了四个隔离级别：

  • read uncommitted (读未提交)：最低的隔离级别，允许读取尚未提交的数据
  • read committed (读已提交)： 允许读取并发事务已经提交的数据
  • repeatable read (可重复读)： 对同一字段的多次读取结果都是一致的
  • serializable (串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行
隔离级别 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read × × √
serializable × × ×

InnoDB 存储引擎默认使用 repeatable read 隔离级别

重要概念

关系是一张表，表中的每行 (即数据库中的每条记录) 就是一个元组，每列就是一个属性
码 ：码就是能唯一标识实体的属性，对应表中的列
候选码 ： 若一张表中的某一属性或属性组的值能唯一的标识一个元组，而其任何子集都不能再标识，则称该属性组为候选码

例如：在学生实体中，” 学号” 能唯一的区分学生实体，同时又假设 “姓名” 、” 班级” 的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码

主码 : 也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码
外码 : 也叫外键。如果一张表中的一个属性是另外一张表中的主键，则这个属性为外码
主属性 ： 候选码中出现过的属性称为主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
非主属性： 不包含在任何一个候选码中的属性称为非主属性

函数依赖：在一张表中，在属性 (或属性组) X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y
部分函数依赖：在一张表中，如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0 → Y，则称 Y 对 X 部分函数依赖

比如：学生基本信息表 R (学号，身份证号，姓名）当然学号属性取值是唯一的，在 R 关系中，(学号，身份证号) -> (姓名)，(学号) -> (姓名)，(身份证号) -> (姓名)，所以姓名部分函数依赖与 (学号，身份证号)

完全函数依赖：在一张表中，如果 X → Y，并且不存在 X 的一个真子集 X0，使得 X0 → Y，则称 Y 对 X 完全函数依赖

比如学生基本信息表 R (学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，(学号，班级) -> (姓名)，但是 (学号) -> (姓名) 不成立，(班级) -> (姓名) 不成立，所以姓名完全函数依赖与(学号，班级)

传递函数依赖 ： 在关系模式 R(U) 中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，(X ∪ Y) ∩ Z = 空集合，则称 Z 传递函数依赖 于 X。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。

比如在关系 R (学号，姓名，系名，系主任) 中，学号 → 系名，系名 → 系主任，所以存在非主属性 系主任 对于 学号 的传递函数依赖

数据库范式

第一范式： 字段不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了
判断是否符合 第二范式：看数据表中是否存在非主属性对于码的部分函数依赖。若不存在，则符合
判断是否符合 第三范式：看数据表中是否存在非主属性对于码的传递函数依赖。若不存在，则符合

若符合前三范式，基本上解决了数据冗余，插入异常，修改异常，删除异常的问题

以前的笔记，不知道对不对

第一范式：数据库表的每一列都不可再分，也就是原子性 第二范式：在满足第一范式基础上要求每个字段都和主键完整相关，而不是仅和主键部分相关（主要针对联合主键而言） 第三范式：表中的非主键字段和主键字段直接相关

性能分析

性能下降 SQL 慢的几点原因

  • 查询语句写的烂
  • 索引失效
  • 关联查询太多 join
  • 服务器调优及 参数设置不合理（缓冲、线程数）

MySQL常见瓶颈

  • CPU：CPU 饱和一般发生在数据装入内存 或 从磁盘上读取数据的时候
  • IO：磁盘 I / O 瓶颈发生在装入数据 远大于 内存容量的时候

  • 服务器硬件：可通过 top，free，iostat 和 vmstat 来查看系统的性能状态

    explain

    功能

  • 使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的

  • 分析查询语句 和 表结构的性能瓶颈
  • 表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询

基本用法：Explain + SQL语句
Explain 执行计划包含的信息解读

show profile


索引

什么情况下需要建索引

需要创建索引的情况

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段 应该创建索引
  • 查询与其它表关联的字段，外键关系建立索引
  • 查询中排序的字段应该 创建索引
  • 查询中统计或者分组字段 应该创建索引

不要创建索引的情况

  • 频繁更新的字段 不适合创建索引
  • where 条件里用不到的字段 不创建索引
  • 表记录太少 不适合创建索引
  • 经常增删改的表 不适合创建索引

  • 数据重复且分布平均的表字段 不适合创建索引

    索引的优点 & 缺点

  • 提高数据检索效率，降低数据库的 IO 成本

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
  • 虽然索引大大提高了查询速度，却会降低更新的速度，如对表进行 insert、update、delete

    因为更新表时，MySQL 不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，会调整因为更新所带来的键值变化后的索引信息

索引优化

join 语句的优化

  • 尽可能减少 join 语句中的 NestedLoop 的循环总次数，永远用小结果集 驱动 大结果集
  • 优先优化 NestedLoop 的内层循环
  • 保证 join 语句中被驱动表上 join 条件字段已经被索引
  • 当无法保证被驱动表的 join 条件字段被索引，并且内存资源充足的前提下，不要太吝惜 JoinBuffer 的设置

索引失效

  • 如果索引了多列，要遵守最左前缀原则。指的是：查询从索引的最左前列开始并且不跳过索引中的列
  • 在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效
  • 存储引擎不能使用索引中 范围条件 右边的列
  • 尽量使用覆盖索引(只访问索引的查询 ( 索引列和查询列一致))，减少select *
  • mysql 在使用不等于(!= 或者<>)的时候 会导致索引失效
  • is null、is not null、like 以通配符开头 ( ‘%abc…’ ) 会导致索引失效
  • 字符串不加单引号 会导致索引失效
  • 少用 or，用它来连接 会导致索引失效

优化总结口诀

  • 全值匹配我最爱，最左前缀要遵守
  • 带头大哥不能死，中间兄弟不能断
  • 索引列上少计算，范围之后全失效
  • LIKE百分写最右， 覆盖索引不写星
  • 不等空值还有or，索引失效要少用

    小表驱动大表

    即 小的数据集 驱动 大的数据集
    类似嵌套循环 ```sql a 表与 b 表的 id 字段应建立索引 当 b 表的数据集 < a 表的数据集时，用 in 优于 exists select from a where id in (select id from b) 等价于(内外层循环) for select id from b for select from a where a.id = b.id

当 a 表的数据集 < b 表的数据集时，用 exists 优于 in select from a where exists (select from b where b.id = a.id) 等价于 for select from a for select from b where b.id = a.id

select … from table where exists ( subquery ) 该语法的理解：将主查询的数据，放到子查询中做条件验证， 根据验证结果 (返回 true、false) 来决定主查询的数据是否保留

  1. <a name="MQGor"></a>
  2. ### order by 优化
  3. MySQL 支持两种方式的排序，FileSort 和 index<br />Index 效率高. 它指 MySQL 扫描索引本身完成排序。FileSort 方式效率较低<br />order by 子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序<br />order by 满足两情况，会使用 index 方式排序
  5. - order by 语句满足最左前缀原则
  6. - 使用 where 子句与  order by 子句条件列组合满足最左前缀原则
  8. FileSort 有两种算法
  10. - 双**路排序**
  12. MySQL 4.1之前使用 双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by 列，        对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出<br />。从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。
  14. - **单路排序**
  16. 取一批数据，要对磁盘进行了两次扫描，众所周知，I \ O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。<br />从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并把随机 IO 变成了顺序 IO ，但是它会使用更多的空间， 因为它把每一行都保存在内存中了<br />  优化策略
  18. - 增大 `max_length_for_sort_data` 参数的设置
  19. - 增大 `sort_buffer_size` 参数的设置
  21. **小总结**<br />![图片.png](https://cdn.nlark.com/yuque/0/2021/png/12877145/1633356875105-c7e2482b-edbe-4d91-bdb5-3f0dd3b2cb38.png#clientId=u2f6458dd-d284-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=440&id=u372210b5&margin=%5Bobject%20Object%5D&name=%E5%9B%BE%E7%89%87.png&originHeight=411&originWidth=561&originalType=binary&ratio=1&rotation=0&showTitle=false&size=123078&status=done&style=shadow&taskId=uedf2bb3d-6e38-46b0-8de6-509daa75d6d&title=&width=600)
  22. <a name="ECbYD"></a>
  23. ### group by 优化
  24. group by 实质是先排序后分组，遵照索引的最左前缀原则<br />当无法使用索引列，增大 `max_length_for_sort_data` 参数的设置 + 增大 `sort_buffer_size` 参数的设置<br />where 高于 having，能写在 where 限定的条件就不要去 having 限定
  25. <a name="S3Tqb"></a>
  26. ## 锁机制
  27. 索引失效导致行锁变表锁
  28. <a name="uVt0g"></a>
  29. ### 锁的分类
  31. - 从数据操作的类型分
  32.    - 读锁 ( 共享锁 )：针对同一份数据，多个读操作可以同时进行而不会互相影响
  33.    - 写锁 ( 排它锁 )：当前写操作没有完成前，它会阻塞其他的读和写
  34. - 从对数据操作的粒度分
  35.    - 表锁：开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
  36.    - 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
  38. MyISAM 在执行查询语句 ( select ) 前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁
  40. - 对 MyISAM 表的读操作 ( 加读锁 )，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作
  41. - 对 MyISAM 表的写操作 ( 加写锁 )，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
  42. - 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞
  44. **间隙锁**<br />当我们用 范围条件 ，而不是用 相等条件 检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做 " 间隙 "，InnoDB 也会对这个 " 间隙 " 加锁，这种锁机制就是 间隙锁 ( Next - Key锁 )<br />**间隙锁的危害**<br />间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
  45. <a name="j5er6"></a>
  46. ### 锁的分析
  47. 查看哪些表被加锁了：` mysql> show open tables`
  48. <a name="vRV61"></a>
  49. #### 表锁
  50. 通过检查 `table_locks_waited` 和 `table_locks_immediate` 状态变量来分析 MySQL 内部表级锁定的情况：`mysql> show status like 'table%'`
  52. - Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加 1
  53. - Table_locks_waited：出现表级锁定争用而发生等待的次数 ( 不能立即获取锁的次数，每等待一次锁值加1 ) ，此值越高则说明表级锁争用情况越严重
  55. ![图片.png](https://cdn.nlark.com/yuque/0/2021/png/12877145/1633405034491-263d10f2-2592-4d5f-854e-a1d93d9b2690.png#clientId=u3191b1ae-d85c-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=327&id=u3a6b2e8b&margin=%5Bobject%20Object%5D&name=%E5%9B%BE%E7%89%87.png&originHeight=171&originWidth=314&originalType=binary&ratio=1&rotation=0&showTitle=false&size=3882&status=done&style=shadow&taskId=u19f68578-ad92-4bf8-afcd-9e8b60fb27a&title=&width=600)
  56. <a name="AbTVT"></a>
  57. #### 行锁
  58. 通过检查 `InnoDB_row_lock` 状态变量来分析 MySQL 内部行级锁定的情况<br />`mysql> show status like 'innodb_row_lock%'`
  60. - Innodb_row_lock_current_waits：当前正在等待锁定的数量
  61. - Innodb_row_lock_time：从系统启动到现在锁定总时长
  62. - Innodb_row_lock_time_avg：每次等待所花平均时间
  63. - Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
  64. - Innodb_row_lock_waits：系统启动后到现在等待的总次数
  66. ![图片.png](https://cdn.nlark.com/yuque/0/2021/png/12877145/1633405007926-4ca32c63-decc-43d9-bed7-17118a1176fb.png#clientId=u3191b1ae-d85c-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=284&id=u2209599f&margin=%5Bobject%20Object%5D&name=%E5%9B%BE%E7%89%87.png&originHeight=170&originWidth=359&originalType=binary&ratio=1&rotation=0&showTitle=false&size=4853&status=done&style=shadow&taskId=ud30205e1-071b-4f3c-98b6-28208d4a45c&title=&width=600)
  67. <a name="IZRiD"></a>
  68. #### 优化建议
  69. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁<br />合理设计索引，尽量缩小锁的范围<br />尽可能较少检索条件，避免间隙锁<br />尽量控制事务大小，减少锁定资源量和时间长度<br />尽可能低级别事务隔离
  70. <a name="WQYc7"></a>
  71. ## 主从复制
  73. <a name="OGylH"></a>
  74. ### 复制的基本原理
  75. slave 会从 master 读取 binlog 来进行数据同步<br />MySQL 的复制是异步 且 串行化的<br />**步骤 + 原理图**
  77. 1. master 将改变 记录到二进制日志 ( binary log )。这些记录过程叫做二进制日志事件 (binary log events )
  78. 2. slave 将 master 的 二进制日志事件拷贝到它的中继日志 ( relay log )
  79. 3. slave 重新做中继日志中的事件，将改变应用到自己的数据库中
  81. ![图片.png](https://cdn.nlark.com/yuque/0/2021/png/12877145/1633407264318-7b60b021-e143-4a61-b733-7086a2e6f98b.png#clientId=u3191b1ae-d85c-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=293&id=uff23d8de&margin=%5Bobject%20Object%5D&name=%E5%9B%BE%E7%89%87.png&originHeight=262&originWidth=536&originalType=binary&ratio=1&rotation=0&showTitle=false&size=92188&status=done&style=shadow&taskId=uca079042-f60a-4a0b-b4eb-0080e98bc82&title=&width=600)
  82. <a name="VC6kA"></a>
  83. ###   一主一从常见配置
  84. 要求
  86. - 主机 从机都关闭防火墙
  87. -  修改配置文件后，请重启后台 mysql 服务
  88. - 主从都配置在 [mysqld] 结点下，都是小写
  90. **主机修改 **`**my.ini**`** 配置文件 ( 在 window 系统 )**
  91. ```javascript
  92. [必须]主服务器唯一ID
  93. server-id=1
  94. [必须]启用二进制日志
  95. log-bin=自己本地的路径/mysqlbin
  96. log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
  97.   [可选]启用错误日志
  98.   log-err=自己本地的路径/mysqlerr
  99.   log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
  100.   [可选]根目录
  101.   basedir="自己本地路径"
  102.   basedir="D:/devSoft/MySQLServer5.5/"
  103.   [可选]临时目录
  104.   tmpdir="自己本地路径"
  105.   tmpdir="D:/devSoft/MySQLServer5.5/"
  106.   [可选]数据目录
  107.   datadir="自己本地路径/Data/"
  108.   datadir="D:/devSoft/MySQLServer5.5/Data/"
  109.   read-only=0
  110.   主机，读写都可以
  111.   [可选]设置不要复制的数据库
  112.   binlog-ignore-db=mysql
  113.   [可选]设置需要复制的数据库
  114.   binlog-do-db=需要复制的主数据库名字

图片.png
从机修改 **my.cnf** 配置文件 ( 在 Linux 系统 )

  1.  [必须]从服务器唯一ID
  2.  [可选]启用二进制日志

图片.png
在 Windows 主机上建立帐户并授权 slave
依次执行
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456'
flush privileges
查询master的状态：mysql> show master status。记录下 File 和 Position 的值
图片.png
执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
在 Linux 从机上配置需要复制的主机
依次执行
mysql> CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字
启动从服务器复制功能 ：start slave
mysql> show slave status\G，如果下面两个参数都是 Yes，则说明主从配置成功！

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

停止从服务复制功能：stop slave

分区分库分表

配置文件

二进制日志 log - bin

  • 主从复制

错误日志 log - error

  • 默认是关闭的。用于记录严重的警告和错误信息，每次启动和关闭的详细信息等

查询日志 log

  • 默认是关闭的，记录查询的 sql 语句，如果开启会降低 mysql 的整体性能，因为记录日志需要消耗系统资源

数据文件

  • frm 文件：存放表结构
  • myd 文件：存放表数据
  • myi 文件：存放表索引

    零散知识

    在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
    如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 order by
    如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序
    1. SELECT * 
    2. FROM runoob_tbl
    3. ORDER BY CONVERT(runoob_title using gbk);

常见的问题

MyISAM & InnoDB 存储引擎对比

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行 / 表锁 表锁 行锁


缓存

只缓存索引，不缓存真实数据		 既缓存索引，还缓存真实数据
对内存要求较高，内存大小对性能有决定性影响
表空间 小 大
关注点 性能 事务
默认安装 Y Y
  • 表锁：操作一条记录也会锁住整个表，不适合高并发的操作
  • 行锁：操作时只锁某一行，不对其它行有影响，适合高并发的操作

Myisam 的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

DateTime & Timestamp

DateTime 类型没有时区信息，即时区无关。DateTime 类型保存的时间都是当前会话所设置的时区对应的时间
Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器的时区变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样
DateTime 类型耗费空间更大
Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小 ( 1970-01-01 00:00:01 到 2038-01-19 03:14:07 [UTC时区的时间] )

DateTime可存储时间的范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
MySQL 将 timestamp 类型的值保存时，会从当前时区转成UTC时间，转换之后的值必须在上面范围

时区问题

delete & drop & truncate 的区别

  • delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除
  • truncate 删除表中全部数据后，再插入数据时自增长 id 又从 1 开始，在清空表中数据的时候使用
  • delete 是 DML 语句，可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
  • 执行的速度上，drop > truncate > delete
    1. drop table 表名
    2. truncate table 表名
    3. delete from 表名 where 列名=值

    数据库设计的步骤

  1. 需求分析 : 分析用户的需求，包括数据、功能 和 性能需求
  2. 概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图
  3. 逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换
  4. 物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径
  5. 数据库实施 : 包括编程、测试和试运行
  6. 数据库的运行和维护 : 系统的运行与数据库的日常维护