SQL数据库基础

绪论

1.1 数据、信息与数据处理

(1) 数据(Data)：是数据库中存储的基本对象；

定义：描述事物的符号记录，是信息的符号表示，或称载体；
种类：数字、文本、图形、图像、声音、视频，学生的档案记录(40951001, 王二小，男，1988，内蒙，tx1201)等。

(2) 信息：数据的内涵，是数据的语义解释。

(3)数据处理：将数据转换成信息的过程;数据管理：是数据处理的核心。

1.2 数据管理技术的产生与发展

人工管理阶段（20世纪50年代中期前）

数据不保存
应用程序管理数据
数据不具有独立性和共享性

文件系统阶段（20世纪50年代后期到60年代中期）

数据以文件形式可以长期保存在外存储设备上
由文件系统管理数据（ “按名访问，按记录存取 ” ）
数据共享性差，冗余度大（面向应用）
数据独立性差

数据库系统阶段（20世纪60年代后期至今）

数据结构化, 并与程序分离
数据的共享性高，冗余度低，易扩充（集中存储）
数据独立性高
数据由数据库管理系统统一控制
数据库系统阶段——数据独立性高
- 物理独立性
  用户的应用程序与数据库中数据相互独立。当数据的存储格式和组织方法改变时，不影响数据库的逻辑结构，从而不影响应用程序。
逻辑独立性
指用户的应用程序与数据库的逻辑结构是相互独立的。数据的逻辑结构改变了，用户程序不用改变。
数据独立性由DBMS的二级映像功能来保证。
数据库系统阶段——DBMS提供数据控制功能
(1) 数据的安全性（（Security））控制
保护数据，以防止不合法的使用造成的数据的泄密和破坏。
(2) 数据的完整性（（Integrity））控制
将数据控制在有效范围内，或保证数据间满足一定的关系。
(3) 并发（（Concurrency））控制
对多用户的并发操作加以控制和协调，防止相互干扰。
(4) 数据恢复（（Recovery））
将数据库从错误状态恢复到某一已知的正确状态。
数据库系统的发展
初级阶段——第一代数据库
出现了层次模型、网状模型的数据库
中级阶段——第二代数据库
关系型数据库和结构化查询语言
高级阶段——新一代数据库
“面向对象”型数据库
1.3 数据库系统的组成
数据库系统（Database System，DBS）包括以下5部分:
数据库
数据库管理系统
硬件系统
软件系统
人员(管理员、分析员、设计员、程序员和用户)

数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据的集合。
数据库的基本特征

数据按一定的数据模型组织、描述和储存
可为各种用户共享
冗余度较小
数据独立性较高
易扩展

数据库管理系统（Database ManagementSystem ，简称DBMS）

管理数据库的系统软件，是数据库系统的核心。
位于用户与操作系统之间的一层数据管理软件。

DBMS的主要功能

数据定义功能（DDL）
数据操纵功能（DML）
数据库的事务管理和运行管理
数据库的建立和维护功能
数据通信

硬件系统
硬件系统要有足够的内存用来存放操作系统、DBMS的核心模块、数据缓存、应用程序及数据备份等。
软件系统
包括DBMS、支持DBMS运行的操作系统和具有数据访问接口的高级语言及其编程环境。
1.4数据模型

根据应用目的，模型分为两个层次：

概念模型( 信息模型)

按用户的观点，独立于计算机实现的，只用来描述和组织所关心的信息结构的概念数据模型，强调语义
数据库设计人员和用户之间进行交流的语言
对应于信息世界

数据模型

按计算机系统的观点，直接面向计算机系统的，描述数据库中数据的逻辑结构的基本数据模型
对应于数据世界（机器世界）
包括逻辑模型和物理模型
概念模型（信息世界中）的基本概念
实体Entity ：客观存在并相互区别的事物。具体的人、事、物，抽象的概念（订货）
属性Attribute ：实体或联系的特征。学生（学号，姓名，性别）
码，键Key ：唯一标识实体的属性或属性组
域Domain ：属性的取值范围。性别（男，女）
实体型Entity Type ：实体名＋属性名集合
实体集Entity Set ：同型实体的集合
联系Relationship: 实体与实体之间的联系
实体间的联系
实体之间的对应关系称为联系，它反映了现实世界事物之间的相互关联。
联系的类型：
一对一联系记为1 ：1。
一对多联系记为１：n。
多对多联系记为m ：n。
概念模型的表示方法
E-R图：提供了表示实体、属性和实体间联系的方法

建立E-R图的步骤

确定实体和实体的属性
确定实体和实体之间的联系及联系的类型
给联系和实体加上属性。
E-R模型实例

数据模型定义

数据模型是表示实体类型和实体间联系的模型，是机器世界对现实世界中的数据和信息抽象、表示和处理。
包括逻辑模型和物理模型。
数据模型的组成要素(三要素)
数据结构、数据操作和数据完整性约束
数据结构

数据结构的概念

实体及实体间联系的表示方法，描述了系统的静态特性.

数据结构描述的内容

对象的数据类型、内容、性质有关的对象；
与数据之间联系有关的对象；
数据操作
1. 概念
对数据库中各种对象(型)的实例(值)允许执行的操作及有关的操作规则，是对系统动态特性的描述。

数据操作的类型（2大类）

数据检索（查询）
数据更新(包括插入、删除、修改)
数据完整性约束

概念：完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则。
作用：限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效、相容。
四种类型：实体完整性约束、域完整性约束、参照完整性约束和用户自定义完整性约束。
最常用的数据模型
非关系模型

层次模型(Hierarchical Model)
层次模型是数据库系统中最早出现的数据模型;
层次数据库系统的典型代表是IBM 公司1968年年推出的IMS(Information Management System);
层次模型用树形结构(“ 有向树”) 来表示各类实体以及实体间的联系.
网状模型(Network Model)
典型代表是DBTG;
通过有向图结构表示实体及联系。“网”中每个结点表示一个实体( 型) ，结点之间箭头表示实体( 型);
网状数据模型可能有多个根结点，某些非根结点可能有多个父结点，适合表示实体的多对多联系。

关系模型(Relational Model)
1970年美国IBM公司San Jose研究室的研究员E.F.Codd首次提出了数据库系统的关系模型
关系数据结构：规范化的二维表（关系）
一个关系就是没有重复行和重复列的二维表，二维表的每一行在关系中称为元组，每一列在关系中称为属性。学生关系的每一行代表一个学生的记录，每一列代表学生记录的一个字段。属性个数（n）称为关系的元。

三种模型比较

1.5数据库系统结构
数据库系统内部的体系结构
从数据库管理系统角度看，数据库系统通常采用三级模式结构，是数据库系统内部的系统结构。
数据库系统外部的体系结构
从数据库最终用户角度看，数据库系统的结构分为:集中式结构；客户/服务器结构；浏览器／应用服务器／数据库服务器多层结构等。
为了有效地组织、管理数据，提高数据库的逻辑独立性和物理独立性，数据库设计的标准体系结构是3 级模式结构，即外模式 ,模式和内模式
对应于体系结构，用户或程序员看到或使用的数据库内容被称为视图，可把视图相应分为：

对应于用户的外部视图（用户视图）
对应于应用程序员的 (概念视图)
对应于系统程序员的内部视图（存储视图）

模式（Schema，也称逻辑模式，概念视图）

– 数据库中全体数据的逻辑结构和特征的描述。
– 一个数据库只有一个模式
– 模式的地位：是数据库系统模式结构的中间层

外模式（External Schema）

– 也称子模式或用户模式；
– 数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
– 外模式的地位：介于模式与应用之间
– 外模式通常是模式的子集
– 一个数据库可以有多个外模式。反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求

内模式（Internal Schema）

– 是数据物理结构和存储方式
– 是数据在数据库内部的表示方式
3级模式是对数据的三个抽象级别
2级映象在DBMS 内部实现这三个抽象层次的联系和转换；

外模式\ 模式映像
– 保证数据的逻辑独立性
– 同一个模式可以有任意多个外模式，外模式／模式映象定义外模式与模式之间的对应关系；
– 当模式改变时，外模式／模式映象使外模式保持不变；
– 应用程序是依据数据的外模式编写的，从而不必修改应用程序，保证了数据与程序的逻辑独立性
- 模式\内模式映像
– 保证数据的物理独立性。
– 模式／内模式映象定义了数据全局逻辑结构与存储结构之间的对应关系。
– 当数据库的存储结构改变了，模式／内模式映象使模式保持不变。
– 应用程序不受影响。保证了数据与程序的物理独立性。

从数据库最终用户角度看（数据库系统外部的体系结构），数据库系统的结构分为:

集中式数据库系统
客户/服务器（C/S）系统结构
分布式数据库系统结构
浏览器／服务器（B/S）系统结构

关系数据库

2.1关系数据模型的基本概念

关系模型的组成（三要素）

关系数据结构（静态特性）
现实世界的实体以及实体间的各种联系均用关系来表示;
数据的逻辑结构—-二维表
关系操作集合（动态特性）
插入、删除、修改、查询（选择、投影、连接、除、并、交、差）
关系完整性约束
实体完整性、参照完整性、域完整性、用户定义完整性

1）关系: 通常将一个无重复行、重复列的二维表看成一个关系，每个关系都有一个关系名。
2）元组: 二维表的每一行在关系中称为元组。描述了现实世界中的一个实体或不同实体间的一种联系。
3）属性: 二维表的每一列在关系中称为属性，每个属性都有一个属性名，各个属性的取值称为属性值。每个属性有一定的取值范围，称为值域。
4）域（Domain): 关系中每个属性所对应的变化范围叫做属性的变域或简称域，关系中所有属性的实际取值必须来自它对应的域。
5）分量: 一个元组在一个属性域上的取值称为该元组在此属性上的分量。
6）关系模式: 二维表的表头那一行称为关系模式，即一个关系的关系名及其全部属性名的集合。关系模式是概念模型中实体型及实体型之间联系的数据模型表示。一般表示为：
关系名（属性名1，属性名2 ， ……，属性名n）
关系模式指出了一个关系的结构；而关系则是由满足关系模式结构的元组构成的集合。关系模式是稳定的、静态的，而关系则是随时间变化的、动态的。
7）候选码（Candidate key）: 若关系中的某一属性或属性组合的值能唯一地标识一个元组，则称该属性组为候选码
在最简单的情况下，候选码只包含一个属性。
在最极端的情况下，关系模式的所有属性组是这个关系模式的候选码，称为全码（All-key）
8）主码: 若一个关系有多个候选码，则选定其中一个为主
码（Primary key）
9）主属性和非主属性
– 主码的各个属性称为主属性（Prime attribute）。
– 不包含在任何候选码中的属性称为非主属性Non-key attribute）
10）外码: 如果关系中某个属性或属性组合并非码，但却是另一个关系的主码，则称此属性或属性组合为本关系的外码或外键(ForeignKey)。在关系数据库中，用外码表示两个表间的联系。

2.2关系数据模型的集合论定义

笛卡尔积

笛卡尔积可表示为一个二维表。表中的每行对应一个元组，表中的每列对应一个域。
基数（Cardinal number）
关系
笛卡尔积中的任意子集叫作在域上的n元关系，简称关系。
n元关系就会有n个属性。一个关系中的每一个属性的属性名都不同，对应参与笛卡儿积运算的每个集合的名称。
一个属性的取值范围Di 称为该属性的域（Domain）。
基本关系的性质
关系模式
2.3 关系模型的完整性约束
数据库系统提供的监测机制，确保存储数据的规范，称为完整性约束。
- 实体完整性
  实体完整性通过定义主码来实现的。若属性A A 是关系R R 的主属性，则A A 不能取空值且取值唯一；
  一个关系模型中的所有元组都是惟一的，没有两个完全相同的元组，也就是一个二维表中没有两个完全相同行，也称为行完整性。
域完整性
对关系R R 中属性（列）数据的规范，也称列完整性，限制属性的数据类型、格式、取值范围、是否允许空值等。
参照完整性
参照完整性是通过定义外键 FK （存在或为空）来建立实体间联系的。
如外键取空，则 FK 中的每个属性的分量都是空值。
用户定义完整性
针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。
2.4关系代数
笛卡尔积
选择（限制）
投影（Projection）
连接（Join ）

除（Division ）

SQL Server 2008 数据库基础

SQL Server 简介

常见数据库产品包括甲骨文公司的Oracle系统，IBM 公司的DB2 系统和Informix 系统，赛贝斯公司的Sybase ASE 系统，微软公司的Microsoft SQL Server 系统和Access 系统，以及MySQL
2008 年8 月，微软发布Microsoft SQL Server 2008 系统，其代码是Katmai 。该系统在安全性、可用性、易管理性、可扩展性、商业智能等方面有了更多的改进和提高，支持更强大的数据存储和应用需求。

SQL Server 2008登录

SQL Server Management Studio 简介

SQL Server 2008 数据库的创建与维护

SQL Server 2008 数据存储结构
（1 ）数据库文件
- 主数据文件.mdf
- 次要数据文件 .ndf
- 日志文件 .ldf
- 数据文件结构组成
- 页
  _ SQL Server最小存储数据单位
  _ 一个页大小是8K
- 区
  _ 管理空间的基本单位
  _ 8个物理上连续的页（64 KB）
  _ 表被存储在区

（2)数据库文件组

(3)数据库创建

（4）数据库修改

（5）数据库删除

Transact-SQL 程序设计基础

（1 ）标识符分类

（2）数据类型

（3）全局变量

（4）运算符

（5）批处理

（6）函数

（7）流程控制

关系数据库标准语言SQL

SQL 的三级模式结构

SQL 的数据定义

基本表定义
基本表的修改与删除
索引的建立和删除

查询语句

查询的基本结构包括了三个子句： SELECT、 FROM 、 WHERE。

从表中（From子句）,选择满足记录选择条件（Where子句）的记录，并对他们进行分组（Group子句, Having子句表达组选择条件）、统计(统计函数）、排序（Order by 子句）和投影（Select子句），形成查询结果集。

单表无条件查询
DISTINCT 保留字的使用：可以使查询的结果中重复列值只保留一个。
查询列中含有运算的表达式。
查询列中含有字符串常量。
查询列中含有聚合函数。
单表有条件查询

分组查询和排序查询

多表查询

SQL通过连接查询、笛卡尔积、并操作、交操作、差操作5种关系代数中的运算功能来实现多个数据表的查询。SQL通过连接查询、笛卡尔积、并操作、交操作、差操作5种关系代数中的运算功能来实现多个数据表的查询。
SQL 提供在子句中列出每个关系然后在SELECT子句和WHERE子句中引用FROM子句中的关系属性，而WHERE子句中用来连接两个关系的条件。在SELECT子句和WHERE子句中引用FROM子句中的关系属性，而WHERE子句中用来连接两个关系的条件。
连接查询包括等值连接、非等值连接、自身连接、内连接和外连接等值连接、非等值连接、自身连接、内连接和外连接等。

5. 嵌套查询

SQL 的数据操纵

插入数据
修改数据
删除数据

视图
- 视图是外模式的基本单位，实际上视图是从若干个基本表或视图导出来的虚表。
- 当基本表的数据发生变化时，相应的视图数据也会随之改变。
- 视图定义后，可以被用户查询、更新，但通过视图来更新基本表中的数据要有一定的限制。

定义视图

2. 删除视图

3. 查询视图

4. 更新视图

视图的作用

存储过程

创建执行
修改删除
查看储存

触发器
- 触发器是一种特殊的存储过程，在对表或视图执行，触发器是一种特殊的存储过程，在对表或视图执行 UPDATE 、 INSERT 或 DELETE 操作时自动触发执行操作时自动触发执行。
  - 触发器技术是保证数据完整性的高级技术，触发器还可以用于对系统的高级监测，用于实施完整性和强制执行业务规则。

关系数据库规范化理论

关系模式的形式化定义

函数依赖的基本概念

函数依赖

码的函数依赖

关系模式的规范化

第 1 范式 1NF
第 2 范式 2NF
第 3 范式 3NF
BCNF 范式
多值依赖与第 4 范式

数据库设计

需求分析

概念结构设计

逻辑结构设计

物理结构设计

数据库实施

数据库运行与维护

MySQL 开发规范

数据库对象命名规范

数据库对象

数据库对象是数据库的组成部分，常见的有以下几种：表（Table ）、索引（Index）、视图（View）、图表（Diagram）、缺省值（Default）、规则（Rule）、触发器（Trigger）、存储过程（Stored Procedure）、用户（User）等。命名规范是指数据库对象如数据库（SCHEMA）、表（TABLE）、索引（INDEX）、约束（CONSTRAINTS）等的命名约定。

数据库对象全局命名规范

1、命名使用具有意义的英文词汇，词汇中间以下划线分隔

2、命名只能使用英文字母、数字、下划线，以英文字母开头

3、避免用MySQL的保留字如：backup、call、group等

4、所有数据库对象使用小写字母，实际上MySQL中是可以设置大小写是否敏感的，为了保证统一性，我们这边规范全部小写表示。

数据库命名规范

1、数据库命名尽量不超过30个字符。

2、数据库命名一般为项目名称+代表库含义的简写，比如IM项目的工作流数据库，可以是 im_flow。

3、数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8（已迁移dumbo的使用utf8mb4）

4、命名应使用小写。

表命名规范

1、常规表表名以t_开头，t代表table的意思，命名规则即 t + 模块（包含模块含义的简写）+ 表（包含表含义的简写），比如用户模块的教育信息表：t_user_eduinfo。

2、临时表（RD、QA或DBA同学用于数据临时处理的表），命名规则：temp前缀+模块+表+日期后缀：temp_user_eduinfo_20210719

3、备份表（用于保存和归档历史数据或者作为灾备恢复的数据）命名规则，bak前缀+模块+表+日期后缀：bak_user_eduinfo_20210719

4、同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义

5、多个单词以下划线 _ 分隔

6、常规表表名尽量不超过30个字符，temp表和bak表视情况而定，也尽量简短为宜，命名应使用小写

字段命名规范

1、字段命名需要表示其实际含义的英文单词或简写，单词之间用下划线 _ 进行连接，如 service_ip、service_port。

2、各表之间相同意义的字段必须同名，比如a表和b表都有创建时间，应该统一为create_time，不一致会很混乱。

3、多个单词以下划线 _ 分隔

4、字段名尽量不超过30个字符，命名应该使用小写

索引命名规范

1、唯一索引使用uni + 字段名来命名：create unique index uni_uid on t_user_basic(uid) 。

2、非唯一索引使用idx + 字段名来命名：create index idx_uname_mobile on t_user_basic(uname,mobile) 。

3、多个单词以下划线 _ 分隔。

4、索引名尽量不超过50个字符，命名应该使用小写，组合索引的字段不宜太多，不然也不利于查询效率的提升。

5、多单词组成的列名，取尽可能代表意义的缩写，如 test_contact表member_id和friend_id上的组合索引：idx_mid_fid。

6、理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)。

视图命名规范

1、视图名以v开头，表示view，完整结构是v+视图内容含义缩写。

2、如果视图只来源单个表，则为v+表名。如果视图由几个表关联产生就用v+下划线（_）连接几个表名，视图名尽量不超过30个字符。如超过30个字符则取简写。

3、如无特殊需要，严禁开发人员创建视图。

4、命名应使用小写。

存储过程命名规范

1、存储过程名以sp开头，表示存储过程（storage procedure）。之后多个单词以下划线（_）进行连接。存储过程命名中应体现其功能。存储过程名尽量不能超过30个字符。

2、存储过程中的输入参数以i_开头，输出参数以o_开头。

3、命名应使用小写。

  create procedure sp_multi_param(in i_id bigint,in i_name varchar(32),out o_memo varchar(100))

函数命名规范

1、函数名以func开始，表示function。之后多个单词以下划线（_）进行连接，函数命名中应体现其功能。函数名尽量不超过30个字符。

2、命名应使用小写。

  create function func_format_date(ctime datetime)

触发器命名规范

1、触发器以trig开头，表示trigger 触发器。

2、基本部分，描述触发器所加的表，触发器名尽量不超过30个字符。

3、后缀（_i,_u,_d）,表示触发条件的触发方式（insert,update或delete）。

4、命名应使用小写。

  DROP TRIGGER IF EXISTS trig_attach_log_d;
  CREATE TRIGGER trig_attach_log_d AFTER DELETE ON t_dept FOR EACH ROW;

约束命名规范

1、唯一约束：uk_表名称_字段名。uk是UNIQUE KEY的缩写。比如给一个部门的部门名称加上唯一约束，来保证不重名，如下：ALTER TABLE t_dept ADD CONSTRAINT un_name UNIQUE(name);

2、外键约束：fk_表名，后面紧跟该外键所在的表名和对应的主表名（不含t_）。子表名和父表名用下划线(_)分隔。如下：ALTER TABLE t_user ADD CONSTRAINT fk_user_dept FOREIGN KEY(depno) REFERENCES t_dept (id);

3、非空约束：如无特殊需要，建议所有字段默认非空(not null)，不同数据类型必须给出默认值(default)。

  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT '',
  `deptId` int(11) DEFAULT 0,
  `salary` float DEFAULT NULL,

4、出于性能考虑，如无特殊需要，建议不使用外键。参照完整性由代码控制。这个也是我们普遍的做法，从程序角度进行完整性控制，但是如果不注意，也会产生脏数据。

5、命名应使用小写。

用户命名规范

1、生产使用的用户命名格式为 code_应用

2、只读用户命名规则为 read_应用

数据库对象设计规范

存储引擎的选择

1、如无特殊需求，必须使用innodb存储引擎。

可以通过 show variables like ‘default_storage_engine’ 来查看当前默认引擎。主要有MyISAM 和 InnoDB，从5.5版本开始默认使用 InnoDB 引擎。

基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

字符集的选择

1、如无特殊要求，必须使用utf8或utf8mb4。

在国内，选择对中文和各语言支持都非常完善的utf8格式是最好的方式，MySQL在5.5之后增加utf8mb4编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。

所以utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

可以使用如下脚本来查看数据库的编码格式

1 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
2 -- 或
3 SHOW VARIABLES Like '%char%';

表设计规范

1、不同应用间所对应的数据库表之间的关联应尽可能减少，不允许使用外键对表之间进行关联，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性。目前业内的做法一般由程序控制参照完整性。

2、表设计的角度不应该针对整个系统进行数据库设计，而应该根据系统架构中组件划分，针对每个组件所处理的业务进行数据库设计。

3、表必须要有PK，主键的优势是唯一标识、有效引用、高效检索，所以一般情况下尽量有主键字段。

4、一个字段只表示一个含义。

5、表不应该有重复列。

6、禁止使用复杂数据类型(数组,自定义等)，Json类型的使用视情况而定。

7、需要join的字段(连接键)，数据类型必须保持绝对一致，避免隐式转换。比如关联的字段都是int类型。

8、设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计，但在项目评审时需要对冗余字段的设计给出解释。

9、TEXT字段作为大体量文本存储，必须放在独立的表中 , 用PK与主表关联。如无特殊需要，禁止使用TEXT、BLOB字段。

10、需要定期删除(或者转移)过期数据的表，通过分表解决，我们的做法是按照2/8法则将操作频率较低的历史数据迁移到历史表中，按照时间或者则曾Id做切割点。

11、单表字段数不要太多，建议最多不要大于50个。过度的宽表对性能也是很大的影响。

12、MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据行数控制在2000W内。

业内的规则是超过2000W性能开始明显降低。但是这个值是灵活的，你可以根据实际情况进行测试来判断，比如阿里的标准就是500W，百度的确是2000W。实际上是否宽表，单行数据所占用的空间都有起到作用的。

13、如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略，后续会有专门的文章来分析数据拆分的做法：垂直拆分（垂直分库和垂直分表）、水平拆分（分库分表和库内分表）；

14、无特殊需求，严禁使用分区表

字段设计规范

1、INT：如无特殊需要，存放整型数字使用UNSIGNED INT型，整型字段后的数字代表显示长度。比如 id int(11) NOT NULL

2、DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型。

对于TIMESTAMP，它把写入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。

另外DATETIME存储的范围也比较大：

timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。

datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。

但是特殊情况，对于跨时区的业务，TIMESTAMP更为合适。

3、VARCHAR：所有动态长度字符串全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替；VARCHAR(N)，

N表示的是字符数而不是字节数。比如VARCHAR(255)，可以最大可存储255个字符（字符包括英文字母，汉字，特殊字符等）。但N应尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，且存储字符个数由所选字符集决定。

如UTF8存储一个字符最大要3个字节，那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时，在进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。(如无特殊需要，原则上单个varchar型字段不允许超过255个字符)

4、TEXT：仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。

所有使用TEXT类型的字段必须和原表进行分拆，与原表主键单独组成另外一个表进行存放，与大文本字段的隔离，目的是。如无特殊需要，不使用MEDIUMTEXT、TEXT、LONGTEXT类型

5、对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE。

6、如无特殊需要，尽量不使用BLOB类型

7、如无特殊需要，字段建议使用NOT NULL属性，可用默认值代替NULL

8、自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分。

索引设计规范

1、索引区分度

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为: selecttivity = count(distinct c_name)/count(*) ; 如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

2、遵循最左前缀

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引，必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

7、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如：depno=1 and empname>’’ and job=1 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%’是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引

  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_brand_define` (`app_id`,`define_id`)

当你同事业务代码中的检索语句如下的时候，应该立即警告了，即没有覆盖索引，也没按照最左前缀原则：

  select brand_id,brand_name from  ds_brand_system where status=?  and define_id=?  and app_id=?

建议改成如下：

  select brand_id,brand_name from  ds_brand_system where app_id=? and define_id=?  and  status=?

约束设计规范

1、PK应该是有序并且无意义的，由开发人员自定义，尽可能简短，并且是自增序列。

2、表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uk_”作为前缀的唯一约束索引。

3、PK字段不允许更新。

4、禁止创建外键约束，外键约束由程序控制。

5、如无特殊需要，所有字段必须添加非空约束，即not null。

6、如无特殊需要，所有字段必须有默认值。

SQL使用规范

select 检索的规范性

1、尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数。

一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。

2、严禁使用 select * from t_name ，而不加任何where条件，道理一样，这样会变成全表全字段扫描。

3、MySQL中的text类型字段存储：

3.1、不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。

3.2、如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下

4、在取出字段上可以使用相关函数，但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。大量的计算和转换会造成效率低下，这个在索引那边也描述过了。

5、分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序

6、用in()/union替换or，效率会好一些，并注意in的个数小于300

7、严禁使用%前缀进行模糊前缀查询:如：select a,b,c from t_name where a like ‘%name’; 可以使用%模糊后缀查询如：select a,b from t_name where a like ‘name%’;

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

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

操作的规范性

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

如：insert into values (‘a’,’b’,’c’); 应使用 insert into t_name(c1,c2,c3) values (‘a’,’b’,’c’); 。

2、大批量写操作（UPDATE、DELETE、INSERT），需要分批多次进行操作

大批量操作可能会造成严重的主从延迟，特别是主从模式下，大批量操作可能会造成严重的主从延迟，因为需要slave从master的binlog中读取日志来进行数据同步。
binlog日志为row格式时会产生大量的日志

程序上的约束

后续我们团队的目标是研发评审工具对开发同学提交的建库、建表、刷数据、查询的语句进行分析，看看是否符合应有的规范。如果不符合，驳回修改。

JackHCC

https://jackhcc.github.io/posts/Database-SQL.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 JackHCC !

SQL

R语言

R语言详解

2020-03-24 Language

数学建模算法学习

数学建模常见算法总结

2020-03-20 Algorithm

Algorithm

SQL Server：数据库基础

SQL数据库基础

绪论

1.1 数据、信息与数据处理

1.2 数据管理技术的产生与发展

数据库系统阶段——数据独立性高

数据库系统阶段——DBMS提供数据控制功能

数据库系统的发展

1.3 数据库系统的组成

1.4数据模型

概念模型（信息世界中）的基本概念

实体间的联系

概念模型的表示方法

建立E-R图的步骤

E-R模型实例

数据模型定义

数据模型的组成要素(三要素)

数据结构

数据操作

数据完整性约束

最常用的数据模型

三种模型比较

1.5数据库系统结构

关系数据库

2.1关系数据模型的基本概念

2.2关系数据模型的集合论定义

笛卡尔积

关系

基本关系的性质

关系模式

2.3 关系模型的完整性约束

2.4关系代数

笛卡尔积

选择（限制）

投影（Projection）

连接（Join ）

除（Division ）

SQL Server 2008 数据库基础

SQL Server 简介

SQL Server 2008登录

SQL Server Management Studio 简介

SQL Server 2008 数据库的创建与维护

Transact-SQL 程序设计基础

关系数据库标准语言SQL

SQL 的三级模式结构

SQL 的数据定义

关系数据库规范化理论

关系模式的形式化定义

函数依赖的基本概念

关系模式的规范化

数据库设计

需求分析

概念结构设计

逻辑结构设计

物理结构设计

数据库实施

数据库运行与维护

MySQL 开发规范

数据库对象命名规范

数据库对象

数据库对象全局命名规范

数据库命名规范

表命名规范

字段命名规范

索引命名规范

视图命名规范

存储过程命名规范

函数命名规范

触发器命名规范

约束命名规范

用户命名规范

数据库对象设计规范

存储引擎的选择

字符集的选择

表设计规范

字段设计规范

索引设计规范

约束设计规范

SQL使用规范

select 检索的规范性