数据库简介

数据库的五大核心价值

• 高效存储：采用结构化方式减少冗余，节省存储空间；如MySQL单表可支持千万级数据量
• 快速检索：通过索引机制实现毫秒级查询；电商大促期间每秒处理数万订单查询
• 数据一致：通过ACID特性保障事务操作的完整性；银行转账时确保金额不丢失
• 安全可控：提供用户权限体系与审计日志；医疗系统严格限制医生仅查看本院患者数据
• 可靠持久：支持定时备份、主从复制与故障自动切换；支付宝系统99.99%可用性

ACID特性详解

ACID是事务处理的四大基石，缺一不可：

• Atomicity（原子性）：事务内所有操作要么全部成功，要么全部回滚。如转账操作中，扣款与入账必须同时成功或失败。
• Consistency（一致性）：事务前后数据必须满足预设规则（如外键约束、唯一索引）。即使系统崩溃，也不会出现中间状态数据。
• Isolation（隔离性）：并发事务之间互不干扰。通过隔离级别实现，但过度隔离会降低并发性能。
• Durability（持久性）：事务提交后，数据必须永久写入存储介质。依赖redo log（重做日志）和fsync机制保障。

案例警示：某电商平台在秒杀活动中未设置唯一索引，导致超卖——同一商品库存被多个订单同时减扣，最终亏本发货。

索引机制——查询性能的加速器

索引是数据库的“目录”，但滥用会导致写性能下降。主流索引类型：

最佳实践：复合索引遵循“最左前缀原则”。例如索引INDEX idx(name, age, city)，查询WHERE name='张三' AND age>25可命中索引，但WHERE age>25无法使用该索引。

事务隔离级别对比

为解决并发事务的三大问题（脏读、不可重复读、幻读），SQL标准定义了四级隔离级别：

MySQL特别说明：InnoDB引擎在REPEATABLE READ级别下，通过Next-Key Lock机制避免了幻读问题，实际表现接近SERIALIZABLE。

经典查询案例：用户行为分析

假设有一张用户行为日志表user_actions，结构如下：

CREATE TABLE user_actions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  action_type ENUM('click', 'view', 'add_cart', 'buy') NOT NULL,
  item_id INT,
  timestamp DATETIME,
  session_id VARCHAR(50)
);

需求1：统计每个用户的转化漏斗（点击→加购→购买）

SELECT
  user_id,
  COUNT(CASE WHEN action_type = 'click' THEN 1 END) AS clicks,
  COUNT(CASE WHEN action_type = 'add_cart' THEN 1 END) AS adds,
  COUNT(CASE WHEN action_type = 'buy' THEN 1 END) AS purchases
FROM user_actions
GROUP BY user_id
ORDER BY purchases DESC;

需求2：计算每个用户的平均转化周期（从首次点击到首次购买）

SELECT
  user_id,
  TIMESTAMPDIFF(HOUR,
    MIN(CASE WHEN action_type = 'click' THEN timestamp END),
    MIN(CASE WHEN action_type = 'buy' THEN timestamp END)
  ) AS conversion_hours
FROM user_actions
GROUP BY user_id
HAVING conversion_hours IS NOT NULL
AND conversion_hours > 0;

需求3：找出连续7天活跃的用户（窗口函数解法）

WITH daily_active AS (
  SELECT DISTINCT user_id, DATE(timestamp) AS active_date
  FROM user_actions
)
SELECT DISTINCT user_id
FROM (
  SELECT user_id, active_date,
         LAG(active_date, 6) OVER (PARTITION BY user_id ORDER BY active_date) AS prev_7th
  FROM daily_active
) t
WHERE DATEDIFF(active_date, prev_7th) = 6;

高级特性：CTE与窗口函数

CTE（Common Table Expression）让复杂查询结构清晰，支持递归查询：

-- 查询员工及其所有下属（递归CTE）
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE id = 1001  -- 从CEO开始
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

窗口函数可在不聚合数据的情况下计算累计值：

-- 计算每日销售额的累计值与占比
SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (ORDER BY order_date) AS cumulative_sales,
  ROUND(100 * total_amount / SUM(total_amount) OVER (), 2) AS daily_ratio
FROM daily_sales
ORDER BY order_date;

MongoDB核心优势

• 灵活schema：同一集合（collection）中文档可有不同字段，适应快速迭代的业务
• 高性能写入：无需预定义表结构，写入吞吐量可达MySQL的5-10倍
• 内置聚合管道：支持复杂数据处理，如：

  db.orders.aggregate([
    { $match: { status: "completed" } },
    { $group: {
        _id: "$category",
        total: { $sum: "$amount" },
        avg: { $avg: "$amount" }
      }
    },
    { $sort: { total: -1 } }
  ]);

• 地理空间查询：通过2dsphere索引支持复杂地理计算

Redis的多面手角色

Redis不仅是缓存，更是高性能数据结构服务器：

选择NoSQL的决策树

SQL层优化黄金法则

• 避免SELECT *：只查必要字段，减少网络传输与内存消耗
• 分页优化：大偏移量分页用“延迟关联”：

  -- 优化前（偏移100万时极慢）
  SELECT * FROM orders LIMIT 1000000, 20;
  -- 优化后（先查ID再关联）
  SELECT o.* FROM orders o
  INNER JOIN (
    SELECT id FROM orders
    ORDER BY create_time DESC
    LIMIT 1000000, 20
  ) t ON o.id = t.id;

• 避免隐式类型转换：WHERE phone="13800138000"（phone是INT类型）会导致全表扫描
• 慎用OR：改用UNION ALL（确保无重复时）：

  -- 慢：索引失效
  WHERE status=1 OR status=2
  -- 快：分别走索引
  WHERE status=1
  UNION ALL
  WHERE status=2

架构层优化策略

监控与诊断工具

• MySQL：慢查询日志（slow_query_log）、Performance Schema、pt-query-digest
• Redis：INFO命令、MONITOR（慎用）、RedisInsight可视化
• 通用：Prometheus+Grafana监控体系、ELK日志分析

诊断案例：某电商系统突然变慢，通过慢查询日志发现一条SQL：

SELECT * FROM products WHERE name LIKE '%手机%';

-- 方案1：改用全文索引（MySQL 5.6+）
-- 方案2：业务层改用Elasticsearch
-- 方案3：若必须模糊查询，限制结果集
SELECT id, name FROM products
WHERE name LIKE '%手机%'
ORDER BY sales DESC
LIMIT 100;

SQL注入防护指南

错误示例：

// PHP + MySQLi（危险！）
$sql = "SELECT * FROM users WHERE username = '" . $_GET['user'] . "'";

正确做法：

// 预处理语句（安全）
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $_GET['user']);
$stmt->execute();

额外防护：

• 最小权限原则：应用账号仅需SELECT/INSERT/UPDATE，禁用DROP/ALTER
• 输入验证：对数字ID做整型校验，对邮箱做格式验证
• WAF规则：部署Web应用防火墙拦截恶意请求

数据脱敏与加密

数据库级加密：

• 透明数据加密（TDE）：MySQL 8.0+支持InnoDB表空间加密
• 应用层加密：敏感字段用AES_ENCRYPT()存储

1. 云原生数据库普及

传统数据库迁移到云平台需解决三大挑战：

• 弹性伸缩：AWS Aurora自动扩缩容，CPU使用率从10%到100%动态调整
• 高可用性：跨可用区部署，故障切换时间<30秒
• 成本优化：按需付费+预留实例，节省30%-60%成本

代表产品：阿里云PolarDB、腾讯云TDSQL、Azure SQL Database

2. AI与数据库深度融合

• AI辅助SQL编写：通过自然语言生成SQL（如Amazon Q Business）
• 智能索引推荐：自动分析查询模式，建议最优索引
• 异常检测：基于历史数据预测性能瓶颈（如Redis内存预警）

案例：某金融公司用AI模型预测交易峰值，提前扩容，避免了系统崩溃。

3. 边缘数据库崛起

物联网设备产生海量数据，需在本地预处理：

• SQLite：轻量级嵌入式数据库（手机APP本地缓存）
• EdgeDB：专为边缘计算设计的新一代数据库

典型场景：自动驾驶汽车本地存储最近10秒视频帧，仅上传关键事件数据。