精华内容
下载资源
问答
  • 千万级大表如何优化,这是一个很有技术含量问题,通常我们直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面经验总结,也欢迎大家提出建议。从一开始脑海里开始也是火光四...

    736fb1da30f24324bb71a2aa435f84b9.png

    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。

    从一开始脑海里开始也是火光四现,到不断的自我批评,后来也参考了一些团队的经验,我整理了下面的大纲内容。

    2b01abdbc2b4d624c44b2ff91966403d.png

    既然要吃透这个问题,我们势必要回到本源,我把这个问题分为三部分:

    “千万级”,“大表”,“优化”,

    也分别对应我们在图中标识的

    “数据量”,“对象”和“目标”。

    我来逐步展开说明一下,从而给出一系列的解决方案。

    1.数据量:千万级

    千万级其实只是一个感官的数字,就是我们印象中的数据量大。 这里我们需要把这个概念细化,因为随着业务和时间的变化,数据量也会有变化,我们应该是带着一种动态思维来审视这个指标,从而对于不同的场景我们应该有不同的处理策略。

    1) 数据量为千万级,可能达到亿级或者更高

    通常是一些数据流水,日志记录的业务,里面的数据随着时间的增长会逐步增多,超过千万门槛是很容易的一件事情。

    2) 数据量为千万级,是一个相对稳定的数据量

    如果数据量相对稳定,通常是在一些偏向于状态的数据,比如有1000万用户,那么这些用户的信息在表中都有相应的一行数据记录,随着业务的增长,这个量级相对是比较稳定的。

    3) 数据量为千万级,不应该有这么多的数据

    这种情况是我们被动发现的居多,通常发现的时候已经晚了,比如你看到一个配置表,数据量上千万;或者说一些表里的数据已经存储了很久,99%的数据都属于过期数据或者垃圾数据。

    数据量是一个整体的认识,我们需要对数据做更近一层的理解,这就可以引出第二个部分的内容。

    2.对象:数据表

    数据操作的过程就好比数据库中存在着多条管道,这些管道中都流淌着要处理的数据,这些数据的用处和归属是不一样的。

    一般根据业务类型把数据分为三种:

    (1)流水型数据

    流水型数据是无状态的,多笔业务之间没有关联,每次业务过来的时候都会产生新的单据,比如交易流水、支付流水,只要能插入新单据就能完成业务,特点是后面的数据不依赖前面的数据,所有的数据按时间流水进入数据库。

    (2)状态型数据

    状态型数据是有状态的,多笔业务之间依赖于有状态的数据,而且要保证该数据的准确性,比如充值时必须要拿到原来的余额,才能支付成功。

    (3)配置型数据

    此类型数据数据量较小,而且结构简单,一般为静态数据,变化频率很低。

    至此,我们可以对整体的背景有一个认识了,如果要做优化,其实要面对的是这样的3*3的矩阵,如果要考虑表的读写比例(读多写少,读少写多...),那么就会是3*3*4=24种,显然做穷举是不显示的,而且也完全没有必要,可以针对不同的数据存储特性和业务特点来指定不同的业务策略。

    对此我们采取抓住重点的方式,把常见的一些优化思路梳理出来,尤其是里面的核心思想,也是我们整个优化设计的一把尺子,而难度决定了我们做这件事情的动力和风险。

    39ad04fda10598cc757fe860e0758d95.png

    而对于优化方案,我想采用面向业务的维度来进行阐述。

    3.目标:优化

    在这个阶段,我们要说优化的方案了,总结的有点多,相对来说是比较全了。

    整体分为五个部分:

    23597b1191b3ac9b6d4500292baa130c.png

    其实我们通常所说的分库分表等方案只是其中的一小部分,如果展开之后就比较丰富了。

    400988b9accb9619f9f763ad8db2d5c7.png

    其实不难理解,我们要支撑的表数据量是千万级别,相对来说是比较大了,DBA要维护的表肯定不止一张,如何能够更好的管理,同时在业务发展中能够支撑扩展,同时保证性能,这是摆在我们面前的几座大山。

    我们分别来说一下这五类改进方案:

    优化设计方案1.规范设计

    在此我们先提到的是规范设计,而不是其他高大上的设计方案。

    黑格尔说:秩序是自由的第一条件。在分工协作的工作场景中尤其重要,否则团队之间互相牵制太多,问题多多。

    规范设计我想提到如下的几个规范,其实只是属于开发规范的一部分内容,可以作为参考。

    4ef761bf3a7be5cebfbefa4a26a65532.png

    规范的本质不是解决问题,而是有效杜绝一些潜在问题,对于千万级大表要遵守的规范,我梳理了如下的一些细则,基本可以涵盖我们常见的一些设计和使用问题,比如表的字段设计不管三七二十一,都是varchar(500),其实是很不规范的一种实现方式,我们来展开说一下这几个规范。

    1)配置规范

    (1)MySQL数据库默认使用InnoDB存储引擎。

    (2)保证字符集设置统一,MySQL数据库相关系统、数据库、表的字符集使都用UTF8,应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。

    注:UTF8格式是存储不了表情类数据,需要使用UTF8MB4,可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4,可以根据公司的业务情况进行统一或者定制化设置。

    (3)MySQL数据库的事务隔离级别默认为RR(Repeatable-Read),建议初始化时统一设置为RC(Read-Committed),对于OLTP业务更适合。

    (4)数据库中的表要合理规划,控制单表数据量,对于MySQL数据库来说,建议单表记录数控制在2000W以内。

    (5)MySQL实例下,数据库、表数量尽可能少;数据库一般不超过50个,每个数据库下,数据表数量一般不超过500个(包括分区表)。

    2)建表规范

    (1)InnoDB禁止使用外键约束,可以通过程序层面保证。

    (2)存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

    (3)整型定义中无需定义显示宽度,比如:使用INT,而不是INT(4)。

    (4)不建议使用ENUM类型,可使用TINYINT来代替。

    (5)尽可能不使用TEXT、BLOB类型,如果必须使用,建议将过大字段或是不常用的描述型较大字段拆分到其他表中;另外,禁止用数据库存储图片或文件。

    (6)存储年时使用YEAR(4),不使用YEAR(2)。

    (7)建议字段定义为NOT NULL。

    (8)建议DBA提供SQL审核工具,建表规范性需要通过审核工具审核后

    3)命名规范

    (1)库、表、字段全部采用小写。

    (2)库名、表名、字段名、索引名称均使用小写字母,并以“_”分割。

    (3)库名、表名、字段名建议不超过12个字符。(库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,统一不超过12字符)

    (4)库名、表名、字段名见名知意,不需要添加注释。

    对于对象命名规范的一个简要总结如下表4-1所示,供参考。

    b13706b1836585405e2aff3f864930db.png

    4)索引规范

    (1)索引建议命名规则:idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段过长建议采用缩写)。

    (2)索引中的字段数建议不超过5个。

    (3)单张表的索引个数控制在5个以内。

    (4)InnoDB表一般都建议有主键列,尤其在高可用集群方案中是作为必须项的。

    (5)建立复合索引时,优先将选择性高的字段放在前面。

    (6)UPDATE、DELETE语句需要根据WHERE条件添加索引。

    (7)不建议使用%前缀模糊查询,例如LIKE “%weibo”,无法用到索引,会导致全表扫描。

    (8)合理利用覆盖索引,例如:

    (9)SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,可以创建覆盖索引idx_uid_email(uid,email)来提高查询效率。

    (10)避免在索引字段上使用函数,否则会导致查询时索引失效。

    (11)确认索引是否需要变更时要联系DBA。

    5)应用规范

    (1)避免使用存储过程、触发器、自定义函数等,容易将业务逻辑和DB耦合在一起,后期做分布式方案时会成为瓶颈。

    (2)考虑使用UNION ALL,减少使用UNION,因为UNION ALL不去重,而少了排序操作,速度相对比UNION要快,如果没有去重的需求,优先使用UNION ALL。

    (3)考虑使用limit N,少用limit M,N,特别是大表或M比较大的时候。

    (4)减少或避免排序,如:group by语句中如果不需要排序,可以增加order by null。

    (5)统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1);InnoDB表避免使用COUNT(*)操作,计数统计实时要求较强可以使用Memcache或者Redis,非实时统计可以使用单独统计表,定时更新。

    (6)做字段变更操作(modify column/change column)的时候必须加上原有的注释属性,否则修改后,注释会丢失。

    (7)使用prepared statement可以提高性能并且避免SQL注入。

    (8)SQL语句中IN包含的值不应过多。

    (9)UPDATE、DELETE语句一定要有明确的WHERE条件。

    (10)WHERE条件中的字段值需要符合该字段的数据类型,避免MySQL进行隐式类型转化。

    (11)SELECT、INSERT语句必须显式的指明字段名称,禁止使用SELECT * 或是INSERT INTO table_name values()。

    (12)INSERT语句使用batch提交(INSERT INTO table_name VALUES(),(),()……),values的个数不应过多。

    优化设计方案2:业务层优化

    业务层优化应该是收益最高的优化方式了,而且对于业务层完全可见,主要有业务拆分,数据拆分和两类常见的优化场景(读多写少,读少写多)

    873b4150fede1f22a5e1812bfe02a196.png

    1)业务拆分

    ü 将混合业务拆分为独立业务

    ü 将状态和历史数据分离

    业务拆分其实是把一个混合的业务剥离成为更加清晰的独立业务,这样业务1,业务2。。。独立的业务使得业务总量依旧很大,但是每个部分都是相对独立的,可靠性依然有保证。

    对于状态和历史数据分离,我可以举一个例子来说明。

    例如:我们有一张表Account,假设用户余额为100。

    9e81cd3a745348c628511c2e6f4b0926.png

    我们需要在发生数据变更后,能够追溯数据变更的历史信息,如果对账户更新状态数据,增加100的余额,这样余额为200。

    这个过程可能对应一条update语句,一条insert语句。

    对此我们可以改造为两个不同的数据源,account和account_hist

    在account_hist中就会是两条insert记录,如下:

    364370ee39b69f587fb473d3906f2b9f.png

    而在account中则是一条update语句,如下:

    3fdd939b95944214c42c389b9924bae9.png

    这也是一种很基础的冷热分离,可以大大减少维护的复杂度,提高业务响应效率。

    2)数据拆分

    2.1 按照日期拆分,这种使用方式比较普遍,尤其是按照日期维度的拆分,其实在程序层面的改动很小,但是扩展性方面的收益很大。

    • 数据按照日期维度拆分,如test_20191021
    • 数据按照周月为维度拆分,如test_201910
    • 数据按照季度,年维度拆分,如test_2019

    2.2 采用分区模式,分区模式也是常见的使用方式,采用hash,range等方式会多一些,在MySQL中我是不大建议使用分区表的使用方式,因为随着存储容量的增长,数据虽然做了垂直拆分,但是归根结底,数据其实难以实现水平扩展,在MySQL中是有更好的扩展方式。

    2.3 读多写少优化场景

    采用缓存,采用Redis技术,将读请求打在缓存层面,这样可以大大降低MySQL层面的热点数据查询压力。

    2.4 读少写多优化场景,可以采用三步走:

    1) 采用异步提交模式,异步对于应用层来说最直观的就是性能的提升,产生最少的同步等待。

    2) 使用队列技术,大量的写请求可以通过队列的方式来进行扩展,实现批量的数据写入。

    3) 降低写入频率,这个比较难理解,我举个例子

    对于业务数据,比如积分类,相比于金额来说业务优先级略低的场景,如果数据的更新过于频繁,可以适度调整数据更新的范围(比如从原来的每分钟调整为10分钟)来减少更新的频率。

    例如:更新状态数据,积分为200,如下图所示

    7f845fe4f3bf58c6ac60526654184659.png

    可以改造为,如下图所示。

    1fb8849067076aa674c93d4db1a0b07d.png

    如果业务数据在短时间内更新过于频繁,比如1分钟更新100次,积分从100到10000,则可以根据时间频率批量提交。

    例如:更新状态数据,积分为100,如下图所示。

    1449b6c771dd155244018b8d5b8c0b6a.png

    无需生成100个事务(200条SQL语句)可以改造为2条SQL语句,如下图所示。

    c8d2a6fb312b6adf805d88f5ea04768c.png

    对于业务指标,比如更新频率细节信息,可以根据具体业务场景来讨论决定。

    优化设计方案3:架构层优化

    架构层优化其实就是我们认为的那种技术含量很高的工作,我们需要根据业务场景在架构层面引入一些新的花样来。

    518bfb3616d068d7039c41c0a98d84ae.png

    3.1.系统水平扩展场景

    3.1.1采用中间件技术,可以实现数据路由,水平扩展,常见的中间件有MyCAT,ShardingSphere,ProxySQL等

    a26ac305dbcf46775fa1ce35adc4e8a3.png

    3.1.2 采用读写分离技术,这是针对读需求的扩展,更侧重于状态表,在允许一定延迟的情况下,可以采用多副本的模式实现读需求的水平扩展,也可以采用中间件来实现,如MyCAT,ProxySQL,MaxScale,MySQL Router等

    311d22907a768496cd4f6f9b49b0e20f.png

    3.1.3 采用负载均衡技术,常见的有LVS技术或者基于域名服务的Consul技术等

    3.2.兼顾OLTP+OLAP的业务场景,可以采用NewSQL,优先兼容MySQL协议的HTAP技术栈,如TiDB

    3.3.离线统计的业务场景,有几类方案可供选择。

    3.3.1 采用NoSQL体系,主要有两类,一类是适合兼容MySQL协议的数据仓库体系,常见的有Infobright或者ColumnStore,另外一类是基于列式存储,属于异构方向,如HBase技术

    3.3.2 采用数仓体系,基于MPP架构,如使用Greenplum统计,如T+1统计

    优化设计方案4:数据库优化

    数据库优化,其实可打的牌也不少,但是相对来说空间没有那么大了,我们来逐个说一下。

    1baff22a68e61a2ea8dab9685398c55b.png

    4.1 事务优化

    根据业务场景选择事务模型,是否是强事务依赖

    对于事务降维策略,我们来举出几个小例子来。

    4.1.1 降维策略1:存储过程调用转换为透明的SQL调用

    对于新业务而言,使用存储过程显然不是一个好主意,MySQL的存储过程和其他商业数据库相比,功能和性能都有待验证,而且在目前轻量化的业务处理中,存储过程的处理方式太“重”了。

    有些应用架构看起来是按照分布式部署的,但在数据库层的调用方式是基于存储过程,因为存储过程封装了大量的逻辑,难以调试,而且移植性不高,这样业务逻辑和性能压力都在数据库层面了,使得数据库层很容易成为瓶颈,而且难以实现真正的分布式。

    所以有一个明确的改进方向就是对于存储过程的改造,把它改造为SQL调用的方式,可以极大地提高业务的处理效率,在数据库的接口调用上足够简单而且清晰可控。

    4.1.2 降维策略2:DDL操作转换为DML操作

    有些业务经常会有一种紧急需求,总是需要给一个表添加字段,搞得DBA和业务同学都挺累,可以想象一个表有上百个字段,而且基本都是name1,name2……name100,这种设计本身就是有问题的,更不用考虑性能了。究其原因,是因为业务的需求动态变化,比如一个游戏装备有20个属性,可能过了一个月之后就增加到了40个属性,这样一来,所有的装备都有40个属性,不管用没用到,而且这种方式也存在诸多的冗余。

    我们在设计规范里面也提到了一些设计的基本要素,在这些基础上需要补充的是,保持有限的字段,如果要实现这些功能的扩展,其实完全可以通过配置化的方式来实现,比如把一些动态添加的字段转换为一些配置信息。配置信息可以通过DML的方式进行修改和补充,对于数据入口也可以更加动态、易扩展。

    4.1.3 降维策略3:Delete操作转换为高效操作

    有些业务需要定期来清理一些周期性数据,比如表里的数据只保留一个月,那么超出时间范围的数据就要清理掉了,而如果表的量级比较大的情况下,这种Delete操作的代价实在太高,我们可以有两类解决方案来把Delete操作转换为更为高效的方式。

    第一种是根据业务建立周期表,比如按照月表、周表、日表等维度来设计,这样数据的清理就是一个相对可控而且高效的方式了。

    第二种方案是使用MySQL rename的操作方式,比如一张2千万的大表要清理99%的数据,那么需要保留的1%的数据我们可以很快根据条件过滤补录,实现“移形换位”。

    4.2 SQL优化

    其实相对来说需要的极简的设计,很多点都在规范设计里面了,如果遵守规范,八九不离十的问题都会杜绝掉,在此补充几点:

    4.2.1 SQL语句简化,简化是SQL优化的一大利器,因为简单,所以优越。

    4.2.2 尽可能避免或者杜绝多表复杂关联,大表关联是大表处理的噩梦,一旦打开了这个口子,越来越多的需求需要关联,性能优化就没有回头路了,更何况大表关联是MySQL的弱项,尽管Hash Join才推出,不要像掌握了绝对大杀器一样,在商业数据库中早就存在,问题照样层出不穷。

    4.2.3 SQL中尽可能避免反连接,避免半连接,这是优化器做得薄弱的一方面,什么是反连接,半连接?其实比较好理解,举个例子,not in ,not exists就是反连接,in,exists就是半连接,在千万级大表中出现这种问题,性能是几个数量级的差异。

    4.3 索引优化

    应该是大表优化中需要把握的一个度。

    4.3.1 首先必须有主键,规范设计中第一条就是,此处不接收反驳。

    4.3.2 其次,SQL查询基于索引或者唯一性索引,使得查询模型尽可能简单。

    4.3.3 最后,尽可能杜绝范围数据的查询,范围扫描在千万级大表情况下还是尽可能减少。

    优化设计方案4:管理优化

    这部分应该是在所有的解决方案中最容易被忽视的部分了,我放在最后,在此也向运维同事致敬,总是为很多认为本应该正常的问题尽职尽责(背锅)。

    694c5f781d7a769c7f534b1f10921070.png

    千万级大表的数据清理一般来说是比较耗时的,在此建议在设计中需要完善冷热数据分离的策略,可能听起来比较拗口,我来举一个例子,把大表的Drop 操作转换为可逆的DDL操作。

    Drop操作是默认提交的,而且是不可逆的,在数据库操作中都是跑路的代名词,MySQL层面目前没有相应的Drop操作恢复功能,除非通过备份来恢复,但是我们可以考虑将Drop操作转换为一种可逆的DDL操作。

    MySQL中默认每个表有一个对应的ibd文件,其实可以把Drop操作转换为一个rename操作,即把文件从testdb迁移到testdb_arch下面;从权限上来说,testdb_arch是业务不可见的,rename操作可以平滑的实现这个删除功能,如果在一定时间后确认可以清理,则数据清理对于已有的业务流程是不可见的,如下图所示。

    013f62e03e0eeeb57be534554c631ffc.png

    此外,还有两个额外建议,一个是对于大表变更,尽可能考虑低峰时段的在线变更,比如使用pt-osc工具或者是维护时段的变更,就不再赘述了。

    最后总结一下,其实就是一句话:

    千万级大表的优化是根据业务场景,以成本为代价进行优化的,绝对不是孤立的一个层面的优化

    个人新书 《MySQL DBA工作笔记》

    个人公众号:jianrong-notes

    展开全文
  • 千万级大表如何优化,这是一个很有技术含量问题,通常我们直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面经验总结,也欢迎大家提出建议。从一开始脑海里开始也是火光四...

    e668ff9bf8474d1fee28fe95c962af0a.png

    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。

    从一开始脑海里开始也是火光四现,到不断的自我批评,后来也参考了一些团队的经验,我整理了下面的大纲内容。

    6eeff933582baba971ca6903125fa15f.png

    既然要吃透这个问题,我们势必要回到本源,我把这个问题分为三部分:

    “千万级”,“大表”,“优化”,

    也分别对应我们在图中标识的

    “数据量”,“对象”和“目标”。

    我来逐步展开说明一下,从而给出一系列的解决方案。

    1.数据量:千万级

    千万级其实只是一个感官的数字,就是我们印象中的数据量大。 这里我们需要把这个概念细化,因为随着业务和时间的变化,数据量也会有变化,我们应该是带着一种动态思维来审视这个指标,从而对于不同的场景我们应该有不同的处理策略。

    1) 数据量为千万级,可能达到亿级或者更高

    通常是一些数据流水,日志记录的业务,里面的数据随着时间的增长会逐步增多,超过千万门槛是很容易的一件事情。

    2) 数据量为千万级,是一个相对稳定的数据量

    如果数据量相对稳定,通常是在一些偏向于状态的数据,比如有1000万用户,那么这些用户的信息在表中都有相应的一行数据记录,随着业务的增长,这个量级相对是比较稳定的。

    3) 数据量为千万级,不应该有这么多的数据

    这种情况是我们被动发现的居多,通常发现的时候已经晚了,比如你看到一个配置表,数据量上千万;或者说一些表里的数据已经存储了很久,99%的数据都属于过期数据或者垃圾数据。

    数据量是一个整体的认识,我们需要对数据做更近一层的理解,这就可以引出第二个部分的内容。

    2.对象:数据表

    数据操作的过程就好比数据库中存在着多条管道,这些管道中都流淌着要处理的数据,这些数据的用处和归属是不一样的。

    一般根据业务类型把数据分为三种:

    (1)流水型数据

    流水型数据是无状态的,多笔业务之间没有关联,每次业务过来的时候都会产生新的单据,比如交易流水、支付流水,只要能插入新单据就能完成业务,特点是后面的数据不依赖前面的数据,所有的数据按时间流水进入数据库。

    (2)状态型数据

    状态型数据是有状态的,多笔业务之间依赖于有状态的数据,而且要保证该数据的准确性,比如充值时必须要拿到原来的余额,才能支付成功。

    (3)配置型数据

    此类型数据数据量较小,而且结构简单,一般为静态数据,变化频率很低。

    至此,我们可以对整体的背景有一个认识了,如果要做优化,其实要面对的是这样的3*3的矩阵,如果要考虑表的读写比例(读多写少,读少写多...),那么就会是3*3*4=24种,显然做穷举是不显示的,而且也完全没有必要,可以针对不同的数据存储特性和业务特点来指定不同的业务策略。

    对此我们采取抓住重点的方式,把常见的一些优化思路梳理出来,尤其是里面的核心思想,也是我们整个优化设计的一把尺子,而难度决定了我们做这件事情的动力和风险。

    36ff7e9d8a1bf6ba2f3a6a7ff77ba1f7.png

    而对于优化方案,我想采用面向业务的维度来进行阐述。

    3.目标:优化

    在这个阶段,我们要说优化的方案了,总结的有点多,相对来说是比较全了。

    整体分为五个部分:

    9acdd439eab6da707f87136609e77163.png

    其实我们通常所说的分库分表等方案只是其中的一小部分,如果展开之后就比较丰富了。

    3ccf9c25a7e6f8b7b298396d9f345e12.png

    其实不难理解,我们要支撑的表数据量是千万级别,相对来说是比较大了,DBA要维护的表肯定不止一张,如何能够更好的管理,同时在业务发展中能够支撑扩展,同时保证性能,这是摆在我们面前的几座大山。

    我们分别来说一下这五类改进方案:

    优化设计方案1.规范设计

    在此我们先提到的是规范设计,而不是其他高大上的设计方案。

    黑格尔说:秩序是自由的第一条件。在分工协作的工作场景中尤其重要,否则团队之间互相牵制太多,问题多多。

    规范设计我想提到如下的几个规范,其实只是属于开发规范的一部分内容,可以作为参考。

    be05c724b3186d234f6fe3e06e3cfa2e.png

    规范的本质不是解决问题,而是有效杜绝一些潜在问题,对于千万级大表要遵守的规范,我梳理了如下的一些细则,基本可以涵盖我们常见的一些设计和使用问题,比如表的字段设计不管三七二十一,都是varchar(500),其实是很不规范的一种实现方式,我们来展开说一下这几个规范。

    1)配置规范

    (1)MySQL数据库默认使用InnoDB存储引擎。

    (2)保证字符集设置统一,MySQL数据库相关系统、数据库、表的字符集使都用UTF8,应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。

    注:UTF8格式是存储不了表情类数据,需要使用UTF8MB4,可在MySQL字符集里面设置。在8.0中已经默认为UTF8MB4,可以根据公司的业务情况进行统一或者定制化设置。

    (3)MySQL数据库的事务隔离级别默认为RR(Repeatable-Read),建议初始化时统一设置为RC(Read-Committed),对于OLTP业务更适合。

    (4)数据库中的表要合理规划,控制单表数据量,对于MySQL数据库来说,建议单表记录数控制在2000W以内。

    (5)MySQL实例下,数据库、表数量尽可能少;数据库一般不超过50个,每个数据库下,数据表数量一般不超过500个(包括分区表)。

    2)建表规范

    (1)InnoDB禁止使用外键约束,可以通过程序层面保证。

    (2)存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

    (3)整型定义中无需定义显示宽度,比如:使用INT,而不是INT(4)。

    (4)不建议使用ENUM类型,可使用TINYINT来代替。

    (5)尽可能不使用TEXT、BLOB类型,如果必须使用,建议将过大字段或是不常用的描述型较大字段拆分到其他表中;另外,禁止用数据库存储图片或文件。

    (6)存储年时使用YEAR(4),不使用YEAR(2)。

    (7)建议字段定义为NOT NULL。

    (8)建议DBA提供SQL审核工具,建表规范性需要通过审核工具审核后

    3)命名规范

    (1)库、表、字段全部采用小写。

    (2)库名、表名、字段名、索引名称均使用小写字母,并以“_”分割。

    (3)库名、表名、字段名建议不超过12个字符。(库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,统一不超过12字符)

    (4)库名、表名、字段名见名知意,不需要添加注释。

    对于对象命名规范的一个简要总结如下表4-1所示,供参考。

    6460779c8fe10d51b94f2b86bd798a7e.png

    4)索引规范

    (1)索引建议命名规则:idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段过长建议采用缩写)。

    (2)索引中的字段数建议不超过5个。

    (3)单张表的索引个数控制在5个以内。

    (4)InnoDB表一般都建议有主键列,尤其在高可用集群方案中是作为必须项的。

    (5)建立复合索引时,优先将选择性高的字段放在前面。

    (6)UPDATE、DELETE语句需要根据WHERE条件添加索引。

    (7)不建议使用%前缀模糊查询,例如LIKE “%weibo”,无法用到索引,会导致全表扫描。

    (8)合理利用覆盖索引,例如:

    (9)SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,可以创建覆盖索引idx_uid_email(uid,email)来提高查询效率。

    (10)避免在索引字段上使用函数,否则会导致查询时索引失效。

    (11)确认索引是否需要变更时要联系DBA。

    5)应用规范

    (1)避免使用存储过程、触发器、自定义函数等,容易将业务逻辑和DB耦合在一起,后期做分布式方案时会成为瓶颈。

    (2)考虑使用UNION ALL,减少使用UNION,因为UNION ALL不去重,而少了排序操作,速度相对比UNION要快,如果没有去重的需求,优先使用UNION ALL。

    (3)考虑使用limit N,少用limit M,N,特别是大表或M比较大的时候。

    (4)减少或避免排序,如:group by语句中如果不需要排序,可以增加order by null。

    (5)统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1);InnoDB表避免使用COUNT(*)操作,计数统计实时要求较强可以使用Memcache或者Redis,非实时统计可以使用单独统计表,定时更新。

    (6)做字段变更操作(modify column/change column)的时候必须加上原有的注释属性,否则修改后,注释会丢失。

    (7)使用prepared statement可以提高性能并且避免SQL注入。

    (8)SQL语句中IN包含的值不应过多。

    (9)UPDATE、DELETE语句一定要有明确的WHERE条件。

    (10)WHERE条件中的字段值需要符合该字段的数据类型,避免MySQL进行隐式类型转化。

    (11)SELECT、INSERT语句必须显式的指明字段名称,禁止使用SELECT * 或是INSERT INTO table_name values()。

    (12)INSERT语句使用batch提交(INSERT INTO table_name VALUES(),(),()……),values的个数不应过多。

    优化设计方案2:业务层优化

    业务层优化应该是收益最高的优化方式了,而且对于业务层完全可见,主要有业务拆分,数据拆分和两类常见的优化场景(读多写少,读少写多)

    b3b9c449c546a28e2ab384d2c58ebb1e.png

    1)业务拆分

    ü 将混合业务拆分为独立业务

    ü 将状态和历史数据分离

    业务拆分其实是把一个混合的业务剥离成为更加清晰的独立业务,这样业务1,业务2。。。独立的业务使得业务总量依旧很大,但是每个部分都是相对独立的,可靠性依然有保证。

    对于状态和历史数据分离,我可以举一个例子来说明。

    例如:我们有一张表Account,假设用户余额为100。

    eccdac9087972cc113dfd0a450a48246.png

    我们需要在发生数据变更后,能够追溯数据变更的历史信息,如果对账户更新状态数据,增加100的余额,这样余额为200。

    这个过程可能对应一条update语句,一条insert语句。

    对此我们可以改造为两个不同的数据源,account和account_hist

    在account_hist中就会是两条insert记录,如下:

    ec6b170adac89d388ab5a51c9fc5c20d.png

    而在account中则是一条update语句,如下:

    440006a4022c5044cc4a2aac25741b27.png

    这也是一种很基础的冷热分离,可以大大减少维护的复杂度,提高业务响应效率。

    2)数据拆分

    2.1 按照日期拆分,这种使用方式比较普遍,尤其是按照日期维度的拆分,其实在程序层面的改动很小,但是扩展性方面的收益很大。

    • 数据按照日期维度拆分,如test_20191021
    • 数据按照周月为维度拆分,如test_201910
    • 数据按照季度,年维度拆分,如test_2019

    2.2 采用分区模式,分区模式也是常见的使用方式,采用hash,range等方式会多一些,在MySQL中我是不大建议使用分区表的使用方式,因为随着存储容量的增长,数据虽然做了垂直拆分,但是归根结底,数据其实难以实现水平扩展,在MySQL中是有更好的扩展方式。

    2.3 读多写少优化场景

    采用缓存,采用Redis技术,将读请求打在缓存层面,这样可以大大降低MySQL层面的热点数据查询压力。

    2.4 读少写多优化场景,可以采用三步走:

    1) 采用异步提交模式,异步对于应用层来说最直观的就是性能的提升,产生最少的同步等待。

    2) 使用队列技术,大量的写请求可以通过队列的方式来进行扩展,实现批量的数据写入。

    3) 降低写入频率,这个比较难理解,我举个例子

    对于业务数据,比如积分类,相比于金额来说业务优先级略低的场景,如果数据的更新过于频繁,可以适度调整数据更新的范围(比如从原来的每分钟调整为10分钟)来减少更新的频率。

    例如:更新状态数据,积分为200,如下图所示

    77bdc1ffcf6e95585c209c6768fbcc32.png

    可以改造为,如下图所示。

    eea568c947f9e7b1ffc988395abf814a.png

    如果业务数据在短时间内更新过于频繁,比如1分钟更新100次,积分从100到10000,则可以根据时间频率批量提交。

    例如:更新状态数据,积分为100,如下图所示。

    5730db550b53c07a1579f706ffb34344.png

    无需生成100个事务(200条SQL语句)可以改造为2条SQL语句,如下图所示。

    e539ad6249309f7803d495d45a8adb20.png

    对于业务指标,比如更新频率细节信息,可以根据具体业务场景来讨论决定。

    优化设计方案3:架构层优化

    架构层优化其实就是我们认为的那种技术含量很高的工作,我们需要根据业务场景在架构层面引入一些新的花样来。

    2c0af6ee520be5a3a16ed8c00e90a5c6.png

    3.1.系统水平扩展场景

    3.1.1采用中间件技术,可以实现数据路由,水平扩展,常见的中间件有MyCAT,ShardingSphere,ProxySQL等

    11a3df4b5124d2db9359b6049782fb40.png

    3.1.2 采用读写分离技术,这是针对读需求的扩展,更侧重于状态表,在允许一定延迟的情况下,可以采用多副本的模式实现读需求的水平扩展,也可以采用中间件来实现,如MyCAT,ProxySQL,MaxScale,MySQL Router等

    c5a9954da4dc1af911b064dac60a3f0d.png

    3.1.3 采用负载均衡技术,常见的有LVS技术或者基于域名服务的Consul技术等

    3.2.兼顾OLTP+OLAP的业务场景,可以采用NewSQL,优先兼容MySQL协议的HTAP技术栈,如TiDB

    3.3.离线统计的业务场景,有几类方案可供选择。

    3.3.1 采用NoSQL体系,主要有两类,一类是适合兼容MySQL协议的数据仓库体系,常见的有Infobright或者ColumnStore,另外一类是基于列式存储,属于异构方向,如HBase技术

    3.3.2 采用数仓体系,基于MPP架构,如使用Greenplum统计,如T+1统计

    优化设计方案4:数据库优化

    数据库优化,其实可打的牌也不少,但是相对来说空间没有那么大了,我们来逐个说一下。

    65e8f27f2cd5cd16e5e0c52b221c078f.png

    4.1 事务优化

    根据业务场景选择事务模型,是否是强事务依赖

    对于事务降维策略,我们来举出几个小例子来。

    4.1.1 降维策略1:存储过程调用转换为透明的SQL调用

    对于新业务而言,使用存储过程显然不是一个好主意,MySQL的存储过程和其他商业数据库相比,功能和性能都有待验证,而且在目前轻量化的业务处理中,存储过程的处理方式太“重”了。

    有些应用架构看起来是按照分布式部署的,但在数据库层的调用方式是基于存储过程,因为存储过程封装了大量的逻辑,难以调试,而且移植性不高,这样业务逻辑和性能压力都在数据库层面了,使得数据库层很容易成为瓶颈,而且难以实现真正的分布式。

    所以有一个明确的改进方向就是对于存储过程的改造,把它改造为SQL调用的方式,可以极大地提高业务的处理效率,在数据库的接口调用上足够简单而且清晰可控。

    4.1.2 降维策略2:DDL操作转换为DML操作

    有些业务经常会有一种紧急需求,总是需要给一个表添加字段,搞得DBA和业务同学都挺累,可以想象一个表有上百个字段,而且基本都是name1,name2……name100,这种设计本身就是有问题的,更不用考虑性能了。究其原因,是因为业务的需求动态变化,比如一个游戏装备有20个属性,可能过了一个月之后就增加到了40个属性,这样一来,所有的装备都有40个属性,不管用没用到,而且这种方式也存在诸多的冗余。

    我们在设计规范里面也提到了一些设计的基本要素,在这些基础上需要补充的是,保持有限的字段,如果要实现这些功能的扩展,其实完全可以通过配置化的方式来实现,比如把一些动态添加的字段转换为一些配置信息。配置信息可以通过DML的方式进行修改和补充,对于数据入口也可以更加动态、易扩展。

    4.1.3 降维策略3:Delete操作转换为高效操作

    有些业务需要定期来清理一些周期性数据,比如表里的数据只保留一个月,那么超出时间范围的数据就要清理掉了,而如果表的量级比较大的情况下,这种Delete操作的代价实在太高,我们可以有两类解决方案来把Delete操作转换为更为高效的方式。

    第一种是根据业务建立周期表,比如按照月表、周表、日表等维度来设计,这样数据的清理就是一个相对可控而且高效的方式了。

    第二种方案是使用MySQL rename的操作方式,比如一张2千万的大表要清理99%的数据,那么需要保留的1%的数据我们可以很快根据条件过滤补录,实现“移形换位”。

    4.2 SQL优化

    其实相对来说需要的极简的设计,很多点都在规范设计里面了,如果遵守规范,八九不离十的问题都会杜绝掉,在此补充几点:

    4.2.1 SQL语句简化,简化是SQL优化的一大利器,因为简单,所以优越。

    4.2.2 尽可能避免或者杜绝多表复杂关联,大表关联是大表处理的噩梦,一旦打开了这个口子,越来越多的需求需要关联,性能优化就没有回头路了,更何况大表关联是MySQL的弱项,尽管Hash Join才推出,不要像掌握了绝对大杀器一样,在商业数据库中早就存在,问题照样层出不穷。

    4.2.3 SQL中尽可能避免反连接,避免半连接,这是优化器做得薄弱的一方面,什么是反连接,半连接?其实比较好理解,举个例子,not in ,not exists就是反连接,in,exists就是半连接,在千万级大表中出现这种问题,性能是几个数量级的差异。

    4.3 索引优化

    应该是大表优化中需要把握的一个度。

    4.3.1 首先必须有主键,规范设计中第一条就是,此处不接收反驳。

    4.3.2 其次,SQL查询基于索引或者唯一性索引,使得查询模型尽可能简单。

    4.3.3 最后,尽可能杜绝范围数据的查询,范围扫描在千万级大表情况下还是尽可能减少。

    优化设计方案4:管理优化

    这部分应该是在所有的解决方案中最容易被忽视的部分了,我放在最后,在此也向运维同事致敬,总是为很多认为本应该正常的问题尽职尽责(背锅)。

    8fc2fe06c99a589071061a82226c7444.png

    千万级大表的数据清理一般来说是比较耗时的,在此建议在设计中需要完善冷热数据分离的策略,可能听起来比较拗口,我来举一个例子,把大表的Drop 操作转换为可逆的DDL操作。

    Drop操作是默认提交的,而且是不可逆的,在数据库操作中都是跑路的代名词,MySQL层面目前没有相应的Drop操作恢复功能,除非通过备份来恢复,但是我们可以考虑将Drop操作转换为一种可逆的DDL操作。

    MySQL中默认每个表有一个对应的ibd文件,其实可以把Drop操作转换为一个rename操作,即把文件从testdb迁移到testdb_arch下面;从权限上来说,testdb_arch是业务不可见的,rename操作可以平滑的实现这个删除功能,如果在一定时间后确认可以清理,则数据清理对于已有的业务流程是不可见的,如下图所示。

    e7d360c6f1dd2044088fda16f7172adc.png

    此外,还有两个额外建议,一个是对于大表变更,尽可能考虑低峰时段的在线变更,比如使用pt-osc工具或者是维护时段的变更,就不再赘述了。

    最后总结一下,其实就是一句话:

    千万级大表的优化是根据业务场景,以成本为代价进行优化的,绝对不是孤立的一个层面的优化

    个人新书 《MySQL DBA工作笔记》

    个人公众号:jianrong-notes

    展开全文
  • 2020植树节作文500范文5篇 春天到了,她又吹响了口哨,将生机洒向大地唤醒万物:小河脱掉了冬天大衣,忙着运动。下面是精心精选植树节作文500,仅供参考,希望喜欢。 植树节作文500 一 今年植树节那天,...
  • 如何设计或优化千万级别大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集信息如下: 1.数据容量:1-3年内会大概多少条数据,每条数据大概...

    如何设计或优化千万级别的大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集的信息如下:

    1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节;

    2.数据项:是否有大字段,那些字段的值是否经常被更新;

    3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等;

    4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中;

    5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少?

    6.预计大表及相关联的SQL,每天总的执行量在何数量级?

    7.表中的数据:更新为主的业务 还是 查询为主的业务 ?

    8.打算采用什么数据库物理服务器,以及数据库服务器架构?

    9.并发如何?

    10.存储引擎选择InnoDB还是MyISAM?

    大致明白以上10个问题,至于如何设计此类的大表,应该什么都清楚了!

    至于优化若是指创建好的表,不能变动表结构的话,那建议InnoDB引擎,多利用点内存,减轻磁盘IO负载,因为IO往往是数据库服务器的瓶颈

    另外对优化索引结构去解决性能问题的话,建议优先考虑修改类SQL语句,使他们更快些,不得已只靠索引组织结构的方式,当然此话前提是, 索引已经创建的非常好,若是读为主,可以考虑打开query_cache,以及调整一些参数值:sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

    以及调整一些参数值:sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size

    案列一

    我现在的公司有三张表,是5亿的数据,每天张表每天的增量是100w

    每张表大概在10个columns左右

    下面是我做的测试和对比

    1.首先看engine,在大数据量情况下,在没有做分区的情况下

    mysiam比innodb在只读的情况下,效率要高13%左右

    2.在做了partition之后,你可以去读一下mysql的官方文档,其实对于partition,专门是对myisam做的优化,对于innodb,所有的数据是存在ibdata里面的,所以即使你可以看到schema变了,其实没有本质的变化

    在分区出于同一个physical disk下面的情况下,提升大概只有1%

    在分区在不同的physical disk下,我分到了三个不同的disks下,提升大概在3%,其实所谓的吞吐量,由很多因素决定的,比如你的explain parition时候可以看到,record在那一个分区,如果每个分区都有,其实本质上没有解决读的问题,这样只会提升写的效率。

    另外一个问题在于,分区,你怎么分,如果一张表,有三个column都是经常被用于做查询条件的,其实是一件很悲惨的事情,因为你没有办法对所有的sql做针对性的分区,如果你只是如mysql官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你

    3.表主要用来读还是写,其实这个问题是不充分的,应该这样问,你在写入的时候,同时并发的查询多么?我的问题还比较简单,因为mongodb的shredding支持不能,在crush之后,还是回到mysql,所以在通常情况下,9am-9pm,写入的情况很多,这个时候我会做一个view,view是基于最近被插入或者经常被查询的,通过做view来分离读取,就是说写是在table上的,读在进行逻辑判断前是在view上操作的

    4.做一些archive table,比如先对这些大表做很多已有的统计分析,然后通过已有的分析+增量来解决

    5.如果你用mysiam,还有一个问题你要注意,如果你的.configure的时候,加了一个max index length参数的时候,当你的record数大于制定长度的时候,这个index会被disable

    案列二

    任何偏离业务场景的优化都是耍流氓,如果是订单表,主要 通过订单id来查询 订单信息,则可以对这样的表 进行垂直分库,每个库表容量500万条,按订单号维度 给拆分到多个库,而在查询的时候,使用订单号查询,通过某个业务规则,直接定位到要查询的目标库。或者通过用户ID 、日期维度 进行分库,但是千万要注意,查询时携带 分库的条件。 如果是CRM系统 ,不直接使用订单号直接查询,而是一个范围查询,返回一个列表集合,而你还继续执着于分库分表就能解决你的性能问题,这样你要对各个库的查询结果集进行union,数据库的性能非但不能提高反而会适得其反!

    解决方案

    • 首先,任何优化,都需要你了解你的业务,了解你的数据。
    • QPS要到多少?- 带宽及存储够的情况下,单机几千QPS妥妥的。
    • 读写比例如何?- 读多写少和写多读少,优化方法是有很大差别的。设置于只读场景,果断压缩。
    • 数据是否快速增长?- 基本就是QPS的要求。
    • 数据及服务的SLA要到多少?- 数据需不需要强一致?HA做到什么程度?
    • 诸如此类。

    不同的场景有不同的侧重,解决方案是不同的。而对于一些典型的场景可能会有成熟的解决方案。

    题主已注明“千万级”,因此以下假设题主为最常见的场景: 大量数据,QPS要求高,读多写少,数据快速增长,SLA要求高 。

    • 其次,说优化的方法。

    主要从三个维度说:Why, How, When。

    0. sql vs nosql

    有些跑题,但也是很重要的一方面。

    Why: nosql天生分布,而且大多针对某种类型的数据、某种使用场景做过优化。

    比如大批量的监控数据,用mysql存费时费力,可以选择mongo,甚至时间序列数据库,存取会有量级提升。

    How: 找对应解决方案。

    When: 有足够诱惑 - 针对使用场景,有成熟解决方案,效率获得大量提升。

    1. 优化shema、sql语句+索引

    Why: 再好的MySQL架构也扛不住一个频繁的垃圾查询。不合理的schema设计也会导致数据存取慢。索引的作用不必多说,但如innodb下,错的索引带来的可能不只是查询变慢而已。

    How: 设计阶段就需要预计QPS及数据规模,参考业务场景对数据的要求,合理设计表结构(参考mysql在线DDL问题),甚至违反设计范式做到适当冗余。生产环境分析慢日志,优化语句。索引的设计需要知道索引是怎么用的,比如innodb的加锁机制。

    When: 这个不仅仅是第一个要考虑的,而应该是需要持续去优化的。特别是要参考业务。但实际环境中如果是这个的问题,那一般比较幸运了,因为一般已经优化过很多了。实际中遇到的一般是更深的问题。

    2. 缓存

    缓存没有那么简单。

    缓存对于应用不是完全透明的,除非你用Django这种成熟框架,而且缓存粒度很大,但实际。。。像python,最少也得加几个装饰器。

    如何保证缓存里面的数据是始终正确的?写数据前失效缓存还是写数据后?

    缓存挂了或者过冷,流量压到后端mysql了怎么办?

    缓存也不是万能的。写多读少,命中率会很低。

    How: memcache用做缓存,redis用于需要持久化的场景。(redis能不能完全取代memcache?呵呵。。)

    还可以使用mysql自带的query cache,对应用基本完全透明。但会受限于本机。而且只缓存查询结果,mc和redis可以缓存一些加工后的数据。

    而且数据量大、QPS大的情况下,也需要考虑分片及HA的问题。如果有一个数据过热,把一个节点压垮了怎么办?

    When: 基本上大多数读多写少的场景都能用,写多的情况下可能需要考虑考虑。

    3. 复制及读写分离(做主从复制或主主复制,读写分离,可以在应用层做,效率高,也可以用三方工具,第三方工具推荐360的atlas,其它的要么效率不高,要么没人维护)

    Why: 这个其实是大多数场景下都必须的。因为复制可以实现备份、高可用、负载均衡。就算嫌麻烦不做负载均衡,那备份下总是要的吧?既然已经备份了,何不加个LVS+HAProxy做下HA?顺便稍微修改下应用,读写分离也就成了。

    How: 节点少的情况下,主备。前面加Keepalived+HAProxy等组件,失效自动切换。读写分离可能需要修改下应用。

    节点多的情况下,一是考虑多级备份,减轻主的压力。其次可以引入第三方组件,接管主节点的备份工作。

    主主不是很推荐。一是需要考虑数据冲突的情况,比如错开id,同时操作数据后冲突解决。其次如果强一致会导致延迟增加,如果有节点挂了,需要等到超时才返回。

    When: 主备几乎大多数场景。甚至不论数据大小。高可用对应用透明,为啥不用?主主麻烦,建议先用切分。

    4. 切分

    包括垂直切分和水平切分,实现方式上又包括分库、分表。

    虽然有些难度,但还是推荐常用的。

    Why: 垂直切分保证业务的独立性,防止不同业务争抢资源,毕竟业务是有优先级的。

    水平切分主要用于突破单机瓶颈。除了主主外,只有切分能真正做到将负载分配下去。

    切分后也可对不同片数据进行不同优化。如按时间切分,超过一定时间数据不允许修改,就可以引入压缩了,数据传输及读取减少很多。

    How: 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外,其余实现不是很复杂。有第三方组件可用,但通用高效又灵活的方式,还是自己写client。

    When: 垂直切分一般都要做,只不过业务粒度大小而已。

    分库有是经常用的,就算当前压力小,也尽量分出几个逻辑库出来。等规模上去了,很方便就迁移扩展。

    水平拆分有一定难度,但如果将来一定会到这个规模,又可能用到,建议越早做越好。因为对应用的改动较大,而且迁移成本高。

    综上,数据库设计要面向现代化,面向世界,面向未来。。。

    在一般运维的角度来看,我们什么情况下需要考虑分库分表?

    首先说明,这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去,而不是类似分区表的原地切分。

    原则零:能不分就不分。

    是的,MySQL 是关系数据库,数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度,数据库除了承载数据的存储和访问外,协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并,查询或者更新条件的分离,事务的分离等等多种后果,业务实现的复杂程度往往会翻倍或者指数级上升。所以,在分库分表之前,不要为分而分,去做其他力所能及的事情吧,例如升级硬件,升级,升级网络,升级数据库版本,读写分离,负载均衡等等。所有分库分表的前提是,这些你已经尽力了。

    原则一:数据量太大,正常的运维影响正常业务访问。

    这里说的运维,例如:

    (1)对数据库的备份。如果单表或者单个实例太大,在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据,网络传输占用50MB的时候,需要20000秒才能传输完毕,在此整个过程中的维护风险都是高于平时的。我们在Qunar的做法是给所有的数据库机器添加第二块网卡,用来做备份,或者SST,Group Communication等等各种内部的数据传输。1T的数据的备份,也会占用大量的磁盘IO,如果是SSD还好,当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘,则在不限流的情况下去执行xtrabackup,该实例基本不可用。

    (2)对数据表的修改。如果某个表过大,对此表做DDL的时候,MySQL会锁住全表,这个时间可能很长,在这段时间业务不能访问此表,影响甚大。解决的办法有类似腾讯游戏DBA自己改造的可以在线秒改表,不过他们目前也只是能添加字段而已,对别的DDL还是无效;或者使用pt-online-schema-change,当然在使用过程中,它需要建立触发器和影子表,同时也需要很长很长的时间,在此操作过程中的所有时间,都可以看做是风险时间。把数据表切分,总量减小,有助于改善这种风险。

    (3)整个表热点,数据访问和更新频繁,经常有锁等待,你又没有能力去修改源码,降低锁的粒度,那么只会把其中的数据物理拆开,用空间换时间,变相降低访问压力。

    原则二:表设计不合理,需要对某些字段垂直拆分

    这里举一个例子,如果你有一个用户表,在最初设计的时候可能是这样:

    table :users

    id bigint 用户的ID

    name varchar 用户的名字

    last_login_time datetime 最近登录时间

    personal_info text 私人信息

    xxxxx 其他信息字段。

    一般的users表会有很多字段,我就不列举了。如上所示,在一个简单的应用中,这种设计是很常见的。但是:

    设想情况一:你的业务中彩了,用户数从100w飙升到10个亿。你为了统计活跃用户,在每个人登录的时候都会记录一下他的最近登录时间。并且的用户活跃得很,不断的去更新这个login_time,搞的你的这个表不断的被update,压力非常大。那么,在这个时候,只要考虑对它进行拆分,站在业务的角度,最好的办法是先把last_login_time拆分出去,我们叫它 user_time。这样做,业务的代码只有在用到这个字段的时候修改一下就行了。如果你不这么做,直接把users表水平切分了,那么,所有访问users表的地方,都要修改。或许你会说,我有proxy,能够动态merge数据。到目前为止我还从没看到谁家的proxy不影响性能的。

    设想情况二:personal_info这个字段本来没啥用,你就是让用户注册的时候填一些个人爱好而已,基本不查询。一开始的时候有它没它无所谓。但是到后来发现两个问题,一,这个字段占用了大量的空间,因为是text嘛,有很多人喜欢长篇大论地介绍自己。更糟糕的是二,不知道哪天哪个产品经理心血来潮,说允许个人信息公开吧,以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下,对此字段的访问大幅度增加。数据库压力瞬间抗不住了,这个时候,只好考虑对这个表的垂直拆分了。

    原则三:某些数据表出现了无穷增长

    例子很好举,各种的评论,消息,日志记录。这个增长不是跟人口成比例的,而是不可控的,例如微博的feed的广播,我发一条消息,会扩散给很多很多人。虽然主体可能只存一份,但不排除一些索引或者路由有这种存储需求。这个时候,增加存储,提升机器配置已经苍白无力了,水平切分是最佳实践。拆分的标准很多,按用户的,按时间的,按用途的,不在一一举例。

    原则四:安全性和可用性的考虑

    这个很容易理解,鸡蛋不要放在一个篮子里,我不希望我的数据库出问题,但我希望在出问题的时候不要影响到100%的用户,这个影响的比例越少越好,那么,水平切分可以解决这个问题,把用户,库存,订单等等本来同统一的资源切分掉,每个小的数据库实例承担一小部分业务,这样整体的可用性就会提升。这对Qunar这样的业务还是比较合适的,人与人之间,某些库存与库存之间,关联不太大,可以做一些这样的切分。

    原则五:业务耦合性考虑

    这个跟上面有点类似,主要是站在业务的层面上,我们的火车票业务和烤羊腿业务是完全无关的业务,虽然每个业务的数据量可能不太大,放在一个MySQL实例中完全没问题,但是很可能烤羊腿业务的DBA 或者开发人员水平很差,动不动给你出一些幺蛾子,直接把数据库搞挂。这个时候,火车票业务的人员虽然技术很优秀,工作也很努力,照样被老板打屁股。解决的办法很简单:惹不起,躲得起。

    20条规则摘要如下:

    规则1:一般情况可以选择MyISAM存储引擎,如果需要事务支持必须使用InnoDB存储引擎。

    规则2:命名规则。

    规则3:数据库字段类型定义

    1. 经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段,如用TIMESTAMP(4个字节,最小值1970-01-01 00:00:00)代替Datetime(8个字节,最小值1001-01-01 00:00:00),通过整型替代浮点型和字符型
    2. 变长字段使用varchar,不要使用char
    3. 对于二进制多媒体数据,流水队列数据(如日志),超大文本数据不要放在数据库字段中

    规则4:业务逻辑执行过程必须读到的表中必须要有初始的值。避免业务读出为负或无穷大的值导致程序失败

    规则5:并不需要一定遵守范式理论,适度的冗余,让Query尽量减少Join

    规则6:访问频率较低的大字段拆分出数据表。有些大字段占用空间多,访问频率较其他字段明显要少很多,这种情况进行拆分,频繁的查询中就不需要读取大字段,造成IO资源的浪费。

    规则7: 水平分表,这个我还是建议 三思,搞不好非但不能提升性能反而多了很多的join和磁盘IO,开发起来也麻烦,有很多的业务就是要求一次查询大部分的字段 看你业务场景了。大表可以考虑水平拆分。大表影响查询效率,根据业务特性有很多拆分方式,像根据时间递增的数据,可以根据时间来分。以id划分的数据,可根据id%数据库个数的方式来拆分。

    规则8:业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的,业务不需要的不要建索引,不允许在联合索引(或主键)中存在多于的字段。特别是该字段根本不会在条件语句中出现。

    规则9:唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引,不能唯一确定一条记录,为了提高查询效率建普通索引。

    规则10:业务使用的表,有些记录数很少,甚至只有一条记录,为了约束的需要,也要建立索引或者设置主键。

    规则11:对于取值不能重复,经常作为查询条件的字段,应该建唯一索引(主键默认唯一索引),并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。

    规则12:对于经常查询的字段,其值不唯一,也应该考虑建立普通索引,查询语句中该字段条件置于第一个位置,对联合索引处理的方法同样。

    规则13:业务通过不唯一索引访问数据时,需要考虑通过该索引值返回的记录稠密度,原则上可能的稠密度最大不能高于0.2,如果稠密度太大,则不合适建立索引了。

    规则14:需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

    注意:索引的顺势不正确也可能导致严重的后果。

    规则15:表中的多个字段查询作为查询条件,不含有其他索引,并且字段联合值不重复,可以在这多个字段上建唯一的联合索引,假设索引字段为 (a1,a2,...an),则查询条件(a1 op val1,a2 op val2,...am op valm)m<=n,可以用到索引,查询条件中字段的位置与索引中的字段位置是一致的。

    规则16:联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))。

    规则17:重要业务访问数据表时。但不能通过索引访问数据时,应该确保顺序访问的记录数目是有限的,原则上不得多于10。

    规则18:合理构造Query语句,慢SQL监控,检查是否有大量的的子查询和关联查询 嵌套查询等,尽量避免使用这些查询, 使用连接(JOIN)来代替子查询(Sub-Queries),使用联合(UNION)来代替手动创建的临时表。

    规则19:应用系统的优化。

    规则20:可以结合redis,memcache等缓存服务,把这些复杂的sql进行拆分, 充分利用二级缓存 ,减少数据库IO操作。对数据库连接池,mybatis,hiberante二级缓存充分利用上。尽量使用顺序IO代替随机IO。合理使用索引,尽量避免全表扫描。

     

    展开全文
  • 如何设计或优化千万级别大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集信息如下: 1.数据容量:1-3年内会大概有多少条数据,每条数据...

    程序员小新人学习 2018-07-26 08:18:57

    思考

    如何设计或优化千万级别的大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集的信息如下:

    1.数据的容量:1-3年内会大概有多少条数据,每条数据大概多少字节;

    2.数据项:是否有大字段,那些字段的值是否经常被更新;

    3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等;

    4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中;

    5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少?

    6.预计大表及相关联的SQL,每天总的执行量在何数量级?

    7.表中的数据:更新为主的业务 还是 查询为主的业务 ?

    8.打算采用什么数据库物理服务器,以及数据库服务器架构?

    9.并发如何?

    10.存储引擎选择InnoDB还是MyISAM?

    大致明白以上10个问题,至于如何设计此类的大表,应该什么都清楚了!

    至于优化,若是指创建好的表,不能变动表结构的话,那建议InnoDB引擎,多利用点内存,减轻磁盘IO负载,因为IO往往是数据库服务器的瓶颈

    另外对优化索引结构去解决性能问题的话,建议优先考虑修改类SQL语句,使他们更快些,不得已只靠索引组织结构的方式,当然此话前提是, 索引已经创建的非常好,若是读为主,可以考虑打开query_cache,以及调整一些参数值:

    sort_buffer_size ,  read_buffer_size ,  read_rnd_buffer_size , join_buffer_size

    案列一

    我现在的公司有三张表,是5亿的数据,每天张表每天的增量是100w

    每张表大概在10个columns左右

    下面是我做的测试和对比

    1.首先看engine,在大数据量情况下,在没有做分区的情况下

    mysiam比innodb在只读的情况下,效率要高13%左右

    2.在做了partition之后,你可以去读一下mysql的官方文档,其实对于partition,专门是对myisam做的优化,对于innodb,所有的数据是存在ibdata里面的,所以即使你可以看到schema变了,其实没有本质的变化

    在分区出于同一个physical disk下面的情况下,提升大概只有1%

    在分区在不同的physical disk下,我分到了三个不同的disks下,提升大概在3%。

    其实所谓的吞吐量,由很多因素决定的,比如你的explain parition时候可以看到,record在那一个分区,如果每个分区都有,其实本质上没有解决读的问题,这样只会提升写的效率。

    另外一个问题在于,分区,你怎么分,如果一张表,有三个column都是经常被用于做查询条件的,其实是一件很悲惨的事情,因为你没有办法对所有的sql做针对性的分区,如果你只是如mysql官方文档上说的,只对时间做一个分区,而且你也只用时间查询的话,恭喜你

    3.表主要用来读还是写,其实这个问题是不充分的,应该这样问,你在写入的时候,同时并发的查询多么?我的问题还比较简单,因为mongodb的shredding不能支持,在crush之后,还是回到mysql,所以在通常情况下,9am-9pm,写入的情况很多,这个时候我会做一个view,view是基于最近被插入或者经常被查询的,通过做view来分离读取,就是说写是在table上的,读在进行逻辑判断前是在view上操作的

    4做一些archive table,比如先对这些大表做很多已有的统计分析,然后通过已有的分析+增量来解决

    5如果你用mysiam,还有一个问题你要注意,如果你的.configure的时候,加了一个max index length参数的时候,当你的record数大于制定长度的时候,这个index会被disable

    案列二

    任何偏离业务场景的优化都是耍流氓,如果是订单表,主要通过订单id来查询订单信息,则可以对这样的表 进行垂直分库,每个库表容量500万条,按订单号维度 给拆分到多个库,而在查询的时候,使用订单号查询,通过某个业务规则,直接定位到要查询的目标库。或者通过用户ID 、日期维度 进行分库,但是千万要注意,查询时携带 分库的条件。 如果是CRM系统 ,不直接使用订单号直接查询,而是一个范围查询,返回一个列表集合,而你还继续执着于分库分表就能解决你的性能问题,这样你要对各个库的查询结果集进行union,数据库的性能非但不能提高反而会适得其反!

    解决方案

    • 首先,任何优化,都需要你了解你的业务,了解你的数据。
    • QPS要到多少?- 带宽及存储够的情况下,单机几千QPS妥妥的。
    • 读写比例如何?- 读多写少和写多读少,优化方法是有很大差别的。设置于只读场景,果断压缩。
    • 数据是否快速增长?- 基本就是QPS的要求。
    • 数据及服务的SLA要到多少?- 数据需不需要强一致?HA做到什么程度?
    • 诸如此类。

    不同的场景有不同的侧重,解决方案是不同的。而对于一些典型的场景可能会有成熟的解决方案。

    题主已注明“千万级”,因此以下假设题主为最常见的场景:大量数据,QPS要求高,读多写少,数据快速增长,SLA要求高

    • 其次,说优化的方法。

    主要从三个维度说:Why, How, When。

    1. sql  VS  nosql

    有些跑题,但也是很重要的一方面。

    Why: nosql天生分布,而且大多针对某种类型的数据、某种使用场景做过优化。

    比如大批量的监控数据,用mysql存费时费力,可以选择mongo,甚至时间序列数据库,存取会有量级提升。

    How: 找对应解决方案。

    When: 有足够诱惑 - 针对使用场景,有成熟解决方案,效率获得大量提升。

    2. 优化shema、sql语句+索引

    Why: 再好的MySQL架构也扛不住一个频繁的垃圾查询。不合理的schema设计也会导致数据存取慢。索引的作用不必多说,但如innodb下,错的索引带来的可能不只是查询变慢而已。

    How: 设计阶段就需要预计QPS及数据规模,参考业务场景对数据的要求,合理设计表结构(参考mysql在线DDL问题),甚至违反设计范式做到适当冗余。生产环境分析慢日志,优化语句。索引的设计需要知道索引是怎么用的,比如innodb的加锁机制。

    When: 这个不仅仅是第一个要考虑的,而应该是需要持续去优化的。特别是要参考业务。但实际环境中如果是这个的问题,那一般比较幸运了,因为一般已经优化过很多了。实际中遇到的一般是更深的问题。

    3. 缓存

    缓存没有那么简单。

    缓存对于应用不是完全透明的,除非你用Django这种成熟框架,而且缓存粒度很大,但实际。。。像python,最少也得加几个装饰器。

    如何保证缓存里面的数据是始终正确的?写数据前失效缓存还是写数据后?

    缓存挂了或者过冷,流量压到后端mysql了怎么办?

    缓存也不是万能的。写多读少,命中率会很低。

    How: memcache用做缓存,redis用于需要持久化的场景。(redis能不能完全取代memcache?呵呵。。)

    还可以使用mysql自带的query cache,对应用基本完全透明。但会受限于本机。而且只缓存查询结果,mc和redis可以缓存一些加工后的数据。

    而且数据量大、QPS大的情况下,也需要考虑分片及HA的问题。如果有一个数据过热,把一个节点压垮了怎么办?

    When: 基本上大多数读多写少的场景都能用,写多的情况下可能需要考虑考虑。

    4. 复制及读写分离(做主从复制或主主复制,读写分离,可以在应用层做,效率高,也可以用三方工具,第三方工具推荐360的atlas,其它的要么效率不高,要么没人维护)

    Why: 这个其实是大多数场景下都必须的。因为复制可以实现备份、高可用、负载均衡。就算嫌麻烦不做负载均衡,那备份下总是要的吧?既然已经备份了,何不加个LVS+HAProxy做下HA?顺便稍微修改下应用,读写分离也就成了。

    How: 节点少的情况下,主备。前面加Keepalived+HAProxy等组件,失效自动切换。读写分离可能需要修改下应用。

    节点多的情况下,一是考虑多级备份,减轻主的压力。其次可以引入第三方组件,接管主节点的备份工作。

    主主不是很推荐。一是需要考虑数据冲突的情况,比如错开id,同时操作数据后冲突解决。其次如果强一致会导致延迟增加,如果有节点挂了,需要等到超时才返回。

    When: 主备几乎大多数场景。甚至不论数据大小。高可用对应用透明,为啥不用?主主麻烦,建议先用切分。

    5. 切分

    包括垂直切分和水平切分,实现方式上又包括分库、分表。

    虽然有些难度,但还是推荐常用的。

    Why: 垂直切分保证业务的独立性,防止不同业务争抢资源,毕竟业务是有优先级的。

    水平切分主要用于突破单机瓶颈。除了主主外,只有切分能真正做到将负载分配下去。

    切分后也可对不同片数据进行不同优化。如按时间切分,超过一定时间数据不允许修改,就可以引入压缩了,数据传输及读取减少很多。

    How: 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外,其余实现不是很复杂。有第三方组件可用,但通用高效又灵活的方式,还是自己写client。

    When: 垂直切分一般都要做,只不过业务粒度大小而已。

    分库有是经常用的,就算当前压力小,也尽量分出几个逻辑库出来。等规模上去了,很方便就迁移扩展。

    水平拆分有一定难度,但如果将来一定会到这个规模,又可能用到,建议越早做越好。因为对应用的改动较大,而且迁移成本高。

    综上,数据库设计要面向现代化,面向世界,面向未来。。。

    在一般运维的角度来看,我们什么情况下需要考虑分库分表?

    首先说明,这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去,而不是类似分区表的原地切分。

    原则零:能不分就不分。

    是的,MySQL 是关系数据库,数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度,数据库除了承载数据的存储和访问外,协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并,查询或者更新条件的分离,事务的分离等等多种后果,业务实现的复杂程度往往会翻倍或者指数级上升。所以,在分库分表之前,不要为分而分,去做其他力所能及的事情吧,例如升级硬件,升级,升级网络,升级数据库版本,读写分离,负载均衡等等。所有分库分表的前提是,这些你已经尽力了。

    原则一:数据量太大,正常的运维影响正常业务访问。

    这里说的运维,例如:

    (1)对数据库的备份。如果单表或者单个实例太大,在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据,网络传输占用50MB的时候,需要20000秒才能传输完毕,在此整个过程中的维护风险都是高于平时的。我们在Qunar的做法是给所有的数据库机器添加第二块网卡,用来做备份,或者SST,Group Communication等等各种内部的数据传输。1T的数据的备份,也会占用大量的磁盘IO,如果是SSD还好,当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘,则在不限流的情况下去执行xtrabackup,该实例基本不可用。

    (2)对数据表的修改。如果某个表过大,对此表做DDL的时候,MySQL会锁住全表,这个时间可能很长,在这段时间业务不能访问此表,影响甚大。解决的办法有类似腾讯游戏DBA自己改造的可以在线秒改表,不过他们目前也只是能添加字段而已,对别的DDL还是无效;或者使用pt-online-schema-change,当然在使用过程中,它需要建立触发器和影子表,同时也需要很长很长的时间,在此操作过程中的所有时间,都可以看做是风险时间。把数据表切分,总量减小,有助于改善这种风险。

    (3)整个表热点,数据访问和更新频繁,经常有锁等待,你又没有能力去修改源码,降低锁的粒度,那么只会把其中的数据物理拆开,用空间换时间,变相降低访问压力。

    原则二:表设计不合理,需要对某些字段垂直拆分

    这里举一个例子,如果你有一个用户表,在最初设计的时候可能是这样:

    table :users

    id bigint 用户的ID

    name varchar 用户的名字

    last_login_time datetime 最近登录时间

    personal_info text 私人信息

    xxxxx 其他信息字段。

    一般的users表会有很多字段,我就不列举了。如上所示,在一个简单的应用中,这种设计是很常见的。但是:

    设想情况一:你的业务中彩了,用户数从100w飙升到10个亿。你为了统计活跃用户,在每个人登录的时候都会记录一下他的最近登录时间。并且的用户活跃得很,不断的去更新这个login_time,搞的你的这个表不断的被update,压力非常大。那么,在这个时候,只要考虑对它进行拆分,站在业务的角度,最好的办法是先把last_login_time拆分出去,我们叫它 user_time。这样做,业务的代码只有在用到这个字段的时候修改一下就行了。如果你不这么做,直接把users表水平切分了,那么,所有访问users表的地方,都要修改。或许你会说,我有proxy,能够动态merge数据。到目前为止我还从没看到谁家的proxy不影响性能的。

    设想情况二:personal_info这个字段本来没啥用,你就是让用户注册的时候填一些个人爱好而已,基本不查询。一开始的时候有它没它无所谓。但是到后来发现两个问题,一,这个字段占用了大量的空间,因为是text嘛,有很多人喜欢长篇大论地介绍自己。更糟糕的是二,不知道哪天哪个产品经理心血来潮,说允许个人信息公开吧,以方便让大家更好的相互了解。那么在所有人猎奇窥私心理的影响下,对此字段的访问大幅度增加。数据库压力瞬间抗不住了,这个时候,只好考虑对这个表的垂直拆分了。

    原则三:某些数据表出现了无穷增长

    例子很好举,各种的评论,消息,日志记录。这个增长不是跟人口成比例的,而是不可控的,例如微博的feed的广播,我发一条消息,会扩散给很多很多人。虽然主体可能只存一份,但不排除一些索引或者路由有这种存储需求。这个时候,增加存储,提升机器配置已经苍白无力了,水平切分是最佳实践。拆分的标准很多,按用户的,按时间的,按用途的,不在一一举例。

    原则四:安全性和可用性的考虑

    这个很容易理解,鸡蛋不要放在一个篮子里,我不希望我的数据库出问题,但我希望在出问题的时候不要影响到100%的用户,这个影响的比例越少越好,那么,水平切分可以解决这个问题,把用户,库存,订单等等本来同统一的资源切分掉,每个小的数据库实例承担一小部分业务,这样整体的可用性就会提升。这对Qunar这样的业务还是比较合适的,人与人之间,某些库存与库存之间,关联不太大,可以做一些这样的切分。

    原则五:业务耦合性考虑

    这个跟上面有点类似,主要是站在业务的层面上,我们的火车票业务和烤羊腿业务是完全无关的业务,虽然每个业务的数据量可能不太大,放在一个MySQL实例中完全没问题,但是很可能烤羊腿业务的DBA 或者开发人员水平很差,动不动给你出一些幺蛾子,直接把数据库搞挂。这个时候,火车票业务的人员虽然技术很优秀,工作也很努力,照样被老板打屁股。解决的办法很简单:惹不起,躲得起。

    20条规则摘要如下:

    规则1一般情况可以选择MyISAM存储引擎,如果需要事务支持必须使用InnoDB存储引擎。

    规则2:命名规则。

    规则3:数据库字段类型定义

    1. 经常需要计算和排序等消耗CPU的字段,应该尽量选择更为迅速的字段,如用TIMESTAMP(4个字节,最小值1970-01-01 00:00:00)代替Datetime(8个字节,最小值1001-01-01 00:00:00),通过整型替代浮点型和字符型
    2. 变长字段使用varchar,不要使用char
    3. 对于二进制多媒体数据,流水队列数据(如日志),超大文本数据不要放在数据库字段中

    规则4:业务逻辑执行过程必须读到的表中必须要有初始的值。避免业务读出为负或无穷大的值导致程序失败

    规则5:并不需要一定遵守范式理论,适度的冗余,让Query尽量减少Join

    规则6:访问频率较低的大字段拆分出数据表。有些大字段占用空间多,访问频率较其他字段明显要少很多,这种情况进行拆分,频繁的查询中就不需要读取大字段,造成IO资源的浪费。

    规则7: 水平分表,这个我还是建议 三思,搞不好非但不能提升性能反而多了很多的join和磁盘IO,开发起来也麻烦,有很多的业务就是要求一次查询大部分的字段 看你业务场景了。大表可以考虑水平拆分。大表影响查询效率,根据业务特性有很多拆分方式,像根据时间递增的数据,可以根据时间来分。以id划分的数据,可根据id%数据库个数的方式来拆分。

    规则8:业务需要的相关索引是根据实际的设计所构造sql语句的where条件来确定的,业务不需要的不要建索引,不允许在联合索引(或主键)中存在多于的字段。特别是该字段根本不会在条件语句中出现。

    规则9:唯一确定一条记录的一个字段或多个字段要建立主键或者唯一索引,不能唯一确定一条记录,为了提高查询效率建普通索引。

    规则10:业务使用的表,有些记录数很少,甚至只有一条记录,为了约束的需要,也要建立索引或者设置主键。

    规则11:对于取值不能重复,经常作为查询条件的字段,应该建唯一索引(主键默认唯一索引),并且将查询条件中该字段的条件置于第一个位置。没有必要再建立与该字段有关的联合索引。

    规则12:对于经常查询的字段,其值不唯一,也应该考虑建立普通索引,查询语句中该字段条件置于第一个位置,对联合索引处理的方法同样。

    规则13:业务通过不唯一索引访问数据时,需要考虑通过该索引值返回的记录稠密度,原则上可能的稠密度最大不能高于0.2,如果稠密度太大,则不合适建立索引了。

    规则14:需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

    注意:索引的顺势不正确也可能导致严重的后果。

    规则15:表中的多个字段查询作为查询条件,不含有其他索引,并且字段联合值不重复,可以在这多个字段上建唯一的联合索引,假设索引字段为 (a1,a2,...an),则查询条件(a1 op val1,a2 op val2,...am op valm)m<=n,可以用到索引,查询条件中字段的位置与索引中的字段位置是一致的。

    规则16:联合索引的建立原则(以下均假设在数据库表的字段a,b,c上建立联合索引(a,b,c))。

    规则17:重要业务访问数据表时。但不能通过索引访问数据时,应该确保顺序访问的记录数目是有限的,原则上不得多于10。

    规则18:合理构造Query语句,慢SQL监控,检查是否有大量的的子查询和关联查询 嵌套查询等,尽量避免使用这些查询,使用连接(JOIN)来代替子查询(Sub-Queries),使用联合(UNION)来代替手动创建的临时表。

    规则19:应用系统的优化。

    规则20:可以结合redis,memcache等缓存服务,把这些复杂的sql进行拆分,充分利用二级缓存,减少数据库IO操作。对数据库连接池,mybatis,hiberante二级缓存充分利用上。尽量使用顺序IO代替随机IO。合理使用索引,尽量避免全表扫描。

    MySQL 千万级的大表要怎么优化(读写分离、水平拆分、垂直拆分)

    展开全文
  • 如何设计或优化千万级别大表?此外无其他信息,个人觉得这个话题有点范,就只好简单说下该如何做,对于一个存储设计,必须考虑业务特点,收集信息如下: 1.数据容量:1-3年内会大概多少条数据,每条数据大概...
  • 关于和平作文倡议书_和平倡议书作文怎么写 人们多么希望:橄榄树在大地上自由地生长,任由和平之舟搁浅在荒滩上!在这个人类世世代代、生生不息古老星球上,战争,终会被划上句号,彻底结束,而和平则能永远...
  • 常用优化sql----突出快,使完成操作时间最短 1、用索引提高效率: 2、选择有效率表名顺序,及数据结构及字段; 3、使用DECODE函数可以避免重复扫描相同记录或重复连接相同表; 4、删除重复记; ...
  • 前言:前一段时间看到有博友了爬虫去刷新博客访问量一篇文章,当时还觉得蛮有意思,就保存了一下,但是当我昨天准备复现时候居然发现文章404了。所以本篇文章仅供学习交流,严禁用于商业用途当我在文章...
  • 前言:前一段时间看到有博友了爬虫去刷新博客访问量一篇文章,当时还觉得蛮有意思,就保存了一下,但是当我昨天准备复现时候居然发现文章404了。所以本篇文章仅供学习交流,严禁用于商业用途当我在文章...
  • 原来问题:请问一下,在SQL中怎么来做可以快速生成1千万个9位英文(大写)随机不重复号码,非常着急,谢谢啦。自己写的一个解决方法: DECLARE@iintCREATETABLE#AZ(Xnchar(1))SET@i=65WHILE@i<=90...
  • 前言:前一段时间看到有博友了爬虫去刷新博客访问量一篇文章,当时还觉得蛮有意思,就保存了一下,但是当我昨天准备复现时候居然发现文章404了。 所以本篇文章仅供学习交流,严禁用于商业用途 当我在文章...
  • 前言:前一段时间看到有博友了爬虫去刷新博客访问量一篇文章,当时还觉得蛮有意思,就保存了一下,但是当我昨天准备复现时候居然发现文章404了。所以本篇文章仅供学习交流,严禁用于商业用途当我在文章...
  • 前言:前一段时间看到有博友了爬虫去刷新博客访问量一篇文章,当时还觉得蛮有意思,就保存了一下,但是当我昨天准备复现时候居然发现文章404了。所以本篇文章仅供学习交流,严禁用于商业用途当我在文章...
  • 鸿合电子白板是一款配合鸿合交互平板使用教学辅助工具,支持手写功能,那你知道鸿合电子白板怎么校准吗?接下来我们一起往下看看鸿合电子白板校准方法吧。方法步骤1、电脑右下角找到白板图标,右键点击服务...
  • 大四学生小杨,为了省去撰写毕业论文时间,只要在学术资源库文献中看到能够直接引用段落,就大篇幅复制、粘贴,不能直接引用,便在原作者内容上直接进行修改,不到两个小时,就完成了一篇8000字的论文。...
  • 区块链怎么赚钱

    2018-09-01 09:15:58
    在世界经济历史中,已经发生过一次又一次危机,以后还会发生危机,如果世界发生不可预期和完全失控金融货币危机,数字货币很可能将是一个重大解救方案,所以,千万不要低估这类货币对人类潜在价值。...
  • Thinkpad T470 拆装字母键帽支架

    千次阅读 2020-03-16 17:40:20
    该文写的非常不错,但是我在装键帽支架时候遇到了一个问题那就是:圆形支架装不到方形支架上面,卡扣和文中不同。 (哇,我已经拆卸下来,也知道怎么安装,但就是这两个支架组合不到一起。真烦,如图!) ...
  • ASP.NET treeview控件改变结点字体颜色

    千次阅读 2010-02-24 14:01:00
    怎么写一段asp.net代码,把一个字符串中所有HTML标记都去掉,只剩下文字内容呢? 比如:较高温度,可能增加啤酒对您诱惑,适量饮用啤酒会给您带来清凉感觉,但千万注意不要过量呦! 去掉后只剩下:较高...
  • 怎么利用区块链赚钱

    2019-01-31 21:28:53
    在世界经济历史中,已经发生过一次又一次危机,以后还会发生危机,如果世界发生不可预期和完全失控金融货币危机,数字货币很可能将是一个重大解救方案,所以,千万不要低估这类货币对人类潜在价值。...
  • 千万别小看现在学生,只要解释到位,不存在理解不了情况。所以我才要公众号,把一些简单数学原理,用丰富文字和动图展示出来。今天我要分享是一个经典数学问题——最短路线。既然要理解,那我就从最初...
  • 想要在互联网上赚大钱,最主要就是需要引流啊。...最重要还是你了多少文字,拍了多少短视频,你产品价值帮助了多少人? 2、锁定用户群体 很多人做网络项目,只追求商品成交量。其实我们还需
  • 本人亲测,试了好久才发现根本所在其实问题很简单,我们在“新建项目”时千万不要把“项目名称”成中文,换成英文字母即可。(附图1.jpg)那么问题来了,会有人担心对话框名称问题,想要中文显示该怎么办呢...
  • 最后,发现只发现有所区别,这是我以前网页是很火大一段文字,每次会情不自禁删掉,经过研究后发现,千万不能小看。 当您打开网易,新浪等网站任何一个页面源码时,第一行都会看到,很多人不明白这段话...
  • 现在开发领域中有很多第三方开源框架,但是在这些...写的很少但是字字有用,也是第一次博客 不满望大家原谅 1、学习成本  学习成本也就是说你要学习这个东西你要花费多长时间学习 ,当然这也要取决于你自己
  • 页面兼容性从认识<!DOCTYPE>开始

    千次阅读 2011-07-08 00:33:49
    今天为了css布局游览器兼容性调整了一晚上,怎么怎么不对,四处找资料和解决方案,...最后,发现只发现有所区别,这是我以前网页是很火大一段文字,每次会情不自禁删掉,经过研究后发现,千万不能小看。当
  • 一、在开始一个网页设计时,要做好三部分工作: 1.创建好相应文件夹:比如项目shoping、图片images、样式css、产品类图片upload、字体类font、脚本js。 2.创建相关文件:首页index.html、CSS初始化...

空空如也

空空如也

1 2 3
收藏数 56
精华内容 22
关键字:

千万的字怎么写