数据库设计、查询规范及常用SQL语句

1.数据库设计规范

1.1 表设计

(1)表名前应加上前缀，表的前缀用系统或模块的英文名称缩写；

(2)数据库表名应该有意义，表名太长需要用前缀表示，并且易于理解，最好使用可以表达功能的英文单词或缩写；

(3)表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字；另外，表名不可以太长，最好不要超过3个英文单词长度（22个字母）；

数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。
因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name

(4)表名不使用复数名词。在数据库表命名时应该用英文单词的单数形式，如员工表命名：应该为Employee而不是Employees ；

说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量；

(5)如果是后台表命名时可以在表名基础上加上后缀_b(backend首字母 )；

(6)在表创建完成前，应该为表及其各字段添加表的注释；

(7)表必须定义主键，默认使用id字段，类型为整型自增，如果不采用默认设计必须咨询DBA进行设计评估；
(8)id字段作为自增主键，禁止在非事务内作为上下文的条件进行数据传递；
(9)在多个表中的相同列，必须保证列定义一致；
(10)国内mysql数据库中表的引擎默认使用InnoDB，表字符集默认使用gbk；国际默认使用utf8字符集的表；其中，Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别；
(11)表必须包含主键id、创建和修改者/时间字段，即表必须包含记录创建时间（如用created_at表示）/创建者(可以使用creator)和修改时间（updated_at）/修改者(可以使用updator/editor)字段；

表必备三字段：id, created_at, updated_at。
说明：其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。
created_at, updated_at的类型均为date_time类型。

(12)表示是否概念的字段，必须使用is_xxx的方式命名，数据类型使用unsigned tinyint（1表示是，0表示否）
说明：任何字段如果为非负数，必须使用unsigned。正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。

(13)禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。

(14)主键索引名使用pk_前缀表示的字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。
说明：pk_ 即primary key；uk_ 即unique key；idx_ 即index的简称。

(15)小数类型为decimal，禁止使用float和double。
说明：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。
如果存储的数据范围超过decimal的范围，建议将数据拆成整数和小数分开存储。

(16)如果存储的字符串长度几乎相等，使用char定长字符串类型；varchar是可变长字符串，长度最好不要超过5000。
说明：varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

(17)如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
(18)字段允许适当冗余，以提高查询性能，但必须考虑数据一致。
冗余字段应遵循：

• 不是频繁修改的字段。
• 不是varchar超长字段，更不能是text字段。
  正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

(19)合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。
正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。
对象年龄区间类型字节表示范围
人150岁之内unsigned tinyint(1)无符号值：0到255
龟数百岁unsigned smallint(2)无符号值：0到65535
恐龙化石数千万年unsigned int(4)无符号值：0到约42.9亿
太阳约50亿年unsigned bigint(8)无符号值：0到约10的19次方

(20)单表数据量超过500w或数据容量超过10G考虑分库分表，且需要提前考虑历史数据迁移或使用脚本自行删除历史数据

说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
(21)单条记录大小禁止超过8k（列长度(中文)*2（gbk）/3(utf8)+列长度(英文)*1）
(22)日志类数据不建议存储在MySQL上，优先考虑Hbase

1.2 字段设计

1. 表被索引列必须定义为not null，并设置default值
   

2. 禁止使用float、double类型，建议使用decimal或者int替代
   

3. 禁止使用blob、text类型保留大文本、文件、图片，建议使用其他方式存储（TFS/SFS），MySQL只保存指针信息
   

4. 禁止使用varchar类型作为主键语句设计

1.3 语句设计

1)    数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
2)    禁止使用非同类型的列进行等值查询！

1.4 其他

1. 禁止使用：存储过程、触发器、函数、视图、事件等MySQL高级功能
   

2. 禁止使用跨库查询
   

3. 禁止使用子查询，建议将子查询转换成关联查询
   

4. 禁止核心业务流程SQL包含：计算操作、多表关联、表遍历case when等复杂查询，建议拆分成单表简单查询
   

5. varchar长度设计需要根据业务实际需要进行长度控制，禁止预留过长空间。例如status使用varchar(128)进行存储

1.5 索引

1.5.1 索引原理

现在互联网应用中对数据库的使用多数都是读较多，比例可以达到 10:1。并且数据库在做查询时 IO 消耗较大，所以如果能把一次查询的 IO 次数控制在常量级那对数据库的性能提升将是非常明显的，因此基于 B+ Tree 的索引结构出现了。

B+ Tree 的数据结构

如图所示是 B+ Tree 的数据结构，是由一个一个的磁盘块组成的树形结构，每个磁盘块由数据项和指针组成。所有的数据都是存放在叶子节点，非叶子节点不存放数据。

查找过程：

以磁盘块1为例，指针 P1 表示小于17的磁盘块，P2 表示在 17~35 之间的磁盘块，P3 则表示大于35的磁盘块。

比如要查找数据项99，首先将磁盘块1 load 到内存中，发生 1 次 IO。接着通过二分查找发现 99 大于 35，所以找到了 P3 指针。通过P3 指针发生第二次 IO 将磁盘块4加载到内存。再通过二分查找发现大于87，通过 P3 指针发生了第三次 IO 将磁盘块11 加载到内存。最后再通过一次二分查找找到了数据项99。

由此可见，如果一个几百万的数据查询只需要进行三次 IO 即可找到数据，那么整个效率将是非常高的。

观察树的结构，发现查询需要经历几次 IO 是由树的高度来决定的，而树的高度又由磁盘块，数据项的大小决定的。磁盘块越大，数据项越少那么树的高度就越低。这也就是为什么索引字段要尽可能小的原因。

1.5.2 几个关键字的区别

key、primary key、unique key与index的区别：https://www.cnblogs.com/zjfjava/p/6922494.html

Mysql中key 、primary key 、unique key 与index区别：
https://www.cnblogs.com/zjfjava/p/6922494.html

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并读完整个表，直到它找出相关的行。

表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。

如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。

所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。

1.5.3 索引使用场景

快速找出匹配一个WHERE子句的行；

当执行联结时，从其他表检索行；

对特定的索引列找出MAX()或MIN()值；

如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。

如果所有键值部分跟随DESC，键以倒序被读取。

在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。

如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。

2. 数据库查询

2.1 更新表中的数据和结构

2.1.1 UPDATE更新表中的数据

UPDATE customers

SET cust_name=‘The Fudds’ ,

cust_email=‘elmer@fudd.com’

WHERE cust_id=10005;

2.1.2 ALTER TABLE——更改表的列结构（添加约束、索引等）

ALTER TABLE vendors

ADD vend_phone CHAR(20);

通过上面的更新表的语句，可看出，使用UPDATE语句是更新表的内容，UPDATE +表名 SET +要修改某些列的内容 +过滤条件；

使用ALTER TABLE是更新表的结构，ALTER TABLE +表名ADD +要添加的列数据类型/DROP COLUMN +要删除的列名;

ALTER TABLE的一种常见用途是定义外键，如：

ALTER TABLE orderitems

ADD CONSTRAINT fk_orderitems_orders

FOREIGN KEY (order_num) REFERENCES orders (order_num);

2.2 删除表中的数据与结构

2.2.1 DELETE FROM——删除表中数据内容

DELETE FROM customers

WHERE cust_id = 10006;

DELETE FROM要求制定从中删除数据的表明，WHERE子句过滤要删除的行，如果省略WHERE子句，就会删除表中的所有数据。注意，DELETE不需要列名或通配符，DELETE删除的是整行而不是删除列。为了删除指定的列，可以使用UPDATE语句。

2.2.2 DROP TABLE——删除整个表结构（包括内容）

DROP TABLE customers2;

2.2.3 更快速地删除表

如果想更快地从表中删除所有的行，可以使用TRUNCATE TABLE语句，它与DELETE语句完成同样的工作，但是速度更快，因为TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。

RENAME TABLE——重命名表

RENAME TABLE backup_customers TO customers,

backup_vendors TO vendors,

backup_products TO products;

通过RENAME TABLE语句可以重命名一张表，重命名多张表时中间使用逗号隔开，如上所示。

2.3 视图VIEW的使用

视图是虚拟的表，它们包含的不是数据而是根据需要检索的查询。

2.3.1 创建视图——CREATE VIEW viewname

查看创建的视图——SHOW CREATE VIEW viewname;

2.3.2 删除视图——DROP VIEW viewname;

更新视图——方式一：先疏通DROP删除视图，在使用CREATE语句创建新的视图；

方式二：直接使用CREATE OR REPLACE VIEW更新。如果要更新的视图不存在，则该更新语句会创建一个视图；如果要更新的视图存在，怎会替换原有视图。

2.3.3 使用视图简化复杂的联结

视图的最常见应用之一是隐藏复杂的SQL，这通常都会涉及联结。从下面的实例可以看出，视图极大地简化了复杂SQL语句的使用，并且可以一次性编写基础的SQL，然后根据需要多次使用。这样扩展视图的适用范围，不仅使得其能被重用，甚至更加有用。

CREATE VIEW productcustomers AS

SELECT cust_name, cust_contact, prod_id

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

此语句创建了一个名为productcustomers的视图，它联结三个表，返回cust_name, cust_contact, prod_id客户列表信息组成的视图。然后，就可以通过如下的SELECT语句进行检索：

SELECT cust_name, cust_contact

FROM productcustomers

WHERE prod_id = ‘TNT2’’;

2.3.4 使用视图简化字段

视图对于简化计算字段的使用非常有用，并且视图非常容易创建，可以重复使用，正确使用视图可极大地简化复杂的数据处理。

CREATE VIEW orderitemsexpanded AS

SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price

FROM orderitems;

先通过上面的语句创建一个视图，然后在视图中检索需要的字段，可简化复杂数据的处理过程。

2.3.5 视图能否更新

通常，视图是可更新的，即可以对它们使用INSERT、UPDATE和DELETE。更新一个视图将更新其基表，因为视图本身没有数据。如果对视图进行删除、增加行，实际上是对其基表删除或增加行。

但是，并非所有的视图都是可更新的。如果MySQL不能正确地确定被更新的基数据，，则不允许更新（包括插入和删除）。这意味着，如果视图定义中有一下操作，则不能进行视图的更新：

分组（使用GROUP BY和HAVING）；联结；子查询；并；聚集函数（Min()、Count()、Sum()等）；DISTINCT；导出（计算）列。

2.4 游标

有时候，需要在检索出来的行中前进或者后退一行或者多行，这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，用户需要滚动屏幕上的数据，并对数据进行浏览或者做出更改。不像DBMS，MySQL游标只能用于存储过程（和函数）。

2.5 触发器

使用条件：在通过某条语句（或者某些语句）对某个表进行修改（插入、删除、更新）时，自动进行处理，这就需要使用触发器。只有表才支持触发器，视图不支持（临时表也不支持）。

触发器是MySQL响应DELETE、INSERT、UPDATE语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句），其他MySQL语句不支持触发器。

2.5.1 创建触发器

创建触发器时需要给出以下四条信息：

唯一的触发器名字（触发器名必须在每个表中唯一，但在 MySQL5中，同一个数据库中的两个表可以具有相同的触发器名，这在其他DBMS中一般是不允许的，顾触发器名最好在数据库范围内唯一）；
触发器关联的表名称；
触发器应该响应的操作（DELETE、INSERT、UPDATE）；
触发器执行的时机（是在操作之前BEFORE还是之后AFTER）。
CREATE TRIGGER newproduct AFTER INSERT ON products

FOR EACH ROW SELECT ‘Product added’ ;

CREATE TRIGGER用来创建名为newproduct的新触发器，AFTER INSERT ON products表示此触发器是在INSERT插入表products成功之后执行。这个触发器还执行FOR EACH ROW ，表示代码对每个插入行执行，后面的SELECT 'Product added’表示文本Product added将对每个插入的行显示返回一次结果。

每个表每个事件每次只允许一个触发器，因此，每个表最多支持6个触发器（每条DELETE、INSERT、UPDATE之前或之后）。每个触发器不能与多个事件或多个表关联，所以，如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

2.5.2 删除触发器

删除触发器使用DROP TRIGGER语句，例如将上面创建的newproduct触发器删除：

DROP TRIGGER newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后在重新创建。

2.5.3 INSERT触发器

INSERT触发器在INSERT语句执行之前或者之后执行。需要注意以下几点：

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
对于AUTO_INCREMENT列，NEW在INSERT执行之前包括0，在INSERT执行之后包含新的自动生成的值，其中AUTO_INCREMENT列具有MySQL自动赋予的值。
CREATE TRIGGER neworder AFTER INSERT ON orders

FOR EACH ROW SELECT NEW.order_num;

在成功执行下面的插入操作之后，将会出发上面的触发器，并返回新的叮当好order_num

INSER INTO orders(order_date, cust_id)

VALUSE(Now(), 10001);

orders包含3个列，order_date和cust_id必须给出，order_num有MySQL自动生成，而现在order_num会自动被返回。

2.5.4 DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要注意以下几点：

在DELETE触发器代码内，可以应用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD（将要被删除的订单）中的值保存到一个名字为archive)orders的存档表中（在实际使用上面实例之前，需要用与orders相同的列创建一个名为archive_orders的表）。

使用BEFORE DELETE触发器的优点：相对于AFTER DELETE触发器来说，如果因为某种原因，订单不能存档，DELETE本身将被放弃（无效）。使用BEGIN END块的好处是触发器能容纳一条或者多条SQL语句（在BEGIN END块中一条挨着一条）。

2.5.5 UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或者之后执行。需要注意以下几点：

在UPDATE触发器中，可以应用一个名为OLD的虚拟表访问UPDATE语句之前的值，引用一个名为NEW的虚拟表访问新更新的值；
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（即允许更改将要用于UPDATE语句中的值）；
OLD中的值全都是只读的，不能更新。
CTEATE TRIGGER updatevendor BEFORER UPDATE ON vendors

FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

该语句用来每次更新一行，NEW.vend_state中的值都用大写的vend_state字符串替换。

2.6 注意事项

创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的。
应该使用触发器来保证数据的一致性，包括大小写、数据格式等。
触发器的一种很重要的应用是穿件审计跟踪，及使用触发器把数据更改前后的状态都备份到另一个表中，以备后续恢复使用。
MySQL触发器中不支持CALL语句，即表示不能从触发器内调用存储过程。

3. SQL 优化

(1)反向查询不能使用索引

select name from user where id not in (1,3,4);
应该修改为:

select name from user where id in (2,5,6);
(2)前导模糊查询不能使用索引

如：select name from user where name like ‘%zhangsan’

非前导则可以：select name from user where name like ‘zhangsan%’

建议可以考虑使用 Lucene 等全文索引工具来代替频繁的模糊查询。

(3)数据区分不明显的不建议创建索引如：user 表中的性别字段；可以明显区分的才建议创建索引，如身份证等字段。

(4)字段的默认值不要为 null，这样可能会带来和预期不一致的查询结果。

(5)使用limit可以提高效率。如果明确知道只有一条记录返回，使用如下语句

select name from user where username=‘zhangsan’ limit 1
可以提高效率，让数据库停止游标移动。

(6)在进行 join 连接时，要连接的两个表的字段的类型要相同，不然也不会命中索引。

(7)不要让数据库帮我们做强制类型转换

select name from user where telno=18722222222
这样虽然可以查出数据，但是会导致全表扫描。

需要修改为:

select name from user where telno=‘18722222222’
(8)在字段上进行计算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE();
应该修改为:

select name from user where create_time < FROM_UNIXTIME(CURDATE());

4 参考

阿里巴巴MYSQL数据库设计，查询规范:

https://blog.csdn.net/qq_28296925/article/details/80455317

阿里的Mysql规范 MySQL库表设计规范:

https://blog.csdn.net/u012966918/article/details/52161519

数据库表及字段命名规范:

https://blog.csdn.net/yuzhouxiang/article/details/7088352