ShardingSphere 结合ES、MySQL MHA、Logstash 实现全家桶

生产环境场景如下：请自己代入，参考链接：Mycat 与 ShardingSphere 如何选择：https://blog.nxhz1688.com/2021/01/19/mycat-shardingsphere/

架构图：

这里提供两种解决方案，我们选择第一种，第二种需要DBA辛苦了。

本次操作需要一定的 docker 基础，作者原创，每一步遇见的坑都记录下来了，怎么解决，有什么其他方案。

1

不支持的项

01

路由至多数据节点

不支持 CASE WHEN、HAVING、UNION (ALL)，有限支持子查询。

除了分页子查询的支持之外(详情请参考分页)，也支持同等模式的子查询。无论嵌套多少层，ShardingSphere 都可以解析至第一个包含数据表的子查询，一旦在下层嵌套中再次找到包含数据表的子查询将直接抛出解析异常。

例如，以下子查询可以支持：

SELECT COUNT(*) FROM (SELECT * FROM t_order o)

以下子查询不支持：

SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))

不支持包含 schema 的 SQL。因为 ShardingSphere 的理念是像使用一个数据源一样使用多数据源，因此对 SQL 的访问都是在同一个逻辑 schema 之上。虚拟库概念。

02

对分片键进行操作

运算表达式和函数中的分片键会导致全路由。

假设 create_time 为分片键，则无法精确路由形如 SQL：

SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';

由于 ShardingSphere 只能通过 SQL 字面提取用于分片的值，因此当分片键处于运算表达式或函数中时，ShardingSphere 无法提前获取分片键位于数据库中的值，从而无法计算出真正的分片值。

当出现此类分片键处于运算表达式或函数中的 SQL 时，ShardingSphere 将采用全路由的形式获取结果。

03

不支持的 SQL

SQL	不支持原因
INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …)	VALUES语句不支持运算表达式
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ?	INSERT .. SELECT
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ?	HAVING
SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2	UNION
SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2	UNION ALL
SELECT * FROM ds.tbl_name1	包含 schema
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name	见 DISTINCT支持情况详细说明
SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ?	会导致全路由

2

DISTINCT 支持情况详细说明

01

支持的 SQL

SQL

• SELECT DISTINCT * FROM tbl_name WHERE col1 = ?    

• SELECT DISTINCT col1 FROM tbl_name    

• SELECT DISTINCT col1, col2, col3 FROM tbl_name  

• SELECT DISTINCT col1 FROM tbl_name ORDER BY col1    

• SELECT DISTINCT col1 FROM tbl_name ORDER BY col2    

• SELECT DISTINCT(col1) FROM tbl_name    

• SELECT AVG(DISTINCT col1) FROM tbl_name

• SELECT SUM(DISTINCT col1) FROM tbl_name  

• SELECT COUNT(DISTINCT col1) FROM tbl_name    

• SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1    

• SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name    

• SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name    

• SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1    

• SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1
  

02

不支持的 SQL

SQL	不支持原因
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name	同时使用普通聚合函数和DISTINCT 聚合函数

3

分页性能

01

性能瓶颈

查询偏移量过大的分页会导致数据库获取数据性能低下，以 MySQL 为例：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

这句 SQL 会使得 MySQL 在无法利用索引的情况下跳过 1000000 条记录后，再获取 10 条记录，其性能可想而知。而在分库分表的情况下（假设分为 2 个库），为了保证数据的正确性，SQL 会改写为：

SELECT * FROM t_order ORDER BY id LIMIT 0, 1000010

即将偏移量前的记录全部取出，并仅获取排序后的最后 10条记录。这会在数据库本身就执行很慢的情况下，进一步加剧性能瓶颈。因为原 SQL 仅需要传输 10 条记录至客户端，而改写之后的 SQL 则会传输 1,000,010 * 2 的记录至客户端。

02

ShardingSphere 的优化

ShardingSphere 进行了 2 个方面的优化。

首先，采用流式处理 + 归并排序的方式来避免内存的过量占用。由于 SQL 改写不可避免的占用了额外的带宽，但并不会导致内存暴涨。与直觉不同，大多数人认为ShardingSphere 会将 1,000,010 * 2 记录全部加载至内存，进而占用大量内存而导致内存溢出。但由于每个结果集的记录是有序的，因此 ShardingSphere 每次比较仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已。对于本身即有序的待排序对象，归并排序的时间复杂度仅为 O(n) ，性能损耗很小。

其次，ShardingSphere 对仅落至单分片的查询进行进一步优化。落至单分片查询的请求并不需要改写SQL也可以保证记录的正确性，因此在此种情况下，ShardingSphere 并未进行 SQL 改写，从而达到节省带宽的目的。

4

分页方案优化

由于 LIMIT 并不能通过索引查询数据，因此如果可以保证ID 的连续性，通过 ID 进行分页是比较好的解决方案：

SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id

或通过记录上次查询结果的最后一条记录的 ID 进行下一页的查询：

SELECT * FROM t_order WHERE id > 100000 LIMIT 10

数据层	缓存	数据同步
Mysql MHA	Elasticsearch	Logstash

 需求如下 

目前涉及到的是生产环境改造：底层数据由 MySQL 存储，MHA 实现集群高可用，目前数据集群没有 配置 vip 漂移策略，也没有什么前置代理，所以后端服务直接怼的是主master 节点，这里有个问题，主从同步由半同步插件在做，MHA 高可用其实只完成了一半，为啥这样，别问我😂，问就是不知道，后台数据通过 logstash 将主节点数据实时同步只 ES，查询通过 ES 进行。现在的问题是数据量大，很大，有多大，一个索引一个月已经 120G。好吧，这个数据是按照设备走的，咱不关心，现在问题是 ES 这么大了，MySQL咋办。

 需要考虑的问题如下 

• MHA 代理服务如何处理？不能把所有的节点都配置成，分表插件的代理节点吧？

• Logstash 访问的代理服务，如何处理？

• 底层服务访问的代理服务，如何处理？

• 分表插件对于 MHA 集群如何分片？如何分表？
  

• 分库分表后，数据同步 Logstash 如何进行数据同步？

• 分库分表插件，代理、分片、分库怎样进行合理操作？

• 怎样保证上下游高可用？

问题有点多，还都比较棘手，先不说其他，先整下ShardingSphere，为啥不用 MyCat,一个是资源，一个是坑大，为啥说坑大，Google 搜搜，或者看看前面提到的Mycat 与 ShardingSphere 如何选择。

本次使用 docker 进行 ShardingSphere 组件搭建，生产环境请根据自己公司的具体情况进行选择

组件	实现方式
MySQL MHA	Centos 部署
Zookeeper	Docker
ShardingSphere-Proxy	Docker
ShardingSphere-UI	Docker
ShardingSphere	代码服务层面

01

部署 Zookeeper 服务，做注册中心

zookeeper 比较常用，占用的资源也很小，所以我用了 –restart unless-stopped，表示除非人工 stop 这个容器，否则这个容器每次都自动重启。

docker run -p 2181:2181 --name zk --restart unless-stopped -d zookeeper

02

新建 mysql 实例

我这里使用 docker-compose 新建 4 个 mysql 8.0 实例用作测试。

新建 docker-compose.yml    

vim /home/mysql/docker-compose.yml    

version: '3.7'
services:
    mysql8_1:
        image: mysql:8.0.17
        container_name: mysql8_1
        ports:
            - "33080:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
    mysql8_2:
        image: mysql:8.0.17
        container_name: mysql8_2
        ports:
            - "33081:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
    mysql8_3:
        image: mysql:8.0.17
        container_name: mysql8_3
        ports:
            - "33082:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678
    mysql8_4:
        image: mysql:8.0.17
        container_name: mysql8_4
        ports:
            - "33083:3306"
        environment:
            MYSQL_ROOT_PASSWORD: 12345678

启动服务,弱弱说一句，这个有问题，找度娘很好解决
cd /home/mysql
docker-compose up

03

部署 ShardingSphere-Proxy

1. 先获取镜像，可以通过 search 查询最新版👀有点懵，去官方看看推荐用什么版本 官方版本 docker search sharding-proxy

2. 下载镜像，不写版本默认最新 docker pull apache/sharding-proxy

3. 运行容器修改配置文件指向主机地址，替换自己的地址，我的配置放在 home 下了，别学我

docker run –name sharding-proxy -d -v /home/sharding-proxy/conf:/opt/sharding-proxy/conf -v /home/sharding-proxy/ext-lib:/opt/sharding-proxy/ext-lib -p13308:3308 -e PORT=3308 apache/sharding-proxy:latest

04

配置 ShardingSphere-Proxy 

在自己的配置文件夹下面新建文件，例如：

cd /home/sharding-proxy/conf
vim config-test.yaml

内容如下：

schemaName: sharding_db


dataSources:
  ds_0:
    url: jdbc:mysql://mysql8_1:3306/demo_ds_0?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8
  ds_1:
    url: jdbc:mysql://mysql8_1:3306/demo_ds_1?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8


dataSourceCommon:
  username: root
  password: 12345678
  connectionTimeoutMilliseconds: 300000
  idleTimeoutMilliseconds: 600000
  maxLifetimeMilliseconds: 1800000
  maxPoolSize: 10000
  minPoolSize: 100
  maintenanceIntervalMilliseconds: 3000000


rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:


  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 2}


  keyGenerators:
    snowflake:
      type: SNOWFLAKE
      props:
        worker-id: 123

vim server.yaml

# 用户通过 Navicat 访问 sharding-proxy 的用户名密码
authentication:
  users:
    root: # 自定义用户名
      password: 12345678 # 自定义密码
    sharding: # 自定义用户名
      password: sharding # 自定义密码
      authorizedSchemas: sharding_db, replica_query_db # 该用户授权可访问的数据库，多个用逗号分隔。缺省将拥有 root 权限，可访问全部数据库。


# sharding-proxy相关配置，建议sql.show设置为true，方便定位问题
props:
  max.connections.size.per.query: 1
  acceptor.size: 16
  executor.size: 16
  proxy.transaction.enabled: false
  proxy.opentracing.enabled: false
  sql-show: true

05

测试 ShardingSphere-proxy 代理服务

5.1 跟着俺，单实例 MySQL，代理配置测试，连接 MySQL，注意连接的是真实数据库，330801

DROP SCHEMA IF EXISTS demo_ds_0;DROP SCHEMA IF EXISTS demo_ds_1;CREATE SCHEMA IF NOT EXISTS demo_ds_0;CREATE SCHEMA IF NOT EXISTS demo_ds_1;

5.2 启动 ShardingSphere-proxy 容器

docker restart sharding-proxy
docker logs -f sharding-proxy

5.3  使用工具或者安装了 MySQL 客户端的命令访问ShardingSphere-proxy 代理服务    

    5.3.1 MySQL 客户端操作，如下操作认为服务已经代理成功    

到这一步就可以直接操作数据库了，测试一下，建表，写库，查询    

• 建表    

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));

CREATE TABLE IF NOT EXISTS demo_ds_0.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));
CREATE TABLE IF NOT EXISTS demo_ds_1.t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_item_id));    

• 写数据    

  INSERT INTO t_order (user_id, status) VALUES (1, ‘init’);
  INSERT INTO t_order (user_id, status) VALUES (1, ‘init’);
  INSERT INTO t_order (user_id, status) VALUES (2, ‘init’);    

    5.3.2 Navicat MySQL 建立连接 ，因为想用 docker， 因此下载 4.1.1 的源码，自己做 docker 镜像，不想用的直接跳过

5

Q&A

Q1：openJDK 问题

A1：最终我修改容器配置处理了，使用的就是上面提到修改 /var/lib/docker/containers/ 下的配置文件，这一步大家应该也用不到，除非，你就是要用容器部署，想和使用主机的 JDK

Q2：网络问题

A2：容器网络建立，会的大神，自己操作就好，因为我的 MySQL 使用 docker-compose 启动的，ShardingSphere-proxy是单独启动的，要把 ShardingSphere 加 入MySQL 网络中。我就不详细写了，大家看图：

本文由杨扬授权转载

关于 Apache ShardingSphere

Apache ShardingSphere 是一款分布式 SQL 事务和查询引擎，可通过数据分片、弹性伸缩、加密等能力对任意数据库进行增强。