如何以最高效的方式查询此MySQL表? [英] How to query this MySQL table in the most performant way?

问题描述

假设我有一个这样创建的MySQL表:

Suppose I have a MySQL table that was created like this:

CREATE TABLE `my_table` (
  `my_id` char(32) NOT NULL,
  `my_boolean_field` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`my_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

此表非常大.它有大约一千万行. my_boolean_field的唯一可能值为1，0和null.

This table is very large. It has approximately 10 million rows in it. my_boolean_field's only possible values are 1, 0 and null.

现在分别列出500个ID.我们将此称为my_list.它们位于用换行符分隔的文本文件中.该文件的名称为myfile.txt.

Now separately, I have a list of 500 IDs. Let's call this my_list. They are in a text file separated by newlines. The name of that file is myfile.txt.

我想回答以下问题:

1. my_table中有多少行具有和my_list中的ID，并且my_boolean_field为空.
2. my_table中有多少行的ID为my_list和my_boolean_field == 1.
3. my_table中有多少行具有和my_list和my_boolean_field == 0中的ID.
4. my_table中有多少行具有和my_list中没有的ID，并且my_boolean_field为空.
5. my_table中有多少行具有和my_list和my_boolean_field == 1中没有的ID.
6. my_table中有多少行具有和my_list和my_boolean_field == 0中没有的ID.

1. How many of the rows in my_table have and ID that is in my_list and my_boolean_field is null.
2. How many of the rows in my_table have and ID that is in my_list and my_boolean_field==1.
3. How many of the rows in my_table have and ID that is in my_list and my_boolean_field==0.
4. How many of the rows in my_table have and ID that is not in my_list and my_boolean_field is null.
5. How many of the rows in my_table have and ID that is not in my_list and my_boolean_field==1.
6. How many of the rows in my_table have and ID that is not in my_list and my_boolean_field==0.

获得上述6个计数的最有效方法是什么?

What is the most performant way to get the above 6 counts??

推荐答案

您将必须进行全表扫描，因此这可能是最好的解决方案:

You will have to do a full table scan, so this is probably the best solution:

select (case when id in (< your list >) then 'in' else 'out' end) as inlist,
       my_boolean_field, count(*)
from mytable t
group by (case when id in (< your list >) then 'in' else out' end),
         my_boolean_field;

如果列表在带有索引的表中，则可以在其上使用left join.但是，MySQL使用常量值优化对in的搜索(它使用二进制搜索).因此，这可能是最快的方法.

If your list is in a table with an index, then you can use a left join on it. However, MySQL optimizes searches for in with constant values (it uses a binary search). So this is likely to be the fastest method.

这篇关于如何以最高效的方式查询此MySQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！