Mysql-各项测试

1、前言：

1.1 MYsql 版本： 8.0.31

1.2 PC端测试

1.3、CPU参数：

内存参数：8G+8G=16G

mysql 表： 20个字段，2800w条数据

2、建表语句

    id INT PRIMARY KEY AUTO_INCREMENT,       -- 自增主键
    name VARCHAR(100) NOT NULL,              -- 用户名
    email VARCHAR(255),                      -- 邮箱
    password_hash CHAR(64),                  -- 密码哈希
    age INT,                                 -- 年龄
    gender ENUM('male', 'female', 'other'),  -- 性别
    phone_number VARCHAR(20),                -- 电话号码
    address TEXT,                            -- 地址
    postal_code CHAR(6),                     -- 邮政编码
    city VARCHAR(100),                       -- 城市
    country VARCHAR(100),                    -- 国家
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 创建时间
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 更新时间
    is_active BOOLEAN DEFAULT TRUE,          -- 是否激活
    last_login DATETIME,                     -- 上次登录时间
    login_attempts INT DEFAULT 0,            -- 登录尝试次数
    user_role ENUM('admin', 'user', 'guest'),-- 用户角色
    profile_picture_url VARCHAR(2083),       -- 头像URL
    bio TEXT,                                -- 个人简介
    preferences JSON                         -- 用户偏好设置
);

3、插入sql的Java代码

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Random;

public class BulkInsertExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database?useSSL=false&rewriteBatchedStatements=true";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";

    public static void main(String[] args) {
        int batchSize = 1000; // 每次批量插入的数据量
        int totalRecords = 20000000; // 总数据量
        String insertSQL = "INSERT INTO example_table (name, email, password_hash, age, gender, phone_number, address, postal_code, city, country, created_at, updated_at, is_active, last_login, login_attempts, user_role, profile_picture_url, bio, preferences) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), ?, ?, ?, ?, ?, ?, ?)";

        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            connection.setAutoCommit(false); // 禁用自动提交
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
                Random random = new Random();

                for (int i = 1; i <= totalRecords; i++) {
                    preparedStatement.setString(1, "User_" + i); // name
                    preparedStatement.setString(2, "user" + i + "@example.com"); // email
                    preparedStatement.setString(3, generateRandomHash()); // password_hash
                    preparedStatement.setInt(4, random.nextInt(80) + 18); // age (18-98)
                    preparedStatement.setString(5, random.nextBoolean() ? "male" : "female"); // gender
                    preparedStatement.setString(6, "123456789" + random.nextInt(10)); // phone_number
                    preparedStatement.setString(7, "Address_" + i); // address
                    preparedStatement.setString(8, String.format("%06d", random.nextInt(1000000))); // postal_code
                    preparedStatement.setString(9, "City_" + random.nextInt(100)); // city
                    preparedStatement.setString(10, "Country_" + random.nextInt(50)); // country
                    preparedStatement.setBoolean(11, true); // is_active
                    preparedStatement.setNull(12, java.sql.Types.TIMESTAMP); // last_login
                    preparedStatement.setInt(13, random.nextInt(5)); // login_attempts
                    preparedStatement.setString(14, random.nextBoolean() ? "admin" : "user"); // user_role
                    preparedStatement.setString(15, "http://example.com/profile/" + i); // profile_picture_url
                    preparedStatement.setString(16, "Bio for user " + i); // bio
                    preparedStatement.setString(17, "{\"preference\":\"value" + random.nextInt(100) + "\"}"); // preferences

                    preparedStatement.addBatch();

                    // 每 batchSize 条提交一次
                    if (i % batchSize == 0) {
                        preparedStatement.executeBatch();
                        connection.commit();
                        System.out.println("Inserted " + i + " records.");
                    }
                }

                // 提交最后一批
                preparedStatement.executeBatch();
                connection.commit();
                System.out.println("Inserted all " + totalRecords + " records.");
            } catch (SQLException e) {
                connection.rollback(); // 出现异常时回滚
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static String generateRandomHash() {
        Random random = new Random();
        StringBuilder hash = new StringBuilder(64);
        for (int i = 0; i < 64; i++) {
            hash.append(Integer.toHexString(random.nextInt(16)));
        }
        return hash.toString();
    }
}

4、测试结果 (一共2800w条数据)

4.1 count(*)与count(1) 与count(id)

时间对比

select  count(1) from user; //时间 2m 23s

select  count(*) from user;//时间 2m 23s

select  count(id) from user;//时间 2m 21s 

4.2 测试用索引和不用索引的时间开销

4.2.1 使用主键索引

select  * from user where id=10562126;  //72ms

4.2.2 不使用主键索引

select  * from user where password_hash='0be0d3c36f6ecfab74236a9d84fc369cdf4bfcbd26892f969684dc0833dbe8ea';  //51 s

4.2.3 在password_hash上建立索引开销

CREATE INDEX idx_password_hash ON user(password_hash); //completed in 9 m 48 s 736 ms

4.2.3 在password_hash上查询，时间开销

select  * from user where password_hash='0be0d3c36f6ecfab74236a9d84fc369cdf4bfcbd26892f969684dc0833dbe8ea';//65 ms

5、总结

如果一条sql语句在生产环境上如何没有命中索引，如果在一个表比较大的情况小，是很致命的，因为只要同时存在多个这样的查询sql，那么意味着数据库很有可能崩盘，宕机

6、慢sql的优化步骤

6.1 启动慢SQL的日志

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;# 查询时间超过1秒被记录
SET GLOBAL log_queries_not_using_indexes = 1;# 记录未使用索引的查询

6.2 定位到具体的sql语句之后，使用 EXPLAIN 分析 SQL 查询

EXPLAIN 命令用于分析查询的执行计划，帮助识别性能瓶颈。

基本用法：

sql
复制代码
EXPLAIN SELECT * FROM your_table WHERE your_condition;

重点字段解读：

• type：

  • ALL：全表扫描，性能最差。
  • ref / index / const：表示索引被使用，性能较优。

• key：

  • 实际使用的索引名称。

• rows：

  • 预计需要扫描的行数，越少越好。

• Extra：

  • Using index：使用覆盖索引，无需回表。
  • Using temporary / Using filesort：可能存在排序或临时表问题，需优化。