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喜好简朴（好恰、我也是）
最好防备运用bit
运用小而简朴的适宜范例；
只管运用整型定义标识列
防备过分设想，比方会致使极庞杂查询的schema设想，或许多列；
应当只管防备运用null值，除非实在数据模型中有确实须要
只管运用雷同的范例存储类似、相干的值，特别是关联前提中运用的列
注重可变长字符串，其在暂时表和排序时能够致使消极的按max长度分派内存
防备运用抛弃的特征，如指定浮点数的精度，或整数的显现宽度
警惕运用enum和set，虽然他们用起来很轻易，但不要滥用，偶然会变圈套
范式是好的，但反范式偶然也是必要的；预先盘算、缓存或生成汇总表也可获很大优点
alter table 大部分状况会锁表且重修整张表（让人痛楚）本章供应了一些有风险的要领，大部分场景必需运用其他更通例的要领
相干文章：
【MySQL数据库】第三章解读：效劳器机能理会（上）
【MySQL数据库】第三章解读：效劳器机能理会 （下）
以上就是【MySQL数据库】第四章解读：Schema与数据范例优化（下）的细致内容，更多请关注ki4网别的相干文章！
