数据库的小知识

Gogo1121
50 阅读9分钟

​

Unix 时间戳

  • 定义：它表示从 "Unix纪元" (即 1970年1月1日 00:00:00 UTC) 开始，到某个特定时间点所经过的总秒数。
  • 整数部分：1748164363 是总秒数。
  • 小数部分：.8 代表更精确的时间，这里是 0.8 秒（即800毫秒）。

DATETIME跟timestamp这两者的区别

​

编辑

主键的作用

除了唯一性和作为唯一标识符

其一：自动创建索引，提升查询速度，当你为一个或多个列设置为主键时，数据库会自动为它创建一个非常高效的索引。这个索引通常是“聚簇索引”（Clustered Index），意味着数据在物理存储上就是按照主键的顺序排列的。

  • 结果：通过主键进行查询是所有查询方式中速度最快的。当你需要从几百万甚至上亿条记录中找某一条时，利用主键几乎可以瞬时完成。

其二：建立表与表之间的关系

在关系型数据库中，表与表之间常常需要关联。主键是建立这种关联的桥梁。一张表的主键，可以作为另一张表的外键 (Foreign Key) ，从而将两张表联系起来。

  • 例子：
    • 你有一个 Users (用户) 表，主键是 user_id。
    • 你还有一个 Orders (订单) 表，里面有一个 user_id 列。
    • 通过将 Orders 表中的 user_id 指向 Users 表的主键 user_id，你就建立了一个关系：“这个订单是属于哪个用户的”。这样你就可以轻松地查询到某个用户的所有订单。

总之，主键是保证数据完整性、提升数据库性能以及构建复杂数据关系的基石，是整个数据库设计的核心。

关于联合索引

这是一个更细微但很重要的性能优化点。您已经有了一个联合主键 PRIMARY KEY (account_id, article_id)，根据MySQL索引的 “最左前缀原则” (Leftmost Prefix Rule) ：

  • 一个建立在 (A, B) 列上的索引，可以有效地用于查询 A 列，或者查询 A 和 B 两列。
  • 在您的情况下，您的主键索引是 (account_id, article_id)，所以当您用 WHERE account_id = ? 进行查询时，数据库已经可以高效地利用这个主键索引了

数据库ACID、事务、隔离级别、锁

用一个大家都能理解的场景——银行转账——来把这些概念串起来。

第一站：地基 —— ACID 原则

想象一下，A要给B转账100元。这个操作至少包含两步：

  1. A的账户余额 - 100元。
  2. B的账户余额 + 100元。

为了保证这个操作绝对正确，不出任何差错，数据库必须遵循 ACID 四个基本原则。ACID是数据库事务必须具备的四个特性。

  1. A - Atomicity (原子性)
    • 含义：“All or Nothing”，一个事务中的所有操作，要么全部成功，要么全部失败。
    • 转账场景：不能出现A的钱扣了，但B没收到的情况。转账这个事务，必须是“扣款”和“收款”这两步同时成功。如果中间任何一步（比如B的账户被冻结）失败，整个事务就要回滚（Rollback），A的账户也要恢复到-100之前的状态，就像什么都没发生过一样。
  1. C - Consistency (一致性)
    • 含义：事务执行前后，数据库的完整性约束没有被破坏，数据状态是合法的。
    • 转账场景：假设A和B账户里原来总共有500元。无论他们之间怎么转账，转账结束后，两个账户的余额总和必须仍然是500元。不能凭空多出钱，也不能凭空消失。
  1. I - Isolation (隔离性)
    • 含义：多个并发事务之间是相互隔离的，一个事务的执行不应该被其他事务干扰。
    • 转账场景：在我给B转账的事务还没有完全结束（比如A扣了钱，B还没收到）的时候，另一个人来查询A和B的总余额，他不应该看到一个因为中间状态而导致的总额减少的“假象”。他看到的应该是转账前或者转账后的状态，而不应该是“中间态”。
  1. D - Durability (持久性)
    • 含义：一旦事务被提交（Commit），它对数据库的修改就是永久性的。即使随后系统崩溃，修改的数据也不会丢失。
    • 转账场景：只要系统提示转账成功，那么这笔交易就实实在在地完成了。哪怕此时银行的服务器断电重启，A的钱确定少了，B的钱也确定多了。

第二站：核心 —— 隔离级别 (Isolation Levels)

隔离性（Isolation）是ACID中最复杂、也是我们能“调节”的一项。完全的隔离（即所有事务排队执行）性能会很差。为了平衡性能和数据安全，数据库定义了四种隔离级别。

级别从低到高，安全性越来越强，但性能越来越差。

在解释级别之前，我们先要了解并发可能带来的三种问题：

  • 脏读 (Dirty Read) ：一个事务读到了另一个事务未提交的数据。这是最严重的问题。
  • 不可重复读 (Non-Repeatable Read) ：在一个事务内，多次读取同一行数据，结果却不一样（因为中间被其他事务修改并提交了）。侧重于UPDATE。
  • 幻读 (Phantom Read) ：在一个事务内，多次执行范围查询，结果返回的行数不一样（因为中间被其他事务插入或删除了数据）。侧重于INSERT和DELETE。

四种隔离级别与它们能解决的问题：

  1. 读未提交 (Read Uncommitted)
    • 级别最低。什么问题都解决不了，会发生脏读、不可重复读、幻读。基本不用。
  1. 读已提交 (Read Committed)
    • 解决了脏读。一个事务只能读到其他事务已经提交的数据。
    • 这是大多数数据库（如Oracle, PostgreSQL）的默认级别。但仍可能发生不可重复读和幻读。
  1. 可重复读 (Repeatable Read)
    • 解决了脏读、不可重复读。在一个事务内，对同一行数据的读取结果保证是一致的。
    • 这是MySQL InnoDB引擎的默认级别。理论上还会发生幻读，但InnoDB通过一种叫做Next-Key Lock的机制，在很大程度上也避免了幻读。
  1. 串行化 (Serializable)
    • 解决了所有问题。级别最高，所有事务完全串行化，一个接一个执行。
    • 数据绝对安全，但并发性能极差，就像公路变成了单行道，所有车都得排队。

第三站：工具 —— 锁 (Lock)

那么，数据库是如何实现上述的“隔离级别”的呢？答案就是通过锁。

锁就是一种控制共享资源访问的机制。当一个事务要操作某些数据时，会先“锁”住它，用完再“释放”。

最基本的锁分为两种：

  1. 共享锁 (Shared Lock / 读锁 / S锁)
    • 作用：一个事务对数据加上读锁后，其他事务也可以对它加读锁（大家可以一起读），但任何事务都不能对它加写锁（在我读的时候，谁都不能改）。
    • 比喻：大家可以一起看同一本书。
  1. 排他锁 (Exclusive Lock / 写锁 / X锁)
    • 作用：一个事务对数据加上写锁后，其他任何事务都不能再对它加任何锁（无论是读锁还是写锁），直到这个写锁被释放。
    • 比喻：有一个人正在书上写笔记，那他必须把书拿走，别人暂时没法看也没法写。

锁是如何实现隔离级别的？

  • 在读已提交级别下，SELECT语句不加锁，UPDATE/DELETE会加上写锁，事务一结束就释放。
  • 在可重复读级别下，SELECT语句会对读取的行加上读锁，UPDATE/DELETE会加上写锁，这些锁都要等到事务结束时才释放。这就保证了你在事务中重复读取时，数据不会被别人修改。

锁的粒度

  • 行锁 (Row Lock) ：只锁住被操作的一行或几行数据。并发性能好，是InnoDB的优势。
  • 表锁 (Table Lock) ：直接锁住整张表。开销小，但并发性能差，是MyISAM引擎的主要方式。

总结：它们的关系

为了保证业务正确，我们需要 ACID 原则 -> ACID 是通过 事务 来实现的 -> 事务的 隔离性(I) 有四种不同的级别 -> 数据库通过 锁 这种机制来实现不同的隔离级别。

理解了这组关系，你就掌握了关系型数据库事务管理的核心思想。

META 元数据

“Meta” 是一个前缀，源自希腊语，意思是“超越”、“之后”或“关于其自身”。在计算机科学和信息技术领域， “Meta”通常是“Metadata”（元数据）的简称。

元数据的核心定义是： “关于数据的数据” (Data about data)

通俗易懂的例子

想象一下我们生活中的事物：

  • 一本书：
    • 数据 (Data): 书中的故事情节、文字内容。
    • 元数据 (Metadata): 书的封面、标题、作者、出版社、国际标准书号（ISBN）、出版日期、页数。这些信息不是故事本身，而是描述这本书的数据。
  • 一张数码照片：
    • 数据 (Data): 照片本身的图像像素，也就是你看到的画面。
    • 元数据 (Metadata): 拍摄这张照片的日期和时间、使用的相机型号、光圈大小、快门速度、GPS地理位置。这些信息不是画面的一部分，而是描述这张照片的数据。

所以，当我们在数据库或程序中提到 META 字段时，我们指的是一个用来存储“上下文”或“描述性信息”的通用空间。

它的主要特点和作用是：

  1. 提供上下文： 它为核心数据提供了背景信息，让我们能更全面地理解数据。
  2. 灵活性和扩展性： 因为您无法预知未来需要记录哪些上下文信息，所以使用一个通用的 TEXT 字段（如 meta_log）来存放这些元数据，意味着未来增加新的描述信息时，不需要修改数据库表结构，只需要您的应用程序在存入时增加新的键值对即可。
  3. 分离关注点： 数据库只负责“忠实记录”，而应用程序（API）负责“理解和解释”这些元数据的含义。这是一种非常清晰和高效的设计模式。

简单来说，META 就是那些描述核心数据、为其提供背景和上下文的附加信息。

​

avatar
文章
阅读
粉丝