一文读懂数据库锁、索引存储结构以及数据库查询优化策略

原创 已于 2025-12-05 17:02:09 修改 · 置顶 · 643 阅读 · 18 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #sql

于 2025-12-05 11:51:15 首次发布

数据库锁

数据库锁用于管理并发访问，确保数据一致性。

锁介绍

共享锁（S锁）
允许多个事务同时读取数据，但阻止其他事务获取排他锁（读读共享）。适用于读操作。

排他锁（X锁）
独占锁，阻止其他事务读取或修改数据（读写互斥）。适用于写操作。

意向锁
表级锁，表明事务将在表中某些行上获取共享锁或排他锁。分为意向共享锁（IS）和意向排他锁（IX）。

行锁
行锁粒度更细，减少锁冲突，使用for update。

表锁
表锁开销小，但并发性低，一般在同步或备份数据使用

死锁处理
通过超时机制或死锁检测（如等待图算法）解决。

锁列分析对比

锁类型级别/用途如何手动使用主要优点主要缺点核心场景
共享锁（S锁）行级/读操作SELECT ... LOCK IN SHARE MODE;允许多事务并发读阻塞其他事务的排他锁请求高并发查询
排他锁（X锁）行级/写操作SELECT ... FOR UPDATE;确保数据修改的独占性阻塞其他所有锁请求更新或删除数据
意向共享锁（IS）表级/预示行级S锁隐式由数据库管理快速判断表内是否有行级S锁增加锁管理开销辅助行级S锁的批量操作
意向排他锁（IX）表级/预示行级X锁隐式由数据库管理快速判断表内是否有行级X锁增加锁管理开销辅助行级X锁的批量操作
行锁行级/细粒度控制UPDATE ... WHERE ...;（默认触发）减少锁冲突，提高并发锁管理开销较大高频单行修改
表锁表级/粗粒度控制LOCK TABLES ... READ/WRITE;实现简单，开销低并发性差数据迁移或备份

关键说明

  • 死锁处理：通过设置锁超时（如innodb_lock_wait_timeout）或启用死锁检测（如InnoDB的等待图算法）自动回滚事务。
  • 锁升级：当行锁数量超过阈值时，数据库可能自动升级为表锁（如SQL Server）。
  • 隐式锁：某些操作（如DML语句）会自动加锁，无需手动声明。

代码示例（MySQL)

-- 显式加共享锁  
SELECT * FROM employees WHERE id = 1 LOCK IN SHARE MODE;  

-- 显式加排他锁  
SELECT * FROM employees WHERE id = 1 FOR UPDATE;  

-- 显式表锁（需手动释放）  
LOCK TABLES employees WRITE;  
UNLOCK TABLES;

数据库索引

索引总览

1、按存储结构分类（最核心）

索引类型数据结构工作原理适用场景优点缺点支持的数据库
B树/B+树索引平衡多路查找树分层索引，有序存储，支持范围扫描大多数场景：主键、外键、范围查询、排序1. 支持范围查询和排序
2. 高度平衡，查询稳定
3. 适合磁盘存储（减少I/O）
4. 支持前缀匹配		1. 插入/删除需维护平衡（成本高）
2. 空间占用较大
3. 全模糊查询（LIKE '%xx'）无效		MySQL(InnoDB)、Oracle、SQL Server、PostgreSQL
哈希索引哈希表计算哈希值，直接定位等值查询、内存表、热点数据加速1. 等值查询极快（O(1)）
2. 内存占用相对较小		1. 不支持范围查询
2. 不支持排序
3. 不支持部分索引列查询
4. 哈希冲突影响性能		MySQL(MEMORY引擎)、PostgreSQL、SQL Server
全文索引倒排索引分词→文档映射，建立词元索引文本搜索、内容检索1. 支持自然语言搜索
2. 支持相关性排序
3. 支持布尔搜索		1. 占用空间大
2. 创建维护成本高
3. 停用词处理复杂		MySQL(MyISAM/InnoDB)、SQL Server、PostgreSQL
空间索引R树/R*树空间分区，最小边界矩形地理位置查询、GIS系统1. 高效支持空间查询
2. 支持空间关系计算
3. 适合多维数据		1. 实现复杂
2. 仅适用于空间数据
3. 更新成本较高		MySQL(MyISAM/InnoDB)、PostGIS、Oracle Spatial
位图索引位图向量列值→位图映射，位运算过滤数据仓库、低基数列、OLAP1. 空间占用小
2. 多列查询快（位运算）
3. 适合OLAP分析		1. 不适合高基数列
2. 更新代价大（锁整个位图）
3. 不适合OLTP高频更新		Oracle、SQL Server、PostgreSQL

2、按物理存储分类

索引类型存储方式数据与索引关系查询过程优点缺点典型实现
聚簇索引数据即索引表数据按索引顺序物理存储直接通过索引找到数据行1. 主键查询极快
2. 范围查询高效
3. 避免回表开销		1. 插入依赖主键顺序
2. 更新主键代价高
3. 二级索引需回表		InnoDB主键索引
非聚簇索引索引与数据分离索引存储指向数据的指针先查索引，再通过指针找数据1. 索引文件小
2. 创建维护灵活		1. 需要回表（二次查找）
2. 查询性能相对较低		MyISAM索引、InnoDB二级索引

3、按逻辑功能分类

索引类型唯一性空值约束数量限制主要用途创建语法示例
主键索引✅ 唯一❌ 不允许NULL1个/表1. 唯一标识行
2. 建立表关联		CREATE TABLE t(id INT PRIMARY KEY)
ALTER TABLE t ADD PRIMARY KEY(col)
唯一索引✅ 唯一✅ 允许NULL多个/表1. 数据完整性约束
2. 避免重复数据		CREATE UNIQUE INDEX idx_name ON t(col)
ALTER TABLE t ADD UNIQUE(col)
普通索引❌ 不唯一✅ 允许NULL多个/表1. 加速查询
2. 优化连接查询		CREATE INDEX idx_name ON t(col)
ALTER TABLE t ADD INDEX idx_name(col)
组合索引可选各列分别约束多个/表1. 多列查询优化
2. 覆盖索引
3. 避免回表		CREATE INDEX idx ON t(col1, col2, col3)
前缀索引可选同普通索引多个/表1. 节约存储空间
2. 长文本字段索引		CREATE INDEX idx ON t(col(10))
（取前10个字符）
函数索引可选同普通索引多个/表1. 优化函数表达式查询
2. 大小写不敏感查询		CREATE INDEX idx ON t(LOWER(col))
CREATE INDEX idx ON t(YEAR(date_col))
覆盖索引索引类型实现方式查询结果来源性能优势创建要点
组合索引的特殊应用索引包含所有查询列直接从索引获取，无需访问数据表1. 避免回表操作
2. 极大提升查询性能		将SELECT和WHERE中的列都包含在索引中

4、索引特性对比矩阵

维度B+树索引哈希索引全文索引位图索引空间索引
等值查询优秀极佳差（需全文匹配）极佳差
范围查询优秀不支持不支持差（需扫描）优秀
排序支持优秀不支持有限（相关性排序）不支持差
模糊查询前缀匹配优秀
后缀匹配差		不支持优秀不支持不支持
更新性能中等优秀差极差中等
空间占用中等小极大小（低基数时）大
适用基数高/中/低高/中文本数据低基数空间数据
典型场景OLTP系统
通用查询		缓存表
热点数据		搜索引擎
内容检索		数据仓库
分析系统		GIS系统
地图应用

5、高级索引类型（特殊实现）

索引类型数据库说明适用场景
自适应哈希索引MySQL(InnoDB)自动为热点数据创建内存哈希索引热点数据等值查询加速
倒排索引Elasticsearch全文索引的分布式实现大规模全文搜索
布隆过滤器索引PostgreSQL概率数据结构，快速判断"不存在"减少不必要磁盘I/O
GiST索引PostgreSQL通用搜索树，支持多种数据类型自定义数据类型索引
GIN索引PostgreSQL倒排索引，支持数组、全文搜索多值列、全文搜索
列存储索引SQL Server按列存储，高度压缩数据仓库、分析查询

6、聚簇索引&非聚簇索引

6.1. 聚簇索引存储（InnoDB）
-- User表：id为主键，name有二级索引

-- 物理存储结构：
聚簇索引B+Tree：
┌─────────────────────────────────┐
│         Root Page               │
│  [id:20, id:40, id:60]          │
└──────────┬────────┬─────────────┘
           │        │        │
     ┌─────┘   ┌────┘   ┌────┘
     ▼         ▼        ▼
┌─────────┐┌─────────┐┌─────────┐
│叶子页1   ││叶子页2   ││叶子页3   │ ← 叶子节点=数据页
│id=1-19   ││id=20-39  ││id=40-59  │
│实际数据行││实际数据行││实际数据行│
└─────────┘└─────────┘└─────────┘

关键特性：

  • 叶子节点就是数据页，存储完整行数据
  • 数据按主键顺序物理存储
  • 表数据 = 聚簇索引
6.2. 非聚簇索引存储（InnoDB二级索引）
-- name列的二级索引存储：
非聚簇索引B+Tree：
┌─────────────────────────────────┐
│         Root Page               │
│  [name:'David', 'Frank']        │
└──────────┬────────┬─────────────┘
           │        │        │
     ┌─────┘   ┌────┘   ┌────┘
     ▼         ▼        ▼
┌─────────┐┌─────────┐┌─────────┐
│叶子页1   ││叶子页2   ││叶子页3   │ ← 只存索引键+主键
│'Alice'   ││'Bob'     ││'Charlie' │
│id:1,5,7  ││id:2,8    ││id:3,9    │
└─────────┘└─────────┘└─────────┘
   ↓         ↓         ↓
需要回表到聚簇索引获取完整数据！

关键特性：

  • 叶子节点存储：索引键值 + 主键值
  • 需要回表查询获取完整数据
  • 索引文件与数据文件分离
6.3、总结对比表
维度聚簇索引非聚簇索引优势方
数据组织按主键物理排序按插入顺序存储视场景
查询性能主键查询极快，范围查询高效所有查询需额外I/O✅ 聚簇
插入性能可能页分裂，较慢追加写入，较快✅ 非聚簇
更新性能更新主键代价高更新索引列代价高平手
空间占用节省主键索引空间索引独立存储，可能更多✅ 聚簇
并发控制行级锁，支持事务表级锁，不支持事务✅ 聚簇
恢复能力支持崩溃恢复恢复较慢✅ 聚簇
适用场景OLTP，点查询为主OLAP，批量加载各有所长

索引存储原理分析

主要以B+Tree和B-Tree来分析,核心区别是B-Tree,节点都存数据，B+Tree 内部节点索引，叶子节点底层以双向链表的来处理

1、数据表结构与示例数据

User表结构

CREATE TABLE User (
    id INT PRIMARY KEY,            -- 主键（聚簇索引）
    name VARCHAR(50),              -- 用户名
    age INT,                       -- 年龄
    sex CHAR(1),                   -- 性别：'M'/'F'
    createDate DATETIME            -- 创建时间，
    email VARCHAR(100),            -- 邮箱
    city VARCHAR(50),              -- 城市
    status TINYINT                 -- 状态
);

-- 创建联合索引（name, sex）
CREATE INDEX idx_name_sex ON User(name, sex);

模拟数据（7条，更丰富的场景）

idnamesexagecreateDate实际磁盘地址
1AliceF252024-01-01 10:00:000x1000
2BobM302024-01-02 11:00:000x2000
3CharlieM282024-01-03 12:00:000x3000
4DavidM352024-01-04 13:00:000x4000
5EveF222024-01-05 14:00:000x5000
6FrankM402024-01-06 15:00:000x6000
7AliceM262024-01-07 16:00:000x7000

联合索引 (name, sex) 的索引键值

1>. 索引键值生成与排序规则

联合索引 (name, sex) 的完整键值：(name, sex, id)
先按name排序，name相同按sex排序，都相同按id排序

排序过程：
原始数据 -> 排序后：
(Alice, F, 1)    # Alice F组，按id排序
(Alice, M, 7)    # Alice M组
(Bob, M, 2)      # Bob
(Charlie, M, 3)  # Charlie
(David, M, 4)    # David
(Eve, F, 5)      # Eve
(Frank, M, 6)    # Frank

排序键值数组：
1. ('Alice', 'F', 1)   → 0x1000
2. ('Alice', 'M', 7)   → 0x7000
3. ('Bob', 'M', 2)     → 0x2000
4. ('Charlie', 'M', 3) → 0x3000
5. ('David', 'M', 4)   → 0x4000
6. ('Eve', 'F', 5)     → 0x5000
7. ('Frank', 'M', 6)   → 0x6000

2、B-Tree结构存储（假设3阶）

B-Tree结构图（数据分散在各层）
             [ (David,M,4) ]                      ← 根节点（存储数据）
             /              \
            /                \
    [ (Alice,F,1) ]       [ (Eve,F,5) ]          ← 中间节点（存储数据）
    /      |      \       /      |      \
[ ] [(Alice,M,7)] [(Bob,M,2)] [(Charlie,M,3)] [(Frank,M,6)] ← 叶子节点（存储数据）
B-Tree节点详细内容（假设每节点最多3个键值）
节点1：根节点
┌─────────────────────────────────────────────────────┐
│ B-Tree Root Node (Page)                             │
├─────────┬──────────────┬─────────┬──────────────────┤
│ Ptr0    │ Key1         | DataPtr1│ Ptr1             │
│ (→节点2)│ (David,M,4)  │ →0x4000 │ (→节点3)         │
└─────────┴──────────────┴─────────┴──────────────────┘
↑ 存储了(David,M,4)的实际数据指针！
节点2：左子树中间节点
┌─────────────────────────────────────────────────────┐
│ B-Tree Non-Leaf Node                                │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Ptr1         │   │
│ (→叶子A)│ (Alice,F,1)  │ →0x1000 │ (→叶子B)     │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘
↑ 存储了(Alice,F,1)的实际数据指针！
节点3：右子树中间节点
┌─────────────────────────────────────────────────────┐
│ B-Tree Non-Leaf Node                                │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Ptr1         │   │
│ (→叶子C)│ (Eve,F,5)    │ →0x5000 │ (→叶子D)     │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘
叶子节点A-D
叶子节点A（最左边）：
┌─────────────────────────────────────────────────────┐
│ B-Tree Leaf Node                                    │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Key2         │   │
│ (NULL)  │ (空槽位)     │         │ (空槽位)     │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘

叶子节点B：
┌─────────────────────────────────────────────────────┐
│ B-Tree Leaf Node                                    │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Key2         │   │
│ (→叶子A)│ (Alice,M,7)  │ →0x7000 │ (Bob,M,2)    │   │
├─────────┼──────────────┼─────────┼──────────────┼───┤
│ DataPtr2│ Ptr1         │ DataPtr3│              │   │
│ →0x2000 │ (→叶子C)     │ →0x...  │              │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘

叶子节点C：
┌─────────────────────────────────────────────────────┐
│ B-Tree Leaf Node                                    │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Key2         │   │
│ (→叶子B)│ (Charlie,M,3)│ →0x3000 │ (空槽位)     │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘

叶子节点D：
┌─────────────────────────────────────────────────────┐
│ B-Tree Leaf Node                                    │
├─────────┬──────────────┬─────────┬──────────────┬───┤
│ Ptr0    │ Key1         | DataPtr1│ Key2         │   │
│ (→叶子C)│ (Frank,M,6)  │ →0x6000 │ (空槽位)     │   │
└─────────┴──────────────┴─────────┴──────────────┴───┘
B-Tree查询示例
-- 查询1：查找 name='Alice', sex='M'
1. 访问根节点：[(David,M,4)]
   Alice < David，走左子树（Ptr0）
2. 访问中间节点：[(Alice,F,1)]
   找到(Alice,F,1)，但我们要找(Alice,M,7)
   继续向下走Ptr1（右子树）
3. 到达叶子节点B：[(Alice,M,7), (Bob,M,2)]
   ✅ 找到(Alice,M,7)，直接返回数据指针→0x7000

-- 查询2：范围查询 name BETWEEN 'C' AND 'E'
1. 查找'Charlie'：根节点→左子树→叶子节点C
   找到(Charlie,M,3) →0x3000
2. 继续找'David'：需要回溯到根节点
   从根节点开始：(David,M,4)就在根节点！
   直接返回→0x4000
3. 找'Eve'：从根节点重新开始
   根节点：Eve > David，走右子树
   中间节点：[(Eve,F,5)]，找到→0x5000
   ⚠️ 多次随机回溯，效率低！

3、B+Tree结构存储（3阶）

B+Tree结构图（数据只在叶子节点）
            [ David ]                            ← 根节点（纯索引）
            /       \
           /         \
    [ Alice ]       [ Eve ]                     ← 非叶子节点（纯索引）
    /       \       /       \
   |         |     |         |
   ▼         ▼     ▼         ▼
┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐
│叶子1 │ │叶子2 │ │叶子3 │ │叶子4 │ ← 叶子节点链表
│Alice │ │Alice │ │Bob   │ │Charlie│
│F,1   │ │M,7   │ │M,2   │ │M,3   │
└──────┘ └──────┘ └──────┘ └──────┘
   ↓         ↓         ↓         ↓
┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐
│David │ │Eve   │ │Frank │ │      │
│M,4   │ │F,5   │ │M,6   │ │      │
└──────┘ └──────┘ └──────┘ └──────┘
B+Tree节点详细内容
节点1：根节点（纯索引）
┌─────────────────────────────────────────┐
│ B+Tree Root Node                        │
├─────────┬─────────┬─────────┬───────────┤
│ Ptr0    │ Key1    │ Ptr1    │           │
│ (→节点2)│ "David" │ (→节点3)│           │
└─────────┴─────────┴─────────┴───────────┘
↑ 只存索引键"David"，不存数据指针！
规则：键值 < "David" 走Ptr0，≥ "David" 走Ptr1
节点2：左非叶子节点
┌─────────────────────────────────────────┐
│ B+Tree Non-Leaf Node                    │
├─────────┬─────────┬─────────┬───────────┤
│ Ptr0    │ Key1    │ Ptr1    │           │
│ (→叶子1)│ "Alice" │ (→叶子2)│           │
└─────────┴─────────┴─────────┴───────────┘
↑ 只存"Alice"作为分隔键
节点3：右非叶子节点
┌─────────────────────────────────────────┐
│ B+Tree Non-Leaf Node                    │
├─────────┬─────────┬─────────┬───────────┤
│ Ptr0    │ Key1    │ Ptr1    │           │
│ (→叶子3)│ "Eve"   │ (→叶子4)│           │
└─────────┴─────────┴─────────┴───────────┘
叶子节点1-4（双向链表连接）
叶子节点1：
┌─────────────────────────────────────────────────────┐
│ B+Tree Leaf Node                                    │
├──────────────┬──────────────┬───────────────────────┤
│ Key1         | DataPtr1     │ Next → 叶子2          │
│ (Alice,F,1)  │ →0x1000      │ Prev ← NULL           │
├──────────────┼──────────────┼───────────────────────┤
│ Key2         | DataPtr2     │                       │
│ (空槽位)     │              │                       │
└──────────────┴──────────────┴───────────────────────┘

叶子节点2：
┌─────────────────────────────────────────────────────┐
│ B+Tree Leaf Node                                    │
├──────────────┬──────────────┬───────────────────────┤
│ Key1         | DataPtr1     │ Next → 叶子3          │
│ (Alice,M,7)  │ →0x7000      │ Prev ← 叶子1          │
├──────────────┼──────────────┼───────────────────────┤
│ Key2         | DataPtr2     │                       │
│ (空槽位)     │              │                       │
└──────────────┴──────────────┴───────────────────────┘

叶子节点3：
┌─────────────────────────────────────────────────────┐
│ B+Tree Leaf Node                                    │
├──────────────┬──────────────┬───────────────────────┤
│ Key1         | DataPtr1     │ Next → 叶子4          │
│ (Bob,M,2)    │ →0x2000      │ Prev ← 叶子2          │
├──────────────┼──────────────┼───────────────────────┤
│ Key2         | DataPtr2     │                       │
│ (Charlie,M,3)│ →0x3000      │                       │
└──────────────┴──────────────┴───────────────────────┘

叶子节点4：
┌─────────────────────────────────────────────────────┐
│ B+Tree Leaf Node                                    │
├──────────────┬──────────────┬───────────────────────┤
│ Key1         | DataPtr1     │ Next → 叶子5          │
│ (David,M,4)  │ →0x4000      │ Prev ← 叶子3          │
├──────────────┼──────────────┼───────────────────────┤
│ Key2         | DataPtr2     │                       │
│ (Eve,F,5)    │ →0x5000      │                       │
└──────────────┴──────────────┴───────────────────────┘

叶子节点5：
┌─────────────────────────────────────────────────────┐
│ B+Tree Leaf Node                                    │
├──────────────┬──────────────┬───────────────────────┤
│ Key1         | DataPtr1     │ Next → NULL           │
│ (Frank,M,6)  │ →0x6000      │ Prev ← 叶子4          │
├──────────────┼──────────────┼───────────────────────┤
│ Key2         | DataPtr2     │                       │
│ (空槽位)     │              │                       │
└──────────────┴──────────────┴───────────────────────┘
B+Tree查询示例
-- 查询1：查找 name='Alice', sex='M'
1. 访问根节点：["David"]
   Alice < David，走左子树（Ptr0）
2. 访问非叶子节点：["Alice"]
   等于"Alice"，需要特殊处理：走右子树（Ptr1）
3. 到达叶子节点2：[(Alice,M,7)]
   ✅ 找到(Alice,M,7)，返回数据指针→0x7000
   ⚠️ 必须到达叶子节点才能获取数据！

-- 查询2：范围查询 name BETWEEN 'C' AND 'E'
1. 查找'Charlie'：
   根节点："David" → 左子树
   非叶子节点："Alice" → 右子树
   叶子节点3：找到(Charlie,M,3) →0x3000
2. 沿叶子链表顺序遍历：
   叶子节点3：下一个是(David,M,4) →0x4000
   叶子节点4：(Eve,F,5) →0x5000（停止）
   ✅ 无需回溯，顺序扫描效率高！

-- 查询3：所有女性用户（sex='F'）
1. 无法有效使用索引（最左前缀原则）
2. 但可以利用叶子链表：
   - 从第一个叶子节点开始遍历
   - 检查每个键值的sex字段
   - 虽然要扫描所有叶子节点，但至少是顺序I/O

4、总结：为什么数据库选择B+Tree

特性B-Tree表现B+Tree表现胜出方
等值查询可能更快（少I/O）稳定3次I/OB-Tree（轻微优势）
范围查询多次随机回溯链表顺序扫描✅ B+Tree
全表扫描遍历所有节点只遍历叶子✅ B+Tree
排序查询需要额外排序天然有序✅ B+Tree
插入维护相对简单需要维护链表B-Tree
缓存效率数据分散索引集中缓存✅ B+Tree

索引优化

** 基于User表来演示 **
** 优化层次**
┌─────────────────┐
│ 覆盖索引 │ ← 最有效
├─────────────────┤
│ 索引下推 │
├─────────────────┤
│ 合理索引设计 │
├─────────────────┤
│ 避免索引失效 │
├─────────────────┤
│ SQL重写优化 │
└─────────────────┘

索引设计核心原则

选择合适列建立索引

– ✅ 适合建索引的列：

– 1. WHERE条件中的高频列
CREATE INDEX idx_name ON User(name); – 高频查询条件
CREATE INDEX idx_city_status ON User(city, status); – 组合条件

– 2. JOIN连接列
CREATE INDEX idx_id ON User(id); – 主键已有，用于JOIN

– 3. ORDER BY/GROUP BY列
CREATE INDEX idx_createDate ON User(createDate); – 按时间排序

– 4. 高选择性列（区分度高）
– 先计算选择性
SELECT
COUNT(DISTINCT name)/COUNT() as name_sel, – 0.8 高选择性
COUNT(DISTINCT sex)/COUNT() as sex_sel, – 0.1 低选择性
COUNT(DISTINCT age)/COUNT(*) as age_sel – 0.6 中等
FROM User;

– ❌ 不适合建索引的列：
– 1. 低选择性列（如性别、状态）
CREATE INDEX idx_sex ON User(sex); – 不建议，区分度太低

– 2. 大文本字段
CREATE INDEX idx_email ON User(email(100)); – 如必须，用前缀索引

联合索引设计原则

-- 联合索引列顺序：高频查询 + 高选择性 + 等值查询 优先

-- ✅ 优秀设计：
-- 1. 等值列在前，范围列在后
CREATE INDEX idx_city_age ON User(city, age);
-- 查询：WHERE city='北京' AND age>20（city等值，age范围）

-- 2. 高频条件在前
-- 假设查询模式：80%查询用city，20%用city+status
CREATE INDEX idx_city_status ON User(city, status);

-- 3. 考虑覆盖索引
-- 常用查询：SELECT name, age FROM User WHERE city='北京'
CREATE INDEX idx_city_name_age ON User(city, name, age);

-- ❌ 错误设计：
-- 1. 范围列在前
CREATE INDEX idx_age_city ON User(age, city);       -- 范围查询age会使city失效
-- 2. 冗余索引
CREATE INDEX idx_name ON User(name);                -- 冗余
CREATE INDEX idx_name_sex ON User(name, sex);       -- 前导列相同

索引数量控制原则

-- 黄金法则：每表不超过5-7个索引
-- 原因：
-- 1. 插入/更新/删除时需要维护所有索引
-- 2. 占用磁盘空间
-- 3. 优化器选择困难

-- 评估索引收益：
-- 读收益 vs 写成本
-- 热点查询 vs 全表扫描

索引覆盖（Covering Index）

什么是索引覆盖

-- 普通查询（需要回表）：
EXPLAIN SELECT * FROM User WHERE city='北京';
-- Extra: Using where

-- 覆盖索引查询（无需回表）：
EXPLAIN SELECT city, status FROM User WHERE city='北京';
-- Extra: Using index

如何实现索引覆盖

-- 方案1：创建包含所有查询列的索引
-- 查询：SELECT id, name, age FROM User WHERE city='北京' AND status=1
CREATE INDEX idx_city_status_covering ON User(city, status, id, name, age);
-- 索引包含：WHERE条件(city,status) + SELECT列(id,name,age)

-- 方案2：利用聚簇索引特性
-- InnoDB中，二级索引包含主键
-- 查询包含主键时天然覆盖
SELECT id, city FROM User WHERE city='北京';  -- 只需扫描二级索引

-- 方案3：前缀覆盖（减少索引大小）
-- 只索引必要的列部分
CREATE INDEX idx_name_part ON User(name(10));  -- 只索引前10字符

覆盖索引的优势

-- 性能对比：
-- 场景：查询北京用户数量（100万数据，10万北京用户）

-- 无覆盖索引：
SELECT COUNT(*) FROM User WHERE city='北京';
-- 1. 扫描city索引找到10万个主键
-- 2. 回表10万次读取数据
-- 总I/O：10万次随机I/O（慢！）

-- 有覆盖索引：
CREATE INDEX idx_city_covering ON User(city, id);
SELECT COUNT(*) FROM User WHERE city='北京';
-- 1. 扫描city索引统计数量
-- 2. 无需回表！
-- 总I/O：1次顺序扫描（快！）

索引下推（Index Condition Pushdown, ICP）

什么是索引下推

MySQL 5.6+引入，将WHERE条件下推到存储引擎层过滤，减少回表次数。

工作原理对比

-- 索引：idx_name_age (name, age)
-- 查询：SELECT * FROM User WHERE name LIKE '张%' AND age>25

-- ❌ 无ICP（MySQL 5.6前）：
1. 存储引擎：使用索引找到所有 name LIKE '张%' 的记录
2. 返回所有找到的主键给Server层
3. Server层：根据主键回表获取完整数据
4. Server层：在内存中过滤 age>25
-- 问题：回表了所有姓张的用户，即使age不满足

-- ✅ 有ICP（MySQL 5.6+）：
1. 存储引擎：使用索引找到所有 name LIKE '张%' 的记录
2. 存储引擎：在索引层直接过滤 age>25（如果age在索引中）
3. 只返回满足 age>25 的主键给Server层
4. Server层：回表获取这些数据
-- 优势：减少了回表次数

ICP生效条件

-- 条件1：二级索引（非聚簇索引）
-- 条件2：WHERE条件部分可用索引
-- 条件3：MySQL 5.6+，且默认开启

-- 查看ICP状态：
SHOW VARIABLES LIKE 'optimizer_switch';
-- index_condition_pushdown=on

-- 执行计划中识别ICP：
EXPLAIN SELECT * FROM User WHERE name LIKE '张%' AND age>25;
-- Extra: Using index condition  ← ICP生效

索引失效场景

违反最左前缀原则

-- 索引：idx_name_age_sex (name, age, sex)

-- ✅ 有效使用：
WHERE name='张三'                         -- 使用第一列
WHERE name='张三' AND age=25              -- 使用前两列
WHERE name='张三' AND age=25 AND sex='M'  -- 使用全部三列

-- ❌ 失效或部分失效：
WHERE age=25                             -- 跳过第一列，全表扫描
WHERE name='张三' AND sex='M'            -- 跳过age，只能用到name
WHERE age=25 AND sex='M'                 -- 跳过name，全表扫描

在索引列上使用函数或计算

-- ❌ 函数操作使索引失效：
WHERE YEAR(createDate) = 2024            -- 索引失效
WHERE LEFT(name, 2) = '张'               -- 索引失效
WHERE age + 1 > 20                       -- 索引失效

-- ✅ 优化方案：
WHERE createDate >= '2024-01-01' AND createDate < '2025-01-01'
WHERE name LIKE '张%'                     -- 前缀匹配，索引有效
WHERE age > 19                           -- 改写计算

使用范围查询导致后续索引列失效

-- 索引：idx_city_age_status (city, age, status)

-- ✅ 范围查询在最后：
WHERE city='北京' AND age=25 AND status>0  -- status范围，不影响前两列

-- ❌ 范围查询在中间导致后续列失效：
WHERE city='北京' AND age>20 AND status=1
-- age是范围查询，status无法使用索引（只能用到city,age）

-- ❌ 多个范围查询：
WHERE age>20 AND age<30 AND createDate>'2024-01-01'
-- 通常只能用到其中一个范围索引

使用不等于（!=, <>）

-- ❌ 不等于使索引失效：
WHERE status != 1        -- 全表扫描
WHERE name <> '张三'     -- 全表扫描

-- ✅ 优化方案：
-- 方案1：改为多个等值查询
WHERE status IN (0, 2, 3)        -- 如果值有限

-- 方案2：使用覆盖索引
CREATE INDEX idx_status_covering ON User(status, id);
-- 扫描索引而不是全表

-- 方案3：使用位运算
-- 假设status有多个标志位
WHERE (status & 1) = 0           -- 检查特定标志位

LIKE以通配符开头

-- ❌ 前导通配符使索引失效：
WHERE name LIKE '%三'             -- 全表扫描
WHERE name LIKE '%张%'            -- 全表扫描

-- ✅ 后缀通配符索引有效：
WHERE name LIKE '张%'             -- 使用索引

-- ✅ 优化方案：
-- 方案1：反向存储+索引
CREATE INDEX idx_name_reverse ON User(REVERSE(name));
WHERE REVERSE(name) LIKE '三张%'   -- 查找'张三'

-- 方案2：使用全文索引
CREATE FULLTEXT INDEX idx_name_ft ON User(name);
WHERE MATCH(name) AGAINST('三' IN BOOLEAN MODE);

-- 方案3：使用前缀索引
CREATE INDEX idx_name_prefix ON User(name(5));
WHERE name LIKE '张%'             -- 使用前缀索引

类型转换导致索引失效

-- 假设name是VARCHAR类型

-- ❌ 隐式类型转换：
WHERE name = 123                  -- MySQL将数字转为字符串，索引失效

-- ❌ 字符集不匹配：
-- 表字符集utf8，连接字符集latin1
WHERE name = '张三'               -- 需要转换，可能使索引失效

-- ✅ 保持类型一致：
WHERE name = '123'                -- 字符串比较，索引有效

OR条件导致索引失效

-- 索引：idx_name (name), idx_age (age)

-- ❌ OR条件可能使索引失效：
WHERE name='张三' OR age=25       -- 可能全表扫描

-- ✅ 优化方案：
-- 方案1：改为UNION
SELECT * FROM User WHERE name='张三'
UNION ALL
SELECT * FROM User WHERE age=25;

-- 方案2：使用覆盖索引
CREATE INDEX idx_name_age_covering ON User(name, age, id);
SELECT * FROM User 
WHERE (name='张三' OR age=25) 
  AND id IN (
    SELECT id FROM User WHERE name='张三'
    UNION
    SELECT id FROM User WHERE age=25
  );

IS NULL 和 IS NOT NULL

-- 索引：idx_name (name)

-- ❌ IS NOT NULL 可能使索引失效（取决于数据分布）：
WHERE name IS NOT NULL            -- 如果大部分name不为NULL，可能全表扫描

-- ✅ 优化方案：
-- 方案1：设置NOT NULL约束
ALTER TABLE User MODIFY name VARCHAR(50) NOT NULL;

-- 方案2：使用覆盖索引
CREATE INDEX idx_name_null_covering ON User(name, id);
SELECT id FROM User WHERE name IS NOT NULL;  -- 使用索引

复合索引列顺序错误

-- 错误顺序：范围列在前
CREATE INDEX idx_age_city ON User(age, city);
WHERE age>20 AND city='北京';     -- city无法使用索引

-- 正确顺序：等值列在前
CREATE INDEX idx_city_age ON User(city, age);
WHERE city='北京' AND age>20;     -- 两列都使用索引

数据量太少或全表扫描更快

-- 当数据量很小时，优化器可能选择全表扫描
-- 例如：User表只有10条记录
SELECT * FROM User WHERE name='张三';
-- 优化器认为全表扫描（读10行）比索引扫描（索引+回表）更快

高级优化技巧

索引合并（Index Merge）

-- MySQL自动将多个索引扫描结果合并
-- 索引：idx_name (name), idx_age (age)

EXPLAIN SELECT * FROM User 
WHERE name='张三' OR age=25;
-- type: index_merge
-- Extra: Using union(idx_name,idx_age); Using where

-- 注意：索引合并是最后手段，最好使用复合索引

索引跳跃扫描（Index Skip Scan）

-- MySQL 8.0.13+引入
-- 索引：idx_sex_age (sex, age)

-- 即使查询没指定前导列，也可能使用索引
EXPLAIN SELECT * FROM User WHERE age>25;
-- type: range
-- Extra: Using index for skip scan

-- 原理：遍历sex的不同值，对每个值使用age索引

自适应哈希索引

-- InnoDB自动为热点数据创建哈希索引
SHOW ENGINE INNODB STATUS\G
-- 查看哈希索引使用情况
-- Buffer pool and memory
-- -------------------------------------
-- Adaptive hash index 203MB

使用虚拟列或函数索引

-- MySQL 5.7+/8.0+支持
-- 优化函数查询

-- 创建虚拟列
ALTER TABLE User 
ADD COLUMN name_first_char CHAR(1) 
GENERATED ALWAYS AS (LEFT(name, 1)) STORED;

-- 在虚拟列上建索引
CREATE INDEX idx_name_first ON User(name_first_char);

-- 查询优化
SELECT * FROM User WHERE LEFT(name, 1) = '张';
-- 可以改为
SELECT * FROM User WHERE name_first_char = '张';
一文读懂 OceanBase 数据库的启动恢复代码解析
seeyousoonhhh的博客
09-03 483
作者简介：镜水，一个致力于无限进步的数据库学徒。 作者简介：海芊，一个致力于当网红的 OceanBase 文档工程师。个人频道：Amber loves OB 本文主要介绍 OceanBase 数据库启动时是如何将已持久化的日志和数据恢复到内存，重新形成各类信息（如租户信息、分区信息等）的内存映像，从而回到宕机前的状态。 在介绍具体的恢复流程之前，我们首先来了解一些与之相关的存储结构。 存储数据结构 MacroBlock OceanBase 数据库将数据分为增量数据和基线数据，基线数据是几乎占满整个磁盘的.
OceanBase 源码解读（七）：一文读懂数据库索引实现原理
OceanBase分布式数据库的博客
12-10 2022
此前，带你读源码第六篇《戳这里回顾：OceanBase 源码解读（六）：存储引擎详解》为大家详细讲解了OceanBase存储引擎，并为大家回答了关于 OceanBase 数据库的相关提问。 本期“源码解读”将尝试从代码导读的角度，简要介绍 OceanBase 的索引构建流程，带领大家读懂索引构建的相关代码，必须速速收藏！ 1. 什么是索引 首先请思考，在一个一般的数据库中，索引表的语义是什么？ 索引表，其实是在主表（也称数据表）之外，再创建一份冗余且有序的数据，用于加速某些查询流程。...
一文读懂MySQL的索引结构及查询优化
程序员八阿哥
01-04 369
(同时再次强调，这几篇关于MySQL的探究都是基于5.7版本，相关总结与结论不一定适用于其他版本) MySQL官方文档中(https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html)有这样一段描述： The best way to improve the performance of SELECT operations is...
向量数据库系列1：一文读懂向量数据库
何哥的博客
05-07 1702
向量数据库（Vector Database）是一种专门用于存储、管理和查询高维向量数据的数据库系统。随着人工智能（尤其是深度学习和自然语言处理）的快速发展，向量数据库成为处理非结构化数据（如图像、文本、音频等）的核心工具，广泛应用于相似性搜索、推荐系统、语义分析等场景。
一文读懂向量数据库，原理到应用全解析！
Datawhale
06-27 945
随着大语言模型等技术的发展，训练时使用的数据已经无法满足一些应用场景的需求（模型的数据只能停留在某个时候，比如Claude提示词就明确显式表示了时效性），这一部分知识就需要通过工具或者记忆模块，并且以提示词的形式告诉大语言模型。不同度量在不同数据库中的支持程度不一，如有的引擎只支持欧氏和余弦，有的还支持更复杂度量。早期的语言表示方法是符号化的离散表示（如独热编码、ti-idf词袋模型），但这些词之间没有距离的概念，比如“计算机”和“电脑”被认为是两个完全不同的词，继而引入了人工知识库，如同义词词典。
一文读懂数据库事务
热门推荐
Dablelv 的博客专栏。
04-19 2万+
首先说一下什么是事务。事务（Transaction）指一个操作，由多个步骤组成，要么全部成功，要么全部失败。比如我们常用的转账功能，假设A账户向B账号转账，那么涉及两个操作：（1）从A账户扣钱；（2）往B账户加入等量的钱。因为是独立的两个操作，所以可能有一个成功，一个失败的情况。但是因为在这种场景下，必须要保证事务，即要么同时成功，要么同时失败（一个失败需要回滚），不能存在从 A 账户扣钱成功，往 B 账户加入等量钱失败这种情况。
一文读懂数据库索引原理及优化
秃头哥编程
06-03 610
一、摘要 本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。 二、常见的查询算法及数据结构 为...
一文搞懂后端面试之数据库MySQL的各种锁以及锁优化【中间件 | 数据库 | MySQL | 锁机制】
LightOfNight的博客
08-03 969
遇事不决临键锁：可以认为，全部都是加临键锁的，除了下面两个子句提到的例外情况右边缺省间隙锁：例如你的值只有(1,4,7)三个，但是你查询的条件是where id < 5，那么加的是间隙锁，因为7本身就不在查询范围里。等值查询记录锁：针对的是主键和唯一索引，不适用于普通索引。
B+树 范围查询_一文读懂MySQL的索引结构及查询优化
weixin_39715538的博客
10-27 443
同时再次强调，这几篇关于MySQL的探究都是基于5.7版本，相关总结与结论不一定适用于其他版本)MySQL官方文档中有这样一段描述：The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the...
一文搞懂数据库中的“锁”（图文详解）
cy的Java学习笔记
03-29 3252
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁的分类**全局锁：**锁定数据库中的所有表。**表级锁：**每次操作锁住整张表。**行级锁：**每次操作锁住对应的行数据。
【SQL数据库备份入门秘籍】：一文读懂备份类型、策略和最佳实践
![sql数据库如何备份]...物理备份创建数据库文件的副本，而逻辑备份创建数据库结构和数据的副本。选择正确的备份类型取决于数据的重要性、备份频率和恢复时间目标。 # 2. SQL数据库备份类
C语言高级编程：一文读懂数据结构的四大逻辑与两大存储
Web安全工具库
09-05 1505
本文介绍了数据结构的基础知识，重点讲解了逻辑结构与物理结构的关系。逻辑结构分为集合、线性、树形和图形四种类型，物理结构主要包括顺序存储（如数组）和链式存储（如链表）。通过生动的比喻和代码示例，文章展示了数据结构与算法的紧密联系，强调选择合适的数据结构对提高程序效率的重要性。最后总结指出，任何逻辑结构都可以用不同物理结构实现，为初学者理清了数据结构的基本框架。
MIB数据库初学者必备：一文读懂OID结构，快速入门网络管理
[MIB数据库初学者必备：一文读懂OID结构，快速入门网络管理](https://arimas.com/wp-content/uploads/2016/04/Network-Optimization-Flowchart.png) # 摘要 本文旨在深入解析管理信息库（MIB）和对象标识符（OID）...
一文搞懂mysql索引底层逻辑
Cr1556648487的博客
09-13 564
在mysql中,索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。通过索引,查询数据时不用读完记录的所有信息,而只是查询索引列即可，索引是帮助Mysql高效获取数据且以排好序的数据结构，直观的说，索引就类似书的目录页，没有目录（即索引）我们就要一页一页的找，有了目录（索引）我们就可以按照目录中标记的页数去相应的页数去查找。
MySQL数据库的数据文件保存在哪？MySQL数据存在哪里
2509_94083514的博客
11-30 782
在安装好MySQL数据库使用一段时间后，会产生许多的数据库和数据。那这些数据库的数据文件存放在本地文件夹的什么位置呢一、默认位置MySQL创建的数据库实际上存储在文件系统的一个特定目录中，该目录通常为MySQL的数据目录。这个目录的默认位置依据操作系统和MySQL的安装方式不同而有所差异。、这个目录通常位于，它可能位于是个隐藏文件夹）**其中“X.X”是MySQL的版本号。目录（每个数据库都有一个与其同名的文件夹，而这个文件夹中包含了该数据库的所有表的文件，而其中的文件代表了数据库中的表。
【免费云平台部署指南】按场景选型+全维度对比（附直达地址）
分享技术干货，聚焦AI前沿科技
12-03 817
免费云平台已能覆盖从静态网站到AI模型部署的大部分场景，核心是根据项目类型（静态/后端/AI）、访问地区（国内/国外）和功能需求（数据库/容器/GPU）选择适配平台。新手建议从简单场景入手（如GitHub Pages部署博客、Hugging Face Spaces部署AI Demo），熟悉后再尝试全栈或容器化部署。
redis基本类型 5种基本类型
yeshihouhou的博客
11-30 266
List的另一个应用就是消息队列，可以利用List的 PUSH 操作，将任务存放在List中，然后工作线程再用 POP 操作将任务取出进行执行。我们在谈基础数据结构时，讨论的是存储值的数据类型，主要包括常见的5种数据类型，分别是：String、List、Set、Zset、Hash。Redis hash 是一个 string 类型的 field（字段） 和 value（值） 的映射表，hash 特别适合用于存储对象。Redis 中集合是通过哈希表实现的，所以添加，删除，查找的复杂度都是 O(1)。
python+django/flask+vue的基于协同过滤算法的体育商品推荐系统
最新发布
Q_Q1963288475的博客
12-04 488
相比于传统的体育商品推荐方式，个性化智能的管理方式可以大幅提高体育商品推荐的管理效率，实现了个性化智能体育商品推荐的标准化、制度化、程序化的管理，有效地防止了体育商品推荐的随意管理， 功能主要包括首页、个人中心、用户管理、商品分类管理、商品信息管理、交流论坛、留言板、系统管理、订单管理等功能，从而实现个性化智能体育商品推荐方式，提高个性化智能体育商品推荐的效率。
我们来学mysql -- undo log系列归档
一起学Coding
12-01 183
undo log系列归档
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值