Mysql索引测试记录

最新推荐文章于 2024-08-29 17:00:41 发布
最新推荐文章于 2024-08-29 17:00:41 发布
阅读量368 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zidieq/article/details/121179033
版权

mysql 数据库 database

首先创建学生表

create table student(RunDate varchar(10),StuID varchar(20),Name varchar(20),Subject varchar(20),Score decimal(18,2));

共有如下九名学生

学号学生姓名
S001李玉
S002李菲
S003张云
S004王林
S005赵虎
S006三儿
S007秦七
S008姚六
S009万三

只有两个科目

科目
English
Math

使用如下代码向数据库中插入1000万行记录

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
/**
 * 本类用于
 *
 * @author SJXQ
 * @version 2021/10/21 17:10
 */
public class Hello {
   
    static Connection connection = null;
    static PreparedStatement ps = null;
    static ResultSet rs = null;

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection("jdbc:mysql:///zhouliu", "root", "root");
            ps = connection.prepareStatement("insert into student(RunDate,StuID,Name,Subject,Score) values(?,?,?,?,?);");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        LocalDate ldt = LocalDate.now();
        Random r = new Random();
        List<Student> list = new ArrayList<>();
        list.add(new Student("S001", "李玉"));
        list.add(new Student("S002", "李菲"));
        list.add(new Student("S003", "张云"));
        list.add(new Student("S004", "王林"));
        list.add(new Student("S005", "赵虎"));
        list.add(new Student("S006", "三儿"));
        list.add(new Student("S007", "秦七"));
        list.add(new Student("S008", "姚六"));
        list.add(new Student("S009", "万三"));

        String[] subs = {"English", "Math"};

        long t1 = 0, t2 = 0;
        //随机生成一个近三年的日期
        String runDate = ldt.minusDays(r.nextInt(1000)).toString().replace("-", "");
        int batchRows = 100;
        t1 = System.currentTimeMillis();
        for (int row = 0; row < 100000; row++) {

            for (int i = 0; i < 100; i++) {

                //随机选择一个学生出来
                Student stu = list.get(r.nextInt(list.size()));
                String stuId = stu.stuId;
                String name = stu.name;

                //随机选择一个科目
                String subName = subs[r.nextInt(subs.length)];

                //随机生成一个分数
                int min = 49;
                int score = r.nextInt(100 - min) + min;

                try {
                    ps.setString(1, runDate);
                    ps.setString(2, stuId);
                    ps.setString(3, name);
                    ps.setString(4, subName);
                    ps.setInt(5, score);
                    ps.addBatch();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            try {
                ps.executeBatch();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            if (row % 100 == 0) {
                t2 = System.currentTimeMillis();
//                System.out.printf("t1:%s t2:%s t2-t1:%s",t1,t2,t2-t1);
                if ((t2 - t1) > 1000) {

                    System.out.println(row + ":" + (batchRows*100 / ((t2 - t1) / 1000)) + "条/秒");
                }
                t1 = System.currentTimeMillis();
            }
        }
        if (rs != null && ps != null && connection != null) {
            try {
                rs.close();
                ps.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}



class Student {
    String stuId;
    String name;

    public Student(String stuId, String name) {
        this.stuId = stuId;
        this.name = name;
    }

    public Student() {
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuId='" + stuId + '\'' +
                ", name='" + name + '\'' +
                '}';
    }
}

本次被测试的SQL语句

select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
  1. 首先是未使用索引模式
    检查确认无索引
MariaDB [zhouliu]> show index  from student;
Empty set (0.001 sec)

执行查询

MariaDB [zhouliu]> select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+-------+---------+-----------+
| StuID | Subject | avgs      |
+-------+---------+-----------+
| S001  | English | 74.018119 |
| S001  | Math    | 73.980035 |
| S002  | English | 73.991890 |
| S002  | Math    | 74.035240 |
| S003  | English | 74.025509 |
| S003  | Math    | 73.976478 |
| S004  | English | 74.008404 |
| S004  | Math    | 74.020877 |
| S005  | English | 74.012730 |
| S005  | Math    | 74.012240 |
| S006  | English | 73.979991 |
| S006  | Math    | 73.962878 |
| S007  | English | 74.003676 |
| S007  | Math    | 74.018311 |
| S008  | English | 73.966731 |
| S008  | Math    | 73.995144 |
| S009  | English | 74.019477 |
| S009  | Math    | 74.020215 |
+-------+---------+-----------+
18 rows in set (13.914 sec)

使用explain分析语句

MariaDB [zhouliu]> explain select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|    1 | SIMPLE      | ta    | ALL  | NULL          | NULL | NULL    | NULL | 10005495 | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.000 sec)
  1. 只在StuID上创建索引
MariaDB [zhouliu]> create index name_index on student(StuID);
Query OK, 0 rows affected (33.139 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行查询

MariaDB [zhouliu]> select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+-------+---------+-----------+
| StuID | Subject | avgs      |
+-------+---------+-----------+
| S001  | English | 74.018119 |
| S001  | Math    | 73.980035 |
| S002  | English | 73.991890 |
| S002  | Math    | 74.035240 |
| S003  | English | 74.025509 |
| S003  | Math    | 73.976478 |
| S004  | English | 74.008404 |
| S004  | Math    | 74.020877 |
| S005  | English | 74.012730 |
| S005  | Math    | 74.012240 |
| S006  | English | 73.979991 |
| S006  | Math    | 73.962878 |
| S007  | English | 74.003676 |
| S007  | Math    | 74.018311 |
| S008  | English | 73.966731 |
| S008  | Math    | 73.995144 |
| S009  | English | 74.019477 |
| S009  | Math    | 74.020215 |
+-------+---------+-----------+
18 rows in set (13.911 sec)

使用explain分析语句

MariaDB [zhouliu]> explain select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|    1 | SIMPLE      | ta    | ALL  | NULL          | NULL | NULL    | NULL | 10005495 | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.000 sec)

删除本次创建的索引

MariaDB [zhouliu]> alter table student drop index name_index;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. 在StuID和Subject上创建复合索引
MariaDB [zhouliu]> create index name_index on student(StuID,Subject);
Query OK, 0 rows affected (28.912 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行查询

MariaDB [zhouliu]> select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+-------+---------+-----------+
| StuID | Subject | avgs      |
+-------+---------+-----------+
| S001  | English | 74.018119 |
| S001  | Math    | 73.980035 |
| S002  | English | 73.991890 |
| S002  | Math    | 74.035240 |
| S003  | English | 74.025509 |
| S003  | Math    | 73.976478 |
| S004  | English | 74.008404 |
| S004  | Math    | 74.020877 |
| S005  | English | 74.012730 |
| S005  | Math    | 74.012240 |
| S006  | English | 73.979991 |
| S006  | Math    | 73.962878 |
| S007  | English | 74.003676 |
| S007  | Math    | 74.018311 |
| S008  | English | 73.966731 |
| S008  | Math    | 73.995144 |
| S009  | English | 74.019477 |
| S009  | Math    | 74.020215 |
+-------+---------+-----------+
18 rows in set (19.353 sec)

使用explain分析语句

MariaDB [zhouliu]> explain select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows     | Extra |
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------+
|    1 | SIMPLE      | ta    | index | NULL          | name_index | 126     | NULL | 10005495 |       |
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------+
1 row in set (0.000 sec)

删除本次创建的索引

MariaDB [zhouliu]> alter table student drop index name_index;
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. 在StuID、Subject和Score上创建复合索引
MariaDB [zhouliu]> create index name_index on student(StuID,Subject,Score);
Query OK, 0 rows affected (1 min 11.887 sec)
Records: 0  Duplicates: 0  Warnings: 0

执行查询

MariaDB [zhouliu]> select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+-------+---------+-----------+
| StuID | Subject | avgs      |
+-------+---------+-----------+
| S001  | English | 74.018119 |
| S001  | Math    | 73.980035 |
| S002  | English | 73.991890 |
| S002  | Math    | 74.035240 |
| S003  | English | 74.025509 |
| S003  | Math    | 73.976478 |
| S004  | English | 74.008404 |
| S004  | Math    | 74.020877 |
| S005  | English | 74.012730 |
| S005  | Math    | 74.012240 |
| S006  | English | 73.979991 |
| S006  | Math    | 73.962878 |
| S007  | English | 74.003676 |
| S007  | Math    | 74.018311 |
| S008  | English | 73.966731 |
| S008  | Math    | 73.995144 |
| S009  | English | 74.019477 |
| S009  | Math    | 74.020215 |
+-------+---------+-----------+
18 rows in set (4.688 sec)

使用explain分析语句

MariaDB [zhouliu]> explain select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows     | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
|    1 | SIMPLE      | ta    | index | NULL          | name_index | 136     | NULL | 10005495 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+----------+-------------+
1 row in set (0.000 sec)

结论：对于SQL语句select ta.StuID,ta.Subject,avg(ta.Score) as avgs from student as ta group by ta.stuID,ta.Subject;

  • 如果只创建第一列，也就是StuID的索引，查询时间无甚变化
  • 如果只创建group by 后面的两列，stuID和Subject，则查询时间可能要比不创建索引时还要久，不知道原因。
  • 如果 创建的是stuID、Subject和Score三列的索引，则查询时间明显短了很多，且使用explain分析时也能看到Extra列using index
水晶心泉
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
mysql索引系统表_MySQL - 表索引
weixin_32220917的博客
01-19 1876
1. 索引概述索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只...
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL 之 创建测试数据_学习记录
GoodJob
07-24 440
MySQL 之 创建测试数据_学习记录   1.创建数据和索引 1.创建索引测试表 1 2 3 4 5 6 7 DROP TABLE IF EXISTS big_data; CREATE TABLE big_data( id int PRIMARY KEY NOT NULL AUTO_INCREM...
MySQL 索引测试
风信子
06-29 772
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
mysql测试索引
weixin_54949000的博客
04-20 379
CREATE TABLE app_user ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) DEFAULT ‘’, email VARCHAR(50) NOT NULL, phone VARCHAR(20) DEFAULT ‘’, gender TINYINT(4) UNSIGNED DEFAULT ‘0’, password VARCHAR(100) NOT NULL DEFAULT ‘’, age TINYINT(4)
MySQL系列11——索引效率测试
qq_42486675的博客
10-29 9893
MySQL索引效率测试 一、以mysql添加索引和不添加索引为例，测试数据的插入速度和查询速度。 1、首先创建数据表和数据库，在cmd命令行中执行： create database TestDataSpeed charset=utf8; use testdataspeed; create table DataNoIndex(id int,name char(10),age int,address char(20),gender char(1)); #创建不带索引的数据表 create table DataN
MySQL 测试数据快速生成
热门推荐
oyc的博客
04-21 2万+
MySQL 测试数据快速生成 一、前言 在我们进行大数据量的报表测试以及性能测试时，需要数据库中存在大量的基础测试数据，但是依赖于系统本身大多数情况下都无法快速产生大量适合的测试数据，这就需要我们手动生成一些测试数据。 目前生成数据的方式有： 1、使用存储过程实现(需要开发了解PL/SQL语法) 2、多线程...
Mysql索引优化解决方案.doc
03-07
"Mysql索引优化解决方案" MySQL索引是帮助MySQL高效获取数据的数据结构。MySQL在存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现...
mysql 测试数据集，单表200万条数据
02-21
这个200万条数据的测试集是实践上述理论的好素材，可以帮助我们深入理解MySQL在大数据场景下的表现，优化数据库设计和SQL语句，提升系统整体性能。通过实际操作和测试，我们可以更好地掌握MySQL数据库的相关知识。
MySQL索引_011
08-03
【MySQL索引详解】 在MySQL数据库中，索引是一种用于加速数据检索的数据结构。它们减少了数据库服务器必须执行的磁盘I/O操作，从而显著提高了查询性能。本文将深入探讨索引的基本概念、工作原理以及如何在没有索引...
mysql-day02
最新发布
m0_64370841的博客
08-29 668
【代码】mysql-day02。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 548
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 425
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
es相关概念、索引操作（相当于mysql中的数据库操作）
小丁的博客
08-26 393
【代码】es相关概念、索引操作（相当于mysql中的数据库操作）
【MySQL数据库管理问答题】第5章 监控 MySQL
Songyaxuan075118的博客
08-26 1147
MySQL服务器的日志文件类型及用途 默认启用的两种日志 错误日志 二进制日志 常规查询日志和慢速查询日志 Performance Schema的作用 Performance Schema顶级检测组件 sys模式的作用 audit_log_filter_linux_install.sql脚本 配置基于规则的MySQL审计功能 MySQL Enterprise Monitor提供的管理功能 SHOW PROCESSLIST命令输出的列名及作用 中继日志 审计日志 查询日志 慢查询日志
MySQL中的事务
2301_80389611的博客
08-25 1316
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name 列更新 为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为' 张三'，而 Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据， 这种现象 就称之为 脏读。事务的执行是互不干扰的，一个事务不可能看到其他事务运行时中间某一时刻的数据。基于锁的状态分类：意向共享锁、意向排它锁。
MySQL做题笔记
qq_44736340的博客
08-25 578
sql语法
前后端分离项目实战-通用管理系统搭建（前端Vue3+ElementPlus，后端Springboot+Mysql+Redis）第七篇：菜单和路由动态绑定
分享式获得也是一种学习的态度
08-26 838
每个人都有惰性，但不断学习是好好生活的根本，共勉！绿竹入幽径，青萝拂行衣。——《下终南山过斛斯山人宿置酒》
Clickhouse和MySQL的区别以及适用业务场景
小橙子的笔记屋
08-26 634
clickhouse和MySQL的区别
MySQL索引与WHERE子句优化技巧
- 测试基础和实践，涵盖了性能测试的步骤、注意事项以及硬件和MySQL本身的测试方法。 这本书对于MySQL的使用者，无论是初学者还是经验丰富的DBA，都提供了宝贵的指导和深入理解数据库技术的途径。通过学习这些知识...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

水晶心泉

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值