一、Sharding-JDBC 简介

Sharding-JDBC 最早是当当网内部使用的一款分库分表框架，到2017年的时候才开始对外开源，这几年在大量社区贡献者的不断迭代下，功能也逐渐完善，现已更名为 ShardingSphere，2020年4⽉16⽇正式成为 Apache 软件基⾦会的顶级项⽬。

随着版本的不断更迭 ShardingSphere 的核心功能也变得多元化起来。从最开始 Sharding-JDBC 1.0 版本只有数据分片，到 Sharding-JDBC 2.0 版本开始支持数据库治理（注册中心、配置中心等等），再到 Sharding-JDBC 3.0版本又加分布式事务 （支持 Atomikos、Narayana、Bitronix、Seata），如今已经迭代到了 Sharding-JDBC 4.0 版本。

现在的 ShardingSphere 不单单是指某个框架而是一个生态圈，这个生态圈 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar 这三款开源的分布式数据库中间件解决方案所构成。

ShardingSphere 的前身就是 Sharding-JDBC，所以它是整个框架中最为经典、成熟的组件，我们先从 Sharding-JDBC 框架入手学习分库分表。

二、核心概念

在开始 Sharding-JDBC分库分表具体实战之前，我们有必要先了解分库分表的一些核心概念。

分片

一般我们在提到分库分表的时候，大多是以水平切分模式（水平分库、分表）为基础来说的，数据分片将原本一张数据量较大的表 t_order 拆分生成数个表结构完全一致的小数据量表 t_order_0、t_order_1、···、t_order_n，每张表只存储原大表中的一部分数据，当执行一条SQL时会通过 分库策略、分片策略 将数据分散到不同的数据库、表内。

数据节点

数据节点是分库分表中一个不可再分的最小数据单元（表），它由数据源名称和数据表组成，例如上图中 order_db_1.t_order_0、order_db_2.t_order_1 就表示一个数据节点。

逻辑表

逻辑表是指一组具有相同逻辑和数据结构表的总称。比如我们将订单表t_order 拆分成 t_order_0 ··· t_order_9 等 10张表。此时我们会发现分库分表以后数据库中已不在有 t_order 这张表，取而代之的是 t_order_n，但我们在代码中写 SQL 依然按 t_order 来写。此时 t_order 就是这些拆分表的逻辑表。

真实表

真实表也就是上边提到的 t_order_n 数据库中真实存在的物理表。

分片键

用于分片的数据库字段。我们将 t_order 表分片以后，当执行一条SQL时，通过对字段 order_id 取模的方式来决定，这条数据该在哪个数据库中的哪个表中执行，此时 order_id 字段就是 t_order 表的分片健。

这样以来同一个订单的相关数据就会存在同一个数据库表中，大幅提升数据检索的性能，不仅如此 sharding-jdbc 还支持根据多个字段作为分片健进行分片。

分片算法

上边我们提到可以用分片健取模的规则分片，但这只是比较简单的一种，在实际开发中我们还希望用 >=、<=、>、<、BETWEEN 和 IN 等条件作为分片规则，自定义分片逻辑，这时就需要用到分片策略与分片算法。

从执行 SQL 的角度来看，分库分表可以看作是一种路由机制，把 SQL 语句路由到我们期望的数据库或数据表中并获取数据，分片算法可以理解成一种路由规则。

咱们先捋一下它们之间的关系，分片策略只是抽象出的概念，它是由分片算法和分片健组合而成，分片算法做具体的数据分片逻辑。

分库、分表的分片策略配置是相对独立的，可以各自使用不同的策略与算法，每种策略中可以是多个分片算法的组合，每个分片算法可以对多个分片健做逻辑判断。

分片算法和分片策略的关系

注意：sharding-jdbc 并没有直接提供分片算法的实现，需要开发者根据业务自行实现。

sharding-jdbc 提供了4种分片算法：

1、精确分片算法

精确分片算法（PreciseShardingAlgorithm）用于单个字段作为分片键，SQL中有 = 与 IN 等条件的分片，需要在标准分片策略（StandardShardingStrategy ）下使用。

2、范围分片算法

范围分片算法（RangeShardingAlgorithm）用于单个字段作为分片键，SQL中有 BETWEEN AND、>、<、>=、<= 等条件的分片，需要在标准分片策略（StandardShardingStrategy ）下使用。

3、复合分片算法

复合分片算法（ComplexKeysShardingAlgorithm）用于多个字段作为分片键的分片操作，同时获取到多个分片健的值，根据多个字段处理业务逻辑。需要在复合分片策略（ComplexShardingStrategy ）下使用。

4、Hint分片算法

Hint分片算法（HintShardingAlgorithm）稍有不同，上边的算法中我们都是解析SQL 语句提取分片键，并设置分片策略进行分片。但有些时候我们并没有使用任何的分片键和分片策略，可还想将 SQL 路由到目标数据库和表，就需要通过手动干预指定SQL的目标数据库和表信息，这也叫强制路由。

分片策略

上边讲分片算法的时候已经说过，分片策略是一种抽象的概念，实际分片操作的是由分片算法和分片健来完成的。

1、标准分片策略

标准分片策略适用于单分片键，此策略支持 PreciseShardingAlgorithm 和 RangeShardingAlgorithm 两个分片算法。

其中 PreciseShardingAlgorithm 是必选的，用于处理 = 和 IN 的分片。RangeShardingAlgorithm 是可选的，用于处理BETWEEN AND， >， <，>=，<= 条件分片，如果不配置RangeShardingAlgorithm，SQL中的条件等将按照全库路由处理。

2、复合分片策略

复合分片策略，同样支持对 SQL语句中的 =，>， <， >=， <=，IN和 BETWEEN AND 的分片操作。不同的是它支持多分片键，具体分配片细节完全由应用开发者实现。

3、行表达式分片策略

行表达式分片策略，支持对 SQL语句中的 = 和 IN 的分片操作，但只支持单分片键。这种策略通常用于简单的分片，不需要自定义分片算法，可以直接在配置文件中接着写规则。

t_order_$->{t_order_id % 4} 代表 t_order 对其字段 t_order_id取模，拆分成4张表，而表名分别是t_order_0 到 t_order_3。

4、Hint分片策略

Hint分片策略，对应上边的Hint分片算法，通过指定分片健而非从 SQL中提取分片健的方式进行分片的策略。

分布式主键

数据分⽚后，不同数据节点⽣成全局唯⼀主键是⾮常棘⼿的问题，同⼀个逻辑表（t_order）内的不同真实表（t_order_n）之间的⾃增键由于⽆法互相感知而产⽣重复主键。

尽管可通过设置⾃增主键 初始值 和 步⻓ 的⽅式避免ID碰撞，但这样会使维护成本加大，乏完整性和可扩展性。如果后去需要增加分片表的数量，要逐一修改分片表的步长，运维成本非常高，所以不建议这种方式。

实现分布式主键⽣成器的方式很多，可以参考我之前写的《9种分布式ID生成方式》。

为了让上手更加简单，ApacheShardingSphere 内置了UUID、SNOWFLAKE 两种分布式主键⽣成器，默认使⽤雪花算法（snowflake）⽣成64bit的⻓整型数据。不仅如此它还抽离出分布式主键⽣成器的接口，⽅便我们实现⾃定义的⾃增主键⽣成算法。

广播表

广播表：存在于所有的分片数据源中的表，表结构和表中的数据在每个数据库中均完全一致。一般是为字典表或者配置表 t_config，某个表一旦被配置为广播表，只要修改某个数据库的广播表，所有数据源中广播表的数据都会跟着同步。

绑定表

绑定表：那些分片规则一致的主表和子表。比如：t_order 订单表和 t_order_item 订单服务项目表，都是按 order_id 字段分片，因此两张表互为绑定表关系。

那绑定表存在的意义是啥呢？

通常在我们的业务中都会使用 t_order 和 t_order_item 等表进行多表联合查询，但由于分库分表以后这些表被拆分成N多个子表。如果不配置绑定表关系，会出现笛卡尔积关联查询，将产生如下四条SQL。

1
2
3
4

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id 
SELECT * FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id 
SELECT * FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id

笛卡尔积查询

而配置绑定表关系后再进行关联查询时，只要对应表分片规则一致产生的数据就会落到同一个库中，那么只需 t_order_0 和 t_order_item_0 表关联即可。

1
2

SELECT * FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id 
SELECT * FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id

绑定表关系

注意：在关联查询时 t_order 它作为整个联合查询的主表。所有相关的路由计算都只使用主表的策略，t_order_item 表的分片相关的计算也会使用 t_order 的条件，所以要保证绑定表之间的分片键要完全相同。

三、和JDBC的猫腻

从名字上不难看出，Sharding-JDBC 和 JDBC有很大关系，我们知道 JDBC 是一种 Java 语言访问关系型数据库的规范，其设计初衷就是要提供一套用于各种数据库的统一标准，不同厂家共同遵守这套标准，并提供各自的实现方案供应用程序调用。

但其实对于开发人员而言，我们只关心如何调用 JDBC API 来访问数据库，只要正确使用 DataSource、Connection、Statement 、ResultSet 等 API 接口，直接操作数据库即可。所以如果想在 JDBC 层面实现数据分片就必须对现有的 API 进行功能拓展，而 Sharding-JDBC 正是基于这种思想，重写了 JDBC 规范并完全兼容了 JDBC 规范。

JDBC流程

对原有的 DataSource、Connection 等接口扩展成 ShardingDataSource、ShardingConnection，而对外暴露的分片操作接口与 JDBC 规范中所提供的接口完全一致，只要你熟悉 JDBC 就可以轻松应用 Sharding-JDBC 来实现分库分表。

因此它适用于任何基于 JDBC 的 ORM 框架，如：JPA， Hibernate，Mybatis，Spring JDBC Template 或直接使用的 JDBC。完美兼容任何第三方的数据库连接池，如：DBCP， C3P0， BoneCP，Druid， HikariCP 等，几乎对主流关系型数据库都支持。

那 Sharding-JDBC 又是如何拓展这些接口的呢？想知道答案我们就的从源码入手了，下边我们以 JDBC API 中的 DataSource 为例看看它是如何被重写扩展的。

数据源 DataSource 接口的核心作用就是获取数据库连接对象 Connection，我们看其内部提供了两个获取数据库连接的方法 ，并且继承了 CommonDataSource 和 Wrapper 两个接口。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

public interface DataSource  extends CommonDataSource, Wrapper {

  /**
   * <p>Attempts to establish a connection with the data source that
   * this {@code DataSource} object represents.
   * @return  a connection to the data source
   */
  Connection getConnection() throws SQLException;

  /**
   * <p>Attempts to establish a connection with the data source that
   * this {@code DataSource} object represents.
   * @param username the database user on whose behalf the connection is
   *  being made
   * @param password the user's password
   */
  Connection getConnection(String username, String password)
    throws SQLException;
}

其中 CommonDataSource 是定义数据源的根接口这很好理解，而 Wrapper 接口则是拓展 JDBC 分片功能的关键。

由于数据库厂商的不同，他们可能会各自提供一些超越标准 JDBC API 的扩展功能，但这些功能非 JDBC 标准并不能直接使用，而 Wrapper 接口的作用就是把一个由第三方供应商提供的、非 JDBC 标准的接口包装成标准接口，也就是适配器模式。

既然讲到了适配器模式就多啰嗦几句，也方便后边的理解。

适配器模式个种比较常用的设计模式，它的作用是将某个类的接口转换成客户端期望的另一个接口，使原本因接口不匹配（或者不兼容）而无法在一起工作的两个类能够在一起工作。比如用耳机听音乐，我有个圆头的耳机，可手机插孔却是扁口的，如果我想要使用耳机听音乐就必须借助一个转接头才可以，这个转接头就起到了适配作用。举个栗子：假如我们 Target 接口中有 hello() 和 word() 两个方法。

1
2
3
4
5
6

public interface Target {

    void hello();

    void world();
}1.2.3.4.5.6.

可由于接口版本迭代Target 接口的 word() 方法可能会被废弃掉或不被支持，Adaptee 类的 greet()方法将代替hello() 方法。

1
2
3
4
5
6
7
8
9

public class Adaptee {

    public void graeet(){

    }
    public void world(){

    }
}

但此时旧版本仍然有大量 word() 方法被使用中，解决此事最好的办法就是创建一个适配器Adapter，这样就适配了 Target 类，解决了接口升级带来的兼容性问题。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

public class Adapter extends Adaptee implements Target {

    @Override
    public void world() {
        
    }

    @Override
    public void hello() {
        super.greet();
    }

    @Override
    public void greet() {
        
    }
}

而 Sharding-JDBC 提供的正是非 JDBC 标准的接口，所以它也提供了类似的实现方案，也使用到了 Wrapper 接口做数据分片功能的适配。除了 DataSource 之外，Connection、Statement、ResultSet 等核心对象也都继承了这个接口。

下面我们通过 ShardingDataSource 类源码简单看下实现过程，下图是继承关系流程图。

ShardingDataSource实现流程

ShardingDataSource 类它在原 DataSource 基础上做了功能拓展，初始化时注册了分片SQL路由包装器、SQL重写上下文和结果集处理引擎，还对数据源类型做了校验，因为它要同时支持多个不同类型的数据源。到这好像也没看出如何适配，那接着向上看 ShardingDataSource 的继承类 AbstractDataSourceAdapter 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

@Getter
public class ShardingDataSource extends AbstractDataSourceAdapter {
    
    private final ShardingRuntimeContext runtimeContext;

    /**
     * 注册路由、SQl重写上下文、结果集处理引擎
     */
    static {
        NewInstanceServiceLoader.register(RouteDecorator.class);
        NewInstanceServiceLoader.register(SQLRewriteContextDecorator.class);
        NewInstanceServiceLoader.register(ResultProcessEngine.class);
    }

    /**
     * 初始化时校验数据源类型 并根据数据源 map、分片规则、数据库类型得到一个分片上下文，用来获取数据库连接
     */
    public ShardingDataSource(final Map<String, DataSource> dataSourceMap, final ShardingRule shardingRule, final Properties props) throws SQLException {
        super(dataSourceMap);
        checkDataSourceType(dataSourceMap);
        runtimeContext = new ShardingRuntimeContext(dataSourceMap, shardingRule, props, getDatabaseType());
    }

    private void checkDataSourceType(final Map<String, DataSource> dataSourceMap) {
        for (DataSource each : dataSourceMap.values()) {
            Preconditions.checkArgument(!(each instanceof MasterSlaveDataSource), "Initialized data sources can not be master-slave data sources.");
        }
    }

    /**
     * 数据库连接
     */
    @Override
    public final ShardingConnection getConnection() {
        return new ShardingConnection(getDataSourceMap(), runtimeContext, TransactionTypeHolder.get());
    }
}

AbstractDataSourceAdapter 抽象类内部主要获取不同类型的数据源对应的数据库连接对象，实现 AutoCloseable 接口是为在使用完资源后可以自动将这些资源关闭（调用 close方法），那再看看继承类 AbstractUnsupportedOperationDataSource 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

@Getter
public abstract class AbstractDataSourceAdapter extends AbstractUnsupportedOperationDataSource implements AutoCloseable {
    
    private final Map<String, DataSource> dataSourceMap;
    
    private final DatabaseType databaseType;
    
    public AbstractDataSourceAdapter(final Map<String, DataSource> dataSourceMap) throws SQLException {
        this.dataSourceMap = dataSourceMap;
        databaseType = createDatabaseType();
    }
    
    public AbstractDataSourceAdapter(final DataSource dataSource) throws SQLException {
        dataSourceMap = new HashMap<>(1, 1);
        dataSourceMap.put("unique", dataSource);
        databaseType = createDatabaseType();
    }
    
    private DatabaseType createDatabaseType() throws SQLException {
        DatabaseType result = null;
        for (DataSource each : dataSourceMap.values()) {
            DatabaseType databaseType = createDatabaseType(each);
            Preconditions.checkState(null == result || result == databaseType, String.format("Database type inconsistent with '%s' and '%s'", result, databaseType));
            result = databaseType;
        }
        return result;
    }
    
    /**
     * 不同数据源类型获取数据库连接
     */
    private DatabaseType createDatabaseType(final DataSource dataSource) throws SQLException {
        if (dataSource instanceof AbstractDataSourceAdapter) {
            return ((AbstractDataSourceAdapter) dataSource).databaseType;
        }
        try (Connection connection = dataSource.getConnection()) {
            return DatabaseTypes.getDatabaseTypeByURL(connection.getMetaData().getURL());
        }
    }
    
    @Override
    public final Connection getConnection(final String username, final String password) throws SQLException {
        return getConnection();
    }
    
    @Override
    public final void close() throws Exception {
        close(dataSourceMap.keySet());
    }
}

AbstractUnsupportedOperationDataSource 实现DataSource 接口并继承了 WrapperAdapter 类，它内部并没有什么具体方法只起到桥接的作用，但看着是不是和我们前边讲适配器模式的例子方式有点相似。

1
2
3
4
5
6
7
8
9
10
11
12

public abstract class AbstractUnsupportedOperationDataSource extends WrapperAdapter implements DataSource {
    
    @Override
    public final int getLoginTimeout() throws SQLException {
        throw new SQLFeatureNotSupportedException("unsupported getLoginTimeout()");
    }
    
    @Override
    public final void setLoginTimeout(final int seconds) throws SQLException {
        throw new SQLFeatureNotSupportedException("unsupported setLoginTimeout(int seconds)");
    }
}

WrapperAdapter 是一个包装器的适配类，实现了 JDBC 中的 Wrapper 接口，其中有两个核心方法 recordMethodInvocation 用于添加需要执行的方法和参数，而 replayMethodsInvocation 则将添加的这些方法和参数通过反射执行。仔细看不难发现两个方法中都用到了 JdbcMethodInvocation类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

public abstract class WrapperAdapter implements Wrapper {
    
    private final Collection<JdbcMethodInvocation> jdbcMethodInvocations = new ArrayList<>();
 
    /**
     * 添加要执行的方法
     */
    @SneakyThrows
    public final void recordMethodInvocation(final Class<?> targetClass, final String methodName, final Class<?>[] argumentTypes, final Object[] arguments) {
        jdbcMethodInvocations.add(new JdbcMethodInvocation(targetClass.getMethod(methodName, argumentTypes), arguments));
    }
    
    /**
     * 通过反射执行 上边添加的方法
     */
    public final void replayMethodsInvocation(final Object target) {
        for (JdbcMethodInvocation each : jdbcMethodInvocations) {
            each.invoke(target);
        }
    }
}

JdbcMethodInvocation 类主要应用反射通过传入的 method 方法和 arguments 参数执行对应的方法，这样就可以通过 JDBC API 调用非 JDBC 方法了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

@RequiredArgsConstructor
public class JdbcMethodInvocation {
    
    @Getter
    private final Method method;
    
    @Getter
    private final Object[] arguments;
    
    /**
     * Invoke JDBC method.
     * 
     * @param target target object
     */
    @SneakyThrows
    public void invoke(final Object target) {
        method.invoke(target, arguments);
    }
}

那 Sharding-JDBC 拓展 JDBC API 接口后，在新增的分片功能里又做了哪些事情呢？

一张表经过分库分表后被拆分成多个子表，并分散到不同的数据库中，在不修改原业务 SQL 的前提下，Sharding-JDBC 就必须对 SQL进行一些改造才能正常执行。

大致的执行流程：SQL 解析 -> 执⾏器优化 -> SQL 路由 -> SQL 改写 -> SQL 执⾏ -> 结果归并 六步组成，一起瞅瞅每个步骤做了点什么。

SQL 解析

SQL解析过程分为词法解析和语法解析两步，比如下边这条查询用户订单的SQL，先用词法解析将SQL拆解成不可再分的原子单元。在根据不同数据库方言所提供的字典，将这些单元归类为关键字，表达式，变量或者操作符等类型。

1

SELECT order_no,price FROM t_order_ where user_id = 10086 and order_status > 0

接着语法解析会将拆分后的SQL转换为抽象语法树，通过对抽象语法树遍历，提炼出分片所需的上下文，上下文包含查询字段信息（Field）、表信息（Table）、查询条件（Condition）、排序信息（Order By）、分组信息（Group By）以及分页信息（Limit）等，并标记出 SQL中有可能需要改写的位置。

抽象语法树

执⾏器优化

执⾏器优化对SQL分片条件进行优化，处理像关键字 OR这种影响性能的坏味道。

SQL 路由

SQL 路由通过解析分片上下文，匹配到用户配置的分片策略，并生成路由路径。简单点理解就是可以根据我们配置的分片策略计算出 SQL该在哪个库的哪个表中执行，而SQL路由又根据有无分片健区分出 分片路由 和 广播路由。

官方路由图谱

有分⽚键的路由叫分片路由，细分为直接路由、标准路由和笛卡尔积路由这3种类型。

标准路由

标准路由是最推荐也是最为常⽤的分⽚⽅式，它的适⽤范围是不包含关联查询或仅包含绑定表之间关联查询的SQL。

当 SQL分片健的运算符为 = 时，路由结果将落⼊单库（表），当分⽚运算符是BETWEEN 或IN 等范围时，路由结果则不⼀定落⼊唯⼀的库（表），因此⼀条逻辑SQL最终可能被拆分为多条⽤于执⾏的真实SQL。

1

SELECT * FROM t_order  where t_order_id in (1,2)

SQL路由处理后

1
2

SELECT * FROM t_order_0  where t_order_id in (1,2)
SELECT * FROM t_order_1  where t_order_id in (1,2)

直接路由

直接路由是通过使用 HintAPI 直接将 SQL路由到指定⾄库表的一种分⽚方式，而且直接路由可以⽤于分⽚键不在SQL中的场景，还可以执⾏包括⼦查询、⾃定义函数等复杂情况的任意SQL。

比如根据 t_order_id 字段为条件查询订单，此时希望在不修改SQL的前提下，加上 user_id作为分片条件就可以使用直接路由。

笛卡尔积路由

笛卡尔路由是由⾮绑定表之间的关联查询产生的，查询性能较低尽量避免走此路由模式。

无分⽚键的路由又叫做广播路由，可以划分为全库表路由、全库路由、 全实例路由、单播路由和阻断路由这 5种类型。

全库表路由

全库表路由针对的是数据库 DQL和 DML，以及 DDL等操作，当我们执行一条逻辑表 t_order SQL时，在所有分片库中对应的真实表 t_order_0 ··· t_order_n 内逐一执行。

全库路由

全库路由主要是对数据库层面的操作，比如数据库 SET 类型的数据库管理命令，以及 TCL 这样的事务控制语句。

对逻辑库设置 autocommit 属性后，所有对应的真实库中都执行该命令。

1

SET autocommit=0;

全实例路由

全实例路由是针对数据库实例的 DCL 操作（设置或更改数据库用户或角色权限），比如：创建一个用户 order ，这个命令将在所有的真实库实例中执行，以此确保 order 用户可以正常访问每一个数据库实例。

1

CREATE USER order@127.0.0.1 identified BY '程序员内点事';

单播路由

单播路由用来获取某一真实表信息，比如获得表的描述信息：

1

DESCRIBE t_order; 

t_order 的真实表是 t_order_0 ···· t_order_n，他们的描述结构相完全同，我们只需在任意的真实表执行一次就可以。

阻断路由

⽤来屏蔽SQL对数据库的操作，例如：

1

USE order_db;

这个命令不会在真实数据库中执⾏，因为ShardingSphere 采⽤的是逻辑 Schema（数据库的组织和结构） ⽅式，所以无需将切换数据库的命令发送⾄真实数据库中。

SQL 改写

将基于逻辑表开发的SQL改写成可以在真实数据库中可以正确执行的语句。比如查询 t_order 订单表，我们实际开发中 SQL是按逻辑表 t_order 写的。

1

SELECT * FROM t_order

但分库分表以后真实数据库中 t_order 表就不存在了，而是被拆分成多个子表 t_order_n 分散在不同的数据库内，还按原SQL执行显然是行不通的，这时需要将分表配置中的逻辑表名称改写为路由之后所获取的真实表名称。

1

SELECT * FROM t_order_n

SQL执⾏

将路由和改写后的真实 SQL 安全且高效发送到底层数据源执行。但这个过程并不是简单的将 SQL 通过JDBC 直接发送至数据源执行，而是平衡数据源连接创建以及内存占用所产生的消耗，它会自动化的平衡资源控制与执行效率。

结果归并

将从各个数据节点获取的多数据结果集，合并成一个大的结果集并正确的返回至请求客户端，称为结果归并。而我们SQL中的排序、分组、分页和聚合等语法，均是在归并后的结果集上进行操作的。

四、快速实践

下面我们结合 Springboot + mybatisplus 快速搭建一个分库分表案例。

1、准备工作

先做准备工作，创建两个数据库 ds-0、ds-1，两个库中分别建表 t_order_0、t_order_1、t_order_2 、t_order_item_0、t_order_item_1、t_order_item_2，t_config，方便后边验证广播表、绑定表的场景。

表结构如下：

t_order_0 订单表

1
2
3
4
5
6
7

CREATE TABLE `t_order_0` (
  `order_id` bigint(200) NOT NULL,
  `order_no` varchar(100) DEFAULT NULL,
  `create_name` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

t_order_0 与 t_order_item_0 互为关联表

1
2
3
4
5
6
7

CREATE TABLE `t_order_item_0` (
  `item_id` bigint(100) NOT NULL,
  `order_no` varchar(200) NOT NULL,
  `item_name` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

广播表 t_config

1
2
3
4
5
6

  `id` bigint(30) NOT NULL,
  `remark` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ShardingSphere 提供了4种分片配置方式：

• Java 代码配置

• Yaml 、properties 配置

• Spring 命名空间配置

• Spring Boot配置

为让代码看上去更简洁和直观，后边统一使用 properties 配置的方式，引入 shardingsphere 对应的 sharding-jdbc-spring-boot-starter 和 sharding-core-common 包，版本统一用的 4.0.0-RC1。

2、分片配置

1
2
3
4
5
6
7
8
9
10
11

<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
 <version>4.0.0-RC1</version>
</dependency>

<dependency>
 <groupId>org.apache.shardingsphere</groupId>
 <artifactId>sharding-core-common</artifactId>
 <version>4.0.0-RC1</version>
</dependency>

准备工作做完（ mybatis 搭建就不赘述了），接下来我们逐一解读分片配置信息。

我们首先定义两个数据源 ds-0、ds-1，并分别加上数据源的基础信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 定义两个全局数据源
spring.shardingsphere.datasource.names=ds-0,ds-1

# 配置数据源 ds-0
spring.shardingsphere.datasource.ds-0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-0.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-0.url=jdbc:mysql://127.0.0.1:3306/ds-0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=root

# 配置数据源 ds-1
spring.shardingsphere.datasource.ds-1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-1.driverClassName=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-1.url=jdbc:mysql://127.0.0.1:3306/ds-1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=root

配置完数据源接下来为表添加分库和分表策略，使用 sharding-jdbc 做分库分表需要我们为每一个表单独设置分片规则。

1
2
3

# 配置分片表 t_order
# 指定真实数据节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..2}

actual-data-nodes 属性指定分片的真实数据节点，$是一个占位符，{0..1}表示实际拆分的数据库表数量。

ds-$->{0..1}.t_order_$->{0..2} 表达式相当于 6个数据节点

• ds-0.t_order_0
• ds-0.t_order_1
• ds-0.t_order_2
• ds-1.t_order_0
• ds-1.t_order_1
• ds-1.t_order_2

1
2
3
4
5

### 分库策略
# 分库分片健
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=order_id
# 分库分片算法
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds-$->{order_id % 2}

为表设置分库策略，上边讲了 sharding-jdbc 它提供了四种分片策略，为快速搭建我们先以最简单的行内表达式分片策略来实现，在下一篇会介绍四种分片策略的详细用法和使用场景。

database-strategy.inline.sharding-column 属性中 database-strategy 为分库策略，inline 为具体的分片策略，sharding-column 代表分片健。

database-strategy.inline.algorithm-expression 是当前策略下具体的分片算法，ds-$->{order_id % 2} 表达式意思是 对 order_id字段进行取模分库，2 代表分片库的个数，不同的策略对应不同的算法，这里也可以是我们自定义的分片算法类。

1
2
3
4
5
6
7
8
9

# 分表策略
# 分表分片健
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
# 分表算法
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 3}
# 自增主键字段
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 自增主键ID 生成方案
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

分表策略 和 分库策略 的配置比较相似，不同的是分表可以通过 key-generator.column 和 key-generator.type 设置自增主键以及指定自增主键的生成方案，目前内置了SNOWFLAKE 和 UUID 两种方式，还能自定义的主键生成算法类，后续会详细的讲解。

1
2

# 绑定表关系
spring.shardingsphere.sharding.binding-tables= t_order,t_order_item

必须按相同分片健进行分片的表才能互为成绑定表，在联合查询时就能避免出现笛卡尔积查询。

1
2

# 配置广播表
spring.shardingsphere.sharding.broadcast-tables=t_config

广播表，开启 SQL解析日志，能清晰的看到 SQL分片解析的过程

1
2

# 是否开启 SQL解析日志
spring.shardingsphere.props.sql.show=true

3、验证分片

分片配置完以后我们无需在修改业务代码了，直接执行业务逻辑的增、删、改、查即可，接下来验证一下分片的效果。

我们同时向 t_order、t_order_item 表插入 5条订单记录，并不给定主键 order_id ，item_id 字段值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

public String insertOrder() {

   for (int i = 0; i < 4; i++) {
       TOrder order = new TOrder();
       order.setOrderNo("A000" + i);
       order.setCreateName("订单 " + i);
       order.setPrice(new BigDecimal("" + i));
       orderRepository.insert(order);

       TOrderItem orderItem = new TOrderItem();
       orderItem.setOrderId(order.getOrderId());
       orderItem.setOrderNo("A000" + i);
       orderItem.setItemName("服务项目" + i);
       orderItem.setPrice(new BigDecimal("" + i));
       orderItemRepository.insert(orderItem);
   }
   return "success";
}

看到订单记录被成功分散到了不同的库表中， order_id 字段也自动生成了主键ID，基础的分片功能就完成了。

基础分片

那向广播表 t_config 中插入一条数据会是什么效果呢？

1
2
3
4
5
6
7
8
9

public String config() {

    TConfig tConfig = new TConfig();
    tConfig.setRemark("我是广播表");
    tConfig.setCreateTime(new Date());
    tConfig.setLastModifyTime(new Date());
    configRepository.insert(tConfig);
    return "success";
}

发现所有库中 t_config 表都执行了这条SQL，广播表和 MQ广播订阅的模式很相似，所有订阅的客户端都会收到同一条消息。

广播表

简单SQL操作验证没问通，接下来在试试复杂一点的联合查询，前边我们已经把 t_order 、t_order_item 表设为绑定表，直接联表查询执行一下。

关联查询

通过控制台日志发现，逻辑表SQL 经过解析以后，只对 t_order_0 和 t_order_item_0 表进行了关联产生一条SQL。

绑定表SQL

那如果不互为绑定表又会是什么情况呢？去掉 spring.shardingsphere.sharding.binding-tables试一下。

发现控制台解析出了 3条真实表SQL，而去掉 order_id 作为查询条件再次执行后，结果解析出了 9条SQL，进行了笛卡尔积查询。所以相比之下绑定表的优点就不言而喻了。

笛卡尔积查询

五、总结

以上对分库分表中间件 sharding-jdbc 的基础概念做了简单梳理，快速的搭建了一个分库分表案例，但这只是实践分库分表的第一步，下一篇我们会详细的介绍四种分片策略的具体用法和使用场景（必知必会），后边将陆续讲解自定义分布式主键、分布式数据库事务、分布式服务治理，数据脱敏等。

在线脑图

先来看表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE TABLE a (
  `id`bigint AUTO_INCREMENT ,
  `a` int,
  `b` int,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`)
);

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`)
)

看一下join语句，因为b上没有索引，所以mysql用的BLNJ：

1
2
3
4

explain select * from a 
join b using(b)
where a = 1
order by a, b;

如果b表有索引的话：

1
2
3
4
5
6
7

CREATE TABLE b (
	`id`bigint AUTO_INCREMENT ,
  `b` int,
  `c` int,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`b`)
)

可以发现a表idx_a_b有序性没有利用上，至于原因，先看一下BNLJ执行的流程图:

执行过程为：

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，直到 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取之后数据放入 join_buffer 中，继续执行第 2 步，直到所有数据读取完毕。

其中隐含的问题在于第二步：即使t1表的数据是有序读取到join_buffer中的，由于是先扫描t2表再关联join_buffer数据，导致join_buffer中的有序性失效。

如果表b有索引idx_b,那么使用BKA算法第二步的关联顺序与BNLJ相反，是先扫描join_buffer后通过索引关联t2,则可以利用join_buffer中的有序数据。

为了便于说明问题，我们先使用一个小一点儿的表，建表和初始化语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values
(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);

这个表除了主键 id 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。

下面的语句序列，是怎么加锁的，加的锁又是什么时候释放的呢？

1

select * from t where d = 5 for update;

比较好理解的是，这个语句会命中 d = 5 的这一行，对应的主键 id = 5，因此在 select 语句执行完成后，会在id = 5 这一行主键上加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

我们知道，InnoDB 的默认事务隔离级别是可重复读，所以本文接下来没有特殊说明的部分，都是设定在可重复读隔离级别下。

幻读是什么？

现在，我们就来分析一下，假设只在 id = 5 这一行加锁，而其他行的不加锁的话，会怎么样。

下面先来看一下这个场景（这个结果是建立在前面假设之上，实际上是错误的）：

假设只在 id = 5 这一行加行锁，可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * fom t where d=5 for update。我们来看一下这三条 SQL 语句，分别会返回什么结果。

1. Q1 只返回 id = 5 这一行；
2. 在 T2 时刻，session B 把 id = 0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id = 0 和 id = 5 这两行；
3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id = 0、id = 1 和 id = 5 的这三行。

其中，Q3 读到 id = 1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

这里，我需要对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。
2. 上面 session B 的修改结果，被 session A 之后的 select 语句用当前读看到，不能称为幻读。幻读仅专指新插入的行。

如果只从我们学到的事务可见性规则来分析的话，上面这三条 SQL 语句的返回结果都没有问题。

因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值。并且，session B 和 sessionC 的两条语句，执行后就会提交，所以 Q2 和 Q3 就是应该看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。

幻读有什么问题？

首先是语义上的。session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。所以我们假设只锁了id = 5这一行的语义与select * from t where d = 5 for update 不同。

其次，是数据一致性的问题。 这个数据不一致到底是怎么引入的？肯定是前面的假设有问题。

我们把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。

由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。

这样对于 id = 0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

1
2
3
4
5
6
7

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
 
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/
 
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id = 0 这一行的最终结果也是 (0,5,5)。所以，id = 0 这一行的问题解决了。

但同时你也可以看到，id = 1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都上了锁，还是阻止不了 id = 1 这一行的插入和更新呢？

原因很简单。在 T3 时刻，我们给所有行加锁的时候，id = 1 这一行还不存在，不存在也就加不上锁。

也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决幻读？

现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。

现在你知道了，数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

比如行锁，分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

也就是说，跟行锁有冲突关系的是“另外一个行锁”。

但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

这句话不太好理解，我给你举个例子：

这里 session B 并不会被堵住。因为表 t 里并没有 c = 7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间。

你可能会问说，这个 supremum 从哪儿来的呢？

这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

1
2
3
4
5
6
7
8
9

begin;
select * from t where id=N for update;
 
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
 
commit;

这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？

这里，我用两个 session 来模拟并发，并假设 N=9。

图 8 间隙锁导致的死锁

你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

1. session A 执行 select … for update 语句，由于 id = 9 这一行并不存在，因此会加上间隙锁 (5,10);
2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。

你现在知道了，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

你可能会说，为了解决幻读的问题，我们引入了这么一大串内容，有没有更简单一点的处理方法呢。

我在文章一开始就说过，如果没有特别说明，今天和你分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

第四章 Schema设计

选择优化的数据类型

更小的通常更好，但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型范围是一个十分耗时的操作。

简单就好

简单的数据类型的操作通常需要更少的cpu时间。

尽量避免NULL

可为NULL的列使得索引、索引比统计和值比较都更为复杂。

当然也有例外，InnoDB使用单独的位存储NULL值， 所以对于稀疏数据有很好的空间效率。

选择标识符

一旦选定一种类型，要确保在所有的关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。尽量只用整型定义标识符。

注意可变长字符串

其在临时表和排序时可能导致悲观的按最大长度分配内存

范式与反范式

范式是好的，但是反范式有时也是必须的，并且能带来好处。

第五章 创建高性能索引

B-Tree 索引的查询类型

• 全值匹配： 指的是和索引的所有列进行匹配
• 匹配最左前缀： 查找索引前几列进行匹配
• 匹配列前缀: 只匹配某一列的值的开头部分
• 匹配范围值： 查找索引某一范围的值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询：覆盖索引

B-Tree 索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引：例如查询 索引为key(last_name, fisrt_name, dob)

  1	where last_name = 'a' and first_name like 'J%' and dob = '1877-12-23'

索引的优点

1. 大大减少服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机I/O变为顺序I/O

高性能索引-独立的列

如果查询中列不是独立的，则mysql不会使用索引

1
2

select actor_id from sakila.actor where actor_id + 1 = 5;

高性能索引-前缀索引和索引选择性

有时候需要索引很长的字符列，通常可以索引开始部分的字符，同时也会降低索引的选择性。

索引的选择性指的是，不重复的索引值和数据表的记录总数的比值。索引的选择性越高表示查询效率越高，因为选择性高的索引可以过滤掉更多的行。

前缀索引是一种能使索引更小，更快的有效方法，但是也有其缺点：前缀索引无法做order by 和 group by，也无法使用前缀索引做覆盖索引。

高性能索引-多列索引

最容易遇到的困惑是多列索引的顺序，正确的顺序依赖于使用索引的查询，同时需要考虑如何更好的满足排序和分组需要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引的最前列。只有不需要考虑排序和分组时，将选择性跟高的列放在最前面通常是最好的，但是考虑问题需要更全面，避免随机I/O和排序更加重要。

高性能索引-覆盖索引

如果一个索引包含所需要查询的字段的值，我们就可以称之为“覆盖索引”

覆盖索引的好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型范围查询回避随机从磁盘读取每一行数据的I/O要小的多
• 由于Innodb的聚簇索引，覆盖索引对Innodb表特别有用，可以避免对主键索引的二次查询。

覆盖索引的陷阱：

1

select * from products where actor = 'SEAN CARREY' and title like '%APOLLO%';

• 没有索引能够覆盖这个查询，因为查询从表中选择了所有的列
• mysql不能再索引中执行like操作，只能做最左前缀匹配

高性能索引-使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：通过排序操作；或者使用索引顺序扫描。mysql可以使用同一个索引既满足排序，有用于查找行。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能用索引做排序。ORDER BY子句和查询的限制是一样的：需要满足索引的最左前缀的要求。

有一种情况下ORDER BY子句可以不满足索引的最左前缀要求：

1
2

select rental_id, staff_id from sakila.rental where rental_date = '2005-05-25'
order by inventory_id, customer_id;

索引为key(rental_date, inventory_id, customer_id)，前导列为常量的时候，如果where子句或者join子句中对这些列指定了常量，就可以弥补ORDER BY的不足。

1
2

where rental_date > '2005-12-25' order by inventory_id, customer_id;
where rental_date = '2005-12-25' and inventory_id in (1, 2) order by cusomter_id;

对于索引上是范围查询，mysql无法使用之后的索引列

高性能索引-使用索引扫描减少锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。但这只有当innoDB在存储引擎层能够过滤掉所有不需要的行是才有效。如果索引无法过滤掉无效的行，那么innoDB检索到数据并返回给服务器层后，innoDB已经锁定这些行了（mysql 5.6后没有这个问题）。

高性能索引-避免多个范围条件

下面的查询：

1

where last_online > date_sub(now(), interval 7 day) and age bwtween 18 and 25

这个查询有一个问题：它有两个范围条件,last_online和age列，mysql可以使用last_online的索引或者是age列的索引，但是无法同时使用它们。

高性能索引-延迟关联优化分页

如果一个查询匹配结果有上百万行的话会怎样？

1

select * from profiles where sex = 'm' order by rating limit 10;

即使有索引，如果用户界面需要翻页，并且翻页到比较靠后的地方也会非常慢，如：

1

select * from profiles where sex = 'm' order by rating limit 1000000, 10;

无论如何创建索引，这种查询都是个严重的问题，mysql需要花费大量时间来扫描需要丢弃的数据。其中一个解决的办法是限制能够翻页的数量。

优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引返回需要的主建，再根据这些主建回主表获得需要的行，这样可以减少mysql扫描需要丢弃的行数。

1
2
3
4
5
6

select * from profiles innner join 
(
    select id fomr profiles p where p.sex = 'm' order by rating limit 1000000, 10

) as t using (id);

第六章 慢查询优化

优化数据访问

查询性能低下最基本的原因是访问数据太多。某些查询可能不可避免的需要筛选大量数据，单这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面几个步骤来分析总是很有效：

1. 确认应用程序时候检索大量超过需要的数据。
2. 确认mysql服务层是否在分许大量超过需要的数据行。

第一种情况可以使用limit和选择需要的列来解决。在确定查询只返回需要的数据之后，接下来应该看看查询为了返回结果是否扫描了过多的数据，对于mysql有三个衡量查询开销的指标：

• 响应时间
• 扫描的行数
• 返回的行数

没有那个指标能完美地衡量查询的开销，但它们大致反映了mysql内部查询时需要访问多少数据，并可以大概推算出查询运行的时间。

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。mysql有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。

在explain语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描行数也是从大到小。如果查询没有办法找到合适是访问类型，那么解决的最好办法通常是增加一个合适的索引。

一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：

• 从索引中使用where条件吗来过滤不匹配的记录，这是在存储引擎层完成的。
• 使用索引覆盖扫描来返回记录（extra出现using index），直接从索引中过滤不需要的数据并返回命中结果。这是在mysql服务层完成的，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（extra出现using where）。这是在mysql服务器层完成，mysql需要从数据表中读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，那么可以使用下面的技巧去优化它：

• 使用覆盖索引，把需要用的列都放到索引中
• 改变库表结构。例如使用单独的汇总表
• 重写复杂的查询， 让mysql优化器能够以更加高效的方式执行这个查询

重构查询方式-切分查询

有时候对于一个大查询我们需要分而治之，将大查询分成小查询。删除旧数据是一个很好的例子，如果用一个大的语句一次性完成，则可能一次需要锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞恨到重要的查询。

重构查询方式-分解关联查询

例如下面这个查询：

1
2
3
4

select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';

可以分解成下面这个查询来代替：

1
2
3
4
5

select * from tag where tag = 'mysql';

select * from tag_post where tag_id = 1234;

select * from post where post.id in (123,456,567);

用分解关联查询的方式重构查询有如下的优势：

• 让缓存效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和高扩展。
• 查询本身效率可能提升，使用in代替关联查询，可能比随机关联更高效。
• 减少冗余记录的查询
• 相当于在应用层实现了哈希关联

重构查询方式-优化关联查询

• 确保on或者using子句上的列上有索引。
• 确保任何group by和 order by 的表达式中只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程