2-数据库高级操作

数据库设计优化 - 字段
字段类 型
99.9%的情况下，全部为NOT NULL类型 预计不会存储非负数的字段 ，例如各项id、发帖数等，必 须设置为UNSIGNED类型 存储开关、选项数据的字段 ，通常使用tinyint(1)非 UNSIGNED类型，少数情况 也可能使用enum()结果集的 方式 任何类型的数据表，字段空 间应当本着足够用，不浪费 的原则
只要数据列里的内容出现重复，就意味着应该把数据表拆分为多个子表 拆分形成的数据表必须用外键关联起来 titleid title publname year authid titleid year authname ==>作者重复了
数据库设计范式（ ） 数据库设计范式（2）
第三范式： 第三范式：表不可再分
存储 空间 (b) 1
UNSI GNED 否 是 否 是 否
取值范围
tinyin t smalli nt medium int
-128~127 0~255 -32768~32767 0~65535 -8388608~8388607 0~16777215 2147483648~214748 3647 0~4294967295 92233720368547758 08 ~9223372036854775 807 0 ~1844674407370955 1615
2NF：行不可再分 ： 网上商店客户资料表
数据库设计范式实例（ ） 数据库设计范式实例（2）
3NF：表不可再分 ： 假设我们有一个商品订单表，包含了以下属性：订单号、客户 编号、单价、数量、总价。如下表：
数据库设计范式课堂练习
身份证上记录有：姓名、性别、民族、生日、住址和身份证号 码，试设计符合第一范式的数据结构。 如需要记录某高校学生的考试成绩以备档，要求信息详细到每 次考试的每门科目。 请理清符合第二范式的数据结构。 注意每门科目除成绩外还有学分数据需要记录 如需要为公司内部建立内部通讯录，记录每个员工的姓名、所 在部门、职位、办公地址、电话号码和电子信箱地址。 请理清符合第三范式的数据结构。
定长表（ ） 数据库设计优化 - 定长表（2）
使用定长列涉及某种折衷。它们更快，但占用的空间更多。 char(n) 类型列的每个值总要占用n 个字节(即使空串也是如 此)，因为在表中存储时，值的长度不够将在右边补空格； 而varchar(n)类型的列所占空间较少，因为只给它们分配存 储每个值所需要的空间，每个值再加一个字节用于记录其长 度。因此，如果在char和varchar类型之间进行选择，需要对 时间与空间作出折衷； 在设计表结构时如果能够使用定长数据类型尽量用定长的， 因为定长表的查询、检索、更新速度都很快。必要时可以把 部分关键的、承担频繁访问的表拆分，例如定长数据一个表 ，非定长数据一个表。例如Discuz!的cdb_members和 cdb_memberfields表、cdb_forums和cdb_forumfields表等。因 此规划数据结构时需要进行全局考虑
结构与索引（ ） 数据库设计优化 - 结构与索引（1）
索引能加快查询速度，而索引优化和查询优化是相辅相成的， 既可以依据查询对索引进行优化，也可以依据现有索引对查询 进行优化，这取决于修改查询或索引，哪个对现有产品架构和 效率的影响最小。 索引优化与查询优化是多年经验积累的结晶，在此无法详述， 但仍然给出几条最基本的准则。 首先，根据产品的实际运行和被访问情况，找出哪些SQL语句 是最常被执行的。最常被执行和最常出现在程序中是完全不同 的概念。最常被执行的SQL语句，又可被划分为对大表(数据条 目多的)和对小表(数据条目少的)的操作。无论大表或小表，有 可分为读(SELECT)多、写(UPDATE/INSERT)多或读写都多的 操作。
数据库设计思维与方法
减少数据存放空间 提高查询速度 思考？互联网的项目中，关注存放空间还是关注查询速度。 如何降低数据冗余度，减少数据存放空间，提高硬盘读写速度。 研究数据库设计的范式原理，进行分析应用 如何提高数据库查询响应，降低查询语句的复杂度，较快地设计、 开发，提高扩展性 分布设计，查询优化
数据库设计优化 - 运算与检索
数值运算一般比字符串运算更快 如果串列的值数目有限，应该利用普通整型或emum类型来获得 数值运算的优越性 更小的字段类型永远比更大的字段类型处理要快得多 对于字符串，其处理时间与串长度直接相关。一般情况下，较 小的表处理更快 对于定长表，应该选择最小的类型，只要能存储所需范围的值 即可。例如，如果mediumint够用，就不要选择bigint。对于可变 长类型，也仍然能够节省空间。一个TEXT 类型的值用2 字节记 录值的长度，而一个LONGTEXT 则用4字节记录其值的长度
2
3 是 否
int
4 是 否
bigint
Байду номын сангаас
8 是
定长表（ ） 数据库设计优化 - 定长表（1）
变长表：包含任何varchar、text等变长字段的数据表，即为变长 表，大部分数据表均为此类型 定长表：不包含任何变长字段的数据表，为定长表 对于变长表，由于记录大小不同，在其上进行许多删除和更改 将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保 持性能。而定长表就没有这个问题 如果表中有可变长的字段，将它们转换为定长字段能够改进性 能，因为定长记录易于处理
与主键没有直接关系的数据列必须消除（“消除”的办法是再创建一个数据表 来存放它们） titleid title publid year publid publname
数据库设计范式实例（ ） 数据库设计范式实例（1）
1NF：列不可再分 ： 在一个人力资源的数据库中，存储了主管及其下级之间的对应关系。为 了说明第一范式的规则，我们设定了这样的商业规则，即每个主管可以 有一个或多个下属，而每个下属只能有一个主管。
结构与索引（ ） 数据库设计优化 - 结构与索引（3）
而小表就相对简单，加入符合查询要求的特定索引，通常效果 比较明显。同时，定长化小表也有益于效率和负载能力的提高 。字段比较少的小定长表，甚至可以不需要索引。 其次，看SQL语句的条件和排序字段是否动态性很高(即根据不 同功能开关或属性，SQL查询条件和排序字段的变化很大的情 况)，动态性过高的SQL语句是无法通过索引进行优化的。惟一 的办法只有将数据缓存起来，定期更新，适用于结果对实效性 要求不高的场合。 MySQL索引，常用的有PRIMARY KEY、INDEX、UNIQUE几 种，详情请查阅MySQL文档。通常，在单表数据值不重复的情 况下，PRIMARY KEY和UNIQUE索引比INDEX更快，请酌情 使用。 事实上，索引是将条件查询、排序的读操作资源消耗，分布到 了写操作中，索引越多，耗费磁盘空间越大，写操作越慢。因 此，索引决不能盲目添加。对字段索引与否，最根本的出发点 ，依次仍然是SQL语句执行的概率、表的大小和写操作的频繁 程度。
三、数据库高级操作
数据库设计范式 数据库设计思维 数据库设计优化 MySQL查询优化 MySQL常用命令 MySQL建模工具
数据库设计范式（ ） 数据库设计范式（1）
第一范式： 第一范式：列不可再分 内容相似的数据列必须消除（“消除”的办法是再创建一个数据 表来存放它们） 必须为每一组相关数据分别创建一个数据表。 每条数据记录必须用一个主键来标识。 titleid title publname year authname1 authname3 ==>都是作者 第二范式： 第二范式：行不可再分
结构与索引（ ） 数据库设计优化 - 结构与索引（2）
对常被执行的SQL语句而言，对大表操作需要尤其注意： 语句而言，对大表操作需要尤其注意： 对常被执行的 语句而言 写操作多的，通常可使用写入缓存的方法，先将需要写或需 要更新的数据缓存至文件或其他表，定期对大表进行批量写 操作，例如Discuz!中点击数延迟更新机制，就是依据此原理 实现。同时，应尽量使得常被读写的大表为定长类型，即便 原本的结构中大表并非定长。大表定长化，可以通过改变数 据存储结构和数据读取方式，将一个大表拆成一个读多写少 的定长表，和一个读少写多的变长表来实现； 读操作多的，需要依据SQL查询频率设置专门针对高频SQL 语句的索引。