「mysql优化专题」优化之路高级进阶——表的设计及优化(6)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/zlc3323/article/details/80836896

前一篇详细讲解了索引优化(面试重点),相信大家都有收获,没看过的可以再看看。本篇则讲解表的设计及其优化,喜欢的朋友收藏关注。共同学习。

正文:表的设计及优化

优化①:创建规范化表,消除数据冗余

数据库范式是确保数据库结构合理,满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表,称为规范化表,范式产生于20世纪70年代初,一般表设计满足前三范式就可以,在这里简单介绍一下前三范式。

通俗的给大家解释一下(可能不是最科学、最准确的理解)

第一范式:属性(字段)的原子性约束,要求属性具有原子性,不可再分割;

第二范式:记录的惟一性约束,要求记录有惟一标识,每条记录需要有一个属性来做为实体的唯一标识。

第三范式:属性(字段)冗余性的约束,即任何字段不能由其他字段派生出来,在通俗点就是:主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们,当然外键除外)

当然,其实我们经常打破第三范式。。。且不可避免的,其实就是要在数据冗余和处理速度之间找到合适的平衡点 。

优化②:合适的字段属性

先举个例子:

以前我做过的电商项目中,关于资金流水类型的字段的选取。本来资金流水类型总共就那么十几种,基本固定死的,那我们就可以选择tinyint(4)就完全足够了,对应的是java的byte。 (要知道的是,tinyint的长度就是8位,tinyint(1)和tinyint(4)只是显示长度)

下面以下给出几个字段的建议:

0)数值型字段的比较比字符串的比较效率高得多,所以字段类型尽量使用最小、最简单的数据类型。如IP地址可以使用int类型,如我上面的例子。

1)建议不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。

2)对于整数的存储,在数据量较大的情况下,建议区分开 TINYINT / INT / BIGINT 的选择(当然,那已经是很老的事情了,现在其实不差这点性能)

3)char是固定长度,所以它的处理速度比varchar快得多,但缺点是浪费存储空间,不能在行尾保存空格。在MySQL中,MyISAM建议使用固定长度代替可变长度列;InnoDB建议使用varchar类型,因为在InnoDB中,内部行存储格式没有区分固定长度和可变长度。

4) 尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。

5)text与blob区别:blob保存二进制数据;text保存字符数据,有字符集。text和blob不能有默认值。

实际场景:text与blob主要区别是text用来保存字符数据(如文章,日记等),blob用来保存二进制数据(如照片等)。blob与text在执行了大量删除操作时候,有性能问题(产生大量的“空洞“),为提高性能建议定期optimize table 对这类表进行碎片整理。

6) 自增字段要慎用,不利于数据迁移

7)强烈反对在数据库中存放 LOB 类型数据,虽然数据库提供了这样的功能,但这不是他所擅长的,我们更应该让合适的工具做他擅长的事情,才能将其发挥到极致。(反正我么碰到过LOB类型数据)

8)尽量将表字段定义为NOT NULL约束,这时由于在MySQL中含有空值的列很难进行查询优化,NULL值会使索引以及索引的统计信息变得很复杂,可以使用0或者空字符串来代替。

9)尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半,且日期类型中只有它能够和实际时区相对应。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。(真的是技术文,欢迎补充)

优化③:索引

索引是一个表优化的重要指标,在表优化中占有极其重要的成分,所以上篇索引优化详解没看过的可以先看看,这里不再赘叙。

优化④:表的拆分(大表拆小表)

1、垂直拆分(其实就是列的拆分将原来的一个有很多列的表拆分成多张表)

注意:垂直拆分应该在数据表设计之初就执行的步骤,然后查询的时候用jion关键起来即可;

通常我们按以下原则进行垂直拆分:

把不常用的字段单独放在一张表;

把text,blob等大字段拆分出来放在附表中;

经常组合查询的列放在一张表中;

缺点也很明显,需要使用冗余字段,而且需要join操作。

2、水平拆分( 如果你发现某个表的记录太多,例如超过一千万条,则要对该表进行水平分割。水平分割的做法是,以该表主键的某个值为界线,将该表的记录水平分割为两个表。)

4624953-cbb5129a61bfdccb

当然,我们还可以用增量法。如流水这类不会改变的数据,我们用增量查询。

1.创建一张日充值表,记录每天充值总额

2.每天用定时器对当前充值记录进行结算

3.创建每月充值表,每月最后一天用定时器计算总额

4.则要查询总额,则从月报表中汇总,再从日报表查询当天之前的数据汇总,再加上今天的使用当天流水表记录今天的流水,三张表加起来,汇总。这样子效率是极好的!

优化⑤:传说中的‘三少原则’

①:数据库的表越少越好

②:表的字段越少越好

③:字段中的组合主键、组合索引越少越好

当然这里的少是相对的,是减少数据冗余的重要设计理念。

今天,表的设计及优化就讲到这里,重点是表的拆分(加分项)。觉得有收获的同学可以收藏关注。

展开阅读全文
博主设置当前文章不允许评论。

sql优化优化

03-08

执行计划输出:rn[code=SQL]rnSELECT STATEMENT, GOAL = CHOOSE 12 1 189rn SORT ORDER BY 12 1 189rn NESTED LOOPS 9 1 189rn NESTED LOOPS 9 1 185rn HASH JOIN SEMI 8 1 32rn TABLE ACCESS FULL NEWDOC T_INHOSPITAL_ACTION 2 218 4142rn VIEW SYS VW_NSO_1 5 274 3562rn SORT GROUP BY 5 274 4658rn TABLE ACCESS FULL NEWDOC T_INHOSPITAL_ACTION 2 274 4658rn TABLE ACCESS BY INDEX ROWID NEWDOC T_IN_PATIENT 1 1 153rn INDEX UNIQUE SCAN NEWDOC PK_T_IN_PATIENT 58 rn INDEX UNIQUE SCAN NEWDOC PK_T_SICKBEDINFO 750 3000rn[/code]rnrn以下是sql部分rn[code=SQL]select rna.id,a.patient_name,a.gender_code,a.birthday,a.pid,a.age,a.sick_doctor_id, a.sick_doctor_name,a.sick_area_id,a.sick_area_name,a.section_id, a.section_name,a.in_time,a.state,a.sick_bed_id,a.sick_bed_no,a.age_unit, a.Sick_Degree,rna.NURSE_LEVEL,a.insection_id,a.insection_name,a.in_area_id, a.in_area_name,a.Die_flag,a.Sick_Group_Id,Marriage_State,Medicare_NO,Home_Address, Homepostal_Code,Home_Phone,Office,Office_Address,Office_Phone,Career,Relation_Name,Relation,rn Relation_Address, Relation_Phone,RelationPOS_Code,OfficePOS_Code,InHospital_Count,Birth_Place,Folk_Code, CERT_ID,Pay_Manner,IN_Circs,native_place,a.card_id,a.patient_Id rnfrom t_in_patient a rninner join t_inhospital_action b rnon a.id=b.patient_Idrninner join t_sickbedinfo crnon a.sick_bed_id=c.bed_id rnwhere a.section_id=5251 and b.action_state=4 and rnb.id in (select max(id) from t_inhospital_action group by patient_Id) rnorder by cast(sick_bed_id as number)rn[/code]rnrn2个关联的表有索引了。查询数据只有13行,速度比较慢。 论坛

没有更多推荐了,返回首页