CRUD BOY实战（一）

验证你的SQL预编译生效了吗？

作业要求：

预编译定义：select * from sbtest1 where id=? 预编译就是将这个 SQL 模板发给 MySQL，将来要执行的时候只需要发送具体 id 值给 MySQL

优点：防止 SQL 注入；更好的性能

比如iBatis内置参数，形如#xxx#的，均采用了SQL预编译的形式：

那么 CRUD boy 这样写了就代表真正用上预编译了吗？也就是同一个 SQL 模板在不同的连接下真的用上PrepareStatement 了吗？

这次的问题是：你怎么确认这个预编译生效了？你如果让这个预编译真正生效？请对比一下预编译前后的性能差异你能扫描一下你们公司的业务，并确认预编译按照 crud的意愿正确生效了预编译的原理是？可以从代码层解释/也可以从SQL网络包层面来解释

  • 你怎么确认这个预编译生效了？
  • 你如果让这个预编译真正生效？
  • 请对比一下预编译前后的性能差异
  • 你能扫描一下你们公司的业务，并确认预编译按照 crud的意愿正确生效了
  • 预编译的原理是？可以从代码层解释/也可以从SQL网络包层面来解释

实验背景

SQL预编译是一种将SQL语句结构和参数分开发送的技术。当使用预编译时，先发送包含占位符的PREPARE命令，MySQL服务器解析并优化这个语句。之后，只需发送EXECUTE命令和参数值，无需每次发送完整SQL语句。

预编译的主要优点：

  • 防止SQL注入
  • 提升查询性能

实验环境

  • MacOS Sequoia 15.3
  • WireShark 4.4.3
  • MySQL 9.2.0 Homebrew
  • Python 3.9.6
  • mysql-connector-python 9.2.0 (Installed by pip3)

实验准备

1. 数据库初始化

CREATE DATABASE IF NOT EXISTS test_prepared;

USE test_prepared;

CREATE TABLE IF NOT EXISTS users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(255),

    age INT

);

INSERT INTO users (name, age) VALUES ("Alice", 30);

INSERT INTO users (name, age) VALUES ("Bob", 25);

INSERT INTO users (name, age) VALUES ("Calvin", 35);

2. Wireshark配置

  1. 选择loopback:lo0接口
  2. 设置过滤器：tcp port 3306 and host localhost
  3. 配置MySQL解析器，启用"Reassemble MySQL Protocol"
  4. Mac用户需要先安装chmodBPF

实验一：预编译验证

代码实现

import mysql.connector



# MySQL 连接信息

cnx = mysql.connector.connect(

    user = 'root',

    password = '123456',

    host = 'localhost',

    database = 'test_prepared',

    raise_on_warnings = True,

    ssl_disabled=True  # 禁用SSL以便Wireshark抓包

)



cursor = cnx.cursor()



# 1. 使用非预编译执行查询

age = 30

query_non_prepared = f"SELECT * FROM users WHERE age = {age}"

cursor.execute(query_non_prepared)

results = cursor.fetchall()

for row in results:

    print(row)



# 2. 使用预编译执行查询

query_prepared = "SELECT * FROM users WHERE age = %s"

data_prepared = (25,)

cursor.execute(query_prepared, data_prepared)

results = cursor.fetchall()

for row in results:

    print(row)



# 再次使用预编译，参数不同

data_prepared_2 = (35,)

cursor.execute(query_prepared, data_prepared_2)

results = cursor.fetchall()

for row in results:

    print(row)



cursor.close()

cnx.close()

抓包分析

1. 连接过程

  1. TCP三次握手
  2. ServerGreeting初始化连接，包含MySQL版本信息
  3. LoginRequest请求登录
  4. AuthSwitchRequest验证身份
  5. 请求和响应交互
  6. RequestQUIT请求关闭连接
  7. TCP四次挥手

2. 非预编译查询

  • 发送完整SQL查询语句
  • 每次查询都需要重新解析优化

3. 预编译查询（启用prepared=True后）

  1. 发送带占位符的查询语句
  2. 生成Statement ID
  3. 执行时只发送参数值
  4. 使用已编译的模板

实验二：性能对比

代码实现

import mysql.connector

import time



# MySQL 连接信息

cnx = mysql.connector.connect(

    user = 'root',

    password = '123456',

    host = 'localhost',

    database = 'test_prepared',

    raise_on_warnings = True,

    ssl_disabled=True

)



cursor = cnx.cursor(prepared=True)



# 查询次数

num_iterations = 1000



# 1. 非预编译查询性能测试

total_time_non_prepared = 0

age_non_prepared_values = [30, 25, 35]

for i in range(num_iterations):

    start_time = time.time()

    age = age_non_prepared_values[i % len(age_non_prepared_values)]

    query_non_prepared = f"SELECT * FROM users WHERE age = {age}"

    cursor.execute(query_non_prepared)

    cursor.fetchall()

    end_time = time.time()

    total_time_non_prepared += (end_time - start_time)



average_time_non_prepared = total_time_non_prepared / num_iterations

print(f"非预编译查询 {num_iterations} 次平均执行时间: {average_time_non_prepared:.6f} 秒")



# 2. 预编译查询性能测试

total_time_prepared = 0

age_prepared_values = [30, 25, 35]

query_prepared = "SELECT * FROM users WHERE age = %s"

for i in range(num_iterations):

    start_time = time.time()

    data_prepared = (age_prepared_values[i % len(age_prepared_values)],)

    cursor.execute(query_prepared, data_prepared)

    cursor.fetchall()

    end_time = time.time()

    total_time_prepared += (end_time - start_time)



average_time_prepared = total_time_prepared / num_iterations

print(f"预编译查询 {num_iterations} 次平均执行时间: {average_time_prepared:.6f} 秒")



cursor.close()

cnx.close()

测试结果

  • 非预编译查询平均时间：0.000391秒
  • 预编译查询平均时间：0.000243秒

实验结论

1. 预编译实现机制

  • 首次执行：
    • 发送SQL模板
    • MySQL解析优化
    • 生成Statement ID
  • 后续执行：
    • 仅发送参数值
    • 复用已编译模板

2. 性能优势

  • 避免重复解析SQL
  • 减少网络传输量
  • 服务器端复用执行计划

3. 实施建议

  • 显式启用prepared=True
  • 使用占位符代替直接值拼接
  • 复杂查询更适合使用预编译
  • 需权衡查询复杂度与预编译开销

4. 注意事项

  • 配置连接参数（如ssl_disabled）
  • 正确使用占位符语法
  • 考虑连接池和预编译语句缓存