择天记,有声读物

云業(yè)務(wù)管理系統(tǒng)（國內(nèi)版）

數(shù)據(jù)庫索引優(yōu)化

最后更新時間：2024-01-11 15:59:47

##### 說明

- 當(dāng)平臺業(yè)務(wù)量大數(shù)據(jù)多，系統(tǒng)使用過程中開始出現(xiàn)頁面加載慢、卡頓的情況，需優(yōu)化數(shù)據(jù)庫表索引。
- <font color='#FF000'>操作前，請務(wù)必備份數(shù)據(jù)庫數(shù)據(jù)，數(shù)據(jù)庫備份參考文檔：http://shsolar88.cn/doc/391.html</font>
- 執(zhí)行SQL語句后未立即返回結(jié)果屬正?，F(xiàn)象，耐心等待即可，請勿進(jìn)行其他操作導(dǎo)致SQL語句執(zhí)行中斷。

##### 操作步驟

1. 使用phpmyadmin或Navicat等數(shù)據(jù)庫管理軟件連接數(shù)據(jù)庫

2. 選中平臺使用的庫，<font color='#FF000'>一條一條執(zhí)行以下語句，請勿批量執(zhí)行。</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=X01cIaDbk_V2-HS0Oi8Jlw&expires=1731672436)

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

***當(dāng)返回以下內(nèi)容時，證明索引已存在，忽略該SQL語句即可。***

![](https://upload.apayun.com/2021/07/60e3b5063e195.jpg?token=MS5NJlkDP-96NwBoqxIrtQ&expires=1731672436)

阿帕云引擎

云引擎受控端

云引擎控制器

云引擎開放API

文檔首頁

云業(yè)務(wù)管理系統(tǒng)（國內(nèi)版）

數(shù)據(jù)庫索引優(yōu)化

18+

1000+

30+

5+