tinyint列优化mysql

Question

我有一个包含id (INT, primary key), name(VARCHAR), status(TINYINT)列的posts表。该帖子的状态可以是1, 2, 3 or 4。而且，比如说，我想选择状态2的帖子。我如何优化表以获得快速查询，因为为status字段添加索引不会有多大帮助。

我使用MySQL5.5。引擎可以是innodb或myisam。

谢谢

对注释的更新

据我所知，索引是如何工作的，当我们添加索引时，它某种程度上创建了该列的副本，但以有序的方式(或按字母顺序表示文本)，因此它提供了一种二进制搜索的可能性。因此，如果我们需要找到一些值，就可以避免对表进行全面扫描。但是在这种情况下，假设我在我的表中有100 K行，并且粗略地说-具有状态1, 2, 3 and 4的行数量相等。如果我为该字段添加索引，那么它应该对其余的25k行进行线性搜索，如果我们考虑到添加索引会减慢插入和更新速度，那么它可能就不值得了。

查询- SELECT id, name FROM posts WHERE status = 2

Answer 1

在这种情况下，索引的工作效果取决于几个因素。这里有三个例子，指数会运行良好(第三个多亏了Vatev)。

第一种情况是当状态2的记录很少时，比如说，您有一个有一百万条记录的表，而在任何给定的时间只有100条记录有状态2。索引会帮助你找到它们。

第二种情况是status是表中的主键(或者至少是主键中的第一列)。这将使用status = 2的任何表扫描限制为表的一部分。当然，拥有状态的主键会使状态的后续更新更加昂贵，因为数据必须根据状态的值进行物理重新定位。

第三种情况是，当status --或包含status的索引中的列--是覆盖索引时。这意味着查询所需的所有列都在索引中，因此引擎不必查找原始数据页。

一般来说，我建议不使用低基数字段的索引。这是一个普遍的规则，但在某些情况下，这样的索引可以提高性能。

Answer 2

我不知道您的应用程序或SQL需求

您可以在批中获取它们，不要使用限制，因为如果像这样查询的话，在表上没有效率，因为它会创建一个基于(磁盘/内存)的临时表。

SELECT id, name FROM posts WHERE status = 2 LIMIT 1000000, 1000000

如果您解释上面的查询，您将看到它将扫描2000000行，并将使用(磁盘/内存)临时表，最糟糕的情况是，如果内存已满，则需要一个基于磁盘的查询。

更好的方法是根据您的状态使用一个职位(确保索引)

SELECT id, name FROM posts WHERE status = 2 and position >= 1 and <= 1000000 
SELECT id, name FROM posts WHERE status = 2 and position >= 1000001  and <= 2000000 
...
... 

真正的低基数字段确实不应该被索引，更好的方法可能是您可以使用列表分区来测试以获得更高的性能，但是它仍然需要一个完整的“表(分区)”扫描。

有关示例，请参见http://sqlfiddle.com/#!2/d947c/7，并查看EXPLAIN分区语句，以了解当您拥有WHERE =1时，将只使用partition_post_status_id_1。

或者查看包含分区的覆盖索引方法http://sqlfiddle.com/#!2/20b0d/1。

此外，涉及聚合函数(如SUM()和COUNT() )的查询可以在分区上并行运行，以获得更高的性能。

Answer 3

如果您的要求是经常运行这种类型的查询，并且获取整个表的1/4，我建议您将表更改为使用InnoDB引擎和(status, id)的主键。

CREATE TABLE posts
( id INT, 
  name VARCHAR (whatever), 
  status TINYINT,
  PRIMARY KEY (status, id),
  UNIQUE INDEX (id)
) ENGINE = InnoDB ; 

这样，(id)仍然是唯一的，但是聚集索引(默认情况下InnoDB选择的索引是主键)首先是基于status的，因此您希望使用WHERE status = @X的数据按顺序存储在磁盘上。