长乐路新闻网
国内新闻
当前位置: 首页 >> 国内新闻 >> 正文

一次MySQL两千万数据大表的优化过程,三种解决方案


文章作者:www.mhgoebel.com 发布时间:2019-09-07 点击:1793



问题概述

使用阿里巴巴rds为MySQL数据库(即MySQL5.6版本),有一个用户在线记录表,为6个月的数据量近20万,保留了最近一年的数据量达到40万,查询速度很慢,每日卡死。严重影响业务。

问题的前提:旧系统,当时设计系统的人可能没有从大学毕业。表设计和sql语句不仅用垃圾编写,而且无法直接查看。原来的开发者已经离开了,给我维护,这是传说中的维护无法运行,那么我就是那个摔下来的人!

我试图解决这个问题,所以,请记录下来。

解决方案1:优化现有的mysql数据库。优点:不影响现有业务,源程序不需要修改代码,成本最低。缺点:存在优化瓶颈,数据量超过1亿。选项2:升级数据库类型,以获得100%兼容的mysql数据库。优点:不影响现有业务,源程序不需要修改代码,几乎可以做任何操作都可以提高数据库性能,缺点:更多钱方案三:一步到位,大数据解决方案,更换newsql/nosql数据库。优点:可扩展性强,成本低,无数据容量瓶颈,缺点:需要修改源代码

上述三种方案可以按顺序使用,并且数据量不需要将nosql更改为十亿级别,并且开发成本太高。我尝试了所有三个程序,他们都形成了一个着陆解决方案。向运行道路的开发人员表示哀悼的过程10,000次:)

与阿里巴巴云数据库和谷歌解决方案交谈并向小组询问,并总结以下内容(都是本质):

1.在设计数据库和表格时应考虑性能。

2.sql写作需要注意优化

3.分区

4.子表

5.子库

1.数据库设计和表创建时就要考虑性能

mysql数据库本身非常灵活,导致性能不足并严重依赖开发人员功能。换句话说,开发人员的能力很高,那么mysql的性能很高。这也是许多关系数据库的常见问题,因此该公司的dba通常薪水很高。

设计表时要注意:

1.表字段避免出现空值。空值很难查询和优化,占用额外的索引空间。建议使用默认编号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,程序保证约束

简言之就是使用合适的数据类型,选择合适的索引

选择正确的数据类型

(1)使用可以存储数据的最小数据类型,整数<日期,时间< char,varchar<团块

(2)对于简单的数据类型,整数比字符处理便宜,因为字符串比较更复杂。例如,int类型存储时间类型,bigint类型到ip函数

(3)使用合理的字段属性长度,固定长度的表将更快。使用enum,char而不是varchar

(4)使用not null来尽可能多地定义字段

(5)尽可能少地使用文本,并使用不使用表来获得最佳分数

#选择适当的索引列

(1)在where,group by,order pieces<<=,=,>>=,between,in等字符串+通配符(%)/p>中查询频繁列出的列

(3)长度较小的列,索引字段越小越好,因为数据库的存储单元是页面,可以在一个页面中保存的数据越多越好。

(4)在接合指数之前放置具有大分散(不同值)的柱。查看色散,通过计算不同的色谱柱值,计数越大,色散度越高:

原来开发者已经运行了,手表已经建立,我无法修改它,所以:措辞无法实现,放弃!

2.sql的编写需要注意优化

1.使用限制来限制查询结果的记录

2.避免选择*,列出您需要找到的字段

3.使用join而不是子查询

4.拆分大型删除或插入语句

5.通过启用慢速查询日志

可以找到较慢的SQL

6.不要做列操作:SELECT id WHERE age + 1=10,对列的任何操作都会导致表扫描,它包括数据库教程函数,计算表达式等,尽可能将操作移动到查询时等号

8.OR被重写为IN:OR为n级,IN为效率(log),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.支持崩溃后的安全恢复

分区的好处是:

1.您可以让单个表存储更多数据

2.分区表的数据更易于维护。您可以通过清除整个分区批量删除大量数据,也可以添加新分区以支持新插入的数据。此外,您可以优化,检查,修复等单独的分区

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或查询官方文档,我只是扔砖头。

我首先根据月份将Internet日志RANGE分区12份,查询效率提高了约6倍。效果不明显。因此,id是HASH分区,并且划分了64个分区,并且查询速度显着提高。问题解决了!结果如下:PARASHTION BY HASH(id)PARTITIONS 64从readroom_website中选择count(); - 行记录/受影响行数: 0找到记录: 1警告: 0持续时间1查询: 5.734秒。/select * from readroom_website where Month(accesstime)=11 limit 10; /受影响行数: 0找到记录: 10警告: 0持续时间1查询: 0.719秒。 */

4.分表

子表是根据上述过程优化大表,或查询卡死,然后将表分成多个表,将查询分成多个查询,然后将结果组合返回给用户。

子表分为垂直分割和水平分割,通常具有要分割的字段。例如,id字段分为100个表:表名为tableName_id%100

但是:子表需要修改源代码,这会给开发带来很多工作,这大大增加了开发成本。因此,它仅适用于在开发的早期阶段考虑大量数据的存在,并且它不适合应用。上网并做出改动,成本太高了!选择此选项并不像选择我提供的第二和第三种解决方案那样具有成本效益!因此,不建议这样做。

5.分库

将数据库划分为多个,建议进行读写分离,真正的子库也会带来很多开发成本,不值一提!不建议。

Mysql性能不好,然后改变它。为了确保不修改源代码以确保现有服务的平滑迁移,有必要更改与mysql 100%兼容的数据库。

开源选择

1.tiDB

2.Cubrid

3.开源数据库会带来很多运维成本,其工业质量与MySQL仍有差距,有很多需要踩的,如果贵公司要求你必须建立自己的数据库,那么选择这种类型的产品。

云数据选择

1.阿里巴巴云POLARDB

2。

官方介绍:POLARDB是阿里云的下一代关系分布式云原生数据库,与MySQL 100%兼容,存储容量高达100T,性能高达MySQL的6倍。 POLARDB将商业数据库的稳定性,可靠性和高性能与开源数据库的简单,可扩展和连续迭代的优势结合在一起,成本仅为商业数据库的1/10。

我开了测试,支持免费的mysql数据迁移,没有运营成本,性能提升10倍左右,价格类似于rds,是一个不错的替代解决方案!

1.阿里云OcenanBase

2.使用淘宝,可以住双十一,性能出色,但在公测中,我不能尝试,但值得期待

3.用于MySQL的阿里云HybridDB(以前称为PetaData)

4。

官方介绍:云数据库HybridDB for MySQL(以前称为PetaData)是一个HTAP(混合事务/分析处理)关系数据库,支持海量数据在线事务(OLTP)和在线分析(OLAP)。

我也测试了它,它是一个olap和oltp兼容的解决方案,但价格太高,每小时高达10美元,用于使存储太浪费,适合存储和分析业务在一起。

1.腾讯云DCDB

2。

官方介绍:DCDB,也称为TDSQL,是一种高性能的分布式数据库,兼容MySQL协议和语法,支持自动水平分割。也就是说,业务显示为完整的逻辑表,并且数据被均匀地分成多个片段。每个分片默认采用主动/备用架构,提供完整的解决方案,如灾难恢复,恢复,监控和不间断扩展。它适用于TB或PB级别的海量数据场景。

腾讯我不喜欢用它,不多说。原因是有人找不到问题,而在线问题无法解决头痛问题!但他便宜又适合超小公司玩。

数据量超过1亿。我别无选择,只能去大数据。

开源解决方案

Hadoop家族。 Hbase/hive恰到好处。但是,运营和维护成本很高,一般公司玩不起,没有10万投资就不会有好的产量!

云解决方案

这越来越多,这也是未来的趋势。大数据由专业公司,专业服务,小公司或个人提供,以购买服务。大数据就像水/电这样的公共设施,它存在于社会的各个方面。

在中国最好的事情是阿里云。

我选择了Alibaba Cloud的MaxCompute和DataWorks,它非常舒适,按体积付费,而且成本非常低。

MaxCompute可以理解为开源Hive,它提供sql/mapreduce/ai算法/python脚本/shell脚本来操作数据。数据以表格的形式呈现,以分布式方式存储,并在定时任务和批处理过程中处理。 DataWorks提供了一个工作流来管理您的数据处理任务和计划监控。

当然,您也可以选择其他产品,如阿里巴巴云Hbase。我主要在这里使用离线处理。因此,MaxCompute基本上是一个图形界面操作。它写了大约300行的SQL。解决数据处理问题的成本不超过100美元。

下一条: “为了让孩子上最好的学校,我们从豪宅搬到了贫民窟”