|
|
51CTO旗下网站
|
|
移步端
  • 记录一次MySQL两千万数目的大表优化解决过程,提供三种解决方案

    采用阿里云rds for MySQL必发娱乐登录(就是MySQL5.6本子),有个用户上网记录表6个月的多寡量近2000万,保留最近一年之多寡量达到4000万,查询速度极慢,一般说来卡死。严重影响业务。我尝试解决该问题,so,有个这个日志。

    笔者:王帅 来源:java版web品种| 2019-02-26 13:18

    记录一次MySQL两千万数目的大表优化解决过程,提供三种解决方案

    题材概述

    采用阿里云rds for MySQL必发娱乐登录(就是MySQL5.6本子),有个用户上网记录表6个月的多寡量近2000万,保留最近一年之多寡量达到4000万,查询速度极慢,一般说来卡死。严重影响业务。

    题材前提:成熟系统,那时规划系统之人数大概是高校没毕业,表面设计和sql说话写的不仅仅是垃圾,简直无法直视。原开发人员都已离职,到我来维护,这就是传说中的保障不了就跑路,下一场我就是少坑的那个!!!

    我尝试解决该问题,so,有个这个日志。

    提案概述

  • 提案一:多极化现有mysql必发娱乐登录。优点:不影响现有业务,源程序不需要修改代码,本***。症结:有多样化瓶颈,数量量过亿就玩完了。
  • 提案二:升级必发娱乐登录类型,换一种100%兼容mysql的必发娱乐登录。优点:不影响现有业务,源程序不需要修改代码,你几乎不需要做其他操作就能提升必发娱乐登录性能,症结:多花钱
  • 提案三:一步到位,大数量解决方案,转换newsql/nosql必发娱乐登录。优点:扩展性强,本低,没有多少容量瓶颈,症结:要求修改源程序代码
  • 上述三种方案,按顺序使用即可,数量量在洞级别一下之没必要换nosql,付出资金太高。三种方案我都试了一遍,而且都形成了落地解决方案。该过程心中慰问跑路的那几个开发者一万遍 :)

    提案一详细说明:多极化现有mysql必发娱乐登录

    跟阿里云必发娱乐登录大佬电话联系 and Google解决方案 and 问起里大佬,总结如下(都是精华):

  • 1.必发娱乐登录设计和外部创建时就要考虑性能
  • 2.sql的编制需要注意优化
  • 3.分区
  • 4.成分表
  • 5.国库
  • 1、必发娱乐登录设计和外部创建时就要考虑性能

    mysql必发娱乐登录本身高度灵活,造成性能不足,严重依赖开发人员能力。具体地说开发人员能力高,则mysql性能高。这也是许多关系型必发娱乐登录的毛病,故此公司的dba普通工资巨高。

    计划表时要小心:

    1. 表字段避免null值出现,null值很难查询优化且占用额外的目录空间,引进默认数字0代表null。
    2. 尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能利用TINYINT、SMALLINT、MEDIUM_INT更好。
    3. 采用枚举或整数代替字符串类型
    4. 尽量使用TIMESTAMP而非DATETIME
    5. 单表不要有太多字段,提议在20以内
    6. 用整型来存IP

    目录

    1. 目录并不是越多越好,要依据查询有突破性的创造,考虑在WHERE和ORDER BY命令上涉及的进建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
    2. 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而展开全表扫描
    3. 值分布很***的字段不适合建索引,例如"性别"这种只有两三个值的字段
    4. 字符字段只建前缀索引
    5. 字符字段***无需做主键
    6. 无需外键,由程序保证约束
    7. 尽量不用UNIQUE,由程序保证约束
    8. 采用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引

    概括就是运用方便的多寡类型,慎选适宜的目录

    慎选适宜的多寡类型 (1)采用可存下数据的最小的多寡类型,整型 < date,time < char,varchar < blob (2)采用简单的多寡类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int品种存储时间类型,bigint品种转ip函数 (3)采用合理的字段属性长度,定位长度的外表会更快。采用enum、char而不是varchar (4)尽可能使用not null定义字段 (5)尽量少用text,非用不可***成分表 # 慎选适宜的目录列 (1)查询频繁之进,在where,group by,order by,on从句中出现的进 (2)where谱中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的进 (3)长小的进,目录字段越小越好,因为必发娱乐登录的存储单位是页,一页中能存下的多寡越多越好 (4)离散度大(不同之值多)的进,放在联合索引前面。翻开离散度,穿过统计不同之进值来促成,count越大,离散程度越高:

    原开发人员已经跑路,该表早已建立,我一筹莫展修改,故:该措辞无法推行,放弃!

    2、sql的编制需要注意优化

    1. 采用limit对查询结果的记录进行限定
    2. 避免select *,名将急需查找的字段列出来
    3. 采用连接(join)来代替子查询
    4. 拆分大的delete或insert说话
    5. 可通过开启慢查询日志来找出较慢的SQL
    6. 不做列运算:SELECT id WHERE age + 1 = 10,其它对进的借鉴都将导致表面扫描,他包括必发娱乐登录教程函数、计算公式等等,查询时要尽可能将操作移至等号右边
    7. sql说话尽可能简单:一枝sql只能在一番cpu运算;大语句拆小语句,调减锁时间;一枝大sql可以堵死整个库
    8. OR扭亏增盈成IN:OR的频率是n级别,IN的频率是log(n)级别,in的底数建议控制在200以内
    9. 无需函数和推进器,在利用程序实现
    10. 避免%xxx式查询
    11. 少用JOIN
    12. 采用同类型进行比较,比如用'123'和'123'比,123和123比
    13. 尽量避免在WHERE子句中采用!=或<>操作符,否则将引擎放弃使用索引而展开全表扫描
    14. 对于连续数值,采用BETWEEN无需IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5
    15. 列表数据不要拿全表,要使用LIMIT来分页,每页数量也不必太大

    原开发人员已经跑路,先后已经形成上点,我一筹莫展修改sql,故:该措辞无法推行,放弃!

    引擎

    引擎

    脚下普遍采用的是MyISAM和InnoDB两种引擎:

    MyISAM

    MyISAM引擎是MySQL 5.1及之前版本的默认引擎,他的性状是:

    1. 不支持行锁,读取时对需要读到的一切表加锁,写入时则对外部加排它锁
    2. 不支持事务
    3. 不支持外键
    4. 不支持崩溃后的平安恢复
    5. 在表面有读取查询的同时,支持往表中插入新纪录
    6. 支持BLOB和TEXT的明天500个字符索引,支持全文索引
    7. 支持延迟更新索引,大幅度提升写入性能
    8. 对于不会进展修改的外表,支持压缩表,大幅度减少磁盘空间占用

    InnoDB

    InnoDB在MySQL 5.5此后成为默认索引,他的性状是:

    1.支持行锁,利用MVCC来支持高并发

    2.支持事务

    3.支持外键

    4.支持崩溃后的平安恢复

    5.不支持全文索引

    完全来讲,MyISAM相当SELECT密集型的外表,而InnoDB相当INSERT和UPDATE密集型的外表

    MyISAM速度可能超快,占用存储空间也小,但是程序要求事务支持,故InnoDB是必须的,故该方案无法推行,放弃!

    3、分区

    MySQL在5.1版引入的基站是一种简易的档次拆分,他家需求组建表的时节加上分区参数,对使用是晶莹的智能化需修改代码

    对客户来说,分区表是一番独立的逻辑表,但是底层由多个物理子表组成,贯彻分区的编码实际上是通过对一组底层表的目标封装,但对SQL层来说是一番完全封装底层的黑盒子。MySQL贯彻分区的措施也意味着索引也是按照分区的子表定义,没有全局索引

    他家之SQL说话是要求针对分区表做优化,SQL谱中要带上分区条件的进,故而使查询定位到少量之基站上,否则就会扫描全部分区,可以通过EXPLAIN PARTITIONS来查看某条SQL说话会落在那些分区上,故而进行SQL多极化,我高考,查询时不带分区条件的进,也会提高速度,故该措施值得一试。

    成分区的功利是:

    1. 可以让单表存储更多的多寡
    2. 分区表的多寡更容易维护,可以通过掌握整个分区批量删除大量数目,也得以增加新的分区来支持新插入的多寡。此外,还可以对一个独立分区进行规范化、检查、修复等操作
    3. 局部查询能够从查询条件确定只落在少数分区上,速度会很快
    4. 分区表的多寡还可以分布在不同之物理设备上,故而搞笑利用多个硬件设备
    5. 可以运用分区表赖避免某些特殊瓶颈,例如InnoDB单个索引的互斥访问、ext3文件系统之inode锁竞争
    6. 可以备份和恢复单个分区

    成分区的限制和缺点:

    1. 一度表最多只能有1024个基站
    2. 如果分区字段中有主键或者唯一索引的进,这就是说所有主键列和唯一索引列都不能不包含进来
    3. 分区表无法运用外键约束
    4. NULL值会使分区过滤无效
    5. 整整分区必须采取相同的存储引擎

    成分区的项目:

    1. RANGE分区:基于属于一个给定连续区间的进值,把多列分配给分区
    2. LIST分区:类似于按RANGE分区,分别在于LIST分区是基于列值匹配一个离散值集合中的某个值来开展分选
    3. HASH分区:基于用户定义之格式的返回值来开展分选的基站,该表达式使用将要插入到表中的这些行的进值进行计算。其一函数可以包含MySQL官方有效的、产生非负整数值的其他模式
    4. KEY分区:类似于按HASH分区,分别在于KEY分区只支持计算一趟或多列,且MySQL传感器提供其自己的哈希函数。必须有一趟或多列包含整数值
    5. 现实关于mysql成分区的定义请自行google或查询官方文档,我这里只是指导了。

    我第一根据月份把上网记录表RANGE分区了12份,查询效率提高6倍左右,功能不明朗,故:换id为HASH分区,成分了64个基站,查询速度提升显著。题材解决!

    结果如下:

          
    1. PARTITION BY HASH (id)PARTITIONS 64 
          
    1. select count() from readroom_website; --11901336列记录  
    2. / 受影响行数: 0 已找到记录: 1 警告: 0 接轨时间 1 查询: 5.734 sec. /  
    3. select * from readroom_website where month(accesstime) =11 limit 10;  
    4. / 受影响行数: 0 已找到记录: 10 警告: 0 接轨时间 1 查询: 0.719 sec. */ 

    4、成分表

    成分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次询问分成多次询问,下一场把结果组合返回给用户。

    成分表分为垂直拆分和档次拆分,普通以某个字段做拆分项。比如以id字段拆分为100张表: 表面名为 tableName_id%100

    但:成分表需要修改源程序代码,会给开发带来大量工作,大幅度的充实了开发资金,故:只适合在开发初期就考虑到了大量数目存在,搞好了分表处理,不适宜应用上点了再做修改,本太高!!!而且选择这个方案,都不如选择我提供的第二次三个方案之资金低!故不建议使用。

    5、国库

    把一个必发娱乐登录分成多个,提议做个读写分离就行了,实际的做分库也会带来大量之支出资金,得不偿失!不推荐使用。

    提案二详细说明:升级必发娱乐登录,换一个100%兼容mysql的必发娱乐登录

    mysql性能不行,那就换个。为保证源程序代码不修改,合同现有业务平稳迁移,故需要换一个100%兼容mysql的必发娱乐登录。

    开源选择

    1. tiDB https://github.com/pingcap/tidb
    2. Cubrid https://www.cubrid.org/
    3. 开源必发娱乐登录会带来大量之运维成本且其工业品质和MySQL尚有差异,有许多坑要踩,如果你公司要求必须自建必发娱乐登录,这就是说选择该项目产品。

    云数据选择

    1. 阿里云POLARDB
    2. https://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wH

    法定介绍语:POLARDB 是阿里云自研的后生关系型分布式云原生必发娱乐登录,100%兼容MySQL,存储容量***可达 100T,性能***提升至 MySQL 的 6 倍。POLARDB 既融合了经贸必发娱乐登录稳定、保险、高性能的性状,又具有开源必发娱乐登录简单、可扩展、接轨迭代的劣势,而成本只需商用必发娱乐登录的 1/10。

    我开通测试了一下,支持免费mysql的多寡迁移,产业化操作成本,性能提升在10倍左右,价格跟rds离开不多,是个很好的准备解决方案!

    1. 阿里云OcenanBase
    2. 淘宝使用的,扛得住双十一,性能***,但是在公测中,我一筹莫展尝试,但值得期待
    3. 阿里云HybridDB for MySQL (原PetaData)
    4. https://www.aliyun.com/product/petadata?spm=a2c4g.11174283.cloudEssentials.54.7a984b5cS7h4wH

    法定介绍:云必发娱乐登录HybridDB for MySQL (原名PetaData)是同时支持海量数据在线事务(OLTP)和在线分析(OLAP)的HTAP(Hybrid Transaction/Analytical Processing)沟通型必发娱乐登录。

    我也测试了一下,是一番olap和oltp兼容的解决方案,但是价格太高,每小时高达10块钱,用来做存储太浪费了,相当存储和分析一起用之工作。

    1. 腾讯云DCDB
    2. https://cloud.tencent.com/product/dcdb_for_tdsql

    法定介绍:DCDB又名TDSQL,一种兼容MySQL协和和语法,支持自动水平拆分的高性能分布式必发娱乐登录——即业务显示为整体的逻辑表,数量却均匀的拆分到多个分片中;每个分片默认采用主备架构,提供灾备、恢复、监督、不停机扩容等全方位解决方案,租用于TB或PB除的海量数据场景。

    腾讯之我不希罕用,不多说。原因是出了问题找不到人口,点上问题无法解决头疼!但是它价格低廉,相当超小商店,玩玩。

    提案三详细说明:去掉mysql,换大数量引擎处理数据

    数量量过亿了,没得选了,只能上大数量了。

    开源解决方案

    hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本,普通公司是玩不帮的,没十万跃入是不会有很好的生产的!

    云解决方案

    其一就比较多了,也是一种未来趋势,大数量由专业的合作社提供专业的劳务,小商店或个人购买服务,大数量就像水/电等公共设施一样,存在于社会之全体。

    境内做的***的当属阿里云。

    我选择了阿里云之MaxCompute配合DataWorks,采用超级舒服,按量付费,本极低。

    MaxCompute可以了解为开源的Hive,提供sql/mapreduce/ai书法/python剧本/shell剧本等艺术操作数据,数量以表格的样式展现,以分布式方式存储,利用定时任务和队处理的措施处理数据。DataWorks提供了一种工作流的措施管理你的数据处理任务和布局监控。

    当然你也得以选择阿里云hbase等其它产品,我这里根本是离线处理,故选择MaxCompute,基本都是图形界面操作,简言之写了300列sql,费用不超过100块钱就解决了数据处理问题。

    【编纂推荐】

    1. MySQL基本复制虽好,能***消灭必发娱乐登录单点问题吗?
    2. 开源必发娱乐登录:PostgreSQL、MariaDB和SQLite的对待
    3. 聊聊Java必发娱乐登录开发的那点政务
    4. MySQL误删数据救命指南:必收藏
    5. MySQL每秒570000的写入,如何实现?
    【义务编辑: 庞桂玉 TEL:(010)68476606】

    点赞 0
  • MySQL  大表优化  必发娱乐登录
  • 分享:
    大家都在看
    猜你喜欢
  • 订阅专栏+更多

    中间件安全防护攻略

    中间件安全防护攻略

    4类安全防护
    共4章 | hack_man

    119人口订阅学习

    CentOS 8 全新学习术

    CentOS 8 全新学习术

    CentOS 8 专业通告
    共16章 | UbuntuServer

    275人口订阅学习

    用Python玩转excel

    用Python玩转excel

    让重复操作傻瓜化
    共3章 | DE8UG

    234人口订阅学习

    读 书 +更多

    安全模式:J2EE、Web劳务和地位管理最佳实践与策

    该书全面论述Java使用安全的中心知识并介绍一种强大的组织化安全设计方法;介绍独立于厂商的平安架构;列入详细的评分核对表以及23种经过实...

    订阅51CTO邮刊

    点击这里查看样刊

    订阅51CTO邮刊

    51CTO劳务号

    51CTO官微




  •