mysql 并行查询,并行执行MySQL SELECT *查询

I have a multithreaded application that periodically fetches the whole content of the MySQL table (with SELECT * FROM query)

The application is written in python, uses threading module to multithreading and uses mysql-python (mysqldb) as MySQL driver (using mysqlalchemy as a wrapper produces similar results).

I use InnoDB engine for my MySQL database.

I wrote a simple test to check the performance of SELECT * query in parallel and discovered that all of those queries are implemented sequentially.

I explicitly set the ISOLATION LEVEL to READ UNCOMMITTED, although it does not seem to help with performance.

The code snipper making the DB call is below:

@performance.profile()

def test_select_all_raw_sql(conn_pool, queue):

'''

conn_pool - connection pool to get mysql connection from

queue - task queue

'''

query = '''SELECT * FROM table'''

try:

conn = conn_pool.connect()

cursor = conn.cursor()

cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")

# execute until the queue is empty (Queue.Empty is thrown)

while True:

id = queue.get_nowait()

cursor.execute(query)

result = cursor.fetchall()

except Queue.Empty:

pass

finally:

cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ")

conn.close()

Am I right expecting this query to be executed in parallel?

If yes, how can I implement that in python?

解决方案

MySQL allows many connections from a single user or many users. Within that one connection, it uses at most one CPU core and does one SQL statement at a time.

A "transaction" can be composed of multiple SQL statements while the transaction is treated as atomically. Consider the classic banking application:

BEGIN;

UPDATE ... -- decrement from one user's bank balance.

UPDATE ... -- increment another user's balance.

COMMIT;

Those statements are performed serially (in a single connection); either all of them succeed or all of them fail as a unit ("atomically").

If you need to do things in "parallel", have a client (or clients) that can run multiple threads (or processes) and have each on make its own connection to MySQL.

A minor exception: There are some extra threads 'under the covers' for doing background tasks such as read-ahead or delayed-write or flushing stuff. But this does not give the user a way to "do two things at once" in a single connection.

What I have said here applies to all versions of MySQL/MariaDB and all client packages accessing them.