MySql 性能慢 [英] Slow performance MySql

问题描述

我正在测试我正在构建的系统的性能，它真的很慢，而且我不知道为什么或者是否应该这么慢.我正在测试的是我可以对数据库执行多少次 INSERT，每秒大约 22 次.这听起来真的很慢，当我尝试插入时，我可以在大约 0.5 秒内插入 30000 条记录.在现实生活中，插入是由系统中的不同用户进行的，因此连接、发送查询、解析查询等的开销将始终存在.到目前为止我尝试过的:

I'm trying out performance of a system I'm building, and it's really slow, and I don't know why or if it should be this slow. What I'm testing is how many single INSERT I can do to the database and I get around 22 per second. That sounds really slow and when I tried to do the inserts i a singel big SQL-query I can insert 30000 records in about 0.5 seconds. In real life the inserts is made by different users in the system so the overhead of connecting, sending the query, parsing the query etc. will always be there. What I have tried so far:

• 用尽可能少的代码编写mysqli.= 每秒 22 次插入
• 使用尽可能少的代码的 PDO.= 每秒 22 次插入
• 将连接主机从本地主机更改为 127.0.0.1 = 每秒 22 次插入
• 没有语句对象的 mysqli 并检查 SQL 注入 = 每秒 22 次插入

所以这里似乎有问题.

系统规格:

• 英特尔 i5
• 16 公制内存
• 7200 rpm 磁盘驱动器

软件:

• Windows 10
• XAMPP，MariaDB 相当新
• 数据库引擎 innoDB.

我用来做测试的代码:

$amountToInsert = 1000;
//$fakeData is an array with randomly generated emails 
$fakeData = getFakeData($amountToInsert); 
$db = new DatabaseHandler();
for ($i = 0; $i < $amountToInsert; $i++) {
    $db->insertUser($fakeUsers[$i]);
}
$db->closeConnection();

调用数据库的类:

class DatabaseHandler {
    private $DBHOST = 'localhost';
    private $DBUSERNAME = 'username';
    private $DBPASSWORD = 'password';
    private $DBNAME = 'dbname';
    private $DBPORT = 3306;

    private $mDb;

    private $isConnected = false;

    public function __construct() {
        $this->mDb = new mysqli($this->DBHOST, $this->DBUSERNAME
                              , $this->DBPASSWORD, $this->DBNAME
                              , $this->DBPORT);
        $this->isConnected = true;
    }

    public function closeConnection() {
        if ($this->isConnected) {
            $threadId = $this->mDb->thread_id;
            $this->mDb->kill($threadId);
            $this->mDb->close();
            $this->isConnected = false;
        }
    }

    public function insertUser($user) {
        $this->mDb->autocommit(true);
        $queryString = 'INSERT INTO `users`(`email`, `company_id`) '
                        .'VALUES (?, 1)';
        $stmt = $this->mDb->prepare($queryString);
        $stmt->bind_param('s', $user);
        if ($stmt->execute()) {
            $stmt->close();
            return 1;
        } else {
            $stmt->close();
            return 0;
        }
    }
}

user"表有 4 列，结构如下:

The "user" table has 4 columns with the following structure:

• id INT 无符号主键
• 电子邮件 VARCHAR(60)
• company_id INT 无符号索引
• 引导文本

我在这里不知所措，真的不知道下一步该往哪里看.任何在正确方向上的帮助将不胜感激.

I'm at a loss here and don't really know where to look next. Any help in the right direction would be very much appreciated.

推荐答案

就像评论中解释的那样，这应该归咎于 InnoDB.默认情况下，此引擎过于谨慎，不使用磁盘缓存，以确保数据确实已写入磁盘，然后再向您返回成功消息.所以你基本上有两个选择.

Like it's explained in the comments, it's InnoDB to blame. By default this engine is too cautious and doesn't utilize the disk cache, to make sure that data indeed has been written on disk, before returning you a success message. So you basically have two options.

1. 大多数时候你只是不关心确认的写入.因此，您可以通过将此 mysql 选项设置为零来配置 mysql:

1. Most of time you just don't care for the confirmed write. So you can configure mysql by setting this mysql option to zero:

innodb_flush_log_at_trx_commit = 0

只要这样设置，你的 InnoDB 写入几乎和 MyISAM 一样快.

as long as it's set this way, your InnoDB writes will be almost as fast as MyISAM.

另一种选择是将您的所有写入都包装在一个事务中.由于它只需要对所有写入进行一次确认，因此速度也相当快.

Another option is wrapping all your writes in a single transaction. As it will require only single confirmation from all the writes, it will be reasonable fast too.

当然，通过多次插入只准备一次查询是明智的，但与上述问题相比，速度增益可以忽略不计.因此，它既不能作为对此类问题的解释，也不能作为补救措施.

Of course, it's just sane to prepare your query only once with multiple inserts but the speed gain is negligible compared to the issue above. So it doesn't count neither as an explanation nor as a remedy for such an issue.

这篇关于MySql 性能慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！