Say Goodbye to Painful MySQL Sharding: How Backends Can Choose NewSQL for Seamless Billion-Scale Scaling

During a recent interview, the interviewer asked about the depth of my practical experience with TiDB implementation.

With 14 years of hands-on experience in PHP backend development, I have long focused on MySQL high-concurrency architectures, large data table evolution, and performance optimization. I have deeply implemented everything from standalone tuning and master-slave replication to complete manual sharding solutions using Sharding-JDBC and MyCat. I have personally experienced and navigated the pitfalls of traditional MySQL scaling: cross-shard challenges, transaction consistency, and operational overhead.

In my daily work, although I have been keeping an eye on mainstream NewSQL distributed solutions like TiDB and encountering them in online architecture reviews and technical research, I had never systematically organized them into a standardized selection framework and implementation boundary document. Taking this interview as an opportunity, I combined my years of large-scale MySQL experience with mature production cases in the industry, and compared them against official architectures and best practices to produce this comprehensive review:

A full-dimensional selection guide for saying goodbye to manual sharding and achieving seamless scaling for hundreds of millions of records, while also clarifying a classic debate in engineering:

After scaling TiDB in our business, how should we layer and trade off ElasticSearch? Which scenarios should keep it, and which can be streamlined? This thoroughly explains the true architectural boundary between an RDBMS and a search engine.

This entire piece is summarized from the perspective of a senior PHP + MySQL practitioner, balancing interview-ready standards with real-world enterprise production evidence. It serves both to round out a knowledge system and to provide peers with a direct reference for database architecture selection.

As a veteran programmer with years of development experience, the most painful traps I have fallen into over the years are MySQL scaling issues. From running a standalone MySQL for the business initially, to hitting query stalls and write jitters when a single table exceeded tens of millions of rows, to reluctantly adopting Sharding-JDBC and MyCat for manual sharding—the entire process was an “ops nightmare.” Planning shard keys, debugging routing rules, navigating cross-shard JOINs and cross-shard transactions everywhere, manually migrating data during scaling, modifying business code, and forcing the PHP business layer to handle complex pagination, sorting, and aggregation logic. Small and medium teams simply cannot bear the weight of this architecture.

I believe many back-end developers like me have asked the same soul-searching question: Is there a database that is compatible with the MySQL protocol, connects seamlessly with PHP, requires us to worry nothing about sharding, easily supports hundreds of millions or billions of rows, and guarantees high availability and strong consistency? The answer is yes—NewSQL distributed databases were born to solve this exact pain point. In this article, drawing on my development experience, I will detail the mainstream MySQL-compatible distributed databases that require no manual sharding (including open-source and public cloud options). I will also answer the question everyone cares about most: After adopting TiDB, do we still need ElasticSearch (ES)? This provides the most practical reference for future database selection in production environments.

1. First, a Review: The “Fatal Pain Points” of Manual MySQL Sharding

Before discussing NewSQL, let’s thoroughly review the evolution bottlenecks of traditional MySQL architecture so everyone understands why we desperately need a “sharding-free” distributed database.

1. Inherent limitations of standalone MySQL: Once a single table’s data volume exceeds tens of millions, obvious performance bottlenecks appear—bloated indexes slow down queries, and write jitters occur as concurrency rises. Scaling can only be done by vertically upgrading the server (adding CPU, memory, or disk), but this approach has a hard ceiling and cannot support hundreds of millions of records and high concurrency.

• Sharding rules rely entirely on manual planning: Choosing which field to use as the shard key, how to split the data, and how to configure routing rules all require repeated debugging. If the shard key is chosen poorly, subsequent scaling and maintenance become much more troublesome;
• Cross-shard operations are extremely difficult to implement: Cross-shard JOINs and cross-shard transactions are practically “black magic.” You either give up strong consistency or write complex business logic as a fallback, making data inconsistency issues highly likely;
• Painful scaling and migration: When data continues to grow and new shards are needed, you must manually migrate data, adjust routing configurations, and even modify business code. During this process, service interruptions or data loss can easily occur;
• Doubled operational costs: Dedicated personnel are needed to maintain the middleware and sharded clusters, monitor the load of each shard, and troubleshoot shard anomalies. Small and medium teams simply do not have the manpower to support this.

For us back-end developers, the most painful part is that the familiar MySQL syntax requires massive adaptations after sharding. ORM usage in frameworks like Laravel and Yii2 must also be adjusted, drastically reducing business development efficiency. The core value of NewSQL is to help us escape these troubles—MySQL protocol compatibility, automatic sharding, zero manual maintenance, completely transparent to the business layer, and easily handling hundreds of millions of records.

2. Full Roundup of Mainstream MySQL-Compatible Distributed Databases with No Manual Sharding (Open Source + Public Cloud, Production-Ready)

Currently, MySQL-compatible distributed databases that require no manual sharding on the market fall into two main categories: open-source self-developed versions (suitable for private deployment and self-built data centers) and public cloud-managed versions (suitable for enterprises that want out-of-the-box solutions without the ops overhead). Below, we will break them down one by one, focusing on the core highlights, suitable scenarios, and shortcomings of each database to help you choose based on your business situation.

(1) Open-Source Self-Developed Versions (Top Choice for Private Deployment / Self-Built Data Centers)

These databases can be deployed on your own servers, giving you full control over data and operations. They are suitable for enterprises with high data security requirements and self-built data centers, and they are the top choice for PHP developers learning on their own or for small team implementations.

① TiDB (PingCAP) [Highly Recommended, Best Fit for the PHP Ecosystem]

This is the database I was asked about in the interview, and it is currently the NewSQL database best suited for PHP back-ends and the easiest to implement. As an open-source distributed NewSQL database developed by PingCAP, its core positioning is to “replace MySQL sharding, be compatible with the MySQL ecosystem, and support high concurrency with hundreds of millions of records.”

Core highlights (pay close attention, as they fit PHP development scenarios):

• Maximum MySQL compatibility: 100% compatible with the MySQL 5.7 protocol, syntax, and connection drivers. PHP projects (Laravel, Yii2, ThinkPHP) can connect directly without modifying any SQL statements or business code, making smooth migration stress-free;
• Automatic sharding, completely freeing your hands: The underlying layer automatically shards data by “Region” (default 96MB) and evenly distributes it across multiple TiKV nodes. There is no need to manually plan shard keys or migrate data. When data grows to hundreds of millions or billions of rows, you only need to add TiKV nodes, making scaling completely transparent;
• High availability + strong transactions: It achieves 3-replica deployment based on the Raft protocol, with automatic failover upon single-node failure, ensuring zero data loss and uninterrupted service, and supporting cross-datacenter disaster recovery. At the same time, it supports distributed strong ACID transactions, solving the pain point of difficult cross-shard transactions after sharding, making it suitable for core businesses like orders and payments;
• Integrated HTAP, balancing transaction and analysis: TiKV (row-store engine) handles OLTP (online transactions, such as user orders and payments), while TiFlash (column-store engine, optional) handles OLAP (online analysis, such as backend reports and data statistics). Data is synchronized in real time, eliminating the need to build a separate data warehouse for ETL. One database handles both transactions and analysis;
• Separated storage and compute, elastic scaling: TiDB Server (compute layer) and TiKV (storage layer) are separated. To increase concurrency capacity, add TiDB nodes; to store more data, add TiKV nodes. Scaling is flexible and costs are controllable.

Suitable scenarios:

• Internet scenarios: Large tables for orders, users, bills, and transactions, with data volumes ranging from tens of millions to tens of billions, requiring high-concurrency reads and writes;
• Legacy PHP project migration: When you don’t want to modify business code, want to escape the troubles of sharding, and smoothly replace traditional MySQL;
• Small and medium team implementation: Low learning curve. A test cluster can be started locally with a single command, and the operational difficulty is far lower than a sharding + middleware solution.

Shortcomings:

• Slightly heavy for small-scale data deployments: Compared to standalone MySQL, TiDB requires deploying three core components: TiDB, TiKV, and PD. Server resource usage is slightly higher, making it suitable for scenarios with larger data volumes. For small data volumes (single tables under a million rows), standalone MySQL is more efficient;
• Ultra-low latency queries are not as good as native MySQL: For simple queries requiring microsecond latency (such as a simple primary key lookup), TiDB’s latency is slightly higher than standalone MySQL. However, for the vast majority of PHP business applications (such as web APIs and backend management), it fully meets the requirements.

② OceanBase Open Source Edition (Self-developed by Ant Group)

OceanBase is a distributed database self-developed by Ant Group. Its core advantage is “financial-grade stability.” It supports Alipay’s core accounting system and Double 11 peak traffic. The open-source version is free to use and suitable for scenarios requiring extremely high stability.

Core highlights:

• Financial-grade high availability: Proven by years of real-world use at Alipay, it supports active-active deployment across different locations and automatic failover, with zero data loss, meeting financial industry compliance requirements;
• Strong compatibility: Compatible with the MySQL protocol while supporting some Oracle syntax, suitable for enterprises needing to migrate from Oracle to the MySQL ecosystem;
• High resource utilization: Supports automatic partitioning, baseline merging, and high data compression. For the same data volume, storage costs are lower than TiDB, and server resource utilization is higher;
• Strong consistency: Distributed transactions support ACID, suitable for core financial scenarios like accounting and payments.

Suitable scenarios: Finance, payments, and core accounting systems for enterprises with extremely high requirements for data consistency and stability.

Shortcomings:

• Steep learning curve: The architecture is more complex than TiDB, operational difficulty is high, and dedicated ops personnel are needed for maintenance. Implementation costs are high for small and medium teams;
• Community ecosystem is not as strong as TiDB: Domestic community activity is lower than TiDB. When problems arise, documentation and solutions are not as abundant as TiDB;
• PHP ecosystem adaptation is not as good as TiDB: Although it is compatible with the MySQL protocol, it has fewer adaptations and community cases in PHP frameworks compared to TiDB, and may require extra debugging during implementation.

③ PolarDB-X Open Source Edition (Self-developed by Alibaba)

PolarDB-X is Alibaba’s native distributed MySQL database. Its positioning is similar to TiDB, and its core advantage is “Alibaba ecosystem adaptation.” It is suitable for enterprises using Alibaba’s technology stack, and the open-source version can be deployed privately.

Core highlights:

• High MySQL compatibility: Seamlessly compatible with MySQL syntax and protocol. PHP projects can connect directly with low migration costs;
• Automatic sharding + global secondary indexes: The underlying layer shards automatically and supports global secondary indexes, solving the pain point of index queries after sharding;
• Alibaba ecosystem integration: Strong integration with other Alibaba Cloud products (such as RDS, OSS, DataWorks), suitable for enterprises already using the Alibaba ecosystem.

Suitable scenarios: Heavy Alibaba ecosystem users who need a privately deployed, MySQL-compatible distributed database.

Shortcomings: Community activity is lower than TiDB, and the open-source version’s feature update speed is not as fast as TiDB. The implementation advantages for enterprises outside the Alibaba ecosystem are not obvious.

(2) Public Cloud-Managed Editions (No Ops Required, Out-of-the-Box, Top Choice for Enterprise Production)

For most enterprises (especially small and medium teams), the operational cost of self-building a distributed database cluster is too high. Choosing a public cloud-managed version is a more efficient and worry-free choice—the vendor handles underlying deployment, operations, scaling, and fault handling, while we only need to focus on business development, ready to use out of the box.

1. Alibaba Cloud PolarDB

Alibaba Cloud’s managed distributed MySQL, optimized from the PolarDB-X open-source version, is Alibaba Cloud’s recommended “distributed solution for hundreds of millions of records” and one of the most widely used public cloud distributed MySQL databases in China.

Core advantages: Cloud-managed, completely shielding underlying sharding details, fully transparent to the business layer; supports elastic scaling, automatic backups, and self-healing; seamlessly integrates with Alibaba Cloud RDS, ECS, SLS, and other products, suitable for PHP project implementation on Alibaba Cloud; offers mature solutions for flash sales, orders, and massive transaction scenarios.

2. Tencent Cloud TDSQL for MySQL

Tencent Cloud’s self-developed distributed MySQL. Its core advantages are “financial-grade stability” and “legacy project adaptation,” with significant implementations in the finance, government, and enterprise sectors.

Core advantages: Automatic sharding, distributed strong transactions, and active-active across different locations; compatible with the MySQL protocol, resulting in low migration costs for PHP projects; provides comprehensive monitoring, backup, and ops tools, suitable for enterprises that do not want to invest too much ops manpower; supports domestic technology adaptation, suitable for government and enterprise projects.

3. Huawei Cloud GaussDB(for MySQL)

Huawei Cloud’s distributed MySQL. Its core advantages are “separated storage and compute” and “ultra-large-scale data support,” suitable for scenarios with extremely large data volumes and extremely high concurrency.

Core advantages: Supports PB-level data storage, automatic sharding, and elastic scaling; compatible with the MySQL protocol for seamless PHP integration; provides financial-grade high availability, suitable for core business implementation; strong Huawei Cloud ecosystem integration, suitable for enterprises using Huawei Cloud.

4. AWS Aurora MySQL Distributed Scaling

Amazon Cloud’s distributed MySQL. Its core advantages are “overseas deployment” and “native MySQL compatibility,” suitable for overseas businesses and enterprises needing to integrate with the AWS ecosystem.

Core advantages: Fully compatible with native MySQL, PHP projects can connect directly; supports elastic scaling with underlying automatic sharding; wide coverage of overseas nodes, suitable for businesses deployed overseas; provides comprehensive monitoring and ops tools.

(3) Production Selection Comparison Table (Compare Directly for Quick Decisions)

To help you choose quickly, I have compiled a comparison table covering core dimensions. You can refer to it directly for production environments:

Database	Ownership/Form	MySQL Compatibility	Automatic Sharding	Distributed Transactions	Ops Difficulty	Typical Best Scenario
TiDB (Open Source)	Open Source / Self-hosted	Extremely High (100% compatible with 5.7)	Fully Automatic	Strong ACID	Medium (suitable for small/medium teams)	Internet/PHP project migration, replacing sharding, structured data with hundreds of millions of rows
OceanBase (Open Source)	Open Source / Self-hosted	High (MySQL + partial Oracle compatibility)	Fully Automatic	Financial-grade strong consistency	High (suitable for dedicated ops)	Core accounting/payments/banking, financial-grade high availability scenarios
PolarDB-X (Open Source)	Open Source / Self-hosted	Extremely High	Fully Automatic	Strong	Medium	Alibaba ecosystem self-hosting, MySQL-compatible distributed scenarios
Alibaba Cloud PolarDB	Public Cloud Managed	Extremely High	Vendor shields details	Strong	Very Low (vendor managed)	Alibaba Cloud ecosystem, no-ops desired, hundreds of millions of rows scenarios
Tencent Cloud TDSQL for MySQL	Public Cloud Managed	Extremely High	Vendor shields details	Strong	Very Low	Tencent Cloud ecosystem, finance/government projects, legacy project migration
MyCat+MySQL (Traditional Solution)	Middleware Combination	Depends on sharding rules	Manual planning	Weak, many pitfalls	Extremely High	Reluctantly keeping legacy projects alive; not recommended for new projects

One-sentence selection conclusion (key takeaways, useful for both interviews and production):

• Traditional PHP web/internet businesses, wanting low-cost migration, self-learning implementation, or interview bonus points → TiDB (Open Source) is optimal;
• Core scenarios like financial accounting and payments, pursuing ultimate stability → OceanBase (Open Source) or Tencent Cloud TDSQL;
• Fully within the Alibaba Cloud ecosystem, don’t want to invest ops manpower, direct cloud implementation → Alibaba Cloud PolarDB;
• Overseas business, need to integrate with the AWS ecosystem → AWS Aurora MySQL;
• Legacy historical projects that cannot directly migrate to NewSQL → temporarily keep alive with MyCat+MySQL; prioritize NewSQL for new projects.

3. Core Q&A: After Adopting TiDB, Can We Stop Using ElasticSearch (ES)?

This was the question I struggled with most when considering TiDB. In many PHP projects, we use MySQL to store business data and ES for full-text search (like article or product search). If we adopt TiDB, can we eliminate ES and reduce the operational cost of maintaining an extra cluster? After in-depth research and practical verification, the answer is clear: TiDB cannot completely replace ES. The two have different positioning. Whether you need to keep ES depends on whether your business requires full-text search.

(1) First, Let’s Clarify: The Core Positioning of TiDB and ES is Completely Different

Let’s first clarify the core strengths of both, and we will see why they cannot replace each other.

1. ElasticSearch (ES) Core Strengths: Full-Text Search + Unstructured Data Processing

ES is essentially a “search engine,” not a database. Its core value is “fast retrieval,” especially full-text tokenized search, which TiDB cannot replace:

• Full-text tokenized search: Supports word segmentation for multiple languages like Chinese and English. For example, “PHP distributed database” can be split into three keywords: “PHP”, “distributed”, and “database”. When a user searches for any of these keywords, it can quickly match relevant content, and also supports highlighting and relevance ranking (e.g., higher relevance content ranks first);
• Log retrieval: For unstructured/semi-structured data like system logs, operation logs, and error logs, ES can quickly index and retrieve them, making it easy for us to troubleshoot issues (e.g., quickly locating a specific error log based on a keyword);
• Multi-dimensional free filtering: Supports free filtering across multiple fields and conditions. For example, during product search, users can filter simultaneously by price, category, sales volume, reviews, and other dimensions, with extremely fast retrieval speeds;
• Unstructured data processing: Supports searching unstructured data like text and images (requires plugins), a capability that TiDB completely lacks.

2. TiDB Core Strengths: Structured Transactional Data + Standard SQL Operations

TiDB is essentially a “distributed relational database.” Its core value is “storing and managing structured transactional data,” replacing MySQL, not ES:

• Structured data storage: Suitable for storing structured data like orders, users, balances, and transactions, ensuring strong data consistency and transactional integrity;
• Standard SQL operations: Supports all MySQL-supported SQL operations like JOINs, transactions, DDL, pagination, and sorting. The PHP business layer can operate using familiar methods directly;
• Support for hundreds of millions of records: Automatic sharding easily supports the storage and querying of structured data at the hundred-million and billion-row scale;
• Basic analytical capabilities: Through the TiFlash columnar storage engine, it can perform simple big data statistical analysis (like monthly order volume or user activity stats), but it cannot achieve full-text search like ES.

(2) Production Implementation Conclusion: Judge by Scenario, Don’t Blindly Keep or Remove ES

Combining common scenarios in PHP projects, we can judge based on two situations to avoid wasting resources and avoid pitfalls:

✅ Scenarios Where ES Can Be Taken Offline / Not Deployed (TiDB is Completely Sufficient)

If your business only involves “structured data queries” and has no full-text search requirements, then ES does not need to be deployed at all. TiDB + TiFlash can handle all requirements:

• User basic information queries: Precise condition queries based on user ID, phone number, username, etc. TiDB’s index query speed is completely sufficient;
• Order and bill queries: Queries based on order number, user ID, time range, etc., supporting pagination, sorting, and simple aggregation (like counting the total orders of a specific user);
• Standard backend reports: Such as sales reports and user reports. Through the TiFlash columnar storage engine, statistical analysis can be completed quickly without needing to sync data to ES or a data warehouse;
• Precise condition queries: For all scenarios that do not require Chinese word segmentation or fuzzy search, TiDB is fully capable, and its performance is better than ES (because ES’s retrieval requires processes like tokenization and inverted indexes, its latency is higher than TiDB’s precise queries).

❌ Scenarios Where ES Absolutely Cannot Be Replaced (Must Keep)

As long as your business has any of the following requirements, ES cannot be omitted; TiDB cannot replace it:

• Full-text tokenized search: Such as fuzzy search and keyword retrieval for blog articles, product titles/descriptions, and news content, requiring tokenization, highlighting, and relevance ranking;
• Log retrieval: Centralized retrieval and troubleshooting of system logs, operation logs, and error logs, especially in massive log scenarios (like tens of millions of logs per day);
• Multi-dimensional free filtering: Such as e-commerce product search, where users can freely filter by price, category, sales, reviews, brand, and other dimensions simultaneously, with fast retrieval speeds required;
• Unstructured text retrieval: Such as searching and analyzing unstructured text like user reviews and article content.

(3) Compromise Solution: Money-Saving Tips for Small and Medium Teams (No ES for Light Search)

If your business only has “light search” requirements (such as simple keyword fuzzy queries without the need for complex tokenization and relevance ranking) and you don’t want to maintain a bulky ES cluster, consider the following compromise solutions. They are lighter and easier to maintain than ES:

• TiDB + MySQL native full-text index: TiDB is compatible with MySQL’s full-text index feature. For simple Chinese tokenization and fuzzy queries (like fuzzy search on usernames or titles), it can be used as a stopgap, but its performance and features are far inferior to ES, making it suitable only for light requirements;
• Integrate a lightweight search engine: Such as Meilisearch or Typesense. These search engines are much lighter than ES, with simple deployment and low operational costs, supporting basic tokenization, highlighting, and fuzzy search. They are suitable for the light search needs of small and medium teams;
• Note: If you have heavy search requirements (like e-commerce product search or massive article retrieval), honestly stick with ES to avoid degrading the user experience.

4. Combining 14 Years of PHP Experience: Best Architecture Evolution Path (Production Implementation)

Drawing on my years of PHP development experience, I recommend a “minimalist, efficient, and easy to implement” architecture evolution path. It is suitable for most PHP projects, especially those migrating from traditional MySQL sharding:

(1) Old Architecture (Painful Version, Not Recommended for Continued Use)

PHP + Standalone MySQL → Data surge → Master-slave replication bottleneck → MyCat/Sharding-JDBC manual sharding → Modifying code, modifying SQL, countless cross-shard pitfalls, exploding operational costs

The problem with this architecture is that operations are complex, business development efficiency is low, and scaling is difficult. It is suitable only for temporarily keeping legacy projects alive; absolutely do not use it for new projects.

(2) New Minimalist Architecture (Recommended, Top Choice for Production Implementation)

Depending on whether the business has search requirements, it is divided into two solutions, both of which completely escape the troubles of manual sharding:

Solution 1: No Search Requirements (e.g., Backend Management Systems, Payment Systems)

PHP + TiDB → Handles all requirements

• No need to worry about sharding; it runs smoothly even when data grows to hundreds of millions of rows;
• PHP code and SQL statements require zero changes for smooth migration;
• TiFlash handles backend report statistics, eliminating the need to build a separate data warehouse;
• Simple operations; you only need to maintain the TiDB cluster, which is 10 times less hassle than sharding + middleware.

Solution 2: Search Requirements (e.g., E-commerce, Blogs, Content Platforms)

PHP + TiDB (Core Business Data) + Lightweight Search Engine / ES (Search Requirements)

• TiDB: Stores core structured data like orders, users, and balances, ensuring transactions and consistency;
• Lightweight search engine (Meilisearch): Handles light search requirements (like simple keyword search) with low operational costs;
• ES (Optional): Handles heavy search requirements (like product full-text search, log retrieval), deploying only necessary nodes to reduce operational costs;
• Data synchronization: Uses TiDB’s binlog to sync data requiring search to the search engine, achieving real-time data updates.

Advantages of this architecture: Core business is stable, search requirements are met, and operational costs are controllable. It perfectly fits the PHP ecosystem, and even small and medium teams can implement it easily.

5. Summary: The Ultimate Approach to PHP Backend Database Selection

As a 14-year PHP veteran, having endured the torture of MySQL sharding and deeply understanding the advantages of NewSQL, I have gained a clearer perspective on database selection. I want to share it here as a conclusion to this blog post:

1. For PHP projects, TiDB is currently the most suitable open-source NewSQL database—MySQL compatible, automatically sharded, zero manual maintenance, and requiring zero PHP code changes. It can thoroughly solve the pain points of sharding and support high concurrency with hundreds of millions of records. Whether for self-learning, interviews, or production implementation, it is the optimal solution;

2. Public cloud-managed versions (like Alibaba Cloud PolarDB, Tencent Cloud TDSQL) are suitable for enterprises that do not want to invest in ops manpower. They are out-of-the-box, guarantee stability, and are the top choice for enterprise production;

3. TiDB and ES are not an “either-or” relationship, but a “complementary” one—TiDB handles structured data storage, transactions, and basic analysis, while ES handles full-text search and unstructured data processing. Whether you need to keep ES depends on your business’s search requirements;

4. New projects should try to avoid manual sharding and prioritize NewSQL databases to reduce architectural complexity and operational costs. Legacy projects can migrate gradually: first move core large tables to TiDB, and then phase out sharding middleware;

5. The core principle of database selection: Fit business requirements, reduce development and operational costs, and ensure stability and scalability. Don’t blindly chase what’s “fancy”; what fits your business is the best.

Finally, I hope this article helps back-end developers who, like me, have been tortured by MySQL sharding. I also hope everyone can quickly grasp the core knowledge points of NewSQL, avoid detours in interviews and production selection, and completely say goodbye to the pain of manual sharding!