What is horizontal sharding?

Horizontal sharding is a database scaling technique where data is partitioned into parts called shards, and these shards are distributed across multiple independent servers. Each shard contains a subset of all table rows but maintains all columns.

Advantages of horizontal sharding:

• Scalability: Handles increasing data volume and traffic by adding new servers for shards.
• Performance: Reduces data each server processes, speeding up queries.
• Availability: If one shard fails, others continue to operate.
• Load reduction: Distributes I/O load across multiple servers.

Disadvantages:

• Complexity: Implementation and management require significant engineering effort.
• Distributed transactions: Multi-shard transactions are complex and may reduce performance.
• Rebalancing: Redistributing data when load changes or new servers are added can be complex and resource-intensive.
• Cross-shard queries: Queries requiring joins across shards can be inefficient.

Sharding methods:

• Range-based: Data is distributed based on value ranges in a key.
• Hash-based: Data is distributed based on hash value of a key.
• Directory-based: A lookup table indicates which shard contains specific data or ranges.

Example (simplified):

Suppose we have a user table with a large amount of data. Instead of storing all users on one server, we split them into shards based on the first letter of the last name:

• Shard 1: Last names from A to E
• Shard 2: Last names from Ж to Л
• Shard 3: Last names from М to С
• Shard 4: Last names from Т to Я

Each shard is stored on a separate server.

<?php

// Example function to determine shard based on last name
function determineShardByLastName(string $lastName): int
{
    $firstLetter = mb_strtoupper(mb_substr($lastName, 0, 1));

    if ($firstLetter >= 'А' && $firstLetter <= 'Е') {
        return 1;
    } elseif ($firstLetter >= 'Ж' && $firstLetter <= 'Л') {
        return 2;
    } elseif ($firstLetter >= 'М' && $firstLetter <= 'С') {
        return 3;
    } elseif ($firstLetter >= 'Т' && $firstLetter <= 'Я') {
        return 4;
    }

    return 0; // For undefined cases
}

// Usage
$lastName = "Иванов";
$shardId = determineShardByLastName($lastName);
// In a real system, connect to the corresponding shard database

?>