您的位置:主页 > 行业新闻 >
行业新闻

值得收集:一个非常完整的MySQL规范

2019-01-16

原标题:值得收藏:一个非常完整的MySQL规范

一、数据库命令规范

所有数据库对象名称必须为小写字母,并以下划线分隔 禁止所有数据库对象名使用mysql保留关键字(如果表名包含关键字查询,则需要将其括在单引号中) 应该能够知道数据库对象的名称,并且最后不超过32个字符。 临时库表必须以tmp_为前缀,并以日期为后缀。备份表必须以bak_为前缀,并以日期(时间戳)为后缀 存储相同数据的所有列名和列类型必须相同(通常作为关联列。如果关联列类型在查询期间不一致,则将隐式转换数据类型,这将使​​列上的索引无效,导致查询效率降低)

二、数据库基本设计规范

1、所有表必须使用Innodb存储引擎

在没有特殊要求的情况下(例如Innodb无法满足诸如:列存储,存储空间数据等功能),所有表必须使用Innodb存储引擎(mysis5.5之前默认使用myisam,Innodb by默认情况下5.6)Innodb支持事务,支持行级锁定,更好的恢复,更高的并发性能

2、数据库和表的字符集统一使用UTF8

更好的兼容性,统一的字符集可以避免字符集转换引起的乱码,在比较之前需要转换不同的字符集会导致索引失效

3、所有表和字段都需要添加注释

使用comment子句为表和列添加注释从头开始维护数据字典

4、尽量控制单表数据量的大小,建议控制在500万以内

500万不是MySQL数据库的限制。如果会议结构被修改,备份和恢复将有很大的问题。

您可以使用历史数据归档(应用于日志数据),子库表(应用于业务数据)等来控制数据量

5、谨慎使用MySQL分区表

分区表在物理上表示为多个文件,逻辑上表示为表。仔细选择分区键,跨分区查询效率可能会更低。建议通过物理分区来管理大数据。

6、尽量做到冷热数据分离,减小表的宽度

MySQL限制每个表存储多达4096列,并且每行数据的大小不能超过65535字节以减少磁盘IO,确保热数据的缓存命中率(表格越宽,将表格加载到的表中使用的内存)内存缓冲池)越大,消耗的IO就越多。更有效地使用缓存,避免使用冷数据,通常在表中一起使用(以避免更多相关操作)

7、禁止在表中建立预留字段

保留字段的命名很难命名。保留字段无法确认存储的数据类型,因此无法选择适当的类型。修改保留字段类型将锁定表。

8、禁止在数据库中存储图片,文件等大的二进制数据

通常,文件大小很大,数据量会在短时间内迅速增加。从数据库中读取数据库时,通常会执行大量随机IO操作。当文件很大时,IO操作通常很耗时并且通常存储在文件服务器中,并且仅存储数据库。文件地址信息

9、禁止在线上做数据库压力测试

10、禁止从开发环境,测试环境直接连接生成环境数据库

三、数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型

原因

列的字段越大,索引所需的空间越大,因此可以存储在页面中的索引节点的数量越来越少,遍历所需的IO时间也越来越多。 ,指数的表现更差

方法

1)将字符串转换为数字类型存储,例如:将IP地址转换为整数数据。

Mysql提供了两种处理ip地址的方法:

Inet_aton将ip转换为无符号整数(4-8位)

Inet_ntoa将整数ip转换为地址

在插入数据之前,使用inet_aton将ip地址转换为整数,这样可以节省空间。显示数据时,使用inet_ntoa将整数ip地址转换为地址显示。

2)对于非负数据(如自动增量ID,整数IP),最好使用无符号整数进行存储

因为:与签名

相比,unsigned可以使存储空间翻倍

SIGNED INT -2147483648~2147483647

UNSIGNED INT 0~4294967295

VAR中的N(N)表示字符数,而不是字节数

使用UTF8存储255个中文字符Varchar(255)=765个字节。长度过长会消耗更多内存

2、避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据

建议将BLOB或TEXT列分隔为单独的扩展表。

Mysql内存临时表不支持TEXT和BLOB等大数据类型。如果查询包含此类数据,则在排序和其他操作期间不能使用内存临时表。您必须使用磁盘临时表。

而对于这种数据,Mysql仍然需要执行第二次查询,这将使sql性能非常差,但这并不意味着不能使用这样的数据类型。

如果必须使用它,建议将BLOB或TEXT列分隔为单独的扩展表。查询时不能使用select *,只需要取出必要的列。当您不需要TEXT列的数据时,请不要查询该列。

TEXT或BLOB类型只能使用前缀索引

由于MySQL对索引字段的长度有限制,因此TEXT类型只能使用前缀索引,而TEXT列不能具有默认值。

3、避免使用ENUM类型

修改ENUM值以使用ALTER语句 ENUM类型ORDER BY操作效率低,需要额外的操作 不要将数值用作ENUM的枚举值

4、尽可能把所有列定义为NOT NULL

原因是:

索引NULL列需要额外的空间来保存,因此占用更多空间。 对NULL值进行特殊处理以进行比较和计算

5、使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

TIMESTAMP存储时间范围1970-01-01 00: 00: 01~2038-01-19-03: 14: 07.

TIMESTAMP占用4个字节,INT相同,但比

更易读

使用超出TIMESTAMP值范围的DATETIME类型存储。

通常人们会使用字符串来存储日期类型的数据(不正确的做法):

缺点1:无法计算并与日期函数进行比较 缺点2:使用字符串存储日期会占用更多空间

6、同财务相关的金额类数据必须使用decimal类型

非精度浮点:浮点数,双精度 精确浮点:十进制

十进制类型是精确浮点数,在计算时不会丢失精度。占用的空间由定义的宽度决定。每4个字节可以存储9个数字,小数点占用一个字节。可用于存储比bigint更大的整数数据。

四、索引设计规范

1、限制每张表上的索引数量,建议单张表索引不超过5个

索引不是尽可能多!索引可以提高效率并降低效率。

索引可以提高查询效率,但也会降低插入和更新的效率,并且在某些情况下会降低查询效率。

由于mysql优化器选择如何优化查询,因此它将根据统一信息评估可以使用的每个索引,以生成最佳执行计划。如果同时有多个索引,则可以用于查询。它将增加mysql优化器生成执行计划的时间,这也会降低查询性能。

2、禁止给表中的每一列都建立单独的索引

在5.6版之前,sql只能在表中使用一个索引。在5.6之后,虽然有一种合并索引的优化方法,但它仍远未使用联合索引。

3、每个Innodb表必须有个主键

Innodb是一个索引组织的表:数据存储的逻辑顺序和索引的顺序是相同的。

每个表可以有多个索引,但只能按顺序存储其中一个表。 Innodb按主键索引的顺序组织表。

不要使用经常更新的列作为主键,而不是多列主键(相当于联合索引)不要使用UUID,MD5,HASH或字符串列作为主键(无法保证数据的顺序增长)。

主键建议使用自动增量ID值。

五、常见索引列建议

SELECT,UPDATE和DELETE语句的WHERE子句中的列 ORDER BY,GROUP BY,DISTINCT中包含的字段 不要为与1和2中的字段匹配的列创建索引。通常最好为1,2中的字段创建联合索引。 多表连接的关联列

六、如何选择索引列的顺序

索引的目的是希望通过索引搜索数据,减少随机IO,并提高查询性能。索引可以过滤的数据越少,从磁盘读取的数据就越少。

最高程度的区分位于联合指数的最左侧(区分=列中不同值的数量/列中的总行数); 尝试将具有小字段长度的列放在联合索引的最左侧(因为字段长度越小,可以在一个页面上存储的数据量越大,IO性能越好); 使用联合索引左侧最常见的列(这样可以构建更少的索引)。

七、避免建立冗余索引和重复索引

这将增加查询优化器生成执行计划的时间。

重复索引示例:主键(id),索引(id),唯一索引(id) 冗余索引示例:索引(a,b,c),索引(a,b),索引(a)

八、优先考虑覆盖索引

使用叠加索引进行频繁查询。

覆盖索引:是一个包含所有查询字段的索引(where,select,ordery by,group by)

叠加索引的好处:

避免对Innodb表的索引进行二次查询

Innodb按聚集索引的顺序存储。对于Innodb,辅助索引保存叶节点中行的主键信息

如果使用辅助索引查询数据,则在找到相应的键值后,需要通过主键执行辅助查询以获取所需的数据。在覆盖索引中,可以在二级索引的密钥值中获取所有数据,避免了主键的二次查询,减少了IO操作,提高了查询效率。

可以将随机IO转换为顺序IO,以加快查询效率

由于叠加索引按键值的顺序存储,因此对于IO密集范围查找,每行的数据IO与从磁盘中随机读取相比相对较小,因此磁盘的随机索引也可以是访问叠加索引时使用。读取IO将转换为索引查找的顺序IO。

九、索引SET规范

尽量避免使用外键约束

建议不要使用外键约束(外键),但必须在表和表之间的关联键上建立索引; 外键可用于保证数据的参照完整性,但建议在业务方面实施; 外键可能会影响父表和子表的写操作,从而降低性能。

十、数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重用这些计划,减少SQL编译所需的时间,并解决由动态SQL引起的SQL注入问题。只传递参数,比传递SQL语句更有效。同一语句可以解析一次并多次使用。提高处理效率。

2、避免数据类型的隐式转换

隐式转换可能导致索引失效。如:选择姓名,来自客户的电话,其中id='111';

3、充分利用表上已经存在的索引

避免使用双%查询条件。

比如'%123%',(如果没有pre-%,只有post-%,你可以使用列上的索引)

SQL只能使用复合索引中的列进行范围查询

例如,如果存在列a,b和c的联合索引,如果在查询条件中存在具有列的范围查询,则将不使用列b和c上的索引。定义关节索引时,如果要使用范围查找,请将列a放在关节索引的右侧。

使用左连接或不存在来优化不在操作中

因为不在通常也使用索引失效。

4、数据库设计时,应该要对以后扩展进行考虑

5、程序连接不同的数据库使用不同的账号,进制跨库查询

为数据库迁移和细分留出空间 减少业务耦合 避免过多权限导致的安全风险

6、禁止使用SELECT * 必须使用SELECT 字段列表 查询

原因是:

使用网络带宽资源消耗更多CPU和IO 无法使用叠加索引 减少表结构变化的影响

7、禁止使用不含字段列表的INSERT语句

如:插入值('a','b','c');

插入t(c1,c2,c3)值('a','b','c')应该使用;

8、避免使用子查询,可以把子查询优化为join操作

通常子查询位于in子句中,子查询是简单的SQL(不包括union,group by,order by,limit子句),然后子查询可以转换为关联查询以进行优化。

子查询性能差的原因:

子查询的结果集不能使用索引。通常,子查询的结果集存储在临时表中。内存临时表或磁盘临时表中没有索引,因此查询性能会受到一定程度的影响; 特别是对于返回大结果集的子查询,它对查询性能的影响更大; 由于子查询生成大量临时表而没有索引,因此它们会占用过多的CPU和IO资源并生成大量慢速查询。

9、避免使用JOIN关联太多的表

对于Mysql,有一个关联的缓存,缓存的大小可以通过join_buffer_size参数设置。

在Mysql中,如果共享同一SQL的表,则将分配关联的缓存。如果SQL中关联的表更多,则使用的内存将更大。

如果程序中的大量操作使用多个表关联,并且join_buffer_size设置不合理,则很容易导致服务器内存溢出,这将影响服务器数据库性能的稳定性。

同时,对于关联操作,将生成临时表操作,这会影响查询效率。 Mysql允许最多关联61个表,建议不超过5个。

10、减少同数据库的交互次数

该数据库更适合处理批处理操作。将多个相同的操作组合在一起可以提高处理效率

11、对应同一列进行or判断时,使用in代替or

在操作中,in的值不应超过500,以便更有效地使用索引,或者在大多数情况下很少使用索引。

12、禁止使用order by rand() 进行随机排序

表中的所有符合条件的数据将被加载到存储器中,然后将根据存储器中随机生成的值对所有数据进行排序,并且可以为每一行生成随机值。如果满足条件的数据集非常大,则会消耗大量的CPU和IO以及内存资源。

建议在程序中获取随机值,然后从数据库中获取数据

13、WHERE从句中禁止对列进行函数转换和计算

在列上执行函数转换或计算时无法建立索引。

不建议:

date(create_time)='20190101'

推荐的:

其中create_time='20190101'和create_time'20190102'

14、在明显不会有重复值时使用UNION ALL而不是UNION

在执行重复数据删除操作之前,UNION会将两个结果集的所有数据放入临时表中。 UNION ALL不再对结果集执行重复数据删除

15、拆分复杂的大SQL为多个小SQL

Big SQL:逻辑上很复杂,需要大量的CPU来计算SQLMySQL:只能使用一个CPU计算SQL SQL拆分可以通过并行执行提高处理效率

十一、数据库操作行为规范

1、超100万行的批量写(UPDATE、DELETE、INSERT)操作,要分批多次进行操作

批量操作可能导致严重的主从延迟

在主从环境中,大规模操作可能导致严重的主从延迟。大规模写操作通常需要一定的时间来执行,并且只有在完成主库的执行后才会在其他从库上执行,因此会导致主库和从库之间的长时间延迟

当binlog日志采用行格式时,将生成大量日志。

批量写入将生成大量日志,尤其是行格式二进制数据,因为行格式将记录每行数据的修改,我们一次修改的数据越多,生成的日志就越多。日志传输和恢复所需的时间越长,这是主从延迟的一个原因。

避免大型交易操作

大量修改数据必须在事务中完成,这会导致表中的大量数据被锁定,导致大量阻塞,阻塞将对MySQL的性能产生非常大的影响。

特别是,长期阻塞可能会填满所有数据库的可用连接,这可能使生产环境中的其他应用程序无法连接到数据库,因此请务必注意批量写入操作。

2、对于大表使用pt-online-schema-change修改表结构

避免大表修改导致的主从延迟 修改表字段时避免锁定表

大表数据结构的修改必须谨慎,这将导致严重的锁表操作,特别是在生产环境中,这是不能容忍的。

Pt-online-schema-change将首先创建一个与原始表具有相同结构的新表,并修改新表上的表结构,然后将原始表中的数据复制到新表中,并在原始表中table添加一些触发器。

将新添加的数据从原始表复制到新表。复制完所有数据后,新表将被命名为原始表,并删除原始表。

将原始DDL操作分解为多个小批量。

3、禁止为程序使用的账号赋予super权限

达到最大连接限制时,超级特权用户连接超级权限只能用于DBA来处理问题。

4、对于程序连接数据库账号,遵循权限最小原则

使用数据库帐户的程序只能在一个DB下使用。跨数据库程序使用的帐户原则上不允许具有删除权限。

上一篇:热门云数据排名“年度十大大数据企业”名单
下一篇:没有了

值得收集:一个非常完整的MySQL规范 相关的内容: