mysql索引性能的测试

1、为了验证索引对mysql查询的影响，先写一个测试脚本

import mysql.connector
import time

# 连接MySQL数据库
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

# 创建测试表
cursor.execute("CREATE TABLE IF NOT EXISTS test_table (id INT AUTO_INCREMENT PRIMARY KEY, value INT NOT NULL);")

# 插入测试数据
cursor.execute("""
    INSERT INTO test_table (value)
    SELECT FLOOR(RAND() * 1000) FROM (
        SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) a
    CROSS JOIN (
        SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) b
    CROSS JOIN (
        SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) c;
""")
conn.commit()

# 测试查询性能（不带索引）
start_time = time.time()
cursor.execute("SELECT * FROM test_table WHERE value = 42")
results = cursor.fetchall()
end_time = time.time()
time_without_index = end_time - start_time
print(f"Query time without index: {time_without_index} seconds")

# 添加索引
cursor.execute("CREATE INDEX IF NOT EXISTS idx_value ON test_table (value);")
conn.commit()

# 测试查询性能（带索引）
start_time = time.time()
cursor.execute("SELECT * FROM test_table WHERE value = 42")
results = cursor.fetchall()
end_time = time.time()
time_with_index = end_time - start_time
print(f"Query time with index: {time_with_index} seconds")

# 比较查询时间
print(f"Time difference: {time_without_index - time_with_index} seconds")

# 关闭连接
cursor.close()
conn.close()