mysql数据库设计及优化_数据库设计及优化

数据库设计 ，可以使数据库通过健壮的数据库结构高效并且健康的进行工作。 数据库设计原则： 1、熟悉需求。2、开发符合规范的数据库。3、审核数据库设计。 数据库规范 1、命名规范： 1、表名的单数和复数形式要统一。2、对于字段，如果主键是数字类型的，可

数据库设计，可以使数据库通过健壮的数据库结构高效并且健康的进行工作。

数据库设计原则：1、熟悉需求。2、开发符合规范的数据库。3、审核数据库设计。

数据库规范

1、命名规范：1、表名的单数和复数形式要统一。2、对于字段，如果主键是数字类型的，可以考虑使用_N结尾，例如USERID_N；如果是字符类型_C结尾USERNAME_C;3、如果一个单词经常出现，比如USER，则可以考虑以_U结尾，例如，USERID可以表示成ID_U，USERNAME表示为NAME_U。4、如果某一列是日期类型，则使用D_开头，起到强调的作用，例如：D_CREATEDATETIME。

2、明确现实中实体与数据表的关系。3、一张数据表不能既没有主键也没有外键。4、表要符合基本表的特征。

5、必须满足第一第二范式，尽量满足第三范式。6、比较简洁的E-R图。7、符合完整性约束。

基本表的特征：1、原子性，就是基本表中的字段是不可在分解的。2、原始性，基本表中的记录是袁术数据的记录。3、演绎性，由基本表和关系表中的数据可以派生出任何想要的数据。4、稳定性、基本表中的结构是相对稳定的，表中的记录需要长期保存。

第一范式：是对属性的原子性约束，要求属性具有原子性，不可再分。第二范式:记录的唯一性约束，要求记录有唯一标识，即实体的唯一性。第三范式：对字段冗余的约束，即任何字段不能由其他字段派生出来，要求字段没有冗余，一个表中的字段除了和主键有关外，这些字段他们之间不能有关系，也就是说这个表中的字段只能和主键相关，他们之间没有关系。

数据库设计技巧：1、在符合系统需求的前提下，表的个数越少越好，一张表中组合主键字段数越少越好，一个表中的字段越少越好。2、检查各种变化字段。3、避免使用保留字。4、数据库设计的时候多使用删除标记字段。5、避免使用触发器。6、如果发现在重复输入数据，就需要创建新的表和新的关系。7、熟练使用数据库设计工具。8、创建E-R图和数据字典。9、每个表都应该有三个有用的字段：修改记录时间、修改人、修改后的版本。10、对于地址和电话采用多个字段。

SQL查询优化

劣质SQL判断条件：1、运行时间超长。2、引发严重的等待事件。3、不能满足压力测试。4、消耗大量系统资源。

索引是对数据库表中一列或者多列的值进行排序的一种结构。使用索引可以快速访问数据库表中的特定信息。

索引存在的问题：1、索引占用表空间，创建太多索引可能会造成索引冗余。2、索引影响DML性能。

索引使用的条件：1、一个字段里包含大量的值。2、一个字段包含多个空值。3、多个字段经常出现在where查询中。4、表非常大并且查询返回数据量低于总数据的20%。

创建索引的语法：

CREATE INDEX 索引名 ON 表名(列名)

TABLESPACE 表空间名

例如：

Select identity_L from depositor where identity_L between

109000 and 110000 --未使用索引，查询值在109000到110000之间的值

CREATE INDEX DPTOR_INDEX ON DEPOSITOR(IDENTITY_L)

TABLESPACE DEMO --使用索引查询。

索引类型，按列值是否唯一分为：非唯一索引和唯一索引。按索引列的个数分为单列索引和复合索引。按照索引的物理组织方式分为：B树索引、反向键索引、基于函数的索引、位图索引。

创建复合索引时，表指定的第一个列为主导列。

select *from depositor t where IDENTITY_L>100000 AND ACT>=500 --未使用索引

create index OP_IDACT_INDEX on DEPOSITOR(IDENTITY_L,ACT) tablespace DEMO --使用复合索引

使用复合索引的情况：1、当SQL语句的WHERE自居中用到复合索引的主导列时。2、当某几个列在SQL语句的WHERE子句中经常通过and操作符联合在一起使用，并且这些列合在一起是选择性比各自单个列的选择性要好时。

3、当有几个查询语句都是查询相同的几个列值时。

反向键索引就是将当前列中的值反转后生成的索引。

创建反向键索引的语法：create index索引名 on

表名(列名) reverse tablespace表空间;

基于函数的索引，基于一个或者多个列上的函数或表达式创建的索引。

注意：表达式中不能包含聚合函数(SUM、COUNT、AVG、MIN、MAX)。

基于函数索引的语法：CREATE INDEX索引名 ON

表名(函数(列名)) TABLESPACE表空间;

select lower(firstname) from depositor t; --没有使用基于函数索引的情况。

create index firstname_lower on depositor(lower(firstname)) tablespace prd;

使用基于函数的索引的情况：1、一个表中的字段经常被函数所调用，那么这个字段就可以使用基于函数的索引。

位图索引：

值\行

1

2

3

4

张三

1

0

0

1

李四

0

0

0

1

王五

0

1

0

1

小明

1

0

1

1

整个表就是针对一个列建立出来的位图索引，这个表中的列(1，2，3，4)表示的是对应的索引列的第几行。行代表的是当前行，当前被索引列的值。比如图中第一列1，张三第一列的值是1，表示在当前被索引的第一行的位置中张三这个值时存在的，并且是1。

使用位图索引的情况：主要是处理数据的聚合关系的，用于一个重复数据很多的情况。

位图索引的语法：

CREATE BITMAP INDEX 索引名 ON 表名(列名) TABLESPACE 表空间; select count(*) from depositor t where firstname=’Luke’;

create bitmap index fn_bitmap on depositor(firstname) tablespace prd;

表分区：有利于管理非常大的表和索引。当表里面的内容或者整个表的结构非常庞大的时候，就考虑表分区。

表分区的优点：1、提高数据的可用性，当某个分区损坏不会影响整个表结构。2、减少管理负担。3、改善语句性能。

表分区的分区方式：1、区间分区。2、散列分区。3、列表分区。4、组合分区。

区间分区：往往根据一个列值的范围来划分，开发中常常使用日期字段来划分。

语法：

PARTITIONBY RANGE(列名) --RANGE表示分区的方式

(

PARTITION 分区表名 VALUES LESS THAN (值)

tablespace 表空间

)

示例：PART_1这个分区保存早于(不包括)2008年12月31日的数据

create table drawlist( dt_draw date not null)

PARTITIONBY RANGE(dt_draw)

(

PARTITION PART_1 VALUES LESSTHAN(to_date(‘1/1/2009’,’dd/mm/yyyy’))

tablespace DEMO,

PARTITION PART_1 VALUES LESSTHAN(to_date(‘1/1/2011’,’dd/mm/yyyy’))

tablespace DEMO,

PARTITION PART_1 VALUES LESSTHAN(to_date(MAXVALUE)

tablespace DEMO

)

insert into drawlist values(to_date(‘31/12/2008’,’dd/mm/yyyy’));

insert into drawlist values(to_date(‘01/01/2009’,’dd/mm/yyyy’));

insert into drawlist values(to_date(‘06/06/2009’,’dd/mm/yyyy’));

insert into drawlist values(to_date(‘31/12/2010’,’dd/mm/yyyy’));

insert into drawlist values(to_date(‘01/01/2011’,’dd/mm/yyyy’));

insert into drawlist values(to_date(‘04/05/2011’,’dd/mm/yyyy’));

select *from drawlist partition(part_2); 显示结果：2009-1-1；2009-6-6；2010-12-31；

散列分区：会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区中。直到散列算法是将数据随机均匀的分布。为了使数据分布的更均匀，分区表的数目建议为2的幂，也就是1、4、8、16……

散列分区语法：

PARTITION BY HASH(列)(PARTITION 分区表名 tablespace表空间)

createtable hash_table (hash_no INTEGER not null )

PARTITIONBY HASH(hash_no)

(

PARTITION PART_1 tablespace DEMO,

PARTITION PART_2 tablespace DEMO,

PARTITION PART_3 tablespace DEMO,

PARTITION PART_4 tablespace DEMO,

)

对其随机插入500条测试语句

selectcount(*) from hash_table partition(part_1)

selectcount(*) from hash_table partition(part_2)

selectcount(*) from hash_table partition(part_3)

selectcount(*) from hash_table partition(part_4)

输出结果基本均匀分布，分别为：123、116、121、140。

列表分区：可以将数据按照列的值分类。列表分区语法如下：

PARTITIONBY LIST(列)

(

PARTITION 分区表名values(指定值1，指定值2……)

)

示例：根据用户所在地区将用户存储在不同分区中

create table area( code INTEGERnot null )

PARTITION BY LIST(code)

(

PARTITIONPART_1 values(102200,102202,102203),

PARTITIONPART_2 values(164300,164302,164303)

)

tablespace DEMO;

insert into area values(102200);

insert into area values(102203);

insert into area values(164300);

insert into area values(164303);

select * from area partition(part_1)

select * from area partition(part_2)

查询结果：在part_1分区中数据为102200、102202；part_2分区中数据位164300、164303；

组合分区：就是区间分区与散列分区或者区间分区与列表分区的组合。

区间-散列组合分区表语法：首先按照列1的值进行区间分区，然后按照列2散列分区。

PARTITION BY RANGE(列1) SUBPARTITION BY HASH(列2)(

PARTITION分区名 VALUES LESS THAN (值)

TABLESPACE表空间,

)

区间-列表组合分区表语法：先按照列1的值进行区间分区，然后按照列2指定值进行列表分区

PARTITION BY RANGE(列1) SUBPARTITION BY LIST(列2)(

PARTITION 分区名 VALUES LESS THAN (值) TABLESPACE 表空间

(

SUBPARTITION 子分区名 VALUES(列表指定值1……) TABLESPACE 表空间

)

)

组合分区示例：

select *from range_list partition(part_2);

返回值为：DT_DATE 2009-1-1,2010-12-31，2010-11-21;CODE 102200，164300，164300;

select *from range_list subpartition(part2_list);

结果为:DT_DATE 2009-1-1;CODE102200;

分区维护：增加分区、删除分区、截断分区、合并分区。

增加分区的语法：

ALTER TABLE 表名 ADD PARTITION 分区表名 VALUES LESS THAN(值)

例如：

ALTERTABLE DRAWLIST ADD PARTITION PART_4 VALUES LESS

THAN(TO_DATE(‘1/1/2012’,’DD/MM/YYYY’)) TABLESPACE DEMO;

删除分区语法：

ALTER TABLE 表名 DROP PARTITION 分区表名

例如：

ALTER TABLE DRAWLIST DROPPARTITION PART_3;

截断分区是删除当前分区中的数据，但不删除当前分区，也不影响其他分区。

语法：

ALTER TABLE 表名 TRUNCATE PARTITION 已存在的分区表

如：

ALTER TABLE DRAWLI ST TRUNCATEPARTITION PART_1;

合并分区是将两个分区的数据合并成一个分区里，注意：高界限的分区不能合并到低界限的分区中。

合并分区的语法：

ALTER TABLE 表名 MERGE PARTITIONS 分区表1，分区表2 INTO PARTITION 分区表2

例如：

ALTER TABLE drawlist MERGE PARTITIONS part_2,part_4 INTO PARTITION part_4;

SQL优化途径：选择合适的Oracle优化器、选择恰当的扫描方式、善于利用共享的SQL语句。

1、选择合适的优化器。CBO优化器：基于成本的优化器，这个成本是指CPU和内存占用率。

在编写SQL时，可以使用CBO对SQL进行优化从而获得更快的响应速度。

优化器使用的模式根据目标而定，CBO的优化模式有四种分别为：1、Rule：基于规则。2、Choose：默认，表或者索引有统计信息，走CBO模式，否则，走RBO模式。3、FirstRow：表中有统计信息时，以最快方式返回查询的前几行，总体减少响应时间，4、AllRows：表中有统计信息时，以最快的方式返回表的所有行，总体提高查询吞吐量。

1、

选择合适的扫描方式。Oracle中，查询数据可以分为：全表扫描、使用ROWID、索引全扫描、快速索引扫描。

全表扫描：就是Oracle读取表中所有的行，并检查每一行是否满足WHERE语句的限制。在数据量大的表中不建议使用全表扫描，效率低下。

使用ROWID：ROWID指出该行所在的数据文件、数据块以及行在该块的位置。是Oracle存取单行数据最快的方式。

索引全扫描：只在CBO模式下有效，这种方式查询的数据必须可以从索引中直接得到。

快速索引扫描：它会扫描索引中所有的数据块，与全索引扫描类似，但这种方式不会对查询出的数据进行排序，这种方式会获得最大吞吐量，并且缩短执行时间。

3、学会利用共享的SQL语句。它存在Oracle的系统全局区中，也就是SGA，可以通过设置SGA的大小，来提高sql的执行效率。

本文原创发布php中文网，转载请注明出处，感谢您的尊重！