Lucanlee
4d36a284ff
- V0.9.1 (P0): Model::count() 改 SELECT COUNT(*)；新增请求内 identity-map 缓存； Product::featured() 改 WHERE+ LIMIT 避免全表 - V0.9.2 (P1): schema.sql 内联二级索引 + install/upgrades/009_perf_indexes.sql 幂等迁移 - V0.9.3 (P1): psi_unread_events() 改 COUNT + JSON_CONTAINS，消除每页 all()+循环
56 lines
3.0 KiB
SQL
56 lines
3.0 KiB
SQL
-- V0.9.2 性能优化：为高频查询列补齐二级索引（MySQL 5.7 兼容）
|
|
-- 通过存储过程判断索引是否已存在，重复执行不会报错。
|
|
-- 部署方式：放入 install/upgrades/ 后，后台「数据库升级」一键应用（仅超级管理员）。
|
|
-- 升级记录会写入 db_upgrades，内容不变不会重复执行。
|
|
|
|
DROP PROCEDURE IF EXISTS _perf_add_idx;
|
|
DELIMITER $$
|
|
CREATE PROCEDURE _perf_add_idx(IN p_table VARCHAR(64), IN p_name VARCHAR(64), IN p_cols VARCHAR(255))
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND INDEX_NAME = p_name
|
|
) THEN
|
|
SET @_perf_sql = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_name, '` (', p_cols, ')');
|
|
PREPARE _perf_stmt FROM @_perf_sql;
|
|
EXECUTE _perf_stmt;
|
|
DEALLOCATE PREPARE _perf_stmt;
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|
|
|
|
CALL _perf_add_idx('categories','idx_categories_slug','`slug`');
|
|
CALL _perf_add_idx('products','idx_products_category_id','`category_id`');
|
|
CALL _perf_add_idx('products','idx_products_slug','`slug`');
|
|
CALL _perf_add_idx('products','idx_products_status','`status`');
|
|
CALL _perf_add_idx('news','idx_news_slug','`slug`');
|
|
CALL _perf_add_idx('news','idx_news_status','`status`');
|
|
CALL _perf_add_idx('cases','idx_cases_slug','`slug`');
|
|
CALL _perf_add_idx('cases','idx_cases_status','`status`');
|
|
CALL _perf_add_idx('pages','idx_pages_slug','`slug`');
|
|
CALL _perf_add_idx('banners','idx_banners_status','`status`');
|
|
CALL _perf_add_idx('banners','idx_banners_sort','`sort_order`');
|
|
CALL _perf_add_idx('admin_users','idx_admin_users_role','`role`');
|
|
CALL _perf_add_idx('orders','idx_orders_order_no','`order_no`');
|
|
CALL _perf_add_idx('orders','idx_orders_status','`status`');
|
|
CALL _perf_add_idx('orders','idx_orders_product_id','`product_id`');
|
|
CALL _perf_add_idx('payments','idx_payments_order_id','`order_id`');
|
|
CALL _perf_add_idx('payments','idx_payments_order_no','`order_no`');
|
|
CALL _perf_add_idx('crm_customers','idx_customers_status','`status`');
|
|
CALL _perf_add_idx('crm_contacts','idx_contacts_customer_id','`customer_id`');
|
|
CALL _perf_add_idx('crm_leads','idx_leads_customer_id','`customer_id`');
|
|
CALL _perf_add_idx('crm_followups','idx_followups_customer_id','`customer_id`');
|
|
CALL _perf_add_idx('crm_followups','idx_followups_lead_id','`lead_id`');
|
|
CALL _perf_add_idx('psi_materials','idx_materials_supplier_id','`supplier_id`');
|
|
CALL _perf_add_idx('psi_materials','idx_materials_code','`code`');
|
|
CALL _perf_add_idx('psi_products','idx_psi_products_code','`code`');
|
|
CALL _perf_add_idx('psi_purchases','idx_purchases_supplier_id','`supplier_id`');
|
|
CALL _perf_add_idx('psi_purchases','idx_purchases_order_no','`order_no`');
|
|
CALL _perf_add_idx('psi_sales','idx_sales_order_no','`order_no`');
|
|
CALL _perf_add_idx('psi_stock_moves','idx_stock_moves_item','`item_type`,`item_id`');
|
|
CALL _perf_add_idx('settings','idx_settings_sgroup','`sgroup`');
|
|
CALL _perf_add_idx('psi_events','idx_events_sys','`sys`');
|
|
CALL _perf_add_idx('psi_events','idx_events_level','`level`');
|
|
|
|
DROP PROCEDURE IF EXISTS _perf_add_idx;
|