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.
This article explains how MySQL treats backslashes as escape characters in INSERT and SELECT queries, demonstrates the effect of different numbers of backslashes through practical tests, and clarifies the double‑escaping required when using LIKE patterns.
This article explores multiple MySQL techniques—triggers with a counter table, range partitioning, general tablespace limits, and application‑side buffering—to enforce a hard cap of 10,000 rows per table, discussing implementation steps, code examples, and the trade‑offs of each method.
This article examines how Oracle, PostgreSQL, and MySQL implement and use database sequences for primary keys, covering explicit calls, trigger‑based, DEFAULT, and AUTO_INCREMENT methods, version support, and the effects of INSERT, UPDATE, DELETE, and TRUNCATE operations, concluding with a comparative summary.
The article explains why storing IPv4 addresses as a 32‑bit unsigned integer in MySQL saves space and improves range‑query performance, outlines the trade‑offs such as readability, and demonstrates conversion using MySQL functions and Java code examples.
This guide walks through selecting a clustering strategy, installing Percona XtraDB Cluster (PXC) with Docker, configuring a five‑node cluster, adding HAProxy for load balancing, testing access, and handling node failures to achieve strong consistency and high availability for critical data.
This tutorial demonstrates how to reproduce a MySQL memory‑spike bug, monitor the process with Linux perf to capture mmap system calls, and analyze the resulting perf.out to identify which SQL statements trigger large memory allocations, while also discussing the method’s limitations.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors when storing true four‑byte UTF‑8 symbols, and shows how the newer “utf8mb4” charset provides full Unicode support, with historical context and migration guidance.
This article explains why MySQL master‑slave replication is used, how the binary‑log based mechanism works, compares statement‑based and row‑based modes, provides key configuration parameters, and offers practical solutions for reducing replication lag.
This tutorial demonstrates how to set up a CentOS 7.5 environment, install RabbitMQ, and use Spring Boot to create a distributed transaction system that ensures data consistency across order and dispatch services by leveraging RabbitMQ's confirm and ACK mechanisms, complete with database schema, configuration, and code examples.
This article explains the definitions of QPS (queries per second) and TPS (transactions per second) in MySQL, compares the various calculation methods—including Queries‑per‑second, Questions‑per‑second, and GTID‑based approaches—and recommends the most reliable ways to measure database load and transaction throughput.
This article traces two decades of application data architecture evolution, comparing traditional single‑system LAMP designs with modern multi‑component cloud‑native stacks, and offers practical guidance on scaling, component selection, CDC‑based data derivation, and cloud‑native implementations such as Tablestore.
The article examines the challenges of running MySQL in Docker containers, highlighting data‑security risks, performance bottlenecks, state‑management problems, and resource‑isolation limits, while also outlining scenarios where containerizing MySQL can still be appropriate.
This article explains the purpose and interpretation of each column in MySQL's EXPLAIN output, covering identifiers, select types, table sources, partition info, access types, possible keys, actual keys, key lengths, row estimates, filtering percentages, and extra execution details, with visual examples and code snippets.
This article compares MySQL‑based single and multi‑record stock deduction methods with a Redis‑based approach that uses Lua scripting and distributed locks, providing full Java implementation details and REST examples for high‑concurrency inventory management.
This guide introduces the sa‑plus framework—a SpringBoot‑based rapid‑development scaffold with an integrated code generator that can automatically produce backend, frontend, and API documentation code from database tables, and walks through its architecture, technology stack, module structure, quick start steps, and code‑generation features.
This guide explains how to set up a Percona XtraDB Cluster (PXC) using Docker containers, configure five-node replication with strong consistency, and implement HAProxy as a TCP load balancer to achieve high availability and balanced read/write traffic across the cluster.
The article explains why running MySQL in Docker containers can lead to data loss, performance degradation, and inadequate resource isolation, while also outlining specific scenarios where containerizing MySQL may still be appropriate.
This article explains the principles of master‑slave read/write separation, the MySQL binlog‑based replication process, its side effects, practical ways to mitigate replication lag, and how middleware or proxy layers can simplify database access in high‑concurrency environments.
This article provides a comprehensive guide to MySQL performance tuning, covering SQL pagination tricks, index design, join strategies, sorting, UNION handling, slow‑query logging, schema design, data‑type choices, normalization, and hardware considerations, all illustrated with concrete code examples.
This article compiles a comprehensive set of MySQL interview questions covering index types, the differences between MyISAM and InnoDB, the rationale for auto‑increment primary keys, sharding strategies, ACID properties, isolation levels, storage‑engine trade‑offs, normalization, index best practices, data types, performance tuning, locking mechanisms, B+Tree characteristics, and common SQL operations.
This article explains how MySQL handles internal temporary tables, detailing the evolution of storage mechanisms and related parameters from version 5.6 through 5.7 to 8.0, including the shift to TempTable, shared session temporary tables, memory‑mapped files, and configuration tips to avoid space issues.
This article presents a detailed performance benchmark comparing MariaDB 10.4.12 and MySQL 8.0.19 on an i7 Windows 10 system, covering single and batch inserts, indexed and non-indexed queries, and analyzes speed and memory usage differences between the two databases.
This article presents a comprehensive guide to improving MySQL query performance by addressing common issues such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and demonstrates how using WITH can simplify complex statements.
This article explains MySQL 8.0's new indexing features—including function indexes via virtual columns, Index Skip Scan for non‑leftmost predicates, descending indexes to avoid filesort, and invisible indexes for optimizer control—providing examples and usage guidelines.
This article demonstrates how to use Google’s tcmalloc library and the pprof tool to capture and analyze MySQL heap dumps, revealing memory allocations that performance_schema cannot track, and explains the experimental setup, data collection, and interpretation of the resulting memory allocation graphs.
