Help us understand the problem. What is going on with this article?

MySQLで大量のシンプルなクエリを高速化する

どんなに軽いクエリでも、たとえばWebサーバーとMySQLの間のRTTが5msあって20クエリを実行したらRTTだけで100msかかってしまいます。

たくさんのデータをinsertするときは bulk insert (VALUES の後に複数の行を書くクエリ) を使うテクニックは有名です。しかしこのテクニックは次のような場合に使えません。

  • 複数のテーブルに1行ずつINSERTしたい
  • 複数のUPDATEやSELECTをまとめたい

たとえば弊社のある案件で次のような場面がありました。

  • 新規ユーザー作成時に大量のテーブルにINSERTしたい
  • ログイン時に大量のテーブルにSELECTしたい

こういった場面を高速化するために multiple statements と multiple result sets を利用しました。

Multiple Statements

複数のクエリを ; で区切って一発(Pythonレベルで言えば1つの Cursor.execute() 呼び出し、プロトコルレベルで言えば1つのCOM_QUERYパケット)で送ることができます。複数のINSERT文を ; で繋ぐことで、大量のINSERT文やUPDATE文を高速化することができます。

RTTを削減できるだけでなくTCPのパケット数も削減できるので、MySQLサーバー側のクエリを受信する部分の負荷の削減も期待できます。

ただし、この ; を使ってクエリを連結する仕組みはSQLインジェクションでもよく悪用されています。そのため MySQL protocol ではハンドシェイク時に multiple statements を無効化できるようになっています。MySQLクライアントのライブラリによっては multiple statements を利用するためにオプションが必要かもしれません。

例えば Python の mysqlclient はデフォルトで multiple statements が利用できます。(fork元の昔からあるライブラリとの後方互換性のため...)
一方 Go の github.com/go-sql-driver/mysqlmultiStatements=true を指定する必要があります。

もう一つの注意点として、 MySQL の (PREPARE 文ではなくプロトコルレベルの) prepared statement を使う場合は、 placeholder を利用できません。自前でエスケープしてSQL文字列を組み立ててからクエリを投げるか、 prepared statement を使わないように設定する必要があります。

Multiple Result sets

複数のSELECT文をまとめたい場合は、複数のクエリを投げるだけでなくその結果を受け取る必要もあります。そのためには mutltiple result sets を使います。

複数の Result Set が返されたとき、Python では Cursor.nextset() を使って次の Result Set を受け取ることができます。

import MySQLdb

con = MySQLdb.connect(host="127.0.0.1", port=3306, user="test", password="test")
cur = con.cursor()

q = """\
select 1;
select 2;
select 3;
select 4;
select 5;
"""

cur.execute(q)

while True:
    print(cur.fetchall())
    if not cur.nextset():
        break

Go の場合は database/sqlRows.NextResultSet() を使って同じように複数の Result Set を受け取ることができます。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away