mysql中用索引进行表连接_在MySQL中使用内连接表上的索引

我有200万条记录的表Foo和1000条记录的表格栏,它们是多对一连接的.列Foo.someTime和Bar.someField有索引.同样在Bar 900中,记录的某些字段为1,100,其中某些字段为2.

(1)此查询立即执行:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime between '2008-08-14' and '2018-08-14' and b.someField = 1 limit 20;

...

20 rows in set (0.00 sec)

(2)这个只需要永远(唯一的变化是b.someField = 2):

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;

(3)但是如果我在someTime上删除where子句而不是立即执行:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where b.someField = 2 limit 20;

...

20 rows in set (0.00 sec)

(4)我也可以通过强制索引使用来加快速度:

mysql> select * from Foo f inner join Bar b force index(someField) on f.table_id = b.table_id where f.someTime between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;

...

20 rows in set (0.00 sec)

这是关于查询(2)的解释(这需要永远)

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

| 1 | SIMPLE | g | range | bar_id,bar_id_2,someTime | someTime | 4 | NULL | 95022220 | Using where |

| 1 | SIMPLE | t | eq_ref | PRIMARY,someField,bar_id | PRIMARY | 4 | db.f.bar_id | 1 | Using where |

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

这是(4)(有力指数)的解释

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

| 1 | SIMPLE | t | ref | someField | someField | 1 | const | 92 | |

| 1 | SIMPLE | g | ref | bar_id,bar_id_2,someTime | bar_id | 4 | db.f.foo_id | 10558024 | Using where |

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

那么问题是如何教MySQL使用正确的索引?查询由ORM生成,并不仅限于这两个字段.并且避免更改查询会很好(尽管我不确定内连接是否适合这里).

更新:

mysql> create index index_name on Foo (bar_id, someTime);

之后,查询(2)在0.00秒内执行.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值