Schema设计——高性能MYSQL

前言

不为啥，学校开课了，而且之前学的MYSQL太浅了，所以来啃书。

选择合适的数据类型

用作索引的id最好使用自增的数字类型，插入时写入的索引位置相近，能减轻页分裂、磁盘随机访问和碎片问题。SELECT语句也会变得更快，因为逻辑相邻的行索引位置也是相邻的，如果使用md5那种随机的散列，访问局部性原理会失效，从而导致缓存命中率降低

善用mysql不存在的类型表示你的数据，如可以用BIGINT来表示精度低于秒的TIMESTAMP，用32位无符号整数代替字符串来表示ip地址

Schema设计的陷阱

1. 太多的列
   Mysql的存储引擎需要在服务器和存储引擎层间通过行缓冲格式来拷贝数据，如果列太多，这里的开销会很大
2. 太多的关联
   MYSQL限制最多关联61张表
3. 枚举
   不要乱用枚举和SET
4. NULL
   虽然使用NULL会带来诸多坏处，但有时NULL比所有的替代选择都好用

范式化和反范式化

范式化就是我们把表解耦，通过两个表之间建立公共字段来连接查询的办法。
反范式化就是所有数据都存到一个表中。

虽然范式化好处多，但是不能太过极端，而且有时可以使用一些看似冗余的数据来加快查询速度，如在用户表中加入一个用户发帖数量的字段比在帖子表中筛选发送用户id来统计会快不少

缓存表和汇总表

比如想获得过去24小时内发送的消息数，比起直接查询整张消息表，每小时统计并插入到一个汇总表更好，虽然会带来一定的不精确

计数器表

比起使用只有一行数据的计数器表，把计数行增多会减轻行锁的竞争，提高效率。

CREATE TABLE counter(
  cnt int not unsigned not null,
  slot tinyint unsigned not null primary key
);

先插入100条cnt为0的行，slot为0-99

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

对于每日的计数器表，可以

CREATE TABLE counter(
  cnt int not unsigned not null,
  slot tinyint unsigned not null,
  day date not null,
  primary key(day,slot)
);

可以这样插入

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

ALTER

ALTER操作有时会重建整张表，他们的做法是创建一张新表，复制旧表的所有数据到新表，用新表替换旧表。这会带来很操蛋的性能问题，如果表很大那么这个操作可能会做几小时或几天。

使用ALTER COLUMN替代MODIFY COLUMN。

这个操作会重建整张表

ALTER TABLE film MODIFY COLUMN rate tinyint(3) not null default 5;

这个不会，这个操作会修改frm文件而不是重建表。

ALTER TABLE film ALTER COLUMN rate SET DEFAULT 5;

通过手动修改frm文件代替ALTER

以下两种修改可以通过修改frm来实现

1. 取消AUTO_INCREMENT
2. 添加修改或删除ENUM或SET中的项目

步骤

1. 创建一张具有相同结构的表，进行修改结构
2. 对旧表执行FLUSH TABLE WITH READ LOCK，结束并禁止所有对表的使用
3. 用新表的frm覆盖掉旧表的frm
4. 删除新表

这样做尽管是效率高的，但也有一些风险需要承担。