马来西亚云顶网站电子游戏|pt电子游戏投注
媒体关注 媒体关注         蓝盟动态         员工故事         上海企业家观察         蓝盟读书会         行业资讯         技术百科

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

发布者:上海IT外包      发布时间:2019-2-27      来源:http://www.zxcoz.tw      点击数:422

  • 方案一:优化现有mysql数据库。利益:不影响现有业务,源轨范不必要改削代码,本钱最低。错误错误:有优化瓶颈,数据量过亿就玩完了。

  • 方案二:进级数据库类型,换一种100%兼容mysql的数据库。利益:不影响现有业务,源轨范不必要改削代码,你几乎不必要做任何把持就能晋升数据库机能,错误错误:多花钱

  • 方案三:一步到位,大数据处理方案,改换newsql/nosql数据库。利益:扩展性强,本钱低,没稀有据容量瓶颈,错误错误:必要改削源轨范代码

以上三种方案,按挨次使用即可,数据量在亿级别一下的没必要换nosql,开发本钱太高。三种方案我都试了一遍,并且都形成了落地处理方案。该过程心中慰问跑路的那几个开发者一万遍 :)

方案一详细声名:优化现有mysql数据库

阿里云数据库大佬电话?#20302;?and Google处理方案 and 问群里大佬,总结如下(都是精华):

  • 1.数据库设计和表建树?#26412;?#35201;考虑机能

  • 2.sql的编写必要注意优化

  • 3.分区

  • 4.分表

  • 5.分库

1、数据库设计和表建树?#26412;?#35201;考虑机能

mysql数据库本身高度?#23186;藎?#24418;成机能不够,严峻依靠开发人员才能。也就是说开发人员才能高,则mysql机能高。这也是?#32423;?#20851;系型数据库的通病,所以公司的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值断定,不然将导致引擎抛却使?#30431;?#24341;而停止全表扫描

  3. 值分布很稀少的字段不合适建索引,例如"性别"这种只需两三个值的字段

  4. 字符字段只建前缀索引

  5. 字符字段最好不要做主键

  6. 不消外键,由轨范保证束缚

  7. 尽量不消UNIQUE,由轨范保证束缚

  8. 使用多列索引时主见挨次和查询前提保持同等,同时删除不必要的单列索引

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

选择适宜的数据类型 (1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob (2)使用简单的数据类型,整型比字符措置开销更小,由于字符串的斗劲更复杂。如,int类型存储时辰类型,bigint类型转ip函数 (3)使用合理的字?#38382;?#24615;长度,固定长度的表会更快。使用enum、char而不是varchar (4)尽可能使用not null界说字段 (5)尽量少用text,非用不成最好分表 # 选择适宜的索引列 (1)查询频仍的列,在where,group by,order by,on从句中出现的列 (2)where前提中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列 (3)长度小的列,索引字段越小越好,由于数据库的存储单位是页,一?#25345;?#33021;存下的数据越多越好 (4)离散度大(不合的值多)的列,放在连系索引前面。检察离散度,经由过程统计不合的列值来实现,count越大,离散程度越高:

原开发人员已经跑路,该表早已建立,我无法改削,故:?#30431;?#35805;无法实行,抛却!

2、sql的编写必要注意优化

  1. 使用limit对查询成效的记?#20302;?#27490;限定

  2. 按捺select *,将必要查找的字段列出来

  3. 使用毗连(join)来庖代子查询

  4. 拆分大的delete或insert语句

  5. 可经由过程开启慢查询日志来?#39029;黿下?#30340;SQL

  6. 不做?#24615;?#31639;:SELECT id WHERE age + 1 = 10,任何对列的把持都将导致表扫描,它网罗数据库教程函数、计较表达式等等,查询时要尽可能将把持移至?#32676;?#21491;边

  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子句中使用!=或<>把持符,不然将引擎抛却使?#30431;?#24341;而停止全表扫描

  14. 对?#35835;?#32493;数值,使用BETWEEN不消IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5

  15. 列表数据不要拿全表,要使用LIMIT来分页,每页数目也不要太大

文章来自:上海IT外包 链接: http://www.zxcoz.tw
 
马来西亚云顶网站电子游戏