This article provides practical guidelines for designing efficient database schemas and optimizing SQL queries, covering row size limits, data type choices, index usage, common pitfalls in WHERE clauses, and best practices for temporary tables and transaction handling to improve overall database performance.
The article explains how exposing auto‑increment primary keys can leak business information, illustrates the danger with historical examples, and evaluates alternative ID generation strategies such as encoding, UUIDs, and Snowflake‑style distributed IDs, including performance comparisons in MySQL.
This guide outlines essential MongoDB naming conventions and planning strategies for databases and collections, highlighting common pitfalls, performance impacts of lock contention, and practical case studies on splitting databases and collections to improve scalability.
This article explains how VARCHAR's variable‑length storage and CHAR's fixed‑length storage differ in space usage, memory consumption during queries, and I/O performance, helping developers select the appropriate type for optimal MySQL efficiency.
This article explains how to define data elements, choose meaningful names, apply ISO‑11179 naming patterns, select appropriate SQL data types, and use domains, constraints, and default values to build clear, maintainable, and high‑performance relational database schemas.
This guide presents practical MySQL and Oracle development recommendations, covering table design, indexing, and SQL optimization techniques such as using appropriate data types, avoiding redundant indexes, employing efficient query patterns, and leveraging specific Oracle hints to improve performance and maintainability.
This article presents a comprehensive set of MySQL performance optimization techniques for web applications, covering query cache usage, EXPLAIN analysis, LIMIT 1, proper indexing, join optimization, avoiding ORDER BY RAND(), selective column retrieval, proper primary keys, ENUM usage, PROCEDURE ANALYSE, NOT NULL fields, prepared statements, unbuffered queries, IP storage as unsigned int, fixed‑length tables, vertical partitioning, batch DELETE/INSERT, column size reduction, storage engine selection, ORM usage, and careful handling of persistent connections.
This article examines the technical challenges and solutions involved in designing, deploying, and evolving a cross‑border e‑commerce platform, covering system architecture, network setup, database scaling, service modularization, and real‑world pitfalls encountered during rapid growth.
The article outlines the design requirements, sharding strategies, capacity planning, cost trade‑offs, and indexing techniques for building a highly available, scalable distributed storage solution for Weibo, focusing on storage‑layer decisions without involving caching or ID generation.
This article chronicles the complete redesign of the BugHound bug‑submission system into UFT, covering data modeling with MySQL, migration from PHP/MongoDB to Node.js, progressive frontend upgrades from jQuery to Angular, schedule‑table algorithms, Git collaboration tactics, and future open‑source plans.
This article explains essential database design principles—including normalization, denormalization, join avoidance, and various sharding techniques—while also covering scaling strategies such as vertical upgrades, horizontal partitioning, and the use of flash storage to boost performance.
This guide presents practical recommendations for MySQL and Oracle development, covering table design, appropriate data types, indexing best practices, and efficient SQL patterns to improve performance, maintainability, and security.
This article explains why using SELECT * is problematic and demonstrates a simple technique—adding a computed column that triggers a divide‑by‑zero error—to block SELECT * queries while still allowing column‑specific selections.
This article explains how to translate a classic SQL many‑to‑many schema for articles and tags into a MongoDB design, showing document structures and practical queries for listing tags, retrieving articles, and filtering by specific tags.
This article dissects the entire WeChat red‑packet ecosystem, exploring why users send and grab packets, the entry points, detailed UI screens, database schema, random‑distribution algorithms, caching strategies, and the full front‑end/back‑end interaction sequence.
This article explains why using a physical primary key for foreign‑key relationships, how to implement optimistic locking with a version column for concurrent updates, and when it’s safe to update records without any locking, providing practical SQL examples for reliable database design.
This article provides an extensive overview of MySQL, covering its history, strengths and limitations, recommended development and operational standards, replication and high‑availability strategies, backup and recovery methods, as well as detailed performance‑tuning techniques for InnoDB and alternative storage engines.
This article explains how variable‑length data structures are stored in databases, illustrates PostgreSQL's page and row formats, and compares the storage overhead of DEFAULT NULL versus DEFAULT '' fields, showing that NULL can reduce space dramatically when many columns are nullable.
CHAR stores fixed‑length strings padded with spaces, while VARCHAR stores variable‑length strings with a length byte, leading to different maximum sizes—255 characters for CHAR and up to 65,535 bytes for VARCHAR (about 21,589 UTF‑8 characters)—and distinct use cases such as short fixed fields versus space‑saving flexible fields.
This article distills the key takeaways from the book “50 Principles of High‑Scalability Websites,” presenting concise guidelines on avoiding over‑design, planning capacity, simplifying architecture, optimizing DNS and assets, leveraging horizontal scaling, proper database usage, caching, fault isolation, asynchronous messaging, and continuous learning to build robust, easily extensible web systems.
This article compiles practical discussions on database sharding and partitioning, covering criteria such as user ID, time, and hash, redundancy techniques, caching with Redis, query handling, pagination, consistency, and distributed transaction approaches for scalable backend systems.
