Schema的优化和索引 - 范式和非范式

核心提示： CREATE TABLE msg_per_hr ( hr DATETIME NOT NULL, cnt INT UNSIGNED NOT NULL, PRIMARY KEY(hr));你可以把下列三个查询求和来计算过去24小时message的数，mysql> SELECT SUM(cnt)

CREATE TABLE msg_per_hr (
　　 hr DATETIME NOT NULL,
　　 cnt INT UNSIGNED NOT NULL,
　　 PRIMARY KEY(hr)
);

你可以把下列三个查询求和来计算过去24小时message的数。

mysql> SELECT SUM(cnt) FROM msg_per_hr
　　　 -> WHERE hr BETWEEN
　　　 ->　　　 CONCAT(LEFT(NOW( ), 14), '00:00') - INTERVAL 23 HOUR
　　　 ->　　　 AND CONCAT(LEFT(NOW( ), 14), '00:00') - INTERVAL 1 HOUR;
mysql> SELECT COUNT(*) FROM message
　　　 -> WHERE posted >= NOW( ) - INTERVAL 24 HOUR
　　　 ->　　　 AND posted < CONCAT(LEFT(NOW( ), 14), '00:00') - INTERVAL 23 HOUR;
mysql> SELECT COUNT(*) FROM message
　　　 -> WHERE posted >= CONCAT(LEFT(NOW( ), 14), '00:00');

不管是准确的计算和不准确的计算都要比计算所有message表的行数要高效很多。关键原因就在于创建了汇总表。这种统计如果实时统计消耗非常大，因为需要扫描的数据太多了，再就是查询所需要的索引，你可能也不会添加，因为这样太影响INSERT和UPDATE了。计算最活跃的用户和频率最高的TAG都是这样的操作。

来说说缓存表，它对于优化搜索和查询的语句非常有用。这些查询一般都需要特定的表和索引结构，这索引结构和应用于一般的OLTP操作的索引结构是不同的。

举个例子，你可能需要不同的索引组合来提升不同种类查询的性能。这些相互冲突的需求有的时候需要你创建一个包含主表一些列的缓存表。一个有用的技术就是对缓存表使用不同的存储引擎。如果主表使用的是INNODB，而对缓存表使用的是MyISAM，那么就可以使索引更小，并且可以做全文搜索的查询。有的时候你可能想让表完全脱离MySQL，可以使用特殊的系统来提升查询性能，比如Lucene或者Sphinx搜索引擎。