検索
ホームページデータベースmysql チュートリアル使用batch insert解决MySQL的insert吞吐量问题_MySQL

最近使用了一个非常简单易用的方法解决了业务上的一个insert吞吐量的问题,在此总结一下。

 

首先我们明确一下,insert吞吐量其实并不是指的IPS(insert per second),而是指的RPS(effect rows per second)。

其次我们再说一下batch insert,其实顾名思义,就是批量插入。这种优化思想是很基本的,MySQL中最出名的应用就是group commit。

简单的来说,就是将SQL A 变成 SQL B

SQL A : insert into table values ($values);SQL B : insert into table values ($values),($values)...($values);

 

下面,我们来看看这种异常简单的改动会带来什么样子的变化。

测试环境交代:单id的表结构,10w个int values,本地使用socket连接MySQL server,使用shell单进程测试。

首先,我们看下使用SQL A将10w个int values插入到test表中所需的耗时,耗时1777秒。

real    29m37.090suser    9m11.705ssys     5m0.762s

然后,我们看下使用SQL B(每次insert,插入10 values)将10w个int values插入到test表中所需的耗时,耗时53秒

real    0m53.871suser    0m19.455ssys     0m6.285s

这是整整近33倍的时间提升。这部分性能提升的原因在于以下几点:

1、每次和MySQL server建立连接都需要经过各种初始化、权限认证,语法解析等等多个步骤,需要消耗一定的资源。

2、更新一个values和更新n个values耗时基本一致。(下面对比一下insert 单values核insert 10 values的profile耗时)

单values:<br>+------------------------------+----------+| Status                       | Duration |+------------------------------+----------+| starting                     | 0.000056 || checking permissions         | 0.000010 || Opening tables               | 0.000034 || System lock                  | 0.000010 || init                         | 0.000011 || update                       | 0.000061 || Waiting for query cache lock | 0.000003 || update                       | 0.000015 || end                          | 0.000003 || query end                    | 0.000053 || closing tables               | 0.000009 || freeing items                | 0.000021 || logging slow query           | 0.000002 || cleaning up                  | 0.000003 |+------------------------------+----------+<br>10 values:+------------------------------+----------+| Status                       | Duration |+------------------------------+----------+| starting                     | 0.000061 || checking permissions         | 0.000008 || Opening tables               | 0.000027 || System lock                  | 0.000008 || init                         | 0.000012 || update                       | 0.000073 || Waiting for query cache lock | 0.000003 || update                       | 0.000010 || end                          | 0.000008 || query end                    | 0.000053 || closing tables               | 0.000010 || freeing items                | 0.000021 || logging slow query           | 0.000002 || cleaning up                  | 0.000003 |+------------------------------+----------+

 

但是,是否values积攒的越多,效率越高吗? 答案自然是否定的,任何优化方案都不会是纯线性的,肯定会在某个条件下出现拐点。

我们按照不同的values number进行测试,分别为1、10、50、100、200、500、1000、5000、10000.

从下图我们可以看出,随着values number的增加,耗时先是急剧下降,从1777s变成53s,然后在增加values number就不会有太大的变化,直到values number超过200,最后的10000个values number耗时达到了2分钟。

从下图我们可以看到随着values numbers的增加,QPS(蓝线)先是猛增,然后下降,最终小于1/s。而RPS(绿线)随着增加猛增到一个高level,然后随着增加逐步下降,超过5000个values number之后开始急剧下降。

另,最关键的是,QPS最高峰和RPS的最高峰并不在同一个values number下,也就是说QPS最高的时候并不代表着insert的吞吐量就最高

在我这个简单测试场景中,values number最合适的值是50,和单values对比,耗时减少97%,insert吞吐量提升36倍

而这个值和表结构和字段类型及大小都有关系。需要根据不同的场景进行测试之后才可以得出,但是普遍来说,50-100是比较推荐的考虑值。

 

至于这个如何实现,只要前端写入的时候加入队列即可,可以按照2个条件进行合并

  • 队列中积攒到n个values number后在写入数据库,优点是性能最高,缺点是时间不可控,有可能等到第n个需要n秒,这时候业务已经不可接收了。
  • 队列中积攒1s之后,有多少个就写入多少个,优点是时间可控,缺点就是values number数目不可能,高并发的情况,可能1s已经积攒上千个values了。
  • 最优的方案其实是2个条件同时起作用,即进行个数效验,也进行时间效验,无论达到那个条件都触发后续写数据库操作。

 

总结:

1、使用batch insert可以提高insert的吞吐量。

2、叠加的values number需要根据实际情况测试得出。

3、同时使用个数和时间控制阀值。

 

 附简单测试的记录值:

ValuesNum

Time

QPS

Rows

1

1777

56

56

10

53

188

1886

50

49

40

2040

100

50

19

2000

200

51

10

1960

500

57

3

1754

1000

60

2

1666

5000

69

0.3

1449

10000

133

0.07

751

 

 

声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
InnoDBバッファープールとそのパフォーマンスの重要性を説明してください。InnoDBバッファープールとそのパフォーマンスの重要性を説明してください。Apr 19, 2025 am 12:24 AM

Innodbbufferpoolは、データをキャッシュしてページをインデックス作成することにより、ディスクI/Oを削減し、データベースのパフォーマンスを改善します。その作業原則には次のものが含まれます。1。データ読み取り:Bufferpoolのデータを読む。 2。データの書き込み:データを変更した後、bufferpoolに書き込み、定期的にディスクに更新します。 3.キャッシュ管理:LRUアルゴリズムを使用して、キャッシュページを管理します。 4.読みメカニズム:隣接するデータページを事前にロードします。 BufferPoolのサイジングと複数のインスタンスを使用することにより、データベースのパフォーマンスを最適化できます。

MySQL対その他のプログラミング言語:比較MySQL対その他のプログラミング言語:比較Apr 19, 2025 am 12:22 AM

他のプログラミング言語と比較して、MySQLは主にデータの保存と管理に使用されますが、Python、Java、Cなどの他の言語は論理処理とアプリケーション開発に使用されます。 MySQLは、データ管理のニーズに適した高性能、スケーラビリティ、およびクロスプラットフォームサポートで知られていますが、他の言語は、データ分析、エンタープライズアプリケーション、システムプログラミングなどのそれぞれの分野で利点があります。

MySQLの学習:新しいユーザー向けの段階的なガイドMySQLの学習:新しいユーザー向けの段階的なガイドApr 19, 2025 am 12:19 AM

MySQLは、データストレージ、管理、分析に適した強力なオープンソースデータベース管理システムであるため、学習する価値があります。 1)MySQLは、SQLを使用してデータを操作するリレーショナルデータベースであり、構造化されたデータ管理に適しています。 2)SQL言語はMySQLと対話するための鍵であり、CRUD操作をサポートします。 3)MySQLの作業原則には、クライアント/サーバーアーキテクチャ、ストレージエンジン、クエリオプティマイザーが含まれます。 4)基本的な使用には、データベースとテーブルの作成が含まれ、高度な使用にはJoinを使用してテーブルの参加が含まれます。 5)一般的なエラーには、構文エラーと許可の問題が含まれ、デバッグスキルには、構文のチェックと説明コマンドの使用が含まれます。 6)パフォーマンスの最適化には、インデックスの使用、SQLステートメントの最適化、およびデータベースの定期的なメンテナンスが含まれます。

MySQL:初心者が習得するための必須スキルMySQL:初心者が習得するための必須スキルApr 18, 2025 am 12:24 AM

MySQLは、初心者がデータベーススキルを学ぶのに適しています。 1.MySQLサーバーとクライアントツールをインストールします。 2。selectなどの基本的なSQLクエリを理解します。 3。マスターデータ操作:テーブルを作成し、データを挿入、更新、削除します。 4.高度なスキルを学ぶ:サブクエリとウィンドウの関数。 5。デバッグと最適化:構文を確認し、インデックスを使用し、選択*を避け、制限を使用します。

MySQL:構造化データとリレーショナルデータベースMySQL:構造化データとリレーショナルデータベースApr 18, 2025 am 12:22 AM

MySQLは、テーブル構造とSQLクエリを介して構造化されたデータを効率的に管理し、外部キーを介してテーブル間関係を実装します。 1.テーブルを作成するときにデータ形式と入力を定義します。 2。外部キーを使用して、テーブル間の関係を確立します。 3。インデックス作成とクエリの最適化により、パフォーマンスを改善します。 4.データベースを定期的にバックアップおよび監視して、データのセキュリティとパフォーマンスの最適化を確保します。

MySQL:説明されている主要な機能と機能MySQL:説明されている主要な機能と機能Apr 18, 2025 am 12:17 AM

MySQLは、Web開発で広く使用されているオープンソースリレーショナルデータベース管理システムです。その重要な機能には、次のものが含まれます。1。さまざまなシナリオに適したInnodbやMyisamなどの複数のストレージエンジンをサポートします。 2。ロードバランスとデータバックアップを容易にするために、マスタースレーブレプリケーション機能を提供します。 3.クエリの最適化とインデックスの使用により、クエリ効率を改善します。

SQLの目的:MySQLデータベースとの対話SQLの目的:MySQLデータベースとの対話Apr 18, 2025 am 12:12 AM

SQLは、MySQLデータベースと対話して、データの追加、削除、変更、検査、データベース設計を実現するために使用されます。 1)SQLは、ステートメントの選択、挿入、更新、削除を介してデータ操作を実行します。 2)データベースの設計と管理に作成、変更、ドロップステートメントを使用します。 3)複雑なクエリとデータ分析は、ビジネス上の意思決定効率を改善するためにSQLを通じて実装されます。

初心者向けのMySQL:データベース管理を開始します初心者向けのMySQL:データベース管理を開始しますApr 18, 2025 am 12:10 AM

MySQLの基本操作には、データベース、テーブルの作成、およびSQLを使用してデータのCRUD操作を実行することが含まれます。 1.データベースの作成:createdatabasemy_first_db; 2。テーブルの作成:createTableBooks(idintauto_incrementprimarykey、titlevarchary(100)notnull、authorvarchar(100)notnull、published_yearint); 3.データの挿入:InsertIntoBooks(タイトル、著者、公開_year)VA

See all articles

ホットAIツール

Undresser.AI Undress

Undresser.AI Undress

リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover

AI Clothes Remover

写真から衣服を削除するオンライン AI ツール。

Undress AI Tool

Undress AI Tool

脱衣画像を無料で

Clothoff.io

Clothoff.io

AI衣類リムーバー

AI Hentai Generator

AI Hentai Generator

AIヘンタイを無料で生成します。

ホットツール

SublimeText3 英語版

SublimeText3 英語版

推奨: Win バージョン、コードプロンプトをサポート!

Dreamweaver Mac版

Dreamweaver Mac版

ビジュアル Web 開発ツール

ゼンドスタジオ 13.0.1

ゼンドスタジオ 13.0.1

強力な PHP 統合開発環境

SublimeText3 Mac版

SublimeText3 Mac版

神レベルのコード編集ソフト(SublimeText3)

EditPlus 中国語クラック版

EditPlus 中国語クラック版

サイズが小さく、構文の強調表示、コード プロンプト機能はサポートされていません