Skip to content
Tagged articles

MySQL

500 articles · Page 2 of 5
liandk
liandk
Sep 6, 2026 · Databases

MySQL Transaction Isolation Levels & Lock Mechanisms: Dirty Reads, Phantom Reads, Deadlocks Explained

This comprehensive guide explains MySQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and lock mechanisms (table, row, gap, next-key locks), demonstrating how dirty reads, non-repeatable reads, and phantom reads occur, with practical deadlock prevention strategies and enterprise-level isolation level selection guidelines.

ACIDDeadlockLock Mechanisms
0 likes · 12 min read
MySQL Transaction Isolation Levels & Lock Mechanisms: Dirty Reads, Phantom Reads, Deadlocks Explained
Architect Chen
Architect Chen
Sep 6, 2026 · Databases

2026 MySQL DBA Command Reference: 9 Essential Commands Explained

This comprehensive guide details nine essential MySQL DBA commands — SHOW DATABASES, SHOW TABLE STATUS, SHOW FULL PROCESSLIST, SHOW ENGINE INNODB STATUS, SHOW VARIABLES, SHOW STATUS, EXPLAIN, SHOW INDEX, and SHOW CREATE TABLE — with syntax examples, key output fields, and practical troubleshooting scenarios for performance tuning and database administration.

DBAEXPLAINInnoDB
0 likes · 6 min read
2026 MySQL DBA Command Reference: 9 Essential Commands Explained
Cloud Architecture
Cloud Architecture
Sep 3, 2026 · Backend Development

TCC Is Not a Silver Bullet: Engineering Go Microservice Distributed Transactions with Performance Tuning

This article details the engineering implementation and performance optimization of TCC distributed transactions in Go microservices, using an order placement scenario with inventory locking and wallet freezing, covering data models, idempotent state machines, recovery workers, hotspot optimization, and observability.

ConcurrencyGoIdempotency
0 likes · 33 min read
TCC Is Not a Silver Bullet: Engineering Go Microservice Distributed Transactions with Performance Tuning
liandk
liandk
Sep 3, 2026 · Databases

MySQL Read-Write Separation Masterclass: Replication, Lag Solutions & HA Architecture

This comprehensive guide covers MySQL read-write separation from fundamentals to production implementation, including master-slave replication principles, replication lag causes and three enterprise-grade solutions, one-master-multi-slave architecture patterns, MHA automatic failover, Sharding-JDBC middleware configuration, and five critical production pitfalls to avoid.

High AvailabilityMHAMaster-Slave Replication
0 likes · 10 min read
MySQL Read-Write Separation Masterclass: Replication, Lag Solutions & HA Architecture
dbaplus Community
dbaplus Community
Sep 2, 2026 · Databases

Why One MySQL UPDATE Blocks and Another Doesn’t: Row vs. Gap Locks Explained

The article examines a MySQL experiment where three transactions (A, B, C) interact on the same row, showing why transaction B’s UPDATE on a primary‑key field proceeds without waiting while transaction C’s UPDATE of the primary key blocks, due to the interplay of row‑level locks, gap locks, and the internal delete‑plus‑insert transformation of indexed updates.

B* treeInnoDBLocks
0 likes · 9 min read
Why One MySQL UPDATE Blocks and Another Doesn’t: Row vs. Gap Locks Explained
Programmer1970
Programmer1970
Sep 2, 2026 · Backend Development

One TINYINT Field, Four Notification Switches: Bitmask Pattern in Practice

This article demonstrates how to store four independent notification channel switches (SMS, email, in-app, WeChat) in a single MySQL TINYINT column using bitwise operations, covering schema design, SQL queries, Java enum and utility classes, service and controller layers, MyBatis mapper, extensibility to N channels, and key considerations like indexing, concurrency, and readability.

JavaMyBatisMySQL
0 likes · 14 min read
One TINYINT Field, Four Notification Switches: Bitmask Pattern in Practice
Java Tech Enthusiast
Java Tech Enthusiast
Sep 2, 2026 · Backend Development

Recreating WeChat‑style Message Recall in Spring Boot: Why Deleting Isn’t Enough

The article walks through a complete Spring Boot implementation of WeChat‑like message recall, explaining why a simple DELETE is insufficient and detailing how to use a status flag, conditional UPDATE, WebSocket, Redis/MQ, and multi‑device session management to synchronize recall across all clients, even under concurrency and offline scenarios.

ConcurrencyMQMySQL
0 likes · 11 min read
Recreating WeChat‑style Message Recall in Spring Boot: Why Deleting Isn’t Enough
liandk
liandk
Sep 1, 2026 · Databases

Master MySQL Sharding with Sharding-JDBC: Sorting, Distributed IDs, and Scaling

This article explains what MySQL sharding and partitioning are, when they are necessary, outlines four splitting patterns, details four sharding key strategies, shows a production‑ready Sharding-JDBC configuration, and discusses five common distributed challenges with practical solutions.

MySQLScalingSharding-JDBC
0 likes · 10 min read
Master MySQL Sharding with Sharding-JDBC: Sorting, Distributed IDs, and Scaling
LuTiao Programming
LuTiao Programming
Aug 31, 2026 · Backend Development

Why Disabled Buttons Still Produce Duplicate Orders – Rebuilding Idempotency in Spring Boot

The article examines a common duplicate‑order bug caused by client retries after network timeouts, explains why @Transactional alone cannot guarantee idempotency, critiques simple Redis SETNX solutions, and presents a robust Spring Boot design that stores idempotency records together with order data in a single MySQL transaction, uses request fingerprints, unique keys, and owner tokens to safely replay or reject retries while handling concurrency and cleanup.

@TransactionalAPI designConcurrency
0 likes · 19 min read
Why Disabled Buttons Still Produce Duplicate Orders – Rebuilding Idempotency in Spring Boot
Niu Liu
Niu Liu
Aug 31, 2026 · R&D Management

Enterprise Codex Governance: Trustworthy Collection, Secure Access, Auditable Lifecycle

This article details the architecture of an enterprise governance platform for Codex AI coding assistants, covering local encrypted collection with 90ms hooks, server-side parsing with tenant isolation, tiered content access (L0–L3) requiring multi-party approval, and operational use cases from deployment health monitoring to employee offboarding with zero-residue verification.

AI coding assistantCodexKMS
0 likes · 17 min read
Enterprise Codex Governance: Trustworthy Collection, Secure Access, Auditable Lifecycle
Lobster Programming
Lobster Programming
Aug 31, 2026 · Databases

How MySQL Repeatable Read Solves Phantom Reads with MVCC and Next-Key Locks

This article explains how MySQL's Repeatable Read isolation level prevents phantom reads by combining MVCC snapshot reads for consistent views and Next-Key Locks (record + gap locks) to block inserts during current reads like UPDATE, detailing the ReadView mechanism, version chains, and a concrete phantom read scenario.

Isolation LevelMVCCMySQL
0 likes · 8 min read
How MySQL Repeatable Read Solves Phantom Reads with MVCC and Next-Key Locks
liandk
liandk
Aug 29, 2026 · Databases

Master MySQL Slow Query Log: Full Hands‑On Workflow to Capture, Analyze, and Fix Slow SQL

This guide explains what the MySQL slow query log records, why it is essential for production, when and how to enable it temporarily or permanently, key parameters to tune, how to simulate slow queries, interpret log fields, use mysqldumpslow for aggregation, follow a step‑by‑step optimization pipeline, and avoid common pitfalls.

MySQLdatabase optimizationmysqldumpslow
0 likes · 10 min read
Master MySQL Slow Query Log: Full Hands‑On Workflow to Capture, Analyze, and Fix Slow SQL
Java Architect Handbook
Java Architect Handbook
Aug 29, 2026 · Databases

Why Use ElasticSearch over MySQL? Key Differences for Interviews

The article explains why ElasticSearch excels at full‑text search and analytics while MySQL remains the reliable storage engine, compares their underlying data structures, outlines real‑time write behavior, lists scenarios where ES should not be used, and describes a production double‑store architecture with asynchronous sync.

B* treeElasticsearchInverted Index
0 likes · 14 min read
Why Use ElasticSearch over MySQL? Key Differences for Interviews
Xiaolin Talks Programming
Xiaolin Talks Programming
Aug 27, 2026 · Backend Development

Why Your Deep Pagination Is Slow: LIMIT OFFSET vs Keyset Pagination in Spring Boot

This article analyzes the performance bottleneck of LIMIT OFFSET deep pagination in MySQL with millions of rows, demonstrates EXPLAIN plan analysis, compares traditional pagination, subquery, covering index, and keyset pagination approaches, provides Spring Boot implementation examples for MyBatis-Plus and JPA, and benchmarks showing keyset pagination maintains constant response time regardless of page depth.

Cursor PaginationDeep PaginationLIMIT OFFSET
0 likes · 22 min read
Why Your Deep Pagination Is Slow: LIMIT OFFSET vs Keyset Pagination in Spring Boot
360 Smart Cloud
360 Smart Cloud
Aug 25, 2026 · Databases

MySQL-DTS: One-Stop MySQL/TiDB Sync with Online DDL Compatibility

MySQL-DTS unifies structure sync, full migration, and incremental replication in a single task for MySQL sources and MySQL/TiDB targets, featuring consistent snapshots, chunked parallelism, GTID/file+pos checkpoints, and built-in pt-osc/gh-ost compatibility for seamless online schema changes.

Database MigrationGTIDMySQL
0 likes · 14 min read
MySQL-DTS: One-Stop MySQL/TiDB Sync with Online DDL Compatibility
liandk
liandk
Aug 23, 2026 · Databases

MySQL Covering Index & Back‑Table Optimization: Hands‑On Guide to Eliminate Lookups

The article explains why MySQL back‑table lookups (回表) double I/O and degrade performance, defines covering indexes that retrieve all needed columns from a secondary index, shows how to identify back‑table cases via EXPLAIN, and provides step‑by‑step commands to create, test, and verify covering indexes for high‑frequency queries.

EXPLAINMySQLSQL performance
0 likes · 11 min read
MySQL Covering Index & Back‑Table Optimization: Hands‑On Guide to Eliminate Lookups
liandk
liandk
Aug 22, 2026 · Databases

Master MySQL MVCC: Snapshot vs Current Reads and Isolation Level Mechanics

This article explains MySQL InnoDB's MVCC mechanism, detailing how snapshot reads and current reads work, the hidden fields that drive versioning, the Read View rules for RC and RR isolation levels, and provides hands‑on SQL demos plus common pitfalls to avoid.

InnoDBMVCCMySQL
0 likes · 12 min read
Master MySQL MVCC: Snapshot vs Current Reads and Isolation Level Mechanics
ITPUB
ITPUB
Aug 21, 2026 · Industry Insights

12 Years of DTCC: How China’s Database Conference Evolved and United Engineers

The article chronicles the twelve‑year evolution of China’s Database Technology Conference (DTCC), showing how it transformed isolated DBA communities into a collaborative ecosystem, introduced new topics such as cloud and NoSQL, and enabled figures like 那海蓝蓝 to shape the nation’s database engineering landscape.

DTCCMySQLPostgreSQL
0 likes · 30 min read
12 Years of DTCC: How China’s Database Conference Evolved and United Engineers
Mike Chen Rui
Mike Chen Rui
Aug 21, 2026 · Databases

Mastering MySQL Sharding: Principles, Architecture, and Real‑World Implementation

The article explains why high‑traffic MySQL deployments hit performance limits, introduces the concepts of vertical and horizontal sharding, and provides a step‑by‑step guide—including necessity assessment, shard key selection, schema design, middleware integration, and data migration—using an e‑commerce order system as a concrete example.

Horizontal ShardingMySQLVertical Sharding
0 likes · 5 min read
Mastering MySQL Sharding: Principles, Architecture, and Real‑World Implementation
Mike Chen Rui
Mike Chen Rui
Aug 20, 2026 · Databases

Complete MySQL Master‑Slave Replication: Principles, Architecture, and Setup

MySQL master‑slave replication separates writes to the master and reads to one or more replicas, using binary logs to record changes; the article explains its core concepts, typical scenarios like read‑write splitting and high‑availability, and provides a step‑by‑step configuration guide covering server IDs, binlog settings, replication accounts, data initialization, GTID options, and status verification.

Binary LogGTIDMaster‑Slave
0 likes · 5 min read
Complete MySQL Master‑Slave Replication: Principles, Architecture, and Setup
liandk
liandk
Aug 20, 2026 · Databases

Master MySQL Locks: Row, Table, and Gap Lock Basics, Pitfalls & Live Code

Understanding MySQL’s lock types—table, row, and gap locks—reveals how they act as resource tokens to ensure data consistency under concurrency, while the article details their characteristics, appropriate and prohibited use cases, common pitfalls like lock degradation and deadlocks, and provides hands‑on SQL examples to reproduce and avoid these issues.

ConcurrencyDeadlockLocks
0 likes · 10 min read
Master MySQL Locks: Row, Table, and Gap Lock Basics, Pitfalls & Live Code
Ray's Galactic Tech
Ray's Galactic Tech
Aug 19, 2026 · Backend Development

Designing a 12‑State Payment System State Machine from Pending to Completed

This article presents a complete design for a 12‑state payment order state machine that handles high‑concurrency scenarios such as payment callbacks arriving after an order has been cancelled, using explicit state transitions, database CAS updates, Redis + DB idempotency, Outbox pattern and Kafka‑driven asynchronous processing to achieve reliable, auditable and compensatable order lifecycle management.

ConcurrencyIdempotencyKafka
0 likes · 39 min read
Designing a 12‑State Payment System State Machine from Pending to Completed
YiSu Grain
YiSu Grain
Aug 19, 2026 · Databases

Optimizing Slow Queries, Sharding and Cache Consistency for Appointment System

This article walks through a comprehensive case study of a provincial medical appointment platform, diagnosing slow‑query bottlenecks, proposing patient‑id + create_time composite indexes, designing read‑write separation with replication, selecting patient_id for horizontal sharding, and implementing cache‑aside strategies to ensure consistency while handling cache avalanche, penetration and thundering‑herd scenarios.

Cache ConsistencyMySQLRedis
0 likes · 30 min read
Optimizing Slow Queries, Sharding and Cache Consistency for Appointment System
MaGe Linux Operations
MaGe Linux Operations
Aug 18, 2026 · Databases

Common Causes and Fix Steps for MySQL Master‑Slave Replication Lag

This guide walks through why MySQL master‑slave replication lag occurs, the key metrics to monitor, a step‑by‑step troubleshooting flow, ten typical root causes, concrete remediation actions, verification methods, rollback plans, and production‑grade best practices for keeping replication latency near zero.

DiskIOLAGMySQL
0 likes · 29 min read
Common Causes and Fix Steps for MySQL Master‑Slave Replication Lag
Java Tech Workshop
Java Tech Workshop
Aug 18, 2026 · Backend Development

Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation

This article explains why indexes speed up queries by reducing disk I/O, dives into MySQL's page structure and B+Tree evolution, compares clustered and secondary indexes, clarifies composite index rules, lists common index‑misuse scenarios, and provides seven practical guidelines for designing efficient MySQL indexes.

B* treeComposite IndexDatabase Performance
0 likes · 20 min read
Beyond Adding Indexes: From Disk Pages to B+Tree – Master MySQL Index Design and Operation
Raymond Ops
Raymond Ops
Aug 17, 2026 · Databases

How to Diagnose and Fix MySQL Deadlocks Without Just Restarting the Service

This article explains why MySQL deadlocks occur in production, distinguishes them from simple lock waits, and provides a step‑by‑step guide—including enabling deadlock logging, analyzing InnoDB lock types, and applying four practical solutions such as distributed locks, unique constraints, isolation‑level changes, and SQL reordering—to reliably troubleshoot and prevent deadlocks.

DeadlockInnoDBMySQL
0 likes · 31 min read
How to Diagnose and Fix MySQL Deadlocks Without Just Restarting the Service
liandk
liandk
Aug 17, 2026 · Databases

MySQL Transaction Basics: ACID, Isolation Levels, and Practical Pitfall‑Avoiding Code

The article explains what a database transaction is, breaks down the ACID properties, details MySQL’s four isolation levels with their appropriate use‑cases and pitfalls, and provides step‑by‑step SQL and SpringBoot code to reproduce and resolve dirty reads, non‑repeatable reads, and phantom reads.

ACIDIsolation LevelMySQL
0 likes · 10 min read
MySQL Transaction Basics: ACID, Isolation Levels, and Practical Pitfall‑Avoiding Code
Yumin Fish Harvest
Yumin Fish Harvest
Aug 17, 2026 · Backend Development

How to Implement Distributed ID Generation with Segment Mode? A Double‑Buffer Issuer in Practice

The article analyses lock contention caused by per‑request ID generation, derives a segment‑based solution that batches IDs using a configurable step, defines a left‑closed/right‑open interval schema in MySQL, and builds a double‑buffer Java issuer with asynchronous pre‑loading, thorough concurrency handling, testing, and configuration guidelines.

ConcurrencyJavaMySQL
0 likes · 21 min read
How to Implement Distributed ID Generation with Segment Mode? A Double‑Buffer Issuer in Practice
IoT Full-Stack Technology
IoT Full-Stack Technology
Aug 17, 2026 · Databases

Does Adding an Index Lock the Table? JD Interview Deep Dive (Full Score Edition)

The article explains that whether adding an index locks a MySQL table depends on the version and DDL algorithm—MySQL 5.5 and earlier lock the whole table, 5.6‑5.7 use Online DDL with brief metadata locks, and 8.0+ can create secondary indexes instantly without locking, while primary, unique, and full‑text indexes always require locking.

COPYINPLACEINSTANT
0 likes · 10 min read
Does Adding an Index Lock the Table? JD Interview Deep Dive (Full Score Edition)
Java Tech Workshop
Java Tech Workshop
Aug 17, 2026 · Backend Development

How to Keep Redis and MySQL Consistent? Update DB First or Delete Cache First?

This article analyzes why cache and database can become inconsistent, compares four basic cache‑update patterns, explains the delayed double‑delete technique, shows how to use MQ for retrying cache deletions, and evaluates Canal binlog subscription as a zero‑intrusion solution for strong eventual consistency.

Backend ArchitectureCache AsideCache Consistency
0 likes · 28 min read
How to Keep Redis and MySQL Consistent? Update DB First or Delete Cache First?
Yumin Fish Harvest
Yumin Fish Harvest
Aug 16, 2026 · Databases

Implementing Continuous Invoice Numbers Using a Transactional Watermark Table

The article explains how to generate strictly sequential invoice numbers required by auditors by using a MySQL InnoDB watermark (water level) table combined with row locking and UPSERT within a single transaction, covering the workflow, concurrency handling, rollback versus void semantics, performance trade‑offs, and applicability limits.

MySQLTransactionaudit
0 likes · 16 min read
Implementing Continuous Invoice Numbers Using a Transactional Watermark Table
ITPUB
ITPUB
Aug 16, 2026 · Databases

How Oracle’s Grip Is Killing MySQL and Why the ‘Toy’ SQLite Is Rising

Oracle’s 2026 layoffs hit the MySQL team, community contributions fell 44% since 2017, while PostgreSQL climbs, and SQLite—once dismissed as a toy—now powers billions of devices and edge‑computing platforms, boosted by projects like LibSQL, Turso, Cloudflare D1 and Litestream, offering latency advantages over traditional servers.

Database TrendsEdge ComputingMySQL
0 likes · 8 min read
How Oracle’s Grip Is Killing MySQL and Why the ‘Toy’ SQLite Is Rising
ITPUB
ITPUB
Aug 14, 2026 · Databases

Still Using NULL? How It Can Quietly Slow Down Your Database

This article explains why MySQL discourages using NULL as a default column value, demonstrates how NULL affects indexing, comparisons, and aggregate functions, and shows through concrete examples that improper NULL handling can lead to unexpected query results and performance degradation.

AggregateBest PracticesIFNULL
0 likes · 13 min read
Still Using NULL? How It Can Quietly Slow Down Your Database
liandk
liandk
Aug 14, 2026 · Databases

Hands‑On MySQL Slow Query: Enable Logs, Analyze SQL, and Optimize Performance

The article explains what MySQL slow queries are, why they must be detected, when to enable slow‑query logging, step‑by‑step commands to configure the log, how to simulate and analyze problematic SQL with EXPLAIN, and practical optimization techniques—including index creation and Spring Boot integration—to eliminate performance bottlenecks.

MySQLPerformance OptimizationSQL
0 likes · 10 min read
Hands‑On MySQL Slow Query: Enable Logs, Analyze SQL, and Optimize Performance
IT Learning Made Simple
IT Learning Made Simple
Aug 13, 2026 · Databases

Databases: I'm Not Just Your Excel‑Savvy Cousin

This article demystifies databases by contrasting them with Excel, explains core concepts such as tables, rows, columns, primary and foreign keys, compares relational and NoSQL systems, introduces SQL CRUD operations, ACID properties, and provides a quick MySQL hands‑on guide, helping readers decide when to adopt a database.

ACIDMySQLNoSQL
0 likes · 10 min read
Databases: I'm Not Just Your Excel‑Savvy Cousin
Su San Talks Tech
Su San Talks Tech
Aug 13, 2026 · Databases

How to Safely Add a Column to a Tens‑Million‑Row MySQL Table? 6 Proven Methods

Adding a column to a MySQL table with tens of millions of rows can lock the table for minutes or even hours, disrupting services, so this article evaluates six practical approaches—including native online DDL, offline maintenance, PT‑OSC, logical migration with dual‑write, gh‑ost, and partition sliding‑window—detailing their mechanisms, trade‑offs, and suitable scenarios.

MySQLdatabase operationsgh-ost
0 likes · 13 min read
How to Safely Add a Column to a Tens‑Million‑Row MySQL Table? 6 Proven Methods
Code Farming
Code Farming
Aug 13, 2026 · Databases

Redis vs MySQL for Shopping Carts: Why I Chose the Option That Can Lose Data

The article explains that a shopping cart can tolerate loss of recent writes but not accumulated items, outlines the data model with seven fields and a unique key, compares client‑side storage options, details merge strategies for guest and logged‑in carts, and evaluates Redis, MySQL, and hybrid solutions with concrete trade‑offs.

Backend ArchitectureMySQLRedis
0 likes · 13 min read
Redis vs MySQL for Shopping Carts: Why I Chose the Option That Can Lose Data
Raymond Ops
Raymond Ops
Aug 11, 2026 · Databases

How to Diagnose MySQL Slow Queries: From Log Capture to Index Optimization

This guide walks MySQL operators through a complete slow‑query troubleshooting workflow—starting with enabling and analyzing the slow‑query log, using pt‑query‑digest and EXPLAIN to pinpoint index, SQL, schema, configuration or hardware bottlenecks, and then applying concrete optimizations such as proper indexing, cursor pagination, JOIN tuning, and server‑level parameter tweaks.

Buffer PoolEXPLAINJOIN tuning
0 likes · 33 min read
How to Diagnose MySQL Slow Queries: From Log Capture to Index Optimization

MySQL Large Table Optimization: 5 Strategies for 100M+ Row Queries

This article presents a five-step practical guide for optimizing MySQL queries on tables exceeding 100 million rows, covering index optimization, SQL rewriting, cursor-based pagination, table structure improvements including partitioning, and architectural upgrades like read-write separation and sharding, with concrete code examples and performance comparisons.

MySQLPartitioningindex optimization
0 likes · 12 min read
MySQL Large Table Optimization: 5 Strategies for 100M+ Row Queries
Lobster Programming
Lobster Programming
Aug 10, 2026 · Backend Development

Designing Efficient Read/Unread Tracking for One-on-One and Group Chats

The article examines how to implement read/unread status for single and group chats at scale, comparing a simple last‑read‑ID approach for one‑on‑one conversations with database, Redis hash, and bitmap‑watermark solutions for groups, and discusses their performance and memory trade‑offs.

BackendBitmapMySQL
0 likes · 7 min read
Designing Efficient Read/Unread Tracking for One-on-One and Group Chats
Raymond Ops
Raymond Ops
Aug 6, 2026 · Databases

Diagnosing and Eliminating MySQL Deadlocks in Production

This article explains how MySQL deadlocks arise, details the four necessary conditions, compares lock types, shows how to enable detailed deadlock logging, query lock metadata, interpret logs, and provides practical code‑level and configuration strategies to prevent and resolve common deadlock scenarios in production environments.

DeadlockInnoDBMySQL
0 likes · 20 min read
Diagnosing and Eliminating MySQL Deadlocks in Production
Coder Trainee
Coder Trainee
Aug 5, 2026 · Operations

How an Overnight Billing System Saved a Logistics Firm from Losing 3000 Yuan Daily

A logistics company struggled with mismatched label fees, manual reconciliation errors, and monthly profit loss, so the team deployed an automated recharge, label import, one‑click deduction, and reconciliation system that eliminated leakage, cut reconciliation time, and provided real‑time balance visibility.

MySQLOperationsSpring Boot
0 likes · 5 min read
How an Overnight Billing System Saved a Logistics Firm from Losing 3000 Yuan Daily
MaGe Linux Operations
MaGe Linux Operations
Aug 4, 2026 · Databases

Why 70% of System Outages Stem from SQL Performance: A MySQL Index Optimization Guide

The article walks through a systematic MySQL 8.0 index‑optimization workflow—starting with diagnosing slow queries and lock waits, validating execution plans with EXPLAIN ANALYZE, safely adding or dropping indexes using online DDL, handling pagination patterns, and verifying improvements via comprehensive metrics before and after.

EXPLAIN ANALYZEMySQLindex optimization
0 likes · 12 min read
Why 70% of System Outages Stem from SQL Performance: A MySQL Index Optimization Guide
System Architect Go
System Architect Go
Aug 4, 2026 · Databases

Quick PostgreSQL Guide for MySQL Users: Beyond Syntax Differences

This article walks MySQL developers through the essential architectural, schema, data‑type, SQL‑dialect, transaction, vacuum, indexing, and extension differences when moving to PostgreSQL, providing concrete examples, code snippets, and best‑practice recommendations to avoid common pitfalls.

MySQLPostgreSQLSQL
0 likes · 38 min read
Quick PostgreSQL Guide for MySQL Users: Beyond Syntax Differences
samdeepthink
samdeepthink
Aug 4, 2026 · Databases

Choosing the Best Composite Index for A = ?, B IN (...), ORDER BY C

The article explains why placing column C immediately after the equality column A in a composite index (A, C, B) avoids filesort and keeps performance stable regardless of how many values appear in the B IN list, outperforming other index orders such as (A, B, C).

Composite IndexFilesortIN Clause
0 likes · 10 min read
Choosing the Best Composite Index for A = ?, B IN (...), ORDER BY C
Raymond Ops
Raymond Ops
Aug 3, 2026 · Databases

How to Diagnose MySQL Slow Queries Without Relying on Blind Indexing

This guide walks through a systematic approach to uncovering and fixing MySQL slow queries, covering slow‑query‑log configuration, log analysis with mysqldumpslow and pt‑query‑digest, EXPLAIN‑based execution‑plan inspection, index design principles, SQL rewrites, configuration tuning, and ongoing monitoring to prevent performance regressions.

Database ConfigurationEXPLAINMySQL
0 likes · 27 min read
How to Diagnose MySQL Slow Queries Without Relying on Blind Indexing
samdeepthink
samdeepthink
Aug 3, 2026 · Backend Development

Is the Classic Update‑DB → Delete‑Cache → TTL Pattern Really the Best Way to Keep Cache Consistent?

The article examines why the common update‑database, delete‑cache, add‑TTL workflow can still produce permanent stale data under high concurrency, explains the underlying race conditions, and compares several alternative strategies—including delete‑first, binlog‑driven invalidation, and lease‑based approaches—to help engineers choose the most reliable and low‑complexity solution for cache consistency.

CacheCache AsideMySQL
0 likes · 19 min read
Is the Classic Update‑DB → Delete‑Cache → TTL Pattern Really the Best Way to Keep Cache Consistent?
Lobster Programming
Lobster Programming
Aug 3, 2026 · Databases

How to Eliminate MySQL Master‑Slave Lag: Parallel Replication, Read‑Routing, and Redis Marking

To address MySQL master‑slave replication lag, the article explains enabling parallel replication (setting slave_parallel_workers), routing reads to the master for latency‑sensitive operations, and using a Redis short‑term marker to direct recent writes to the master, while outlining configuration steps and trade‑offs.

Database LagMySQLRead Routing
0 likes · 6 min read
How to Eliminate MySQL Master‑Slave Lag: Parallel Replication, Read‑Routing, and Redis Marking
liandk
liandk
Aug 2, 2026 · Databases

Why Transaction Timeouts and Deadlocks Occur: Master MySQL Row, Table, Gap Locks

This article breaks down MySQL’s locking mechanisms—table, row, and gap locks—explaining their principles, performance trade‑offs, when they are triggered, how they relate to index usage, and provides practical deadlock avoidance techniques and a concise cheat‑sheet for common concurrency problems.

ConcurrencyDeadlockLocks
0 likes · 7 min read
Why Transaction Timeouts and Deadlocks Occur: Master MySQL Row, Table, Gap Locks
Ray's Galactic Tech
Ray's Galactic Tech
Aug 1, 2026 · Databases

Beyond CRUD: Full‑Scale Production Guide for MySQL 8.4 LTS

This article walks through a complete production‑grade view of MySQL 8.4 LTS, explaining how a chain of traffic spikes, connection‑pool exhaustion, long transactions and replication lag can cause an avalanche, and then detailing the five core modules, seven production mechanisms, architectural evolution steps, incident post‑mortems, and concrete configuration and code examples to build a resilient MySQL service.

High AvailabilityInnoDBMySQL
0 likes · 36 min read
Beyond CRUD: Full‑Scale Production Guide for MySQL 8.4 LTS
Raymond Ops
Raymond Ops
Aug 1, 2026 · Databases

Essential MySQL Backup and Recovery Process Every Ops Engineer Must Master

This comprehensive guide walks MySQL administrators through the fundamentals of backup and recovery, covering RPO/RTO concepts, tool comparisons (mysqldump, mydumper, xtrabackup), step‑by‑step scripts for full, incremental, and binlog backups, encryption, compression, troubleshooting, and best‑practice monitoring to ensure data safety and rapid restoration.

MySQLRecoverybackup
0 likes · 35 min read
Essential MySQL Backup and Recovery Process Every Ops Engineer Must Master
MaGe Linux Operations
MaGe Linux Operations
Aug 1, 2026 · Databases

MySQL Replication Lag Soars to 10 seconds? Three Parallel‑Replication Tricks to Fix It

When MySQL replication latency jumps from milliseconds to 10 seconds, blindly raising replica_parallel_workers won’t help; the article walks through diagnosing the delay, then applies three concrete parallel‑replication optimizations—enabling WRITESET on the source, configuring LOGICAL_CLOCK with appropriate applier workers, and optionally preserving commit order—while showing the required SQL commands, monitoring queries, and rollback steps.

GTIDLOGICAL_CLOCKMySQL
0 likes · 21 min read
MySQL Replication Lag Soars to 10 seconds? Three Parallel‑Replication Tricks to Fix It
Rain's Path to Java Mastery
Rain's Path to Java Mastery
Jul 30, 2026 · Databases

Running MySQL in Docker: Avoiding Data Loss, Performance Drops & HA Gaps

This article analyzes the risks of running MySQL in Docker—data loss, I/O performance degradation, networking overhead, and HA gaps—and provides production-ready solutions including Docker Compose configs, custom my.cnf, backup scripts, Kubernetes StatefulSet examples, and a troubleshooting matrix for interview preparation.

Backup StrategyContainerizationDocker
0 likes · 28 min read
Running MySQL in Docker: Avoiding Data Loss, Performance Drops & HA Gaps

MySQL Index Ignored? Implicit Type Conversion Causes Full Table Scan

A MySQL query on a varchar column with an unquoted numeric literal triggered implicit type conversion, forcing a full scan of 2 million rows instead of an index lookup; adding quotes cut latency from 19 seconds to 50 ms. The article explains the mechanism, lists four other common index-invalidation patterns, and provides a quick EXPLAIN-based diagnosis checklist.

EXPLAINMySQLdatabase tuning
0 likes · 7 min read
MySQL Index Ignored? Implicit Type Conversion Causes Full Table Scan
Rain's Path to Java Mastery
Rain's Path to Java Mastery
Jul 28, 2026 · Databases

MySQL Accidental Deletion Recovery: Binlog Flashback, Delayed Replicas & Prevention

This guide details a production-ready MySQL accidental deletion recovery process: immediate damage control via read locks and replication break, then binlog flashback using position-based extraction or binlog2sql for reverse SQL generation, plus delayed replica failover, with prevention strategies including sql_safe_updates, delayed replicas, and backup drills.

FlashbackMySQLbinlog
0 likes · 30 min read
MySQL Accidental Deletion Recovery: Binlog Flashback, Delayed Replicas & Prevention
Raymond Ops
Raymond Ops
Jul 28, 2026 · Databases

MySQL Disk Space Explodes: How Binary Logs Become the Hidden Culprit

MySQL servers can trigger alarming disk‑space warnings even when the data directory is small, because unchecked binary logs rapidly consume storage; this article explains the log’s purpose, why it grows, how to diagnose the issue, and provides step‑by‑step cleanup, configuration, replication, monitoring, and recovery best practices.

Binary LogMySQLbackup
0 likes · 26 min read
MySQL Disk Space Explodes: How Binary Logs Become the Hidden Culprit
Rain's Path to Java Mastery
Rain's Path to Java Mastery
Jul 27, 2026 · Databases

MySQL Table Design Guide: Naming Conventions, Index Optimization & Production SQL Templates

This comprehensive guide covers MySQL table creation best practices including naming conventions, field type selection, index design principles, constraint strategies, and solutions for large-table performance, JSON querying, and logical deletion conflicts, with production-ready SQL templates and interview-focused insights.

JSON queryingMySQLSQL performance
0 likes · 30 min read
MySQL Table Design Guide: Naming Conventions, Index Optimization & Production SQL Templates
Dabaoshi
Dabaoshi
Jul 27, 2026 · Databases

Why MySQL’s utf8 Is Not Real UTF‑8 and How to Avoid Garbled Text

The article explains the difference between character sets and encodings, shows that MySQL’s utf8 is actually an alias for utf8mb3, demonstrates how using the wrong charset or collation causes garbled data, and provides practical steps to consistently use utf8mb4 across versions and connections.

MySQLcharacter-setcollation
0 likes · 13 min read
Why MySQL’s utf8 Is Not Real UTF‑8 and How to Avoid Garbled Text
Raymond Ops
Raymond Ops
Jul 27, 2026 · Databases

What to Do First When MySQL Connections Are Maxed Out

This guide walks you through a complete emergency response, root‑cause analysis, and long‑term mitigation for MySQL connection‑limit exhaustion, covering Linux diagnostics, SQL commands, quick‑kill scripts, monitoring with Prometheus/Grafana, and best‑practice configuration of connection pools and max_connections.

MySQLconnection limitsmonitoring
0 likes · 37 min read
What to Do First When MySQL Connections Are Maxed Out
Dabaoshi
Dabaoshi
Jul 26, 2026 · Databases

When to Scale MySQL: From Single Instance to Distributed Architecture

The article explains why a single MySQL server eventually hits read, write, or availability limits, outlines a step‑by‑step evolution—from SQL tuning and caching to read‑write splitting, high‑availability setups, and finally vertical or horizontal sharding—while warning against premature distribution.

High AvailabilityMySQLScaling
0 likes · 15 min read
When to Scale MySQL: From Single Instance to Distributed Architecture
Dabaoshi
Dabaoshi
Jul 26, 2026 · Databases

From Client to Disk: The Complete Life Cycle of a MySQL Query

This article walks through every stage a MySQL statement undergoes—from client connection, authentication, and (now‑removed) query cache, through parsing, optimization, execution, and storage‑engine access—highlighting the roles of InnoDB vs MyISAM, logging mechanisms, and best‑practice table design tips.

InnoDBMyISAMMySQL
0 likes · 14 min read
From Client to Disk: The Complete Life Cycle of a MySQL Query
Dabaoshi
Dabaoshi
Jul 26, 2026 · Databases

Understanding MySQL Binlog and Its Two‑Phase Commit Process

This article explains what MySQL's binary log (binlog) is, how it differs from the redo log, the three binlog formats, the full execution flow of an UPDATE statement, why MySQL uses a two‑phase commit, and how crash recovery ensures data consistency across primary and replica servers.

MySQLTransactionTwo-Phase Commit
0 likes · 18 min read
Understanding MySQL Binlog and Its Two‑Phase Commit Process
Dabaoshi
Dabaoshi
Jul 26, 2026 · Databases

Practical MySQL SQL Optimization: How to Locate and Rewrite Slow Queries

The article explains how to make MySQL do less work by reducing scanned rows, avoiding back‑and‑forth lookups with covering indexes, and minimizing sorting or temporary tables, while detailing common index pitfalls, deep pagination tricks, COUNT usage, ORDER BY/filesort handling, JOIN strategies, and step‑by‑step methods for finding and fixing slow SQL statements.

EXPLAINMySQLSQL optimization
0 likes · 17 min read
Practical MySQL SQL Optimization: How to Locate and Rewrite Slow Queries
LuTiao Programming
LuTiao Programming
Jul 25, 2026 · Backend Development

Why Delayed Double Delete Fails: A Hierarchical Java Design for MySQL‑Redis Consistency

The article analyzes why MySQL and Redis cannot guarantee strong consistency with simple cache‑aside patterns, explains the pitfalls of delayed double delete, and presents a tiered Java design—including transaction‑after‑commit deletion, retryable invalidation, CDC/Outbox pipelines, TTL safeguards, and multi‑level cache considerations—to achieve reliable cache consistency.

CDCJavaMySQL
0 likes · 22 min read
Why Delayed Double Delete Fails: A Hierarchical Java Design for MySQL‑Redis Consistency
Cloud Architecture
Cloud Architecture
Jul 25, 2026 · Backend Development

From MySQL to 10M QPS: A Full‑Stack Engineering Blueprint for High‑Throughput Transaction Systems

This white‑paper dissects why a monolithic MySQL‑based order service collapses under peak traffic and presents a layered, asynchronous architecture—using Redis for stock pre‑allocation, RocketMQ for transactional messaging, sharding, idempotency, and comprehensive observability—to reliably handle tens of millions of queries per second.

Backend DevelopmentMySQLRedis
0 likes · 34 min read
From MySQL to 10M QPS: A Full‑Stack Engineering Blueprint for High‑Throughput Transaction Systems
samdeepthink
samdeepthink
Jul 25, 2026 · Backend Development

Designing a Hundred‑Million‑Scale Like System: Graph Store + KV Approach

The article analyzes the requirements of a massive‑scale like feature, compares small‑scale MySQL implementations with the graph‑plus‑KV architectures used by ByteDance, Kuaishou and Xiaohongshu, and presents a practical hybrid solution based on Redis, MySQL and batch processing to handle hot content and super‑node challenges.

IdempotenceKV storeMySQL
0 likes · 19 min read
Designing a Hundred‑Million‑Scale Like System: Graph Store + KV Approach
Dabaoshi
Dabaoshi
Jul 23, 2026 · Databases

Understanding MySQL InnoDB Storage: From Data Pages to Tablespaces

This article walks through MySQL InnoDB's five‑level storage hierarchy—record, page, extent, segment, tablespace—explaining page internals, record chaining, page directories, headers, trailers, extent and segment management, and the system tablespace and data dictionary across MySQL 5.7 and 8.0.

InnoDBMySQLdata page
0 likes · 24 min read
Understanding MySQL InnoDB Storage: From Data Pages to Tablespaces
LuTiao Programming
LuTiao Programming
Jul 22, 2026 · Backend Development

Why Does Pagination Slow Down at 10 Million Records? Rethinking MySQL LIMIT in Java Backends

The article explains why deep pagination with large offsets becomes increasingly slow, why adding indexes often does not help, examines common “optimizations” like delayed joins, introduces cursor (keyset) pagination as a high‑performance alternative, and provides practical guidelines for designing pagination APIs in Java back‑ends at scale.

Cursor PaginationDeep PaginationJava
0 likes · 16 min read
Why Does Pagination Slow Down at 10 Million Records? Rethinking MySQL LIMIT in Java Backends
Dabaoshi
Dabaoshi
Jul 22, 2026 · Databases

Understanding MySQL Locks: From MVCC to Deadlock Prevention

This article explains why MySQL needs locks beyond MVCC snapshot reads, categorizes lock granularity, mode and design, details global, table and row‑level locks—including intention, gap, next‑key, and implicit locks—then compares pessimistic and optimistic locking, shows how to choose the right lock, and covers deadlock causes, detection, and best‑practice avoidance.

DeadlockInnoDBLocks
0 likes · 21 min read
Understanding MySQL Locks: From MVCC to Deadlock Prevention
Code Farming
Code Farming
Jul 22, 2026 · Backend Development

Designing a 5‑Layer Flash‑Sale System to Handle Millions of Requests

To survive a million concurrent flash‑sale clicks, the article breaks down a production‑grade, five‑layer architecture—CDN, load balancer, API gateway, Redis/Lua stock control, message‑queue order processing, and a state‑machine fallback—that filters traffic early, uses atomic operations, and decouples writes to keep database load to just a few QPS.

LuaMessage QueueMySQL
0 likes · 7 min read
Designing a 5‑Layer Flash‑Sale System to Handle Millions of Requests
Dabaoshi
Dabaoshi
Jul 22, 2026 · Databases

Why InnoDB Needs a Buffer Pool and How It Works

The article explains why InnoDB must cache disk pages in a Buffer Pool, details the pool's internal structure, eviction policies, dirty‑page flushing mechanisms, multi‑instance and chunk sizing for large workloads, and how to monitor its health with SHOW ENGINE INNODB STATUS.

Buffer PoolChunkInnoDB
0 likes · 13 min read
Why InnoDB Needs a Buffer Pool and How It Works
dbaplus Community
dbaplus Community
Jul 21, 2026 · Databases

Tired of Hand‑Crafted Backup Scripts? Meet Databasus – One Open‑Source Platform for All Major Databases

Databasus is an open‑source backup management platform that unifies PostgreSQL, MySQL, MariaDB and MongoDB backups with a web UI, offering logical, physical and incremental backups, automated scheduling, AES‑256‑GCM encryption, multi‑cloud storage, RBAC collaboration, and optional agents for secure, out‑bound connections.

AES-256-GCMDatabasusDocker
0 likes · 14 min read
Tired of Hand‑Crafted Backup Scripts? Meet Databasus – One Open‑Source Platform for All Major Databases
Dabaoshi
Dabaoshi
Jul 21, 2026 · Databases

Understanding MySQL EXPLAIN Execution Plans from Scratch

This article walks through MySQL's EXPLAIN output column by column, explaining id, table, select_type, type rankings, possible_keys, key, key_len, ref, rows, filtered, and Extra, while demonstrating each concept with concrete SQL examples, JSON‑formatted plans, and SHOW WARNINGS insights.

EXPLAINJSON formatMySQL
0 likes · 20 min read
Understanding MySQL EXPLAIN Execution Plans from Scratch
Dabaoshi
Dabaoshi
Jul 20, 2026 · Databases

Understanding InnoDB B+ Tree Indexes: Structure and Real‑World Use Cases

The article explains how InnoDB implements indexes as B+ trees, detailing their evolution from simple page directories, the differences between clustered and secondary indexes, the costs of indexing, and the specific query patterns and scenarios where such indexes are effective.

B* treeInnoDBMySQL
0 likes · 14 min read
Understanding InnoDB B+ Tree Indexes: Structure and Real‑World Use Cases
Cloud Architecture
Cloud Architecture
Jul 20, 2026 · Databases

Designing MySQL for Millions of QPS: From Single Server to Distributed Architecture

The article walks through a real‑world order system that spikes to 300,000 QPS, explaining why the original single‑node MySQL design fails, and detailing a step‑by‑step evolution—index tuning, transaction fixes, read‑write splitting, vertical and horizontal sharding, plus data‑pipeline integration—to achieve stable low latency at massive scale.

MySQLdatabase scalinghigh QPS
0 likes · 20 min read
Designing MySQL for Millions of QPS: From Single Server to Distributed Architecture
Top Architect
Top Architect
Jul 20, 2026 · Databases

Why You Should Stop Using Snowflake for IDs and Try a Shorter MySQL Auto‑Increment Solution

The article examines the drawbacks of using Snowflake for generating short numeric user IDs, details a MySQL auto‑increment based approach, reveals deadlock problems with REPLACE INTO, evaluates alternative schemes, and presents a final sharding‑friendly short‑ID design that meets performance and usability requirements.

DeadlockMySQLSnowflake
0 likes · 14 min read
Why You Should Stop Using Snowflake for IDs and Try a Shorter MySQL Auto‑Increment Solution
dbaplus Community
dbaplus Community
Jul 19, 2026 · Databases

When Does MySQL Skip Writing to the Binlog? A Deep Dive into ROW‑Mode Logic and Controls

The article explains how MySQL determines whether a statement should be recorded in the binary log under ROW format, detailing the check_table_binlog_row_based function, the cached_row_logging_check flag, OPTION_BIN_LOG, and various scenarios—such as temporary tables, replication filters, no‑replicate tables, session settings, and internal operations—where binlog entries are omitted.

Binary LogMySQLROW format
0 likes · 11 min read
When Does MySQL Skip Writing to the Binlog? A Deep Dive into ROW‑Mode Logic and Controls
Raymond Ops
Raymond Ops
Jul 18, 2026 · Databases

MySQL Master‑Slave Replication: Core Architecture, GTID Setup, and Common Troubleshooting

This article provides a comprehensive, hands‑on guide to MySQL master‑slave replication, covering the underlying architecture, binlog formats, GTID and semi‑synchronous modes, detailed configuration steps, thread workflows, common failure scenarios with step‑by‑step diagnostics, and practical monitoring and failover scripts.

FailoverGTIDMySQL
0 likes · 38 min read
MySQL Master‑Slave Replication: Core Architecture, GTID Setup, and Common Troubleshooting
Linyb Geek Road
Linyb Geek Road
Jul 17, 2026 · Databases

8 Common SQL Mistakes That Are Destroying Your Database

The article identifies eight easy-to-overlook SQL habits—such as using SELECT *, applying functions on indexed columns, careless LIKE patterns, improper IN/OR logic, deep offset pagination, unconditional UPDATE/DELETE, excessive indexing, and ignoring execution plans—that can cause slow queries, lock tables, index failures, and even data loss, and provides concrete examples and safer alternatives.

MySQLSQLdatabase
0 likes · 12 min read
8 Common SQL Mistakes That Are Destroying Your Database
Java Architect Handbook
Java Architect Handbook
Jul 16, 2026 · Databases

Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Can Backfire

An in‑depth MySQL benchmark compares auto‑increment, UUID, and Snowflake‑style random long keys, showing how index structure, insert latency, and page fragmentation differ, and explains why auto‑increment keys usually outperform the others while also highlighting the security and lock‑contention drawbacks of sequential IDs.

InnoDB indexMySQLPrimary Key
0 likes · 12 min read
Why Using Snowflake IDs or UUIDs as MySQL Primary Keys Can Backfire
samdeepthink
samdeepthink
Jul 16, 2026 · Databases

Checkpoint‑Based Resumption for Billions‑Row Full Migration

When migrating tens of billions of rows, the article explains how a simple progress‑tracking table and failure‑log table enable automatic checkpoint‑based resumption, stateless execution, and dynamic batch tuning without restarting or rewriting code.

Batch ProcessingMySQLNacos
0 likes · 5 min read
Checkpoint‑Based Resumption for Billions‑Row Full Migration
Black & White Path
Black & White Path
Jul 16, 2026 · Information Security

Exploiting MySQL JDBC Deserialization: A Step‑by‑Step Analysis

The article walks through setting up a MySQL fake server, crafting a malicious JDBC URL with autoDeserialize and query interceptors, demonstrating how the MySQL JDBC driver automatically deserializes BLOB data via ObjectInputStream, and traces the call chain to show how arbitrary code can be executed during connection initialization.

JDBCMySQLdeserialization
0 likes · 7 min read
Exploiting MySQL JDBC Deserialization: A Step‑by‑Step Analysis