This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.
This comprehensive guide covers MySQL fundamentals such as the three normal forms, differences between MyISAM and InnoDB, redo log versus binlog, when queries bypass indexes, join types, various index structures, covering indexes, back‑row lookups, transaction properties and isolation levels, common performance bottlenecks, replication mechanics, lag mitigation, storage behavior after deletes, VARCHAR limits, lock types, systematic SQL tuning steps, the purpose and trade‑offs of indexes, B+‑tree advantages, MVCC internals, and the Snowflake distributed ID algorithm.
The DTLE 4.22.07.2 release introduces performance‑boosting pipeline enhancements for large transactions, continuous insert optimizations, data chunking for massive loads, a new DumpEntryLimit setting, and bug fixes, while providing repository links, documentation, and recommended articles for further exploration.
This guide explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, how to delete the extra rows while keeping the earliest entry, and how to handle duplicate detection across multiple columns with correct query patterns and common pitfalls.
This article explains why a single MySQL instance can become a bottleneck, introduces asynchronous master‑slave replication using binary logs, provides detailed configuration commands for both master and slave servers, and shows how to achieve read‑write splitting in Java projects with ShardingJDBC, including dependency setup, YAML configuration, and testing procedures.
This article presents a comprehensive list of MySQL optimization best practices, covering SELECT field selection, WHERE clause design, data type choices, index usage, join strategies, batch operations, UNION handling, and many other techniques to reduce resource consumption, improve query speed, and ensure efficient database management.
This article walks through a real‑world incident caused by MySQL deep pagination, explains how the slow queries and CPU spikes were diagnosed, and presents several concrete optimization techniques—including ID‑based queries, range scans, sub‑queries and cursor‑based pagination—culminating in a stable production fix.
This article explains how to collect comprehensive MySQL performance metrics using only native SHOW commands, covering connections, buffer pool, locks, SQL statements, throughput, server variables, and slow‑query analysis, while also offering practical tips for interpreting and optimizing the results.
This article investigates MySQL's recommendation against using UUIDs as primary keys by creating three tables (auto‑increment, UUID, and random snowflake IDs), benchmarking insert speeds with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the advantages and drawbacks of each key strategy.
The preview release SQLE 1.2209.0-pre1 introduces OceanBase TopSQL auditing, Alibaba Cloud RDS MySQL audit‑log support, and a sub‑query nesting rule, while providing project background, download links, demo environments, and a detailed list of new features and release notes for database professionals.
This article explains the evolution from single‑node MySQL to master‑slave replication, various replication modes, high‑availability solutions such as MHA and MGR, and the challenges and techniques of vertical and horizontal sharding for scaling large‑scale internet applications.
The article explains the three primary MySQL data removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space for InnoDB and MyISAM engines, and best‑practice usage such as running OPTIMIZE TABLE after DELETE.
This guide explores five essential dimensions of MySQL performance tuning—including connection configuration, architectural choices such as caching and read‑write splitting, optimizer settings, storage engine selection, and schema design—providing concrete commands, formulas, and best‑practice recommendations to accelerate query execution and reduce load.
The article compares MySQL's limited join capabilities with PostgreSQL's richer options, explains why multi‑table joins can be slower than separate single‑table queries performed in the service layer, and outlines how decomposing joins improves caching, scalability, and overall system performance.
This article describes a step‑by‑step investigation of a MySQL remote‑connection failure where the server reports port 0, explains how the skip‑grants‑tables option implicitly enables skip‑networking, and provides the simple fix of commenting out the option and restarting the service.
This article provides a comprehensive tutorial on installing and configuring the MySQL Orchestrator high‑availability tool, covering required host environments, software download, extraction, backend MySQL user setup, configuration file editing, service startup, and web UI access.
This article walks through a real‑world large‑scale MySQL sharding project, covering business refactoring, storage architecture design, data migration, incremental upgrades, best‑practice tips, and stability safeguards, while sharing concrete steps, pitfalls, and lessons learned from start to production rollout.
This article explains how MySQL performs crash recovery by using Redo logs, doublewrite buffers, undo tablespace handling, transaction subsystem initialization, and the processing of active, prepared, and committed transactions to restore data pages and ensure consistency after an unexpected shutdown.
This article walks through installing and using the golang-migrate CLI to manage database schema changes, covering supported drivers, creating migration files, applying and rolling back migrations in PostgreSQL and MySQL, handling errors, and advanced scenarios such as multiple sequential migrations.
The article examines the Chengdu COVID‑19 testing system failure, analyzes its root causes such as oversized MySQL tables and insufficient load handling, and then presents a step‑by‑step high‑concurrency roadmap—including single‑machine, service‑database separation, caching, load balancing, read/write splitting, sharding, hardware and DNS load balancing—to help backend developers design scalable systems.
The September 2023 DB-Engines ranking update shows Oracle’s steep decline, while MySQL and MongoDB gain points, and presents the top ten databases across relational, key‑value, document, time‑series, and graph categories along with the five metrics used to calculate popularity.
This article investigates why DBLE repeatedly logs "no handler" messages by analyzing DBLE and MySQL logs, using Arthas to trace the call chain, reproducing the scenario with timeout settings, capturing network traffic, and explaining the MySQL 8.0.24 error‑packet behavior that leads to the observed logs.
The September DB‑Engines ranking shows Oracle’s steep decline while MySQL and MongoDB climb, presents top‑10 lists for relational, key‑value, document, time‑series and graph databases, explains the five metrics behind the scores, and advises professionals to choose databases based on business needs rather than pure popularity.
This article examines why single-node databases no longer meet modern internet workloads, explores MySQL replication models (master‑slave, asynchronous, semi‑synchronous, group replication), discusses high‑availability solutions such as MHA, MGR and Orchestrator, and outlines vertical and horizontal sharding techniques along with their trade‑offs.
This weekly digest curates top MySQL and database articles, highlights the new SQLE 1.2208.0 release with its features and upcoming plans, and invites community feedback on DBLE, DTLE, SQLE, and TXLE middleware usage.
The SQLE 1.2208.0 release introduces intelligent scanning for Alibaba Cloud RDS MySQL slow logs, adds numerous MySQL audit rules, opens enterprise‑level rules to the community, provides new OceanBase for MySQL audit rules, a reporting statistics page, PostgreSQL analysis support, and several scenario‑based audit enhancements along with many optimizations and bug fixes.
An in‑depth guide explains why using MyBatis’s foreach for bulk inserts can cause severe performance degradation, especially with large tables, and demonstrates how switching to ExecutorType.BATCH or limiting batch sizes to 20‑50 rows dramatically reduces insertion time from minutes to seconds.
This article explains MySQL’s binlog, how it enables master‑slave replication, and explores three practical business uses—data heterogeneity, cache synchronization, and task dispatch—showing how a middleware that pretends to be a slave can leverage binlog events to improve system architecture and performance.
This article explains the features, advantages, drawbacks, and step‑by‑step installation process of a MySQL high‑availability Percona XtraDB Cluster (PXC), covering configuration, firewall settings, SELinux disabling, node initialization, clustering parameters, and verification commands.
This article details a step‑by‑step optimization of a Java backend that imports massive Excel files into MySQL, covering library switches, data caching, batch INSERT statements, and parallel stream insertion to cut processing time from minutes to under two minutes.
This article investigates why MySQL treats strings with trailing spaces as equal during "=" comparisons, explains the underlying PAD SPACE collation rule, and demonstrates how to perform exact matches using LIKE and BINARY operators with practical examples and code snippets.
This article compares the MySQL binlog parsing tools my2sql and binlog2sql, detailing their parameters, performance benchmarks on 2 GB binlog files with varying thread counts, feature differences, usage constraints, and provides practical guidance for selecting the appropriate tool.
When trying to change a table’s primary key from int to bigint, a 500‑million‑row MySQL table required data archiving and a bulk MODIFY, but parallel DELETE statements caused lock‑wait timeouts; experiments reveal that under REPEATABLE READ the range scan locks the boundary row, leading to contention.
This guide demonstrates how to perform asynchronous CRUD operations on MySQL, PostgreSQL, and Redis using Python libraries such as aiomysql, asyncpg, and SQLAlchemy, covering connection setup, querying, inserting, updating, deleting, and handling result objects with practical code examples.
The weekly SQLE newsletter curated by community reporter Yongzheng highlights recent MySQL audit rule additions, Alibaba Cloud RDS slow‑log support, links to several in‑depth technical articles, open‑source progress, and outlines development plans for the coming week.
The SQLE 1.2208.0-pre4 preview release introduces smart scanning for Alibaba Cloud RDS MySQL slow logs, opens a comprehensive set of enterprise MySQL audit rules to the community version, and provides full release information including download links, documentation, and detailed feature lists.
This comprehensive guide explains why distributed locks are needed, outlines their three essential properties, compares common implementations such as Redis, MySQL, ZooKeeper, and Redlock, discusses pitfalls like non‑atomic operations and lock expiration, and presents correct patterns using atomic commands, Lua scripts, watchdogs, and fencing tokens.
The article provides a comprehensive guide to relational database design using MySQL, covering ER modeling, normalization (BCNF and 3NF), storage engine selection, character set and data type choices, as well as index design principles illustrated with a student course selection example.
The article explains why MyBatis foreach‑based batch inserts can cause extreme CPU usage and long execution times, demonstrates how combining multiple VALUES into a single INSERT or using ExecutorType.BATCH dramatically improves performance, and provides practical code examples and sizing recommendations.
This article explains the principles of MySQL semi‑synchronous replication, analyzes how data consistency is maintained during high‑availability failover, presents detailed step‑by‑step transaction flow, discusses scenarios causing GTID divergence, and offers testing methods and remediation techniques for DBA practitioners.
This article investigates a puzzling 13‑hour time discrepancy caused by the MySQL 8.x JDBC driver interpreting the ambiguous CST timezone as America/Chicago, explains the underlying driver code and JDK timezone mappings, and provides multiple practical solutions including server settings, driver parameters, and official fixes.
This article details Huolala's challenges and solutions in governing hybrid‑cloud databases, covering background, pain points, platform architecture, MySQL/Redis/Kafka implementations, automation, cost optimization, and the evolving role of DBAs in the cloud era.
This article explains the fundamentals of relational database design, covering E‑R modeling with a student‑course example, normalization through BCNF and 3NF, selection of MySQL storage engines, character set and data type choices, and best practices for index design.
This article examines common pitfalls and six practical strategies for maintaining data consistency between MySQL and Redis caches, comparing naive approaches with optimal solutions such as cache double‑delete, asynchronous serialization via message queues, and binlog‑driven eventual consistency, and offers recommendations for real‑time and eventual consistency scenarios.
This article explains the limitations of MySQL's max_execution_time for SELECT statements inside stored procedures and provides practical solutions—including external kill scripts and the Percona Toolkit's pt‑kill—to enforce query timeouts while warning against use in production environments.
This article explains why timestamps in MySQL binary logs can appear out of order, detailing how event times are derived from the thread start time, how long‑running transactions and binlog rotation affect ordering, and provides code examples and an experiment to illustrate the behavior.
This article examines the DBLE distributed database middleware cache, reproduces a primary‑key update query that returns incomplete results due to stale routing cache, analyzes cache contents via the management console, explains the cache’s behavior and limitations, and shows how disabling the primary‑key cache restores correct query routing.
This article explains how Flink's Broadcast State feature can be used to dynamically update processing rules and enrich streaming events with user information from MySQL, showing configuration, code examples, key considerations, and runtime results that demonstrate real‑time adaptability without restarting the job.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
This comprehensive guide walks you through MySQL's core architecture, explains why SQL optimization matters, details index types and creation methods, demystifies EXPLAIN output, and provides practical examples and best‑practice tips to dramatically improve query performance.
This article reviews popular free MySQL client tools—including MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, DBeaver, and command‑line utilities—highlighting their key features, platform support, and download links, helping users choose a cost‑effective solution for database management.
This article explains relational database design using MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine and charset choices, data type selection, and index design, illustrated with examples and practical guidelines for building a student course selection system.
The SQLE 1.2208.0-pre3 preview release introduces several new MySQL audit rules, scenario‑based audit mode enhancements, a detailed release notes list, bug fixes, and links to documentation and previous versions, providing a comprehensive overview for database users and administrators.
This article explains how MySQL's auto‑increment primary keys behave when they reach their maximum value, the internal InnoDB row_id mechanism, the generation of Xid and trx_id for transactions, and the handling of thread identifiers, including practical examples and code snippets.
This guide presents comprehensive MySQL naming standards covering database objects, schemas, tables, columns, indexes, views, stored procedures, functions, triggers, constraints, and users, along with design principles for storage engines, character sets, table structures, and SQL query best practices.
This article compares two approaches for keeping MySQL and Redis in sync—using MySQL triggers combined with a UDF to write directly to Redis, and parsing MySQL binlogs (e.g., with Alibaba's Canal) to propagate changes—detailing their workflows, advantages, limitations, and implementation details.
The article investigates why the MySQL client defaults to a non‑root user (zhenxing) despite configuration files showing root, detailing the discovery of the hidden .mylogin.cnf file, the effect of --no-defaults, and how MySQL reads option files in a specific order.
This article demonstrates how Spring's @Transactional rolls back only RuntimeException subclasses, shows the failure to roll back a checked Exception, and explains how to use rollbackFor to ensure proper transaction rollback for all exception types.
This article explains why Percona Xtrabackup backups of MySQL 5.7 can fail with "Too many open files" errors, demonstrates how to reproduce the issue under both non‑root and root users, analyzes the open‑files‑limit settings, and provides practical solutions to adjust system and user limits for successful backups.
This article explains how to collect comprehensive MySQL performance metrics—including connections, buffer cache, locks, statement counts, throughput, server variables, and slow‑query analysis—using only MySQL's native SHOW commands, providing a fast, low‑overhead monitoring solution.
This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article explores a common MySQL InnoDB pitfall where unique indexes fail to prevent duplicate rows, explains how NULL values and logical deletion affect uniqueness, and presents practical strategies—including composite keys, delete status counters, timestamps, hash fields, and Redis locks—to enforce true uniqueness.
A MySQL 5.6+ optimizer bug causes queries that filter by uid and order by id with a small LIMIT to use a costly full table scan instead of the appropriate idx_uid_stat index, and the article explains the root cause, shows optimizer_trace output, and offers two practical work‑arounds.
This article introduces Python's DB‑API, lists supported databases, demonstrates how to install and use PyMySQL for basic CRUD operations, shows techniques for preventing SQL injection, and explains two connection‑pooling models with DBUtils, including code examples and a brief promotional note at the end.
This article explains MySQL's internal architecture and provides twelve practical SQL optimization strategies—including avoiding subqueries, using IN instead of OR, efficient pagination, minimizing ORDER BY, batch inserts, selective column retrieval, proper JOIN handling, and index best practices—to dramatically improve database query performance.
This article explains how MySQL 8.0's InnoDB generates Redo logs, groups them into mini‑transactions, writes them concurrently to the log buffer, and uses log_writer and log_flusher threads together with LSN, slots, and recent_written structures to ensure durable, ordered persistence.
This article explains how MySQL processes LIMIT with an offset, why large offsets cause slower queries and deep‑pagination problems, and presents practical optimization techniques such as id‑based pagination, sub‑queries, and index‑aware strategies to improve performance.
The preview release SQLE 1.2208.0-pre2 introduces a comprehensive report statistics page for the enterprise edition, details the project's purpose, provides resource links, enumerates new feature items such as work‑order metrics and license usage, and lists bug fixes and links to prior versions.
This article demonstrates how MySQL 8.0.29’s ALGORITHM=INSTANT enables instant column addition, deletion, and row‑size checks at any position in large tables, compares performance with previous versions, and discusses related XtraBackup backup considerations.
This article demonstrates how Spring's @Transactional annotation rolls back transactions only for RuntimeException subclasses, shows practical MySQL update tests that trigger ArithmeticException, and explains how to configure rollback for checked exceptions using rollbackFor, with complete code examples.
This article examines various multi‑table join scenarios in MySQL, demonstrating when partition tables can accelerate queries and when they degrade performance, supported by concrete examples, execution times, and explain plans to guide proper partition key usage.
This article explains the risks of misusing the sql_slave_skip_counter parameter in MySQL replication, demonstrates a step‑by‑step test environment with both transactional and non‑transactional tables, analyzes the resulting binlog events, and provides practical methods to reset the counter safely.
This article demystifies MySQL's Multi‑Version Concurrency Control (MVCC) by explaining its Undo Log and Read View mechanisms, showing how InnoDB implements read‑committed and repeatable‑read isolation without locking, and illustrating the process with clear examples and diagrams.
This article examines the challenges of running MySQL in Docker containers, highlighting data safety concerns, I/O performance bottlenecks, statefulness issues, and resource isolation limits, while also outlining scenarios where containerization can work and offering practical mitigation strategies.
The article explains why caching is essential for high‑traffic read‑heavy services, analyzes the consistency problems that arise when data exists simultaneously in MySQL and Redis, compares four cache‑update strategies, and provides practical recommendations such as using expiration, delayed double‑delete, message queues, and binlog subscription to achieve eventual consistency.
This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.
This article explains why SQL performance becomes a bottleneck as data grows, outlines a systematic optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, tracing, and index tuning—and illustrates each step with concrete MySQL examples and best‑practice recommendations.
This article examines a MySQL InnoDB pitfall where a unique index fails to block duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents practical solutions such as adjusting index columns, adding timestamps, delete status counters, hash fields, and proper bulk‑insert strategies.
The August 2023 DB‑Engines ranking shows Oracle’s steep 19.5‑point drop, while MySQL and MongoDB surge, PostgreSQL continues rising, and the top‑10 list highlights shifts across relational, key‑value, and document databases, with methodology based on search, job, and community metrics.
The article analyzes why a coupon‑distribution feature can issue more coupons than available under heavy load, explains the root cause of concurrent stock deductions, and presents four practical solutions—including Java synchronized blocks, SQL row‑level locking, Redis distributed locks, and Redisson—to reliably prevent over‑issuance.
This comprehensive guide explains MySQL's native JSON data type, its advantages over text storage, detailed CRUD operations, indexing strategies, migration from TEXT columns, the powerful Partial Updates feature, performance benchmarks, and a wide range of JSON functions with practical examples.
The SQLE 1.2208.0-pre1 release introduces a preview version of the SQL audit tool with project details, new OceanBase for MySQL audit rules, PostgreSQL plugin SQL analysis, a complete list of release notes, optimizations, and bug fixes, all aimed at enhancing database security and usability.
This article explains why using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves query performance, outlines the trade‑offs such as readability, shows MySQL conversion functions, discusses IPv6 alternatives, and provides Java code for converting between string and numeric representations.
The article explains how to query sales statistics for multiple business units and their associated products in MySQL, compares several implementation strategies such as looping, OR‑concatenation, mixed filtering, and finally adopts the SQL‑92 row‑by‑row comparison technique to achieve a single‑query solution that respects indexing and corporate coding standards.
The article investigates why MySQL Router 8.0.29/8.0.30 reports “ERROR 1040 (HY000): Too many connections to MySQL Router” even when the backend MySQL server has only a few connections, reproduces the issue, analyzes packet traces, and concludes that the problem exists in those versions but not in 8.0.28 or 8.0.21, likely due to a new connection‑pooling feature.
This article explains why a unique index on a MySQL InnoDB table may still permit duplicate rows, especially when indexed columns contain NULL values or when logical deletion is used, and presents practical solutions such as adjusting index fields, adding timestamps, hash columns, or redesigning delete logic.
This article explains the new online REDO log size adjustment feature introduced in MySQL 8.0.30, compares it with the cumbersome pre‑8.0.30 procedure, and provides step‑by‑step commands and code snippets for both legacy and modern methods.
This guide explains why slow SQL hurts performance, prioritizes remediation, details MySQL's execution phases, shows how to enable and read slow‑query logs, provides a real‑world analysis example, and lists concrete optimization techniques to make queries run faster.
This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.
This article explains the complete MySQL execution flow, covering the connector's duties, permission tables, caching behavior, parsing of SQL statements, optimizer decision‑making, executor interaction with storage engines, processlist states, logical query order, and practical tips for ordering WHERE‑clause conditions.
This article explains the new MySQL 8.0.30 feature that automatically generates invisible primary keys for InnoDB tables without explicit primary keys, demonstrates how to enable and use it, discusses DDL and replication implications, and outlines related limitations and backup considerations.
This tutorial demonstrates how to set up MySQL master‑slave replication using Docker, configure MyBatisPlus and ShardingSphereJDBC for read/write splitting, and integrate the setup into a Spring Boot application with Druid, code generation, and REST endpoints for testing.
This article investigates a MySQL crash triggered by a specific SELECT statement, analyzes the stack trace and optimizer behavior that leads to an invalid memory access, and presents three practical solutions including disabling DuplicateWeedout, upgrading MySQL, and normalizing table character sets.
The article explains why MySQL sometimes chooses a full table scan instead of using an available index when a query includes ORDER BY id ASC LIMIT 1, demonstrates the optimizer's cost calculations with optimizer_trace, and offers two practical work‑arounds to force the optimal index usage.
This weekly newsletter curates top community technical shares covering MySQL 8.0.30 GA, master‑slave replication recovery, InnoDB parameters, Linux I/O optimization, two‑phase commit, Prometheus‑Grafana monitoring pitfalls, as well as the latest SQLE 1.2207.0 release, development progress, and upcoming plans.
This tutorial explains how to configure MySQL master‑slave replication using Docker, set up read‑write splitting with ShardingSphereJDBC, integrate MyBatis‑Plus, and build a Spring Boot application that demonstrates write operations on the master and read operations on the slave.
This article examines frequent MySQL performance pitfalls—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down failures—and demonstrates how rewriting queries with proper indexes, JOINs, early range reduction, and WITH statements can dramatically reduce execution time.
This article explains how to identify and resolve performance bottlenecks in MySQL by locating slow queries, analyzing execution plans with EXPLAIN, focusing on type, rows and extra information, using profiling and trace tools, and applying practical optimization techniques illustrated through multiple real‑world cases.
This article walks through a real MySQL deadlock scenario, explaining the four necessary deadlock conditions, illustrating how Next‑Key locks on non‑unique indexes cause gap locks, and presenting step‑by‑step analysis, experimental verification, and practical recommendations to avoid such deadlocks in production systems.
This article explains how Dble validates its configuration files during startup, lists the main configuration file types, and walks through typical startup failure examples—such as port conflicts, syntax errors, and backend MySQL connectivity issues—providing step‑by‑step troubleshooting guidance for new users.
