This article explains the MySQL InnoDB undo log—its definition, role in transaction rollback and MVCC, when it is created, where and how it is stored across different MySQL versions, the internal tablespace and rollback‑segment structures, log types, and real‑world examples of its usage.
This article compares MySQL and HBase by examining their architectural designs, storage engines (B‑Tree vs LSM‑Tree), performance characteristics, ecosystem features such as TTL and multi‑versioning, and identifies scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
This tutorial explains how to embed Dataway—a DataQL‑based API configuration UI—into a Spring Boot application, covering Maven dependencies, property settings, database schema creation, datasource integration, Hasor module registration, enabling annotations, and step‑by‑step usage to publish and test APIs without writing traditional controller code.
This article explains what data consistency means for MySQL and Redis, analyzes inconsistency cases in both single‑threaded and concurrent environments, and proposes practical strategies—including read‑only and read‑write cache handling, message‑queue retries, binlog subscription, delayed double‑delete, and distributed locking—to achieve eventual or strong consistency.
This article introduces three MyBatis batch insertion techniques—looped single inserts, MyBatis‑Plus batch inserts, and native SQL batch inserts—provides complete implementation code, performance test results for inserting 100,000 records, analyzes the causes of differences, and offers practical solutions to common pitfalls such as MySQL packet size limits.
This article explains when and how to use caching with Redis, compares recursive and table‑lookup algorithms for factorial calculation, discusses cache design, expiration policies, and presents several write‑through and write‑behind strategies to maintain data consistency between Redis and MySQL under various concurrency scenarios.
This article compares MySQL and HBase by examining their architectural designs, storage engines, data access patterns, and ecosystem features, highlighting the strengths and trade‑offs of each system and outlining the scenarios where HBase is a suitable complement to MySQL.
This article explains the difference between MySQL’s utf8 and utf8mb4 character sets, why utf8 cannot store emojis or complex Chinese characters, and provides step‑by‑step examples showing how to configure tables and columns with utf8mb4 to avoid encoding errors.
To keep MySQL and Redis synchronized, the article defines consistency, examines read‑only and read‑write cache inconsistency cases, and recommends practical strategies such as update‑DB‑then‑delete‑cache with message‑queue retries, binlog‑driven updates, delayed double‑delete, and Redis distributed locks, while outlining stronger protocols and key design considerations.
This article explains MySQL's native horizontal sharding using MERGE tables, covering the concepts of horizontal versus vertical splitting, table‑level sharding and partitioning, practical examples, advantages, limitations, and suitable use cases for MERGE tables.
After a client mistakenly imported 60,000 useless rows and a developer’s faulty delete command erased over 100,000 critical records, this guide explains how to use MySQL binlog to locate, extract, and replay the missing SQL statements, restore the data, and prevent future disasters.
When a MySQL table is sharded into multiple databases or tables, the usual LIMIT offset, pagesize pagination breaks, and this article examines why simple merge fails, then presents three practical approaches—global limit, secondary queries, and jump‑page restriction—highlighting their trade‑offs and limitations.
This guide walks through setting up a full‑stack web chatroom demo built with Spring Boot, Vue, WebSocket, MySQL, Redis, RabbitMQ and FastDFS, covering the technology stack, core features, database schema, configuration files, and step‑by‑step deployment instructions.
This article examines why a MySQL composite index on (first_name, last_name) initially fails to be used for ordering, demonstrates how adding a range predicate reduces the scanned row count, and explains the threshold at which MySQL switches from filesort to index‑based retrieval.
This article examines why containerizing MySQL often leads to data‑security, performance, state‑management, and resource‑isolation problems, while also outlining scenarios where MySQL can safely run in Docker with proper strategies and examples.
This article documents a real‑world incident where a MyISAM table in a Percona XtraDB Cluster caused data inconsistency and node self‑shutdown, analyzes the root cause, and describes how using pt-online-schema-change together with proper engine conversion restored consistency across the cluster.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates clustered and secondary indexes with practical SQL examples and diagrams.
This guide walks through installing Redis, MariaDB, Go, configuring Open-Falcon's backend and frontend components, setting up Python virtual environments, and launching the monitoring system, providing complete command-line instructions and configuration details for a functional deployment.
This article presents a comprehensive MySQL slow‑query risk‑scoring model that quantifies each slow query's impact using metrics such as query count, execution time, lock wait, bytes sent and rows examined, assigns weighted scores up to 100, and demonstrates how the model enables proactive, business‑aligned remediation.
This guide details how to protect MySQL databases from errors and crashes by using mysqldump for manual dumps, creating Bash scripts for automated daily backups, and scheduling them with Linux cron, including command examples, restoration methods, and crontab syntax for reliable periodic backups.
This article explains MySQL InnoDB's locking mechanisms—including record locks, gap locks, and next‑key locks—detailing how they work, their interaction with isolation levels such as Repeatable Read, and the principles, optimizations, and examples that illustrate their impact on concurrency and phantom reads.
This article demonstrates three progressive techniques—direct list insertion, grouped batch insertion, and multithreaded batch insertion—using MyBatis to dramatically reduce MySQL bulk‑import time from over a minute to under ten seconds while handling packet size limits.
This article explains how MySQL handles concurrent transactions using MVCC, lock mechanisms, and various isolation levels, detailing dirty writes, dirty reads, non‑repeatable reads, phantom reads, and the internal structures such as ReadView that enable reliable data consistency.
This article examines how enabling the MTS (writeset‑based incremental parallel replay) feature in DTLE and MySQL affects data transfer speed, CPU and memory usage, and network latency, presenting test procedures, configurations, and performance results that show up to 28‑fold speed improvements.
This article explains how to diagnose a sudden rise in MySQL file handle usage by reproducing the issue with large tables, using Linux perf to trace open system calls, analyzing hash join behavior, and querying performance_schema.file_instances to pinpoint the underlying cause.
This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.
This guide explains why database backups are essential, walks through checking disk space, creating a backup directory, writing a MySQL dump script, making it executable, scheduling it with cron, and verifying that backups run reliably on a Linux server.
This article explains what MySQL slow queries are, why they cause system failures, proposes multi‑dimensional metrics to assess their severity, outlines concrete guidelines and change standards, and shares real‑world optimization cases and daily operational practices for eliminating slow SQL.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering key constraints, storage functions, GeoJSON support, indexing, in‑place updates, essential JSON utilities, path expressions, extraction, modification techniques, and the powerful JSON_TABLE feature with clear code examples.
This article explains the concepts, characteristics, and practical scenarios of vertical and horizontal table partitioning as well as vertical and horizontal database sharding in MySQL, helping developers decide when and how to apply each strategy for large‑scale applications.
This article explains MySQL indexes, covering their definition, benefits, various types such as B‑tree and B+‑tree, practical techniques for creating high‑performance indexes like prefix, composite, and clustering indexes, and how to inspect and maintain index information and fragmentation.
This article examines MySQL's INSERT locking behavior, explains why phantom reads do not occur under REPEATABLE READ isolation, and walks through compiling the MySQL source, debugging lock acquisition, and the role of insert‑intention and gap locks using detailed code examples and execution traces.
This guide explains how to use the open‑source Screw tool to automatically produce database schema documentation in HTML, Word, or Markdown and to generate Java entity classes, offering step‑by‑step examples with Maven plugin and Java code configurations for MySQL, Oracle, and PostgreSQL.
The article explains why slow SQL queries drain I/O and CPU resources, outlines prioritization rules, details MySQL execution steps, identifies performance‑affecting factors, and provides practical solutions and best‑practice guidelines for diagnosing and optimizing slow MySQL queries in production environments.
This article provides a comprehensive guide on using ShardingSphere-JDBC for read‑write splitting, configuring MySQL master‑slave replication with Docker, explaining redo log and binlog mechanisms, demonstrating two‑phase commit, and showing how to implement database sharding and distributed primary keys in a Spring Boot application.
This article recounts a real-world incident where a client mistakenly imported 60,000 rows of erroneous Excel data into a MySQL production database, leading to an accidental bulk delete, and explains how the team used MySQL binlog to recover the lost data while offering practical safeguards to prevent similar disasters.
The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.
MySQL 8.0 replaces the default mysql_native_password with the caching_sha2_password plugin, which stores a 70‑byte salted SHA‑256 hash with configurable rounds, caches SHA256(password) for fast logins, and falls back to RSA‑encrypted full verification on insecure connections, supporting user creation, replication and key management.
This article investigates why a DELETE IN subquery in MySQL 5.7 performs a full table scan instead of using an index, explains the optimizer’s behavior compared to SELECT IN, and provides practical solutions such as rewriting the statement as a JOIN or adding table aliases to enable index usage.
This article details the design and implementation of a high‑performance, highly available membership system for the merged Tongcheng‑eLong platform, covering Elasticsearch dual‑center clusters, traffic‑isolated three‑cluster architecture, deep ES optimizations, Redis caching and dual‑center clusters, MySQL dual‑center partitioning, migration strategies, and future fine‑grained flow‑control and degradation measures.
This article explains how MySQL implements trusted IP access control via user@host definitions, demonstrates creating and updating user accounts and associated metadata tables, and provides step‑by‑step procedures—including DCL commands, metadata updates, and handling of stored objects—to safely modify trusted IPs in production environments.
The article examines a MySQL optimizer bug where a query with a WHERE clause on uid, ORDER BY id ASC, and LIMIT 1 incorrectly triggers a full table scan despite a usable composite index, explains the optimizer’s cost calculations, and offers workarounds such as FORCE INDEX or a harmless arithmetic trick to force index usage.
A careless UPDATE without an indexed WHERE clause can trigger full‑table locking in InnoDB, halting business operations, but by understanding InnoDB's next‑key locks, enabling sql_safe_updates, and using FORCE INDEX you can avoid this costly mistake.
This article compares MySQL's INSERT INTO and LOAD DATA INFILE statements, explains their syntax, shows practical examples, highlights when each should be used for bulk data loading, discusses error handling with IGNORE, and explains why LOAD DATA INFILE is typically faster.
This article explains how MySQL 8.0 introduces strict JSON schema validation, demonstrates the json_schema_valid and json_schema_validation_report functions with array and object examples, and shows how to enforce schema checks in tables using CHECK constraints.
This article provides a comprehensive guide to DBLE's built‑in commands for viewing system information, diagnosing faults, and performing maintenance tasks such as killing connections, reloading configurations, and managing sharding nodes, helping MySQL DBAs efficiently operate distributed database clusters.
This article explains the fundamentals of MySQL indexing, including the structure of InnoDB pages, the evolution from simple page reads to B+ tree indexes, and how clustered and non‑clustered indexes affect query performance and I/O efficiency.
This article explains MySQL's layered architecture, the roles and mechanisms of redo log and binlog, their write and flush strategies, crash‑safe guarantees, and how two‑phase commit ensures consistency, providing practical commands and configuration tips for reliable database operation.
The author, a senior Java developer with ten years of experience, documents a ten‑day interview marathon across 20 companies, summarizing interview processes, outcomes, and the most frequently asked technical questions on Java fundamentals, Spring, MySQL, Redis, MQ, and micro‑service architecture to help fellow developers prepare effectively.
The article examines the 2012 GitHub data‑leak caused by MySQL master‑slave failover and auto_increment misuse, explains why developers' expectations of uniqueness, monotonicity and continuity are unrealistic, and proposes half‑sync replication and distributed ID algorithms like Snowflake as more reliable solutions.
This article compares MySQL and HBase across architecture, engine design, indexing structures, and feature sets such as TTL and multi‑versioning, highlighting how MySQL excels in low‑latency online transactions while HBase offers distributed scalability and write‑optimized storage for big‑data scenarios.
This article explains MySQL's two essential logs—redo log and binlog—by describing the database's layered architecture, the write‑ahead logging mechanism, persistence strategies, crash‑safe guarantees, and how a two‑phase commit ensures consistency between them.
This article explains how MySQL's cost‑based optimizer decides between using an index and performing a full table scan, demonstrates the behavior with a large test table, shows optimizer trace details, and clarifies why index access can sometimes be more expensive than scanning the whole table.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
This article explains why MySQL LIMIT with large offsets becomes slow, analyzes the execution flow, and presents four optimization techniques—including subquery, INNER JOIN, tag‑record, and BETWEEN…AND…—with real‑world performance numbers and sample SQL code.
The article analyzes a MySQL optimizer bug where a query with ORDER BY id ASC LIMIT 1 triggers a full‑table scan despite an applicable idx_uid_stat index, explains the cost‑based decision process, and presents two practical work‑arounds—using FORCE INDEX or a harmless arithmetic expression—to ensure the index is used.
This article provides a step‑by‑step tutorial for building a hospital registration web application with Java, Spring MVC, MyBatis, JSP pages, and MySQL, covering environment setup, database schema, DAO interfaces, service implementation, controller logic, and front‑end pages.
This guide walks through exporting business databases, re‑initializing the MySQL instance, importing data, extracting GTID information, creating replication and monitoring accounts with helper functions, resetting logs, and finally re‑joining the node to a MySQL Group Replication cluster.
This article presents a concise guide to the most frequently used MySQL commands, covering how to list databases and tables, create and drop databases and tables, and perform essential CRUD operations such as inserting, querying, updating, and deleting records.
This article walks through essential MySQL interview topics, covering basic concepts, OLTP vs OLAP, normalization forms, DML/DDL/DCL, varchar vs char trade‑offs, storage engines, ACID properties, primary and foreign keys, and how to monitor running queries, all presented from an interviewer's perspective.
This guide walks you through using mysqldump to back up MySQL databases, restoring backups, and creating a Bash script with crontab to automate daily backups while retaining only the most recent 31 days of dump files.
The article explains why using LIMIT with a large OFFSET in MySQL causes severe performance degradation, demonstrates the issue with a 5‑million‑row table, and shows how rewriting the query as a join dramatically reduces I/O and execution time while also discussing buffer‑pool effects.
The article demonstrates how a large OFFSET in a MySQL LIMIT query forces MySQL to scan millions of index rows and data pages, causing severe slowdown, and shows that rewriting the query with an inner‑join sub‑select dramatically reduces I/O and buffer‑pool usage, confirming the performance difference through experiments.
This guide walks through deploying a SpringBoot‑Vue split‑stack project with Docker by creating separate containers for Redis and MySQL, building custom Docker images for the backend JAR and the Nginx‑served frontend, configuring host networking, and exposing the necessary ports for verification.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space, improves index and range‑query performance, and provides built‑in conversion functions, while also noting readability drawbacks and offering Java utilities for manual conversion.
This weekly bulletin from the DBLE community presents curated technical news, video courses, event announcements, detailed DBLE and DTLE development updates, bug fixes, and upcoming plans, providing valuable insights for developers working with MySQL middleware and open‑source database solutions.
Facebook’s engineering team detailed the multi‑year effort to upgrade a heavily customized MySQL 5.6 deployment to MySQL 8.0, describing the migration strategy, code‑patch classification, row‑based replication adoption, extensive automation testing, and the performance and compatibility issues they resolved along the way.
This tutorial walks through setting up Python 2.7 on Ubuntu, then demonstrates how to install, connect to, and perform CRUD operations with four different databases—SQLite, MySQL, LMDB, and LevelDB—showing complete example scripts and summarizing the key differences between relational and key‑value stores.
This article analyzes the at‑least‑once guarantee of message middleware, explains why duplicate deliveries occur, compares simple database‑based deduplication methods, explores concurrency challenges, and presents a non‑transactional, status‑driven idempotence solution using MySQL or Redis with practical code examples and limitations.
This article explains MySQL's MVCC mechanism, detailing how multi-version concurrency control, undo logs, and ReadView work together to implement isolation levels, prevent dirty reads, non-repeatable reads, and phantom reads, and compares the behavior of Read Committed and Repeatable Read.
This article explains the meaning of the 'Pages flushed up to' value in MySQL's InnoDB engine, how it differs from the 'Last checkpoint' LSN, the underlying logic for its calculation, and provides test results demonstrating its behavior during writes and checkpoints.
This article explains how MySQL's Seconds_Behind_Master metric is calculated, the underlying replication mechanisms, factors that affect its accuracy, and provides detailed source‑code analysis to help DBA engineers interpret and monitor replication delay effectively.
This article explains MySQL’s essential logging mechanisms—binary log, redo log, and undo log—detailing their structures, purposes, configuration parameters, and how they work together to support replication, crash recovery, and transaction durability while balancing performance and consistency.
This article explains MySQL 8.0’s optimizer architecture and walks through the detailed transformation steps for subqueries, scalar‑to‑derived conversions, flattening, join simplification, partition pruning, and condition push‑down, illustrating each phase with code examples and execution‑plan screenshots.
The article explains how MySQL 8.0’s new doublewrite buffer implementation dramatically slows LOAD performance on a 1 GB file, identifies the innodb_doublewrite_pages default setting as the root cause, and shows that increasing it to 64 restores speeds comparable to MySQL 5.7.
This article presents a comprehensive approach to quantifying MySQL slow‑query risk by defining scoring items, establishing boundary values, applying various scoring functions, and integrating business‑level weighting, ultimately enabling proactive identification and remediation of high‑impact slow queries.
This article explains how DMA and zero‑copy techniques reduce the four memory copies and context switches typical of Linux I/O, detailing their mechanisms, implementations such as sendfile, mmap and Direct I/O, and real‑world usage in Kafka and MySQL to boost performance.
The article explains MySQL vertical partitioning techniques—including instance‑level database splitting, schema‑level splitting, and column‑level table splitting—detailing their advantages, drawbacks, and step‑by‑step command‑line procedures with code examples for implementing each method in production environments.
This article explains how MySQL’s logical‑clock replication can be enhanced with writeset‑based dependency tracking, detailing the underlying theory, code implementation, configuration options, performance testing, and practical considerations for improving parallel applier concurrency.
This article presents a systematic investigation of MySQL's open_files_limit setting across three scenarios—conflict between my.cnf and systemd service, effectiveness when using mysqld_safe, and the impact of Docker's own limits—revealing which source actually controls the maximum number of open files.
This article provides a comprehensive collection of typical MySQL interview questions covering storage engines, lock types, gap locks, deadlock avoidance, isolation levels, index types, covering indexes, left‑most prefix rule, replication, distributed transactions, optimization techniques, sharding challenges and solutions, and global unique ID generation.
This article explains the new DBLE v3.20.10.0 feature that enables read/write splitting independently of sharding, detailing environment setup, configuration files, SQL testing, and log verification for both sharded and pure read/write split scenarios.
This article explains how to use RabbitMQ as a message middleware in a Spring Boot application to achieve distributed transactions between order and dispatch services, detailing environment setup, configuration, code implementation, testing, and verification of data consistency across systems.
This weekly roundup, presented by the community reporter Yongzheng, compiles the latest MySQL issue links, a Python automation video course, DBLE and DTLE open‑source progress, new feature developments, bug fixes, community Q&A, and plans for the upcoming week.
This article explains the costly four‑copy, four‑context‑switch data path in traditional Linux I/O, introduces DMA as a co‑processor that offloads memory transfers, describes zero‑copy techniques such as sendfile, mmap and Direct I/O, and shows how Kafka and MySQL leverage these methods to reduce CPU overhead and improve throughput.
The article explains why MySQL recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of strings, outlines space and query advantages, notes readability drawbacks, and demonstrates conversion using built‑in functions and Java utility code.
This article explains why duplicate rows appeared in production MySQL tables, demonstrates how to identify them with SELECT queries, and provides two SQL solutions—one to delete all duplicates and another to keep a single record per duplicated key—while preserving data integrity.
The article explains MySQL 8.0.22's new optimizer_switch variable derived_condition_pushdown, shows how enabling it can cause unexpected results with user‑defined variables in outer WHERE clauses, provides test cases, explains the underlying behavior, and offers three practical solutions.
This article explains why using a large OFFSET with MySQL LIMIT can cause severe performance degradation, demonstrates the problem with a real‑world 9.5 million‑row table, and shows how rewriting the query with a sub‑query that selects only primary keys can cut execution time from seconds to milliseconds.
This article examines MySQL’s locking mechanisms—including shared and exclusive row locks, gap and next‑key locks, table‑level and metadata locks—detailing how different SQL statements and index types affect lock acquisition, illustrating lock behavior with examples, and offering practical strategies to avoid deadlocks.
This article walks through MySQL 8.0’s SQL optimizer, explaining how a raw SQL statement is parsed, transformed, and optimized through stages such as Setup, Resolve, Derived‑table merging, materialization, window‑function handling, and ROLLUP processing, illustrated with diagrams and code examples.
This article compiles twenty detailed MySQL interview questions covering redo log vs. binlog, index types and best practices, covering indexes, joint index matching rules, back‑table queries, left‑most prefix principle, lock mechanisms, MVCC, execution plans, master‑slave replication, XA transactions, and the Snowflake ID algorithm, providing clear explanations and example SQL code.
The article demonstrates how to use MySQL error logs and gdb to trace a crash‑related bug, identify the affected function in the source code, compare version histories on GitHub, and confirm that the issue was fixed in MySQL 8.0.20.
This article explains the design and implementation of MySync, a component that leverages MySQL binlog to synchronously replicate data to heterogeneous storage systems such as Redis, ElasticSearch, and HBase, detailing its architecture, reader and writer modules, Kafka integration, and strategies for handling master‑slave failover.
An in-depth investigation of a MySQL InnoDB startup deadlock caused by truncate operations and the innodb_flush_sync parameter, detailing log observations, stack traces, source code examination, and three practical solutions including GDB intervention, parameter adjustment, and data directory cleanup.
The article explains Tencent Cloud's enterprise‑level MySQL dynamic thread‑pool capability, describing the one‑thread‑per‑connection and thread‑pool modes, the new thread_handling_switch_mode parameter with its four options, status‑monitoring commands, and added columns for process‑list visibility, enabling seamless mode switching without server restarts.
This article analyzes common like‑system business scenarios, derives functional requirements, proposes three architectural solutions (Redis‑based priority queue, relational database, and a hybrid cache‑MQ‑DB design), presents detailed implementation code, and evaluates each approach with extensive performance tests to guide practical backend design choices.
The article explains that Xtrabackup only needs to ensure consistency between data and the binlog position, not full binlog data, because its backup process captures only committed or not‑started transactions, eliminating the need to back up the binlog itself.
This article explains the impact of slow SQL on database resources and user experience, outlines priority rules for addressing slow queries, details MySQL execution steps, identifies key performance factors, and provides concrete optimization methods including enabling slow‑query logs, indexing, I/O merging, and distributed architecture.
This article explains why MySQL queries become slow, how proper indexing, MDL locks, sharding, read‑write separation, and large‑table strategies can improve performance, and also introduces Elasticsearch and HBase as complementary technologies for search and high‑write workloads.
