前言有人说，如果单表超过千万条数据，就应该分库分表。这么玩是不合理的。但对于创新业务来说，在业务系统设计时不可能预估这么大的容量，成本和建设周期都不足以完成系统开发工作。我认为对于创新业务系统的设计，首先要满足需求，其次要考虑业务井喷式发展时的临时解决方案，为系统升级预留时间。每个人都希望生意井喷，所以就在这里！具体时间点我就不说了。我开始了一项新业务，看到了一张桌子。本表累计数据条目不超过100万条，提供查询功能。后来业务量不断攀升，mysql磁盘开始报警，查询超时报警。而且客户需要实时查询和下载业务表的数据。暂时改变存储方案已经来不及了，KPI也不能耽误。解决眼前问题，先扩容磁盘。停止两个机房的同步，减少不必要的告警。不过，1000G可能用不了多久。和业务同学商量后，可以实时查询下载业务接受的T-7范围内的数据。按照这个增长速度，7天的时间就有1亿多条记录。不过7天的数据盘肯定够用了，所以历史数据必须先离线存储。这个也简单，几行代码就可以了。当然，这取决于完善的基础设施。容量问题解决了，再优化数据分页查询。为了说明问题，去掉敏感业务数据，数据表结构如下：CREATETABLE`t`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'primarykey',`a`char(32)DEFAULT''COMMENT'',`b`varchar(64)DEFAULTNULLCOMMENT'',`c`bigint(20)unsignedNOTNULLCOMMENT'',`d`varchar(64)NOTNULLCOMMENT'',`e`tinyint(4)DEFAULTNULLCOMMENT'',`f`int(11)NOTNULLDEFAULT'0'COMMENT'',`g`varchar(32)NOTNULLCOMMENT'',`h`char(32)DEFAULTNULLCOMMENT'',`i`varchar(64)DEFAULTNULLCOMMENT'',`j`varchar(64)DEFAULTNULLCOMMENT'',`k`datetimeDEFAULTNULLCOMMENT'',`l`int(11)DEFAULTNULLCOMMENT'',`m`timestampNULLDEFAULTNULLCOMMENT'',`n`timestampNULLDEFAULTNULLCOMMENT''PRIMARYKEY(`id`),UNIQUEKEY`UK_b`(`b`),KEY`IDX_c`(`c`,)USINGBTREE)当数据量较小的时候，我们使用如下分页是没有问题的：SELECTid,a,b...FROMtLIMITn,m例如:pagesize：每页显示的项目数。pageno：页码，然后m=pagesize；n=(pageno-1)*页面大小。MySQLlimit的工作原理是先读取前n条记录，然后舍弃前n条记录，想读取后面的m条记录，所以n越大，offset越大，性能越差。修改sql减少io消耗SELECTid,a,b...FROMtwhereidin(SELECTidFROMtLIMITn,m)其实这样也避免不了扫描前n条，但是时间已经节省了很多。以上是每次页面请求的RT。可以看出，随着页数的增加，RT逐渐上升。Qps逐渐下降。那么如果数据过多的话，最后一页超时的概率会非常高。优化之后，我们来看看优化后的表现。该接口的性能得到了显着改善。如图：RT平均在10ms左右，因为返回做了数据处理，RT最终在15ms左右，qps也很稳定，应该高一些，看客户调用。优化全表扫描肯定是不现实的。这时候我想到了LSM，LogStructuredMergeTrees。这个数据结构在很多产品的文件结构策略中都有使用：HBase、Cassandra、LevelDB、SQLite、Kafka等，是一个非常复杂的复合数据结构，包括WAL（WriteAheadLog）、跳表（SkipList）和分层有序表（SSTable，SortedStringTable）。这里，不需要实现LSM树，只需要参考它的稀疏索引的思想，就可以准确定位数据。就是这样。步骤如下：1、根据分析业务，构建字段a和b的联合索引。因为a和b是数据的查询条件，1/7的数据是可以分开的。ALTERtableADDINDEXindex_a_b('a','b')2.因为这个表的数据是通过insert...onduplicatekeyupdate...更新的，【这也是网上死锁分析文章留下的伏笔】，以及id是一个自增的主键，所以所有的数据都是按照存储的先后顺序进来的，后面遇到冲突的时候，修改也会更新，所以主键id是不会变的。在Redis中设计稀疏索引。在Redis中设计稀疏索引。key=a+b+pagevalue=本页的起始id，以每页2条数据为例key1=ab1value=0;key2=ab1value=4;key3=ab1value=8;...然后是第一页：select*fromtwhereid>0anda='a'b='b'limit2;第二页：select*fromtwhereid>4anda='a'b='b'limit2;第三页：select*fromtwhereid>8anda='a'b='b'limit2;....就可以快速定位到每页的起始id，省去很多扫描操作，同时使用索引，虽然ab联合索引的ab值相同判别度不高，但是这样也保证了ids的顺序，不用orderby。因为主键索引的id本来就是有序的。稀疏索引的计算时机：一批数据加载到数据库后，开始计算稀疏索引。计算方法：第一页：id=0第一页数据select*fromtwhereid>0anda='a'b='b'limit2;第二页：id计算方法；selectmax(t.id)from(select*fromtwhereid>0anda='a'b='b'limit2)t;第三页：id计算方法；selectmax(t.id)from(select*fromtwhereid>【第二页id】anda='a'b='b'limit2)t;…………等等……然后写到redis，更新是一样的。为什么不使用覆盖索引？肯定有人会说为什么不用覆盖索引，这样就不用回表了！答案是不;如果我们返回的字段是a,b,cd,e,f，那么我们建立一个覆盖索引x。x的B+树如下：如果此时我把id=5的值从a=4改成a=1，那么现在id就乱序了！！！！！！！使用覆盖索引+按id排序怎么样？如果数据量不大也是可以的，何必呢。再来看看orderby的原理。首先，MySQL会为每个查询线程分配一块内存，称为sort_buffer，用于排序。这个内存有多大？它是由参数**sort_buffer_size**控制的，可以通过以下命令查看。#查看sort_buffer的大小showvariableslike'sort_buffer_size';这种方式有两个问题：每次都是按照过滤条件进行排序。如果数据量太大，内存不够，会触发文件排序，比较慢。所以用刚才的方案还是老老实实的。效果还不错，只加了几行代码。这个临时解决方案也顺利运行了一年多。(>??)
