分库分表后如何全站搜索，MySQL如何实现分库分表,如何提高查询效率

大家好，如果您还对分库分表后如何全站搜索不太了解，没有关系，今天就由本站为大家分享分库分表后如何全站搜索的知识，包括MySQL如何实现分库分表,如何提高查询效率的问题都会给大家分析到，还望可以解决大家的问题，下面我们就开始吧！

MySQL如何实现分库分表,如何提高查询效率

本人没有做过电商平台，但了解其中的道道，今天闲来无事，说说其中的道道。下边我要开始表演了。

在大型电商网站中，随着业务的增多，数据库中的数据量也是与日俱增，这时候就要将数据库进行分库分表了。

1、如何分库分表？

两种解决方案：垂直拆分、水平拆分

垂直拆分：根据业务进行拆分，比如可以将一张表中的多个字段拆成两张表，一张是不经常更改的，一张是经常改的。

水平拆分：即根据表来进行分割：比如user表可以拆分为user0,、user1、user2、user3、user4等

2、分库分表之后如何实现联合查询？

可以使用第三方中间件来实现，比如：mycat、shading-jdbc

原理解析：

当客户端发送一条sql查询：select* from user;此时中间件会根据有几个子表，拆分成多个语句：select* from user1;select* from user2;select* from user3等多条语句查询，然后将查询的结果返回给中间件，然后汇总给客户端。这些语句是并发执行的，所以效率会很高哦。

MySQL如何实现分库分表，如何提高查询效率

标签：romhandle更改水平mysqlhand方案字段效率

分库分表后的分页查询

如果要获取第N页的数据(每页S条数据)，则将每一个子库的前N页( offset 0,limit N*S)的所有数据都先查出来(有筛选条件或排序规则的话都包含)，然后将各个子库的结果合并起来之后，再做查询下 top S（可不用带上相同的筛选条件，但还要带上排序规则)即可得出最终结果，这种方式类似es分页的逻辑。

如果要获取第N页的数据，第一页时，是和全局视野法一致，但第二页开始后，需要在每一个子库查询时，加上可以排除上一页的过滤条件(如按时间排序时，获取上一页的最大时间后，需要加上 time>${maxTime_lastPage}的条件；如果没有排序规则，由于是默认主键id的排序规则，也可加上 id>${maxId_lastPage}的条件)，然后再 limit S，即可获取各个子库的结果，之后再合并后 top S即可得到最终结果。在类似app中列表下拉的场景中，业务上可以禁止跳页查询，此时可以使用这种方式。

在大数据量的前提下，需要查询的数据，从概率论角度，是均匀分布在各个字库中的，因此可以假定需要查询的第N页数据，在子库中都处于第 N/X页的前 S/X条中（X=子库数）；所以查询子库时，限定 offset((N/X)-1)*S/X,limit S/X即可，例 N=S=100,X=2时,子库分页条件为 offset 4950,limit 50；然后合并子库结果后即可得出最终结果，当然这个结果是不准确的。在类似网页回帖上的场景下，往往数据精度要求不太高，此时可以使用这种方式。

也是在大数据量的前提下，依据概率论，可以假定需要查询的第N页的数据，在子库中都处于第 N/X页的后面。然后可按如下步骤查询:

1). [第一次查询]按指定条件(筛选条件或排序规则条件)查询各个子库的S条数据，即 offset((N/X)-1)*S/X,limit S

2).如果没有排序规则条件，则默认主键id排序，那么获取各个子库的返回数据的最小值和最大值: min_i_id,max_i_id；如果有排序条件，就按排序条件获取

3).比较各个子库的 min_i_id，得到最小的，定义为 min_id

4). [第二次查询]再次查询(有筛选条件的话也要包含)各个子库，加上条件: min_id<id<max_i_id；(注: min_i_id= min_id的子库可省略查询)

5).查看第二次查询结果中， min_id_id!= min_id的其它子库中，共多了几条数据，如果多了M条，则可以得出全局中，min_id前面的数据有(((N/X)-1)*S/X)*X- M=>((N/X)-1)*S-M条，((N/X)-1)*S-M即为 min_id的全局offset

6).计算真正的全局offset:((N-1)*S)和 min_id的全局offset:((N/X)-1)*S-M之间的差值K，由公式可得: K>=0

7).合并第二次查询的各子库结果，并按id排序后，以 K为offset,S为limit即可得到最终全局的分页结果

参考:

分库分表技术及技术方案

一、分库分表的必要性

分库分表技术的使用，主要是数据库产生了瓶颈，如单库的并发访问或单表的查询都超出了阈值。对系统使用造成一定的影响，不得已而产生的技术。

通过分库分表技术来解决此类问题，但正因为使用此技术，会产生ACID一系列的问题，各类中间件解决此类问题各有各的优势。

提示：如场景无必要，千万不要使用分库分表。

二、分库分表的思路

1、垂直区分

垂直分库：从业务角度，一个库分成多个库，如把订单和用户信息分成两个库来存储。这样的好处就是可以微服务了。每块的业务单独部署，互不影响，通过接口去调用。

垂直分表：把大表分成多个小表，如热点数据和非热点数据分开，提高查询速度。

2、水平区分

水平分表：同一业务如数据量大了以后，根据一定的规则分为不同的表进行存储。

水平分库：如订单分成多个库存储，分解服务器压力。

以上一般来说，垂直分库和水平分表用的会多些。

三、分库分表的原理分析

分库分表常用的方案：Hash取模方案和range范围方案；

路由算法为最主要的算法，指得是把路由的Key按照指定的算法进行存放；

1、Hash取模方案

根据取余分配到不同的表里。要根据实际情况确认模的大小。此方案由于平均分配，不存在热点问题，但数据迁移很复杂。

2、Range范围方案

range根据范围进行划分，如日期，大小。此方案不存在数据迁移，但存在热点问题。

四、分库分表的技术选型

1、技术选型

解决方案主要分为4种：MySQL的分区技术、NoSql、NewSQL、MySQL的分库分表。

（1）mysql分区技术：把一张表存放在不同存储文件。由于无法负载，使用较少。

（2）NoSQL（如MongoDB）：如是订单等比较重要数据，强关联关系，需约束一致性，不太适应。

（3）NewSql(具有NoSQL对海量数据的存储管理能力，还保持了传统数据库支持ACID和SQL等特性):如TiDB可满足需求。

（4）MySQL的分库分表：如使用mysql,此种方案为主流方式。

2、中间件

解决此类问题的中间件主要为：Proxy模式、Client模式。

（1）Proxy模式

（2）Client模式

把分库分表相关逻辑存放在客户端，一版客户端的应用会引用一个jar，然后再jar中处理SQL组合、数据库路由、执行结果合并等相关功能。

（3）中间件的比较

由于Client模式少了一层，运维方便，相对来说容易些。

五、分库分表的实践

根据容量（当前容量和增长量）评估分库或分表个数->选key（均匀）->分表规则（hash或range等）->执行（一般双写）->扩容问题（尽量减少数据的移动）。

在这里我们选用中间件share-jdbc。

1、引入maven依赖

2、spring boot规则配置

行表达式标识符可以使用${...}或$->{...}，但前者与Spring本身的属性文件占位符冲突，因此在Spring环境中使用行表达式标识符建议使用$->{...}。

3、创建DataSource

通过ShardingDataSourceFactory工厂和规则配置对象获取ShardingDataSource，ShardingDataSource实现自JDBC的标准接口DataSource。然后即可通过DataSource选择使用原生JDBC开发，或者使用JPA, MyBatis等ORM工具。