【最佳实践】 - MySQL

概述

本文介绍如何玩转 MySQL, 可以说是 MySQL 开发规范, 也可以理解为 MySQL 最佳实战.

一、基础规范

1. 使用 InnoDB 存储引擎
2. 数据库字符集使用 UTF8，校对字符集使用 utf8_general_ci
3. 所有 表 、字段 都尽量 添加注释
4. 库名、表名、字段名 使用 小写字母，禁止 超过32个字符 ，须见名知意
5. 非唯一索引 以 “idx_字段1_字段2” 命名，唯一索引 必须以 “uniq_字段1_字段2” 命名

二、查询规范

1. SQL 语句尽可能简单，大的 SQL 想办法 拆分 成小的 SQL 实现
2. *不要使用SELECT ** ，查询具体要用到的字段
3. 禁止 like 做 where 条件 （会全表扫描且不能用索引）
4. 除非必要，避免使用 != 等非等值操作符 （会导致用不到索引）
5. Where 条件里不要对列使用函数 （不会引用索引）
6. 能确定返回结果只有一条时，使用limit 1 （LIMIT分页注意效率，LIMIT越大，效率越低）
7. 少用 子查询，改用 JOIN（子查询要在内存里建临时表）
8. 多表 JOIN 的字段，区分度最大的字段放在前面
9. IN 条件里的 数据数量要尽量少，超过200 个用 EXIST 代替 IN
10. Where 字句中同一个表的 不同字段组合 建议 小于5组 （否则考虑分表）
11. 禁止单条语句同时更新多个表
12. 事务要尽量简单，整个事务的时间长度不要太长

三、表设计规范

1. 用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数（精确数据）
2. 使用 TINYINT 代替 ENUM 类型（便于迁移时兼容）
3. 尽可能不使用 TEXT、BLOB 类型 （该数据类型不能设置默认值、不便于排序、不便于建立索引）
4. 同一意义的字段设计定义必须相同（便于联表查询）
5. 所有字段 均定义为 NOT NULL（避免使用NULL字段，NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效）
6. 表必须有主键，不使用更新频繁的列做主键 、尽量不使用字符串列做主键，尽量使用非空的唯一自增键做主键

四、索引设计规范

1. 单表索引数量不超过10个
2. 单个字段不要超过两个索引
3. 新建的唯一索引必须不能和主键重复
4. 避免冗余和重复索引
5. 尽量不要在频繁更新的列上建立索引
6. 不在低基数列上建立索引，例如状态、类型等
7. 不在索引列进行数学运算和函数运算（参与了运算的列不会引用索引）
8. 复合索引须符合最左前缀的特点建立索引（mysql使用复合索引时从左向右匹配）
9. 重要的 SQL 中 where 条件里的字段必须被索引
10. Where 条件里的 字段顺序 与 索引顺序 无关，优化器会自动调整
11. 索引选择性 = Cardinality / Total Rows，即基数 ÷ 数据行数，值越接近1说明使用索引的过滤效果越好
12. 建立索引时，务必先explain，查看索引使用情况

最佳实践

666 彩蛋

刚开始写博客, 希望大家支持, 如果有没疑问或不清楚的地方可以留言噢!

下周再见~