This article examines the root causes of MySQL deadlocks in online services, explains transaction and lock mechanisms such as UndoLog and RedoLog, analyzes real‑world cases, and presents practical strategies—including isolation level adjustments and pre‑delete checks—to avoid and resolve deadlock issues.
The article documents a MySQL 5.6.35 deadlock occurring during inventory deduction, explains how Index Merge caused inconsistent lock ordering across multiple indexes, and offers solutions such as forcing specific indexes, disabling Index Merge, adding composite indexes, or restructuring the SQL to prevent the deadlock.
This article provides an in‑depth guide to MySQL internals, covering the SQL execution process, server and storage engine layers, binlog/redo/undo logs, index structures, transaction isolation levels, lock types, MVCC implementation, buffer pool management, and practical optimization techniques.
This comprehensive guide details the essential redis.conf parameters—including daemonization, networking, memory limits, logging, slowlog, advanced data structure encodings, replication options, and persistence settings—providing clear explanations and recommended values for optimal Redis deployment.
When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.
This article provides a step‑by‑step guide to preparing the operating system, installing and configuring MySQL, tuning system parameters, and using a Bash one‑click deployment script to automate the setup and initialization of a MySQL 5.7.32 instance on Linux.
This article explains how to list MySQL databases, perform full, multiple‑database, single‑database, and table‑specific backups using mysqldump, and restore them correctly, highlighting the differences when using the --databases option and common pitfalls.
This article explains common I/O and CPU bottlenecks in databases, introduces horizontal and vertical sharding techniques, discusses tools, global primary‑key generation methods, Snowflake ID algorithm, migration challenges, and provides practical guidance on when and how to apply database partitioning to improve performance and scalability.
This article explains the functional and performance differences among SQL count(1), count(*), and count(column) expressions, including how they handle NULL values and when each method is most efficient, illustrated with a MySQL example.
This article provides a detailed Linux tutorial for installing MongoDB, disabling the firewall, extracting the package, setting environment paths, creating data and log directories, editing the configuration file, and launching the database both in foreground and as a background service.
This guide explains MySQL GTID concepts, how to enable GTID-based replication, configure the server, create replication users, initialize a slave, and verify replication using command-line examples and configuration steps, including transaction commit and rollback testing.
This article explains MySQL's built‑in replication mechanism, covering its purpose, supported replication formats, step‑by‑step master‑slave configuration, common topologies, troubleshooting techniques, and practical SQL commands for setting up and maintaining reliable data distribution and high availability.
This guide provides step‑by‑step Oracle commands for creating and dropping databases, configuring users and tablespaces, performing full and table‑level backups with expdp/impdp, handling version compatibility, and cleaning up residual files and sessions.
This article examines MySQL's recommendation to use auto_increment primary keys instead of UUID or random snowflake IDs by creating three tables, running insertion benchmarks with Spring Boot and JdbcTemplate, and analyzing how index structure affects performance and scalability.
This article demonstrates how to perform a full backup of a MySQL 8.0 database using mysqldump, drop and recreate the database, then restore it and apply incremental recovery with mysqlbinlog by specifying start and stop positions to recover data inserted after the backup.
This tutorial walks through installing MySQL 8.0 on two servers, enabling binary logging, creating a replication user, configuring master and slave settings, handling common errors, and verifying that master‑slave synchronization works correctly.
This article explains how JD’s JUST platform leverages the open‑source columnar database ClickHouse to store, query and analyze massive time‑series datasets, covering data modeling, lifecycle management, cluster architecture, write and query processes, scaling strategies and future enhancements.
This tutorial walks through high‑level Django ORM techniques—including aggregation, grouping, field‑to‑field (F) expressions, complex Q‑object queries, and dynamic query construction—providing clear code examples, SQL equivalents, and practical tips for backend developers working with Python and databases.
This article explains why SQL query optimization matters and presents five practical rules—return only needed rows, use the right indexes, avoid subqueries, replace OFFSET‑based pagination, and master the logical execution order—to help developers write faster, more efficient database queries across major relational systems.
This tutorial walks through extracting the MySQL 8.0.17 package, creating required directories and users, configuring my.cnf, initializing the data directory, setting up startup scripts, adding MySQL to the system PATH, starting the service, and securing the root password on a Linux host.
This article provides a comprehensive guide to MySQL replication, covering built‑in replication types, the three‑step replication workflow, detailed master‑slave configuration commands, topology options, cloning methods, binlog formats, replication files, filtering, and common error scenarios with solutions.
This article analyzes a MySQL master‑slave setup where a delayed replica experiences growing replication lag due to deadlocks caused by REPLACE INTO statements, examines the lock behavior, reproduces the issue in a test environment, and proposes configuration and coding mitigations.
This article presents a comprehensive collection of 52 practical SQL performance optimization techniques, covering indexing, query rewriting, use of EXISTS, avoiding full table scans, proper handling of NULLs, OR/IN clauses, temporary tables, storage engine choices, backup procedures, and other best practices to improve database efficiency.
The article outlines seven key drawbacks of running databases inside Docker containers—including data safety, performance, networking, statefulness, resource isolation, cloud platform incompatibility, and hardware requirements—while also suggesting scenarios where containerization may still be viable.
This article explains a real‑world MySQL GROUP_CONCAT truncation problem caused by the default length limit, demonstrates how it affected a doctor‑scheduling system, and provides two practical solutions: increasing the group_concat_max_len setting or adjusting the business logic to reduce concatenated data.
This article explains five common issues caused by NULL values in MySQL columns—data loss with COUNT, DISTINCT, and inequality queries, NULL‑induced NULL results in SUM leading to NPE, increased query complexity, and index behavior—while providing practical SQL solutions and best‑practice recommendations.
This article explains MySQL transaction fundamentals, covering ACID concepts, isolation levels, MVCC implementation, transaction start methods, and practical examples that illustrate how InnoDB ensures atomicity, consistency, isolation, and durability while avoiding common pitfalls such as dirty reads and long‑running transactions.
This article provides a comprehensive overview of database system design, covering its historical evolution, classification of relational and NoSQL databases, key architectural patterns, consistency models, indexing techniques, storage formats, compression methods, and practical considerations for selecting the right database solution.
The presentation details the design and implementation of JD’s Urban Spatio‑Temporal Data Engine (JUST), explaining its architecture, novel storage and indexing techniques, performance optimizations, experimental results, and real‑world applications such as pandemic contact tracing and hazardous‑material monitoring, while highlighting its academic impact.
The presentation details the design, implementation, and real‑world applications of the JD Urban Spatio‑Temporal Data Engine (JUST), a distributed, scalable database that handles massive, complex spatio‑temporal data with novel storage, indexing, and query techniques, demonstrating high performance and ease of use across smart‑city scenarios.
This article explains Redis’s memory eviction policies, expiration strategies, common cache problems such as penetration, breakdown, and avalanche, outlines deployment modes, compares RDB and AOF persistence, and lists typical business scenarios for using Redis as a high‑performance key‑value store.
This article provides a step‑by‑step guide on configuring the MySQL CDC connector for Flink, covering Maven and SQL client dependencies, MySQL user setup, connector options, table creation via SQL and Stream API, key features, common issues, and practical troubleshooting tips.
Learn how to design a simple book management database, define Django models for authors, publishers, books and their many‑to‑many relationships, and perform common ORM operations such as retrieving all records, filtering with conditions, using first(), get(), and inspecting the generated SQL.
This article demonstrates how to create two MySQL tables, populate them with sample data, and use various SELECT statements—including inner joins, filtered joins, and UNION operations—to retrieve combined information across the tables.
This guide walks through building a comprehensive MySQL monitoring platform using Prometheus and Grafana, covering exporter installation, configuration, key performance metrics such as replication health, query throughput, slow queries, connection limits, buffer pool usage, and provides ready‑made Grafana dashboards and alerting rules.
This article explains Redis persistence mechanisms, detailing how the in‑memory database can lose data on shutdown and how the two main durability options—RDB snapshots and AOF append‑only logs—work, their configuration commands, their advantages, disadvantages, and guidance on choosing the right approach for production use.
This guide walks through downloading and installing Percona XtraBackup on a CentOS server, then demonstrates how to create full backups, remove timestamps, inspect backup contents, and execute incremental backups with detailed command examples and explanations.
This article presents a thorough overview of MySQL performance tuning, covering index usage, query rewriting, SELECT statement ordering, DML batch operations, hint directives, and table design recommendations to help developers write efficient SQL for large‑scale systems.
This article explores common data migration scenarios such as sharding, storage engine changes, and system switches, and outlines practical approaches for bulk and incremental migration, validation techniques, cut‑over strategies, and ID management to ensure reliable, low‑risk database transitions.
ebatis is an Elasticsearch ORM framework that simplifies query building by providing a MyBatis-like interface, eliminating the need for manual DSL construction and enabling seamless version upgrades.
This article explains the different index mechanisms used by MySQL (MyISAM and InnoDB) and Lucene, compares them with Elasticsearch's inverted index, and discusses how these structures affect storage, memory usage, and query performance.
This article presents five practical rules for writing efficient SQL queries—including selecting only needed rows, using proper indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑set pagination, and mastering the logical execution order of SQL clauses—to help developers and DBAs improve query performance across major relational databases.
This article provides a thorough overview of Redis, covering its eight core data types, essential commands, practical usage examples, and advanced features such as geospatial indexes, hyperloglog, and bitmap operations, making it a valuable reference for developers and interview preparation.
Even when a MySQL query uses an index, it can still become a slow query; this article explains index structures, explains why full‑index scans and poor selectivity cause performance issues, and presents step‑by‑step optimization techniques such as index condition pushdown, virtual columns, and better filtering.
This article explains MySQL’s logical architecture, query processing steps, caching mechanisms, index structures, and provides practical performance‑tuning advice—including schema design, index creation, query rewriting, and pagination—helping readers grasp the underlying principles and apply effective optimizations in real‑world workloads.
This article introduces MyFlash, an open‑source MySQL flashback utility, explains its limitations, provides step‑by‑step installation commands, details the available command‑line options for specifying databases, tables, time ranges and SQL types, and demonstrates a complete rollback test case with binlog parsing and execution.
This guide walks through Redis’s eight core data structures—String, List, Set, Hash, Sorted Set, Geospatial, HyperLogLog, and Bitmap—detailing their commands, practical usage scenarios, and example interactions to help developers ace interview questions and optimize NoSQL solutions.
This article explains the two forms of SQL CASE expressions, demonstrates how to use CASE for concise data aggregation and conditional updates, and provides practical examples with customer and order tables to simplify queries and avoid pitfalls.
This article reviews five widely used cache read/write strategies—including simple write‑DB‑then‑cache, Cache‑Aside, Write/Read‑Through, Write‑Back, and LRU eviction—explaining their workflows, advantages, disadvantages, and suitability for different production scenarios.
This article explains the concept of streaming queries, introduces MyBatis's Cursor interface, demonstrates how to use it in Spring MVC controllers, and provides three concrete solutions—using SqlSessionFactory, TransactionTemplate, and @Transactional—to keep the database connection open and avoid cursor‑closed errors.
This guide demonstrates how to install PyMySQL, connect Python to a MySQL database, create tables, perform CRUD operations, manage transactions, and handle errors, providing complete code examples and explanations for each step.
This article explains a real‑world case where a newly added mapping rule didn't appear immediately due to master‑slave replication lag, walks through the evolution of database architectures from single‑node to read‑write separation, and presents several practical strategies to mitigate consistency issues caused by replication delay.
This article explains the concept of streaming queries, introduces MyBatis's Cursor interface, demonstrates common pitfalls with closed connections, and provides three practical solutions—using SqlSessionFactory, TransactionTemplate, or @Transactional—to safely perform large‑scale data retrieval in Spring applications.
This article provides a step‑by‑step guide for manually building a six‑node Redis cluster on macOS, detailing environment setup, configuration changes, node startup, slot allocation, and converting master nodes to slaves, with screenshots and command examples.
This article outlines a comprehensive process for identifying root causes of performance bottlenecks in production business systems, covering hardware, database, middleware, JVM settings, code inefficiencies, and monitoring tools, and provides practical optimization techniques for each layer.
This article explains the mechanisms of Redis master‑slave data synchronization—including full and partial resynchronization—details the internal workings of Redis clustering, slot assignment, automatic node detection, and failover recovery, and provides practical insights for building reliable Redis deployments.
This guide explains how to manually back up a MySQL database using mysqldump, import the backup file, and set up a Bash script with crontab for daily automated backups, including handling of MySQL event tables and path configuration.
Redis, an in‑memory C‑language database, offers ultra‑fast read/write speeds, rich data structures, persistence, clustering, and multi‑threaded I/O, making it ideal for distributed caching, locks, queues, and more; this guide explains its fundamentals, comparisons with Memcached, data types, eviction policies, and operational best practices.
This article introduces Redis's eight fundamental data types—String, List, Set, Hash, Sorted Set, Geospatial, HyperLogLog, and Bitmap—explaining their characteristics, typical use‑cases, and providing concrete command‑line examples that demonstrate creation, manipulation, and querying of each type.
The article explains why idempotent operations are essential in backend and financial systems, describes the mathematical concept, and presents practical techniques such as unique indexes, token validation, pessimistic and optimistic locking, distributed locks, state‑machine design, and API patterns with concrete SQL examples.
This article explains Redis's cache expiration policies—including timed, periodic, and lazy eviction—details the internal structure and parsing of RDB files, and describes the AOF persistence mechanism with its write‑ahead logging, synchronization options, and rewrite process, providing a comprehensive view of Redis data durability.
This article explains the concept of streaming queries, introduces MyBatis's Cursor interface, demonstrates common pitfalls when the database connection closes prematurely, and provides three practical solutions—using SqlSessionFactory, TransactionTemplate, or @Transactional—to keep the connection open for efficient data retrieval.
This article explains the concept of streaming queries in MyBatis, introduces the Cursor interface, demonstrates common pitfalls with connection closure, and provides three practical solutions—using SqlSessionFactory, TransactionTemplate, or @Transactional—to safely retrieve large result sets without exhausting memory.
This article outlines MySQL Group Replication’s key features, fault-detection mechanism, limitations, required environment, and step-by-step deployment scripts, providing a practical guide for setting up a high-availability MySQL cluster on CentOS using the provided shell scripts and configuration files.
This article explains the real JDBC transaction methods, clarifies common misconceptions about transaction states in MyBatis, details the MyBatis Transaction and TransactionFactory interfaces, and explores special scenarios such as auto‑commit false, missing commit or close, with concrete code examples.
This article introduces Redis as a high‑performance, in‑memory NoSQL database, explains why learning it is valuable, provides step‑by‑step installation instructions, and presents essential commands for keys, strings, lists, sets, sorted sets, and hashes with practical code examples.
This article presents five practical rules for writing efficient SQL queries—including using precise WHERE clauses, creating appropriate indexes, avoiding costly subqueries, replacing OFFSET‑based pagination with key‑based limits, and understanding the logical execution order of SQL clauses—to help developers improve database performance across major relational systems.
This article explains Redis's two persistence mechanisms—AOF (Append Only File) and RDB (snapshot)—detailing their concepts, write‑back strategies, rewrite process, performance trade‑offs, snapshot handling, copy‑on‑write technique, incremental snapshots, and the benefits of combining both approaches.
This article provides an extensive overview of MySQL, covering database creation, renaming, and deletion, table creation and alteration, column and index management, data queries, inserts, updates, deletes, operator usage, view handling, functions, stored procedures, triggers, sequence generation, user management, and permission configuration, with practical examples and code snippets.
The article details a MySQL server startup failure caused by insufficient memory, shows command outputs and error logs, and explains how reducing the innodb_buffer_pool_size to 64 MB in the my.cnf configuration resolves the issue on a 2 GB Alibaba Cloud instance.
This article provides a comprehensive overview of database high‑availability techniques—including DB2 HADR, HACMP clustering, SQL and Q replication, storage‑layer disaster recovery, and DPF considerations—explaining their features, suitable scenarios, and how they can be combined to achieve robust end‑to‑end resilience.
The article compares five data synchronization approaches—periodic Logstash pulls, synchronous dual writes, asynchronous dual writes with MQ, Canal-based binlog streaming, and a Canal‑MQ hybrid—detailing their architectures, advantages, drawbacks, and suitable scenarios for integrating databases with Elasticsearch.
This article compiles a series of technical interview questions and answers covering MySQL query best practices, index optimization, B+‑tree storage, binlog recovery, master‑slave replication, design pattern usage in Laravel, a PHP binary‑search insertion algorithm, transport‑layer protocols, TCP three‑handshake, and concurrency control strategies.
This article explains how MyBatis loads its global configuration, parses mapper files, builds SqlSessionFactory and SqlSession, and executes SQL statements through a series of internal components such as Executor, StatementHandler, and caches, illustrated with detailed code snippets.
This curriculum outlines a three‑stage live online program covering front‑end technologies, core PHP and MySQL development, and integrated full‑stack projects with frameworks like ThinkPHP6 and Laravel, plus optional electives and practical deployment and interview preparation for enterprise‑level applications.
The article examines MySQL pagination performance on a large order_history table, presenting various pagination methods—including simple LIMIT, subquery, ID range, and temporary table techniques—along with detailed test results that show how query speed varies with record count and offset.
The article explains why using COUNT(*) for existence checks is inefficient, proposes using SELECT 1 with LIMIT 1 in SQL and a null‑check in Java, provides code examples, and highlights the performance improvements of this optimization.
This article explains MySQL 8’s newly supported CHECK constraints, outlines usage restrictions, and provides step‑by‑step examples—including simple age validation and complex gender‑age logic—showing how to create, test, alter, and drop constraints while highlighting practical considerations.
This article explains how to diagnose MySQL client connection failures by checking whether the mysqld process is running, verifying network communication settings such as skip‑networking and bind‑address, and examining authentication or password issues via the error log.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
The article explains how ThinkPHP’s query method works with raw SQL, distinguishes between execute for writes and query for reads, details MySQL master‑slave replication considerations, and describes the framework’s database connection initialization, including read/write separation logic and related code examples.
This article provides a comprehensive overview of the relational model and SQL, covering fundamental concepts, set‑based operations such as SELECT, WHERE, GROUP BY, UNION, and various JOIN types, along with practical code examples and explanations of integrity constraints and DML statements.
MySQL startup failures are typically caused by either insufficient system resource access (such as file permissions or security policies) or incorrect configuration parameters, and can be diagnosed by checking permissions, security policies, and the order and content of MySQL option files.
This article explains the fundamentals of single- and multi-data-source transactions, reviews common distributed-transaction solutions such as 2PC, 3PC, TCC, transaction-status tables and message-queue based eventual consistency, and then details Seata’s AT mode implementation, including its workflow, undo-log mechanism, and isolation handling.
This article provides a detailed walkthrough of MySQL protocol analysis using Wireshark, covering SSL disabling, packet capture commands, step‑by‑step examination of handshake, authentication, database selection, query execution packets, and an in‑depth look at related source‑code functions and command enums.
This article introduces HBase’s column‑oriented architecture, explains the roles of Master, RegionServer, and Zookeeper, and provides detailed environment preparation and installation commands for setting up an HBase cluster on Hadoop.
This article explains the functional and performance differences among MySQL's count(1), count(*), and count(column) functions, illustrates how NULL handling varies, and provides a concrete example demonstrating their results and execution efficiency under different table schemas.
In his 2020 talk, Prof. Zhou Xuan outlined the evolution of database systems, highlighted ECNU’s research on distributed transactions, hardware‑aware modular designs, HTAP and system decoupling, and argued that future databases will be increasingly modular, hardware‑adapted, and driven by close academia‑industry collaboration.
This article explains how to prevent duplicate order numbers in ERP systems under high concurrency by creating a dedicated table and a MySQL stored procedure that generates, validates, inserts, and returns a uniquely formatted order identifier.
This guide explains seven critical Linux operating‑system settings—memory overcommit, swappiness, Transparent Huge Pages, OOM killer, NTP synchronization, ulimit, and TCP backlog—that together ensure Redis runs reliably and at peak speed.
This article explains why GTID‑based replication is preferred for synchronizing data from four regional factories to an IDC, and provides a step‑by‑step guide—including data export, GTID configuration, master‑slave channel setup, adding a new slave, and important precautions—for successful multi‑source MySQL replication.
This article explains why a MySQL query that uses an index can still become a slow query, explores the difference between index usage and execution time, and presents practical optimization techniques such as improving selectivity, reducing back‑table lookups, using index condition push‑down, and leveraging virtual columns.
This guide walks you through installing the Go MongoDB driver, establishing connections, and performing full CRUD operations—including inserting single and multiple documents, querying, updating, and deleting records—while also showing how to retrieve server status and explaining BSON handling in Go.
This article explains how MySQL's CASE WHEN, IF, and IFNULL functions can be used to implement conditional logic in SQL queries, providing syntax, examples, and combinations with aggregate functions for common data‑analysis scenarios.
This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.
This article provides a comprehensive guide to advanced Redis operations, covering pipeline usage, transaction handling, publish/subscribe mechanisms, key migration techniques, custom command implementation with Jedis, and efficient key scanning methods, complete with code examples and performance comparisons.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This article explains the structure of MySQL ROW‑mode binlogs, demonstrates how to modify binlog events with Python to recover deleted rows, locate oversized transactions, and split large transactions into smaller ones, providing practical scripts and examples for advanced database manipulation.
