SQLAlchemy中指定想要使用的索引

SQLAlchemy中指定想要使用的索引

 

 

我们知道在MySQL中可以使用FORCE INDEX(index_name,...)来强制使用索引，那在SQLAlchemy如何指定呢？

经过多方查找和实践，我发现了一个叫with_hint的函数。

我们看下源码

def with_hint(self, selectable, text, dialect_name="*"):
    r"""Add an indexing or other executional context hint for the given
    selectable to this :class:`.Select`.

    The text of the hint is rendered in the appropriate
    location for the database backend in use, relative
    to the given :class:`.Table` or :class:`.Alias` passed as the
    ``selectable`` argument. The dialect implementation
    typically uses Python string substitution syntax
    with the token ``%(name)s`` to render the name of
    the table or alias. E.g. when using Oracle, the
    following::

        select([mytable]).\
            with_hint(mytable, "index(%(name)s ix_mytable)")

    Would render SQL as::

        select /*+ index(mytable ix_mytable) */ ... from mytable

    The ``dialect_name`` option will limit the rendering of a particular
    hint to a particular backend. Such as, to add hints for both Oracle
    and Sybase simultaneously::

        select([mytable]).\
            with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
            with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')

    .. seealso::

        :meth:`.Select.with_statement_hint`

    """
    if selectable is None:
        self._statement_hints += ((dialect_name, text),)
    else:
        self._hints = self._hints.union({(selectable, dialect_name): text})

with_hint 接受三个参数

selectable : 查询的表

text: 需要指定的索引字符串

dialect_name: 数据库类型

源码中只是给了sybase和oracle两个数据库的实例 对于mysql你应该按照下面的去配置。

query_sql = table_sa.select().where(
    ).order_by("create_time desc").with_hint(
        table_sa, "force index(idx_one, idx_two)", 'mysql',
    )


row_proxy = yield db_client.execute(
    query_sql
)

data = yield row_proxy.fetchall()