史上最全MySQL运用标准分享

【相关学习引荐：mysql教程】

比来触及数据库相关操纵较多，公司现有标准也不是太全面，就按照网上各路大神的相关标准，整理了一些自用的标准用途，万望指正。

数据库环境

dev: 开发环境

开发可读写，可修改表构造。开发人员可以修改表构造，可以随便修改其中的数据但是需要包管不影响其他开发同事。

test: 测试环境

开发可读写，开发人员可以通过工具修改表构造。

online: 线上环境

开发人员不同意直接在线上环境停止数据库操纵，假如需要操纵必需寻DBA停止操纵并停止响应记载，制止停止压力测试。

重点的问题，各个环境的mysql效劳器对应的会员权限，必然要做到权限划清楚确，有辨识度，能详细区分业务场景等。

命名标准

根本命名规则

• 使用成心义的英文词汇，词汇中心以下划线分隔。（不要用拼音）
• 只能使用英文字母，数字，下划线，并以英文字母开头。
• 库、表、字段全部采纳小写，不要使用驼峰式命名。
• 幸免用ORACLE、MySQL的保存字，如desc，关键字如index。
• 命名制止超越32个字符，须见名之意，倡议使用名词不是动词
• 数据库，数据表一概使用前缀
• 暂时库、表名必需以tmp为前缀，并以日期为后缀
• 备份库、表必需以bak为前缀，并以日期为后缀

为什么库、表、字段全部采纳小写？

在 MySQL 中，数据库和表对就于那些名目下的名目和文件。因此，操纵系统的敏锐性决议数据库和表命名的大小写敏锐。

• Windows下是不区分大小写的。
• Linux下大小写规则
• 数据库名与表名是严厉区分大小写的；
• 表的别号是严厉区分大小写的；
• 列名与列的别号在所有的状况下均是忽略大小写的；
• 变量名也是严厉区分大小写的；
• 假如已经设定了驼峰式的命名怎样解决？需要在MySQL的配置文件my.ini中增添 lower_case_table_names = 1即可。
  

表命名

统一个模块的表尽大概使用雷同的前缀，表名称尽大概表达含义。所有日志表均以 log_ 开头

字段命名

• 表达其实际含义的英文单词或简写。布尔意义的字段以is_作为前缀，后接动词过去分词。
• 各表之间雷同意义的字段应同名。各表之间雷同意义的字段，以去除模块前缀的表名_字段名命名。
• 外键字段用表名_字段名表示其关联关系。
• 表的主键一样都约定成为id，自增类型，是别的表的外键均使用xxx_id的方式来表白。

索引命名

• 非独一索引必需依照“idx_字段名称_字段名称[_字段名]”停止命名
• 独一索引必需依照“uniq_字段名称_字段名称[_字段名]”停止命名
  

束缚命名

• 主键束缚：pk_表名称。
• 独一束缚：uk_表名称_字段名。（利用中需要同时有独一性检查逻辑。）
  

表设计标准

表引擎取决于实际利用场景；日志及报表类表倡议用myisam，与交易，考核，金额相关的表倡议用innodb引擎。如无说明，建表时一概采纳innodb引擎

默许使用utf8mb4字符集，数据库排序规则使用utf8mb4_general_ci，（由于数据库定义使用了默许，数据表可以不再定义，但为保险起见，倡议都写上

为什么字符集不选中utf8，排序规则不使用utf8_general_ci

采纳utf8编码的MySQL没法留存占位是4个字节的Emoji表情。为了使后端的项目，全面支撑客户端输入的Emoji表情，升级编码为utf8mb4是最好解决方案。关于JDBC连接串设定了characterEncoding为utf8或者做了上述配置仍然没法正常插入emoji数据的状况，需要在代码中指定连接的字符集为utf8mb4。

所有表、字段均利用 comment 列属性来描写此表、字段所代表的真正含义，如枚举值则倡议将该字段中使用的内容都定义出来。

如无说明，表中的第一个id字段必然是主键且为主动增长，制止在非事务内作为上下文作为前提停止数据传递。制止使用varchar类型作为主键语句设计。

如无说明，表必需包括create_time和modify_time字段，即表必需包括记载创立时间和修改时间的字段

如无说明，表必需包括is_del，用来标示数据可否被删除，原则上数据库数据不同意物理删除。

• 用尽量少的储备空间来存数一个字段的数据
• 能用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有什么不一样？

雷同点：

TIMESTAMP列的显示格局与DATETIME列雷同。显示宽度牢固在19字符，并且格局为YYYY-MM-DD HH:MM:SS。

不一样点：

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的主动赋值属性？

将当前时间作为ts的默许值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。当行更新时，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。

可以将1和2结合起来：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

怎样使用INT UNSIGNED储备ip？

使用INT UNSIGNED而不是char(15)来储备ipv4地址，通过MySQL函数inet_ntoa和inet_aton来停止转化。Ipv6地址当前没有转化函数，需要使用DECIMAL或者两个bigINT来储备。

• 如无备注，所有字段都设定NOT NULL，并设定默许值；
• 制止在数据库中储备明文密码
• 如无备注，所有的布尔值字段，如is_hot、is_deleted，都必需设定一个默许值，并设为0；
• 如无备注，排序字段order_id在程序中默许使用落序摆列；
• 整形定义中不增加长度，比方使用INT，而不是INT[4]
  

INT[M]，M值代表什么含义？

留意数值类型括号后面的数字只是表示宽度而跟储备范畴没有关系。许多人他们认为INT(4)和INT(10)其取值范畴离别是 (-9999到9999)和(-9999999999到9999999999)，这种懂得是错误的。其实对整型中的 M值与 ZEROFILL 属性结合使用时可以实现列值等宽。不管INT[M]中M值是多少，其取值范畴还是 (-2147483648到2147483647 有符号时)，(0到4294967295无符号时)。

显示宽度并不限制可以在列内留存的值的范畴，也不限制超越列的指定宽度的值的显示。当结合可选扩展属性ZEROFILL使用时默许补充的空格用零代替。例如：关于声明为INT(5) ZEROFILL的列，值4检索为00004。请留意假如在整数列留存超越显示宽度的一个值，当MySQL为复杂联接生成暂时表时会碰到问题，由于在这些状况下MySQL信赖数据适合原列宽度，假如为一个数值列指定ZEROFILL, MySQL主动为该列增加UNSIGNED属性。

使用VARBINARY储备大小写敏锐的变长字符串

什么时候用CHAR，什么时候用VARCHAR？

CHAR和VARCHAR类型相似，但它们留存和检索的方式不一样。它们的最大长度和可否尾部空格被保存等方面也不一样。CHAR和VARCHAR类型声明的长度表示你想要留存的最大字符数。例如，CHAR(30)可以占用30个字符。

CHAR列的长度牢固为创立表时声明的长度。长度可认为从0到255的任何值。当留存CHAR值时，在它们的右侧填充空格以到达指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在储备或检索历程中不停止大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集肯定。团体最大长度是65,532字节）。同CHAR对照，VARCHAR值留存时只留存需要的字符数，另加一个字节来记载长度(假如列声明的长度超越255，则使用两个字节)。VARCHAR值留存时不停止填充。当值留存和检索时尾部的空格仍保存，相符标准SQL。

char适合储备会员密码的MD5哈希值，它的长度总是一样的。关于经常改动的值，char也好于varchar,由于牢固长度的行不容易发生碎片，关于很短的列，char的效力也高于varchar。char(1)字符串关于单字节字符集只会占用一个字节，但是varchar(1)则会占用2个字节，由于1个字节用来储备长度信息。

索引设计标准

MySQL的查询速度依靠良好的索引设计，因此索引关于高机能至关重要。合理的索引会加快查询速度（包罗UPDATE和DELETE的速度，MySQL会将包括该行的page加载到内存中，然后停止UPDATE或者DELETE操纵），不合理的索引会落低速度。MySQL索引查寻相似于新华字典的拼音和部首查寻，当拼音和部首索引不存在时，只能通过一页一页的翻页来查寻。当MySQL查询不克不及使用索引时，MySQL会停止全表扫描，会耗损大量的IO。索引的用处：去重、加快定位、幸免排序、覆盖索引。

什么是覆盖索引

InnoDB储备引擎中，secondary index（非主键索引）中没有直接储备行地址，储备主键值。假如会员需要查询secondary index中所不包括的数据列时，需要先通过secondary index查寻到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引的概念就是查询可以通过在一个索引中完成，覆盖索引效力会比力高，主键查询是自然的覆盖索引。合理的创立索引乃至合理的使用查询语句，当使用到覆盖索引时可以获得机能晋升。比方SELECT email,uid FROM user_email WHERE uid=xx，假如uid不是主键，恰当时候可以将索引增加为index(uid,email)，以获得机能晋升。

索引的根本标准

• 索引数目操纵，单张表中索引数目不超越5个，单个索引中的字段数不超越5个。
• 综合评估数据密度和分布
• 思考查询和更新比例
  

为什么一张表中不克不及存在过多的索引？

InnoDB的secondary index使用b+tree来储备，因此在UPDATE、DELETE、INSERT的时候需要对b+tree停止调整，过多的索引会减慢更新的速度。

对字符串使用前缀索引，前缀索引长度不超越8个字符，倡议优先思考前缀索引，必要时可增加伪列并创立索引。

不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的储备空间

什么是前缀索引？

前缀索引说白了就是对文本的前几个字符（详细是几个字符在创立索引时指定）创立索引，这样创立起来的索引更小，所以查询更快。前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的害处：MySQL 不克不及在 ORDER BY 或 GROUP BY 中使用前缀索引，也不克不及把它们用作覆盖索引(Covering Index)。

创立前缀索引的语法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

主键原则

• 表必需有主键
• 不使用更新频繁的列
• 尽量不选中字符串列
• 不使用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,…)

合理的使用分页

限制分页展现的页数只能点击上一页、下一页采纳延迟关联

怎样准确的使用分页？

假设有相似下面分页语句：SELECT * FROM table ORDER BY id LIMIT 10000, 10由于MySQL里对LIMIT OFFSET的处置方式是取出OFFSET+LIMIT的所有数据，然后去除OFFSET，返回底部的LIMIT。所以，在OFFSET数值较大时，MySQL的查询机能会非常低。可以使用id > n 的方式停止解决：

使用id > n 的方式有局限性，关于id不持续的问题，可以通过翻页的时候同时传入最后一个id方式来解决。

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

这种方式比力大的缺陷是，假如在阅读中有插入/删除操纵，翻页不会更新，而总页数大概依然是按照新的count(*) 来运算，终究大概会发生某些记载拜访不到。为了修补这个问题，可以连续引入当前页码乃至在上次翻页今后可否有插入/删除等影响总记载数的操纵并停止缓存

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诊断，幸免生成暂时表
  

EXPLAIN语句（在MySQL客户端中施行）可以获得MySQL怎样施行SELECT语句的信息。通过对SELECT语句施行EXPLAIN，可以知晓MySQL施行该SELECT语句时可否使用了索引、全表扫描、暂时表、排序等信息。尽量幸免MySQL停止全表扫描、使用暂时表、排序等。详见官方文档。

用union all而不是union

union all与 union有什么不同？

union和union all关键字都是将两个结果汇合并为一个，但这两者从使用和效力上来说都有所不一样。

union在停止表链接后会挑选掉反复的记载，所以在表链接后会对所发生的结果集停止排序运算，删除反复的记载再返回结果。如：

select * from test_union1 
union select * from test_union2

这个SQL在运转时先取出两个表的结果，再用排序空间停止排序删除反复的记载，最后返回结果集，假如表数据量大的话大概会致使用磁盘停止排序。

而union all只是简便的将两个结果合并后就返回。这样，假如返回的两个结果集中有反复的数据，那么返回的结果集就会包括反复的数据了。

从效力上说，union all要比union快许多，所以，假如可以确定合并的两个结果集中不包括反复的数据的话，那么就使用union all，如下：

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类型字段的程序处置，请验证会员输入，不要超出其预设的长度；
  

分表标准

单表一到两年内数据量超越500w或数据容量超越10G思考分表，需提早思考历史数据迁移或利用自行删除历史数据，采纳等量平衡分表或按照业务规则分表均可。要分表的数据表必需与DBA商量分表战略

• 用HASH停止散表，表名后缀使用十进制数，下标从0开端
• 按日期时间分表需相符YYYY[MM][dd][HH]格局
• 采纳适宜的分库分表战略。例如千库十表、十库百表等
• 制止使用分区表，分区表对分区键有严厉要，分区表在表变大后施行DDL、SHARDING、单表复原等都变得愈加艰难。
• 拆分大字段和拜访频率低的字段，别离冷热数据
  

行动标准

• 大量导入、输出数据必需提早通知DBA协助视察
• 制止在线上从库施行后台治理和统计类查询
• 制止有super权限的利用程序账号存在
• 产品显现非数据库致使的故障时及时通知DBA协助排查
• 推行活动或上线新功效必需提早通知DBA停止流量评估
• 数据库数据丧失，及时联络DBA停止复原
• 对单表的屡次alter操纵必需合并为一次操纵
• 不在MySQL数据库中存置业务逻辑
• 严重项目的数据库方案选型和设计必需提早通知DBA参与
• 对特殊重要的库表，提早与DBA沟通肯定保护和备份优先级
• 不在业务顶峰期大量更新、查询数据库其他标准
• 提交线上建表改表需求，必需具体说明所有相关SQL语句
  

其他标准

日志类数据不倡议储备在MySQL上，优先思考Hbase或OceanBase，如需要储备请寻DBA评估使用紧缩表储备。

相关图文教程：mysql数据库图文教程

以上就是史上最全MySQL使用标准分享的具体内容，更多请关注百分百源码网其它相关文章！