MySQL高效全文索引：游戏对局结果存储与查询方案咨询

Hey there! Let's work through your game match result storage and query problem. Your initial idea of using a varchar with full-text index has some key pitfalls, so let's break down better, more efficient approaches tailored to MySQL.

First: Why Your Initial Idea Might Not Work #

The player_score varchar field with full-text index sounds simple, but it's not built for this kind of structured query:

• Full-text indexes are designed for natural language text, not structured key-value pairs like player:score. When querying multiple players' scores, you can't guarantee all conditions match the same match—you might get results where only one of the players meets their score condition.
• Exact matching is risky. For example, a player named Hamburger145 with a score of 1 might get incorrectly matched if you're searching for Hamburger14:5.
• Updates are clunky: changing one player's score requires rewriting the entire string, which is error-prone and inefficient.

Recommended Solution 1: Normalized Relational Design (Best for Long-Term) #

This is the gold standard for MySQL—it aligns with relational database best practices, supports fast queries, and is easy to maintain. Split your data into two tables:

1. matches Table (Stores Match Metadata) #

CREATE TABLE matches (
    match_id INT AUTO_INCREMENT PRIMARY KEY,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    -- Add other match-specific fields here (e.g., game mode, map name)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. player_match_scores Table (Stores Player-Score Per Match) #

This is a junction table that links players to their scores in each match:

CREATE TABLE player_match_scores (
    match_id INT NOT NULL,
    player_name VARCHAR(50) NOT NULL,
    score INT NOT NULL CHECK (score BETWEEN 1 AND 5),
    -- Composite primary key ensures one player can't have multiple scores in the same match
    PRIMARY KEY (match_id, player_name),
    -- Foreign key to enforce referential integrity
    FOREIGN KEY (match_id) REFERENCES matches(match_id) ON DELETE CASCADE,
    -- Critical index to speed up queries filtering by player + score
    INDEX idx_player_score (player_name, score),
    -- Auxiliary index to quickly fetch all players in a match
    INDEX idx_match_players (match_id, player_name)
);

Querying for Specific Player Scores #

To find all matches where Hamburger14 scored 5, Cheese3 scored 1, and Hotdog99 scored 4, use EXISTS clauses (this is efficient because it leverages the idx_player_score index):

SELECT m.match_id, m.start_time
FROM matches m
WHERE EXISTS (
    SELECT 1 FROM player_match_scores s 
    WHERE s.match_id = m.match_id 
      AND s.player_name = 'Hamburger14' 
      AND s.score = 5
)
AND EXISTS (
    SELECT 1 FROM player_match_scores s 
    WHERE s.match_id = m.match_id 
      AND s.player_name = 'Cheese3' 
      AND s.score = 1
)
AND EXISTS (
    SELECT 1 FROM player_match_scores s 
    WHERE s.match_id = m.match_id 
      AND s.player_name = 'Hotdog99' 
      AND s.score = 4
);

Why This Works:

• Flexibility: You can easily run other queries (e.g., "show all matches where Hamburger14 scored >3" or "calculate Cheese3's average score").
• Efficiency: The indexes ensure MySQL doesn't scan the entire table— it jumps directly to the relevant rows.
• Maintainability: Updating a player's score only requires modifying one row, not an entire string.

Recommended Solution 2: JSON Field (For Quick Prototyping) #

If you want to avoid table joins for a prototype or low-traffic app, use MySQL's JSON type. Note this is less efficient for complex queries than the normalized design, but it's simpler to set up:

Create the Table #

CREATE TABLE matches (
    match_id INT AUTO_INCREMENT PRIMARY KEY,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    player_scores JSON NOT NULL
);

Insert Data #

Store player scores as a JSON object:

INSERT INTO matches (start_time, end_time, player_scores)
VALUES (
    '2024-05-20 14:30:00',
    '2024-05-20 14:40:00',
    '{"Hamburger14":5, "Cheese3":1, "Hotdog99":4}'
);

Query the Data #

Use JSON_EXTRACT to filter for specific player scores:

SELECT match_id, start_time
FROM matches
WHERE JSON_EXTRACT(player_scores, '$.Hamburger14') = 5
  AND JSON_EXTRACT(player_scores, '$.Cheese3') = 1
  AND JSON_EXTRACT(player_scores, '$.Hotdog99') = 4;

For better performance, you can add a generated column and index for frequently queried players, but this doesn't scale well if you have hundreds of unique players.

Final Verdict #

Stick with the normalized relational design for most production scenarios—it's the most efficient, maintainable, and flexible option. The JSON approach works for quick prototypes, but avoid the original varchar+full-text index idea—it will cause more headaches than it solves.

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