Mysql查询调优(大数据集)和解释计划

Question

我使用mysql5.1，我有一个表，它有大约1500万个(150万) records.This表，有不同实体的记录，即所有主实体的子记录。

该表中有8列，其中有6列为主键。这些列可能是单独的外键，但由于性能原因，我们已经做了此更改。

即使一个带有两个条件的简单select语句也采用6-8 seconds.Below作为相同的解释计划。

查询

explain extended 
 select distinct location_code, Max(trial_number) as replication
   from status_trait  t  
  where t.status_id='N02'
    and t.trial_data='orange'
  group by location_code

解释推广的结果

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
 1  SIMPLE  t   index       FK_HYBRID_EXP_TRAIT_DTL_2   5       1481572 100.00  Using where; Using index

我有以下问题：

1. 如何处理具有大数据的表
2. 是否为此表建立良好的索引

Answer 1

有两件事可能对你有帮助。

首先，在聚合查询中，SELECT DISTINCT是没有意义的。只需使用SELECT。

其次，您没有公开您创建的索引。然而，为了有效地满足这个查询，下面的复合覆盖索引可能会有很大帮助。

(status_id, trial_data, location_code, trial_number)

为什么这是正确的指数？因为MySQL索引被组织为BTREE。此组织允许服务器随机访问索引以查找特定值。在您的情况下，您需要status_id和trial_data的特定值。一旦服务器对索引进行了随机访问，就可以按顺序进行扫描.在这种情况下，您希望扫描location_code的各种值。服务器知道它将按照顺序找到这些不同的值。最后，服务器需要提取trial_number的值，以便在MAX()函数中使用。瞧，他们就在指数里，准备好准备采摘了。

(如果要对大型表进行大量的聚合和查询，那么了解复合索引和覆盖索引是如何工作的是有意义的。)

添加索引是有代价的:当您使用INSERT或UPDATE行时，您也必须更新索引。但是这种索引会大大加快你的检索速度。