This article documents a MySQL read‑write splitting architecture, investigates an unexpected slave‑node crash, and provides a step‑by‑step guide to using Keepalived for monitoring and automatically restarting the MySQL container to achieve high availability of the replica.
This article presents a comprehensive set of MySQL performance guidelines, including avoiding SELECT *, preferring specific columns, eliminating OR in WHERE clauses, using numeric types, choosing VARCHAR over CHAR, applying proper JOINs, leveraging LIMIT, UNION ALL, composite indexes, and other practical tips to improve query efficiency and resource usage.
This article compiles fifteen typical scenarios where MySQL indexes become ineffective—covering composite index left‑most matching, SELECT *, arithmetic on indexed columns, functions, LIKE patterns, type conversion, OR/AND logic, range queries, ORDER BY, and optimizer decisions—providing example SQL, EXPLAIN output, and practical tips to help developers prevent costly full‑table scans.
This article walks through a real‑world incident where an update to a MySQL row was not visible to a subsequent read, explains how SkyWalking tracing revealed the exact timing of binlog writing, MQ delivery, and query execution, and offers practical mitigation strategies.
This article explains how to add unique indexes to large MySQL tables using Online DDL, pt‑osc, and gh‑ost, compares their advantages and drawbacks, outlines common data‑loss risks, and provides practical hook scripts and best‑practice recommendations to mitigate those risks.
This article details the background, migration options, and step‑by‑step automated procedures used by a large‑scale e‑commerce platform to safely move over 400 MySQL clusters, comparing expansion‑plus‑master‑slave switching with cascading replication and explaining the chosen fast, reliable solution.
This article explains why large MySQL tables cause slow queries, analyzes table size, disk usage, and instance capacity, and presents three practical solutions—table partitioning, database sharding (horizontal/vertical), and hot‑cold data archiving—helping developers choose the appropriate strategy for their workloads.
This article walks through a hands‑on process for detecting problematic SQL statements, interpreting execution plans, and applying concrete optimizations such as index adjustments and data‑type fixes to dramatically improve query performance, illustrated with a real MySQL case study.
This article explains how to identify slow SQL statements, analyze execution plans with EXPLAIN, use profiling and optimizer trace, and apply practical solutions such as index optimization, query rewriting, and alternative storage, illustrated with ten real‑world MySQL case studies.
After a month of testing a MySQL dual‑master high‑availability setup, the team encountered numerous replication pitfalls and decided to migrate to a simpler master‑slave architecture, detailing the reasons, differences, and a concrete manual conversion procedure.
To efficiently process massive MySQL result sets without exhausting JVM memory, this article explains three query strategies—regular pagination, stream-based retrieval using MyBatis cursors, and cursor-based fetching with configurable fetchSize—detailing their implementations, advantages, and practical considerations.
This article explains how to prepare and tune MySQL SQL statements before execution, covering logical and physical optimization, table and view analysis, join‑key checks, query rewriting, and execution‑plan evaluation to achieve faster, more efficient database queries.
This article explains how InnoDB uses undo segment states for crash‑safe recovery, details the XA transaction protocol, describes bugs in earlier MySQL versions, and shows how MySQL 8.0.30 introduces a new undo state and revised prepare order to make external XA transactions crash‑safe, including code examples and recovery steps.
This article details a comprehensive approach to optimizing a large MySQL table, including slow‑query analysis with pt‑query‑digest, index redesign, backup and restore using mydumper, online DDL versus pt‑osc, and batch deletion techniques to reduce latency and storage fragmentation.
This article introduces the open‑source sync‑diff‑inspector tool, explains how to download and install it, and provides detailed configuration and command‑line examples for schema‑level, table‑level, and range‑based data validation between MySQL instances, including automatic SQL repair generation and common troubleshooting tips.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
This article recounts a real‑world investigation of a MySQL high‑availability outage where HikariCP connections appeared to dead‑lock, detailing the architecture, observed symptoms, code analysis, thread‑dump findings, and the final fix of adding socket timeout parameters to the JDBC URL.
This guide explains how to decouple business logic in a SpringBoot application by using Canal to listen to MySQL binlog changes, forwarding those events through RabbitMQ, and processing them with a Java client to record both new and old data for insert, update, and delete operations.
Ctrip evaluated and extended OceanBase Migration Assessment tools, built a one‑click migration workflow, implemented comprehensive monitoring and automatic fault‑diagnosis pipelines, and addressed compatibility challenges such as .NET charset issues and Druid parser errors, ultimately achieving a smooth MySQL‑to‑OceanBase transition.
The article advises against using MySQL subqueries and joins for large datasets, recommending single‑table queries merged in application code, and outlines the benefits, scenarios, drawbacks, and alternatives for application‑level data association while also noting when joins can still be useful.
This article compiles frequent Flink job submission problems on YARN—including WordCount jar errors, HBase dependency conflicts, MySQL timeout, checkpoint restoration failures, parallelism limits, and unexpected container termination—provides root‑cause analysis and step‑by‑step remediation instructions.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause MySQL Next‑Key lock deadlocks, explains the underlying gap‑lock and row‑lock mechanisms, demonstrates practical experiments, and proposes strategies such as postponing locks or using primary‑key updates to avoid such deadlocks.
The article describes how a large‑scale membership system serving over a billion users achieves high performance and availability through dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster designs, Redis caching with distributed locks, and a seamless migration from SQL Server to sharded MySQL, while also detailing operational safeguards and fine‑grained flow‑control strategies.
This article explains how to assess MySQL table size, why massive tables degrade performance due to B+‑tree depth, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—along with guidance on selecting the appropriate approach.
This article explains MySQL's file‑sort mechanism in depth, covering the sort buffer, handling of long sort keys, three sort modes, priority‑queue and read‑rnd‑buffer optimizations, internal vs. external sorting, descending sort implementation, and how to inspect details with optimizer trace.
This article details the background, migration plan selection, and step‑by‑step procedures—including pre‑building cascades, service pause, automated batch operations, cluster tiering, pre‑ and post‑checks, and gray‑scale validation—to achieve a fast, stable MySQL data‑center migration for a large‑scale production environment.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
Discover 18 essential MySQL 8.0 management enhancements—including persistent global variables, admin IP/port settings, minimal installation packages, resource groups, read‑only databases, improved SHOW PROCESSLIST, instant DDL, faster DROP/TRUNCATE, OS thread visibility, connection memory limits, enriched slow‑log details, backup options, Clone plugin, upgrade process, client defaults, and RESTART command usage.
This article examines eight frequent SQL anti‑patterns—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, premature data reduction, and intermediate result handling—explaining why they degrade performance and providing concrete rewrite strategies and code examples to dramatically speed up queries.
This guide introduces Sequel Pro, explains how to configure Standard, Socket, or SSH connections with required fields, describes the main interface modules for browsing schemas, tables, relationships, triggers, and running queries, and highlights useful shortcuts for efficient MySQL management.
This comprehensive guide outlines 46 practical MySQL optimization rules—from understanding the query execution flow and naming conventions to index design, join strategies, pagination techniques, and safe use of data types—providing concrete examples and code snippets that help developers dramatically improve database performance and reliability.
This 2023 comparison examines MySQL and PostgreSQL across history, features, performance, scalability, cost, and migration trends, helping developers decide which relational database best fits their specific application requirements.
In MySQL 5.7 replication, disabling unique_checks can cause duplicate‑key errors on a unique index that is not a primary key, leading to replication stalls, and this guide explains the root cause, temporary and permanent fixes, and step‑by‑step reproduction instructions.
The article explains why an UPDATE on the same row in MySQL can block in one transaction but not in another, by analyzing the types of row‑level locks (record, next‑key, gap) acquired during SELECT … FOR UPDATE, normal updates, and updates that modify primary‑key values, illustrating the lock behavior with B+‑tree examples.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—shows how they interact, and provides practical guidance for avoiding deadlocks and safely updating rows.
This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.
This guide shows how to decouple change‑recording logic in a SpringBoot application by using Alibaba Canal to listen to MySQL binlog events, forwarding them through RabbitMQ, and persisting both new and old row data for insert and update operations.
This article explains how DBAs can improve MySQL query performance by applying logical and physical optimizations, analyzing table structures, indexes, and execution plans, and provides a step‑by‑step checklist for diagnosing and rewriting slow or poorly optimized SQL statements.
This article explains how to determine the optimal order of columns in MySQL composite indexes by analyzing B+Tree structure, index selectivity, and real‑world query performance, providing practical examples and SQL demonstrations to help developers create more efficient indexes.
This article describes how a membership platform serving over ten billion users achieves high performance and fault tolerance through a dual‑center Elasticsearch cluster, traffic‑isolated three‑cluster ES design, Redis multi‑center caching, and a seamless migration from SQL Server to a partitioned MySQL architecture, while detailing operational safeguards and fine‑grained flow‑control strategies.
The article analyzes why an identical MySQL query runs faster in a QA environment than in DEV, examines execution‑plan differences caused by table driving choices, optimizer settings, and unnecessary conditions, and demonstrates how rewriting the SQL and adjusting hints can achieve a stable, efficient plan.
This article explains the fundamentals of MySQL indexes, compares them to a library catalog, details various index types and their physical implementations, demonstrates how indexes dramatically speed up queries on large tables, and provides practical SQL syntax for creating and managing indexes.
The article explains the limitations of standalone MySQL for large‑scale workloads, explores sharding, read/write separation, and middleware approaches, and argues that modern distributed databases and cloud‑native solutions are the sustainable path forward for data‑intensive applications.
This article breaks down MySQL's InnoDB storage engine, detailing where data files reside, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑tree indexes (clustered and secondary) are built and searched.
This article explains MySQL master‑slave replication, covering its basic concepts, the advantages of read‑write separation, data backup and high availability, the underlying binlog‑based mechanism, the four replication modes (full, asynchronous, semi‑synchronous, enhanced semi‑synchronous), common consistency challenges, and practical ways to mitigate them.
This article walks through installing Docker and Docker‑Compose, creating a project directory, writing a docker‑compose.yml that defines Redis, MySQL, Nginx, Java, and a file‑preview service, configuring each container, and using docker‑compose commands to build, start, monitor, and manage the full SpringBoot‑Vue application stack.
This comprehensive guide explores MySQL architecture, index types, query execution plans, and practical optimization techniques, offering detailed examples of creating, modifying, and analyzing indexes, avoiding common pitfalls, and improving query performance through proper use of EXPLAIN, composite indexes, and query rewriting.
This article explains why an UPDATE on a MySQL row that changes a regular column proceeds without blocking, while an UPDATE that modifies the primary‑key value gets blocked, by analyzing the locks set by preceding transactions, the B+‑tree index structure, and the delete‑plus‑insert rewrite performed by InnoDB.
This article explains MySQL row‑level locking by analyzing three transactions—A, B, and C—to show why an UPDATE on a primary‑key column can block while an UPDATE on a non‑indexed column proceeds without waiting.
This article explains how MySQL 8.0.32 introduces the @@explain_format system variable that allows administrators to set different execution‑plan output formats (tree, traditional, json, etc.) for individual users, and demonstrates the configuration steps with concrete SQL commands and examples.
This article explains common MySQL database performance optimization techniques, underlying storage architecture, index construction and usage guidelines, and presents several real‑world cases from an internet hospital project, helping developers understand optimization strategies, avoid hidden pitfalls, and improve query efficiency.
This article explains how DISTINCT and GROUP BY differ in MySQL, comparing their index usage, execution speed with and without indexes, the impact of implicit sorting in older versions, and why GROUP BY is often preferred for clearer semantics and advanced aggregations.
This article analyzes why MySQL 8.0 reports an illegal mix of collations when querying a view that joins tables with different character sets, demonstrates reproducing the issue, explains the role of the CONVERT function and default collation variables, and provides practical recommendations to avoid such errors.
This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.
This weekly newsletter curates high‑quality MySQL and database articles, shares the latest SQLE release and development progress, and invites community feedback, providing a concise overview of recent open‑source database developments and upcoming plans.
This article explains the evolution of tree-based index structures—from basic binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—highlighting their trade‑offs and why MySQL adopts B+‑trees for efficient disk‑based indexing.
This guide explains how to install DataX, set up MySQL environments, configure JSON job files, and run both full and incremental data synchronization between heterogeneous databases using DataX's Reader/Writer framework and job scheduling features.
This article explains MySQL transaction fundamentals, including the ACID properties, isolation levels, MVCC mechanics, transaction start methods, and practical examples that illustrate how different isolation levels affect data visibility and consistency.
This article provides a step‑by‑step tutorial on installing, configuring, and using Alibaba's open‑source DataX tool to perform both full and incremental data synchronization between MySQL databases on Linux, covering framework design, job architecture, JSON job files, and practical command‑line examples.
This article details a robust solution for exporting massive MySQL datasets—potentially millions of rows—to Excel by leveraging asynchronous processing with jobs or MQ, the memory‑efficient EasyExcel library, pagination, multi‑sheet handling, OSS storage, and WebSocket notifications, ensuring performance and usability.
This article analyzes the overselling problem in high‑concurrency flash‑sale scenarios, demonstrates seven concrete solutions—including improved locks, AOP locks, pessimistic and optimistic database locks, and queue‑based approaches using BlockingQueue and Disruptor—provides full SpringBoot code, JMeter test results, and practical recommendations for reliable stock reduction.
This free Cantonese-language course introduces PHP7 fundamentals, MySQL database connection, and simple website development using professional tools, empowering learners to build their own web projects, with all materials available online via the provided link.
This article demonstrates how creating a covering composite index on MySQL tables can reduce a 55‑second aggregation query on five million rows to about two seconds by eliminating the need for row lookups and fully utilizing index data.
This article explores four common approaches for synchronizing product data from MySQL to Elasticsearch in e‑commerce systems—synchronous dual write, asynchronous dual write with message queues, scheduled tasks, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations.
This article examines typical MySQL out‑of‑memory (OOM) situations in production, explains how to diagnose memory usage with Performance Schema and other tools, and presents a series of TDSQL‑C‑specific optimization techniques—including server‑parameter tuning, process‑list monitoring, cold‑page detection, buffer‑pool limits, and dynamic resizing—to mitigate OOM risks.
This article presents a collection of practical MySQL functions and SQL commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—explaining their purposes, usage patterns, and providing concrete example queries with results.
This article explores four common approaches for synchronizing product data from MySQL to Elasticsearch—including synchronous dual writes, asynchronous messaging, scheduled jobs, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations for e‑commerce search systems.
This article presents nine practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries as joins, handling mixed sorting, replacing EXISTS with joins, pushing conditions into subqueries, early range reduction, and using CTEs—to dramatically improve query execution time across common scenarios.
Processing massive datasets in MySQL can cause OOM and slow performance; this article explains when to use regular pagination, streaming queries, and cursor-based fetching, compares their trade‑offs, and provides MyBatis code examples with @Options and @ResultType to handle millions of rows efficiently.
This article examines four common approaches for synchronizing product data from MySQL to Elasticsearch—synchronous dual write, asynchronous dual write with message queues, scheduled batch jobs, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations.
This article explains why MySQL count(*) queries can become a performance bottleneck on InnoDB tables and presents practical optimization techniques such as Redis caching, secondary caches, multithreading, reducing joins, and even switching to ClickHouse for massive datasets.
The article explains how single‑instance MySQL struggles with huge tables, resource‑starved databases, and high read traffic, and argues that sharding middleware is being replaced by modern distributed databases such as TiDB, OceanBase, Aurora, and PolarDB as the sustainable scaling solution.
This article explains how to install DataX, configure MySQL readers and writers, and execute both full and incremental data synchronization jobs between two MySQL instances, providing step‑by‑step commands, JSON job templates, and troubleshooting tips for large‑scale data transfers.
This article introduces Alibaba DataX, explains its architecture and role in offline heterogeneous data synchronization, provides step‑by‑step Linux installation, demonstrates full‑load and incremental MySQL‑to‑MySQL sync with JSON job templates, and shares practical tips for handling large data volumes.
This guide explains how to install, configure, and run a lightweight Redis Monitor that visualizes connection count, QPS, memory usage and replication delay, supports standalone, sentinel and cluster modes, and provides email and WeChat alerts via simple PHP scripts.
This article experimentally demonstrates how MySQL's SELECT … FOR UPDATE statement acquires various locks—IX, X, and gap locks—under RC (read‑committed) and RR (repeatable‑read) isolation levels with unique, primary, normal, and missing indexes, revealing when row‑level versus table‑level locking occurs.
This article examines eight common SQL pitfalls—from inefficient LIMIT usage and implicit type conversion to suboptimal joins and condition push‑down—offering concrete rewrite examples and performance‑boosting techniques that can shrink query times from seconds to milliseconds.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the B+Tree storage mechanism compared with B‑Tree, and the distinction between clustered and non‑clustered (auxiliary) indexes, providing practical insights for database performance optimization.
This article provides a comprehensive technical walkthrough of MySQL‑8.0.22 InnoDB's buffer pool, detailing its physical and logical structures, page management algorithms, fast read/write optimizations, change buffer, adaptive hash index, and the complete page‑read call stack, complete with code excerpts and diagrams.
This article shares practical experience comparing ClickHouse and StarRocks as real‑time data warehouses, outlines the project requirements, evaluates each system's suitability for log‑type and business‑type data, and describes CDC‑based synchronization methods from MySQL to both platforms.
This article presents a series of MySQL 5.7.26 experiments that reveal how record locks on non‑indexed columns are applied to the primary (clustered) index under different isolation levels, how full‑table scans affect locking behavior, and why updates and inserts may or may not be blocked.
This article examines MySQL’s transition from dual‑master to master‑slave replication, highlights the complexities of position‑based syncing, introduces GTID as a more reliable alternative, and provides practical steps to enable GTID and accurately monitor replication lag using seconds_behind_master, binlog positions, and GTID sets.
This article experimentally verifies how SELECT FOR UPDATE behaves under different MySQL versions, isolation levels, and index conditions, revealing when it acquires row‑level, gap, or table‑level locks across 20 distinct scenarios.
This article introduces four open‑source MySQL performance‑tuning utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—explains how to download, install, run them, and interpret their reports to identify configuration issues and slow‑query problems.
This article details the requirements analysis, design approach, database schema, and step‑by‑step implementation of a follow/unfollow microservice using MySQL for persistence and Redis Sets for efficient set operations, including Maven dependencies, Spring Boot configuration, RedisTemplate setup, service, controller, and testing of common‑follow queries.
This weekly community bulletin curates high‑quality technical shares and release notes from the MySQL and DBLE ecosystems, highlighting new features, bug fixes, and upcoming development plans while inviting reader feedback and questions about the middleware tools.
This article walks through the complete design and implementation of a friend‑follow microservice, covering requirement analysis, MySQL table schema, Redis set operations, Spring Boot dependencies, configuration, service and controller code, gateway routing, and end‑to‑end testing with concrete examples and performance insights.
The article introduces the SQLE project, details the new preview version 2.2302.0-pre2 with features such as external data source import and DMP integration, and provides the complete release notes, optimization items, links to previous versions, and additional reading resources.
This article explains MySQL's transaction concepts, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their effects on dirty reads, non‑repeatable reads and phantom reads, how they are implemented with locks and MVCC, and provides practical SQL examples and diagrams.
This article provides a comprehensive tutorial on configuring MySQL master‑slave replication with Docker, explains the roles of binlog and redo log, describes two‑phase commit, and demonstrates how to use Sharding‑JDBC (ShardingSphere) for read‑write splitting and horizontal sharding in a Spring Boot application, complete with configuration files and code examples.
This article presents a practical guide to implementing monthly sharding for high‑volume billing data using MySQL stored procedures, dynamic table creation, and SpringBoot‑MyBatis integration, covering schema design, code examples, and query strategies for inserting, retrieving, and managing partitioned tables.
The article explains how MySQL Shell 8.0.31's util.debug component provides built‑in functions to gather comprehensive diagnostic data—including error logs, replica status, InnoDB metrics, and OS metrics—and demonstrates practical command‑line examples for generating and analyzing zip‑packed diagnostic reports.
This article explains the transition from dual‑primary MySQL to a master‑slave read/write split, outlines the pain points of position‑based replication, and demonstrates how GTID simplifies setup, failover, and monitoring of replication lag while ensuring data consistency.
This guide compiles a comprehensive set of MySQL command‑line scripts for exporting whole databases, individual tables or schemas, importing data, and performing common database and table operations such as creation, selection, alteration, deletion, granting privileges, and viewing structure, all illustrated with ready‑to‑use code examples.
The article analyzes a MySQL mysqldump failure where tables appear missing due to the lower_case_table_names setting, reproduces the issue through a controlled experiment, and provides practical solutions and operational recommendations for handling case‑sensitivity during database migrations.
This article explains how InnoDB implements row‑level locking in MySQL, detailing the lock types (record, gap, next‑key), the scenarios that trigger each lock, how unique and non‑unique indexes affect locking, and provides step‑by‑step commands and examples for analyzing locks with performance_schema.
This comprehensive guide walks you through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, insert‑select variations, on duplicate key update, show create table, explain, show processlist, and mysqldump—complete with clear examples, SQL snippets, and result screenshots to help you manage and optimize your data effectively.
This article explains the complete MySQL execution flow, covering the connector, permission verification, caching, parser, optimizer, executor, process states, SQL execution order, and the impact of WHERE‑clause condition ordering on query performance.
The preview release of SQLE 2.2302.0-pre1 introduces manual deployment support, UI enhancements, and several bug fixes, providing a detailed overview of the project's purpose, new functionalities, and complete release information for database users and administrators.
This article investigates MySQL's recommendation against using UUIDs as primary keys by creating three tables with auto‑increment, UUID, and random (snowflake) keys, running insertion benchmarks with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the advantages and drawbacks of each key strategy.
