[PYTHON] Accelerate a large number of simple queries with MySQL

No matter how light the query, for example, if there is an RTT between the web server and MySQL for 5ms and 20 queries are executed, the RTT alone will take 100ms.

The technique of using bulk insert (a query that writes multiple rows after VALUES) when inserting a lot of data is well known. However, this technique cannot be used in the following cases.

• I want to insert one row into multiple tables
• I want to combine multiple UPDATEs and SELECTs

For example, there was the following scene in a certain case of our company.

• I want to insert into a large number of tables when creating a new user
• I want to SELECT a large number of tables at login

I used multiple statements and multiple result sets to speed up these situations.

Multiple Statements

Multiple queries can be sent in one shot (one Cursor.execute () call at the Python level, one COM_QUERY packet at the protocol level) separated by;. By connecting multiple INSERT statements with;, you can speed up a large number of INSERT statements and UPDATE statements.

Not only can RTT be reduced, but the number of TCP packets can also be reduced, so it can be expected to reduce the load on the part that receives queries on the MySQL server side.

However, this mechanism of concatenating queries using; is also often abused in SQL injection. Therefore, the MySQL protocol allows you to disable multiple statements during the handshake. Depending on the MySQL client library, you may need an option to take advantage of multiple statements.

For example, Python's mysqlclient has multiple statements available by default. (For backwards compatibility with the original fork library ...) On the other hand, Go's github.com/go-sql-driver/mysql must specify multiStatements = true.

Another caveat is that placeholder is not available when using MySQL's prepared statement (protocol level rather than PREPARE statement). You have to escape it yourself, assemble the SQL string and then throw the query, or set it not to use prepared statements.

Multiple Result sets

If you want to combine multiple SELECT statements, you need to not only throw multiple queries but also receive the results. To do this, use mutltiple result sets.

When multiple Result Sets are returned, Python can use Cursor.nextset () to receive the next 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

In case of Go, use Rows.NextResultSet () of database / sql to receive multiple Result Sets in the same way. I can.