如何更规范化使用 MySQL（上）

Feb 15 2022

阿里云代理

分类：linux图文教程阅读次数

已被围观 655 次

一、MySQL数据库命名规范

1、数据库一切表前缀均运用项目名称首字母缩写；

2、数据库一切目标名称均运用小写字母，并且单词之间经过下划线分开；

3、数据库一切目标名称制止运用MySQL保留字及关键字，涉及到关键字的SQL查询需求将关键字用单引号括起来；

4、数据库一切目标名称不超过32个字符，并且命名要遵从见名知意原则；

5、数据库临时表有必要以 pro_tmp_ 为前缀并且以日期 20190917 为后缀，备份表有必要以 pro_bac 为前缀并以时刻戳为后缀；(pro为项目名称首字母缩写)

6、数据库一切存储相同数据的列名和列类型有必要保持一致。

二、MySQL数据库根本规划规范

1、若无特殊说明，建表时一律选用Innodb存储引擎。

挑选合适的引擎能够提高数据库功能，如InnoDB和MyISAM，InnoDB和MyISAM是许多人在运用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体运用而定；

根本的差别为：MyISAM类型不支撑业务处理等高档处理，而InnoDB类型支撑；MyISAM类型的表着重的是功能，其履行数度比InnoDB类型更快，可是不提供业务支撑，而InnoDB提供业务支撑以及外部键等高档数据库功用；

因此，其支撑业务处理、支撑外键、支撑溃散修正能力和并发操控是我们建表时首选的存储引擎。

2、数据库和表的字符集一致运用UTF8

数据库和表的字符集一致运用utf8，若是有字段需求存储emoji表情之类的，则将表或字段设置成utf8mb4；由于，utf8号称万国码，其无需转码、无乱码危险且节省空间，而utf8mb4又向下兼容utf8。

3、规划数据库时一切表和字段有必要添加注释

运用Comment从句添加表和列的补白，或直接在数据库衔接东西的注释栏添加注释，从项目开始就进行数据字典的维护。

运用Comment从句添加注释如：

-- 1、创建表： CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number); -- 2、添加表注释： Comment on table t1 is '个人信息'; -- 3、添加字段注释： comment on column t1.id is 'id'; comment on column t1.nameis '名字'; comment on column t1.age is '年龄';

运用数据库衔接东西添加注释：

4、单个表的数据量巨细操控在500万以内

尽量操控单表数据量的巨细，主张操控在500万以内；500万并不是MySQL数据库的极限，但数据量太多不利于对表结构进行修正、备份和康复数据，恰当选用分库分表等手段来操控单表数据量的巨细。

5、运用MySQL分区表需慎重

分区是将一个表的数据依照某种方式，比方依照时刻上的月份，分成多个较小的，更容易办理的部分，可是逻辑上仍是一个表；分区表在物理上表现为多个文件，在逻辑上仍表现为同一个表，需求慎重挑选分区键；跨分区查询功率或许会更低，主张运用物理分区表等方式办理大数据。

6、尽量满足冷热数据别离，减小表等宽度

MySQL限制每个表最多存储4096列，并且每一行数据的巨细不超过65535字节，为了减少磁盘IO线程的开支，就要恰当操控表的宽度，由于表越宽，把表装载进内存缓冲池时所占用的内存也就越大，就会耗费更多的IO线程；除此之外，为了保证热数据的内存缓存命中率，更有效的利用缓存，防止读入无用的冷数据，尽量把经常运用到的列放到同一个表中，防止不必要的相关操作。

7、树立预留字段需慎重

部分友人在规划数据库表时，不只规划了当前所需求的字段，并且还在其间留出几个字段作为备用。比方说，我规划了一个人员表（Person），其间已经添加了各种必要的字段，包括名字（Name）、性别（Sex）、出生年月日（birthday）等等；

为了以防万一，比方之后或许Person 表会涉及到结业院校、工作单位、是否婚配和相片等信息，于是就加入5个varchar2 型的字段，别离叫做Text1、Text2……Text5；这一手操作看似防备于未然，其实也并不见得，由于大量预留字段会浪费空间、预留字段不能做到见名知意、预留字段无法确认存储的数据类型且修正其字段类型还或许会形成锁表等问题。

针对此等状况能够参阅以下两点解决方案：

假如数量很少，并且信息的性质与原表密切相关，那么就能够直接在原表上添加字段，并将相关的数据更新进去；
假如数量较大，或许并非是原表目标至关重要的属性，那么就能够新增一个表，然后经过键值衔接起来；

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

若往数据库表中存储文件，而文件一般很大，当数据库进行读取操作时，会进行大量的随机IO操作，大文件使得IO操作很耗时耗功能，形成短时刻内数据量快速增长；所以，一般将图片、文件存储在文件服务器中，数据库只用于存储文件地址信息。

三、MySQL数据库字段规划规范

1、优先挑选符合存储需求的最小的数据类型。

主要是考虑索引的功能，由于列的字段越大，树立索引时所需求的空间也越大，这样一页中能存储的索引节点的数量也就越少，在遍历时需求的IO次数也就越多，索引的功能也就越差。

2、防止运用TEXT、BLOB数据类型

防止运用TEXT和BLOB数据类型，其间最常见的TEXT类型能够存储64K数据，MySQL内存临时表不支撑TEXT、BLOB这样的大数据类型，若查询中包括这样的数据，在履行排序等操作时就不能运用内存临时表，有必要运用磁盘临时表履行操作；

TEXT和BLOB类型只能运用前缀索引(当索引是很长的字符序列时，这个索引将会很占内存，并且会很慢，这时候就会用到前缀索引了；所谓的前缀索引就是去索引的前面几个字母作为索引，可是要下降索引的重复率，所以我们还有必要要判断前缀索引的重复率；)，由于MySQL对索引字段长度是有限的，所以TEXT类型只能运用前缀索引，并且TEXT列上是不能有默认值的；

若需求运用，主张把BLOB或TEXT列别离到单独的的扩展表中，且查询时必定不要运用select *，只需取出必要的列即可。