共计 5774 个字符，预计需要花费 15 分钟才能阅读完成。
问题概述
应用阿里云 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…
