When Does a MySQL Table Need Sharding? Understanding Size Limits and Performance

MySQL itself does not impose a hard limit on the number of rows in a single table; the limit depends on the operating system's file size constraints, though a common rule of thumb in the industry is about 5 million rows, after which sharding may be considered.

Alibaba's "Java Development Manual" recommends sharding when a single table exceeds 5 million rows or its size exceeds 2 GB.

A Simple Test

The following test inserts rows in a loop, records the count, and prints it to the console.

private static void insertDataDemo() {
  DButil dButil = new DButil();
  myCon = dButil.getConnection();
  try {
    int i = 0;
    while(1==1) {
      i++;
      String sql = "insert into users (user_name,user_password)"
        + " value ('"+i+"','password')";
      sta = myCon.createStatement();
      sta.execute(sql);
      System.out.println(i);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    dButil.close();
  }
}

After running overnight, more than two million rows were inserted, but the insertion speed slowed to one row every 2–3 seconds, which is unacceptable.

The maximum amount of data a MySQL table can store is not directly tied to row count but to MySQL configuration and hardware. MySQL loads table indexes into memory; with a sufficient InnoDB buffer size, the entire index can reside in memory, keeping queries fast.

When a table grows beyond a certain size, the index can no longer fit in memory, causing disk I/O during queries and degrading performance. This issue is also influenced by table schema design, but ultimately stems from memory limitations.