旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

【MySQL数据库】第四章解读:Schema与数据类型优化(下)【MySQL教程】,MySQL

作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:58评论:0


导读:4.2MySQLschema设想中的圈套由于mysql完成机制致使了一些特定毛病,怎样防备,逐步道来:1、太多的列MySQL存储引擎api事情时须要在效劳器层和存...

4.2MySQL schema设想中的圈套

由于mysql完成机制致使了一些特定毛病,怎样防备,逐步道来:

1、太多的列

MySQL存储引擎api事情时须要在效劳器层和存储引擎层经由历程行缓冲花样拷贝数据,然后在效劳器层将缓冲内容解码成各个列,从行缓冲中将编码过的列转换成行数据的操纵价值高,myisam定长行与效劳器行组织恰好婚配,不须要转换;然则变长行组织 InnoDB的行组织老是须要转换,转换价值依赖于列的数目。

2、太多的关联

实体-属性-值EAV:蹩脚的设想形式,mysql限定了每一个关联操纵最多只能有61张表,但EAV数据库需许多自关联;一个大略的履历轨则,假如愿望查询实行得疾速且并发性好,单个查询最幸亏12个表内做关联

3、防备过分运用罗列

注重防备过分运用罗列;运用外键关联到字典表或查找表查找细致的值,在mysql中,须要在罗列列表中添加值时,要做一次alter table;MySQL5.0更早alter table壅塞操纵,5.1更新版本中,不是在列表末端增添值也会一样须要alter table

4、非此发现not invent here的null

发起存空值能够用0、特别值、空字符串替代,只管不要null;然则不要走极端,在某些场景下、运用null会更好:

create table ……(
//全0 (不能够的日期)会致使许多题目
    dt datetime not null default '0000-00-00 00:00:00'
    ……
)

MySQL会在索引中存储null值,Oracle不会

4.3范式与反范式

4.3.1优瑕玷

1、范式化的更新操纵更快

2、当数据较好地范式化时,很少有反复数据,只须要修正更少的数据

3、范式化的表更小,可更好地放到内存里,实行操纵更快

4、很少冗余数据,检索列表数据时更少须要distinct、group by语句

瑕玷:

须要关联,有价值且能够使索引无效

4.3.2反范式的长处和瑕玷

防备关联,数据比内存大能够比关联要快许多(防备了随机I/O)

4.4缓存表和汇总表

缓存表:

对优化搜刮和检索查询语句很有效,

存储那些能够较简朴地从其他表猎取数据(每次猎取速率比较慢)的表

汇总表:保留运用group by语句聚合数据的表

运用时决定是及时保护数据照样按期重修,按期重修:节约资本、碎片少、递次组织的索引(高效)

重修时,保证数据在操纵时依旧可用,经由历程“影子表”来完成,影子表:一张在实在表背地建立的表,在完成建表操纵后,可经由历程原子的重命名操纵切换影子表和原表

4.4.1物化视图

预先盘算并存在磁盘上的表,可经由历程种种战略革新和更新,mysql不原生支撑,可运用Justin Swanhart东西flexviews完成:

flexviews构成:

  • 变动数据抓取,读取效劳器二进制日记且理会相干行的变动

  • 一系列能够协助 建立和治理 视图 的定义 的 存储历程

  • 一些可应用变动到 数据库中的物化视图 的东西

flexviews经由历程提取对源表的变动,可增量地从新盘算物化视图的内容:不须要查询原始数据(高效)

4.4.2计数器表

计数器表:缓存一个用户朋侪数、文件下载次数等,引荐建立一张自力的表存储计数器,防备查询缓存失效;

更新加事件,只能串行实行,为了更高的并发性,可将计数器保留在多行,每次随机选一行更新,要统计效果时,聚合查询;(这个我读了两三边,能够比较笨吧,就是同一个计数器保留多分,每次选个中一个更新,末了乞降,彷佛还不是很好明白哈,多读几遍吧)

4.5加快alter table 操纵的速率

mysql大部分修正表组织是:用新的效果建立空表、从旧表中查出all数据插进去新表,删除旧表

mysql5.1及更新包含一些范例的“在线”操纵的支撑,全部历程不须要全锁表,最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支撑经由历程排序来建索引,建索引更快且紧凑的规划;

一般而言,大部分alter table致使mysql效劳中断,对罕见场景,运用的技能

1、先在一台不供应效劳的机械上实行alter table操纵,然后和提取效劳的主库举行切换

2、影子拷贝,用请求的表组织建立张和源表无关的新表,经由历程重命名、删表交流两张表(上有)

不是all的alter table都引发表重修,理论上可跳过建立表的步骤:列默认值实际上存在表的.frm文件中,so可直接修正这个文件不须要修改表自身,但mysql还没有采纳这类优化要领,all的modify column将致使表重修;

alter column:通frm文件转变列默认值:alter table允许运用alter column、modify column change column修正列,三种操纵不一样;

alter table sakila.film alter column rental_duration set default 5;

4.5.1只修正frm文件

mysql偶然在没有必要的时刻也重修表,假如愿冒一些风险,可做些其他范例的修正而不必重修表:下面操纵能够不能一般事情,先备份数据

下面操纵不须要重修表:

1、移除一个列的auto_increment

2、增添、移除、变动enum和set常量,假如移除的是被用到的常量、查询返回空字符串

基础手艺为想要的表效果建立新的frm文件,然后用它替换掉已存在的那张表的frm文件:

1、建立一张有雷同组织的空表,举行所需的修正

2、实行flush tables with read lock:封闭all正在运用的表且制止任何表被翻开

3、交流frm文件

4、实行unlock tables开释第2步的读锁

示例略

4.5.2疾速建立myISAM索引

1、为高效地载入数据到MyISAM表,经常使用技能:先禁用索引、载入数据、重启索引:由于构建索引的事情延晚到数据载入后,此时可经由历程排序构建索引,快且使得索引树的碎片更少、更紧凑

然则对唯一索引无效(disable keys),myisam会在内存中组织唯一索引且为载入的每一行搜检唯一性,一旦索引大小凌驾有效内存、载入操纵会越来越慢;

2、在现代版InnoDB中,有个类似技能:先删除all非唯一索引,然后增添新的列,末了重修删撤除的索引(依赖于innodb疾速在线索引建立功用)Percona server可自动完成这些操纵;

3、像前alter table 的骇客要领来加快这个操纵,但需多做些事情且负担风险,这对从备份中载入数据很有效,如already know all data is effective ,and no need to do the unique check

  • 用须要的表组织建立一张表,不包含索引(如用load data file 且载入的表是空的,myisam可排序建索引)

  • 载入数据到表中以构建MYD文件

  • 按须要的组织建立别的一张空表,这次要包含索引,会建立.frm .MYI文件

  • 获读锁并革新表

  • 重命名第二张表的frm文件 MYI,让mysql以为这是第一张表的文件

  • 开释读锁

  • 运用repair table来重修表的索引,该操纵会经由历程排序来构建all索引、包含唯一索引

4.6总结

优越的schema设想原则是一般运用的,但mysql有本身的完成细节要注重,归纳综合来讲:只管坚持任何东西小而简朴老是好的;mysql喜好简朴(好恰、我也是)

  1. 最好防备运用bit

  2. 运用小而简朴的适宜范例;

  3. 只管运用整型定义标识列

  4. 防备过分设想,比方会致使极庞杂查询的schema设想,或许多列;

  5. 应当只管防备运用null值,除非实在数据模型中有确实须要

  6. 只管运用雷同的范例存储类似、相干的值,特别是关联前提中运用的列

  7. 注重可变长字符串,其在暂时表和排序时能够致使消极的按max长度分派内存

  8. 防备运用抛弃的特征,如指定浮点数的精度,或整数的显现宽度

  9. 警惕运用enum和set,虽然他们用起来很轻易,但不要滥用,偶然会变圈套

  10. 范式是好的,但反范式偶然也是必要的;预先盘算、缓存或生成汇总表也可获很大优点

  11. alter table 大部分状况会锁表且重修整张表(让人痛楚)本章供应了一些有风险的要领,大部分场景必需运用其他更通例的要领

相干文章:

【MySQL数据库】第三章解读:效劳器机能理会(上)

【MySQL数据库】第三章解读:效劳器机能理会 (下)

以上就是【MySQL数据库】第四章解读:Schema与数据范例优化(下)的细致内容,更多请关注ki4网别的相干文章!

标签:MySQL


欢迎 发表评论: