This article explains why MySQL's count(*) can become a performance bottleneck, especially with InnoDB, and presents practical optimization techniques such as Redis caching, second‑level in‑memory caches, parallel execution, reducing unnecessary joins, and using column‑store databases like ClickHouse.
The article examines MySQL partition tables that lack primary keys, compares query performance against tables with primary keys, and provides guidance on index design for such partitioned tables, concluding that partition keys need not be included in indexes.
This article explains why MySQL's count(*) can be slow on InnoDB, compares different count variations, and presents practical optimization techniques such as Redis caching, second‑level caches, multithreading, reducing joins, and using ClickHouse for massive datasets.
This article walks through the complete lifecycle of a MySQL UPDATE statement, covering InnoDB page structure, tablespaces, the buffer pool, redo and undo logs, binlog, and the two‑phase commit process, illustrating each step with examples and command outputs.
This article explains MySQL’s internal execution flow, covering the connector’s role, permission verification, caching, parsing, optimization, execution stages, processlist states, SQL execution order, and best practices for arranging WHERE‑clause conditions to improve query performance.
This article explains how to transform a simple personal‑blog backend into a high‑performance, horizontally scalable system by introducing caching with Redis, load balancing with Nginx/LVS, read/write separation, database sharding, and modern NewSQL solutions such as TiDB, while also discussing related challenges like cache consistency and data migration.
This article analyzes a MySQL InnoDB lock wait timeout caused by batch INSERT operations, examines configuration parameters and slow‑query diagnostics, and presents a rewritten SQL solution that reduces execution time from over 100 seconds to under one second.
The article presents a detailed performance comparison between auto‑increment integer primary keys and UUID string primary keys in MySQL, including table definitions, 5‑million‑row and 10‑million‑row test results for read, range, write, backup, and storage efficiency, and offers practical recommendations for choosing the appropriate key strategy in distributed environments.
This article investigates a historical MySQL bug where dropping large tables with a sizable buffer pool and Adaptive Hash Index (AHI) can cause severe latency, presents test configurations for 5.7.29 and 8.0.28, compares AHI‑on/off results, shows stack traces, and explains the lazy‑delete fix introduced in 8.0.23.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters such as emojis, demonstrates the resulting insertion error, and shows how switching to the utf8mb4 charset resolves the issue while also covering the historical reasons behind MySQL's limited utf8 implementation.
This article explains MySQL's crash recovery process, covering how the doublewrite buffer repairs corrupted pages, how redo logs are applied to restore data, and how undo tablespaces and unfinished transactions are handled during server startup.
This article explains MySQL's client‑server architecture and walks through the three main stages—connection handling, query parsing and optimization, and storage engine selection—detailing protocols, authentication, cache behavior, optimizer mechanics, and engine characteristics with practical commands and examples.
A MySQL DBA recounts a 4‑TB InnoDB instance that stalled during startup, analyzes top, stack traces, and source code to pinpoint a bug involving truncate operations and the buf_flush_event, then presents three practical solutions—including adjusting innodb_flush_sync, using GDB to break the wait loop, or removing truncate logs—to restore normal operation.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters like emojis, demonstrates the resulting insert errors, and shows how switching to the utf8mb4 charset resolves the issue while providing a brief history of MySQL's encoding decisions.
This article explains why traditional ORDER BY … LIMIT pagination becomes slow on tens of millions of rows, analyzes the underlying index scan cost, and presents two practical optimizations—delayed join using covering indexes and a bookmark‑based approach—showing how they can cut execution time to a third or even a tenth of the original.
This article explains how MySQL executes ORDER BY queries, compares the default sorting algorithm with the rowid method, and provides practical optimization steps such as indexing, adjusting sort_buffer settings, and choosing the appropriate algorithm based on data size and memory.
This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
This article explains why MySQL’s legacy utf8 charset only supports three‑byte characters, causing emoji insertion failures, and shows how switching tables, columns, and the database to utf8mb4 resolves the issue while detailing the historical reasons behind MySQL’s limited utf8 implementation.
This weekly roundup curates top database‑related technical articles from the MySQL community, shares the latest SQLE audit tool release and development progress, and invites readers to contribute feedback and questions about DBLE, DTLE, SQLE, and TXLE middleware.
This article explains how MySQL master‑slave replication enables read/write splitting, details the asynchronous replication process, discusses latency and its impact on business logic, and presents practical mitigation strategies along with middleware options for accessing replicated databases.
This article explains why high‑concurrency big‑data systems need database sharding, describes table‑level partitioning methods (modulus and range), shows how to implement them with MySQL and Java code, compares vertical and horizontal database splitting, and evaluates middleware solutions such as Sharding‑JDBC and Mycat.
This article presents a systematic approach to transform MySQL slow‑query handling from reactive troubleshooting to proactive risk management by defining a scoring model, selecting key metrics, calculating weighted risk indices, and testing the model to prioritize and reduce harmful queries.
This article explains how to handle billions of order records by classifying data into hot and cold tiers, using MySQL sharding with database‑and‑table partitioning, storing cold data in Elasticsearch and Hive, and outlines combined routing formulas for scalable backend architecture.
This article walks through a step‑by‑step investigation of why incremental backups failed on a DBaaS platform, revealing that the PERCONA_SCHEMA.xtrabackup_history table’s binlog_pos column was too short, and shows how altering its length resolves the problem.
This announcement introduces the MySQL DBA Elite Practical Course book, detailing its comprehensive coverage of core and advanced MySQL technologies, and outlines a limited-time giveaway event where readers can win copies by following the community’s participation rules.
This article walks through a real‑world MySQL slow‑query case, detailing analysis, index checks, character‑set alignment, forced index usage, and IN‑clause considerations that together reduced a 5‑second query to under one second.
This article explains how MySQL auto‑increment primary keys, InnoDB hidden row_id, transaction Xid, InnoDB trx_id, and the server thread_id behave when they reach their numeric limits, the consequences for data integrity, and recommended mitigation strategies.
This comprehensive guide outlines five common MySQL performance problems, a step‑by‑step investigation methodology, detailed Java middleware and database analyses, practical tuning tactics such as index optimization and sharding, and a governance framework for sustainable performance management.
This article walks through a simulated MySQL 3307 cluster failure, examines Orchestrator's source code to explain the ContinuousDiscovery loop, discovery queues, health ticks, caretaking tasks, raft coordination, topology snapshots, and the logic distinguishing UnreachableMaster from DeadMaster states.
This guide explains how to enable and read MySQL's slow query log, use EXPLAIN, profiling, and optimizer trace to pinpoint inefficient SQL, and presents ten classic problem patterns—such as implicit conversion, left‑most prefix violations, deep pagination, large IN lists, and file‑sort order‑by—with concrete code examples and practical optimization steps.
The article explains why a recent write may be invisible when reading from a MySQL replica, analyzes the replication threads and latency causes, and presents practical solutions such as forcing master reads, lag detection, semi‑sync mode, and GTID‑based waiting, with examples from Sharding‑JDBC, MyCat and MaxScale.
This article explains the fundamentals of MySQL indexing, detailing how InnoDB pages, B+‑tree structures, clustered and non‑clustered indexes, and disk prefetching work together to reduce I/O operations and dramatically improve query performance.
This article provides a comprehensive technical overview of MySQL's underlying index structures, SQL execution plan analysis, transaction isolation levels, MVCC concurrency control, and the InnoDB buffer pool mechanism, illustrating each concept with diagrams and practical SQL examples.
This article explains how MySQL performs implicit type conversion during queries, demonstrates the unexpected results caused by joining mismatched column types, outlines the conversion rules, and provides practical tips to avoid performance degradation and incorrect data retrieval.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
This article explains MySQL's native data encryption capabilities, detailing the AES_ENCRYPT/AES_DECRYPT functions, their parameters, storage considerations, example usage, and best‑practice recommendations for secure and efficient encryption of sensitive fields.
This article describes the layered architecture of Qunar's next‑generation database automation platform, outlines the design goals for secure Agent/Plugin‑Server communication, compares communication and encryption methods, and details the authentication and request flow using symmetric encryption and token‑based security.
This article examines why a MySQL InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies such as adjusting delete status, adding timestamp or auxiliary ID fields, using hash columns, and leveraging proper bulk‑insert techniques to enforce uniqueness effectively.
The article walks through a MySQL MGR failover incident, analyzes the InnoDB assertion failure shown in the error log, pinpoints the problematic code at row0pread.cc line 727, compares the fix in MySQL 8.0.28, and references the related bug report for future troubleshooting.
This article explains how MySQL uses indexes during JOIN operations, compares indexed and non‑indexed join execution, introduces Index Nested‑Loop Join and Block Nested‑Loop Join algorithms, discusses the role of the join buffer, and provides practical guidance on choosing the optimal driving table for efficient query performance.
This article investigates a recurring MySQL CPU‑100% alarm caused by a slow SELECT with ORDER BY id ASC LIMIT, explains why the optimizer mistakenly chooses the primary‑key index over a suitable composite index, and presents six practical experiments—including force‑index, ORDER BY tweaks, and LIMIT adjustments—that reliably restore optimal index usage.
The e‑commerce platform’s order‑query alerts were caused by MySQL master‑slave replication lag—specifically, delayed redo‑log flushing let the slave receive binlog events before the master committed—so queries returned empty, a condition mitigated by retry logic, delayed MQ delivery, or emitting post‑commit business events.
This article details the investigation and resolution of a MySQL 8.0.21 client segmentation fault on CentOS 8.4, covering missing ncurses libraries, implicit function declaration warnings, pointer truncation on 64‑bit systems, core‑file generation, gdb analysis, and the steps required to rebuild the client without crashes.
This article explains when read/write splitting is needed, details MySQL master‑slave replication mechanics, outlines middleware design choices, and provides practical monitoring and mitigation strategies for replication lag, helping developers decide and implement effective database scaling.
This article explains MySQL’s three‑tier logical architecture—from client services to core query processing and storage engines—and details the four ANSI SQL transaction isolation levels, highlighting their behavior, advantages, and trade‑offs in MySQL.
The SQLE 1.2210.0 release introduces integrated CloudBeaver support for multiple database types, new MySQL audit rules, enhanced smart‑scan filtering, independent menus for scan tasks, along with numerous optimizations and bug fixes, and provides full release information and acknowledgments.
The article explains why a seemingly non‑conflicting UPDATE on a secondary index can cause lock‑wait alerts under RC isolation, detailing the missing end‑range condition push‑down, the InnoDB row‑search flow, semi‑consistent read behavior, and differences between primary‑key and secondary‑index locking.
Shopee’s scalable fund‑routing decision engine combines a configurable Go‑based rule engine with Redis‑backed, Lua‑driven quota control to dynamically match loan orders to external capital providers, enforce multi‑dimensional volume limits, achieve tens of thousands TPS, and provide extensible risk‑management capabilities across markets.
MVCC (Multiversion Concurrency Control) in MySQL InnoDB uses undo logs and read views to provide non‑locking snapshot reads, managing read‑write, write‑write, and read‑read concurrency, with implicit row fields and transaction IDs determining visibility across isolation levels.
The article explains how a MySQL 5.7 service fails to start on a CentOS 7.9 server because SELinux blocks write access to err.log, walks through log inspection, strace tracing, SELinux context analysis, and provides a simple fix by disabling SELinux.
This article documents practical experiments comparing four MySQL bulk‑insert techniques—single‑row loop, concatenated SQL, MyBatis‑Plus saveBatch, and loop with batch mode—in a SpringBoot environment, revealing the most efficient approach and key configuration tips.
This article explains how to achieve database read‑write separation in high‑concurrency Java applications by configuring master and slave data sources, implementing a routing DataSource, managing context with ThreadLocal, defining a custom @DataSourceSwitcher annotation, and applying AOP to switch between read and write operations.
This article explains the functional differences between the run_sql and sql commands in MySQL Shell, demonstrates how to execute DDL/DML statements, bind variables, and retrieve results using both commands, and provides guidance on selecting the appropriate function for various use cases.
This article introduces Debezium, an open‑source low‑latency change data capture platform that streams database row changes via Kafka, explains its architecture and common scenarios such as cache invalidation and CQRS, and provides step‑by‑step Docker commands to install ZooKeeper, Kafka, MySQL and the Debezium connector.
This article presents a comprehensive set of MySQL optimization techniques, covering everything from avoiding SELECT * and OR conditions to proper index usage, join strategies, batch operations, and query planning, all aimed at reducing resource consumption, improving execution speed, and ensuring reliable database design.
The article examines three approaches to handling inventory deduction in e‑commerce—single‑field MySQL, sharded MySQL rows, and Redis incrby—analyzes their concurrency drawbacks, and presents a robust Redis‑Lua script solution with distributed locking and Java implementation details.
Facing a surge in MySQL slow queries, Ctrip’s senior database engineers detail a comprehensive approach—including refined SQL release processes, deep execution‑plan analysis, index tuning, query rewriting, and resource management—to systematically identify, diagnose, and eliminate performance bottlenecks in large‑scale hotel services.
This article explains MySQL's three concurrency types—read‑read, read‑write, and write‑write—describes how InnoDB uses shared/exclusive locks, MVCC snapshot reads, and ReadView metadata to ensure transaction isolation, and provides practical examples and lock‑based solutions for avoiding update loss.
After a write to the MySQL master, reads from a slave can return old or missing data due to replication lag, and this article explains the underlying asynchronous replication mechanisms, the involved threads, and presents practical solutions such as forcing master reads, delay detection, semi‑sync mode, GTID waiting, and middleware implementations like Sharding‑JDBC, MyCat, and MaxScale.
This article explains why a unique index in MySQL can still allow duplicate rows when indexed columns contain NULL, explores the challenges of adding unique indexes to logically deleted tables, and presents practical solutions such as incremental delete status, timestamps, extra IDs, hash fields, and proper batch insertion techniques.
This article explains how to implement fast nearby POI queries by calculating distances with the Haversine formula, designing MySQL tables and indexes, applying GeoHash for dimensionality reduction, and leveraging Redis Geo for high‑concurrency scenarios, while also providing Go code examples for each step.
The SQLE 1.2210.0-pre2 release introduces CloudBeaver integration for multiple database types, adds new MySQL audit rules, provides several UI optimizations, and resolves critical bugs, offering a more comprehensive and secure SQL auditing experience for both community and enterprise users.
This article presents a detailed performance test of inserting massive data into MySQL with Java, comparing three strategies—MyBatis lightweight insertion without transactions, direct JDBC handling with and without transactions, and JDBC batch processing—showing timing results for millions of rows and offering practical recommendations for high‑throughput data loading.
This article describes Ctrip's practical approach to identifying and optimizing MySQL slow queries, covering background, improved SQL review workflow, execution plan analysis, common index problems, query rewriting, pagination issues, resource contention, and best‑practice recommendations for long‑term performance.
This article explains how to choose storage options, design sharding keys, apply partitioning strategies, modify business code, handle historical data migration, and plan future scaling for large‑scale e‑commerce systems using MySQL, NoSQL, and NewSQL solutions.
This article explains the evolution from single‑node MySQL databases to master‑slave replication, various replication modes, high‑availability strategies, and both vertical and horizontal sharding techniques, while discussing the associated challenges such as distributed transactions, routing, and operational complexity.
This article explains why single‑node databases cannot keep up with rapid business growth and describes MySQL replication methods, high‑availability solutions such as MHA and MGR, and the challenges and techniques of vertical and horizontal sharding for large‑scale systems.
The article explains MySQL's continued dominance in the database market, presents recent developer survey statistics, describes its three‑layer logical architecture, and introduces the new edition of the "High Performance MySQL" book as a guide for modern database professionals.
This article explains MySQL collations and character sets, shows how to view and set them, and demonstrates through test cases that while data insertion is unaffected by collation, query results are influenced by the collation defined on columns or the connection.
This article presents a comprehensive set of MySQL best‑practice guidelines—including avoiding SELECT *, steering clear of OR in WHERE clauses, preferring numeric over string types, using VARCHAR instead of CHAR, limiting DELETE/UPDATE, leveraging proper JOIN types, and many other indexing and query‑execution tricks—to dramatically improve query speed, reduce resource consumption, and maintain healthy database design.
This weekly newsletter curates top community tech shares, featuring MySQL 8.0 updates, database localization insights, concurrency consistency strategies, a new SQL auditing tool release, development progress, upcoming plans, and invites community feedback on DBLE, DTLE, SQLE, and TXLE middleware.
In an exclusive interview, MariaDB founder Michael "Monty" Widenius discusses the rapid growth of China’s database industry, the rise of open‑source adoption, competition with cloud providers, and strategic plans for expanding MariaDB’s presence in the Chinese market.
The SQLE 1.2210.0-pre1 preview release introduces new MySQL audit rules prohibiting rename/change operations and requiring LIMIT clauses, adds filter options to the intelligent scan task list, refines work‑order progress styling, and provides a detailed list of features, optimizations, and bug fixes.
The article explains why transaction isolation is needed, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads and phantom reads, outlines the ACID properties of a transaction, and details MySQL's four isolation levels with their effects and default settings.
This article explains how MySQL 8.0's prefer_ordering_index optimizer switch can be toggled to improve LIMIT queries that involve ORDER BY and GROUP BY, demonstrates the effect with test data, and shows why manual parameter control can yield better execution plans.
This article explains the different layers—encoding, framework, driver, proxy, and implementation—through which database sharding can be introduced, compares driver‑level and proxy‑level middleware, outlines their characteristics, common constraints, and provides a step‑by‑step process for planning, preparing, and executing a sharding project.
This article explains MySQL's execution order and presents eight practical optimizations—including smarter LIMIT usage, implicit conversion handling, join‑based updates, mixed sorting, EXISTS rewriting, condition push‑down, early range reduction, and intermediate result push‑down—each illustrated with SQL examples and performance measurements.
This article explains the logical execution order of SQL statements, highlights common performance pitfalls such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and demonstrates how to rewrite queries with JOINs, early range reduction, CTEs, and other optimizations to achieve dramatically faster execution times.
This article explains how MySQL 8.0.31 introduces native INTERSECT and EXCEPT set operators, demonstrates how to achieve the same intersection and difference results in MySQL 5.7 using joins, and shows usage of the ALL modifier for preserving duplicate rows.
This guide walks through installing NFS on Kubernetes nodes, creating a shared directory, configuring exports, setting up PersistentVolume and PersistentVolumeClaim objects, and deploying a MySQL instance that uses the NFS-backed storage.
This guide explains how to deploy and configure MariaDB MaxScale as a database proxy to achieve MySQL read/write splitting, covering topology design, user creation, installation steps, configuration files, service setup, and verification of routing behavior.
The article analyzes why MySQL LIMIT pagination becomes slower as the offset grows, demonstrates the performance impact with sample queries on a 500,000‑row table, and presents three optimization strategies—using ordered primary keys, subqueries, and join‑based pagination—to reduce full‑table scans and improve query speed.
This guide explains how to gather extensive MySQL monitoring data—including connections, buffer cache, locks, SQL activity, statement counts, throughput, server variables, and slow‑query analysis—solely with MySQL's native SHOW statements, providing low‑overhead, real‑time insight for database administrators.
This article explains MySQL's two‑layer storage engine architecture, details how the server layer prepares record metadata, and walks through a concrete InnoDB table example to show the exact memory layout and data‑exchange process between the server and storage engine.
The article analyzes a MySQL MGR cluster failover incident caused by a VM pause and system time jump, explains the underlying detection mechanisms, presents detailed source code excerpts, and demonstrates through debugging that time changes alone do not trigger MGR failover.
This article explains how MySQL determines default values for TIMESTAMP columns, the effect of the explicit_defaults_for_timestamp parameter, when zero‑date values are permitted via sql_mode, and demonstrates the behavior through four test scenarios with corresponding SQL statements and results.
This article analyzes why MySQL LIMIT pagination becomes slower on large tables, demonstrates the performance impact with real query timings, explains the underlying full‑table scan behavior, and presents three practical optimization strategies—including ordered primary‑key scans, sub‑queries, and join‑based approaches—to improve query speed.
The article explains why MySQL users created with passwords containing special characters like "$" or "#" may fail to log in, analyzes how shell quoting affects the stored password, demonstrates the problem with scripts and login‑path, and provides practical work‑arounds and version notes.
This article provides a comprehensive guide on using Flink CDC with Debezium to capture MySQL changes, covering serialization, adding dependencies, configuring SQL client and Java/Scala APIs, creating source and sink tables, enabling checkpoints, and streaming data into a Hudi data lake.
This article explains how MySQL’s InnoDB storage engine, page size, B+‑tree index structure, and primary‑key limits combine to make roughly 20 million rows the practical ceiling for a single table, and shows how to generate massive test data and tune server settings.
This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
This article analyzes a large MySQL table with frequent SLA alerts, demonstrates how to identify slow queries using pt‑query‑digest, proposes index redesign and online DDL versus pt‑osc methods, and shows testing, implementation, and batch‑delete techniques to dramatically reduce query time and maintenance overhead.
This article explains how to perform full, incremental, and binlog‑based data migrations, compares their trade‑offs, and introduces distributed transaction models such as XA, BASE, TCC, and AT, helping developers choose the right strategy for consistency and performance.
This article explains why a MySQL InnoDB table with a unique index can still contain duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies, including schema changes and alternative locking mechanisms, to enforce true uniqueness.
This article explains how to enable and configure MySQL's slow query log, set appropriate thresholds, use EXPLAIN to analyze execution plans, and apply index optimizations to dramatically reduce query execution time for large tables.
This guide presents 21 practical MySQL table design guidelines covering naming conventions, field types, primary key strategies, indexing, logical deletion, common columns, storage engines, character sets, time types, relationship modeling, sharding considerations, and SQL optimization tips for robust backend development.
The article outlines common database and cache failures—such as instance outages, replication lag, data corruption, and cache avalanches—while detailing Bilibili’s mitigation strategies including high‑availability architectures, scaling, multi‑active designs, proxy controls, slow‑query alerts, fault‑injection drills, and ongoing resilience improvements.
This article explains how MySQL's count() works across different storage engines, compares the performance of various count() forms, and offers practical strategies—such as using EXPLAIN rows, auxiliary count tables, and batch processing—to obtain accurate or approximate row counts efficiently even on massive tables.
The article details the refactoring of memory monitoring in MySQL 8.0.28, introducing a new PFS metadata structure for precise tracking of memory allocations and deallocations across various components like Buffer Pool and user variables.
This article explains the difference between ON and WHERE clauses in MySQL LEFT JOIN, illustrates why a proposed optimization for DBLE sharding does not work, and provides practical examples and pseudo‑code to clarify how MySQL processes left joins.
