問題描述
我正在測試我正在構建的系統的性能，它真的很慢，而且我不知道為什么或者是否應該這么慢.我正在測試的是我可以對數據庫執行多少次 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.
大多數時候你只是不關心確認的寫入.因此，您可以通過將此 mysql 選項設置為零來配置 mysql:
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 性能慢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！