Mysql数据库36条军规：血淋淋的教训（上）

前言

总是在灾难发生后，才想起容灾的重要性； 

总是在吃过亏后，才记得曾经有人提醒过。

来自一线的实战经验

每一军规背后都是血淋淋教训

不要华丽，只要实用

若有一条让各位有所受益，足以

别让脚趾头想事情，那是脑子的工作

正文

一.核心军规(5)

1.尽量不在数据库做运算：

比如：

1. 尽量不在数据库做运算

2. 复杂运算移到程序端CPU

3. 尽可能简单应用MySQL

md5() Order by Rand()

2.控制单表数据量

比如：

1. 一年内的单表数据量预估

2. 合理分表不超载

3. 纯INT不超1000W

4. 纯CHAR不超500W

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

3.保持表身段苗条

比如：

1. 表字段数少而精（ IO高效、全表遍历、表修复快、提高幵发、alter table快）

2. 单表1G体积 500W行评估

1. 顺序读1G文件需N秒

2. 单行不超过200Byte

3. 单表不超过50个纯INT字段

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

5. 建议：单表字段数上限控制在20~50个

4.平衡范式与冗余

比如：

1. 严格遵循三大范式

2. 效率优先、提升性能

3. 没有绝对的对不错

4. 适当时牺牲范式、加入冗余

5. 但会增加代码复杂度

5.拒绝3B

比如：

1. 数据库幵发像城市交通

1. 非线性增长

2. 拒绝3B

1. 大SQL (BIG SQL)

2. 大事务 (BIG Transaction)

3. 大批量 (BIG Batch)

核心军规小结

尽量不在数据库做运算

控制单表数据量

保持表身段苗条

平衡范式不冗余

拒绝3B

二.字段类军规(6) 

1.用好数值字段类型

比如：

1. TINYINT(1Byte)

2. SMALLINT(2B)

3. MEDIUMINT(3B)

4. INT(4B)、BIGINT(8B)

5. FLOAT(4B)、DOUBLE(8B)

6. DECIMAL(M,D)

坏的情况：

1. INT(1) VS INT(11)

2. BIGINT AUTO_INCREMENT

3. DECIMAL(18,0)

2.将字符转化为数字

• 数字型VS字符串型索引

• 更高效

• 查询更快

• 占用空间更小

比如：

用无符号INT存储IP，而非CHAR(15)

1. INT UNSIGNED

2. INET_ATON()

3. INET_NTOA()

3.优先使用ENUM或SET

存储

• ENUM占用1字节，转为数值运算

• SET视节点定，最多占用8字节

• 比较时需要加‘ 单引号(即使是数值)

比如：

• `sex` enum('F','M') COMMENT '性别'

• `c1` enum('0','1','2','3') COMMENT '职介审核'

4.避免使用NULL字段

1. 很难进行查询优化

2. NULL列加索引，需要额外空间

3. 含NULL复合索引无效
   

比如：

• `a` char(32) DEFAULT NULL

• `b` int(10) NOT NULL

• `c` int(10) NOT NULL DEFAULT 0

5.少用并拆分TEXT/BLOB

比如：

1. TEXT类型处理性能远低亍VARCHAR

1. 强制生成硬盘临时表

2. 浪费更多空间

3. VARCHAR(65535)==>64K (注意UTF-8)

2. 尽量不用TEXT/BLOB数据类型

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

6.不在数据库里存图片

字段类军规小结

用好数值字段类型

将字符转化为数字

优先使用枚举ENUM/SET

避免使用NULL字段

少用并拆分TEXT/BLOB

不在数据库里存图片

三.索引类军规(5) 

1.谨慎合理添加索引

1. 谨慎合理添加索引

• 改善查询

• 减慢更新

• 索引不是越多越好

2. 能不加的索引尽量不加

• 综合评估数据密度和数据分布

• 最好不超过字段数20%

3. 结合核心SQL优先考虑覆盖索引

比如：

不要给“性别”列创建索引

2.字符字段必须建前缀索引

1. 提高索引效率：

1. 减少索引的大小，从而减少磁盘空间的使用。

2. 加快索引的构建速度。

3. 提高查询性能，因为索引越小，查询时扫描的速度就越快。

2. 适用场景：

1. 当字段内容较长时（例如长文本），创建完整的索引可能导致索引过大，影响性能。

2. 当字段的前缀已经足够区分不同的记录时，创建前缀索引可以满足查询需求。

比如：

CREATE INDEX idx_name ON table_name (column_name(10));

这里 column_name(10) 表示对 column_name 字段的前 10 个字符创建索引。

3.不在索引列做运算

1. 不在索引列进行数学运算或函数运算

1. 无法使用索引

2. 导致全表扫描

比如：

反例 ：select * from table where to_days(current_date) - to_days(date_col) <= 10
正例 ：select * from table WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY); 

4.自增列或全局ID做INNODB主键

1. 对主键建立聚簇索引

2. 二级索引存储主键值

3. 主键不应更新修改

4. 按自增顺序插入入值

5. 忌用字符串做主键

6. 聚簇索引分裂

7. 推荐用独立于业务的AUTO_INCREMENT列或全局ID生成 器做代理主键

8. 若不指定主键，InnoDB会用唯一且非空值索引代替

5.尽量不用外键

1. 外键可节省开发量

2. 有额外开销

3. 逐行操作

4. 可‘到达’其它表，意味着锁

5. 高幵发时容易死锁

6. 由程序保证约束

索引类军规小结

谨慎合理添加索引

字符字段必须建前缀索引

不在索引列做运算

自增列或全局ID做INNODB主键

尽量不用外键

......

暂时先这样

下集明天更新

谢谢观看

写在最后

    如果您觉得这些文章对您有所启发和帮助，何不将它们与您的好友分享呢？这样，他们也能够享受其中的精彩内容，并从中获得启发。谢谢您的支持与分享！~

    同时也希望您用发财的手帮忙点个关注，可以通过下方菜单点击福利领取上千套简历模板、几千道的面试题pdf以及几百G涵盖了Java开发，前端开发，小程序开发，数据库，测试等等的相关学习书籍与资料。

另外也可以通过点击交流群按钮添加我好友，然后拉您到自己的创建的Java知识分享群。一起去讨论、学习、成长、进步，谢谢~