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

Question

我使用的是 mysql5.1，我有一个包含大约 150 万(150 万)条记录的表。该表包含不同实体的记录，即所有主实体的子记录。

该表中有 8 列，其中 6 列被组合起来形成主键。这些列可能是单独的外键，但由于性能原因，我们进行了此更改。

即使是带有两个条件的简单 select 语句也需要 6-8 秒。下面是其解释计划。

查询

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 行时，您还必须更新索引。但这种索引会大大加快你的检索速度。