Mysql学习总结（77）——温故Mysql数据库开发核心原则与规范

川长思鸟来 2024-04-18 21:57 70阅读 0赞

# 一、核心原则 #

### 1、尽量不在数据库做运算 ###

俗话说：别让脚趾头想事情，那是脑瓜子的职责。作为数据库开发人员，我们应该让数据库多做她所擅长的事情。尽量不在数据库做运算，复杂运算移到程序端CPU，尽可能简单应用MYSQL。

**举例：在mysql中尽量不要使用如：md5()、Order by Rand()等这类运算函数**

### 2、尽量控制单表数据量 ###

大家都知道单表数据量过大后会影响数据查询效率，严重情况下会导致整个库都卡住。一般情况下，按照一年内单表数据量预估：纯INT不超过1000W，含CHAR不超过500W，同时要尽量做好合理的分表，使单表数据量不超载，常见的分表策略有：通过**USERID来分表（根据ID区间分表）**：在金融行业应用较多，用户量大、用户特征明显。**按DATE分表（按天、周、月分表）**：在电信行业应用非常多，如用户上网记录表、用户短信表、话单表等。**按AREA分表**（省、市、区分表）。分区表的适用场景主要有：

*  **① 表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据；**
 *  **② 分区表的数据更易维护，可以对独立的分区进行独立的操作；**
 *  **③ 分区表的数据可以分布在不同的机器上，从而高效使用资源；**
 *  **④ 可以使用分区表来避免某些特殊的瓶颈；**
 *  **⑤ 可以备份和恢复独立的分区。**
 *  **但是使用分区表同样有一些限制，在使用的时候需要注意：**
 *  **① 一个表最多只能有 1024 个分区；**
 *  **② 5.1版本中，分区表表达式必须是整数， 5.5可以使用列分区；**
 *  **③ 分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来；**
 *  **④ 分区表中无法使用外键约束；**
 *  **⑤ 需要对现有表的结构进行修改；**
 *  **⑥ 所有分区都必须使用相同的存储引擎；**
 *  **⑦ 分区函数中可以使用的函数和表达式会有一些限制；**
 *  **⑧ 某些存储引擎不支持分区；**
 *  **⑨ 对于 MyISAM 的分区表，不能使用 load index into cache；**
 *  **⑩ 对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。**

### 3、尽量控制表字段数量 ###

单表的字段数量也不能太多，根据业务场景进行优化调整，尽量调整表字段数少而精，这样有以下好处：**IO高效、全表遍历、****表修复快、提高并发、alter table更快。**那究竟单表多少字段合适呢？按照单表1G体积，500W行数据量进行评估：**顺序读1G文件需N秒、单行不超过200Byte、单表不超50个纯INT字段、单表不超20个CHAR(10)字段、建议单表字段数上限控制在20~50个。**

### 4、平衡范式与冗余 ###

数据库表结构的设计也讲究平衡，以往我们经常说要严格遵循三大范式，所以先来说说什么是范式。**第一范式：单个字段不可再分。唯一性。第二范式：不存在非主属性只依赖部分主键。消除不完全依赖。第三范式：消除传递依赖。**用一句话来总结范式和冗余：**冗余是以存储换取性能，范式是以性能换取存储。**所以，**一般在实际工作中冗余更受欢迎一些**。模型设计时，这两方面的具体的权衡，首先要以企业提供的计算能力和存储资源为基础。其次，一般互联网行业中都根据Kimball模式实施数据仓库，建模也是以任务驱动的，因此冗余和范式的权衡符合任务需要。例如，一份指标数据，必须在早上8点之前处理完成，但计算的时间窗口又很小，要尽可能减少指标的计算耗时，这时在计算过程中要尽可能减少多表关联，模型设计时需要做更多的冗余。

### 5、拒绝3B ###

数据库的并发就像城市交通，呈非线性增长，这就要求我们在做数据库开发的时候一定要注意高并发下的瓶颈，**防止因高并发造成数据库瘫痪**。这里的拒绝3B是指：**大SQL（BIG SQL）、大事务（BIG Transaction）、大批量（BIG Batch）。**

# 二、字段类原则 #

### 1、用好数值字段类型 ###

整型：TINYINT(1Byte)、TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B)；浮点型：FLOAT(4B)、DOUBLE(8B)、DECIMAL(M,D)。以几个常见的例子来进行说明：

**1）、INT(1) VS INT(11)**

很多人都分不清INT(1)和INT(11)的区别，想必大家也很好奇吧，其实1和11其实只是显示长度的区别而已，也就是不管int（x）x的值是什么值，存储数字的取值范围还是int本身数据类型的取值范围，x只是数据显示的长度而已。

**2）、BIGINT AUTO\_INCREMENT**

大家都知道，有符号int最大可以支持到约22亿，远远大于我们的需求和MySQL单表所能支持的性能上限。对于OLTP应用来说，单表的规模一般要保持在千万级别，不会达到22亿上限。如果要加大预留量，可以把主键改为改为无符号int，上限为42亿，这个预留量已经是非常的充足了。使用bigint，会占用更大的磁盘和内存空间，内存空间毕竟有限，无效的占用会导致更多的数据换入换出，额外增加了IO的压力，对性能是不利的。**因此推荐自增主键使用int unsigned类型，但不建议使用bigint**。

**3）、DECIMAL(N,0)**

当采用DECIMAL数据类型的时候，一般小数位数不会是0，如果小数位数设置为0，那建议使用INT类型

### 2、将字符转化为数字 ###

**数字型VS字符串型索引有更多优势：更高效、查询更快、占用空间更小。**举例：用无符号INT存储IP，而非CHAR(15) INT UNSIGNED，可以用INET\_ATON()和INET\_NTOA()来实现IP字符串和数值之间的转换。

### 3、优先使用ENUM或SET ###

对于一些枚举型数据，我们推荐优先使用ENUM或SET，这样的场景适合：**字符串型并且可能值已知且有限**。存储方面：1）ENUM占用1字节，转为数值运算，2）SET视节点定，最多占用8字节，3）比较时需要加‘单引号（即使是数值）举例：

`sex` enum('F','M') COMMENT '性别'；
    `c1` enum('0','1','2','3') COMMENT '审核'；

### 4、避免使用NULL字段 ###

为什么在数据库表字段设计的时候尽量都加上NOT NULL DEFAULT ''，这里面不得不说用NULL字段的弊端：很难进行查询优化，NULL列加索引，需要额外空间，含NULL复合索引无效。举例：

`a` char(32) DEFAULT NULL 【不推荐】
    `b` int(10) NOT NULL 【不推荐】
    `c` int(10) NOT NULL DEFAULT 0 【推荐】

### 5、少用并拆分TEXT/BLOB ###

TEXT类型处理性能远低于VARCHAR，强制生成硬盘临时表，浪费更多空间。VARCHAR(65535)==>64K(注意UTF-8)，**尽量不用TEXT/BLOB数据类型。如果业务需要必须用，建议拆分到单独的表**。如下举例：

CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    data TEXT NOT NULL,
    PRIMARY KEY(id)
    ) ENGINE=InnoDB;

### 6、不在数据库里存图片 ###

如果将图片全部存在数据库，将使得数据库体积变大，会造成读写速度变慢。**图片存数据库的弊端：对数据库的读/写的速度永远都赶不上文件系统处理的速度，数据库备份变的巨大，越来越耗时间，对文件的访问需要穿越你的应用层和数据库层**。**推荐数据库中保存图片路径。**按照年月日生成路径。具体是按照年月日还是按照年月去生成路径，根据自己需要(不一定是按照日期去生成)。理解为什么要分散到多个文件夹中去才是关键，涉及到一个原理就明白了：**操作系统对单个目录的文件数量是有限制的。当文件数量很多的时候。从目录中获取文件的速度就会越来越慢。所以为了保持速度，才要按照固定规则去分散到多个目录中去。图片分散到磁盘路径中去。**数据库字段中保存的是类似于这样子的”images/2012/09/25/ 1343287394783.jpg”原来上传的图片文件名称会重新命名保存，比如按照时间戳来生成，1343287394783. jpg。这样子是为了避免文件名重复，多个人往同一个目录上传图片的时候会出现。反正用什么样的规则命名图片，只要做到图片名称的唯一性即可。比如网站的并发访问量大，目录的生成分得月细越好。比如精确到小时，一个小时都可以是一个文件夹。同时0.001秒有两个用户同时在上传图片(因为那么就会往同一个小时文件夹里面存图片)。因为时间戳是精确到秒的。为了做到图片名称唯一性而不至于覆盖，生成可以在在时间戳后面继续加毫秒微秒等。总结的规律是，并发访问量越大。就越精确就好了。（**注意：数据库保存的路径不要把域名也保存进去了，而且保存的路径最好是相对路径**）

# 三、索引类原则 #

### 1、谨慎合理添加索引 ###

**添加索引是为了改善查询，添加索引会减慢更新，索引不是越多越好，能不加的索引尽量不加（综合评估数据密度和数据分布，最好不超过字段数20%），结合核心SQL有限考虑覆盖索引。**举例：**不要给“性别”列创建索引**。**理论上值重复率高的字段不适合建索引。**不要说性别字段只有两个值，网友亲测，一个字段使用拼音首字母做值，共有26种可能，加上索引后，百万加的数据量，使用索引的速度比不使用索引要慢！**为什么性别不适合建索引呢？因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO**。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

### 2、字符字段必须建前缀索引 ###

单字母区分度：26；4字母区分度：26\*26\*26\*26 = 456,976；5字母区分度：26\*26\*26\*26\*26 = 11,881,376；6字母区分度：26\*26\*26\*26\*26\*26 = 308,915,776 。字符字段必须建前缀索引，例如：

create table P(
    `pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音',
    KEY `idx_pinyin` (`pinyin`(8)),
    ) ENGINE=InnoDB

### 3、不在索引列做运算 ###

在索引列计算会导致：无法使用索引、全表扫描。如下：

BAD SAMPLE:
    select * from table
    WHERE to_days(current_date) – to_days(date_col) <= 10
    GOOD SAMPLE:
    select * from table
    WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);

### 4、自增列或全局ID做INNODB主键 ###

**对主键建立聚簇索引，二级索引存储主键值，主键不应更新修改，按自增顺序插入值，忌用字符串做主键，聚簇索引分裂，推荐用独立于业务的AUTO\_INCREMENT列或全局ID生成器做代理主键，若不指定主键，InnoDB会用唯一且非空值索引代替。**

### 5、尽量不用外键 ###

线上OLTP系统尽量不用外键：外键可节省开发量，有额外开销，逐行操作，可“到达”其他表，意味着锁，高并发时容易死锁，建议由程序保证约束。比如我们原来建表语句是这样的：

CREATE TABLE `user` (
        `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
        `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
        PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `order` (
        `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
        `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
        `user_id` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`),
        KEY `for_indx_user_id` (`user_id`),
        CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不使用外键约束后：

不适用外键约束后，为了加快查询我们通常会给不建立外键约束的字段添加一个索引。实际开发中，一般不会建立外键约束。

CREATE TABLE `order` (
        `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
        `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
        `user_id` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 四、SQL类原则 #

### 1、SQL语句尽可能简单 ###

在开发过程中，我们**尽量要保持SQL语句的简单性**，我们对比一下大SQL和多个简单SQL。传统设计思想 BUG MySQL NOT，一条SQL只能在一个CPU运算，5000+ QPS的高并发中，1秒大SQL意味着？可能一条大SQL就把整个数据库堵死，拒绝大SQL，拆解成多条简单SQL，简单SQL缓存命中率更高，减少锁表时间，特别是MyISAM，用上多CPU。

### 2、保持事务（连接）短小 ###

**事务/连接使用原则：即开即用，用完即关。与事务无关操作都放到事务外面，减少锁资源的占用，不破坏一致性前提下，使用多个短事务代替长事务。**如：1）发帖时的图片上传等待。2）大量的sleep连接。3.尽可能避免使用SP/TRIG/FUNC。线上OLTP系统中，我们应当：**尽可能少用存储过程、尽可能少用触发器，减少使用MySQL函数对结果进行处理，将上述这些事情都交给客户端程序负责。**

### 4、尽量不用SELECT \* ###

**用SELECT \* 时，将会更多的消耗CPU、内存、IO以及网络带宽**。我们在写查询语句时，应当尽量不用SELECT \* ,只取需要的数据列。更安全的设计，减少表变化带来的影响，为使用covering index提供可能性。Select/JOIN 减少硬盘临时表生成，特别是有TEXT/BLOB时。

### 5、改写OR为IN() ###

同一字段，将or改写为in()。OR效率：O(n)，IN效率：O(Log n）。当n很大时，OR会慢很多。注意控制IN的个数，建议n小于200

Select * from opp WHERE phone='12347856' or phone='42242233'  #（不推荐）
    Select * from opp WHERE phone in ('12347856' , '42242233') #（推荐）

### 6、改写OR为UNION ###

不同字段，将or改为union。减少对不同字段进行 "or" 查询，Merge index往往很弱智，如果有足够信心：set global optimizer\_switch='index\_merge=off';

Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000'; #不推荐
    
    Select * from opp WHERE phone='010-88886666' union Select * from opp WHERE cellPhone='13800138000'; # 推荐

### 7、避免负向查询和%前缀模糊查询 ###

在实际开发中，我们要尽量避免负向查询，那什么是负向查询呢，主要有以下：**NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。同时，我们还要避免%前缀模糊查询，因为这样会使用B+ Tree，同时会造成使用不了索引，并且会导致全表扫描，性能和效率可想而知。**

### 8、减少COUNT(\*) ###

在开发中我们经常会使用COUNT(\*)，殊不知这种用法会造成大量的资源浪费，**因为COUNT(\*)资源开销大，所以我们能不用尽量少用。**对于计数类统计，实时统计：用memcache，双向更新，凌晨跑基准。非实时统计：尽量用单独统计表，定期重算。

### 9、LIMIT高效分页 ###

传统分页：Select * from table limit 10000,10;
    LIMIT原理：
    Limit 10000,10
    偏移量越大则越慢
    推荐分页：
    Select * from table WHERE id>=23423 limit 11;
    #10+1 (每页10条)
    select * from table WHERE id>=23434 limit 11;
    分页方式二：
    Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
    分页方式三：
    SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;
    分页方式四：
    #先使用程序获取ID：
    select id from table limit 10000,10;
    #再用in获取ID对应的记录
    Select * from table WHERE id in (123,456…) ;
    具体需要根据实际的场景分析并重组索引

### 10、用UNION ALL 而非UNION ###

如果无需对结果进行去重，仅仅是对多表进行联合查询并展示，则用UNION ALL，因为UNION有去重开销。如下例子：

MySQL>SELECT * FROM detail20091128 UNION ALL
    SELECT * FROM detail20110427 UNION ALL
    SELECT * FROM detail20110426 UNION ALL
    SELECT * FROM detail20110425 UNION ALL
    SELECT * FROM detail20110424 UNION ALL
    SELECT * FROM detail20110423;

### 11、分解联接保证高并发 ###

高并发DB不建议进行两个表以上的JOIN。适当分解联接保证高并发：可缓存大量早期数据，使用了多个MyISAM表，对大表的小ID IN()，联接引用同一个表多次。

原SQL：
    
    MySQL> Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post
    on tag_post.post_id=post.id WHERE tag.tag=‘二手玩具’;
    
    分解SQL：
    
    MySQL> Select * from tag WHERE tag=‘二手玩具’;
    MySQL> Select * from tag_post WHERE tag_id=1321;
    MySQL> Select * from post WHERE post.id in (123,456,314,141)

### 12、GROUP BY 去除排序 ###

使用GROUP BY可以实现分组和自动排序。无需排序：Order by NULL；特定排序：Group by DESC/ASC。

### 13、同数据类型的列值比较 ###

原则：数字对数字，字符对字符。数值列与字符类型比较：同时转换为双精度进行比对。字符列与数值类型比较：字符列整列转数值，不会使用索引查询。

### 14、Load data 导数据 ###

批量数据快导入：成批装载比单行装载更快，不需要每次刷新缓存。无索引时装载比索引装载更快。Insert values ,values，values 减少索引刷新。**Load data比insert快约20倍，尽量不用INSERT ... SELECT，一个是有延迟，另外就是会同步出错**。

### 15、打散大批量更新 ###

大批量更新尽量凌晨操作，避开高峰，凌晨不限制，白天上线默认为100条/秒（特殊再议）。

update post set tag=1 WHERE id in (1,2,3);
    sleep 0.01;
    update post set tag=1 WHERE id in (4,5,6);
    sleep 0.01;

### 16、Know Every SQL ###

作为DBA乃至数据库开发人员，我们必须对数据库的每条SQL都非常了解，常见的命令有：SHOW PROFILE、MYSQLsla、MySQLdumpslow、explain、Show Slow Log、Show Processlist、SHOW QUERY\_RESPONSE\_TIME(Percona）等

# 五、约定类原则 #

### 1、隔离线上线下 ###

构建数据库的生态环境，确保开发无线上库操作权限。原则：线上连线上，线下连线下。生产数据用pro库，预生产环境用pre库，测试用test库，开发用dev库。

### 2、禁止未经DBA确认的子查询 ###

大部分情况优化较差，特别WHERE中使用IN id的子查询，一般可用JOIN改写。如下例子：

MySQL> select * from table1 where id in (select id from table2);
    MySQL> insert into table1 (select * from table2); //可能导致复制异常

### 3、永远不在程序端显式加锁 ###

外部锁对数据库丌可控，高幵发时是灾难，极难调试和排查，对于类似并发扣款等一致性问题，我们采用事务来处理，Commit前进行二次校验冲突。

### 4、统一字符集为UTF8 ###

### 5、统一命名规范 ###

# 六、14个实用的数据库设计技巧 #

### **1、原始单据与实体之间的关系** ###

可以是一对一、一对多、多对多的关系。在一般情况下，它们是一对一的关系：即一张原始单据对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。这里的实体可以理解为基本表。明确这种对应关系后，对我们设计录入界面大有好处。

> 〖例1〗：一份员工履历资料，在人力资源信息系统中，就对应三个基本表：员工基本情况表、社会关系表、工作简历表。这就是“一张原始单证对应多个实体”的典型例子。

### **2、 主键与外键** ###

一般而言，一个实体不能既无主键又无外键。在E—R 图中, 处于叶子部位的实体, 可以定义主键，也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。

主键与外键的设计，在全局数据库的设计中，占有重要地位。当全局数据库的设计完成以后，有个美国数据库设计专家说：“键，到处都是键，除了键之外，什么也没有”，这就是他的数据库设计经验之谈，也反映了他对信息系统核心(数据模型)的高度抽象思想。因为：主键是实体的高度抽象，主键与外键的配对，表示实体之间的连接。

### **3、基本表的性质** ###

基本表与中间表、临时表不同，因为它具有如下四个特性：

(1) 原子性。基本表中的字段是不可再分解的。

(2) 原始性。基本表中的记录是原始数据（基础数据）的记录。

(3) 演绎性。由基本表与代码表中的数据，可以派生出所有的输出数据。

(4) 稳定性。基本表的结构是相对稳定的，表中的记录是要长期保存的。

理解基本表的性质后，在设计数据库时，就能将基本表与中间表、临时表区分开来。

### **4、范式标准** ###

基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

> 〖例2〗：有一张存放商品的基本表，如表1所示。“金额”这个字段的存在，表明该表的设计不满足第三范式，因为“金额”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“金额”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。

在Rose 2002中，规定列有两种类型：数据列和计算列。“金额”这样的列被称为“计算列”，而“单价”和“数量”这样的列被称为“数据列”。

表1 商品表的表结构

商品名称 商品型号 单价 数量 金额

电视机 29吋 2,500 40 100,000

### **5、通俗地理解三个范式** ###

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

### **6、要善于识别与正确处理多对多的关系** ###

若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是，在两者之间增加第三个实体。这样，原来一个多对多的关系，现在变为两个一对多的关系。要将原来两个实体的属性合理地分配到三个实体中去。这里的第三个实体，实质上是一个较复杂的关系，它对应一张基本表。一般来讲，数据库设计工具不能识别多对多的关系，但能处理多对多的关系。

> 〖例3〗：在“图书馆信息系统”中，“图书”是一个实体，“读者”也是一个实体。这两个实体之间的关系，是一个典型的多对多关系：一本图书在不同时间可以被多个读者借阅，一个读者又可以借多本图书。为此，要在二者之间增加第三个实体，该实体取名为“借还书”，它的属性为：借还时间、借还标志(0表示借书，1表示还书)，另外，它还应该有两个外键(“图书”的主键，“读者”的主键)，使它能与“图书”和“读者”连接。

### **7、主键PK的取值方法** ###

PK是供程序员使用的表间连接工具，可以是一无物理意义的数字串, 由程序自动加1来实现。也可以是有物理意义的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，建议字段的个数不要太多，多了不但索引占用空间大，而且速度也慢。

### **8、正确认识数据冗余** ###

主键与外键在多表中的重复出现, 不属于数据冗余，这个概念必须清楚，事实上有许多人还不清楚。非键字段的重复出现, 才是数据冗余！而且是一种低级冗余，即重复性的冗余。高级冗余不是字段的重复出现，而是字段的派生出现。

> 〖例4〗：商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。

### **9、E--R图没有标准答案** ###

信息系统的E--R图没有标准答案，因为它的设计与画法不是惟一的，只要它覆盖了系统需求的业务范围和功能内容，就是可行的。反之要修改E--R图。尽管它没有惟一的标准答案，并不意味着可以随意设计。好的E—R图的标准是：结构清晰、关联简洁、实体个数适中、属性分配合理、没有低级冗余。

### **10、视图技术在数据库设计中很有用** ###

与基本表、代码表、中间表不同，视图是一种虚表，它依赖数据源的实表而存在。视图是供程序员使用数据库的一个窗口，是基表数据综合的一种形式, 是数据处理的一种方法，是用户数据保密的一种手段。为了进行复杂处理、提高运算速度和节省存储空间, 视图的定义深度一般不得超过三层。若三层视图仍不够用, 则应在视图上定义临时表, 在临时表上再定义视图。这样反复交迭定义, 视图的深度就不受限制了。对于某些与国家政治、经济、技术、军事和安全利益有关的信息系统，视图的作用更加重要。这些系统的基本表完成物理设计之后，立即在基本表上建立第一层视图，这层视图的个数和结构，与基本表的个数和结构是完全相同。并且规定，所有的程序员，一律只准在视图上操作。只有数据库管理员，带着多个人员共同掌握的“安全钥匙”，才能直接在基本表上操作。请读者想想：这是为什么？ 个 ，这个推荐看一下。

### **11、中间表、报表和临时表** ###

中间表是存放统计数据的表，它是为数据仓库、输出报表或查询结果而设计的，有时它没有主键与外键(数据仓库除外)。临时表是程序员个人设计的，存放临时记录，为个人所用。基表和中间表由DBA维护，临时表由程序员自己用程序自动维护。

### **12、完整性约束表现在三个方面** ###

域的完整性：用Check来实现约束，在数据库设计工具中，对字段的取值范围进行定义时，有一个Check按钮，通过它定义字段的值城。参照完整性：用PK、FK、表级触发器来实现。用户定义完整性：它是一些业务规则，用存储过程和触发器来实现。

### **13、防止数据库设计打补丁的方法是“三少原则”** ###

(1) 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E--R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计；

(2) 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间；

(3) 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。这个方法很简单，有的人就是不习惯、不采纳、不执行。

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念，综合观点，不能孤立某一个原则。该原则是相对的，不是绝对的。“三多”原则肯定是错误的。试想：若覆盖系统同样的功能，一百个实体(共一千个属性) 的E--R图，肯定比二百个实体(共二千个属性)的E--R图，要好得多。

提倡“三少”原则，是叫读者学会利用数据库设计技术进行系统的数据集成。数据集成的步骤是将文件系统集成为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为全局综合数据库。

集成的程度越高，数据共享性就越强，信息孤岛现象就越少，整个企业信息系统的全局E—R图中实体的个数、主键的个数、属性的个数就会越少。

提倡“三少”原则的目的，是防止读者利用打补丁技术，不断地对数据库进行增删改，使企业数据库变成了随意设计数据库表的“垃圾堆”，或数据库表的“大杂院”，最后造成数据库中的基本表、代码表、中间表、临时表杂乱无章，不计其数，导致企事业单位的信息系统无法维护而瘫痪。

“三多”原则任何人都可以做到，该原则是“打补丁方法”设计数据库的歪理学说。“三少”原则是少而精的原则，它要求有较高的数据库设计技巧与艺术，不是任何人都能做到的，因为该原则是杜绝用“打补丁方法”设计数据库的理论依据。

### **14、提高数据库运行效率的办法** ###

在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：

(1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。

(2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C++语言计算处理完成之后，最后才入库追加到表中去。这是电信计费系统设计的经验。

(3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。

(4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。 SQL 数据库小技巧，这个推荐看一下。

(5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。

# 七、总结 #

要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。上述十四个技巧，是许多人在大量的数据库分析与设计实践中，逐步总结出来的。对于这些经验的运用，读者不能生帮硬套，死记硬背，而要消化理解，实事求是，灵活掌握。并逐步做到：在应用中发展，在发展中应用。