MYSQL数据库大并发的服务器运维方案

      

      问题概述:使用阿里云rds-MySQL5.6数据库(MySQL5.6版),一个用户在网上连续6个月在表中记录了近2000万个数据,最近一年保存的数据达到4000万,所以查询速度极其缓慢,卡在日常生活中。严重影响业务。问题前提:旧系统中,当时设计系统的人很可能不是大学毕业的,表设计和sql语句也不仅仅是垃圾,直接看是不可能的。原来的开发者都离职了,我来维护。这就是传说中的说法,你维护不了他们就跑,然后掉坑的就是我!!!


我试图解决这个问题,所以,有一个日志。方案概述。方案一:优化现有的mysql数据库。优点:不影响现有业务,源程序不需要修改代码,成本最低。缺点:存在优化瓶颈,数据量超过1亿就完了。方案二:升级数据库类型,换成和MySQL100%兼容的数据库。优点:不影响现有业务,源程序不需要修改代码,几乎不用做任何操作就可以提高数据库的性能;缺点:花费更多的钱。方案三:一步到位,大数据解决方案,取代newsql/nosql数据库。优点:扩展性强,成本低,没有数据容量瓶颈,缺点:需要修改源代码。以上三种方案都可以按顺序使用,数据量在十亿以下就不用换nosql了,开发成本太高。三个方案我都试过,都形成了落地方案。在这个过程中,我同情跑了一万次的开发者:)方案一:优化现有的mysql数据库。电话沟通与阿里巴巴云数据库和Google解问群,总结如下(均为精华):设计数据库和创建表时,必须考虑性能。sql的编写要注意优化。分区。分表。子库。


1.设计数据库和创建表时应考虑性能。Mysql数据库本身灵活性高,导致性能不足,严重依赖开发人员的能力。也就是说,如果开发者能力高,mysql性能就高。这也是很多关系数据库普遍存在的问题,所以公司的dba通常工资都很高。设计表格时请注意:表字段避免空值,空值很难查询和优化,并且会占用额外的索引空间。建议使用默认数字0,而不是null。尽量用INT代替BIGINT,如果不是负数就加UNSIGNED(这样数值容量会翻倍)。当然TINYINT,SMALLINT,MEDIUM_INT可以用的更好。使用枚举或整数代替字符串类型。请尝试使用时间戳而不是DATETIME。单个表中不要有太多字段。建议在20以内。使用整数存储IP。


索引。索引的数量没有尽可能多。有必要根据查询创建索引,考虑在WHERE和ORDERBY命令中涉及的列上建立索引,并根据解释检查是否使用了索引或全表扫描。尽量避免判断WHERE子句中字段的NULL值,否则引擎会放弃索引,扫描整个表。稀有值分布的字段不适合做索引,比如“gender”,一个只有两三个值的字段。只为字符字段创建前缀索引。字符字段不应是主键。没有外键,程序保证约束。不要试图成为唯一的,而是被程序所束缚。使用多列索引时,思路顺序和查询条件一致,同时删除不必要的单列索引。简而言之,使用适当的数据类型并选择适当的索引。


选择适当的数据类型。(1)使用可以存储数据的最小数据类型,整数(2)使用简单的数据类型,整数比字符处理便宜,因为字符串比较更复杂。例如,int类型存储时间类型,bigint类型转换为ip函数。(3)使用合理的字段属性长度,定长表会更快。请使用enum、char而不是varchar。(4)尽可能使用notnull来定义字段。(5)尽量少用文字。如果非要用,就要分表。


选择适当的索引列。(1)经常查询的列,出现在where、groupby、orderby、on子句中的列。(2)第2列),其中出现<、<=、=、>、>=、介于、在和类似字符串+通配符(%)。(3)对于长度较小的列,索引字段越小越好。因为数据库的存储单位是页面,一个页面能存储的数据越多越好。(4)分散度大(不同值较多)的列放在联合索引前面。要查看分散度,通过统计不同的列值来实现。计数越大,分散度越高:

原来的开发者已经跑路了,表已经建立好了,我不能修改。所以,措辞不能执行,放弃.

分享: