下面是做500w条数据table的步骤:
[root@localhost tmp]# i=1;while [ $i -le 5000000 ];do echo $i ;let i+=1; done >500w.txt
[root@localhost tmp]# ls
500w.txt
[root@localhost tmp]# tail 500w.txt
4999991
4999992
4999993
4999994
4999995
4999996
4999997
4999998
4999999
5000000
在mysql中创建名字叫tmp500w的表
mysql> use yyds
Database changed
mysql> create table tmp500w(id int,primary key(id));
导入数据到表tmp500w
mysql> load data infile '/tmp/500w.txt' replace into table tmp500w;#注意500w.txt存放的路径在哪
Query OK, 5000000 rows affected (9.66 sec)
Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0
如果显示错误ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
则在数据库的配置文件my.cnf中,在[mysqld]下添加语句
[root@localhost ziliao]# echo "secure_file_priv=''" >> /etc/my.cnf
添加完重启mysqld: systemctl restart mysqld
或/usr/local/mysql/support-files/mysql.server restart
这时可以看到
mysql> load data infile '/tmp/500w.txt' replace into table tmp500w;
Query OK, 5000000 rows affected (28.91 sec)
Records: 5000000 Deleted: 0 Skipped: 0 Warnings: 0
#再创建生成500万数据的表exam
mysql> create table exam(id int,c1 int,c2 varchar(100),primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_yyds |
+----------------+
| exam |
| tmp500w |
+----------------+
2 rows in set (0.00 sec)
mysql> desc exam;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| c1 | int(11) | YES | | NULL | |
| c2 | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc tmp500w;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
插入500万条数据到表exam
mysql> insert into exam select id,round(rand()*5000000),concat('laoliulinuxyyds',id)from tmp500w;
Query OK, 5000000 rows affected (27.36 sec)
Records: 5000000 Duplicates: 0 Warnings: 0
mysql> select * from exam limit 10;
+----+---------+-------------------+
| id | c1 | c2 |
+----+---------+-------------------+
| 1 | 4736170 | laoliulinuxyyds1 |
| 2 | 831375 | laoliulinuxyyds2 |
| 3 | 4948363 | laoliulinuxyyds3 |
| 4 | 2247689 | laoliulinuxyyds4 |
| 5 | 1393359 | laoliulinuxyyds5 |
| 6 | 223725 | laoliulinuxyyds6 |
| 7 | 1938551 | laoliulinuxyyds7 |
| 8 | 4021580 | laoliulinuxyyds8 |
| 9 | 4292246 | laoliulinuxyyds9 |
| 10 | 4396492 | laoliulinuxyyds10 |
+----+---------+-------------------+
10 rows in set (0.00 sec)
500万条数据的表就创建好了
压测模拟:(这个语句是在[root@localhost ~]# 下执行的)
介绍explain语句:类似模拟查看,会给出explain后面语句运行的相关结果和参数。
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=50 --iterations=1 --create-schema='test' \
--query="select * from yyds.exam where c2='780p'" engine=innodb \
--number-of-queries=200 -uroot -p123 -verbose
#参数解释:
#--concurrency=50 并发量(50个人一起上的意思)
#--iterations=1 语句迭代次数(迭代的实验次数)
#--create-schema,测试的schema,MySQL中schema也就是database
#--query 指定SQL语句
#--number-of-queries,执行的SQL总数量
#这里要保证mysql中数据库yyds,表exam,字段c2要存在
如果显示mysqlslap找不到命令,那就前面加个绝对路径:
/usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='test' --query="select * from yyds.exam where c2='780p'" engine=innodb --number-of-queries=200 -uroot -p -verbose
mysql5.7.35:没有添加索引之前, 要263.303秒,4分多钟,
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=50 --iterations=1 --create-schema='test' \
> --query="select * from yyds.exam where c2='780p'" engine=innodb \
> --number-of-queries=200 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 263.303 seconds
Minimum number of seconds to run all queries: 263.303 seconds
Maximum number of seconds to run all queries: 263.303 seconds
Number of clients running queries: 50
Average number of queries per client: 4
现在给c2建立索引:
mysql> use yyds
mysql> alter table exam add index idx_c2(c2);
Query OK, 0 rows affected (31.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看索引(主要看key列)
mysql> show index from exam\G
*************************** 1. row ***************************
Table: exam
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 4981848
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: exam
Non_unique: 1
Key_name: idx_c2
Seq_in_index: 1
Column_name: c2
Collation: A
Cardinality: 4981393
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql> desc exam;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| c1 | int(11) | YES | | NULL | |
| c2 | varchar(100) | YES | MUL | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
建立索引之后,只需要0.073秒
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='test' --query="select * from yyds.exam where c2='780p'" engine=innodb --number-of-queries=200 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.073 seconds
Minimum number of seconds to run all queries: 0.073 seconds
Maximum number of seconds to run all queries: 0.073 seconds
Number of clients running queries: 50
Average number of queries per client: 4
mysql5.6:没有添加索引之前, 要270.309秒
[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='test' --query="select * from yyds.exam where c2='780p'" engine=innodb --number-of-queries=200 -uroot -p -verbose
Enter password:
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 270.309 seconds
Minimum number of seconds to run all queries: 270.309 seconds
Maximum number of seconds to run all queries: 270.309 seconds
Number of clients running queries: 50
Average number of queries per client: 4
给c2建立索引之后,只需要0.176秒
[root@localhost ~]# /usr/local/mysql/bin/mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='test' --query="select * from yyds.exam where c2='780p'" engine=innodb --number-of-queries=200 -uroot -p -verbose
Enter password:
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.176 seconds
Minimum number of seconds to run all queries: 0.176 seconds
Maximum number of seconds to run all queries: 0.176 seconds
Number of clients running queries: 50
Average number of queries per client: 4
对比发现mysql5.7比mysql5.6读取(查询)速度要快