记录一次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版引入的分区是一种简朴的程度拆分，用户必要在建表的时辰加上分区参数，对应用是透明的无需修改代码

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!