老大让我整理下公司内部 MySQL 使用规范，分享给大家
点击上方“码农突围”，马上关注
这里是码农充电第一站，回复“666”，获取一份专属大礼包
真爱，请设置“星标”或点个“在看”
数据库环境
dev: 开发环境
test: 测试环境
online: 线上环境
命名规范
基本命名规则
使用有意义的英文词汇，词汇中间以下划线分隔。（不要用拼音） 只能使用英文字母，数字，下划线，并以英文字母开头。 库、表、字段全部采用小写，不要使用驼峰式命名。 避免用ORACLE、MySQL的保留字，如desc，关键字如index。 命名禁止超过32个字符，须见名之意，建议使用名词不是动词 数据库，数据表一律使用前缀 临时库、表名必须以tmp为前缀，并以日期为后缀 备份库、表必须以bak为前缀，并以日期为后缀
为什么库、表、字段全部采用小写？
Windows下是不区分大小写的。 Linux下大小写规则 数据库名与表名是严格区分大小写的； 表的别名是严格区分大小写的； 列名与列的别名在所有的情况下均是忽略大小写的； 变量名也是严格区分大小写的； 如果已经设置了驼峰式的命名如何解决？需要在MySQL的配置文件my.ini中增加 lower_case_table_names = 1即可。
表命名
字段命名
表达其实际含义的英文单词或简写。布尔意义的字段以is_作为前缀，后接动词过去分词。 各表之间相同意义的字段应同名。各表之间相同意义的字段，以去掉模块前缀的表名_字段名命名。 外键字段用表名_字段名表示其关联关系。 表的主键一般都约定成为id，自增类型，是别的表的外键均使用xxx_id的方式来表明。
索引命名
非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名
约束命名
主键约束：pk_表名称。 唯一约束：uk_表名称_字段名。（应用中需要同时有唯一性检查逻辑。）
表设计规范
为什么字符集不选择utf8，排序规则不使用utf8_general_ci
用尽量少的存储空间来存数一个字段的数据 能用int的就不用char或者varchar 能用tinyint的就不用int 使用UNSIGNED存储非负数值。 不建议使用ENUM、SET类型，使用TINYINT来代替 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE 时间字段，除特殊情况一律采用int来记录unix_timestamp 存储年使用YEAR类型。 存储日期使用DATE类型。 存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。 建议使用INT UNSIGNED存储IPV4。 尽可能不使用TEXT、BLOB类型 禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储（TFS/SFS），MySQL只保存指针信息。 单条记录大小禁止超过8k（列长度(中文)_3(UTF8)+列长度(英文)_1）
datetime与timestamp有什么不同？
4个字节储存，时间范围：1970-01-01 08:00:01 ~ 2038-01-19 11:14:07值以UTC格式保存，涉及时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。 datetime8个字节储存，时间范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 实际格式储存，与时区无关 如何使用TIMESTAMP的自动赋值属性？
如何使用INT UNSIGNED存储ip？
如无备注，所有字段都设置NOT NULL，并设置默认值； 禁止在数据库中存储明文密码 如无备注，所有的布尔值字段，如is_hot、is_deleted，都必须设置一个默认值，并设为0； 如无备注，排序字段order_id在程序中默认使用降序排列； 整形定义中不添加长度，比如使用INT，而不是INT[4]
使用VARBINARY存储大小写敏感的变长字符串
索引设计规范
什么是覆盖索引
索引的基本规范
索引数量控制，单张表中索引数量不超过5个，单个索引中的字段数不超过5个。 综合评估数据密度和分布 考虑查询和更新比例
为什么一张表中不能存在过多的索引？
什么是前缀索引？
主键准则
表必须有主键 不使用更新频繁的列 尽量不选择字符串列 不使用UUID MD5 HASH 默认使用非空的唯一键 建议选择自增或发号器
重要的SQL必须被索引，核心SQL优先考虑覆盖索索引
UPDATE、DELETE语句的WHERE条件列 ORDER BY、GROUP BY、DISTINCT的字段 多表JOIN的字段
区分度最大的字段放在前面
选择筛选性更优的字段放在最前面，比如单号、userid等，type，status等筛选性一般不建议放在最前面 索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引,(a,c)作为条件的时候只能使用到a列索引,所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理,(b,c)则不能走索引 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
索引禁忌
不在低基数列上建立索引，例如“性别” 不在索引列进行数学运算和函数运算 不要索引常用的小型表 尽量不使用外键 外键用来保护参照完整性，可在业务端实现 对父表和子表的操作会相互影响，降低可用性 INNODB本身对online DDL的限制
MYSQL 中索引的限制
MYISAM 存储引擎索引长度的总和不能超过 1000 字节 BLOB 和 TEXT 类型的列只能创建前缀索引 MYSQL 目前不支持函数索引 使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。 过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。 join语句中join条件字段类型不一致的时候MYSQL无法使用索引 使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。 使用非等值查询的时候, MYSQL 无法使用 Hash 索引。
语句设计规范
使用预编译语句
只传参数，比传递SQL语句更高效 一次解析，多次使用 降低SQL注入概率
避免隐式转换
会导致索引失效
充分利用前缀索引
必须是最左前缀 不可能同时用到两个范围条件 不使用%前导的查询，如like “%ab”
不使用负向查询，如not in/like
无法使用索引，导致全表扫描 全表扫描导致buffer pool利用率降低
避免使用存储过程、触发器、UDF、events等
让数据库做最擅长的事 降低业务耦合度，为sacle out、sharding留有余地 避开BUG
避免使用大表的JOIN
MySQL最擅长的是单表的主键/二级索引查询 JOIN消耗较多内存，产生临时表
避免在数据库中进行数学运算
MySQL不擅长数学运算和逻辑判断 无法使用索引
减少与数据库的交互次数
INSERT … ON DUPLICATE KEY UPDATE REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),() UPDATE … WHERE ID IN(10,20,50,…)
合理的使用分页
如何正确的使用分页？
http://example.com/page.php?last=100
select * from table where id<100 order by id desc limit 10
//上一页
http://example.com/page.php?first=110
select * from table where id>110 order by id desc limit 10
select * from table where id >= (select id from table order by id limit #offset#, 1)
拒绝大SQL，拆分成小SQL 充分利用QUERY CACHE 充分利用多核CPU 使用in代替or，in的值不超过1000个 禁止使用order by rand() 使用EXPLAIN诊断，避免生成临时表
用union all而不是union
select * from test_union1
union select * from test_union2
select * from test_union1 union all select * from test_union2
程序应有捕获SQL异常的处理机制 禁止单条SQL语句同时更新多个表 不使用select * ，SELECT语句只获取需要的字段 消耗CPU和IO、消耗网络带宽 无法使用覆盖索引 减少表结构变更带来的影响 因为大，select/join 可能生成临时表 UPDATE、DELETE语句不使用LIMIT INSERT语句必须显式的指明字段名称，不使用INSERT INTO table() INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对Myisam 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新 禁止使用跨库查询 禁止使用子查询，建议将子查询转换成关联查询 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；
分表规范
用HASH进行散表，表名后缀使用十进制数，下标从0开始 按日期时间分表需符合YYYY[MM][dd][HH]格式 采用合适的分库分表策略。例如千库十表、十库百表等 禁止使用分区表，分区表对分区键有严格要，分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。 拆分大字段和访问频率低的字段，分离冷热数据
行为规范
批量导入、导出数据必须提前通知DBA协助观察 禁止在线上从库执行后台管理和统计类查询 禁止有super权限的应用程序账号存在 产品出现非数据库导致的故障时及时通知DBA协助排查 推广活动或上线新功能必须提前通知DBA进行流量评估 数据库数据丢失，及时联系DBA进行恢复 对单表的多次alter操作必须合并为一次操作 不在MySQL数据库中存放业务逻辑 重大项目的数据库方案选型和设计必须提前通知DBA参与 对特别重要的库表，提前与DBA沟通确定维护和备份优先级 不在业务高峰期批量更新、查询数据库其他规范 提交线上建表改表需求，必须详细注明所有相关SQL语句
其他规范
最近热文
• 程序员因接外包被判坐牢 456 天！两万字长文揭露心酸真实经历... • GitHub标星3.5K，阿里巴巴工程师开源的这个Java诊断工具也太好用了吧！ • 牛逼了，日本神秘男子用 AI 黑科技破解马赛克，震惊业界大佬！ • 别死写代码，这 25 条比涨工资都重要 最近整理了一份大厂算法刷题指南，包括一些刷题技巧，在知乎上已经有上万赞。同时还整理了一份6000页面试笔记。关注下面公众号，在公众号内回复「刷题」，即可免费获取！回复「加群」，可以邀请你加入读者群！
明天见(｡･ω･｡)ﾉ♡
评论