MySQL 建表 如何建立索引更有利

Question

CREATE TABLE `typecho_album` (
  `id`                  int(10)             unsigned            NOT NULL            auto_increment,
  `name`                text,                               
  `mime`                varchar(32)                             default NULL,
  `pixel`               text,                                               
  `size`                int(12)             unsigned            default '0',
  `created`             int(10)             unsigned            default '0',
  `description`         text,
  `url`                 text,
  `thumb`               text,
  `public`              int(1)              unsigned            default '1',
  `from`                varchar(32)                             default NULL,
  `category`            varchar(32)                             default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=%charset%;

这样一个表 要建立一个索引
看了不少 还是迷糊 看看大家的说法

检索方式
select * from table
可能存在的条件
where public = 1 (pubilc 只有 0 和 1 二个值)
where from = xxx (from 有3个值)
where category = xx (category 有1-N 个值 不确定)
where created < xx and created > xx ( 这个是重点 created 是创建时间，通过时间范围检索，比如 一天，一星期， 一个月，一年)

以上条件 可能都存在 可能都不存在 也可能其中1个或者多个

谢谢！

Answer 1

from 和 public 基数太小， 建了索引也未必能用得上。
MySQL通过分析每条索引内部数据分布的统计信息去确定使用哪个索引（是否使用索引）。某一列的所有唯一数据数据被称为基数，这个值越大，也就说明该列中唯一值的数量越多，那么越有可能在选用这个索引时以更少的读操作中找到需要的记录。知道索引中唯一值的数目意义很有限，重要的是将这个数值和索引中的总行数做比较。MySQL衡量索引可用度高低的指标叫做选择性：cardinality/selectivity(基数/字段总数)。理想情况下，选择性值为1，且每一个值都是一个非空唯一值。一个有着优秀选择性的索引意味着有更少的相同值的行。当某一列中仅仅有少数不同的值的时候就会有较差的选择性，例如性别或者状态列。我就遇到过性别的字段数据量很大，仍然使用全表扫描，因为数据库认为此事用索引效率更低。一种说法是可用度低于40%将弃用索引，我没有印证过真伪。

你可以给 category 和 created 创建联合索引， 并在PHP逻辑中处理成， 无论用户传入什么样的条件（无论是WHERE from AND public AND category 还是 没有 WHERE）， 都处理成 category 和 created 联合索引可以使用到的方方式，即：

SELECT * FROM table WHERE category = "{$category}" AND created > "{$time}";

如果有from 和 public， 加在后面，确保使用创建的索引。

如果没有from 和 public ， 且category 和 created 中只有一个， 确保另一个得到补齐，另一个随便指定成NOT NULL或长度>0。

你也可以按照别人的说法，但我这种方式是索引命中率最高的。

Answer 2

对于时间检索，要考虑时间数据的查询范围和查询性能，如果能做到时间上的数据汇总，这样能够减少数据库的压力。如果对于查询范围非常大的数据是会造成很大的性能上的问题。
建议采用列式数据库或者针对时间序列有特别的优化的数据库进行查询。
这样会大大减轻时间汇总和数据量大的问题。

Answer 3

悲剧，写了一大段，提交时遇到sf不能访问，懒得写了

高性能mysql第三版，5.4章有类似的例子，自己看看吧 >> http://yun.baidu.com/share/link?shareid=2425526850&uk=4197171002

最好仔细读读这本书

Answer 4

key p (public) 这个最好改成tinyint 或者 enum
key f (from) 这个是关键字 最好不用也可以改成enum
key cate (category)
key t (created)
key cate_f_p (category, from, public)

Answer 5

四个字段的联合索引 created要放到最后