数据库索引优化

##### 说明

- 当平台业务量大数据多，系统使用过程中开始出现页面加载慢、卡顿的情况，需优化数据库表索引。
- <font color='#FF000'>操作前，请务必备份数据库数据，数据库备份参考文档：https://www.apayun.com/doc/391.html</font>
- 执行SQL语句后未立即返回结果属正常现象，耐心等待即可，请勿进行其他操作导致SQL语句执行中断。

##### 操作步骤

1. 使用phpmyadmin或Navicat等数据库管理软件连接数据库

2. 选中平台使用的库，<font color='#FF000'>一条一条执行以下语句，请勿批量执行。</font>

```
ALTER TABLE `admin` ADD INDEX `idx_groupId` USING BTREE(`groupId`);
ALTER TABLE `admin_auth_group_access` ADD INDEX `idx_group_id` USING BTREE(`group_id`);
ALTER TABLE `admin_auth_group_access` ADD INDEX `idx_uid` USING BTREE(`uid`);
ALTER TABLE `admin_log` ADD INDEX `idx_createTime` USING BTREE(`createTime`);
ALTER TABLE `admin_role` ADD INDEX `idx_role_id` USING BTREE(`role_id`);
ALTER TABLE `admin_role` ADD INDEX `idx_user_id` USING BTREE(`user_id`);
ALTER TABLE `admin_workflow` ADD INDEX `idx_type` USING BTREE(`type`);
ALTER TABLE `admin_workflow_step` ADD INDEX `idx_code` USING BTREE(`code`);
ALTER TABLE `admin_workflow_step` ADD INDEX `idx_stepLevel` USING BTREE(`stepLevel`);
ALTER TABLE `admin_workflow_step` ADD INDEX `idx_workflowId` USING BTREE(`workflowId`);
ALTER TABLE `admin_workflow_user` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `admin_workflow_user` ADD INDEX `idx_workflowId` USING BTREE(`workflowId`);
ALTER TABLE `admin_workflow_user` ADD INDEX `idx_workflowStepId` USING BTREE(`workflowStepId`);
ALTER TABLE `baremetal_log` ADD INDEX `idx_type_create_time` USING BTREE(`type`, `create_time`);
ALTER TABLE `baremetal_service` ADD INDEX `baremetal_guid` USING BTREE(`baremetal_guid`);
ALTER TABLE `baremetal` ADD INDEX `baremetal` USING BTREE(`guid`);
ALTER TABLE `cm_list` ADD INDEX `product_guid` USING BTREE(`product_guid`);
ALTER TABLE `cashcoupon` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `cashcoupon_log` ADD INDEX `idx_cashCouponID` USING BTREE(`cashCouponID`);
ALTER TABLE `cashcoupon_log` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `cdn` ADD INDEX `idx_create_time` USING BTREE(`create_time`);
ALTER TABLE `cdn` ADD INDEX `idx_user_id` USING BTREE(`user_id`);
ALTER TABLE `cdn_bill` ADD INDEX `idx_user_id_bill_time` USING BTREE(`user_id`, `bill_time`);
ALTER TABLE `cdn_bill_final_time` ADD INDEX `idx_user_id` USING BTREE(`user_id`);
ALTER TABLE `cdn_log_extra` ADD INDEX `idx_parent_id` USING BTREE(`parent_id`);
ALTER TABLE `custom_log` ADD INDEX `idx_product_id_type_create_time` USING BTREE(`product_id`, `type`, `create_time`);
ALTER TABLE `domains` ADD INDEX `idx_domain` USING BTREE(`domain`);
ALTER TABLE `domains` ADD INDEX `index_contact_guid` (`contact_guid`) USING BTREE ,ADD INDEX `index_guid` (`guid`) USING BTREE ;
ALTER TABLE `domain_contacts_template` ADD INDEX `index_guid` (`guid`) USING BTREE ;
ALTER TABLE `host` ADD INDEX `idx_guid` USING BTREE(`guid`);
ALTER TABLE `host` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `host_db_log` ADD INDEX `idx_type_createTime` USING BTREE(`type`, `createTime`);
ALTER TABLE `host_log` ADD INDEX `idx_type_createTime` USING BTREE(`type`, `createTime`);
ALTER TABLE `host_user_db` ADD INDEX `idx_hostId` USING BTREE(`hostId`);
ALTER TABLE `icp_firewall` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `icp_firewall` ADD INDEX `idx_webDomain` USING BTREE(`webDomain`);
ALTER TABLE `icp_web` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `icp_web` ADD INDEX `idx_webDomain` USING BTREE(`webDomain`);
ALTER TABLE `idc` ADD INDEX `idx_guid` USING BTREE(`guid`);
ALTER TABLE `idc` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `idc_ip_sub` ADD INDEX `idx_ip_segment_id` USING BTREE(`ip_segment_id`);
ALTER TABLE `idc_log` ADD INDEX `idx_type_createTime` USING BTREE(`type`, `createTime`);
ALTER TABLE `idc_pool_ip` ADD INDEX `idx_ip_segment_id` USING BTREE(`ip_segment_id`);
ALTER TABLE `idc_pool_ip` ADD INDEX `idx_ip_sub_id` USING BTREE(`ip_sub_id`);
ALTER TABLE `idc_task_log` ADD INDEX `idc_task_id` USING BTREE(`idc_task_id`);
ALTER TABLE `idc_task_log` ADD INDEX `action` USING BTREE(`action`);
ALTER TABLE `login_log` ADD INDEX `idx_userType_userId_type_result` USING BTREE(`userType`, `userId`, `type`, `result`);
ALTER TABLE `login_log` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `message` ADD INDEX `idx_type_isNotify_isNotifySend_isRead` USING BTREE(`type`, `isRead`);
ALTER TABLE `message` ADD INDEX `idx_isRead_type_toUserId_isNotify_isNotifySend` USING BTREE(`isRead`, `type`, `toUserId`);
ALTER TABLE `message` ADD INDEX `idx_message_id` USING BTREE(`message_id`);
ALTER TABLE `orders` ADD INDEX `idx_orderType` USING BTREE(`orderType`);
ALTER TABLE `orders` ADD INDEX `idx_orderType_orderStatus` USING BTREE(`orderType`, `orderStatus`);
ALTER TABLE `orders` ADD INDEX `idx_orderUserID` USING BTREE(`orderUserID`);
ALTER TABLE `orders` ADD INDEX `idx_PromotionUserID` USING BTREE(`PromotionUserID`);
ALTER TABLE `orders_extra` ADD INDEX `idx_attrValue` (`attrValue`(50)) USING BTREE  ;
ALTER TABLE `orders_extra` ADD INDEX `Index_name` (`attrName`, `attrValue`(50)) USING BTREE ;
ALTER TABLE `orders_extra` ADD INDEX `Index_orderID` (`orderID`) USING BTREE ;
ALTER TABLE `servers` ADD INDEX `idx_id_orderStatus_userID` USING BTREE(`id`, `orderStatus`, `userID`);
ALTER TABLE `servers` ADD INDEX `vps_name` USING BTREE(`vps_name`);
ALTER TABLE `servers` ADD INDEX `orderStatus` USING BTREE(`orderStatus`);
ALTER TABLE `servers` ADD INDEX `idx_id_userID` USING BTREE(`id`, `userID`);
ALTER TABLE `servers` ADD INDEX `idx_orderID` USING BTREE(`orderID`);
ALTER TABLE `servers` ADD INDEX `idx_guid` USING BTREE(`guid`);
ALTER TABLE `servers` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `servers_apm_list` ADD INDEX `idx_createTime` USING BTREE(`createTime`);
ALTER TABLE `servers_apm_list` ADD INDEX `idx_serverId_isDelete_userId` USING BTREE(`serverId`, `isDelete`, `userId`);
ALTER TABLE `servers_apm_list` ADD INDEX `idx_userId_serverId_rulesGuid` USING BTREE(`userId`, `serverId`, `rulesGuid`);
ALTER TABLE `servers_apm_list` ADD INDEX `idx_serverId` USING BTREE(`serverId`);
ALTER TABLE `servers_apm_rules` ADD INDEX `idx_guid` USING BTREE(`guid`);
ALTER TABLE `servers_log` ADD INDEX `idx_serverID_type_createTime` USING BTREE(`serverID`, `type`, `createTime`);
ALTER TABLE `servers_service` ADD INDEX `idx_server_id` USING BTREE(`server_id`);
ALTER TABLE `server_auto_task` ADD INDEX `idx_service_id` USING BTREE(`service_id`);
ALTER TABLE `storage_bill` ADD INDEX `idx_user_id_bill_time` USING BTREE(`user_id`, `bill_time`);
ALTER TABLE `storage_bill_data` ADD INDEX `idx_user_id_date_key` USING BTREE(`user_id`, `date`, `key`);
ALTER TABLE `trade_mark` ADD INDEX `idx_trade_code` USING BTREE(`trade_code`);
ALTER TABLE `users_data` ADD INDEX `idx_operator` USING BTREE(`operator`);
ALTER TABLE `users_data` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `users_key` ADD INDEX `idx_create_time` USING BTREE(`create_time`);
ALTER TABLE `users_key` ADD INDEX `idx_user_id` USING BTREE(`user_id`);
ALTER TABLE `users_log` ADD INDEX `idx_userId` USING BTREE(`userId`);
ALTER TABLE `users_money` ADD INDEX `idx_money` USING BTREE(`money`);
ALTER TABLE `users_money` ADD INDEX `idx_type` USING BTREE(`type`);
ALTER TABLE `users_money` ADD INDEX `idx_type_money` USING BTREE(`type`, `money`);
ALTER TABLE `users_money` ADD INDEX `idx_serviceID` USING BTREE(`serviceID`);
ALTER TABLE `users_money` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `users_recharge` ADD INDEX `idx_type_status_result` USING BTREE(`type`, `status`, `result`);
ALTER TABLE `user_screen_used_free_num` ADD INDEX `idx_userID` USING BTREE(`userID`);
ALTER TABLE `workorder_reply` ADD INDEX `idx_workorderID` USING BTREE(`workorderID`);
ALTER TABLE `yundb_master` ADD INDEX `idx_line_id` USING BTREE(`line_id`);
ALTER TABLE `yundb_product_storage` ADD INDEX `idx_product_id` USING BTREE(`product_id`);
ALTER TABLE `yundb_user_parameter` ADD INDEX `idx_instance_id_paraid` USING BTREE(`instance_id`, `paraid`);
ALTER TABLE `zkeys_card` ADD INDEX `idx_guid` USING BTREE(`guid`);
ALTER TABLE `zkeys_card` ADD INDEX `idx_user_id` USING BTREE(`user_id`);
ALTER TABLE `icp_analysis_log` ADD INDEX `index_web_id` USING BTREE(`web_id`);
ALTER TABLE `icp_website` ADD INDEX `index_analysis_time_cancel_able` USING BTREE(`cancel_able`, `check_analysis_time`);
ALTER TABLE `idc_pool_ip` ADD INDEX `ip_segment_id` USING BTREE(`ip_segment_id`);
ALTER TABLE `idc_pool_ip` ADD INDEX `ip_sub_id` USING BTREE(`ip_sub_id`);
ALTER TABLE `idc_ip_sub` ADD INDEX `ip_segment_id` USING BTREE(`ip_segment_id`);
ALTER TABLE `port` ADD INDEX `idx_serverid`  USING BTREE (`service_id`) ;
```

![](https://upload.apayun.com/2021/07/60dfcdfbb061f.jpg?token=4y5IQHJMxdcY3YUFAAhtNQ&expires=1714138122)

![](https://upload.apayun.com/2021/07/60dfce0875953.jpg?token=LZ7ZsX3BKyyL9OteIHDhNQ&expires=1714138122)

***当返回以下内容时，证明索引已存在，忽略该SQL语句即可。***

![](https://upload.apayun.com/2021/07/60e3b5063e195.jpg?token=dssY8tafFVcGp0-2hSGYHA&expires=1714138122)

阿帕云引擎

云引擎受控端

云引擎控制器

云引擎开放API

文档首页

云业务管理系统（国内版）

18+

1000+

30+

5+