首先创建学生表
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;
- 首先是未使用索引模式
检查确认无索引
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)
- 只在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
- 在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
- 在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