我們知道在MySQL中可以使用FORCE INDEX(index_name,...)來強(qiáng)制使用索引，那在SQLAlchemy如何指定呢唆铐？
經(jīng)過多方查找和實(shí)踐淮菠，我發(fā)現(xiàn)了一個(gè)叫with_hint的函數(shù)遍愿。
我們看下源碼
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 接受三個(gè)參數(shù)
selectable : 查詢的表
text: 需要指定的索引字符串
dialect_name: 數(shù)據(jù)庫類型
源碼中只是給了sybase和oracle兩個(gè)數(shù)據(jù)庫的實(shí)例 對(duì)于mysql你應(yīng)該按照下面的去配置存淫。
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()
推薦閱讀一篇不錯(cuò)的文章：Mysql 你不知道的 Limit
