分类目录归档:数据库

用 Python(PyMySQL/SQLAlchemy)安全操作数据库

用 Python 连 MySQL 时,安全第一原则是:永远不要把用户输入拼进 SQL 字符串。本文给出 PyMySQL 与 SQLAlchemy 的正确姿势。

PyMySQL 参数化查询

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    user="app",
    password="***",
    database="demo",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)

def get_user(user_id: int):
    sql = "SELECT id, name, email FROM users WHERE id=%s"
    with conn.cursor() as cur:
        cur.execute(sql, (user_id,))
        return cur.fetchone()

# 错误示范:f-string 拼接,有 SQL 注入风险
# cur.execute(f"SELECT * FROM users WHERE id={user_id}")

批量插入

rows = [("Alice", "a@x.com"), ("Bob", "b@x.com")]
sql = "INSERT INTO users(name, email) VALUES (%s, %s)"
with conn.cursor() as cur:
    cur.executemany(sql, rows)
conn.commit()

SQLAlchemy 核心写法(简版)

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://app:***@127.0.0.1:3306/demo?charset=utf8mb4")

with engine.begin() as conn:
    result = conn.execute(
        text("SELECT * FROM users WHERE city=:city"),
        {"city": "北京"},
    )
    for row in result.mappings():
        print(row)

工程实践清单

  • 连接信息放环境变量,不写进仓库
  • 生产库账号最小权限(不要随便用 root)
  • 读写超时、连接池、失败重试要有
  • 变更数据必须事务:成功 commit,失败 rollback

SQL 多表 JOIN 与子查询怎么选

真实业务数据分散在多张表:用户、订单、商品、支付。会写 JOIN,才能把「故事」拼完整。

四种 JOIN 直觉

  • INNER JOIN:两边都匹配才保留
  • LEFT JOIN:保留左表全部,右表没有则 NULL
  • RIGHT JOIN:少用,能写成 LEFT 就写成 LEFT
  • FULL JOIN:MySQL 不直接支持,常用 UNION 模拟

示例:用户与订单

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01';

想找「注册了但没下单」的用户:

SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

子查询 vs JOIN

-- 子查询:先找高价值用户,再查明细
SELECT *
FROM orders
WHERE user_id IN (
  SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) > 10000
);

-- 等价思路用 JOIN + 聚合派生表,往往更好优化
SELECT o.*
FROM orders o
JOIN (
  SELECT user_id
  FROM orders
  GROUP BY user_id
  HAVING SUM(amount) > 10000
) vip ON o.user_id = vip.user_id;

选型建议

  1. 关联取字段:优先 JOIN
  2. 存在性判断:可用 EXISTS
  3. 子查询结果很大时,注意重复计算,考虑临时表/派生表

MySQL 索引原理与慢查询排查入门

接口变慢,十有八九跟 SQL 和索引有关。理解 B+Tree 索引、会看执行计划,是后端和数据分析同学的共同必修课。

索引解决什么问题

没有索引时,MySQL 可能整表扫描;有合适索引时,可以快速定位到目标行。代价是:占用空间,拖慢写入。

最左前缀原则

-- 联合索引 (city, status, created_at)
SELECT * FROM orders WHERE city='北京' AND status='paid';           -- 能用
SELECT * FROM orders WHERE city='北京';                             -- 能用
SELECT * FROM orders WHERE status='paid';                           -- 通常用不上该联合索引

用 EXPLAIN 看执行计划

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status='paid';

重点看:

  • type:all 很危险;ref/range 通常可接受
  • key:实际用了哪个索引
  • rows:预估扫描行数
  • Extra:是否 Using filesort / Using temporary

慢查询怎么开

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记下来

常见优化思路

  1. 给 WHERE / JOIN / ORDER BY 高频字段建合适索引
  2. 避免在索引列上包函数:WHERE DATE(created_at)=...
  3. 只查需要的列,慎用 SELECT *
  4. 深分页改成基于上次最大 ID 的翻页
-- 深分页不友好
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 更好
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;