数据库 schema名称-数据库表schema

MySQL模式设计中的缺陷

太多列

MySQL的存储引擎API工作时，需要通过行缓冲区格式在服务器层和存储引擎层之间复制数据，然后在服务器层将缓冲区内容解码成列。 将编码的列从行缓冲区转换为行数据结构非常昂贵。 转换成本取决于列数。 当我们研究一个CPU使用率很高的案例时，我们发现客户使用了一个很宽的表，但实际使用的列只有一小部分，此时转换的成本很高。

MySQL 将每个关联操作限制为最多 61 个表。 作为一个粗略的经验，如果想要查询执行速度快数据库 schema名称，并发性好，最好在12张表内关联一次查询。

全能枚举

注意放置过度使用的枚举

如果您不列举，而是引用一组完整的数字，那将是不礼貌的。

变相枚举

枚举列允许将定义的一组值中的单个值存储在列中，集合列允许将一组定义的值中的一个或多个值存储在列中。

例如

create TABLE 。。。 （
is_default set('Y','N') NOT NULL default 'N'
）

这里需要注意的是，这种真假情况不会同时出现，那么我们无疑应该用枚举来代替这个集合。

未发明的 null

我们之前写过避免 null 的好处，并建议尽可能考虑替代方案。 比如我们可以用0，或者一些特殊的字符来代替null。

但要遵循这个原则，不要走极端。 当您确实需要表示未知值时数据库 schema名称，不要害怕使用 null。

范式与反范式

范式：

范式是符合一定层次的关系模式的集合。 关系数据库中的关系必须满足一定的要求，满足不同层次要求的是不同的范式。

第一范式 (1NF)

在任何关系数据库中，第一范式（1NF）[2]是关系模式的基本要求，不满足第一范式（1NF）的数据库不是关系数据库。

第二范式 (2NF)

它建立在第一范式（1NF）的基础上，即要满足第二范式（2NF）必须先满足第一范式（1NF）。 第二范式 (2NF) 要求数据库表中的每个 [实例] 或行必须是唯一可区分的。 为了实现区分，通常需要在表中增加一列来存储每个实例的唯一标识。 这个唯一的属性列叫做[primary key] 或primary key，primary code。

范式的优缺点

优势：

缺点：

反规范化的优点和缺点

非规范化模式很好地避免了关联，因为所有数据都在一个表中。

混合规范化和反规范化

对数据进行非规范化的最常见方法是复制或缓存，将相同的特定列存储在不同的表中。 我们还可以使用触发器来更新缓存值，这使得实现此类场景变得更加简单。

缓存表和汇总表

有时提高性能的最佳方法是将派生的冗余数据存储在同一个表中。 但是，有时需要创建一个完全独立的汇总表或缓存表。

我们使用术语缓存表来指代存储数据的表，这些数据可以相对容易地从模式中的其他表中获取。 另一方面，术语汇总表包含一个使用 group by 语句聚合数据的表。

我们使用汇总表，这比我们扫描表的所有行要高效得多。

相反，缓存表对于优化搜索和检索查询非常有效。 这些查询通常需要特殊的表和索引结构。 例如：可能需要许多不同的索引组合来加速各种类型的查询。 这些相互冲突的要求有时会创建一个仅包含主表中某些列的缓存表。 一个有用的技巧是我可以使用不同的存储引擎。 比如主表用的是innodb，我能不能用myisam作为缓存表的引擎，索引占用更小，全文搜索。

使用缓存表和汇总表时，我必须决定是实时维护数据还是定期重建数据。 哪个更好取决于应用程序，但定期重建不仅可以节省资源，还可以防止表变得如此碎片化，并且具有完全按顺序组织的索引。

当然，为了安全起见，我们在重建这些表的时候也会使用影子表来保证运行过程中数据也是可用的。

物化视图计数器表

计数器表是经常使用的东西，我们使用单独的表来帮助避免查询缓存失效。

让我们展示一些更高级的技巧：

比如我们有一个计数器表，记录了这个网站的点击次数，但是我们每次修改的时候都有一个全局互斥锁，导致这些事务只能串行执行。 如果我们想获得更好的性能，我们可以将计数器保存在多行中，每次随机选择一行进行更新。 我们像这样更新计数表：

CREATE TABLE hit_counter(
slot tinyint unsigned not null primary key ,
cnt int unsigned not null
)ENGINE = InnoDB

我们预先在表中添加100行数据，随机选择一个slot进行更新：

UPDATE hit_counter SET cnt = cnt +1 WHERE slot = RAND()*100;

为了计算结果，我们使用这样的聚合查询：

SELECT SUM(cnt) FROM hit_counter;

我们的一个共同需求是每隔一段时间启动一个新的计数器，我们修改表如下：

CREATE TABLE daily_hit_counter(
day date not null,
slot tinyint unsigned not null,cnt int unsigned not null,
primary key (day,slot)
)ENGINE = InnoDB;

在这种情况下，我们不需要预先生成行，而是使用on duplicate key更新语句（存在则更新，不存在则插入）

INSERT INTO daily_hit_counter(day,slot,cnt)
VALUES (CURRENT_DATE,RAND()*100,1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

如果想减少表的行数，防止表变得太大，可以写一个周期性执行的任务，将所有结果合并到slot 0，删除所有其他slot：

UPDATE daily_hit_counter as c
    INNER JOIN (
    SELECT day,SUM(cnt)AS cnt,MIN(slot)AS mslot
    FROM daily_hit_counter
    GROUP BY day)AS x USING(day)
SET  c.cnt = IF(c.slot = x.mslot,x.slot,0),
    c.slot = IF (c.slot = x.mslot,0,c.slot);

DELETE FROM daily_hit_counter WHERE slot <>0 AND cnt = 0;

加速 alter TABLE 操作

MySQL对于大表的alter TABLE一直是个大问题。  mysql 执行大多数修改表结构的操作的方式是创建一个具有新结构的空表，然后将旧表中的数据插入到新表中。

对于常见的场景，只有两种场景可以使用：

并非所有的 alter TABLE 操作都会导致表重建。 例如，有两种方法可以更改或删除列的默认值（一种是快速的，一种是慢速的）。

缓慢的方式：

ALTER TABLE sakila.film 
MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

这种方法比较慢，因为modify方法会导致重建表。

ALTER TABLE sakila.film 
ALTER  COLUMN rental_duration  SET DEFAULT 5;

这个alter方法非常快，因为他直接修改.firm文件，不涉及表数据。 所以这个操作非常快。

只修改.frm文件即可快速创建索引