Day14：数据库

目标：

1
2
3
1. 数据库索引（★★★★★）
2. SQL 为什么会慢（★★★★★）
3. 数据库事务（★★★★★）

1. 为什么数据库索引像书的目录？为什么不用全表扫描？

索引本质是一种帮助数据库快速定位数据的数据结构，就像书的目录一样。如果没有索引，数据库只能从第一条记录开始逐行扫描；有了索引，可以直接定位到目标数据所在的位置，大幅减少扫描的数据量。

2. 为什么数据库大部分索引使用B+Tree为什么不用Hash？

数据库需要的不只是等值查询，还需要范围查询和排序，因此选择了 B+Tree。

3. 为什么 LIKE ‘%abc’ 容易索引失效？

B+Tree 索引按照前缀有序存储，LIKE ‘abc%’ 可以利用前缀定位，而 LIKE ‘%abc’ 无法确定起始位置，因此通常无法利用索引。

4. SQL查询很慢你一般怎么排查？

  1. EXPLAIN ANALYZE 查看执行计划
  2. 看有没有Seq Scan，有说明是全表扫描
  3. 看看索引为什么没用。例如：
    • 没索引
    • 索引失效
    • 返回数据太多，优化器觉得全表扫描更快
    • 类型转换
    • 函数计算
  4. 如果SQL没问题再看是不是JOIN、数据量、IO的问题

5. 为什么数据库索引不是越多越好？很多人觉得每个字段都建索引这样最快真的吗？

  1. 占用存储空间，每个索引本身就是一棵 B+Tree，需要额外磁盘和内存。
  2. 写入性能下降（最重要），不仅要修改数据，还要维护所有相关索引。
  3. 优化器选择成本增加，索引太多，数据库还要判断到底使用哪一个索引，执行计划可能反而变复杂。

索引能提高查询效率，但会增加存储开销和写入成本，因此不是越多越好，而是应该建立在高频查询条件上的索引。

Day15：数据库 B+Tree 和联合索引

最左前缀原则

联合索引遇到范围查询（>、<、BETWEEN、LIKE ‘abc%’），范围查询后面的列通常不能继续用于索引定位。

1. 为什么数据库索引不用红黑树？

红黑树是二叉树，数据多的话树会很高，那么就需要查询多次。

数据库查询最大的成本不是 CPU，而是磁盘 IO。数据库优化的核心就是减少磁盘 IO。

2. 为什么最左前缀不是数据库规定而是B+Tree决定？

联合索引中的数据是按照 (A,B,C) 的顺序在 B+Tree 中排序的，因此只能先利用 A，再利用 B，再利用 C，而不能跳过前面的字段直接利用后面的字段。

3. 为什么范围查询以后，后面的索引不能继续定位？

因为范围查询之后是一系列数据，需要对这一系列数据进行筛选扫描，不能直接定位。

4. 什么叫覆盖索引？

查询的所有字段都在索引中，不需要回主表读取数据，就叫覆盖索引。

5. 什么叫回表为什么回表慢？

索引需要先查到id然后回主表查询需要的字段。多一次 B+Tree 查找所以慢。

回表不是又执行一条 SQL，而是数据库内部又走了一次索引查找。

Day16：数据库

目标

1
2
3
4
5
1. 聚簇索引 vs 非聚簇索引（★★★★★）
2. MySQL 和 PostgreSQL 索引区别（★★★★★）
3. MVCC（★★★★☆）
4. 数据库隔离级别（★★★★★）
5. 结合你的项目

聚簇索引 vs 非聚簇索引

mysql的主键索引的叶子节点直接就是整条数据。

PostgreSQL没有聚簇索引，叶子节点保存的是 “TID（Tuple ID）磁盘位置”。

MVCC

数据库通过保存数据的多个版本，让读操作和写操作尽量不互相阻塞。

例如两个事务事务A正在查询，事务B更新数据。为什么事务A不用等？因为MVCC。
事务A看Version1，事务B写Version2。不用加锁。

数据库隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
Read Uncommitted
读未提交。

Read Committed（PG默认）
读已提交。

Repeatable Read（MySQL默认）
可重复读。

Serializable
串行化。
最安全。最慢。

脏读

1
读到了别人没提交的数据。

不可重复读

同一条数据两次查询结果不同。

幻读

第一次10条。
第二次11条。
因为别人新增了一条。

1. 为什么MySQL主键查询通常最快？

因为mysql采用聚簇索引，主键索引的叶子结点就是数据本身。

二级索引需要先找到主键，再回到聚簇索引查找完整数据，因此主键查询通常只需要一次 B+Tree 查找，而二级索引可能需要两次。

2. 为什么PostgreSQL没有InnoDB那种聚簇索引？

PostgreSQL 的索引叶子节点存的是 磁盘位置 TID（Tuple ID），本质上就是一条记录在数据文件中的位置。

3.MVCC解决什么问题?为什么减少锁？

通过多个数据版本，让读和写分开操作。

读操作读取历史版本，不需要等待写事务释放锁，因此读写可以并发执行。

4. 数据库四种隔离级别?MySQL默认哪个?PostgreSQL默认哪个？

读未提交、读已提交、可重复读、串行化。

mysql默认可以重复度。pg默认读已提交。

5. 你的GIS平台很多人同时查询分析任务, 有人修改状态为什么数据库不会所有查询都阻塞？

因为mvcc，修改状态和查询并不是同一个数据版本。

6. 为什么 UUID 不适合作为 MySQL 主键？

因为UUID随机。插入B+tree可能会插到中间，B+tree频繁分裂性能下降。
建议有自增趋势的。