学校考试系统单库与多库选型及性能优化技术问询

Hey there, let's break down your questions and give you practical, actionable advice based on your current exam system setup.

Short answer: Yes, absolutely—with targeted optimizations. Splitting into multi-database should be a fallback plan, not your first move, given your current scale and the overhead of managing distributed systems.

Your Two Key Questions Answered #

1. Is single-database optimization feasible and recommended for 1000 schools? #

100% feasible and highly recommended. Right now, you only have 40-50 active schools, so your current bottleneck is likely from inefficient indexing or query patterns rather than raw data volume. Fixing these first will be cheaper, faster, and easier to maintain than spinning up dozens of independent databases.

2. Can a single DB handle 2000 active schools (scaled from your current 40-50)? #

Yes, but you’ll need to implement the optimizations below to keep performance snappy. Multi-database becomes more attractive only if you hit hard, unresolvable limits (e.g., millions of new quiz records per day, persistent locking issues), which isn’t predictable from your current scale.

Current Data Context Recap (For Alignment) #

Just to ground our advice:

• 1000 registered schools, 40-50 active
• student: 8k+ rows (small, low risk)
• student_answers & quiz_takers: 70k+ rows each, with quiz_takers identified as the performance bottleneck

Practical Single-Database Optimization Steps #

1. Index Tuning (Fix the Low-Hanging Fruit) #

Since you already have indexes but still see bottlenecks, let’s refine them for your most common query patterns:

• quiz_takers (the critical bottleneck):
  • Drop any unused single-column indexes—they waste space and slow down write operations.
  • Add covering composite indexes for your top queries:
    • If you frequently track exam progress by school + quiz: (school_id, quiz_id, status) (include user_id, start_time, end_time as covered columns if your query needs them; MySQL 8.0+ supports INCLUDE for this, older versions can add them directly to the index).
    • If you pull user-specific exam history: (user_id, school_id, quiz_start_time)
    • Stick to 2-3 high-impact indexes to avoid over-indexing.
• student:
  • Add a composite index (school_id, student_id) if you often fetch students by school. If you need name/email in those queries, make it a covering index to avoid table lookups.
• student_answers:
  • Add a composite index (school_id, quiz_taker_id, question_id)—since each answer ties to a quiz session, this will drastically speed up fetching all answers for a single exam.

2. Summary Tables (Avoid Scanning Big Tables Repeatedly) #

Summary tables precompute aggregated data so you don’t run expensive COUNT()/AVG() queries on raw tables every time. Here are tailored designs for your use case:

school_quiz_summary (Track School-Level Exam Progress)

This lets teachers instantly see their school’s exam performance without scanning large raw tables:

CREATE TABLE school_quiz_summary (
    school_id INT NOT NULL,
    quiz_id INT NOT NULL,
    total_takers INT DEFAULT 0,
    completed_takers INT DEFAULT 0,
    avg_score DECIMAL(5,2) DEFAULT 0.00,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (school_id, quiz_id)
);

• Populate this with an hourly cron job (safer than triggers to avoid write locks) that aggregates data from quiz_takers (count total/completed) and student_answers (calculate average score).

student_quiz_summary (Student-Level Performance)

For quick access to a student’s exam history and stats:

CREATE TABLE student_quiz_summary (
    student_id INT NOT NULL,
    school_id INT NOT NULL,
    total_quizzes_taken INT DEFAULT 0,
    total_correct_answers INT DEFAULT 0,
    avg_overall_score DECIMAL(5,2) DEFAULT 0.00,
    last_active_date DATE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id),
    INDEX idx_school_student (school_id, student_id)
);

• Update this when a student completes an exam (via application logic or a post-commit trigger).

school_student_summary (School-Level Student Metrics)

For admin dashboards showing school-wide student activity:

CREATE TABLE school_student_summary (
    school_id INT NOT NULL PRIMARY KEY,
    total_students INT DEFAULT 0,
    active_students_30d INT DEFAULT 0, -- Students who took exams in the last 30 days
    total_quizzes_completed INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

• Refresh this daily to count active students and total quizzes from quiz_takers.

3. Additional Optimizations to Scale Further #

• Read-Write Separation: Set up a MySQL replica to offload all read queries (e.g., dashboard views, progress tracking) from the primary database. The primary only handles writes (submitting answers, starting exams).
• Caching: Use Redis to cache frequently accessed summary data (e.g., a school’s top 5 quiz results, a student’s recent exams). Set short TTLs (5-15 minutes) to keep data fresh without hitting the DB every time.
• Database Configuration Tweaks:
  • Set innodb_buffer_pool_size to ~70% of your server’s available RAM (this is the most impactful setting for InnoDB performance).
  • Adjust max_connections to match your actual traffic (avoid setting it too high—unused connections waste resources).
  • Enable innodb_flush_log_at_trx_commit = 2 (trades a tiny bit of durability for better write performance; only do this if you can tolerate losing 1 second of data in a crash).
• Query Optimization: Audit slow queries with EXPLAIN—look for full table scans, inefficient joins, or SELECT * queries that fetch unnecessary data. Rewrite them to use covering indexes and limit result sets.

When to Consider Multi-Database? #

Only switch to per-school or sharded databases if:

• Your quiz_takers/student_answers tables grow to 100M+ rows and even with optimizations, query latency becomes unacceptable.
• You have strict data isolation requirements (e.g., schools demand their data be physically separated).
• Write throughput exceeds what a single MySQL instance can handle (e.g., 10k+ writes per second).

Until then, single-database with the above optimizations is the most cost-effective and maintainable approach.

内容的提问来源于stack exchange，提问作者Compoot