我有8万多个客户,有4个组。现在我想找到 2 组用户,并在 mysql 中进行查询。我的查询如下所示:
select c.customers_firstname as recipient_firstname,
c.customers_lastname as recipient_lastname,
c.customers_id as recipient_id,
c.customers_email_address as recipient_email_address
from customers c
where customers_group_id = '1' OR customers_group_id = '3'
当我在phpmyadmin中运行此查询时,我得到的结果:显示行0 - 29(总共59,815,查询花费0.0034秒)
但是当我在此查询中添加按ORDER BY recipient_firstname ASC
顺序时,结果时间为:显示第 0 - 29 行(总共 59,815 行,查询耗时 0.2607 秒)
按查询排序的结果花费了太多时间。
我想通过查询减少订购时间。
如果有另一种方法可以在更短的时间内获得相同的结果,请提供帮助。
在recipient_firstname
字段上有一个索引(所以真的很customers.customers_firstname)。索引允许对结果集进行有序的线性时间迭代。
如果没有索引,则必须聚合结果集,然后对其进行排序。这种排序将是n log n
.对于大型集合来说,这显然非常慢,如果它不能放入内存(60k 记录可能不取决于配置),它将执行非常慢的基于文件的排序。
你需要一个索引。recipient_firstname
上的索引将使查询的性能非常接近非ORDER BY
版本。
顺便说一下,如果customers_group_id是整数字段,请使用整数文字,而不是字符串。它可能不会有什么不同,但它具有误导性,实际上在某些情况下它很重要。
根据情况,可能还值得在组 ID 上放置索引。对于小型集,可以在构建集时筛选结果,但对于大型结果集,最终将需要相当繁重的磁盘全表扫描。
您必须在customers_firstname
字段上索引:这将加快ORDER BY
速度,但也会减慢WHERE
(现在可能已编制索引)。
因此,索引必须按此顺序customers_group_id, customers_firstname
。
CREATE INDEX my_query_ndx
ON customers ( customers_group_id, customers_firstname );
理论上,您可以将索引放大为覆盖索引,并在两个关键字段之后包含SELECT
中所需的所有其他字段。不过,维持这种指数是昂贵的;您必须平衡优点和缺点。如果表非常"宽",则对组 ID、名字、姓氏、ID 和电子邮件进行索引可能是有利的。
小(或不太小)的查询改进
where customers_group_id = '1' OR customers_group_id = '3'
为了清楚起见,这可以重写为(它没有任何变化)作为
WHERE customers_group_id IN ('1','3')
但现在,要么customer_group_id
是整数字段,要么不是。如果是,那么最好这样对待:
WHERE customers_group_id IN (1, 3)
在某些情况下,您可以提前计划您的 ID,例如组 3 实际上是组 2,即您可能感兴趣的组是连续的。这样,您可以将查询重写为 variable < value
或 variable > value
或 variable BETWEEN
,这是OR
的两倍。使用大型OR
套装,您可以轻松获得 4 倍的加速。
如果它不是一个整数字段,那么一定要努力使其成为一个。整数性能(和索引大小)将受益匪浅(但请注意,对于字符串,"3"大于"12",就像"C"大于"AB"一样;因此,类型转换不一定没有副作用)。
尝试创建索引(customers_group_id, customers_firstname)
- 这应该有效。
您需要在应用顺序依据子句的列上创建索引。
CREATE INDEX index_name ON customers (customers_firstname);