What is the insertion speed of MySQL?

This article mainly introduces "how much is the MySQL insertion speed". In the daily operation, I believe many people have doubts about the MySQL insertion speed. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how much is the MySQL insertion speed?" Next, please follow the editor to study!

Writing speed

Can MySQL insert 50w records per second?

With doubt, let's see how many records mysql can insert per second.

To answer this question, what are the factors that affect the speed of mysql insertion?

The speed of the hard disk, the speed of the network card, the amount of data written to the row, the location of the data in the hard disk, and so on.

Simple data, the insertion speed must be fast, complex insertion must be slow.

So simply this question is not easy to answer, the best way is to conduct a stress test, and finally find an average.

A test environment:

MySQL table structure

CREATE TABLE `user_ 10w` (`id` int (10) NOT NULL AUTO_INCREMENT, `name` varchar (20) DEFAULT NULL, `mobile` varchar (11) DEFAULT NULL, `add_ time` int (11) DEFAULT NULL, `groupid` tinyint (1) DEFAULT NULL, `login_ time` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM AUTO_INCREMENT=4730016 DEFAULT CHARSET=utf8mb4

Computer configuration

4-core Intel (R) Core (TM) i3-8100 CPU @ 3.60GHz

Ssd 120g hard disk

two。 Start testing.

Description: stand-alone test, does not involve network card, the impact of network transmission

Test program php pdo

Include_once ("tool.php"); $dsn = 'mysql:dbname=demo;host=127.0.0.1'; $user =' root'; $pass = 'root'; try {$hand = new PDO ($dsn, $user, $pass);} catch (PDOException $e) {echo' Connection failed:'. $e-> getMessage ();} $query = $hand- > prepare ("insert into user_10w (name,mobile,add_time,groupid,last_login_time) values (?,?)"); $nong0; $count = 1000000; / / 100w $t = time (); echo 'start write,time is'. $t. "\ n"; while (true) {$nails; $name = Tool::getRandomStr (5); $mobile = Tool::getRandNum (9); $group = Tool::getRandNum (1); $rs = $query- > execute ([$name,'13'.$mobile,time (), $group,time ()]); if ($n > $count) break;} echo 'write end, time is'. (time ()-$t). "\ n"

1. No index, single process

Start write,time is 1593338798 write end, time is 50

Time-consuming 50s, average 2w/ seconds

two。 Add a normal index to name

Write end, time is 60 root@test:/data/php# php7 pdo.php start write,time is 1593393695 write end, time is 61

It is about 10 seconds slower, so the index will cause the insert to be slower because the index will be updated.

Load

3. Multi-process testing

A simple multi-process execution program is written with go.

Package main import ("os/exec"sync") var wg sync.WaitGroup func testRun (wg * sync.WaitGroup) {c: = exec.Command ("php7", ". / pdo.php") c.Run () wg.Done ()} func main () {var n int = 10 wg.Add (n) for