This article explains three MySQL pagination techniques in Java—using LIMIT clauses, MyBatis RowBounds, and the MyBatis‑PageHelper plugin—providing syntax, XML and Java code examples, execution results, and practical recommendations for efficient data retrieval in backend development.
This article provides a comprehensive overview of MySQL indexes, explaining their underlying data structures, classification methods, practical usage scenarios, and optimization strategies, while illustrating concepts with diagrams and SQL examples to help developers design efficient query performance.
This weekly community newsletter summarizes recent MySQL licensing research, bug fixes, open-source middleware updates for DBLE, DTLE, and SQLE, highlights new features, community Q&A, and upcoming plans, providing developers with concise technical insights and resource links.
This article explains MySQL's redo log, binlog, and undo log, covering their roles in crash recovery, durability, performance optimization, log formats, two‑phase commit handling, and how they together ensure data consistency and atomicity in InnoDB.
This article explains why a MySQL root account may bypass password authentication, walks through common causes, demonstrates reproducing the issue, identifies the auth_socket plugin as the culprit, and shows how to switch to a native password plugin to restore proper password checks.
This article explains MySQL index fundamentals, covering B+Tree structures, various index types, creation syntax, and practical optimization techniques such as prefix, covering, and composite indexes, while illustrating how to avoid common pitfalls like index misuse and performance‑degrading scenarios.
This article walks you through the complete hands‑on lab for migrating a local MySQL database to Huawei Cloud RDS using the DRS service, covering environment setup, connection testing, full and incremental migration phases, and final execution tips.
This comprehensive guide details naming conventions for databases, tables, columns, indexes, views, procedures, functions, triggers, users, and constraints, along with design principles for objects, storage engines, character sets, table structures, field types, index strategies, and SQL usage to ensure consistency, performance, and maintainability in MySQL environments.
This article introduces and compares three MySQL online schema‑change solutions—pt‑online‑schema‑change, gh‑ost, and native MySQL ONLINE DDL—detailing their workflows, limitations, risks, monitoring features, and practical considerations for safely altering large tables in production environments.
This article walks through the background of a production Java service that received many performance complaints, enumerates common causes such as slow MySQL queries, complex business logic, thread‑pool misconfiguration, lock contention and machine issues, and provides concrete diagnostic steps and code‑level solutions including pagination fixes, indexing strategies, async processing, thread‑pool tuning, lock refinement and caching techniques.
The article explains why Oracle can release MySQL under a dual GPLv2/commercial licensing model, how the GPL works, the impossibility of changing licenses for existing versions, and proposes creating a domestically controlled GPL‑v2 MySQL fork to avoid future licensing risks.
Ctrip’s hotel order platform evolved from a single SQL Server instance to a multi‑IDC, sharded architecture, and this article details the middleware‑driven migration, unified caching, data‑chain redesign, consistency safeguards, and operational practices that enabled seamless, high‑performance order processing at millions of transactions per day.
This article explains why backup is the last line of data security, outlines multi‑location and multi‑media deployment requirements, compares physical and logical backup methods, reviews MySQL backup tools, evaluates their speed, and provides practical guidance for testing backup validity and minimizing RTO and RPO.
The SQLE 1.2205.0 release introduces three major features, enhancements to the SQL audit workflow, Oauth2 and LDAPS integrations, and a comprehensive list of bug fixes and optimizations, providing a more secure and efficient experience for database users and administrators.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication with Docker, explains redo log and binlog fundamentals, demonstrates two‑phase commit, and shows how to implement read‑write splitting and sharding using ShardingSphere‑Jdbc and Sharding‑Jdbc with detailed code examples.
This article explains how MySQL's EXPLAIN command reveals execution plans through twelve key fields, demonstrates practical analysis of a slow query on a large table, and provides actionable tips such as adding or forcing indexes to dramatically improve query performance.
This article analyses why physical backups using Xtrabackup fail when MySQL 5.7 performs online DDL operations that skip redo logging, demonstrates the issue with real‑world logs, and presents four practical solutions—including lock‑DDL parameters, per‑table MDL locks, and disabling online DDL—to ensure consistent backups.
This article explains the two kinds of MySQL temporary tables—external and internal—covers the scenarios that trigger their creation, details how MySQL chooses MEMORY, MyISAM or InnoDB storage engines based on system variables and data size, and describes indexing, hash‑field fallbacks, and optimization hints such as SQL_SMALL_RESULT and SQL_BIG_RESULT.
This article explains why concurrency issues like overselling occur in e‑commerce, introduces distributed lock concepts and mainstream implementations (MySQL, Redis, Zookeeper/etcd), and provides a three‑stage quality‑assurance framework with code‑review checklists, testing methods, and data‑reconciliation techniques.
This article provides a comprehensive overview of MySQL's internal execution flow—from client connection handling, permission verification, caching, parsing, optimization, and execution—to the detailed SQL execution order, helping readers grasp how queries are processed and optimized within the database engine.
This tutorial explains why read/write splitting is useful, shows step‑by‑step how to build a MySQL master‑slave cluster with Docker, configure the master and slave instances, and then use Sharding‑JDBC to transparently route writes to the master and reads to the slave.
The article investigates why read queries are sent to the master node when Dble is configured with rwSplitMode=3, analyzes reproductions on standard replication and MySQL Group Replication, identifies heartbeat and replication status issues, and provides troubleshooting steps and recommendations.
This article provides a comprehensive, step‑by‑step explanation of MySQL InnoDB's MVCC mechanism, covering its fundamentals, the role of Undo Log and Read View, and how it enables repeatable‑read and read‑committed isolation levels while preventing dirty reads and other concurrency anomalies.
The article explains why a newly created read‑only MySQL 5.7 user receives a “MySQL server has gone away” error, analyzes the role of the init_connect variable and SUPER privilege, and provides a step‑by‑step fix by correcting the init_connect setting.
This article shares a detailed, step‑by‑step account of three interview rounds at ByteDance, covering the background, technical and algorithm questions on Java, Redis, MySQL, Kafka, and more, along with personal reflections and practical tips for future candidates.
The article introduces the SQLE 1.2205.0-pre3 preview release, detailing the project's purpose, new enterprise‑edition SQL query features, complete release notes, and upcoming plans, while providing download links, documentation, and demo environments for users and administrators.
This article explains MySQL replication fundamentals, binary log mechanics, master‑slave roles, configuration steps, and the various replication types—including asynchronous, semi‑synchronous, multi‑source, and delayed replication—while providing practical commands and code examples for reliable high‑availability setups.
This article chronicles the architectural evolution of JD Baitiao’s backend—from early MySQL monoliths through Solr‑HBase, MongoDB, and DBRep—to the adoption of Apache ShardingSphere, highlighting the motivations, technical trade‑offs, decoupling strategies, and performance outcomes for a high‑throughput financial service.
The article investigates how loss of TCP SYN, SYN‑ACK, or ACK packets during the three‑way handshake affects MySQL client‑server connections, demonstrating retransmission behavior, kernel parameter tuning, and keep‑alive mechanisms through controlled iptables and tcpdump experiments.
This guide demonstrates how to deploy a MySQL cluster on Kubernetes using NFS persistent storage, ConfigMap for configuration, Service for access, and a StatefulSet to manage multiple replicas with init containers and volume claims.
This article explains the complete MySQL query processing pipeline, covering the connector, authentication, privilege checks, caching, parser, optimizer, executor, and the detailed SQL execution order with examples of each stage, helping readers understand and optimize their SQL statements.
The article investigates why two MySQL 5.7.32 installations on RedHat 7 show different client character sets—utf8 in the first batch and latin1 in the second—by reviewing MySQL charset settings, OS locale configuration, and providing corrective commands.
This article explains InnoDB’s role as a MySQL storage engine, how it reads and writes data using 16 KB pages, the mechanics of row formats—especially the dynamic format—and why varchar columns are limited to 16383 characters, illustrated with SQL examples and diagrams.
This article details how a Java Spring Boot microservice's response time was reduced from 5‑6 seconds to under one second by applying gzip compression, parallel data fetching, short‑lived caching, MySQL index tuning, and JVM G1 garbage‑collector adjustments.
This guide walks beginners through downloading, installing, and configuring MySQL 8.0.28 on Windows and Linux, covering environment variables, service management, password recovery, user and role creation, and JDBC connection setup with Maven, providing detailed commands, code snippets, and troubleshooting tips for a complete setup.
This article explains the complete MySQL execution pipeline—from client connection, authentication, and permission checks through caching, parsing, optimization, and execution—plus the logical order of SQL clauses, illustrated with diagrams to help developers optimize and comprehend database queries.
This article explains how to replace raw PyMySQL usage with SQLAlchemy ORM in Python, covering environment setup, database connection initialization, MVC‑style project structure, model class definitions, field attributes, session handling with context managers, and practical code examples for saving and querying data.
This article walks through MySQL's InnoDB storage engine, explaining how pages, locality, skip‑list concepts and B+‑tree structures work together to make indexes efficient, and shows how clustered, non‑clustered and covering indexes affect query performance and I/O behavior.
This article investigates a MySQL 5.7.30 replica crash caused by an InnoDB assertion in btr0btr.cc when handling temporary tables, reproduces the issue using the MySQL Test Run framework, analyzes logs and parameters, and proposes configuration and SQL‑optimisation fixes.
This article explains the concepts, motivations, strategies, and challenges of database and table sharding, covering vertical and horizontal partitioning, range and hash algorithms, when to adopt them, and popular middleware solutions for scalable MySQL architectures.
This article provides a comprehensive examination of InnoDB's physical file layout, detailing page components such as the file header, page header, record storage formats, variable‑length and NULL handling, free space, page directory, file trailer, external storage pages, and the differences among row formats and compression techniques, illustrated with concrete hexdump examples and SQL statements.
This article explains MySQL hash partition tables, illustrating their suitable use cases such as equality queries on IDs or dates, demonstrating creation, data loading, and query plans, and comparing standard hash partitions with linear hash partitions to highlight performance and data distribution considerations.
This article explains how Meituan tackles MySQL slow‑query problems by leveraging the database cost‑based optimizer to generate index suggestions, evaluate their quality, and operate a full‑stack governance workflow that includes data collection, statistical modeling, validation, and continuous improvement.
This article demonstrates how to package Ansible in a Docker container, use the mysql_db module to create MySQL backups, and run a simple playbook, highlighting the benefits of containerized deployment for clean, flexible operations automation.
This article analyzes a MySQL replication failure where the slave reports error 1236 "Could not open log file", explains the root cause involving duplicate entries in the binlog index after manual deletion of active binlog files, and provides step‑by‑step reproduction procedures and preventive recommendations.
The article explains how a pagination query on a 9.5‑million‑row MySQL table using a large OFFSET can take seconds, demonstrates an optimization that moves the condition into a subquery to fetch only primary keys before joining, and validates the speed gain with buffer‑pool measurements.
This article explains MySQL 8.0.17 InnoDB locking mechanisms—including latches, row and table locks, lock modes, intention locks, their compatibility matrix, and detailed source‑code examples that illustrate how IX and X locks are acquired during record updates.
This article details the end‑to‑end development of a supplier management system using SpringBoot, Thymeleaf, Mybatis‑Plus, MySQL and Redis, covering project planning, code implementation, file upload handling, caching optimization, and deployment considerations.
This weekly community newsletter curates the latest technical news and open‑source progress, covering MySQL and Mycat issues, a ClickHouse distributed DDL block case, a MySQL MDL lock analysis, the new SQLE release, and updates on DBLE, DTLE, and SQLE middleware projects.
The article introduces the SQLE 1.2205.0-pre1 release, detailing the project's purpose, new capabilities such as OAuth2 login integration and improved MySQL syntax‑error handling, and provides complete release notes, bug fixes, links to documentation, previous versions, and community resources.
This article shares a curated selection of interview questions and answers covering concurrency programming, MySQL, and Redis, extracted from the author's extensive interview notes, with links to the full content on a personal website and instructions for obtaining the complete material.
This article analyzes MySQL batch insertion by examining field size, max_allowed_packet limits, transaction overhead, buffer pool usage, and empirical tests with varying batch sizes, ultimately recommending a batch size around half of the max_allowed_packet for optimal throughput.
The article investigates a production MySQL outage caused by FLUSH NO_WRITE_TO_BINLOG TABLES blocking during backups, presents three experimental scenarios to identify the root cause, and offers practical recommendations such as setting query timeouts, adding indexes, and using Percona XtraBackup options to avoid backup stalls.
MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.
This article provides a comprehensive tutorial on using ShardingSphere‑JDBC, configuring MySQL master‑slave replication with Docker, explaining binlog and redo‑log mechanisms, demonstrating two‑phase commit, and detailing Sharding‑JDBC read/write splitting and sharding configurations with code examples.
In MySQL, an UPDATE that modifies no actual data returns an affected‑rows count of zero, making it unreliable for business logic that distinguishes between unchanged rows and non‑existent records, so developers should avoid using this count for critical decisions.
This article explains that MySQL imposes no fixed row limit, discusses the common 5‑million‑row myth and Alibaba's 2 GB recommendation, shows a Java insertion test that slows after millions of rows, and highlights how InnoDB buffer size and memory constraints affect table performance.
This article provides a step‑by‑step tutorial on setting up Prometheus and Grafana for 24/7 monitoring of Linux servers and MySQL databases, covering installation, configuration, data visualization, alerting with onealert, and common troubleshooting tips for reliable operations.
Facing a sudden MySQL table lock? This guide walks through real‑world troubleshooting steps—from checking table usage and process lists to inspecting InnoDB transaction and lock tables, identifying lock‑wait relationships, and safely killing offending sessions, while also explaining lock types and common metadata‑lock scenarios.
After deploying a project, a 500 Server Error appears the next day due to MySQL closing idle connections after the default 8‑hour wait_timeout, and the article explains how to verify the issue, adjust wait_timeout, use autoReconnect, and properly configure the HikariCP connection pool to prevent the failure.
This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
A recent poll revealed that MySQL with the InnoDB engine can support up to four billion tables, a limit dictated by the underlying file system rather than MySQL itself, and the official documentation confirms this massive capacity.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article introduces a full‑stack food‑delivery project built with Spring Boot, Vue.js, MySQL and MongoDB, detailing its architecture, module breakdown, technology choices, database setup, and step‑by‑step instructions for launching the backend API, admin portal, and mobile client, along with download links for data and images.
This article compiles eight classic MySQL error scenarios—including forgotten passwords, case‑sensitivity mismatches, Windows service startup failures, export/import restrictions, excessive connections, full binary logs, and primary‑key replication conflicts—along with detailed troubleshooting steps, configuration tweaks, and command‑line solutions to help database practitioners resolve them efficiently.
MySQL 8.0.29, released on April 26 2022, brings 160 bug fixes, several feature refinements—including string handling, stricter time formats, replication defaults, instant‑algorithm support for InnoDB, and new clone variables—while signaling the final phase of the 8.0 lifecycle and urging users of older versions to upgrade for security.
This article walks through generating a heavy‑load MySQL workload, using top, iostat, and iotop to pinpoint high disk I/O, mapping OS thread IDs back to MySQL sessions, and safely killing the offending query to relieve pressure.
The article asks how many tables can be created in a single MySQL database using the InnoDB storage engine, prompting readers to provide the correct limit without consulting external sources and discuss the theoretical maximum based on MySQL specifications.
The article describes how a large‑scale membership system achieves high performance and fault tolerance by deploying a dual‑center Elasticsearch cluster, isolating traffic with multiple ES clusters, adding a Redis cache with distributed locks, migrating the primary relational store from SQL Server to a partitioned MySQL cluster, and implementing fine‑grained flow‑control and degradation strategies.
The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
This article explains MySQL's lack of a hard row limit, the industry rule of thumb around 5 million rows, how InnoDB buffer size and hardware affect performance, and demonstrates a simple insertion test that reveals slowdown when a table grows too large.
This article analyzes a MySQL InnoDB lock wait timeout error, explains how gap locks cause blocked inserts and phantom reads under repeatable‑read isolation, and offers optimization strategies such as primary‑key deletions, while concluding with a recruitment invitation for the Zero technology team.
Yearning is a lightweight, self‑contained MySQL SQL audit platform that offers query auditing, SQL review, permission management, and automated rollback, with simple installation steps and no reliance on third‑party tools, making it ideal for small and medium enterprises.
This article analyzes MySQL batch insertion performance, explains how row size, max_allowed_packet, buffer pool, transaction handling, and index design affect throughput, and presents empirical tests that suggest using roughly half of the max_allowed_packet size as the optimal batch size for large data loads.
This weekly roundup from the DBLE open‑source community shares the latest MySQL‑related news, feature releases, bug fixes, and upcoming plans for DBLE, DTLE, and SQLE, providing developers with concise technical updates and links to detailed articles.
The article explains how Meituan uses MySQL's cost‑based optimizer to analyze slow queries, generate virtual index candidates, evaluate their costs with detailed statistics, and deploy a recommendation system that validates, tracks, and governs index suggestions to reduce CPU/IO waste and prevent database failures.
This article outlines several frequent MySQL query pitfalls—including missing table aliases in multi‑table operations, unsupported FULL JOIN syntax, unexpected REPLACE INTO side effects, auto‑increment edge cases, data‑type mismatches, nondeterministic functions, and warning‑driven query behavior—providing examples and best‑practice recommendations to prevent data loss and performance issues.
The article analyzes why a MySQL 5.7.32 instance’s monitoring thread remains in the Opening table state when querying information_schema.tables, discovers the involvement of the Federated storage engine, reproduces the issue with a non‑existent remote server, and confirms the root cause through controlled experiments.
This article provides a comprehensive guide to relational database design with MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine selection, character set and data type choices, and index design principles, illustrated through a student‑course selection example.
This article explains the components of MySQL InnoDB Cluster, introduces the InnoDB Cluster Set (MICS) for disaster‑recovery, outlines its limitations, and provides a step‑by‑step demonstration with code on how to create, monitor, and fail over a MICS deployment.
This article presents eight common MySQL error scenarios—including forgotten passwords, password policy violations, case‑sensitivity issues, service startup failures, export/import restrictions, connection limits, binary log overflow, and primary‑key replication errors—along with detailed troubleshooting steps and configuration commands to resolve each problem.
This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.
This article explains how to retrieve sales statistics for multiple business units and their associated products in MySQL by comparing rows, discusses alternative approaches such as loop queries, OR concatenation, and mixed filtering, and demonstrates why row‑by‑row comparison is the most suitable solution.
The DTLE 4.22.04.0 release introduces UTF‑32 support, Chinese table name handling, enhanced ColumnMapFrom conversion, expanded SqlFilter capabilities, additional logging, and numerous bug fixes including procedure handling, DDL panic prevention, and task list display issues, with links to documentation and related articles.
This article examines frequent SQL anti‑patterns—such as inefficient LIMIT usage, implicit type conversion, suboptimal UPDATE/DELETE joins, mixed ordering, unnecessary EXISTS clauses, condition push‑down failures, early limiting, and intermediate result handling—and provides rewritten queries that dramatically improve performance.
This article evaluates the open‑source SQLE SQL auditing platform against MySQL 8.0.28, demonstrating how DDL, DML, DCL statements and their contextual relationships are checked, highlighting successful detections, mismatches caused by new MySQL 8.0 features, and concluding on overall compatibility.
The article examines how slow‑query‑induced avalanches arise in MySQL, explains why naïvely killing slow queries is fraught with accuracy, automation and responsibility issues, and proposes a signature‑based timeout mechanism that lets applications safely abort problematic queries.
The article presents a kindergarten management system featuring three user roles, outlines its Spring Boot and Thymeleaf technology stack, details required runtime and database environments, provides step‑by‑step deployment instructions, and includes complete backend controller source code for user, teacher, student, and school management functionalities.
This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.
This article analyzes the full lifecycle of a MySQL connection from Java code to TCP handshake using Wireshark, measures each step’s latency, calculates the total cost per request, and demonstrates why connection pooling is essential for high‑traffic web applications.
This tutorial presents a complete Python + Django e‑commerce mini‑project, outlining its main features, required environment, step‑by‑step installation commands, configuration changes, database setup, and how to run the application locally for self‑learners.
After prolonged Zabbix monitoring generates massive history tables, this guide provides a Bash script that stops Zabbix services, disables foreign key checks, truncates and optimizes various history tables in MySQL, backs up the database, and restarts the services.
This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.
This article explains how MySQL can implement GROUP BY using either a compact index scan that reads rows sequentially or a loose index scan that jumps to the needed rows, compares their costs, and shows when each method is optimal, including a hybrid sequential‑loose scan.
This article investigates how much time a simple Java program spends establishing and closing a MySQL connection, measures the latency of each TCP and MySQL handshake step with Wireshark, and demonstrates why connection pooling is essential for high‑traffic web services.
This article demonstrates how a MySQL pagination query on a 9.5‑million‑row table that originally took over 16 seconds can be accelerated to under 0.4 seconds by moving the LIMIT condition into a subquery that selects only primary‑key IDs and then joining to retrieve the remaining columns, with detailed code and experimental verification.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This document describes the background, problems, requirements, analysis, and detailed design of a high‑performance, highly available, and scalable log‑analysis pipeline for MySQL that leverages Kafka, ClickHouse, MySQL, and custom services to aggregate, enrich, and visualize massive query logs.
This article explains how DTLE supports pt-osc table schema changes by describing pt-osc's underlying mechanism, DTLE's DDL capabilities, and step‑by‑step instructions for deploying DTLE, preparing MySQL data, configuring a DTLE job, running pt‑online‑schema‑change, and verifying successful synchronization.
