|
|
|
|
|
-- ============================================
|
|
|
|
|
|
-- RuoYi-Vue 数据库索引优化脚本
|
|
|
|
|
|
-- 创建日期: 2026-06-28
|
|
|
|
|
|
-- 说明: 为核心业务表添加缺失索引
|
|
|
|
|
|
-- ============================================
|
|
|
|
|
|
|
|
|
|
|
|
-- 1. 用户表索引
|
|
|
|
|
|
ALTER TABLE sys_user ADD INDEX idx_user_name (user_name);
|
|
|
|
|
|
ALTER TABLE sys_user ADD INDEX idx_dept_id (dept_id);
|
|
|
|
|
|
ALTER TABLE sys_user ADD INDEX idx_email (email);
|
|
|
|
|
|
ALTER TABLE sys_user ADD INDEX idx_phone (phonenumber);
|
|
|
|
|
|
ALTER TABLE sys_user ADD INDEX idx_status (status);
|
|
|
|
|
|
|
|
|
|
|
|
-- 2. 角色表索引
|
|
|
|
|
|
ALTER TABLE sys_role ADD INDEX idx_role_key (role_key);
|
|
|
|
|
|
ALTER TABLE sys_role ADD INDEX idx_status (status);
|
|
|
|
|
|
|
|
|
|
|
|
-- 3. 菜单表索引
|
|
|
|
|
|
ALTER TABLE sys_menu ADD INDEX idx_menu_name (menu_name);
|
|
|
|
|
|
ALTER TABLE sys_menu ADD INDEX idx_parent_id (parent_id);
|
|
|
|
|
|
|
|
|
|
|
|
-- 4. 部门表索引
|
|
|
|
|
|
ALTER TABLE sys_dept ADD INDEX idx_parent_id (parent_id);
|
|
|
|
|
|
ALTER TABLE sys_dept ADD INDEX idx_status (status);
|
|
|
|
|
|
|
|
|
|
|
|
-- 5. 字典表索引
|
|
|
|
|
|
ALTER TABLE sys_dict_type ADD INDEX idx_dict_type (dict_type);
|
|
|
|
|
|
ALTER TABLE sys_dict_data ADD INDEX idx_dict_type (dict_type);
|
|
|
|
|
|
ALTER TABLE sys_dict_data ADD INDEX idx_status (status);
|
|
|
|
|
|
|
|
|
|
|
|
-- 6. 业务表索引（book-system）
|
|
|
|
|
|
ALTER TABLE account ADD INDEX idx_user_id (user_id);
|
|
|
|
|
|
ALTER TABLE account ADD INDEX idx_status (status);
|
|
|
|
|
|
ALTER TABLE book ADD INDEX idx_user_id (user_id);
|
|
|
|
|
|
ALTER TABLE book ADD INDEX idx_account_id (account_id);
|
|
|
|
|
|
|
|
|
|
|
|
-- 7. 股票表索引（核心优化）
|
|
|
|
|
|
ALTER TABLE stocks ADD INDEX idx_stock_code (code);
|
|
|
|
|
|
ALTER TABLE stocks ADD INDEX idx_trade_date (trade_date);
|
|
|
|
|
|
ALTER TABLE stocks ADD INDEX idx_code_date (code, trade_date);
|
|
|
|
|
|
ALTER TABLE stock_index ADD INDEX idx_index_code (code);
|
|
|
|
|
|
ALTER TABLE stock_index ADD INDEX idx_trade_date (trade_date);
|
|
|
|
|
|
ALTER TABLE trends ADD INDEX idx_stock_code (stock_code);
|
|
|
|
|
|
ALTER TABLE trends ADD INDEX idx_trade_date (trade_date);
|
|
|
|
|
|
ALTER TABLE trends ADD INDEX idx_code_date (stock_code, trade_date);
|
