#mysql 优化

Junjie

2026-04-15 fa96027d61fdabdd651beef069d0c6ac51af00ea

#mysql 优化

 src/main/java/com/core/config/CoolBaseConfig.java

@@ -2,7 +2,6 @@

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.core.common.SnowflakeIdWorker;
import com.core.common.SpringUtils;
import org.springframework.context.annotation.Bean;
@@ -14,7 +13,7 @@
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        interceptor.addInnerInterceptor(new FastPaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }


 src/main/java/com/core/config/FastPaginationInnerInterceptor.java

New file
@@ -0,0 +1,63 @@
package com.core.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;

/**
 * Optimized pagination interceptor that avoids full-table COUNT queries
 * for pages without WHERE conditions.
 *
 * <p>Strategy:
 * <ul>
 *   <li>SQL WITH WHERE clause -> delegate to parent (normal COUNT(*), indexes help)</li>
 *   <li>SQL WITHOUT WHERE clause -> rewrite to simple SELECT COUNT(*) FROM table,
 *       which lets InnoDB choose the smallest available index for the scan</li>
 * </ul>
 */
@Slf4j
public class FastPaginationInnerInterceptor extends PaginationInnerInterceptor {

    public FastPaginationInnerInterceptor(DbType dbType) {
        super(dbType);
    }

    @Override
    public String autoCountSql(IPage<?> page, String sql) {
        if (!page.optimizeCountSql()) {
            return super.autoCountSql(page, sql);
        }

        try {
            Statement statement = CCJSqlParserUtil.parse(sql);
            if (!(statement instanceof Select)) {
                return super.autoCountSql(page, sql);
            }

            Select select = (Select) statement;
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

            // If SQL has WHERE conditions, delegate to parent's full COUNT rewriting
            if (plainSelect.getWhere() != null) {
                return super.autoCountSql(page, sql);
            }

            // No WHERE clause — this is the "browse all records" case.
            // Return minimal COUNT query so InnoDB picks the smallest index.
            if (plainSelect.getFromItem() != null) {
                String tableName = plainSelect.getFromItem().toString();
                return "SELECT COUNT(*) FROM " + tableName;
            }
        } catch (JSQLParserException e) {
            log.debug("SQL parsing failed for fast count, using default: {}", e.getMessage());
        }

        return super.autoCountSql(page, sql);
    }
}

 src/main/resources/sql/20260415_add_indexes_for_pagination_optimization.sql

New file
@@ -0,0 +1,132 @@
-- =====================================================
-- Pagination COUNT Optimization: Add time-column indexes
-- to all large log/operation tables for faster COUNT queries
-- and ORDER BY performance.
-- =====================================================

-- Tier 1: Operation log tables (ordered by send_time, no existing index)

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_crnp_opt'
    AND INDEX_NAME = 'idx_crnp_opt_send_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_crnp_opt` ADD INDEX `idx_crnp_opt_send_time` (`send_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_rgv_opt'
    AND INDEX_NAME = 'idx_rgv_opt_send_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_rgv_opt` ADD INDEX `idx_rgv_opt_send_time` (`send_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_station_opt'
    AND INDEX_NAME = 'idx_station_opt_send_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_station_opt` ADD INDEX `idx_station_opt_send_time` (`send_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_dual_crnp_opt'
    AND INDEX_NAME = 'idx_dual_crnp_opt_send_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_dual_crnp_opt` ADD INDEX `idx_dual_crnp_opt_send_time` (`send_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Tier 1: System log tables

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_wrk_mast_log'
    AND INDEX_NAME = 'idx_wrk_mast_log_modi_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_wrk_mast_log` ADD INDEX `idx_wrk_mast_log_modi_time` (`modi_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'man_api_log'
    AND INDEX_NAME = 'idx_api_log_create_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `man_api_log` ADD INDEX `idx_api_log_create_time` (`create_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sys_http_request_log'
    AND INDEX_NAME = 'idx_http_request_log_create_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `sys_http_request_log` ADD INDEX `idx_http_request_log_create_time` (`create_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sys_operate_log'
    AND INDEX_NAME = 'idx_operate_log_create_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `sys_operate_log` ADD INDEX `idx_operate_log_create_time` (`create_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Tier 2: Error log tables (no existing index on start_time)

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_crnp_err_log'
    AND INDEX_NAME = 'idx_crnp_err_log_start_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_crnp_err_log` ADD INDEX `idx_crnp_err_log_start_time` (`start_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_rgv_err_log'
    AND INDEX_NAME = 'idx_rgv_err_log_start_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_rgv_err_log` ADD INDEX `idx_rgv_err_log_start_time` (`start_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'asr_bas_dual_crnp_err_log'
    AND INDEX_NAME = 'idx_dual_crnp_err_log_start_time'
);
SET @sql := IF(@idx_exists = 0,
  'ALTER TABLE `asr_bas_dual_crnp_err_log` ADD INDEX `idx_dual_crnp_err_log_start_time` (`start_time`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;