Mysql大数据量问题与解决方案及如何给字符串加索引[图]

一、Mysql大数据量问题与解决方案
Mysql单表适合的最大数据量是多少？
我们说Mysql单表适合存储的最大数据量，自然不是说能够存储的最大数据量，如果是说能够存储的最大量，那么，如果你使用自增ID，最大就可以存储2^32或2^64条记录了，这是按自增ID的数据类型int或bigint来计算的；如果你不使用自增id，且没有id最大值的限制，如使用足够长度的随机字符串，那么能够限制单表最大数据量的就只剩磁盘空间了。显然我们不是在讨论这个问题。
影响Mysql单表的最优最大数量的一个重要因素其实是索引。
我们知道Mysql的主要存储引擎InnoDB采用B+树结构索引。（至于为什么Mysql选择b+树而不是其他数据结构来组织索引，不是本文讨论的话题，之后的文章会讲到。）那么B+树索引是如何影响Mysql单表数据量的呢？
B+树

一棵B+树如下所示：
Mysql的B+树索引存储在磁盘上，Mysql每次读取磁盘Page的大小是16KB，为了保证每次查询的效率，需要保证每次查询访问磁盘的次数，一般设计为2-3次磁盘访问，再多性能将严重不足。MysqlB+树索引的每个节点需要存储一个指针（8Byte）和一个键值（8Byte）。因此计算16KB/(8B+8B)=1K
16KB可以存储1K个节点，3次磁盘访问(即B+树3的深度)可以存储1K_1K_1K即10亿数据。
如果查询依赖非主键索引，那么还涉及二级索引。这样数据量将更小。
拆分
分而治之——没有什么问题不能通过拆分一次来解决，不行就拆多次。
Mysql单表存储的数据量有限。一个解决大数据量存储的办法就是分库分表。说白了就是一个数据库一张表放不下那么多数据，那就分多个数据库多张表存储。
拆分可分为垂直拆分和水平拆分。
垂直拆分是按照不同的表（或者Schema）来切分到不同的数据库（主机）之上，水平拆分则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面或多张相同Schema的不同表中。
垂直拆分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。
水平拆分与垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。
垂直拆分最直接的就是按领域拆分服务，隔离领域数据库。如此每个库所承担的数据压力就减少了。
水平拆分就是将同一个Schema的数据拆分到不同的库或不同的表中，这样每个表的数据量也将减小，查询效率将更高效。水平拆分就涉及到表的分片规则问题。
几种典型的分片规则包括：
按照用户ID求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中。
按照日期，将不同月甚至日的数据分散到不同的库中。
按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中。
实现
门面模式——没有什么问题不能通过添加一个中间层来解决。
垂直拆分的一个方案就是在应用层使用多个数据源，按业务访问不同的数据源。另外更好方案其实就是微服务化。按不同的业务领域来拆分微服务，明确领域边界，隔离领域数据库。这样将对数据的存取内聚到独立的服务之中，对外提供统一的接口。在需要同时依赖多个服务时，我们可以通过添加门面应用来组合底层服务的数据，以提供更符合上层业务需求的接口，这些服务往往更接近真实的业务。而底层的服务则是更加内聚的资源服务。
代理模式——没有什么问题不能通过添加一个中间层来解决。
对于水平拆分应该尽量屏蔽拆分带来的数据访问困恼，为了让上层业务无需关心下层数据组织方式。水平拆分往往通过添加一个代理层来做这些事情，代理层对上提供虚拟表，这些虚拟表就像我们在单库上设计的单表一样；代理层对下解析和拆分执行sql，然后按相应规则在不同的库和表执行相应的sql请求，再合并数据，并将合并后的结果返回给上层调用者。
一般代理方式分为如下两种：
进程内代理进程内代理即将代理层嵌入到业务服务内部，拦截sql请求并做相应的处理。这样的好处是简单，但是侵入性大，且不够灵活。
进程外代理进程外代理即将代理独立成服务，代理真实业务服务和数据库之间的请求。这样是比较复杂的，需要高可用的代理服务架构。但是这样对业务的侵入性低，且易于升级扩展。
问题
分布式事务问题
什么是分布式事务？本地事务的定义就是一系列相关的数据库操作完成后要满足ACID四大特性，而分布式事务就是将同一进程的操作放到不同的微服务进程中，即不同微服务应用进程的数据库操作满足事务要求，或者对不同数据库的一系列操作需满足事务要求。
这里就有两个问题需要解决。一个是因为应用的分布式造成的，一个是因为数据库本身的分布式造成的。数据库本身的分布式事务问题一般由数据库自身解决，大多数分布式数据库都可以做到一定的数据一致性保证，如HBase保证的强一致性，Cassandra保证的最终一致性。
应用数据的一致性事务方案我们也可以参考分布式数据库的实现原理来实现。业界也有很多分布式事务的解决思路，如：
XA方案
TCC方案
本地消息表
可靠消息最终一致性方案
最大努力通知方案
多表Join问题
通过分析Joinsql，将sql拆分成独立的查询请求，然后分别执行，并将结果合并计算返回给调用者。这个地方会涉及到很多执行优化的问题。
数据统计问题

当数据被分片到不同的数据库或不同的表中时，要对数据做一些全局的或涉及大量数据的统计时便会遇到一些问题。如求Max，Min，Sum等聚合问题。如果统计的数据有一定的业务规则，如只会按用户维度去统计，观察绿豆芽的生长过程（https://www.isanxia.com/sinianji/9961.html）如统计某个用户的订单量，那么对订单表的分片，其实可以采用按用户id来分片，如此就可以解决这类统计问题。但是这种方案不通用。很多分片代理服务都需要将sql分片到不同的节点上去执行，然后再合并结果返回。

ID问题
使用分库分表之后，就无法使用Mysql的表自增作为id，因为不同库和表的自增将出现冲突的id。解决这个问题就需要引入分布式id生成技术（将在以后的文章中讲到）。
二、Mysql性能优化：如何给字符串加索引？
顾名思义，对于列值较长，比如BLOB、TEXT、VARCHAR，就"必须"使用前缀索引，即将值的前一部分作为索引。因为索引的存储也是需要空间的，同样索引太长维护起来也比较困难。
比如我们给User表中的邮箱添加前缀索引，如下:
altertableuseraddindexindex1(email(7));
上述语句将email的前7个字符作为索引。
前缀索引和普通索引比较
我们分别将email的全部作为索引和前7个字符作为索引来看看在性能上有什么差异。建立索引的语句如下：
altertableuseraddindexindex1(email);
altertableuseraddindexindex2(email(7));
假设有user表中有这样几条数据(id,name,email)：(1,"陈某","chenmou1993@xxx")、(2,"张某","chenmou1994@xxx")、(3,"李某","chenmou1995@xxx")、(4,"王某","chenmou1996@xxx")。
对应于index1和index2的索引树如下两张图：
如果执行下面的查询语句，Mysql如何利用索引来查询呢？
select*fromuserwhereemail="chenmou1995@xxx";
【1】普通索引的执行过程
从index1索引树找到满足索引值是chenmou1995@xxx的这条记录，取得id=2的值；
到主键上查到主键值是id=2的行，判断email的值是正确的，将这行记录加入结果集；
取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=chenmou1995@xxx的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
【2】前缀索引的执行过程
从index2索引树找到满足索引值是chenmou的记录，找到的第一个是id=1；
到主键上查到主键值是id=1的行，判断出email的值不是chenmou1995@xxx，这行记录丢弃；
取index2上刚刚查到的位置的下一条记录，发现仍然是chenmou，取出id=2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在idxe2上取到的值不是chenmou时，循环结束。
在这个过程中，要回主键索引取4次数据，也就是扫描了4行。
通过以上查询的对比，很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。
但是对于这个查询语句来说，如果建立的前缀索引的长度为13呢？那么满足chenmou1995的记录只有一个，这样就可以直接定位到id=2，此时不但空间缩小了，扫描的行数也减少了。
于是结论就来了：使用前缀索引，只要定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
那么如何建立正确的前缀索引才能达到最佳的性能呢？接着往下看................
如何建立最佳性能的前缀索引
通过上述的比较，可以得出一个结论，建立前缀索引的区分度越高越好，意味着重复的键值越少。
那么如何统计区分度，其实很简单，只需要判断数据库中重复的次数即可。sql如下：
selectcount(distinctleft(email,4)）asL4,count(distinctleft(email,5)）asL5,count(distinctleft(email,6)）asL6,count(distinctleft(email,7)）asL7,fromuser;
但是如果对于使用前缀区分度不太好的情况，比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。这时候如果对身份证号做长度为6的前缀索引的话，这个索引的区分度就非常低了。
按照我们前面说的方法，可能你需要创建长度为12以上的前缀索引，才能够满足区分度要求。
但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？这种方法，既可以占用更小的空间，也能达到相同的查询效率。现在简单的介绍一种解决此种问题的方式，当然方法肯定不止一种，如下：
倒序存储
如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：
selectfield_listfromtwhereid_card=reverse('输入的身份证号');
由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用count(distinct)方法去做个验证。
前缀索引对覆盖索引的影响
前缀索引会导致覆盖索引失效，查询语句如下：
selectid,namefromuserwhereemail="chenmou1995@xxx";
由于使用了前缀索引，因此必须会回表验证查询到的时候正确，此处使用了覆盖索引也是无效的。
也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
总结
如何给字符串加索引是一个需要考量的问题，陈某在这里给出如下的建议：
如果字符串长度很短，建议直接用全部作为索引。
使用前缀索引注意分析区分度，区分度越高越好。
使用前缀索引需要考虑覆盖索引失效的问题。

本文地址：https://blog.csdn.net/minemi/article/details/110577555