旅游类网站模板免费下载,wordpress阅读量没改,百度seo培训公司,网站题目有哪些前面讲了SQL优化以及索引的使用、设计优化了#xff0c;那么接下来就到表的设计与优化啦#xff01;#xff01;#xff01;真实地去设计优化单表结构以及讲述多表设计基本原则(结合真实的生产环境的取舍来讲述)。文章结构#xff1a;(1)单表设计与优化#xff1b;(2)基于…前面讲了SQL优化以及索引的使用、设计优化了那么接下来就到表的设计与优化啦真实地去设计优化单表结构以及讲述多表设计基本原则(结合真实的生产环境的取舍来讲述)。文章结构(1)单表设计与优化(2)基于单表设计的多表设计原则(含表拆分原则)(均以实际生产开发环境下的环境为基准)文章目录(1)单表设计与优化设计规范化表消除数据冗余(以使用正确字段类型最明显)- 前三范式- 所有字段类型- 所有字段类型罗列- 针对常用的varchar我们来思考几个问题- 给出几个类型选取建议适当的冗余增加计算列(实际开发中必须思考的点)索引的设计主键和外键的必要性(实际项目开发的重要取舍)存储过程、视图、函数的适当使用(这些是优化的方法这几个后面会讲)传说中的‘三少原则’分割你的表减小表尺寸字段设计原则(2)基于单表设计的多表设计原则表关系- 一对一关系- 一对多关系(多对一)- 多对多关系- 注意- 外键与索引- 建立关系分表原则(涉及分区分表问题探究以后的篇章再补充实例)- 表拆分方式- 垂直切分- 水平拆分(分表分区)–按表中某一字段值的范围划分- 散列库表(基于hash算法的切分)- 在了解完分表了我们先来理解区分分区与分表吧- 表拆分建议(针对大系统)一、单表设计与优化(1)设计规范化表消除数据冗余(以使用正确字段类型最明显)数据库范式是确保数据库结构合理满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表称为规范化表范式产生于20世纪70年代初一般表设计满足前三范式就可以在这里简单介绍一下前三范式。第一范式(1NF)无重复的列所谓第一范式(1NF)是指在关系模型中对域添加的一个规范要求所有的域都应该是原子性的即数据库表的每一列都是不可分割的原子数据项而不能是集合数组记录等非原子数据项。第二范式(2NF)属性在1NF的基础上非码属性必须完全依赖于码[在1NF基础上消除非主属性对主码的部分函数依赖]第三范式(3NF)属性在1NF基础上任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖。通俗点讲第一范式属性(字段)的原子性约束要求属性具有原子性不可再分割第二范式记录的惟一性约束要求记录有惟一标识每条记录需要有一个属性来做为实体的唯一标识即每列都要和主键相关。第三范式属性(字段)冗余性的约束即任何字段不能由其他字段派生出来在通俗点就是主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们当然外键除外)。即确保每列都和主键列直接相关,而不是间接相关。如果数据库设计达到了完全的标准化则把所有的表通过关键字连接在一起时不会出现任何数据的复本(repetition)。标准化的优点是明显的它避免了数据冗余自然就节省了空间也对数据的一致性(consistency)提供了根本的保障杜绝了数据不一致的现象同时也提高了效率。尤其是正确字段类型的选择(先列出所有字段类型再写建议)所有字段类型(一)整型数值整数类型字节数最小值 ~ 最大值tinyint1-128~127 或 0-255smallint2-32768~32767 或 0~65535mediumint3-8388608~8388607 或 0~1677215int4-2147483648~2147483647 或 0~4294967295bigint8-9223372036854775808~9223372036854775807 或 0~18446744073709551615(二)浮点数类型浮点数类型字节数最小值 ~ 最大值double4±1.175494351E-38 ~ ± 3.402823466E38double8±2.2250738585072014E-308 ~ ±1.7976931348623157E308(三)定点数类型定点数类型字节数最小值 ~ 最大值dec(m,d)m2最大取值范围与double相同给定decimal的有效值取值范围由m和d决定关于浮点数与定点数有点看法浮点数相对于定点数的优点是在长度一定的情况下浮点数能够表示更大的数据范围它的缺点是会引起精度问题。使用时我们要注意1. 浮点数存在误差问题2. 对货币等对精度敏感的数据应该用定点数表示或存储3. 编程中如果用到浮点数要特别注意误差问题并尽量避免做浮点数比较4. 要注意浮点数中一些特殊值的处理。(四)位类型位类型字节数最小值 ~ 最大值bit(m)1~8bit(1) ~ bit(64)(五)日期时间类型时间日期类型字节数最小值 ~ 最大值date41000-01-01 ~ 9999-12-31datetime81000-01-01 00:00:00 ~ 9999-12-31 23:59:59timestamp419700101080001 ~ 2038年某个时刻time3-838:59:59 ~ 838:59:59year11901 ~ 2155mysql中用now()写入当前时间。(六)字符串类型字符串类型字节数取值范围char(m)mm为0 ~ 255之间的整数varchar(m)值长度1m为0~65535之间的整数tinytext值长度2允许长度0~255字节text值长度2允许长度0~65535字节mediumtext值长度3允许长度0~167772150字节longtext值长度3允许长度0~4294967295字节binary(m)m允许0~m个字节定长的字符串varbinary(m)值长度1允许0~m个字节变长的字符串tinyblob值长度1允许长度0~255字节blob值长度2允许长度0~65535字节mediumblob值长度3允许长度0~167772150字节longblob值长度4允许长度0~4294967295字节enum1或21255个成员需要1个字节存25565535个成员2个字节存set1/2/3/4/8类似enum,set一次可以选取多个成员而enum只能一个针对常用的varchar我们来思考几个问题1)varchar的长度MySQL的文档其中对varchar字段类型这样描述varchar(m) 变长字符串。m 表示最大列长度。m的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定最大有效长度是65,532字节)。mysql varchar(50) 不管中文 还是英文 都是存50个的但是一个表中所有varchar字段的总长度跟编码有关如果是utf-8那么大概65535/3如果是gbk那么大概65535/2.2)存储限制编码长度限制行长度限制超出了会变成怎样针对第一个问题varchar 字段是将实际内容单独存储在聚簇索引之外实际存储从第二个字节开始接着要用1到2个字节表示实际长度(长度超过255时需要2个字节)因此最大长度不能超过65535。针对第二个问题字符类型若为gbk每个字符最多占2个字节。字符类型若为utf8每个字符最多占3个字节。针对第三个问题导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值则提示ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。针对第四个问题若定义的时候超过上述限制则varchar字段会被强行转为text类型并产生warning。3)与char的对比CHAR(M)定义的列的长度为固定的M取值可以为0255之间当保存CHAR值时在它们的右边填充空格以达到指定的长度。当检 索到CHAR值时尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便CHAR字段上的索引效率级高比如定义 char(10)那么不论你存储的数据是否达到了10个字节都要占去10个字节的空间,不足的自动用空格填充。CHAR和VARCHAR最大的不同就是一个是固定长度一个是可变长度。由于是可变长度因此实际存储的时候是实际字符串再加上一个记录 字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或VARCHAR列的值超过列的最大长度则对值进行裁剪以使其适合。如果被裁掉 的字符不是空格则会产生一条警告。如果裁剪非空格字符则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。4)char、varchar与text的建议TEXT只能储存纯文本文件。效率来说基本是charvarchartext但是如果使用的是Innodb引擎的话推荐使用varchar代替charchar和varchar可以有默认值text不能指定默认值以下给出几个类型选取建议(一)数字类型1)不到不要使用DOUBLE不仅仅只是存储长度的问题同时还会存在精确性的问题。2)固定精度的小数也不建议使用DECIMAL建议乘以固定倍数转换成整数存储可以大大节省存储空间且不会带来任何附加维护成本。3)对于整数的存储在数据量较大的情况下建议区分开 TINYINT / INT / BIGINT 的选择因为三者所占用的存储空间也有很大的差别能确定不会使用负数的字段建议添加unsigned定义。当然如果数据量较小的数据库也可以不用严格区分三个整数类型。4)对于整型数值mysql支持在类型名称后面的小括号内指定显示宽度例如int(5)表示当数值宽度小于5位时候在数值前面填满宽度一般配合zerofill属性使用。如果一个列指定为zerofill,则MySQL自动为该列添加unsigned属性。5)在数据量较大时、建议把实数类型转为整数类型。原因很简单1. 浮点不精确2.定点计算代价昂贵。例如要存放财务数据精确到万分之一、则可以把所有金额乘以一百万、然后存在BIGINT下。(二)字符类型1)尽量不要使用 TEXT 数据类型其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段建议使用 CHAR 类型不定长字段尽量使用 VARCHAR且仅仅设定适当的最大长度而不是非常随意的给一个很大的最大长度限定因为不同的长度范围MySQL也会有不一样的存储处理。2)char会删除字符串尾部的空格varchar不会varchar向前补1-2字节char定长。binary类似于char,binary只能保存二进制字符串。char是固定长度所以它的处理速度比varchar快得多但缺点是浪费存储空间不能在行尾保存空格。在MySQL中MyISAM建议使用固定长度代替可变长度列InnoDB建议使用varchar类型因为在InnoDB中内部行存储格式没有区分固定长度和可变长度。3)enum类型忽略大小写。4)text与blob区别blob保存二进制数据text保存字符数据有字符集。text和blob不能有默认值。应用text与blob主要区别是text用来保存字符数据(如文章日记等)blob用来保存二进制数据(如照片等)。blob与text在执行了大量删除操作时候有性能问题(产生大量的“空洞“)为提高性能建议定期optimize table 对这类表进行碎片整理。关于text与blob我们有些看法建议1. BLOB和TEXT值也会引起自己的一些问题特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的空洞以后填入这些空洞的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理.2. 在不必要的时候避免检索大型的BLOB或TEXT值。3. 把BLOB或TEXT列分离到单独的表中。在某些环境中如果把这些数据列移动到第二张数据表中可以让你把原数据表中 的数据列转换为固定长度的数据行格式那么它就是有意义的。这会减少主表中的碎片使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。(三)时间类型1)尽量使用TIMESTAMP类型因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型建议使用DATE类型因为他的存储空间只需要3个字节比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值因为这太不直观会给维护带来不必要的麻烦同时还不会带来任何好处。2)根据实际需要选择能够满足应用的最小存储日期类型。3)timestamp日期类型中只有它能够和实际时区相对应。(四)ENUM SET对于状态字段可以尝试使用 ENUM 来存放因为可以极大的降低存储空间而且即使需要增加新的类型只要增加于末尾修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢可以尝试使用SET类型即使存在多种属性同样可以游刃有余同时还可以节省不小的存储空间。(五)LOB类型强烈反对在数据库中存放 LOB 类型数据虽然数据库提供了这样的功能但这不是他所擅长的我们更应该让合适的工具做他擅长的事情才能将其发挥到极致。(2)适当的冗余增加计算列(实际开发中必须思考的点)数据库设计的实用原则是在数据冗余和处理速度之间找到合适的平衡点。满足范式的表一定是规范化的表但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率常常需要降低范式标准适当增加冗余达到以空间换时间的目的。比如我们有一个表产品名称单价库存量总价值。这个表是不满足第三范式的因为“总价值”可以由“单价”乘以“数量”得到说明“金额”是冗余字段。但是增加“总价值”这个冗余字段可以提高查询统计的速度这就是以空间换时间的作法。合理的冗余可以分散数据量大的表的并发压力也可以加快特殊查询的速度冗余字段可以有效减少数据库表的连接提高效率。其中总价值就是一个计算列在数据库中有两种类型数据列和计算列数据列就是需要我们手动或者程序给予赋值的列计算列是源于表中其他的数据计算得来比如这里的总价值在SQL中创建计算列create table goods(id int auto_increment not null,c1 int,c2 int,c3 int as (c1c2), //这个就是计算列啦primary key(id))(3)索引的设计表优化的重要途径百万级别的表没有索引注定卡死。(4)主键和外键的必要性(实际项目开发的重要取舍)概述主键与外键的设计在全局数据库的设计中占有重要地位。 因为主键是实体的抽象主键与外键的配对表示实体之间的连接。主键根据第二范式需要有一个字段去标识这条记录主键无疑是最好的标识但是很多表也不一定需要主键但是对于数据量大查询频繁的数据库表一定要有主键主键可以增加效率、防止重复等优点。主键的选择也比较重要一般选择总的长度小的键小的键的比较速度快同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序对于组合主键来说不同的字段次序的主键的性能差别可能会很大一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。外键外键作为数据库对象很多人认为麻烦而不用实际上外键在大部分情况下是很有用的理由是外键是最高效的一致性维护方法。数据库的一致性要求依次可以用外键、CHECK约束、规则约束、触发器、客户端程序一般认为离数据越近的方法效率越高。但是要谨慎使用级联删除和级联更新因为级联删除和级联更新有些突破了传统的关于外键的定义功能有点太过强大使用前必须确定自己已经把握好其功能范围否则级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。实际项目中的主外键取舍设计(在性能和可扩展性之间寻求平衡)边缘模块指的是小功能不常用需求很少再改的模块中心模块是指关联的东西太多的模块、是很多表的主表物理键指的是在表建立主外键关联逻辑主外键指的是利用字段去实现逻辑主外键关联热点模块指的是需求经常要改的模块大型系统1. 针对性能要求不高安全要求高的模块推荐使用物理主外键关联针对性能要求高、安全自己控制的模块推荐不用物理外键2. 针对中心模块和其他模块的联系推荐使用物理主外键。3. 针对热点模块必须使用逻辑主外键4. 针对边缘模块推荐使用物理主外键小系统随便你啦也就是20张表以下的系统。逻辑不复杂都无所谓啦不过推荐还是使用外键。注意不用外键而用程序控制数据一致性和完整性时应该写一层来保证然后个个应用通过这个层来访问数据库。外键是有性能问题的不能过分追求。(5)存储过程、视图、函数的适当使用(这些是优化的方法这几个后面会讲)很多人习惯将复杂操作都放在应用程序层但如果你要优化数据访问性能将SQL代码移植到数据库上(使用存储过程视图函数和触发器)也是一个很大的改进原因如下1)存储过程减少了网络传输、处理及存储的工作量且经过编译和优化执行速度快易于维护且表的结构改变时不影响客户端的应用程序2)使用存储过程视图函数有助于减少应用程序中SQL复制的弊端因为现在只在一个地方集中处理SQL3)使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题同时有助于你集中管理TSQL代码更好的重构TSQL代码。(6)传说中的‘三少原则’1)数据库的表越少越好2)表的字段越少越好3)字段中的组合主键、组合索引越少越好这里的少是相对的是减少数据冗余的重要设计理念而已。实际上我们为了减少单表查询压力会把去分表从而分发记录量避免一个超级表的诞生。(7)分割你的表减小表尺寸如果你发现某个表的记录太多例如超过一千万条则要对该表进行水平分割。水平分割的做法是以该表主键的某个值为界线将该表的记录水平分割为两个表。如果你若发现某个表的字段太多例如超过八十个则垂直分割该表将原来的一个表分解为两个表(8)字段设计原则字段是数据库最基本的单位其设计对性能的影响是很大的。需要注意如下1)数据类型尽量用数字型数字型的比较比字符型的快很多。2)数据类型尽量小这里的尽量小是指在满足可以预见的未来需求的前提下的。3)尽量不要允许NULL除非必要可以用NOT NULLDEFAULT代替。NULL 类型比较特殊SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异会进入索引中但如果是一个组合索引那么这个NULL 类型的字段会极大影响整个索引的效率。此外NULL 在索引中的处理也是特殊的也会占用额外的存放空间。4)少用TEXT和IMAGE二进制字段的读写是比较慢的而且读取的方法也不多大部分情况下最好不用。5)自增字段要慎用不利于数据迁移二、基于单表设计的多表设计原则(1)表关系一)一对一关系定义在这种关系中关系表的每一边都只能存在一个记录。每个数据表中的关键字在对应的关系表中只能存在一个记录或者没有对应的记录。这种关系和一对配偶之间的关系非常相似——要么你已经结婚你和你的配偶只能有一个配偶要么你没有结婚没有配偶。大多数的一对一的关系都是某种商业规则约束的结果而不是按照数据的自然属性来得到的。如果没有这些规则的约束你通常可以把两个数据表合并进一个数据表而且不会打破任何规范化的规则。一对一关系又分为一对一外键关联和一对一主键关联。一对一主键关联要求两个表的主键必须完全一致通过两个表的主键建立关联关系。可以看到下图很明显的班级编号作为主键的话就是一个主键关联了。这里写图片描述一对一外键关联下面又很明显看到以班主任ID作为外键关联起来的一个表。这里写图片描述二)一对多关系(多对一)定义主键数据表中只能含有一个记录而在其关系表中这条记录可以与一个或者多个记录相关也可以没有记录与之相关。这种关系类似于你和你的父母之间的关系。你只有一位母亲但是你母亲可以有几个孩子。下图可以看到一对多-班级表有多个学生多对一-多个学生属于一个班级。这里写图片描述三)多对多关系定义两个数据表里的每条记录都可以和另一个数据表里任意数量的记录(或者没有记录)相关。例如如果你有多个兄弟姐妹这对你的兄弟姐妹也是一样(有多个兄弟姐妹)多对多这种关系需要引入第三个数据表这种数据表称为联系表或者连接表因为关系型系统不能直接实现这种关系。在RDBMS中必须使用中间表来表示多对多的关系。中间表我们可以分成两种一种是纯粹表示关系的中间表一种是表示中间实体的中间表。纯粹表示关系的中间表很简单只需要两列AID和BIDAID以外键关联到A表的主键BID以外键关联到B表的主键然后这两个列组成联合主键。这个中间表纯粹是表示多对多关系而存在在业务上不会有对应的实体与之对应。比如前面提到的学生和课程的关系如果我们只需要知道哪些学生上哪些课哪些课有哪些学生选不需要有更多的信息的情况下我们就可以建立“学生课程”中间表里面只有学生ID和课程ID两个字段。这里写图片描述中间实体是在纯粹的中间关系表的基础上加上了更多的属性从而形成了一个新的实体。比如上面提到的学生和课程的关系如果我们需要记录学生选课的时间、学生选择这门课程后的考试成绩那么我们就像建立一个“选课”实体该实体具有如下属性选课ID主键学生ID与学生表做外键关联课程ID与课程表做外键关联选课时间DateTime类型考试成绩记录选修该课程后考试的最终成绩注意一)外键与索引外键是一种约束与索引的概念不一样只是大多数情况下我们建立外键时都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查如果不满足外键约束则禁止数据的插入或修改这必然带来一个问题就是在数据量特别大的情况下每一次约束检查必然导致性能的下降。索引其实也有类似的问题索引如果建多了那么在插入删除修改数据时也要去维护对应的索引所以索引的存在也会导致数据操作变慢。不过外键与索引的优点不同外键只是保证数据的一致性并不能给系统性能带来任何好处所以由于外键导致的插入数据变慢会随着数据量的增长而越来越严重。而索引的目的是为了检索数据更快维护数据时导致的索引数据的变更对性能的影响不会像外键那样随着数据量增长而变得严重(当然大数量时的索引树维护会比小数据量的索引树维护更麻烦但至少不是像外键那样)。出于性能的考虑如果我们的系统完全由我们开发的程序使用而不需要提供数据库给其他应用系统写入数据而且对性能要求较高那么我们可以考虑在生产环境中不使用外键只需要建立能够提高性能的索引。由于整个数据库的操作都是由我们开发的程序来完成的所以我们程序可以在开发过程中做好各方面的一致性检查保证操作的数据是满足外键约束的而不需要真正的存在这样一个外键约束。怎么做到这一点呢首先我们在建立数据库时有多个脚本包括创建表、创建初始化数据、创建索引、创建外键等我们在开发和测试环境中都把这些脚本运行了以使开发测试环境中的数据库是完整的经过大量测试保证应用程序能够维护数据之间的约束的情况下那么我们在生产时并不需要运行创建外键这个脚本文件只需要创建表、初始化数据、创建索引等即可。二)建立关系在开始着手考虑建立关系表之间的关系之前你可能需要对数据非常熟悉。只有在熟悉数据之后关联会比你刚开始的时候更明显。你的数据库系统依赖于在两个数据表中找到的匹配值来建立关系。进行匹配的值都是主键和外键的值。(关系模型不要求一个关系必须对应的使用一个主键来确定。你可以使用数据表中的任何备选关键字来建立关系但是使用主键是大家都已经接受的标准。)主键(primary key)唯一的识别表中的每个记录。而外键(foreign key)只是简单的将一个数据表中的主键存放在另外一个数据表中。同样地对于你来说也不需要做太多的工作——只是简单地将主键加到关系表中并将其定义为外键。(2)分表原则(涉及分区分表问题探究以后的篇章再补充实例)分表主要目的是为突破单节点数据库服务器的 I/O 能力限制解决数据库扩展性问题。 同时分表分库等思想也将引出以后的数据库集群主从复制、读写分离方案.....为什么我们要分表分区日常开发中我们经常会遇到大表的情况所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大导致数据库在查询和插入的时候耗时太长性能低下如果涉及联合查询的情况性能会更加糟糕。分表和表分区的目的就是减少数据库的负担提高数据库的效率通常点来讲就是提高表的增删改查效率。(一)表拆分方式1)垂直切分这里写图片描述定义把主键和一些数据表的列放在一个表中然后把主键和另一些数据表的列放在一个表中。如果一个表的某些列常用另一些不常用则可以采用垂直拆分。垂直拆分可以使数据行变小一个数据页就可以存放更多的数据在查询时候可以减少I/O次数。其缺点是需要管理冗余列查询所有数据时候需要join查找。优点使得行数据变小一个数据块(Block)就能存放更多的数据在查询时就会减少I/O次数(每次查询时读取的Block 就少)。可以达到最大化利用Cache的目的。缺点表垂直分割后主码(主键)出现冗余需要管理冗余列会引起表连接JOIN操作(增加CPU开销)需要从业务上规避2)水平拆分(分表分区)--按表中某一字段值的范围划分这里写图片描述定义根据列的范围值进行合理切分放在多个独立的表或分区中。适用场景表很大分割后可以降低查询时候需要读取的数据和索引的页数同时降低索引的层数提高查询速度。表中的数据是独立的例如表中分别记录各个地区的数据或不同时期的数据特别是有些数据常用而另一些数据不常用。需要把数据放在多个存储介质上。需要把历史数据和当前的数据拆分开。例子当伴随着某一个表的数据量越来越大以至于不能承受的时候就需要对它进行进一步的切分。一种选择是根据key 的范围来做切分譬如ID 为 1-10000的放到表A上ID 为10000~20000的放到表B。这样的扩展就是可预见的。另一种是根据某一字段值来划分譬如根据用户名的首字母如果是A-D就属于表AE-H就属于表B。这样做也存在不均衡性当某个范围超出了单点所能承受的范围就需要继续切分。还有按日期切分等等。可以使用Mrg_Myisam引擎实现水平分表。优点单表大小可控天然水平扩展。降低在查询时需要读的数据和索引的页数同时也降低了索引的层数加快了查询速度。缺点无法解决集中写入瓶颈的问题。同时水平分割会给应用增加复杂度它通常在查询时需要多个表名查询所有数据需要union操作。在许多数据库应用中这种复杂性会超过它带来的优点因为只要索引关键字不大则在索引用于查询时表中增加两到三倍数据量查询时也就增加读一个索引层的磁盘次数。3)散列库表(基于hash算法的切分)定义表散列与水平分割相似但没有水平分割那样的明显分割界限采用Hash算法把数据分散到各个分表中, 这样IO更加均衡。一般采用mod来切分一开始确定切分数据库的个数通过hash取模来决定使用哪台。这种方法能够平均地来分配数据但是伴随着数据量的增大需要进行扩展的时候这种方式无法做到在线扩容。每增加节点的时候就需要对hash 算法重新运算。我们会按照业务或者功能模块将数据库进行分离不同的模块对应不同的数据库或者表再按照一定的策略对某个页面或者功能进行更小的数据库散列比如用户表按照用户ID进行表散列散列128张表则应就能够低成本的提升系统的性能并且有很好的扩展性优点数据分布均匀缺点数据迁移的时候麻烦不能按照机器性能分摊数据(二)在了解完分表了我们先来理解区分分区与分表吧。分区定义分区和分表相似都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表而分区是将数据分段划分在多个位置存放可以是同一块磁盘也可以在不同的机器。分区后表面上还是一张表但数据散列到多个位置了。app读写的时候操作的还是大表名字db自动去组织分区的数据。分表定义分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表我们可以称为子表每个表都对应三个文件MYD数据文件.MYI索引文件.frm表结构文件。这些子表可以分布在同一块磁盘上也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名然后去操作它。mysql分表和分区有什么联系呢1.都能提高mysql的性高在高并发状态下都有一个良好的表现。2.分表和分区不矛盾可以相互配合的对于那些大访问量并且表数据比较多的表我们可以采取分表和分区结合的方式(如果merge这种分表方式不能和分区配合的话可以用其他的分表试)访问量不大但是表数据很多的表我们可以采取分区的方式等。3.分表技术是比较麻烦的需要手动去创建子表app服务端读写时候需要计算子表名。采用merge好一些但也要创建子表和配置子表间的union关系。4.表分区相对于分表操作方便不需要创建子表。(三)表拆分建议(针对大系统)其实这点没有明确的判断标准比较依赖实际业务情况和经验判断。一般MySQL单表1000W左右的数据是没有问题的(前提是应用系统和数据库等层面设计和优化的比较好)。1)对记录多的表进行拆分。(几百-上千万级别的表)2)需要拆分的表分为动态表和相对静态表。动态表拆分到不同库静态表存在于公共库。从公共库同步到分库。实现表的连接。3)按照年、月、地域等来分割或者根据时间范围、和很固定又清晰的字段值范围等具有确定的分割标志来分割。好了MySQL优化笔记(四)--表的设计与优化(单表、多表)讲完了这是我学习优化数据库时候的笔记这是积累的必经一步我会继续出这个系列文章分享经验给大家。欢迎在下面指出错误共同学习你的点赞是对我最好的支持
