This article investigates how the MyBatis‑Plus saveOrUpdate method can trigger gap‑lock deadlocks in MySQL, explains the underlying concepts of deadlocks and gap locks, reproduces the issue with concrete SQL scripts, and proposes both risky and recommended mitigation strategies.
This article explains the differences between MySQL DDL and DML, describes how online DDL works—including copy and inplace algorithms, execution phases, common pitfalls, limitations, and recent MySQL 8.0 enhancements—providing practical guidance for safely altering large tables.
The article explains why unrestricted large‑table queries cause JVM Out‑Of‑Memory errors, analyzes the drawbacks of simple LIMIT pagination, and provides a MyBatis‑Plus based bulk‑execution utility with code examples for safe, efficient batch processing and multithreaded execution.
This article provides a comprehensive overview of Redis's ten data types—including strings, hashes, lists, sets, sorted sets, streams, hyperloglog, geospatial, bitmap, and bitfield—detailing their structures, encoding mechanisms, application scenarios, and underlying implementation details with code examples.
This article provides a systematic, interview‑focused overview of MySQL fundamentals—including normal forms, DML vs DDL, primary and foreign keys, storage engines, redo and binlog mechanisms, indexing strategies, transaction properties, MVCC, lock types, replication, and common performance pitfalls—helping candidates quickly master essential database knowledge for interview success.
This weekly newsletter curates top industry tech articles covering SQL deployment capabilities, MySQL replication issues, LLM‑to‑SQL benchmarks, connection limit errors, OceanBase tracing, and provides an open‑source SQL quality management platform update with development progress and upcoming plans.
The article explains the structural differences between B+ trees and skip lists, compares their read‑write performance in MySQL and Redis, and shows why MySQL prefers B+ trees while Redis adopts skip lists for its in‑memory ZSET implementation.
This guide outlines the most common production failures—including network, server, database, software, security, storage, configuration, and third‑party service issues—and provides step‑by‑step methods to detect, troubleshoot, and resolve each problem, helping maintain system stability and reliability.
This weekly digest curates top industry technical articles, featuring MySQL XA transaction crash safety, SQL optimization case study, MySQL 9.0 new features, database sharding origins, vector database workings, and a Chinese graph database product guide, offering readers concise insights into current database developments.
This article explains MySQL's binlog, its role in master‑slave replication, and how businesses can harness it for data heterogeneity, cache synchronization, and task dispatch, illustrating practical middleware designs that transform raw changes into valuable services.
CrateDB 5.3.2 introduces numerous fixes and enhancements, including corrected handling of empty partition tables, improved default clause errors for OBJECT columns, resolved ON CONFLICT insert issues, fixed LIKE/ILIKE pattern bugs, snapshot restoration problems, Azure repository errors, and various optimizer and interval function improvements.
Designing a time axis for HR systems involves choosing between an interval model that stores start and end timestamps and an effective‑date model that uses effective dates and sequences, each affecting query simplicity, maintenance effort, common pitfalls, and ultimately enabling accurate historical and future employee data analysis.
The article explains how MySQL auto‑increment primary keys, InnoDB invisible row_id, transaction Xid and trx_id, and thread identifiers behave when they reach their numeric limits, showing the resulting errors, data overwrites, and potential visibility bugs in long‑running instances.
This document introduces a lightweight Redis graphical management tool, outlines its Spring Boot backend and Vue frontend architecture, provides step‑by‑step installation instructions including Docker deployment, details recent feature enhancements such as multi‑user support and permission control, and mentions future plans for clustering and sentinel modes.
Database schema changes are among the riskiest development tasks, and this article reviews top migration tools—from basic CLI clients like mysql and psql, through popular GUIs such as phpMyAdmin and DBeaver, to modern GitOps/Database-as-Code solutions like Liquibase, Flyway, Sqitch, Atlas, Prisma, and Bytebase—highlighting their features, pricing, and collaboration capabilities.
This article explains why Redis offers transactions, introduces the five core transaction commands, demonstrates practical examples including WATCH and CAS optimistic locking, and discusses error handling and the reasons Redis does not support rollback.
This article details a November 2022 database outage caused by a sudden CPU and load surge, explains how the team diagnosed the issue, outlines the emergency steps taken, and shares practical SQL performance optimization recommendations to prevent similar incidents.
This article compares B+ trees and skip lists, explaining their structures, insertion and search complexities, and why MySQL chooses B+ trees for disk‑based indexing while Redis prefers skip lists for in‑memory sorted sets, highlighting trade‑offs in read/write performance and I/O costs.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
This article provides a comprehensive English guide to Redis’s ten core data types—including strings, hashes, lists, sets, sorted sets, streams, hyperloglog, bitmap, and bitfield—detailing their definitions, typical application scenarios, underlying storage mechanisms, conversion rules, and sample Redis commands with code examples.
The article investigates a MySQL 8.0 instance where many sessions were stuck in the executing state, analyzes InnoDB thread‑concurrency settings, stack traces and source code to reveal that exhausted InnoDB tickets caused nanosleep‑based blocking, and proposes configuration and SQL‑optimisation solutions.
This article translates and expands on a HikariCP wiki post, showing how dramatically reducing database connection pool size—from thousands to just a handful—can cut response times from around 100 ms to 3 ms, backed by Oracle and PostgreSQL benchmarks and a practical sizing formula.
This article explains MySQL’s various lock types—global, table, metadata, intention, and row‑level—and shows how SELECT statements acquire different locks under each isolation level, with examples for existing and missing rows, range scans, unique and non‑unique indexes, and full‑table scans.
This article shows how a concise 10‑line shell script, created with ChatGPT, can continuously capture MySQL QPS, TPS, and connection counts and append the metrics to a log file for performance monitoring.
This article introduces Redis HSET, covering its definition, suitable use cases, common commands for creating, reading, updating, and deleting hash fields, as well as the underlying encoding mechanisms (ziplist vs hashtable) and best practices, including the deprecation of HMSET.
This article explains how Cassandra’s newly added vector data type and ANN search capabilities empower AI developers to store, index, and query high‑dimensional embeddings at scale, enabling use cases such as image retrieval, recommendation, and large‑language‑model integration.
This article explains the true meaning of NoSQL, its theoretical foundation in the CAP theorem, the reasons why it excels in large‑scale and high‑concurrency scenarios, compares it with relational databases, and outlines typical use cases and trade‑offs.
Meituan’s high‑availability database system, built on a multi‑region Raft‑group architecture, addresses rapid instance growth and stringent availability by deploying three‑node HA cores, micro‑services, and MGR clusters with AZ‑ and region‑level disaster recovery, while employing multi‑channel fault detection, weighted election, and semi‑synchronous consistency mechanisms, and outlines future moves toward decentralized proxies and fully clustered designs.
After upgrading from MySQL 5.7 to 8.0, a client experienced a flood of slow queries despite low system load; the root cause was a change in the sys.innodb_lock_waits view that now relies on performance_schema.data_locks, whose large lock table holds a global mutex and blocks other transactions, a problem solved by adding an index to reduce lock volume.
This article explains how to reduce database pressure by counting likes and views in Redis, designing a hash‑based storage structure, synchronizing cached data to MySQL with Quartz, and marking articles as liked for each user, while also outlining current limitations.
Redis provides two primary persistence mechanisms—RDB snapshots that capture the dataset at specific moments and AOF write‑ahead logs that record every write command—along with a hybrid model combining both, each with distinct strategies, advantages, drawbacks, and configuration options to ensure data durability and fast recovery.
This guide explains how to use MyBatis dynamic SQL tags such as foreach, if, where, choose, trim, and selectKey, providing practical examples for conditional queries, batch inserts, updates, and reusable SQL fragments to write flexible and error‑free MySQL statements in Java applications.
This article explains how to use redis-benchmark for performance testing, compares single‑node and cluster throughput, and details Redis Cluster's hash‑slot architecture, including CRC16 hashing, slot allocation, scaling considerations, and operational nuances.
This article explains how to ensure idempotency in order services by using database transactions, unique request identifiers, and version control to prevent duplicate orders and solve the ABA problem, providing practical code examples and best practices for backend developers.
The article introduces Deno KV, a globally replicated, strongly consistent key‑value database that runs locally with SQLite or in Deno Deploy with FoundationDB, highlights its simple yet powerful JavaScript‑centric API, atomic transactions, consistency models, and provides practical code examples for developers.
This guide explains how to pinpoint the MySQL statements that use the most CPU by mapping OS thread IDs to MySQL sessions, querying performance_schema and information_schema, and reviewing the execution plan to identify optimization opportunities.
This article outlines comprehensive Redis development standards, covering key naming conventions, value design, lifecycle management, command usage guidelines, prohibited commands, batch operations, transaction considerations, and Lua scripting constraints, aiming to reduce common pitfalls and improve performance and maintainability.
This comprehensive guide covers MySQL basics such as join types, normal forms, data types, indexing strategies, lock mechanisms, transaction isolation levels, replication, sharding, and practical optimization techniques for handling large tables and high‑traffic workloads.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This guide walks you through MongoDB sharding fundamentals, step‑by‑step cluster setup, common failure diagnostics, and practical commands, enabling you to build, manage, and optimize a sharded MongoDB deployment for scalable storage and high‑performance applications.
This guide walks through a real‑world MySQL master‑slave sync failure caused by a corrupted relay log, explains the replication mechanics, shows how to analyze slave status, use GTID to skip the bad transaction, and fully restore synchronization with step‑by‑step commands and screenshots.
This comprehensive guide covers why Redis is fast, its underlying data structures, single‑threaded and epoll models, cache eviction policies, persistence mechanisms, high‑availability architecture, performance bottlenecks, distributed locking, cache avalanche/penetration strategies, data skew handling, and bitmap applications, providing essential knowledge for interview preparation.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains why correlated subqueries are costly, introduces the Apply (correlated join) operator, and presents a series of systematic transformation rules that convert scalar, existential, and aggregate subqueries into efficient join‑based plans while preserving SQL semantics.
MariaDB, the MySQL fork founded by Michael Widenius, has seen its NYSE‑listed shares tumble from $14.45 to around $1.30, faced a $104 million Series D round, now lacks cash for twelve months, cut 8% staff, and holds just 2.15% of the relational‑database market.
This article explains how KeyDB, a multithreaded fork of Redis, redesigns the thread model, connection handling, lock mechanisms, and introduces active‑replica features while staying fully compatible with the Redis API.
This article explains how SQL, built on relational algebra, became the standard analysis language for OLAP engines, covering its history, data models, syntax, functions, aggregation techniques, window functions, subqueries, and practical optimization considerations for modern data warehouses.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This weekly roundup curates the latest high‑quality technical shares on databases, featuring articles about PolarDB HTAP acceleration, MySQL datetime precision, new MySQL releases, ChatGPT as a database client, and the SQLE platform's recent enhancements and upcoming plans.
This article explains MySQL replication, covering its background, binlog fundamentals, replication formats, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world implementation details for high availability and data synchronization.
This article presents a comprehensive set of practical techniques—including batch processing, asynchronous execution, caching, pre‑processing, pooling, parallelization, indexing, transaction size reduction, code refactoring, and pagination optimization—to systematically reduce API latency and improve overall backend service efficiency.
This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.
The DTLE 4.23.04.0 release introduces numerous MySQL‑focused enhancements and bug fixes—including timestamp handling, UUID processing, transaction retry logic, UTF‑8 support, and API improvements—while providing download links, repository information, and recommended reading for users of this open‑source data transfer component.
This article presents a comprehensive set of backend interface performance optimization strategies—including batch processing, asynchronous execution, caching, pre‑processing, pooling, parallelism, indexing, transaction handling, program structure, pagination, SQL tuning, and lock granularity—to help developers reduce latency and improve scalability.
This article explains MySQL replication’s background, binlog formats, event types, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world deployment strategies such as HA components, middleware, remote binlog copying, and data‑transfer services, providing a comprehensive guide for building highly available and scalable MySQL infrastructures.
This article introduces Redis Sets as unordered unique string collections, explains their ideal use cases, and provides detailed walkthroughs of creation, query, update, and deletion commands—including SADD, SREM, SISMEMBER, SCARD, SMEMBERS, SSCAN, SINTER, SUNION, and SDIFF—along with underlying encoding mechanisms.
The SQLE 2.2304.0-pre1 release introduces a preview version of the SQL audit and quality control tool with detailed project information, enhanced suggestion features for audit results, and a concise list of new functionalities and release details for database users and administrators.
This article explains how the default PL/SQL delimiter '/' in OceanBase Oracle tenants conflicts with division operators, causing syntax errors, and demonstrates fixing the issue by changing the delimiter to '//' with detailed code examples and execution results.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
This article explains MySQL master‑slave replication, covering its architecture, binlog‑based replication process, causes and mitigation of replication lag, and strategies for master‑slave failover, helping readers grasp why and how to use replication for read/write separation, high availability, and backup.
The article explains the concepts, differences, and practical usage scenarios of local (partition‑aligned) and global indexes in OceanBase, providing SQL examples for queries with and without partition keys and guidance on creating unique indexes using either index type.
This article provides a comprehensive overview of Redis—including its architecture, common data structures, real‑world usage scenarios such as caching, counters, and distributed locks—followed by detailed QA test cases, common pitfalls like cache penetration, and practical mitigation strategies.
This article examines various strategies for maintaining data consistency between caches (such as Redis and local memory) and databases during double-write operations, evaluates their advantages and drawbacks, and proposes solutions like delayed double deletion, message‑queue compensation, and binlog‑based cache updates.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
Redis version 7.0.10 has been released, addressing a critical security vulnerability (CVE‑2023‑28425) in the MSETNX command, fixing several bugs including memory‑usage issues with large stream nodes and client reply handling, and providing updated release notes on GitHub.
This article reveals frequent SQL mistakes—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, premature filtering, and intermediate result push‑down—and provides concrete rewrite techniques that dramatically improve MySQL query performance.
This article introduces several SQL date‑time functions—including TO_TIMESTAMP, LOCALTIMESTAMP, NOW, CURRENT_TIMESTAMP, DATE_FORMAT, and UNIX_TIMESTAMP—explaining their syntax, parameters, behavior, and providing example queries with expected results for each function.
The article explains what OceanBase BUFFER (Queuing) tables are, why frequent bulk updates cause performance degradation, and provides three optimization methods—including binding execution plans, manual data transfer, and setting table_mode='queuing'—with concrete MySQL and Oracle examples and query scripts to monitor merges.
This article outlines the latest RocksDB release updates, including disabled checksum verification options, removed Lite and deprecated APIs, default shared library builds, new compression filter and MultiGetEntity APIs, and various bug fixes such as concurrency issues and async I/O error handling.
This article reviews the SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0, covering boolean literals, window functions, the FILTER clause, upsert syntax, column renaming, and related API changes, while comparing SQLite’s support to other major database systems.
SQLAlchemy 2.0.7 has been released, addressing a critical caching bug in custom operator expressions, fixing typing issues with composite(), adding the PostgreSQL CITEXT type, and improving dialect compatibility, with all changes back‑ported to earlier stable releases.
This article compiles a detailed interview guide covering TCP/UDP differences, three‑way handshake and four‑way termination, flow and congestion control, select/poll/epoll mechanisms, zero‑copy techniques, Redis advantages and persistence, MySQL MVCC and repeatable‑read semantics, as well as core C++ topics and common algorithm questions.
MySQL 8.0 introduces numerous enhancements including InnoDB engine defaults, atomic DDL, instant column addition, CTE support, UTF8MB4 default charset, Clone plugin, resource groups, role management, multi-valued and functional indexes, invisible indexes, descending indexes, SET_VAR syntax, persistent parameters, window functions, online DDL, and improved backup and binlog capabilities.
This article presents a comprehensive set of backend interface performance optimization techniques, including batch processing, asynchronous execution, caching, pre‑processing, pooling, parallelization, indexing, transaction handling, lock granularity, and code restructuring, aimed at reducing latency and improving system efficiency.
SelectDB shares its perspective on modern data analytics stacks, detailing the current challenges, the evolution of data architectures, and how Apache Doris enables a real‑time unified data foundation, while also reviewing Doris 1.2’s latest features, performance gains, and future roadmap.
This guide walks through the background of MySQL read/write splitting, records troubleshooting steps for an unexpected slave crash, explains the two splitting models, and provides detailed commands and configuration examples for using Keepalived to ensure high availability of the slave node.
SQLite’s 2018 updates bring powerful new SQL capabilities—including true/false literals, IS TRUE/FALSE predicates, window functions, FILTER clauses, UPSERT syntax, and column‑renaming—demonstrating that this lightweight, serverless database can handle modern workloads while remaining reliable and widely deployable.
The article explains Redis master‑slave asynchronous replication, describes how repeated full‑sync requests from slaves can cause a replication storm that overloads CPU, memory and network, and offers practical solutions such as limiting data size, adjusting buffer limits, and redesigning deployment topology.
The article examines why running MySQL in Docker containers often leads to data‑security, performance, state, and resource‑isolation problems, outlines practical mitigation strategies, and identifies scenarios where containerizing MySQL can still be viable.
This article explains why dual‑write consistency between MongoDB and a relational database is challenging, compares common misconceptions with cache usage, and provides practical patterns—including write ordering, retry mechanisms, and scheduled cleanup—to reliably synchronize core and non‑core data.
This article introduces KeyDB, a high‑performance Redis fork that adds multithreading, a redesigned thread model, a spin‑lock fastlock mechanism, and active‑replica capabilities, while remaining fully compatible with Redis protocols and offering up to double the throughput on the same hardware.
An online service experienced severe latency due to massive GAP times, leading to repeated OutOfMemoryErrors; by analyzing monitoring data, JVM dumps, and SQL queries, the team uncovered a massive userId array causing a 1 GB count query, then implemented request limits and JVM flags to prevent recurrence.
This article explains how Redis ensures data durability by using two persistence strategies—Append Only File (AOF) and Redis Database (RDB) snapshots—detailing their operation, configuration options, trade‑offs, rewrite processes, and how they handle expired keys and recovery.
This article presents sixteen typical Redis use cases—including caching, distributed locks, global IDs, counters, rate limiting, bitmaps, shopping carts, timelines, message queues, lotteries, likes, product tags, filtering, follow relationships, and ranking—each explained with practical code snippets and command examples.
This article presents a comprehensive set of MySQL performance guidelines, including avoiding SELECT *, preferring specific columns, eliminating OR in WHERE clauses, using numeric types, choosing VARCHAR over CHAR, applying proper JOINs, leveraging LIMIT, UNION ALL, composite indexes, and other practical tips to improve query efficiency and resource usage.
This article compiles fifteen typical scenarios where MySQL indexes become ineffective—covering composite index left‑most matching, SELECT *, arithmetic on indexed columns, functions, LIKE patterns, type conversion, OR/AND logic, range queries, ORDER BY, and optimizer decisions—providing example SQL, EXPLAIN output, and practical tips to help developers prevent costly full‑table scans.
This article examines why running MySQL in Docker containers often leads to data‑security risks, performance bottlenecks, state management issues, and resource‑isolation problems, while also outlining scenarios where containerization can still be viable and offering practical mitigation strategies.
This article walks through a hands‑on process for detecting problematic SQL statements, interpreting execution plans, and applying concrete optimizations such as index adjustments and data‑type fixes to dramatically improve query performance, illustrated with a real MySQL case study.
This article details a comprehensive investigation of a Java backend service suffering from severe OutOfMemoryError due to an unbounded userId list in a count query, describing monitoring findings, heap dump analysis, and practical mitigation steps including request limiting and JVM tuning.
To efficiently process massive MySQL result sets without exhausting JVM memory, this article explains three query strategies—regular pagination, stream-based retrieval using MyBatis cursors, and cursor-based fetching with configurable fetchSize—detailing their implementations, advantages, and practical considerations.
This article explains how InnoDB uses undo segment states for crash‑safe recovery, details the XA transaction protocol, describes bugs in earlier MySQL versions, and shows how MySQL 8.0.30 introduces a new undo state and revised prepare order to make external XA transactions crash‑safe, including code examples and recovery steps.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
This article explains Oracle database backup and recovery fundamentals, classifies failure types, compares logical and physical backup methods, introduces flashback technology, outlines complete and incomplete recovery, and provides practical RMAN scripts and scheduling tips for reliable data protection.
This article explains Redis's three persistence options—Append‑Only File (AOF), Redis DataBase snapshots (RDB), and the hybrid mode—detailing their inner workings, configuration strategies, performance trade‑offs, and how to choose the most reliable solution for a given workload.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause MySQL Next‑Key lock deadlocks, explains the underlying gap‑lock and row‑lock mechanisms, demonstrates practical experiments, and proposes strategies such as postponing locks or using primary‑key updates to avoid such deadlocks.
This article provides a comprehensive step‑by‑step tutorial on deploying OceanBase in a single‑node environment, covering resource unit, resource pool, and MySQL‑type tenant creation, modification, deletion, user management, and relevant system data dictionary tables, with full SQL command examples.
This article explains the logical execution sequence of a typical SQL query, detailing each phase from FROM/JOIN/WHERE through GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and illustrating the process with diagrams and code examples.
The article explains how to replace repetitive raw SQL and manual row scanning in Go by building a lightweight custom ORM that combines a chainable SQLBuilder for dynamic query construction with a reflection‑based Scanner that maps results to structs, also covering code generation and optional non‑reflection optimizations.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
This article examines eight frequent SQL anti‑patterns—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, premature data reduction, and intermediate result handling—explaining why they degrade performance and providing concrete rewrite strategies and code examples to dramatically speed up queries.
