MySQL
Deployment Approaches
| Approach | Scenarios | Pros | Cons |
|---|---|---|---|
| Standalone | Small applications, test environments | Simple to deploy, easy to manage | No high availability; weaker performance and scalability |
| Primary-replica replication | Read-heavy applications, such as e-commerce and forums | Enables read/write splitting, backup, and recovery | Replica lag, data synchronization issues |
| Clustered | High-concurrency, large-scale scenarios, such as finance and telecom | High availability, high performance, good scalability | Complex to deploy and manage; needs specialist support |
| Sharded | Scenarios with very large data volumes or heavy read/write pressure, such as e-commerce and social | Horizontal scaling, improved database performance | Sharding strategy and data routing need technical support; hard to maintain and manage |
Configuration Requirements
| Approach | Memory | CPU | Disk | Concurrency | Response target |
|---|---|---|---|---|---|
| Standalone | 8GB or more | 4 cores or more | SSD or SAS disk | Below 100 QPS | 99% of requests should respond within 500ms |
| Primary-replica replication | Primary: 16GB or more Replica: 8GB or more |
Primary: 8 cores or more Replica: 4 cores or more |
SSD or SAS disk | Read: below 500 QPS Write: below 100 QPS |
99% of requests should respond within 500ms |
| Clustered | 64GB or more | 32 cores or more | SSD or SAS disk | Read: several thousand QPS or more Write: several hundred QPS or more |
99% of requests should respond within 100ms |
| Sharded | Memory and CPU depend on data volume | Depends on data volume | SSD or SAS disk | Read: several thousand QPS or more Write: several hundred QPS or more |
99% of requests should respond within 100ms |
Storage Engines
| Engine | Supported index types | Scenarios |
|---|---|---|
| InnoDB | Primary key, unique, ordinary, full-text, spatial | Suited to transaction processing, supports row-level locking, high data reliability; suited to OLTP systems and similar. |
| MyISAM | Primary key, unique, ordinary, full-text | Suited to applications with relatively few writes; no transaction or row-level locking support, relatively lower data reliability; suited to OLAP systems and similar. |
| Memory | Primary key, hash | Suited to applications needing fast read/write, low latency, and high concurrency. Data is stored in memory and is not persistent; suited to caching and similar. |
| Cluster | Primary key, hash, spatial | Suited to applications needing high availability and scalability; supports data sharding and parallel queries; suited to large-scale distributed systems and similar. |
Sharding
When MySQL handles large data volumes it may run into problems such as an oversized single table or declining query performance. To solve these, sharding is usually required.
| Scenario | Description |
|---|---|
| Oversized single table | When one table reaches millions, tens of millions, or even hundreds of millions of rows, query performance on that table may decline and the system slow down. The table then needs to be split, spreading the data across multiple tables. |
| Uneven data distribution | When data is unevenly distributed, some nodes may be overloaded, affecting the performance and stability of the whole system. The data then needs to be sharded across multiple nodes to balance the load. |
| Read/write splitting | When an application has many reads and few writes, separating reads from writes can improve performance. The primary and replica are separated: the primary handles writes, the replica handles reads. |
| Globally unique IDs | In a distributed system, globally unique IDs must be guaranteed. This can be done with a distributed ID generator, or by sharding so that IDs are spread across multiple tables, reducing the data volume of any single table. |
Competitor Analysis
| Competitor | Description | Differences | Pros | Cons | Scenarios |
|---|---|---|---|---|---|
| PostgreSQL | An open-source relational database management system. Compared with MySQL it emphasizes data integrity and consistency more. | More emphasis on integrity and consistency; supports advanced features such as stored procedures and triggers. | Good integrity and consistency; supports advanced features. | Relatively weaker performance; steeper learning curve. | Scenarios needing guaranteed integrity and consistency, such as finance. |
| Oracle | A commercial relational database management system with rich functionality and high availability. | Feature-rich, with high availability. | Powerful; supports advanced features and large-scale data processing. | Expensive; steep learning curve. | Enterprise applications needing high availability and large-scale data processing. |
| Microsoft SQL Server | A commercial relational database management system that integrates seamlessly with Windows Server and other Microsoft products. | Seamless integration with Windows Server and other Microsoft products. | Easy to install and manage; supports advanced features such as data warehousing and reporting. | Relatively weaker performance; expensive. | Enterprise applications in Windows environments. |
| MongoDB | An open-source non-relational database management system that uses a document storage format rather than tables. | Non-relational, using a document storage format. | Supports high availability and horizontal scaling; flexible data model. | No transaction support; performance below MySQL. | Scenarios needing high availability and a flexible data model, such as large internet applications. |
| Redis | An open-source key-value store. | Stores data as key-value pairs; supports high concurrency and high performance. | Excellent read/write performance; supports advanced features such as pub/sub and Lua scripting. | Simple data model, no complex queries; data volume limited by memory size. | High-concurrency, high-performance, low-latency scenarios such as caching and session management. |
Overall, MySQL performs well in performance, stability, reliability, and community support, and is a popular database management system. Different competitors nonetheless have their own characteristics and scenarios, and the choice should follow the specific business need. For example, PostgreSQL suits scenarios needing guaranteed integrity and consistency, while Oracle suits scenarios needing high availability and large-scale data processing.
powered by ChatGPT