Spring使用sharding-jdbc实现读写分离或分库分表

逻辑表与实际表之间的对应关系,均匀分布

使用sharding-jdbc来实现无论是读写分离,还是分库分表,都是很简单易用的。

如下图,其中order被拆分为两个表:

 

001

 

//使用默认的分表配置

TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

orderTableRuleConfig.setLogicTable(“t_order”);

orderTableRuleConfig.setActualDataNodes(“db0.t_order_0, db0.t_order_1, db1.t_order_0, db1.t_order_1″);

LogicTable and ActualTable

数据库分库分表的目的是将数据从原始表传播到不同数据库中的不同表,并在不更改原始sql的情况下查询数据。

这种映射关系将通过使用LogicTable和ActualTable来说明。假设使用PreparedStatement访问数据库,SQL如下:

select * from t_order where user_id = ? and order_id = ?;

当条件user_id = 0 并且 order_id= 0时,Sharding-JDBC会改变这个SQL为以下目标SQL:

select * from db0.t_order_0 where user_id = ? and order_id = ?;

第一个SQL中的t_order是LogicTable和db0。第二个SQL中的t_order_0是ActualTable。

规则配置

我们可以通过配置规则来实现上述功能,本部分将介绍详细的规则配置:

ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(orderTableRule);

shardingRuleConfig.getTableRuleConfigs().add(orderItemTableRule);

shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“user_id”, “xxx.ModuloDatabaseShardingAlgorithm”));

shardingRuleConfig.setDefaultTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“order_id”, “xxx.ModuloTableShardingAlgorithm”));

数据源配置

我们需要创建至少一个数据源映射对象,用于描述数据源名称和数据源的映射。如果使用了分库,那么是需要两个创建两个BasicDataSource对象:

private BasicDataSource dataSource1() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/db0?serverTimezone=GMT&characterEncoding=utf8");
    dataSource.setUsername("root");
    dataSource.setPassword("root");

    dataSource.setInitialSize(0);
    dataSource.setMaxIdle(5);
    dataSource.setMinIdle(100);
    dataSource.setMaxOpenPreparedStatements(100);
    dataSource.setTestWhileIdle(true);
    dataSource.setValidationQuery("SELECT 1");
    dataSource.setTimeBetweenEvictionRunsMillis(3600000);
    dataSource.setMinEvictableIdleTimeMillis(18000000);
    dataSource.setTestOnBorrow(true);
    dataSource.setMaxWaitMillis(300000);

    return dataSource;
}

private BasicDataSource dataSource2() {
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=GMT&characterEncoding=utf8");
    dataSource.setUsername("root");
    dataSource.setPassword("root");

    dataSource.setInitialSize(0);
    dataSource.setMaxIdle(5);
    dataSource.setMinIdle(100);
    dataSource.setMaxOpenPreparedStatements(100);
    dataSource.setTestWhileIdle(true);
    dataSource.setValidationQuery("SELECT 1");
    dataSource.setTimeBetweenEvictionRunsMillis(3600000);
    dataSource.setMinEvictableIdleTimeMillis(18000000);
    dataSource.setTestOnBorrow(true);
    dataSource.setMaxWaitMillis(300000);

    return dataSource;
}

 

以下是数据源集合的代码:

Map<String, DataSource> dataSourceMap = new HashMap<>();

dataSourceMap.put(“ds_0″, datasource1());

dataSourceMap.put(“ds_1″, datasource());

*注:如果只是为了分表,那么无需创建两个数据源,但是如果你想实现读写分离或者是分库,那么则需要至少个数据源。

策略配置

一共有两个策越,分表是针对数据库跟数据库表

在sharding-jdbc中有两个用于分库分表的策略:

  • DatabaseShardingStrategy
  • TableShardingStrategy

DatabaseShardingStrategy用于分布式数据库的数据源的策略。

TableShardingStrategy用于分布数据库表的策略。

此外,这两种策略的API是相同的,因此我们只要对其中一种API进行详细的介绍就可以了。

特定表规则的全局默认策略

策略与数据表(t_order)规则密切相关,因为策略适用于特定的表规则。

TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();

orderTableRuleConfig.setLogicTable(“t_order”);

orderTableRuleConfig.setActualDataNodes(“ds_0.t_order_0, ds_0.t_order_1, ds_1.t_order_0, ds_1.t_order_1″);

orderTableRuleConfig.setDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“user_id”, “xxx.ModuloDatabaseShardingAlgorithm”));

orderTableRuleConfig.setTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“order_id”, “xxx.ModuloTableShardingAlgorithm”));

上述的代码,有两种策略,第一种就是通过user_id进行数据库的分配;第二种就是根据order_id再对数据表进行分配。最终实现的逻辑代码其实是:xxx.ModuloDatabaseShardingAlgorithm、xxx.ModuloTableShardingAlgorithm。

如果所有或大部分数据表都使用相同的分片策略,则可以使用默认策略来简化配置。

 

TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();//第个表的策略

orderTableRuleConfig.setLogicTable(“t_order”);

orderTableRuleConfig.setActualDataNodes(“ds_0.t_order_0, ds_0.t_order_1, ds_1.t_order_0, ds_1.t_order_1″);

 

 

TableRuleConfiguration orderItemTableRuleConfig = new TableRuleConfiguration();//第二个表的策略

orderItemTableRuleConfig.setLogicTable(“t_order_item”);

orderItemTableRuleConfig.setActualDataNodes(“ds_0.t_order_item_0,ds_0.t_order_item_1,ds_1.t_order_item_0,ds_1.t_order_item_1″);

 

 

ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

shardingRuleConfig.getTableRuleConfigs().add(orderItemTableRuleConfig);

shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“user_id”, “xxx.ModuloDatabaseShardingAlgorithm”));

shardingRuleConfig.setDefaultTableShardingStrategyConfig(new ComplexShardingStrategyConfiguration(“order_id”, “xxx.ModuloTableShardingAlgorithm”));

上述代码其实跟前一段是一致的,但是因为配置了两个数据表的分配策略,所以创建了两个TableRuleConfiguration,然后通过ShardingRuleConfiguration的方法getTableRuleConfigs().add()把规则添加进去。

分表或分库的列字段

分片策略中设置为第一个参数为分库分表的列字段(user_id、order_id)是SQL中WHERE中的条件列。如果你的SQL语句的WHERE中可能会没有这两个列字段,那么你最好在xxx.ModuloDatabaseShardingAlgorithm、xxx.ModuloTableShardingAlgorithm逻辑代码中特殊处理一下,当然你也可以配置多个分片列。

分表分库的算法(以下来自官方的翻译文件)

Sharding-JDBC provides 5 kinds of sharding strategies. Because of the closely connection between specific business and specific sharding algorithms, Sharding-JDBC not carry out sharding algorithm. Instead, after making a higher level of abstraction, we provide API to allow developers to implement sharding algorithms as they need.

Sharding-JDBC提供了5种切分策略。由于特定业务和特定的切分算法之间的紧密联系,Sharding-JDBC没有执行切分算法。相反,在进行了更高级别的抽象之后,我们提供了允许开发人员根据需要实现切分算法的API。

  • StandardShardingStrategy (标准共享策略)

Support =, IN, BETWEEN AND in SQLs for sharding operation. StandardShardingStrategy only supports single sharding column, and provides two sharding algorithms of PreciseShardingAlgorithm and RangeShardingAlgorithm. The PreciseShardingAlgorithm is required to handle the sharding operation of = and IN. The RangeShardingAlgorithm is optional to handle BETWEEN AND. If the RangeShardingAlgorithm is not configured, the BETWEEN-AND SQLs will be executed in all tables.

在SQL语句中支持 =, IN, BETWEEN AND,以便进行切分操作。标准分片策略只支持单分片列,提供了两种分片算法:精确分片算法(PreciseShardingAlgorithm)和测距分片算法(RangeShardingAlgorithm)。精确分片算法(PreciseShardingAlgorithm)需要使用精确的硬件算法来处理“=”和in的切分操作。RangeShardingAlgorithm是在BETWEEN AND处理的可选方法。如果未配置RangeShardingAlgorithm,那么在查询SQL语句中,将在所有表中执行BETWEEN-AND SQLs。

  • ComplexShardingStrategy(综合硬件策略)

Support =, IN, BETWEEN AND in SQLs for sharding operation. ComplexShardingStrategy supports multiple sharding columns. Due to the complex relationship among the multiple sharding columns, Sharding-JDBC only provide algorithm API to allow developers combine different sharding columns and implement the specific algorithm.

在SQL中支持 =, IN, BETWEEN AND in,以便进行切分操作。ComplexShardingStrategy 支持多个切分列。由于多个切分列之间的复杂关系,Sharding-JDBC只提供算法API,允许开发人员组合不同的切分列,实现特定的算法。

  • InlineShardingStrategy (内部共享策略)

This strategy provides sharding support for =, IN in SQLs by means of Groovy’s Inline expression. InlineShardingStrategy only supports single sharding column. Some simple sharding algorithm can be configured, e.g. tuser $ {user_id% 8} shows us the t_user table is divided into 8 tables via mod(user_id), and the child tables is t_user_0 to t_user_7.

此策略通过内联表达式提供支持SQL中的 =, IN ,提供分片支持。InlineShardingStrategy 只支持单个分片的列字段。可以配置一些简单的切分算法,例如, t_user $ {user_id % 8} 向我们显示,t_user表通过取模(user_id)分为8个表,子表分别是t_user_0到t_user_7。

  • HintShardingStrategy

Support spliting table by means of Hint method, not SQL Parsing.

通过提示方法支持分表,而不对SQL进行解析。

  • NoneShardingStrategy

注:这种策略不要拆分数据库或表。

联表操作

它由一组表组成,其中逻辑表和实际表之间的映射关系是相同的。例如order table与order ID进行了分割,order_item table也与order ID进行了分割,因此可以将order table与order_item table配置为彼此的BindingTable。

在这种情况下,SQL语句应该如下:

SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=? AND o.order_id=?

t_order位于FROM的左侧,Sharding-JDBC将它视为绑定表的驱动表。所有计算将只使用已配置的驱动表策略。因此,t_order_item的路由计算也将使用t_order的条件。这个实现的核心在于它们相同的分片列。

最后提交我的实验代码:

@Configuration
@EnableTransactionManagement
@MapperScan(value = "com.lanxinbase.repository.mapper")
public class MybatisConfig implements TransactionManagementConfigurer {

    @Resource
    private ShardingDataSource shardingDataSource;

    public MybatisConfig(){
    }


    @Bean(value = "sessionFactoryBean")
    public SqlSessionFactoryBean sessionFactoryBean() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(shardingDataSource);
        factoryBean.setConfiguration(this.getConfiguration());

        String locationPattern = ResourcePatternResolver.CLASSPATH_URL_PREFIX + "com/lanxinbase/repository/resource/**.xml";
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factoryBean.setMapperLocations(resolver.getResources(locationPattern));
        return factoryBean;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


    @Override
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        DataSourceTransactionManager manager = new DataSourceTransactionManager();
        manager.setDataSource(shardingDataSource);
        manager.setDefaultTimeout(40);
        manager.setRollbackOnCommitFailure(true);
        return manager;
    }

    public org.apache.ibatis.session.Configuration getConfiguration() {
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        configuration.setLocalCacheScope(LocalCacheScope.SESSION);
        configuration.setCacheEnabled(true);
        return configuration;
    }

    @Bean
    public ShardingDataSource shardingDataSource() throws SQLException {
        ShardingRuleConfiguration conf = new ShardingRuleConfiguration();

        /**
         * 添加分表策略
         */
        conf.getTableRuleConfigs().add(tableRuleConfiguration());
        conf.getBindingTableGroups().add(Constant.SHARDING_TABLE_GPS);

        //http://shardingsphere.apache.org/document/legacy/2.x/en/02-guide/master-slave/
       //conf.getMasterSlaveRuleConfigs().add();配置读写分离

        //这个是分库的
       //conf.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("age", ShardingPreciseShardingAlgorithm.class.getName()));

        /**
         * 分表处理对象类
         */
        conf.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("imei", ShardingPreciseShardingAlgorithm.class.getName()));
        conf.build(this.getDataSourceMap());

        ShardingDataSource dataSource = new ShardingDataSource(conf.build(this.getDataSourceMap()));
        return dataSource;
    }

    /**
     * 数据表配置,我这里只做了分表
     * 如果有多个表,就重复创建tableRuleConfiguration方法,
     * 然后通过getTableRuleConfigs.add(tableRuleConfiguration());
     *
     * @return
     */
    @Bean
    public TableRuleConfiguration tableRuleConfiguration() {
        TableRuleConfiguration ruleConfiguration = new TableRuleConfiguration();

        /**
         * 要逻辑表
         */
        ruleConfiguration.setLogicTable(Constant.SHARDING_TABLE_GPS);

        /**
         * 区分规则
         */
        ruleConfiguration.setActualDataNodes("dataSource2.lx_dev_gps_${0..9}");

        /**
         * 用于区分的字段
         */
        ruleConfiguration.setKeyGeneratorColumnName("imei");

        return ruleConfiguration;
    }

    /**
     * 可以配置多个dataSource,如dataSource1、dataSource2
     * 然后就可以把读写分离分开了
     * @return
     */
    private Map<String, DataSource> getDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put("dataSource2", this.dataSource2());
        return result;
    }

    /**
     * 创建一个BasicDataSource给sharding-jdbc
     * @return
     */
    private BasicDataSource dataSource2() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT&characterEncoding=utf8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");

        dataSource.setInitialSize(0);
        dataSource.setMaxIdle(5);
        dataSource.setMinIdle(100);
        dataSource.setMaxOpenPreparedStatements(100);
        dataSource.setTestWhileIdle(true);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTimeBetweenEvictionRunsMillis(3600000);
        dataSource.setMinEvictableIdleTimeMillis(18000000);
        dataSource.setTestOnBorrow(true);
        dataSource.setMaxWaitMillis(300000);

        return dataSource;
    }
}
//这里才是最终处理分表的逻辑代码
public class ShardingPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {

    private static final Logger logger = Logger.getLogger(ShardingPreciseShardingAlgorithm.class.getName());

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
        int flag = shardingValue.getValue() % 10;

        //只对特别的表进行拦截处理
        if (shardingValue.getLogicTableName().contains(Constant.SHARDING_TABLE_GPS)){
            for (String tableName : availableTargetNames) {
                if (tableName.endsWith(flag + "")) {
                    logger.info(">>>>tableName:" + tableName);
                    if (flag == 0) {
                        return tableName.substring(0, tableName.lastIndexOf("_"));
                    }
                    return tableName;
                }
            }
            throw new IllegalArgumentException("No match to the table.");
        }

        return shardingValue.getLogicTableName();
    }
}

 

*注:本人只做了分表的策略,没有做分库,是用于存放硬件设备GPS数据的表,一共10个。

更多文献:http://shardingsphere.apache.org/document/legacy/2.x/en/02-guide/sharding/

 

MySQL索引和查询优化

索引相关

索引基数

基数是数据列所包含的不同值的数量,例如,某个数据列包含值 1、3、7、4、7、3,那么它的基数就是 4。

索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。

如果某数据列含有很多不同的年龄,索引会很快地分辨数据行;如果某个数据列用于记录性别(只有“M”和“F”两种值),那么索引的用处就不大;如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。

在这些情况下,最好根本不要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是“30%”。

索引失效原因

索引失效的原因有如下几点:

  • 对索引列运算,运算包括(+、-、*、/、!、<>、%、like’%_'(% 放在前面)。
  • 类型错误,如字段类型为 varchar,where 条件用 number。
  • 对索引应用内部函数,这种情况下应该要建立基于函数的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1,此时应该建 ROUND (t.logicdb_id) 为索引。

MySQL 8.0 开始支持函数索引,5.7 可以通过虚拟列的方式来支持,之前只能新建一个 ROUND (t.logicdb_id) 列然后去维护。

  • 如果条件有 or,即使其中有条件带索引也不会使用(这也是为什么建议少使用 or 的原因),如果想使用 or,又想索引有效,只能将 or 条件中的每个列加上索引。
  • 如果列类型是字符串,那一定要在条件中数据使用引号,否则不使用索引。
  • B-tree 索引 is null 不会走,is not null 会走,位图索引 is null,is not null 都会走。
  • 组合索引遵循最左原则。

索引的建立

索引的建立需要注意以下几点:

  • 最重要的肯定是根据业务经常查询的语句。
  • 尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT(*),表示字段不重复的比率,比率越大我们扫描的记录数就越少。
  • 如果业务中唯一特性最好建立唯一键,一方面可以保证数据的正确性,另一方面索引的效率能大大提高。

EXPLIAN 中有用的信息

基本用法

EXPLIAN 基本用法如下:

  • desc 或者 explain 加上你的 SQL。
  • extended explain 加上你的 SQL,然后通过 show warnings 可以查看实际执行的语句,这一点也是非常有用的,很多时候不同的写法经 SQL 分析后,实际执行的代码是一样的。

提高性能的特性

EXPLIAN 提高性能的特性如下:

  • 索引覆盖(covering index):需要查询的数据在索引上都可以查到不需要回表 EXTRA 列显示 using index。
  • ICP特性(Index Condition Pushdown):本来 index 仅仅是 data access 的一种访问模式,存数引擎通过索引回表获取的数据会传递到 MySQL Server 层进行 where 条件过滤。

5.6 版本开始当 ICP 打开时,如果部分 where 条件能使用索引的字段,MySQL Server 会把这部分下推到引擎层,可以利用 index 过滤的 where 条件在存储引擎层进行数据过滤。

EXTRA 显示 using index condition。需要了解 MySQL 的架构图分为 Server 和存储引擎层。

  • 索引合并(index merge):对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。

一般用 or 会用到,如果是 AND 条件,考虑建立复合索引。EXPLAIN 显示的索引类型会显示 index_merge,EXTRA 会显示具体的合并算法和用到的索引。

Extra 字段

Extra 字段使用:

  • using filesort:说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。

MySQL 中无法利用索引完成的排序操作称为“文件排序”,其实不一定是文件排序,内部使用的是快排。

  • using temporary:使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
  • using index:表示相应的 SELECT 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。
  • impossible where:where 子句的值总是 false,不能用来获取任何元组。
  • select tables optimized away:在没有 group by 子句的情况下基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
  • distinct:优化 distinct 操作,在找到第一匹配的元组后即停止找同样值的操作。

using filesort、using temporary 这两项出现时需要注意下,这两项是十分耗费性能的。

在使用 group by 的时候,虽然没有使用 order by,如果没有索引,是可能同时出现 using filesort,using temporary 的。

因为 group by 就是先排序在分组,如果没有排序的需要,可以加上一个 order by NULL 来避免排序,这样 using filesort 就会去除,能提升一点性能。

type 字段

type 字段使用:

  • system:表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现。
  • const:如果通过索引依次就找到了,const 用于比较主键索引或者 unique 索引。因为只能匹配一行数据,所以很快。如果将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量。
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
  • ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
  • range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的 where 语句中出现 between、<、>、in 等的查询。
  • 这种范围扫描索引比全表扫描要好,因为只需要开始于缩印的某一点,而结束于另一点,不用扫描全部索引。
  • index:Full Index Scan ,index 与 ALL 的区别为 index 类型只遍历索引树,这通常比 ALL 快,因为索引文件通常比数据文件小。
  • 也就是说虽然 ALL 和 index 都是读全表,但 index 是从索引中读取的,而 ALL 是从硬盘读取的。
  • all:Full Table Scan,遍历全表获得匹配的行。

字段类型和编码

MySQL 返回字符串长度

CHARACTER_LENGTH(同CHAR_LENGTH)方法返回的是字符数,LENGTH 函数返回的是字节数,一个汉字三个字节。

varchar 等字段建立索引长度计算语句

select count(distinct left(test,5))/count(*) from table;越趋近 1 越好。

MySQL 的 utf8

MySQL 的 utf8 最大是 3 个字节不支持 emoji 表情符号,必须只用 utf8mb4。需要在 MySQL 配置文件中配置客户端字符集为 utf8mb4。

JDBC 的连接串不支持配置 characterEncoding=utf8mb4,最好的办法是在连接池中指定初始化 SQL。

例如:hikari 连接池,其他连接池类似 spring . datasource . hikari . connection – init – sql =set names utf8mb4。否则需要每次执行 SQL 前都先执行 set names utf8mb4。

MySQL 排序规则

一般使用 _bin 和 _genera_ci:

  • utf8_genera_ci 不区分大小写,ci 为 case insensitive 的缩写,即大小写不敏感。
  • utf8_general_cs 区分大小写,cs 为 case sensitive 的缩写,即大小写敏感,但是目前 MySQL 版本中已经不支持类似于 ***_genera_cs 的排序规则,直接使用 utf8_bin 替代。
  • utf8_bin 将字符串中的每一个字符用二进制数据存储,区分大小写。

那么,同样是区分大小写,utf8_general_cs 和 utf8_bin 有什么区别?

  • cs 为 case sensitive 的缩写,即大小写敏感;bin 的意思是二进制,也就是二进制编码比较。
  • utf8_general_cs 排序规则下,即便是区分了大小写,但是某些西欧的字符和拉丁字符是不区分的,比如 ä=a,但是有时并不需要 ä=a,所以才有 utf8_bin。
  • utf8_bin 的特点在于使用字符的二进制的编码进行运算,任何不同的二进制编码都是不同的,因此在 utf8_bin 排序规则下:ä<>a。

初始化命令

SQLyog 中初始连接指定编码类型使用连接配置的初始化命令,如下图:

1553049871-8082-f30aeb6b3a7c26fdbd2e48f6ecf3

SQL 语句总结

常用但容易忘的

SQL 语句常用但容易忘的总结如下:

  • 如果有主键或者唯一键冲突则不插入:insert ignore into。
  • 如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量:INSERT INTO room_remarks(room_id,room_remarks)VALUE(1,”sdf”) ON DUPLICATE KEY UPDATE room_remarks = “234”。
  • 如果有就用新的替代,values 如果不包含自增列,自增列的值会变化:REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,”sdf”)。
  • 备份表:CREATE TABLE user_info SELECT * FROM user_info。
  • 复制表结构:CREATE TABLE user_v2 LIKE user。
  • 从查询语句中导入:INSERT INTO user_v2 SELECT * FROM user 或者 INSERT INTO user_v2(id,num) SELECT id,num FROM user。
  • 连表更新:UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id。
  • 连表删除:DELETE user FROM user,black WHERE user.id=black.id。

锁相关

锁相关(作为了解,很少用):

  • 共享锁:select id from tb_test where id = 1 lock in share mode。
  • 排它锁:select id from tb_test where id = 1 for update。

优化时用到

优化时用到:

  • 强制使用某个索引:select * from table force index(idx_user) limit 2。
  • 禁止使用某个索引:select * from table ignore index(idx_user) limit 2。
  • 禁用缓存(在测试时去除缓存的影响):select SQL_NO_CACHE from table limit 2。

查看状态

查看状态:

  • 查看字符集:SHOW VARIABLES LIKE ‘character_set%’。
  • 查看排序规则:SHOW VARIABLES LIKE ‘collation%’。

SQL 编写注意

SQL 编写请注意:

  • where 语句的解析顺序是从右到左,条件尽量放 where 不要放 having。
  • 采用延迟关联(deferred join)技术优化超多分页场景,比如 limit 10000,10,延迟关联可以避免回表。
  • distinct 语句非常损耗性能,可以通过 group by 来优化。
  • 连表尽量不要超过三个表。

踩坑

踩坑总结如下:

  • 如果有自增列,truncate 语句会把自增列的基数重置为 0,有些场景用自增列作为业务上的 ID 需要十分重视。
  • 聚合函数会自动滤空,比如 a 列的类型是 int 且全部是 NULL,则 SUM(a) 返回的是 NULL 而不是 0。
  • MySQL 判断 null 相等不能用 “a=null”,这个结果永远为 UnKnown,where 和 having 中,UnKnown 永远被视为 false,check 约束中,UnKnown 就会视为 true 来处理。所以要用“a is null”处理。

千万大表在线修改

MySQL 在表数据量很大的时候,如果修改表结构会导致锁表,业务请求被阻塞。

MySQL 在 5.6 之后引入了在线更新,但是在某些情况下还是会锁表,所以一般都采用 PT 工具( Percona Toolkit)。

如对表添加索引:

  1. pt-online-schema-change –user=’root’ –host=’localhost’ –ask-pass –alter “add index idx_user_id(room_id,create_time)” 
  2. D=fission_show_room_v2,t=room_favorite_info –execute

慢查询日志

有时候如果线上请求超时,应该去关注下慢查询日志,慢查询的分析很简单,先找到慢查询日志文件的位置,然后利用 mysqldumpslow 去分析。

查询慢查询日志信息可以直接通过执行 SQL 命令查看相关变量,常用的 SQL 如下:

1553049871-4435-7eca532cafc585df2df3f6950d99

mysqldumpslow 的工具十分简单,我主要用到的参数如下:

  • -t:限制输出的行数,我一般取前十条就够了。
  • -s:根据什么来排序默认是平均查询时间 at,我还经常用到 c 查询次数,因为查询次数很频繁但是时间不高也是有必要优化的,还有 t 查询时间,查看那个语句特别卡。
  • -v:输出详细信息。

例子:mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500。

查看 SQL 进程和杀死进程

如果你执行了一个 SQL 的操作,但是迟迟没有返回,你可以通过查询进程列表看看它的实际执行状况。

如果该 SQL 十分耗时,为了避免影响线上可以用 kill 命令杀死进程,通过查看进程列表也能直观的看下当前 SQL 的执行状态;如果当前数据库负载很高,在进程列表可能会出现,大量的进程夯住,执行时间很长。

命令如下:

  1. –查看进程列表
  2. SHOW PROCESSLIST;
  3. –杀死某个进程
  4. kill 183665

如果你使用的 SQLyog,那么也有图形化的页面,在菜单栏→工具→显示→进程列表。

在进程列表页面可以右键杀死进程,如下所示:

1553049871-4118-e1f8c1f08376a8c359dbe4873f72

1553049871-5239-a0a6.jpg-wh-600x-s-902309858

一些数据库性能的思考

在对公司慢查询日志做优化的时候,很多时候可能是忘了建索引,像这种问题很容易解决,加个索引就行了。但是有几种情况就不是简单加索引能解决了:

业务代码循环读数据库

考虑这样一个场景,获取用户粉丝列表信息,加入分页是十个,其实像这样的 SQL 是十分简单的,通过连表查询性能也很高。

但是有时候,很多开发采用了取出一串 ID,然后循环读每个 ID 的信息,这样如果 ID 很多对数据库的压力是很大的,而且性能也很低。

统计 SQL

很多时候,业务上都会有排行榜这种,发现公司有很多地方直接采用数据库做计算,在对一些大表做聚合运算的时候,经常超过五秒,这些 SQL 一般很长而且很难优化。

像这种场景,如果业务允许(比如一致性要求不高或者是隔一段时间才统计的),可以专门在从库里面做统计。另外我建议还是采用 Redis 缓存来处理这种业务。

超大分页

在慢查询日志中发现了一些超大分页的慢查询如 Limit 40000,1000,因为 MySQL 的分页是在 Server 层做的,可以采用延迟关联在减少回表。

但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的,所以很有可能是有恶意用户在刷接口,最好在开发的时候也对接口加上校验拦截这些恶意请求。

MySQL 慢日志收集平台详解

ELK 介绍

ELK 最早是 Elasticsearch(以下简称ES)、Logstash、Kibana 三款开源软件的简称,三款软件后来被同一公司收购,并加入了Xpark、Beats等组件,改名为Elastic Stack,成为现在最流行的开源日志解决方案,虽然有了新名字但大家依然喜欢叫她ELK,现在所说的ELK就指的是基于这些开源软件构建的日志系统。

我们收集mysql慢日志的方案如下:

1535077408-4711-7FjictpBGeByvulpbIXxibcnOpMA

  • mysql 服务器安装 Filebeat 作为 agent 收集 slowLog
  • Filebeat 读取 mysql 慢日志文件做简单过滤传给 Kafka 集群
  • Logstash 读取 Kafka 集群数据并按字段拆分后转成 JSON 格式存入 ES 集群
  • Kibana读取ES集群数据展示到web页面上

慢日志分类

目前主要使用的mysql版本有5.5、5.6 和 5.7,经过仔细对比发现每个版本的慢查询日志都稍有不同,如下:

5.5 版本慢查询日志

 

5.6 版本慢查询日志

 

5.7 版本慢查询日志

1535077408-1356-PNe8c6IlfcCbvgXtTabhSb9TXmaw

慢查询日志异同点:

  1. 每个版本的Time字段格式都不一样
  2. 相较于5.6、5.7版本,5.5版本少了Id字段
  3. use db语句不是每条慢日志都有的
  4. 可能会出现像下边这样的情况,慢查询块# Time:下可能跟了多个慢查询语句

1535077408-4980-lKvIYRERaSHVcMH61JibC5ibOszg

处理思路

上边我们已经分析了各个版本慢查询语句的构成,接下来我们就要开始收集这些数据了,究竟应该怎么收集呢?

  1. 拼装日志行:mysql 的慢查询日志多行构成了一条完整的日志,日志收集时要把这些行拼装成一条日志传输与存储。
  2. Time行处理:# Time: 开头的行可能不存在,且我们可以通过SET timestamp这个值来确定SQL执行时间,所以选择过滤丢弃Time行
  3. 一条完整的日志:最终将以# User@Host: 开始的行,和以SQL语句结尾的行合并为一条完整的慢日志语句
  4. 确定SQL对应的DB:use db这一行不是所有慢日志SQL都存在的,所以不能通过这个来确定SQL对应的DB,慢日志中也没有字段记录DB,所以这里建议为DB创建账号时添加db name标识,例如我们的账号命名方式为:projectName_dbName,这样看到账号名就知道是哪个DB了
  5. 确定SQL对应的主机:我想通过日志知道这条SQL对应的是哪台数据库服务器怎么办?

    慢日志中同样没有字段记录主机,可以通过filebeat注入字段来解决,例如我们给filebeat的name字段设置为服务器IP,这样最终通过beat.name这个字段就可以确定SQL对应的主机了。

Filebeat配置

filebeat 完整的配置文件如下:

 

重要参数解释:

  • input_type:指定输入的类型是log或者是stdin
  • paths:慢日志路径,支持正则,比如/data/*.log
  • exclude_lines:过滤掉# Time开头的行
  • multiline.pattern:匹配多行时指定正则表达式,这里匹配以# Time或者# User开头的行,Time行要先匹配再过滤
  • multiline.negate:定义上边pattern匹配到的行是否用于多行合并,也就是定义是不是作为日志的一部分
  • multiline.match:定义如何将皮排行组合成时间,在之前或者之后
  • tail_files:定义是从文件开头读取日志还是结尾,这里定义为true,从现在开始收集,之前已存在的不管
  • name:设置filebeat的名字,如果为空则为服务器的主机名,这里我们定义为服务器IP
  • output.kafka:配置要接收日志的kafka集群地址可topic名称

Kafka 接收到的日志格式:

 

{“@timestamp”:”2018-08-07T09:36:00.140Z”,”beat”:{“hostname”:”db-7eb166d3″,”name”:”10.63.144.71″,”version”:”5.4.0″},”input_type”:”log”,”message”:”# User@Host: select[select] @  [10.63.144.16]  Id: 23460596\n# Query_time: 0.155956  Lock_time: 0.000079 Rows_sent: 112  Rows_examined: 366458\nSET timestamp=1533634557;\nSELECT DISTINCT(uid) FROM common_member WHERE hideforum=-1 AND uid != 0;”,”offset”:1753219021,”source”:”/data/slow/mysql_slow.log”,”type”:”log”}

Logstash配置

logstash完整的配置文件如下:

 

重要参数解释:

  • input:配置 kafka 的集群地址和 topic 名字
  • filter:过滤日志文件,主要是对 message 信息(看前文 kafka 接收到的日志格式)进行拆分,拆分成一个一个易读的字段,例如User、Host、Query_time、Lock_time、timestamp等。

    grok段根据我们前文对mysql慢日志的分类分别写不通的正则表达式去匹配,当有多条正则表达式存在时,logstash会从上到下依次匹配,匹配到一条后边的则不再匹配。

    date字段定义了让SQL中的timestamp_mysql字段作为这条日志的时间字段,kibana上看到的实践排序的数据依赖的就是这个时间

  • output:配置ES服务器集群的地址和index,index自动按天分割

kibana查询展示

打开Kibana添加 mysql-slowlog-* 的Index,并选择timestamp,创建Index Pattern

 

进入Discover页面,可以很直观的看到各个时间点慢日志的数量变化,可以根据左侧Field实现简单过滤,搜索框也方便搜索慢日志,例如我要找查询时间大于2s的慢日志,直接在搜索框输入 query_time: > 2 回车即可。

 

点击每一条日志起边的很色箭头能查看具体某一条日志的详情。

1535077408-6683-Dibdog7XPG3UGEDBUZgSyY2OWTeA

如果你想做个大盘统计慢日志的整体情况,例如top 10 SQL等,也可以很方便的通过web界面配置。

1535077408-2887-AYtL3RW4ibMUQbmx6n9kFwdTsibw

mysql 已设置utf8mb4 还是存不了表情【解决方法】

1,emoji介绍

(1)emoji 就是表情符号,来自日语词汇“絵文字”(假名为“えもじ”,读音即 emoji)。
(2)最早由栗田穰崇(Shigetaka Kurita)创作,并在日本网络及手机用户中流行。 自苹果公司发布的 iOS 5 输入法中加入了 emoji 后,这种表情符号开始席卷全球。
(3)目前 emoji 已被大多数现代计算机系统所兼容的 Unicode 编码采纳,普遍应用于各种手机短信和社交网络中。
1535075603-7981-201610
2,问题描述

最近使用 MySQL 作为一个移动应用的数据库。但是不管使用 Anroid 设备,还是 iOS 设备。只要插入包含有 emoji 表情符号的记录时就报错。
1535075604-3223-201610

3,问题原因

MySQL 我使用的是默认的 utf8 编码,UTF8 编码只支持 1-3 个字节。而 emoji 占有 4 个字节的存储空间,所以自然保存不了。
从 MYSQL5.5 开始,可支持 4 个字节 UTF 编码,只要将编码标记成 utf8mb4 即可。并且 utf8mb4 是兼容 utf8 的。

了解一下mysql分区示例(partition)

一.数据分区

mysql数据库中的数据是以文件的形式存储在磁盘介质上,具体存储目录,请查阅“my.cnf”中的“datadir”属性。一张数据表主要对应着三个文件,一个是FRM存放表结构的,一个是MYD存放表数据的,一个是MYI存表索引的。如果一张表的数据量太大的话,那么MYD、MYI就会变的很大,查找数据就会变得很慢,这个时候可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样做的话,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。

1.横向分区

就是横着来分区,举例:假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。也就是把表分成了十分,根用merge来分表,有点像哦。取出一条数据的时候,这条数据包含了表结构中的所有字段,也就是说横向分区,并没有改变表的结构。

2.纵向分区

什么是纵向分区呢?就是竖来分区了,举例来说明,在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这样这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,在去查找,分表的时候,可以把这样的大字段,分开来。

mysql提供的分区属于第一种,横向分区,并且细分成很多种方式

12313