71 lines
2.4 KiB
Python
71 lines
2.4 KiB
Python
|
"""
|
|||
|
|
数据库索引优化迁移脚本
|
|||
|
|
为Site、News表的高频查询字段添加索引，提升查询性能
|
|||
|
|
|
|||
|
|
执行方式: python migrations/add_performance_indexes.py
|
|||
|
|
"""
|
|||
|
|
import os
|
|||
|
|
import sys
|
|||
|
|
|
|||
|
|
# 添加项目根目录到路径
|
|||
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|||
|
|
|
|||
|
|
from app import create_app, db
|
|||
|
|
from models import Site, News, Tag
|
|||
|
|
|
|||
|
|
|
|||
|
|
def add_indexes():
|
|||
|
|
"""添加性能优化索引"""
|
|||
|
|
app = create_app('development')
|
|||
|
|
|
|||
|
|
with app.app_context():
|
|||
|
|
# 检查并添加索引
|
|||
|
|
indexes_to_add = [
|
|||
|
|
# Site表索引
|
|||
|
|
('idx_site_is_active', 'sites', 'is_active'),
|
|||
|
|
('idx_site_created_at', 'sites', 'created_at'),
|
|||
|
|
('idx_site_view_count', 'sites', 'view_count'),
|
|||
|
|
('idx_site_is_recommended', 'sites', 'is_recommended'),
|
|||
|
|
('idx_site_sort_order', 'sites', 'sort_order'),
|
|||
|
|
|
|||
|
|
# News表索引
|
|||
|
|
('idx_news_site_id', 'news', 'site_id'),
|
|||
|
|
('idx_news_is_active', 'news', 'is_active'),
|
|||
|
|
('idx_news_published_at', 'news', 'published_at'),
|
|||
|
|
('idx_news_created_at', 'news', 'created_at'),
|
|||
|
|
|
|||
|
|
# Tag表索引
|
|||
|
|
('idx_tag_sort_order', 'tags', 'sort_order'),
|
|||
|
|
]
|
|||
|
|
|
|||
|
|
for index_name, table_name, column_name in indexes_to_add:
|
|||
|
|
try:
|
|||
|
|
# 检查索引是否已存在
|
|||
|
|
check_sql = f"""
|
|||
|
|
SELECT COUNT(*) as count
|
|||
|
|
FROM information_schema.STATISTICS
|
|||
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|||
|
|
AND TABLE_NAME = '{table_name}'
|
|||
|
|
AND INDEX_NAME = '{index_name}'
|
|||
|
|
"""
|
|||
|
|
result = db.session.execute(db.text(check_sql)).fetchone()
|
|||
|
|
|
|||
|
|
if result[0] == 0:
|
|||
|
|
# 创建索引
|
|||
|
|
db.session.execute(db.text(
|
|||
|
|
f"CREATE INDEX {index_name} ON {table_name}({column_name})"
|
|||
|
|
))
|
|||
|
|
print(f"✓ 添加索引: {index_name} ON {table_name}({column_name})")
|
|||
|
|
else:
|
|||
|
|
print(f"- 索引已存在: {index_name}")
|
|||
|
|
|
|||
|
|
except Exception as e:
|
|||
|
|
print(f"✗ 添加索引失败 {index_name}: {str(e)}")
|
|||
|
|
|
|||
|
|
# 提交更改
|
|||
|
|
db.session.commit()
|
|||
|
|
print("\n索引优化完成！")
|
|||
|
|
|
|||
|
|
|
|||
|
|
if __name__ == '__main__':
|
|||
|
|
add_indexes()
|