并行执行MySQL SELECT *查询 [英] Executing MySQL SELECT * query in parallel

问题描述

我有一个多线程应用程序，该应用程序定期获取MySQL表的全部内容(使用SELECT * FROM查询) 该应用程序是用python编写的，使用线程模块进行多线程处理，并使用mysql-python(mysqldb)作为MySQL驱动程序(使用mysqlalchemy作为包装程序会产生类似的结果). 我为我的MySQL数据库使用InnoDB引擎.

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.

我写了一个简单的测试来并行检查SELECT *查询的性能，发现所有这些查询都是按顺序实现的.

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

我明确将ISOLATION LEVEL设置为READ UNCOMMITTED，尽管它似乎对性能没有帮助.

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()

我是否期望该查询可以并行执行? 如果是的话，如何在python中实现呢?

Am I right expecting this query to be executed in parallel? If yes, how can I implement that in python?

推荐答案

MySQL允许来自单个用户或多个用户的许多连接.在该连接中，它最多使用一个CPU内核，并且一次执行一个SQL语句.

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.

一个事务"可以由多个SQL语句组成，而该事务被原子地处理.考虑经典的银行应用程序:

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").

如果您需要并行"执行操作，请具有一个或多个客户端，这些客户端可以运行多个线程(或进程)，并且每个线程都与MySQL建立自己的连接.

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.

一个小例外:在后台进行一些额外的线程来执行后台任务，例如预读或延迟写入或刷新内容.但这 not 并没有为用户提供一种在单个连接中一次完成两件事"的方法.

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.

我在这里所说的内容适用于MySQL/MariaDB的所有版本以及访问它们的所有客户端软件包.

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

这篇关于并行执行MySQL SELECT *查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！