This guide presents a comprehensive set of MySQL conventions—including engine choice, character set, naming, table and column design, indexing, and SQL usage—tailored for high‑concurrency, large‑scale internet services, explaining the rationale behind each rule to boost performance and maintainability.
This article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete reasons and code examples showing how to replace NULL with meaningful defaults for more reliable database design.
This article explains how to design a flexible approval workflow with serial, parallel, conditional, and mixed processes, defines the required database tables, shows UI mock‑ups, and provides Java Spring code for handling approval actions, including auto‑pass, delegation, and rule‑based field visibility.
This article walks through the complete design and implementation of a permission module for the Mall e‑commerce project, covering functional design, database schema, API specifications, and key technical choices such as Spring Security, JWT, and Redis‑based AOP optimization.
The article explains the differences between data schemas, data structures, and data models, proposes a systematic naming approach, and outlines a five‑schema architecture—including business, view, logical, deployment, and physical schemas—while addressing terminology challenges and normalization processes.
This article explains how to design a scalable multi‑account login system, covering self‑built phone‑number authentication, optimized password‑less flows, third‑party integrations such as Weibo and WeChat, a refactored user‑basic and authorization schema, and the steps for implementing one‑click carrier‑based login.
This article explores practical methods for performing fuzzy searches on encrypted phone numbers, covering in‑memory caching, database decryption functions, segment‑based storage, and adding dedicated fuzzy‑search fields to balance security, performance, and scalability.
Starting with MySQL 8.0.30, the Generated Invisible Primary Keys (GIPKs) feature automatically adds an invisible bigint auto‑increment column named my_row_id when a table lacks an explicit primary key, simplifying schema design, easing DBA‑developer conflicts, and offering flexible handling in MGR and replication environments, while noting its limitations and usage commands.
This article compares UUID and auto‑increment primary keys in MySQL, explaining how each is generated, their advantages and drawbacks, performance implications, and practical guidance on selecting the right key type for different business scenarios.
This article explains why a single MySQL table should stay below about twenty million rows by examining primary‑key limits, InnoDB data‑page structure, B+‑tree storage calculations, and provides a practical recommendation based on row size and fragmentation.
This guide compiles proven security, design, naming, schema, field, index, SQL, behavior, and process standards for databases, validated by large internet companies to help avoid low‑level faults in high‑concurrency, high‑volume environments.
This article walks through the creation and step‑by‑step optimization of a MySQL service_log table, covering proper data‑type selection, index usage, NULL handling, DATETIME vs TIMESTAMP choices, and floating‑point considerations to achieve a lean, high‑performance schema.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This article outlines practical strategies for building a flexible multi‑account login architecture, covering traditional phone‑number/password registration, optimized password‑less flows, integration of third‑party providers such as Weibo, WeChat and QQ, a unified user‑base and authorization schema, and the implementation of carrier‑based one‑click authentication.
This article explains why MySQL joins and subqueries can become performance bottlenecks on large tables, recommends fetching data via indexed single‑table queries and merging in the application layer, outlines advantages, use cases, drawbacks of joins, and offers practical alternatives and tuning tips.
This article explains why using NULL in MySQL tables hurts storage, index size, query correctness, and update performance, and shows concrete examples, benchmark data, and official documentation that justify preferring NOT NULL with sensible default values.
This article explains how to develop a simple yet powerful user permission management system for a blog using PHP, covering database schema design, user authentication, role and permission handling, and code examples for login, permission checks, and access control.
This article examines the design of a flexible multi‑account authentication system, covering native phone‑code login, optimized password‑optional flows, third‑party OAuth integration, database schema separation, and one‑click carrier‑based login to improve extensibility and user experience.
This guide outlines a five‑level learning path for MySQL, starting with installation and basic queries, progressing through proficient use, deep understanding of locks, indexes and transactions, advanced performance tuning, and finally product‑level redesign, while recommending essential books and resources.
The article outlines a data‑space architecture that employs a wide‑table design with dynamic columns and dedicated metadata tables, a metadata execution engine for business‑logic mapping, upgraded SQL parsing via Druid, MySQL‑proxy protocol handling, and distributed flow control using Redis and Zookeeper to enable scalable, multi‑tenant, low‑code and cloud‑native data management.
This article explains how MySQL stores and updates auto‑increment values, why gaps appear after failed inserts, unique‑key conflicts, transaction rollbacks or batch operations, and how different innodb_autoinc_lock_mode settings and MySQL 8.0 improvements can mitigate the issue.
This article explores how MySQL determines the maximum length of VARCHAR columns, showing that the limit depends on the chosen character set, nullability, and column count, and explains the underlying row storage format and how to handle oversized strings.
This article introduces time series data concepts, outlines the challenges of storing and analyzing high‑frequency data, and presents best‑practice guidelines for building MongoDB‑based time‑series applications, covering ingestion, read/write workloads, retention, security, and real‑world use cases.
This article presents a comprehensive, seven‑dimensional design for a flash‑sale (秒杀) system, covering business characteristics, overall architecture, peak‑limiting, Nginx configuration, page optimization, Redis clustering, message‑queue throttling, database design, captcha mechanisms, and key precautions for achieving high throughput and reliability.
The article explains the six SOLID design principles, emphasizes the importance of the Open‑Closed principle, and demonstrates how to achieve extensible database schemas and APIs using type fields, extendable JSON columns, bitmap role encoding, and strategy/template patterns with concrete Java code examples.
This article explains the concept of data models, why warehouse models need reconstruction, compares normative and dimensional modeling approaches, and provides a step‑by‑step guide—including information gathering, design, and implementation—to build efficient, maintainable data warehouse architectures.
This article explains what data models are, why model refactoring is necessary, compares normalized and dimensional data warehouse modeling approaches, and details a three‑step implementation process—including information research, model design, and model deployment—while highlighting best‑practice naming conventions and practical examples.
This article examines the architectural design of a Weibo-like feed system, covering storage schema, the trade‑offs between push (write‑amplification) and pull (read‑amplification) delivery models, caching techniques, and a hybrid approach for handling both small‑scale and large‑scale follower scenarios.
This article explains fundamental database design concepts, including candidate and primary keys, attributes, relations, ER diagram components, and detailed descriptions of the first, second, third, and BC normal forms with examples and how to resolve insertion, deletion, update, and redundancy anomalies.
This article explains Baidu's Duoli Bear permission system, detailing its business context, challenges of multi‑role and platform isolation, and the custom RBAC design that uses a tree‑structured menu, role‑based data rules, and business‑line isolation to manage access across diverse services.
When order_id is not the sharding key, querying a sharded order table can require scanning every shard, but three design patterns—data redundancy, an index table, and gene‑based sharding—enable fast single‑shard lookups while balancing storage and complexity.
This article presents a complete end‑to‑end design of a coupon system for a rental‑listing platform, covering business flow, database schema, activity management, coupon issuance, binding, user redemption, state‑machine implementation, caching strategies, stock deduction, redemption handling, and future scalability optimizations.
This article details the business flow, database schema, micro‑service architecture, caching strategies, state‑machine implementation, and concurrency handling for building a robust coupon system that enables merchants to bind coupons to rental listings and attract end‑users.
This article explains how to build a reliable e‑commerce inventory system by handling concurrent stock deductions, designing inventory and flow tables, using optimistic locking and transactions, and scaling the solution with read‑write splitting and Redis caching to meet high‑QPS demands.
This article explores how MySQL tables store data in 16 KB pages, demonstrates how to generate massive test data, explains the impact of primary‑key size and B+Tree depth on query performance, and derives the practical 20 million‑row recommendation for single‑table limits.
The article explains how to formally name data schemas, structures, and models, clarifies the differences between internal, external, logical, and physical schemas, and proposes a systematic hierarchy—including strategic and tactical schemas—to improve data architecture and reduce confusion in enterprise data design.
This article describes how to design an open API interface that issues time‑limited access tokens, defines the supporting database schema, implements token generation utilities, creates a token‑issuing endpoint, and secures all open‑API calls with a Spring MVC interceptor.
Learn the fundamentals of logical database design, including key concepts such as candidate and primary keys, attributes, ER diagram symbols, and detailed explanations of the first, second, third, and BC normal forms with practical e‑commerce examples to avoid data anomalies.
This article details the end‑to‑end design of a high‑performance coupon system for a rental platform, covering business flow, database schema, micro‑service interactions, caching strategies, state‑machine handling, and practical solutions for concurrency and consistency challenges.
This article examines why a MySQL InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies such as adjusting delete status, adding timestamp or auxiliary ID fields, using hash columns, and leveraging proper bulk‑insert techniques to enforce uniqueness effectively.
This article explains why a unique index in MySQL can still allow duplicate rows when indexed columns contain NULL, explores the challenges of adding unique indexes to logically deleted tables, and presents practical solutions such as incremental delete status, timestamps, extra IDs, hash fields, and proper batch insertion techniques.
This article presents a comprehensive set of MySQL best‑practice guidelines—including avoiding SELECT *, steering clear of OR in WHERE clauses, preferring numeric over string types, using VARCHAR instead of CHAR, limiting DELETE/UPDATE, leveraging proper JOIN types, and many other indexing and query‑execution tricks—to dramatically improve query speed, reduce resource consumption, and maintain healthy database design.
This article explains how to select and implement routing keys for database sharding in a food‑delivery order system, covering supported scenarios, single‑ and multi‑database strategies, hash‑based distribution for both user and merchant data, and practical flow diagrams to ensure balanced and efficient data access.
The article explains how to choose and implement routing keys after database sharding for an online food ordering system, covering supported scenarios, hash‑based routing strategies for both user and merchant sides, multi‑database/table selection, and practical data‑flow diagrams.
This article explains why a MySQL InnoDB table with a unique index can still contain duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies, including schema changes and alternative locking mechanisms, to enforce true uniqueness.
This article explains why solid database design is crucial for scalable applications and outlines ten best‑practice guidelines—including stakeholder input, choosing the right DB type, consistent naming, normalization, documentation, security, scalability, stored procedures, modeling time, and thorough testing.
This article explains why thoughtful database design is critical for scalable applications and outlines ten best‑practice guidelines—including stakeholder input, choosing the right DB type, consistent naming, normalization, documentation, security, scalability, stored procedures, modeling effort, and thorough testing—to help developers build robust, high‑performance data stores.
The article provides a comprehensive guide to relational database design using MySQL, covering ER modeling, normalization (BCNF and 3NF), storage engine selection, character set and data type choices, as well as index design principles illustrated with a student course selection example.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
This article explains relational database design using MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine and charset choices, data type selection, and index design, illustrated with examples and practical guidelines for building a student course selection system.
This article explores a common MySQL InnoDB pitfall where unique indexes fail to prevent duplicate rows, explains how NULL values and logical deletion affect uniqueness, and presents practical strategies—including composite keys, delete status counters, timestamps, hash fields, and Redis locks—to enforce true uniqueness.
This article shares practical guidelines for new backend projects, covering database design, module organization, scheduled task management, meaningful naming and comments, code robustness, clear error messages, and comprehensive self‑testing to help developers build maintainable and scalable systems.
The talk outlines IoT data classifications, requirements, and proposes a one‑stop storage product using multi‑model support, columnar formats, compute‑storage separation, tiered storage, and query optimization to achieve ten‑fold cost reduction and ten‑fold performance gains.
This article provides a comprehensive guide to relational database design with MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine selection, character set and data type choices, and index design principles, illustrated through a student‑course selection example.
This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.
This comprehensive guide covers MySQL fundamentals including the three normal forms, permission tables, ACID properties, index design principles, SQL statement categories, sharding, deadlock resolution, isolation anomalies, view characteristics, SQL lifecycle, primary key choices, performance troubleshooting, replication, GTID, backup tools, and backup planning.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+Tree fan‑out, showing why a table with 100 million rows can still query quickly when the index depth remains low.
This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.
This article explains how to design data architecture for microservice systems, covering the advantages of microservices, decoupling principles, lightweight APIs, DevOps integration, database per service versus shared databases, polyglot persistence, and why MongoDB is a suitable choice for scalable, dynamic, and sharded data storage.
This article compiles essential MySQL interview topics, covering normal forms, privilege tables, ACID properties, SQL statement categories, sharding, deadlocks, isolation anomalies, view characteristics, statement lifecycle, primary key choices, CPU troubleshooting, replication, GTID, backup tools, and backup planning strategies.
This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.
This article explores how to query sales data for multiple business units and selected products in MySQL, comparing loop queries, OR concatenation, mixed filtering, and SQL‑92 row‑value comparison, ultimately recommending the row‑comparison technique for efficiency and maintainability.
This article explains how to design a scalable multi‑account login architecture—including self‑built phone‑number registration, optimized password‑optional flows, third‑party integrations such as Weibo, and a unified user‑basic and user‑auth table schema—while also covering one‑click carrier‑based login and its advantages and drawbacks.
This guide explains the six relational database normal forms, focusing on the first three—1NF, 2NF, and 3NF—detailing their requirements, practical examples, visual illustrations, and the concept of denormalization to balance redundancy and query performance.
The article explains that the number in MySQL's INT definition (e.g., INT(1) or INT(4)) does not limit the column's numeric range, but only affects display width when combined with ZEROFILL, illustrating the concept with practical CREATE and INSERT examples.
This article explains how domain‑driven design shifts database modeling from a data‑structure‑first approach to a domain‑model‑centric process, covering persistence of domain objects, the conversion of the four classic relational relationships and inheritance into tables, and the distinct strategies required for NoSQL databases.
This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.
This article presents a comprehensive guide to building maintainable, extensible backend systems by covering coding conventions, branch naming, commit messages, comment standards, result‑wrapping patterns, database normalization, algorithmic improvements, concurrency handling, service layering, and practical code examples.
This article explains how MySQL allocates auto‑increment IDs, the differences between MySQL versions, the three InnoDB lock modes, scenarios that break ID continuity, what to do when IDs run out, and strategies to hide sequential IDs from malicious crawlers.
This article explains how to design a scalable multi‑account login system by describing self‑built phone‑number authentication, optimized password‑less registration, third‑party OAuth integration, database schema separation, and one‑click carrier‑based login, while highlighting advantages, drawbacks, and implementation details.
This article explains how to design a flexible multi‑account login system—including self‑built mobile‑number registration, optimized password‑less login, third‑party OAuth integration, database schema separation, and carrier‑based one‑click login—while weighing the advantages and drawbacks of each approach.
This article explains microservice fundamentals, outlines their advantages and technical traits, and provides practical guidance on data design, database selection, polyglot persistence, scaling strategies, and why MongoDB often fits microservice architectures best.
This article presents a step‑by‑step methodology for converting business domain models into robust data models, illustrated with a community group‑buying pre‑scheduling case, and discusses object identification, relationship definition, model refinement, and mapping to database structures to improve scalability and maintainability.
This article explains microservice fundamentals, their advantages, technical characteristics, and detailed data‑design considerations, comparing shared versus per‑service databases, polyglot persistence versus multi‑model databases, and demonstrates why MongoDB is a strong fit for scalable, resilient microservice architectures.
This article explains the shortcomings of static front‑end permission schemes in modern SPA architectures and presents a comprehensive, database‑driven user‑role‑page‑function‑API model that enables dynamic, fine‑grained access control for both frontend menus and backend endpoints.
This comprehensive guide covers MongoDB's core concepts—including schema‑free design, high availability, sharding, storage engine internals, indexing, and performance tuning—while providing practical code examples, configuration steps, and best‑practice tips for developers and architects looking to master the database in production environments.
This article analyses the business flow, unique characteristics, technical challenges, architectural principles, layer‑wise design, database strategies, high‑concurrency issues, cheating defenses, and data‑safety mechanisms required to build a reliable flash‑sale system capable of handling tens of thousands of simultaneous requests.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
The article explains why MySQL recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of strings, outlines space and query advantages, notes readability drawbacks, and demonstrates conversion using built‑in functions and Java utility code.
This article explores multiple MySQL techniques—triggers with a counter table, range partitioning, general tablespace limits, and application‑side buffering—to enforce a hard cap of 10,000 rows per table, discussing implementation steps, code examples, and the trade‑offs of each method.
This article compiles a comprehensive set of MySQL interview questions covering index types, the differences between MyISAM and InnoDB, the rationale for auto‑increment primary keys, sharding strategies, ACID properties, isolation levels, storage‑engine trade‑offs, normalization, index best practices, data types, performance tuning, locking mechanisms, B+Tree characteristics, and common SQL operations.
This article explains why storing IPv4 addresses as UNSIGNED INT in MySQL saves space and improves query performance, outlines the pros and cons, shows built‑in conversion functions, and provides Java code for converting between string and numeric representations.
The article explains why storing IPv4 addresses as 32‑bit unsigned integers in MySQL saves space and improves range queries, outlines the pros and cons, shows MySQL conversion functions, and provides Java code for converting between string and numeric IP representations.
The article explains the drawbacks of strict relational normalization, introduces the concept of anti‑normalization (controlled redundancy) to simplify queries, demonstrates it with a set of employee, department, salary tables and four typical query scenarios, and shows how denormalizing the schema can dramatically improve MySQL query performance.
The article describes how to design a MySQL table for product sales statistics, initialize large data sets, and compare several approaches—including loop queries, OR concatenation, mixed filtering, and row‑comparison techniques—ultimately selecting row‑comparison as the most efficient solution within MyBatis dynamic SQL constraints.
This article investigates the meaning of the number in parentheses for MySQL INT types, demonstrates through storage tests that INT(4) and INT(11) have identical storage size and range, and explains how the ZEROFILL attribute changes only the displayed width.
This article summarizes comprehensive MySQL index design guidelines, covering naming conventions, column selection criteria, index count control, handling of frequently updated columns, function and duplicate indexes, small‑table considerations, and index ordering to optimize query performance.
This article walks you through using Navicat for MySQL to create databases and tables, design table structures, and perform native SQL SELECT queries—including filtering, multiple conditions, and column selection—illustrated with step‑by‑step screenshots and code examples.
This article explains practical principles for designing scalable, stateless backend systems—choosing the right tools, using multiple servers, caching, rate limiting, dividing responsibilities, handling large data volumes, and providing concrete example architectures for projects of any size.
Starting with MySQL 8.0.23, columns can be marked INVISIBLE, causing them to be omitted from SELECT * queries unless explicitly referenced; this article explains the feature, demonstrates creation, inspection, DML considerations, schema modifications, backup behavior, and its impact on database design.
This article explains why data warehouses are critical for decision‑making, outlines the challenges of immature warehouses, and provides a step‑by‑step framework—including goal setting, technology selection, problem identification, domain modeling, layer design, modeling principles, and governance standards—to help teams build a robust, maintainable data warehouse.
This article outlines practical approaches for implementing multi‑account login, covering early‑stage username/password and phone‑based registration, detailed authentication flows, OAuth2 integration with third‑party providers, and comprehensive database schema designs to unify local and external user identities.
This article explains the concepts of data model, data dictionary, database schema, and ER diagram, clarifies their differences, and compares them across stages, purposes, detail levels, authors, users, tools, and forms to help readers grasp database design terminology.
This article presents an extensive collection of MySQL performance‑tuning techniques, covering index design, query rewriting, use of UNION and EXISTS, temporary tables, NOLOCK hints, join limits, backup strategies, storage engine choices, data‑type selection, and other practical tips to improve query efficiency and overall database reliability.
This article explains the concept, technical solutions, workflow diagrams, and database schema for implementing unified multi‑account login using username/password, mobile verification, and third‑party OAuth2 integrations, providing practical design guidance without detailed source code.
This article explains why setting MySQL columns to NOT NULL improves query optimization, reduces index complexity, saves storage space, and prevents unexpected behavior in calculations, while also detailing default values, handling of NULL in aggregates, and the internal row format differences between NULLable and NOT NULL columns.
This article explains why defining columns as NOT NULL in MySQL improves query optimization, reduces index complexity and storage overhead, and avoids unexpected behavior in aggregates and calculations, while also covering default values, row formats, and practical testing results.
This article outlines ten practical MySQL development guidelines—from always using InnoDB and simple data types to avoiding SELECT *, ENUM, and low‑cardinality indexes, while emphasizing proper naming, charset choices, NOT NULL constraints, and continuous SQL monitoring with Percona Toolkit.
This article outlines ten practical MySQL development guidelines—from using InnoDB tables and simple data types to avoiding SELECT * and unnecessary ENUMs—helping DBAs and developers collaborate more efficiently and write cleaner, faster SQL.
This article compiles a comprehensive set of MySQL interview questions covering index structures, B‑tree vs. hash trade‑offs, primary‑key and clustered‑index design, ACID properties, isolation levels, lock types, storage‑engine choices, data‑type considerations, binlog formats, pagination strategies, sharding, stored procedures, normalization, and MyBatis parameter syntax.
