查询慢-不使用相关索引

Question

该表大约有700万行。下面的查询大约需要7秒时间，似乎没有使用任何相关索引：

select 
lead_id as id , 
user_id , 
cmol_status_id , 
crm_status_id , 
product_status_id , 
callmeback , 
followup_date , 
lead_attempt_id , 
allocated_to , 
new_id , 
leadpriority 
  from gen_que 
 where lead_attempt_id > '1' 
   and followup_date <= curdate() 
   and lead_type in ('User', 'LenderOffer') 
   and crm_status_id in ('147', '180', '181', '182') 
   and product_family_id in ('1', '2', '21', '23', '19') 
   and product_status_cat in ('01', '02', '05', '06', '07','021', '022', '023') 
   and new_id in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '29', '30', '31', '32', '33', '35', '36', '37', '39', '40', '41', '42') 
order by lead_id desc limit 5;

解释扩展结果：

**id select_type table       type
  1  SIMPLE      gen_queview index

possible_keys
igen_queview_attemptallocated, igen_queview_followup_date, igen_queview_newcmol_id,  
igen_queview_crmstatus_id, igen_queview_prodstatuscat, igen_queview_prodfamiliyid

key     key_len ref rows filtered Extra 
PRIMARY 8       \N  1023 2921.7   Using where**

如果我使用一个值，而不是在' in‘子句中检查多个值，那么结果是在.03 secs中。

有人能帮忙吗？提前谢谢。

Answer 1

根据我对索引的了解(我最近没有使用MySql )，首先:选择列表中的任何字段都应该被索引。

但我注意到在给定的查询中可能发生的另一个可能的行为:在INs中，您正在查找字符串(“1”、“2”、“等”)，这些字段是Varchars、Chars吗？

如果不使用INT，则尝试使用INT代替字符串，如果您的"product_family_id“为INT，并且正在执行"product_family_id IN ('1'，'2')，则引擎执行隐式转换，从而导致更多的执行时间。

如果"product_family_id“是字符串，并且将数字存储为文本。如果只有数字存储在其中(但作为字符串)，则尝试转换为int。

cast(product_family_id as int) IN (1,2,3,4,5)

两种解决方案都试一试。向选择列表成员添加索引，使用数字而不是文本。也试着选演员。

Answer 2

MySQL很少使用“索引合并联合”(可能与Server更乐意做的事情相同)。这是因为它效率低下。

通常，使用WHERE ... AND ...加速查询的最佳方法是有一个复合索引(多列)。但是，这是实用的，只有当第一列是用=测试，而只有最后一列可能是一个范围或IN。

因此，该查询的最佳MySQL操作是使用单列索引(或等效地)只使用多列索引的第一列)。

请提供SHOW CREATE TABLE。同时，我猜它做了一次“表扫描”，因为即使是范围也不是很有选择性，它决定只扫描整个表比在索引和数据之间跳来跳去要快。

由于您有ORDER BY ... LIMIT ...，优化器可能希望避免ORDER BY通常需要的“排序”可能导致在满足LIMIT之前只扫描几行。如果是这样的话，那么添加

INDEX(lead_id)

(或者那是PRIMARY KEY？)

lead_id是BIGINT吗？你真的想要数以百万计的身份证？