问题概述
应用阿里云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个分区，查问速度晋升显著。问题解决！后果如下：PARTITION BY HASH (id)PARTITIONS 64
select count() from readroom_website; --11901336行记录
/ 受影响行数: 0 已找到记录: 1 正告: 0 持续时间 1 查问: 5.734 sec. /
select * from readroom_website where month(accesstime) =11 limit 10;
/ 受影响行数: 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/produc...
官网介绍语：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/produc...
官网介绍：云数据库HybridDB for MySQL （原名PetaData）是同时反对海量数据在线事务（OLTP）和在线剖析（OLAP）的HTAP（Hybrid Transaction/Analytical Processing）关系型数据库。
我也测试了一下，是一个olap和oltp兼容的解决方案，然而价格太高，每小时高达10块钱，用来做存储太节约了，适宜存储和剖析一起用的业务。
1.腾讯云DCDB
2.https://cloud.tencent.com/pro...
官网介绍：DCDB又名TDSQL，一种兼容MySQL协定和语法，反对主动程度拆分的高性能分布式数据库——即业务显示为残缺的逻辑表，数据却平均的拆分到多个分片中；每个分片默认采纳主备架构，提供灾备、复原、监控、不停机扩容等全套解决方案，实用于TB或PB级的海量数据场景。
腾讯的我不喜爱用，不多说。起因是出了问题找不到人，线上问题无奈解决头疼！然而他价格便宜，适宜超小公司，玩玩。
计划三具体阐明：去掉mysql，换大数据引擎解决数据
数据量过亿了，没得选了，只能上大数据了。
开源解决方案
hadoop家族。hbase/hive怼上就是了。然而有很高的运维老本，个别公司是玩不起的，没十万投入是不会有很好的产出的！
云解决方案
这个就比拟多了，也是一种将来趋势，大数据由业余的公司提供业余的服务，小公司或集体购买服务，大数据就像水/电等公共设施一样，存在于社会的方方面面。
国内做的最好的当属阿里云。
我抉择了阿里云的MaxCompute配合DataWorks，应用超级难受，按量付费，老本极低。
MaxCompute能够了解为开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本等形式操作数据，数据以表格的模式展示，以分布式形式存储，采纳定时工作和批处理的形式解决数据。DataWorks提供了一种工作流的形式治理你的数据处理工作和调度监控。
当然你也能够抉择阿里云hbase等其余产品，我这里次要是离线解决，故抉择MaxCompute，根本都是图形界面操作，大略写了300行sql，费用不超过100块钱就解决了数据处理问题。
作者：王帅
起源地址：https://yq.aliyun.com/article...