Mysql优化表优化

1. 核心原则

1. 不在数据库做运算
2. cpu计算务必移至业务层
3. 控制列数量(字段少而精,字段数建议在20以内);
4. 平衡范式与冗余(效率优先；往往牺牲范式)
5. 拒绝3B
6. (拒绝大sql语句：big sql、拒绝大事务：big transaction、拒绝大批量：big batch);

2. 字段类原则

1. 用好数值类型(用合适的字段类型节约空间);
2. 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
3. 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
4. 少用text类型(尽量使用varchar代替text字段);
5. 字段少而精，建议20个以内（经验之谈），超过可以拆分

• 把常用的字段放到一起
• 大字段(TEXT/BLOB/CLOB等等)独立出去
• 把不常用的字段独立出去

2.1. 用好数值字段类型

1. TINYINT(1Byte)
2. SMALLINT(2B)
3. MEDIUMINT(3B)
4. INT(4B)、BIGINT(8B)
5. FLOAT(4B)、DOUBLE(8B)
6. DECIMAL(M,D)

2.2. 将字符转化为数字

数字型VS字符串型索引

• 更高效
• 查询更快
• 占用空间更小

2.2.1. int类型存储时间-时间转换

unix_timestamp()函数是将日期格式的数据转换为int类型

FROM_UNIXTIME(timestr)函数是将int类型转换为时间格式

insert into test (timestr) values (unix_timestamp(current_timestamp));
select FROM_UNIXTIME(timestr) from test;

2.2.2. ip地址的存储

记录ip地址，varchar（15）进行存储，就需要15个字节进行存储，但是bigint只需要8个字节进行存储，当数据量很大的时候（千万级别的数据），相差7个字节

10000000字节(b)=9.5367432兆字节(mb)

create table sessions(
  id int auto_increment not null,
  ipaddress bigint,
  primary key (id)
)
insert into sessions (ipaddress) values (inet_aton('192.168.0.1'));
select inet_ntoa(ipaddress) from sessions;

2.3. 优先使用ENUM或SET

• 优先使用ENUM或SET

• 字符串
• 可能值已知且有限

• 存储

• ENUM占用1字节，转为数值运算
• SET视节点定，最多占用8字节
• 比较时需要加‘ 单引号(即使是数值)

• 举例

• `sex` enum('F','M') COMMENT '性别'
• `c1` enum('0','1','2','3') COMMENT '职介审核'

2.4. 避免使用NULL字段

• 避免使用NULL字段

• 很难进行查询优化
• NULL列加索引，需要额外空间
• 含NULL复合索引无效

2.5. 少用并拆分TEXT/BLOB

• TEXT类型处理性能远低于VARCHAR

• 强制生成硬盘临时表
• 浪费更多空间
• VARCHAR(65535)==>64K (注意UTF-8)

• 尽量不用TEXT/BLOB数据类型

• 若必须使用则拆分到单独的表

CREATE TABLE t1 (
 id INT NOT NULL AUTO_INCREMENT,
 data text NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB; 

2.6. 不在数据库里存图片

3. 索引类原则

1. 合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
2. 字符字段必须建前缀索引;
3. 不在索引做列运算;
4. innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
5. 不用外键(由程序保证约束);

4. 控制单表数据量

• 一年内的单表数据量预估

• • 纯INT 1000W
  • 含CHAR 500W

• 合理分表不超载

• • USERID
  • DATE
  • AREA
  • ….

• 建议单库不超过过300-400个表

5. 表字段数少而精

√ IO高效 √全表遍历 √表修复快

√提高幵发 √alter table快

• 单表多少字段合适？

• 单表1G体积 500W行评估

顺序读1G文件需N秒

单行不超过200Byte

单表不超过50个纯INT字段

单表不超过20个CHAR(10)字段

• 单表字段数上限控制在20~50个