花于陌上开个人站

第七章 索引与触发器 v1.0 ​

一、索引相关语法 ​

  1. 主键索引（Primary key）：特殊的唯一索引，非空且唯一。
  2. 唯一索引（Unique index）：限制索引列中的值必须唯一，但允许为空值。
  3. 全文索引（Fulltext index）：使用倒排索引进行存储，适合按照单词进行全文检索，但不支持没有单词界定符的语言，如中文、日语和韩语等。
  4. 普通索引（Index）：没有额外限制，主要用于提高查询速度。

使用全文索引时，可以执行如下查询：

sql
SELECT *
FROM documents
WHERE MATCH(content) AGAINST ('keyword');

二、索引底层原理 ​

1.B树（多路查找树） ​

B树（多路查找树）

2.B+树（MySQL实际使用） ​

B+树

B树与B+树的区别：

  1. 若需要随机查找，二者查找方式相同，不过 B+ 树需要到叶子结点才能提供实际记录的访问。
  2. B树若需要按顺序查找，需要多次经过分支节点；B+树可以通过指针遍历所有关键字。
  3. B+树更适合范围查询，能够在叶子结点按照顺序找到符合范围的所有记录。

3.联合索引 ​

联合索引

  1. 最左匹配原则：如索引 ABC，支持 A、AB、ABC 几种组合走索引进行查找。
  2. 索引字段查询应尽量出现在 WHERE 条件中；当 WHERE 条件使用 OR 时，索引可能无法生效。

4.页分裂 ​

由于在 B+ 树索引中，当一个索引页已经满了，后续新增的数据可能位于之前节点的中间，插入时 MySQL 需要进行页分裂操作，为新的键腾出空间。这会带来额外开销，例如磁盘 I/O 和索引重建。

因此 MySQL 中通常建议使用整型自增主键，尽量让新增数据追加到后面，减少数据页分裂。

三、触发器 ​

1.创建触发器 ​

sql
CREATE TRIGGER [IF NOT EXISTS] `触发器名` <BEFORE|AFTER> <INSERT|UPDATE|DELETE>
    ON `表名` FOR EACH ROW
BEGIN
    <触发器主体>
END;
  • <BEFORE|AFTER>：表示触发器是在激活语句之前还是之后触发。
  • <INSERT|UPDATE|DELETE>：表示触发事件的类型。
  • <触发器主体>：触发器激活时执行的 SQL 语句，执行多个语句时使用 BEGIN...END。
  • OLD 和 NEW 在触发器中分别代表旧数据和新数据。UPDATE 可以使用二者，INSERT 只能使用 NEW，DELETE 只能使用 OLD。

2.修改与删除触发器 ​

触发器不能直接更新或覆盖。修改触发器时，需要先删除，再重新创建。

sql
DROP TRIGGER [IF EXISTS] [数据库名] `触发器名`;
SHOW TRIGGERS;