SQLAlchemy中指定想要使用的索引

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

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

我们看下源码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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: 数据库类型

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

1
2
3
4
5
6
7
8
9
10
11
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()

推荐阅读一篇不错的文章:Mysql 你不知道的 Limit)

知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水!