问题概述使用阿里云rdsforMySQL数据库（即MySQL5.6版本），有一个用户记录了6个表数据月在线数据量近2000万条，去年留存数据量达到4000万条。查询速度极慢，日常卡顿。严重影响生意。问题前提：老系统，当时设计系统的人很可能大学没毕业，表设计和SQL语句不就是垃圾，直接看不出来。原来的开发人员走了，我来维护。这就是传说中的养不起就跑，到时候掉坑里的就是我！！！我试图解决这个问题，所以，有这个日志。解决方案概述方案一：优化现有的mysql数据库。优点：对现有业务无影响，无需修改源程序代码，成本低。缺点：存在优化瓶颈，数据量过亿就完蛋了。方案二：升级数据库类型，换成100%兼容mysql的数据库。优点：不影响现有业务，无需修改源码，无需任何操作即可提升数据库性能。缺点：多花钱方案三：一步到位，大数据方案，替换newsql/nosql数据库。优点：扩展性强，成本低，无数据容量瓶颈。缺点：以上三种方案需要修改源码，依次使用即可。如果数据量小于1亿，没必要改成nosql，开发成本太高。三个方案我都试过了，都形成了落地方案。在此过程中，对跑路10000次的开发者表示慰问：）方案一详细：优化现有mysql数据库，电话与阿里云数据库大佬交流，谷歌解决方案，请教群里大佬，总结如下（都是精华）：1.数据库设计和建表必须考虑性能2.sql的编写需要优化高度灵活，导致性能不足，严重依赖开发者能力。也就是说开发者的能力高，mysql的性能就高。这也是很多关系型数据库的通病，所以公司的dba通常都有着丰厚的薪水。设计表格时要注意：避免表格字段出现空值。空值难以查询和优化，占用额外的索引空间。建议使用默认数字0而不是null。尝试使用INT而不是BIGINT。如果是非负数，加上UNSIGNED（这样值容量就会翻倍）。当然，最好使用TINYINT、SMALLINT和MEDIUM_INT。使用枚举或整数而不是字符串类型，并尝试使用TIMESTAMP而不是DATETIME。单个表中的字段不应太多。建议使用20以内的整数存储IP索引。索引不是越多越好，根据查询创建，考虑在WHERE和ORDERBY命令涉及的列上创建索引，可以查看是否使用索引或根据EXPLAIN进行全表扫描。尽量避免在WHERE子句中判断字段的NULL值，否则引擎会放弃使用索引，但是全表扫描值分布非常大的字段不适合做索引。例如，“性别”是一个只有两个或三个值的字段。字符字段只构建前缀索引字符字段。***不要使用主键和外键。程序保证约束尽量不要使用UNIQUE。在使用多列索引时，程序保证约束使思路的顺序与查询条件保持一致，删除不需要的单列索引。总之，使用合适的数据类型，选择合适的索引，选择合适的数据类型（1）使用能存储数据的最小数据类型，integer,>=,between,in,likestring+wildcard(%)出现的column(3）length对于小列来说，索引字段越小越好，因为数据库的存储单位是页，一页可以存放的数据越多越好（4）离散度大的列（更多不同的值）应该放在联合索引的前面。检查分散程度，这是通过统计不同的列值来实现的。count越大，分散度越高：原开发者跑路了，表已经建好了，我修改不了，所以：这个写法执行不了，放弃！2.sql写的时候需要注意优化。使用limit限制查询结果的记录，避免select*，列出需要查询的字段。使用连接而不是子查询拆分。打开慢查询日志可以发现大的delete或insert语句。较慢的SQL不进行列操作：SELECTidWHEREage+1=10，对列的任何操作都会引起表扫描，包括数据库教程函数，计算表达式等，操作要尽可能多的移动查询时等号右边的sql语句越简单越好：一条sql只能在一个CPU上运行；一个大语句可以分成多个小语句，以减少锁定时间；一个大的sql可以阻塞整个库OR重写成IN：OR的效率是n级别，IN的效率是log(n)级别。在不使用函数和触发器的情况下，建议将in的数量控制在200以内。在应用的实现中，避免%xxx查询，少用JOIN。使用相同的类型进行比较，比如使用'123'和'123''比，123和123比尽量避免在WHERE子句中使用!=或<>运算符，否则引擎会放弃使用索引而执行全表扫描。对于连续值，用BETWEEN代替IN：SELECTidFROMtWHEREnumBETWEEN1AND5list数据不要用全表，用LIMIT分页，页数不要太大。原来的开发者已经跑路了，程序已经启动了，我不能修改sql，所以：这个写法不能执行，放弃！EngineEngine目前广泛使用的引擎有MyISAM和InnoDB两种：MyISAMMyISAM引擎是MySQL5.1及更早版本的默认引擎。它的特点是：不支持行锁。然后给表加排他锁，不支持事务，不支持外键，不支持崩溃后安全恢复，支持在表有读查询的情况下向表中插入新记录，支持前500个字符的索引BLOB和TEXT，并支持全文索引，支持索引的延迟更新，大大提高了写入性能。对于不会修改的表，支持压缩表，大大减少了占用的磁盘空间。InnoDBInnoDB在MySQL5.5之后成为默认索引。其特点是：1.支持行锁，使用MVCC支持高并发2.支持事务3.支持外键4.支持崩溃后安全恢复5.不支持全文索引一般来说，MyISAM适合SELECT密集型表，而InnoDB适用于INSERT和UPDATE密集型表MyISAM可能速度超快，占用存储空间小，但是程序需要事务支持，所以需要InnoDB，所以这个方案无法实施，放弃！3.分区MySQL在5.1版本中引入的分区是一种简单的水平分割。用户在建表时需要添加分区参数，对应用程序透明，无需修改代码。对于用户来说，分区表是一个独立的逻辑表，但底层是由多个物理子表组成的，实现分区的代码实际上是由一组底层表对象封装的，但是对于SQL层来说，是一个完全封装底层的黑盒子。需要针对分区表优化全局索引用户的SQL语句。分区条件的列必须包含在SQL条件中，这样查询才能定位到少数分区，否则会扫描所有分区。可以通过EXPLAINPARTITIONS查看某行SQL语句会落在那些分区上，从而优化SQL。我测试过不带分区条件的列在查询时也会提高速度，所以这个措施值得一试。分区的好处是：它允许单个表存储更多数据。分区表中的数据更易于维护。可以通过清空整个分区来批量删除大量数据，也可以添加新的分区来支持新插入的数据。此外，您还可以对独立的分区进行优化、检查、修复等操作。有些查询可以从查询条件判断只落在少数分区上，速度会很快。分区表中的数据还可以分布在不同的物理设备上，这很有趣。多个硬件设备可以使用分区表来避免一些特殊的瓶颈，比如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争可以备份和恢复单个分区分区的限制和缺点：一张表最多只能有1024个分区如果分区字段中有主键或唯一索引列，则必须包括所有主键列和唯一索引列。分区表不能使用外键约束。NULL值将使分区过滤失效。所有分区必须使用相同的存储引擎。类型：RANGE分区：根据属于给定连续区间的列值分配多行分区LIST分区：类似于RANGE分区，不同的是LIST分区是根据列值匹配一个值在用于选择的一组离散值HASH分区：根据使用要插入表的行的列值计算的用户定义表达式的返回值进行选择分区。这个函数可以包含任何在MySQL中有效的表达式，它产生一个非负整数值。KEY分区：类似于HASH分区，不同的是KEY分区只支持计算一列或多列，MySQL服务器提供了自己的哈希函数。必须有一个或多个包含整数值的列。mysql分区的概念请自行google或查询官方文档。我只是在这里扔砖头。我先把在线记录表RANGE按照月份分成12个分区，查询效率提高了6倍左右，但是效果不明显。所以将id改为HASH分区，划分了64个分区，查询速度明显提升。问题已经解决了！结果如下：PARTITIONBYHASH(id)PARTITIONS64selectcount()fromreadroom_website;--11901336行记录/受影响的行数：0条记录：1条警告：0持续时间1查询：5.734sec./select*fromreadroom_websitewheremonth(accesstime)=11limit10;/受影响的行数：0找到的记录：10警告：0持续时间1查询：0.719sec.*/4.表分表就是按照上面的流程优化一个大表，或者查询卡死，然后分这个一个表分成多个表，将一个查询分成多个查询，然后将结果组合返回给用户。表格拆分分为垂直拆分和水平拆分，通常以某个字段作为拆分项。比如id字段拆分成100张表：表名是tableName_id%100但是：分表需要修改源程序代码，会给开发带来很大的工作量，大大增加开发成本，所以：只适用于开发初期考虑到大量数据的存在，分表处理，不适合上线后修改的应用。成本太高了！！！而且选择这个方案并不像选择我提供的第二个和第三个方案那么低！不建议使用。5、分库将一个数据库分成多个。建议读写分开。真正的分库也会带来大量的开发成本，得不偿失！不建议使用。方案二详解：升级数据库，换成100%兼容mysql的数据库。如果mysql性能不好，那就换吧。为了保证不修改源程序代码，顺利迁移现有业务，需要换成100%兼容mysql的数据库。开源选择tiDBhttps://github.com/pingcap/tidbCubridhttps://www.cubrid.org/开源数据库会带来大量的运维成本，其工业品质与MySQL相比还差得很远，踩的坑很多，如果你的公司需要自建数据库，那就选择这类产品。云数据选择阿里云POLARDBhttps://www.aliyun.com/product/polardb?spm=a2c4g.11174283.cloudEssentials.47.7a984b5cS7h4wH官方介绍：POLARDB是阿里云开发的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量高达100T，性能高达MySQL的6倍。POLARDB既结合了商业数据库稳定、可靠、高性能的特点，又具有开源数据库简单、可扩展、持续迭代的优势，成本仅为商业数据库的1/10。我打开它并测试了它。支持免费mysql数据迁移，无运行成本，性能提升10倍左右。价格和rds差不多。这是一个很好的替代解决方案！十一、性能优秀，但在公测中，无法尝试，但值得期待阿里云HybridDBforMySQL（原PetaData）https://www.aliyun.com/product/petadata?spm=a2c4g.11174283.cloudEssentials.54.7a984b5cS7h4wH官方介绍：ApsaraDBforMySQL（原名PetaData）是一款支持海量数据在线交易（OLTP）和在线分析（OLAP）的HTAP（HybridTransaction/AnalyticalProcessing）关系型数据库。我也测试了它。是olap和oltp兼容的方案，但是价格太高，高达10元/小时。用它来储存太浪费了。适用于存储和分析业务。腾讯云DCDBhttps://cloud.tencent.com/product/dcdb_for_tdsql官方介绍：DCDB又称TDSQL，是兼容MySQL协议和语法的高性能分布式数据库，支持自动水平拆分——即业务展示的是完整的逻辑表，但数据被平分到多个分片；每个shard默认采用主备架构，提供容灾、恢复、监控、不停机扩容等一整套解决方案，适用于TB级或PB级海量数据场景。我不喜欢用腾讯的，就不多说了。原因是出了问题找不到人，网上的问题也解决不了头疼的问题！但是便宜，适合超小公司，好玩。方案三详细解释：去掉mysql，换成大数据引擎，处理上亿量级的数据，只好用大数据了。Hadoop开源解决方案系列。只是hbase/配置单元。但运维成本高，一般企业承担不起。没有10万元的投入，就没有好的产出！云解决方案更多，也是未来的趋势。大数据是由专业公司提供专业服务，小公司或个人购买服务。大数据就像水/电等公共设施一样，存在于社会的方方面面。中国最好的是阿里云。我选择了阿里云的MaxCompute来配合DataWorks。使用超级舒服，按量付费，成本极低。MaxCompute可以理解为一个开源的Hive，提供sql/mapreduce/ai算法/python脚本/shell脚本来操作数据。数据以表格形式展示，分布式存储，定时任务和批处理。DataWorks提供了一个工作流来管理您的数据处理任务和调度监控。当然你也可以选择阿里云hbase等其他产品。我主要是用MaxCompute做离线处理，所以选择MaxCompute，基本都是图形界面操作。写完300行左右的SQL，不超过100元的成本就可以解决数据处理问题。
