想成为一名顶尖Java开发工程师？这些优化手段一定要掌握！(三)

/**
* {
*     "tableName": "tb_user",
*     "labCodes": [
*         "sh",//上海
*         "gz"//广州
*     ]
* }     
*/
public Boolean createTable(ConfigReq reqObject) {
  if (CollectionUtils.isEmpty(reqObject.getLabCodes())) {
    return false;
  }
  List<String> labCodes = reqObject.getLabCodes();
  for (String labCode: labCodes){
    //主表表名
    String tableName = reqObject.getTableName();
    //子表后表名
    String newTable = String.format("%s_%s", tableName, labCode);
    //校验子表是否存在
    Integer checkMatrix = configExtMapper.checkTable(newTable);
    if(checkMatrix == null || checkMatrix.intValue() < 0){
    //创建子表结构
    configExtMapper.createConfigTable(tableName, newTable);
    }
    }
  return true;
}

/**
* 主表字段同步到子表
* @param masterTable 主表
* @return
*/
private Boolean syncAlterTableColumn(String masterTable) {
  String table = masterTable + "%";
  //获取子表名
  List<String> tables = configExtMapper.getTableInfoList(table);
  if(CollectionUtils.isEmpty(tables)){
    return false;
  }
  //获取主表结构列信息
  List<ColumnInfo> masterColumns = configExtMapper.getColumnInfoList(masterTable);
  if (masterColumns.isEmpty()){
    return false;
  }
  String alterName = null;
  for (ColumnInfo column: masterColumns) {
    column.setAlterName(alterName);
    alterName = column.getColumnName();
  }
  for(String tableName : tables){
    if(StringUtils.equalsIgnoreCase(tableName, masterTable)){
      continue;
    }
    //获取子表结构列信息
    List<ColumnInfo> columns = configExtMapper.getColumnInfoList(tableName);
    if(CollectionUtils.isEmpty(columns)){
      continue;
    }
    for (ColumnInfo masterColumn : masterColumns) {
      ColumnInfo column = columns.stream().filter(c -> StringUtils.equalsIgnoreCase(c.getColumnName(),
      masterColumn.getColumnName())).findFirst().orElse(null);
      if (column == null){
        column = new ColumnInfo();
        column.setColumnName(masterColumn.getColumnName());//列名
        column.setAddColumn(true);//是否修改
      }
      if (column.hashCode() == masterColumn.hashCode()){
        continue;
      }
      column.setTableName(tableName);//表名
      column.setColumnDef(masterColumn.getColumnDef());//是否默认值
      column.setIsNull(masterColumn.getIsNull());//是否允许为空（NO：不能为空、YES：允许为空）
      column.setColumnType(masterColumn.getColumnType());//字段类型（如：varchar(512)、text、bigint(20)、datetime）
      column.setComment(masterColumn.getComment());//字段备注（如：备注）
      column.setAlterName(masterColumn.getAlterName());//修改的列名
      //创建子表字段
      configExtMapper.alterTableColumn(column);
    }
  }
  return true;
}

/**
* 主表索引同步子表
* @param masterTableName 主表名
* @return
*/
private Boolean syncAlterConfigIndex(String masterTableName) {
  String table = masterTableName + "%";
  //获取子表名
  List<String> tableInfoList = configExtMapper.getTableInfoList(table);
  if (tableInfoList.isEmpty()){
    return false;
  }
  // 获取所有索引
  List<String> allIndexFromTableName = configExtMapper.getAllIndexNameFromTableName(masterTableName);
  if (CollectionUtils.isEmpty(allIndexFromTableName)) {
    return false;
  }
  for (String indexName : allIndexFromTableName) {
    //获取拥有索引的列名
    List<String> indexFromIndexName = configExtMapper.getAllIndexFromTableName(masterTableName, indexName);
    for (String tableName : tableInfoList) {
      if (!tableName.startsWith(masterTableName)) {
        continue;
      }
      //获取索引名称
      List<String> addIndex = configExtMapper.findIndexFromTableName(tableName, indexName);
      if (CollectionUtils.isEmpty(addIndex)) {
        //创建子表索引
        configExtMapper.commonCreatIndex(tableName, indexName, indexFromIndexName);
      }
    }
  }
  return true;
}

<!--校验子表是否存在 这里db_user写死了数据库名称，后面可以根据实际情况调整-->
<select id="checkTable" resultType="java.lang.Integer" >
  SELECT 1 FROM INFORMATION_SCHEMA.`TABLES` WHERE TABLE_SCHEMA = 'db_user' AND TABLE_NAME = #{tableName};
</select>
<!--创建子表结构-->
<update id="createConfigTable" >
  CREATE TABLE `${newTableName}` LIKE `${sourceName}`;
</update>
<!--获取子表名-->
<select id="getTableInfoList" resultType="java.lang.String">
  SELECT `TABLE_NAME`
  FROM INFORMATION_SCHEMA.`TABLES`
  WHERE `TABLE_NAME` LIKE #{tableName};
</select>
<!--获取主/子表结构列信息 这里db_user写死了数据库名称，后面可以根据实际情况调整-->
<select id="getColumnInfoList" resultType="com.yunxi.datascript.config.ColumnInfo">
  SELECT `COLUMN_NAME` AS columnName
  ,COLUMN_DEFAULT AS columnDef   -- 是否默认值
  ,IS_NULLABLE AS isNull    -- 是否允许为空
  ,COLUMN_TYPE AS columnType    -- 字段类型
  ,COLUMN_COMMENT AS comment      -- 字段备注
  FROM INFORMATION_SCHEMA.`COLUMNS`
  WHERE TABLE_SCHEMA = 'db_user'
  AND `TABLE_NAME` = #{tableName}
  ORDER BY ORDINAL_POSITION ASC;
</select>
<!--创建子表字段-->
<update id="alterTableColumn" parameterType="com.yunxi.datascript.config.ColumnInfo">
  ALTER TABLE `${tableName}`
  <choose>
    <when test="addColumn">
      ADD COLUMN
    </when >
    <otherwise>
      MODIFY COLUMN
    </otherwise>
  </choose>
  ${columnName}
  ${columnType}
  <choose>
    <when test="isNull != null and isNull == 'NO'">
      NOT NULL
    </when >
    <otherwise>
      NULL
    </otherwise>
  </choose>
  <if test="columnDef != null and columnDef != ''">
    DEFAULT #{columnDef}
  </if>
  <if test="comment != null and comment != ''">
    COMMENT #{comment}
  </if>
  <if test="alterName != null and alterName != ''">
    AFTER ${alterName}
  </if>
</update>
<!--获取所有索引-->
<select id="getAllIndexNameFromTableName" resultType="java.lang.String">
  SELECT DISTINCT index_name FROM information_schema.statistics WHERE table_name = #{tableName} AND index_name != 'PRIMARY'
</select>
<!--获取拥有索引的列名-->
<select id="getAllIndexFromTableName" resultType="java.lang.String">
  SELECT COLUMN_NAME FROM information_schema.statistics WHERE table_name = #{tableName} AND index_name = #{idxName} AND index_name != 'PRIMARY'
</select>
<!--获取索引名称-->
<select id="findIndexFromTableName" resultType="java.lang.String">
  SELECT index_name FROM information_schema.statistics WHERE table_name = #{tableName} AND index_name = #{idxName}
</select>
<!--创建子表索引-->
<update id="commonCreatIndex">
  CREATE INDEX ${idxName} ON `${tableName}`
  <foreach collection="list" item="item" open="(" close=")" separator=",">
    `${item}`
  </foreach>;
</update>

MSET、MSETNX、MGET、LPUSH、RPUSH、LRANGE、LINDEX、LSET、LINSERT、HDEL、HGETALL、HKEYS/HVALS、SMEMBERS、
SUNION/SUNIONSTORE、SINTER/SINTERSTORE、SDIFF/SDIFFSTORE、ZRANGE/ZREVRANGE、ZRANGEBYSCORE/ZREVRANGEBYSCORE、
ZREMRANGEBYRANK/ZREMRANGEBYSCORE、DEL、KEYS

CONFIG SET slowlog-log-slower-than 10000

CONFIG SET slowlog-max-len 1000

#-h:redis主机ip
#-p: redis端口号
#-i:隔几秒扫描
redis-cli -h 127.0.0.1 -p 6379 --bigkeys -i 0.01

list-max-ziplist-entries 512
list-max-ziplist-value 64