This article explains why slow SQL queries become a major risk for service stability, outlines a step‑by‑step investigation process using EXPLAIN, and presents multiple real‑world case studies with code snippets and optimization principles to quickly locate and resolve performance bottlenecks.
When MySQL tables grow to millions of rows, insert and query latency increase, schema changes become costly, and irrelevant data bloats storage, so this guide evaluates table size, explains why B+‑tree depth hurts performance, and presents partitioning, sharding, and hot‑cold archiving solutions with practical SQL examples.
This article presents a detailed reference of MySQL server configuration options (my.cnf), explaining each setting such as storage engine, networking, replication, logging, cache sizes, and performance‑related parameters to help administrators fine‑tune their databases.
MySQL 8.2.0, released on October 25, 2023, introduces audit‑log cleanup scripts, FIDO‑based authentication, named‑parameter support, numerous deprecations, firewall reload scheduling, expanded SQL syntax options, and new client and server features while dropping outdated functions and variables.
This article explains the technical reasons why a MySQL single table should not exceed about 20 million rows, covering limits from auto‑increment primary keys, data‑page structures, B+‑tree storage calculations, and practical partitioning recommendations for large‑scale applications.
This article explains how a hidden non‑breaking space character copied from documentation caused the MySQL lower_case_table_names parameter to be unrecognizable, details the reproduction steps, and presents three methods (hexdump, od, editor) to detect and resolve such encoding issues.
This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.
This article explains the principles of disaster recovery for MySQL, covering RTO/RPO metrics, national backup level standards, common master‑slave topologies, a comparative analysis of high‑availability solutions, and a detailed three‑center multi‑region replication design with code patches to avoid replication loops.
The article explains mysql_sniffer, a MySQL‑protocol packet sniffer that captures queries in real time, details its benefits for MySQL 8.0 upgrades, shows how to interpret incompatibilities, provides full command‑line options, usage examples, testing procedures, and a GitHub download link.
This article explores common reasons MySQL queries become sluggish—beyond missing indexes—including connection limits, insufficient InnoDB buffer pool size, and query cache issues, and provides practical steps such as profiling, using FORCE INDEX, adjusting max_connections, enlarging the buffer pool, and tuning connection pools to improve performance.
MySQL 8.2 introduces transparent read/write splitting via MySQL Router, allowing automatic routing of read traffic to replica instances and write traffic to the primary source without any changes to the application, demonstrated with a simple InnoDB ReplicaSet deployment and configuration examples.
The newsletter announces the SQLE 3.0 release with new UI and demo environments, highlights the SQLE 2.2310.0‑pre3 update, reviews technical articles on MySQL cloning, OceanBase indexing, data replication, charset issues, and connection‑pool design, recaps recent conferences, and previews upcoming OB Cloud open class events.
The article explains what MySQL master‑slave replication lag is, analyzes why it occurs—especially during relay‑log replay and large DDL/DML operations—and provides concrete configuration, hardware, and architectural solutions to reduce or eliminate the delay.
MySQL 8.0.28 and earlier can deadlock in multi‑threaded slave replication when the internal commit sequence counter overflows after 2³¹ transactions, causing workers to stop waking and the replica to hang; the article explains the underlying slave_preserve_commit_order mechanism, the overflow bug, and a step‑by‑step reproduction method.
This article explains the concept of database transactions, the ACID properties, how MySQL implements transactions with InnoDB, how to manage autocommit, the essential transaction control statements, a practical example using savepoints, and the four isolation levels supported by MySQL.
This article explains why indexing long character columns can bloat MySQL indexes, introduces the concept of index selectivity, shows how to calculate the best prefix length with real‑world queries, and demonstrates performance differences between short and optimal index prefixes.
This guide explains why case‑sensitivity issues arise in databases, how naming conventions can prevent them, and compares the behavior of Snowflake, Databend, MySQL, and PostgreSQL for database, table, column names and string literals, complete with practical examples and a concise checklist.
This announcement details a book giveaway sponsored by Tsinghua University Press and presents a comprehensive SQL learning curriculum covering SQLite, MySQL, query fundamentals, subqueries, joins, and advanced database features, complete with micro‑lecture videos and hands‑on exercises.
This tutorial walks through creating an Ansible role that structures directories, defines configuration templates, sets variables, writes task files, and runs a playbook to install MySQL 8.0.32 on a Linux host, illustrating a complete automation workflow for database deployment.
This article systematically investigates how MySQL's SELECT FOR UPDATE behaves under different versions (5.7 and 8.0) and isolation levels (RR and RC), covering twenty scenarios that vary by index type and query range, and summarizes whether the statement acquires row‑level, gap, or table‑level locks.
This tutorial walks you through preparing a Kubernetes cluster, deploying MySQL and Typecho containers with detailed YAML configurations, creating the necessary services and ingress, testing the setup, and highlighting Kubernetes' high‑availability and auto‑scaling benefits for a reliable blog platform.
This article explains MySQL InnoDB's Multi-Version Concurrency Control (MVCC), covering its basic concepts, read/write scenarios, implicit fields, undo logs, read view mechanisms, transaction handling, and differences between RC and RR isolation levels, providing a comprehensive guide for developers.
This tutorial explains how to set up the environment, configure Elasticsearch and Logstash, create the necessary MySQL tables, and use a Logstash pipeline to continuously sync MySQL records into an Elasticsearch index, while also covering common pitfalls and troubleshooting steps.
This article provides a step‑by‑step walkthrough of MySQL’s execution pipeline—from the client connection and parsing, through optimization, execution, undo/redo logging, change buffer handling, and binlog recording—illustrated with diagrams and detailed explanations of each component.
This article explains how to safely run MySQL's CLONE INSTANCE command with the DATA DIRECTORY option on a replica that lacks enough disk space for both source and target data sets, offering step‑by‑step strategies, SQL examples, and a final recommendation.
This article reproduces MySQL's official LIMIT‑optimization example, shows how adding rows changes the ORDER BY + LIMIT result, explains the hidden priority‑queue mechanism revealed by optimizer_trace, and derives the exact row‑count threshold where the behavior switches.
This article details a practical approach to handling massive billing data by dynamically creating monthly tables in MySQL via stored procedures, integrating them with a SpringBoot‑MyBatis backend, and providing APIs for insertion, serial‑number lookup, and time‑range queries, complete with code examples and operational tips.
This article examines why combining ORDER BY with LIMIT in MySQL pagination can produce duplicate rows on subsequent pages, analyzes the optimizer’s priority‑queue behavior introduced in version 5.6, and presents practical solutions such as using unique sort keys, adding ID ordering, and understanding deep‑pagination limitations.
This article explores multiple strategies for enabling fuzzy search on encrypted phone numbers, including in‑memory caching, database decryption functions, segment‑wise storage, and dedicated fuzzy‑search fields, comparing their trade‑offs in performance, memory usage, and data consistency.
This article explains database sharding concepts, compares vertical and horizontal partitioning, details ID‑range and modulo sharding methods, discusses the read‑amplification issue caused by non‑shard queries, and presents solutions such as auxiliary index tables, Elasticsearch integration, and TiDB as alternatives.
This tutorial walks you through creating a Node backend using NestJS, configuring Sequelize with MySQL, setting up the project structure, writing DTOs, controllers, services, and modules, and implementing basic CRUD operations with code examples and deployment tips.
This article explains how to import 1 billion 1 KB log records stored in HDFS or S3 into MySQL by analyzing table capacity limits, choosing storage engines, designing batch inserts, coordinating file reading and writing, and handling task reliability with Redis, Redisson, and Zookeeper.
This article walks through setting up a Node.js backend using NestJS, integrating Sequelize with MySQL, configuring the database, defining entities, services, controllers, and demonstrating CRUD operations, along with optional features like GraphQL, Swagger, and deployment considerations.
This article explains why a MySQL 5.7.30 upgrade with GTID enabled causes a GTID‑consistency error for CREATE TABLE ... SELECT statements, describes the underlying mechanism, provides a safe two‑step workaround, and notes that MySQL 8.0.21+ now supports atomic DDL for this operation.
This article explains the fundamentals and types of MySQL indexes, provides guidelines for selecting appropriate indexes based on query frequency, uniqueness and complexity, and presents PHP code examples for adding and using indexes, along with optimization strategies such as proper schema design, data types, and index maintenance.
This article explains how to assess MySQL table size, understand performance degradation caused by large B+‑tree indexes, and apply three practical solutions—table partitioning, database sharding, and cold‑data archiving—to improve query speed and maintainability.
This article investigates the nondeterministic behavior of MySQL when combining ORDER BY with LIMIT, reproduces the official example, identifies a critical row count threshold where the optimizer switches to a priority‑queue (heap) sort, and explains the underlying source‑code logic and its impact on query results.
The mysqlstat command‑line tool provides real‑time monitoring and analysis of MySQL servers, offering metrics such as QPS, TPS, network bandwidth, top‑executed SQL statements, hot tables, lock and deadlock information, index analysis, connection counts, table size statistics, binlog insights, and replication status, with usage examples and download instructions.
This article explains why the dbops tool does not support MySQL master‑master architecture, describes the two common dual‑master patterns, outlines their drawbacks such as role‑confusion, dirty‑data risk, split‑brain, and update loss, and provides practical workarounds and step‑by‑step deployment instructions to safely implement or avoid dual‑master setups.
This article presents practical methods—including indexing, query consolidation, field selection, PHP code refinements, and caching strategies with Redis and page caching—to significantly improve the performance of PHP‑MySQL applications, accompanied by concrete code examples.
This article explains how MySQL's optimizer calculates execution costs for different indexes, why it may select a full table scan instead of an apparently better index, and shows how to use EXPLAIN and optimizer trace to reveal and improve the chosen execution plan.
This article presents a comprehensive guide to planning and executing MySQL version upgrades—including industry version statistics, performance benchmarks, migration challenges, stability guarantees, detailed upgrade strategies, compatibility testing, and post‑upgrade validation—to ensure zero‑downtime and minimal operational impact.
This article explains the reasons for read/write splitting, compares direct‑client and proxy‑based master‑slave setups, and presents five practical strategies—including forcing reads to the master, sleep delays, lag checks, master‑position waiting, and GTID waiting—to mitigate stale reads caused by MySQL replication lag.
This article compares various MySQL data import/export methods—including mysqldump, mydumper, SELECT OUTFILE, and MySQL Shell utilities—explaining their commands, configuration options, performance results, and best‑practice recommendations based on testing with a 10‑million‑row table.
This article explores practical methods for performing fuzzy searches on encrypted phone numbers, covering in‑memory caching, database decryption functions, segment‑based storage, and adding dedicated fuzzy‑search fields to balance security, performance, and scalability.
This article explains how to identify whether MySQL replication delay originates from the I/O thread or SQL thread, demonstrates diagnostic commands, and provides practical tips, configuration tweaks, and monitoring tools such as pt‑stalk to reduce lag and improve replication performance.
This technical report details the background, hardware configuration, database setup, tuning results, and step‑by‑step optimization procedures—including BIOS, OS, network, container NUMA binding, MySQL CRC32 patching, and Go PGO tuning—performed to improve JED performance on ARM compared with Intel.
This article demonstrates two practical approaches for handling MySQL auto‑increment columns during migration to OB Oracle—creating custom sequences with DBCAT and using the GENERATED BY DEFAULT AS IDENTITY attribute—provides step‑by‑step commands, scripts, and validation results to help DBA engineers achieve seamless primary‑key migration.
This guide explains MySQL's lock mechanisms—including shared and exclusive locks, row‑level locking requirements, pessimistic versus optimistic strategies, and practical implementations with InnoDB, transaction handling, and JMeter concurrency testing—to prevent overselling in high‑traffic scenarios.
The article examines why using application‑layer association instead of MySQL JOIN can improve cache efficiency, reduce lock contention, simplify scaling, and enhance query performance, while also outlining scenarios where JOIN is still beneficial and offering practical alternatives for large‑scale data retrieval.
This article examines why MySQL can still handle massive tables and presents practical techniques—including proper primary keys, eliminating redundant indexes, choosing optimal data types, compression, archiving, partitioning, sharding, query optimization, and safe schema changes—to keep large‑table performance high.
This article explains how MySQL's offline_mode variable and the pt‑kill tool can be used by DBAs to place a server in maintenance mode, instantly dropping non‑privileged client connections, preventing new ones, and providing a safe way to perform maintenance or backups.
When a single MySQL table grows to millions of rows, offset‑based pagination becomes painfully slow, so this article explains why, measures the performance impact, and presents three practical sharding‑aware pagination strategies—including global query, jump‑page prohibition, and a two‑step query method—along with concrete SQL rewrites and optimization tips.
Starting with MySQL 8.0.30, the Generated Invisible Primary Keys (GIPKs) feature automatically adds an invisible bigint auto‑increment column named my_row_id when a table lacks an explicit primary key, simplifying schema design, easing DBA‑developer conflicts, and offering flexible handling in MGR and replication environments, while noting its limitations and usage commands.
This article investigates why inserting the foreign_key_checks system variable into MySQL's my.cnf prevents the server from starting, explains the underlying NO_CMD_LINE flag mechanism, and provides a simple command‑line method to distinguish variables that can or cannot be set via configuration files.
This article analyzes the differences between MySQL's full table scan and full primary‑key scan by examining execution plans, source‑code paths, and runtime behavior, demonstrating that both ultimately read data from the primary‑key index and explaining why the optimizer distinguishes them.
This guide walks through installing Apache Ignite via Docker, configuring it alongside MySQL in a Spring Boot project, setting up data sources and MyBatis, and performing benchmark tests to illustrate Ignite’s superior in‑memory SQL performance compared to traditional MySQL setups.
This article explains how to enable fuzzy searches on encrypted sensitive fields such as names, phone numbers, and ID numbers in a Spring Boot backend by using a tokenized ciphertext mapping table, outlining four possible solutions, recommending the tokenized approach, and providing complete configuration and code examples.
This article details the architecture, technical requirements, and implementation techniques—including hybrid push‑pull, heterogeneous storage, distributed scheduling, task chunking, and at‑least‑once delivery—used to build a high‑concurrency, reliable reminder notification service for QQ.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
This article explains how to use MySQL's EXPLAIN statement to analyze slow queries, detailing each output column, various select types, access methods, and common pitfalls, then provides practical optimization techniques such as proper indexing, query rewriting, index merging, handling COUNT, UNION, OFFSET, and table maintenance.
This guide presents a comprehensive set of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting specific columns, avoiding costly ORDER BY RAND(), choosing UNION ALL over UNION, distinguishing IN from EXISTS, employing full‑text indexes, respecting left‑most prefix rules, and leveraging FORCE INDEX or STRAIGHT_JOIN—to help developers write faster, more efficient queries.
Learn how MySQL's index merge optimization works, explore its three algorithms—intersect, union, and sort_union—understand when each applies, see practical setup and sample queries, and discover configuration flags and best‑practice tips to improve query efficiency.
This guide reviews popular free MySQL graphical clients—including MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, and DBeaver—detailing their key features, platform support, and download links, offering a cost‑free alternative to paid tools like Navicat.
Learn how to capture MySQL data changes using Debezium's CDC capabilities within a Spring Boot application, avoiding heavyweight message brokers by leveraging binlog monitoring, configuring connectors, handling snapshots, and processing change events for use cases like cache invalidation, data integration, and simplifying monolithic architectures.
This article examines how executing an UPDATE before a slow SELECT within a @Transactional method in Spring can prolong MySQL write locks, and demonstrates code refactoring techniques—reordering operations and separating transactional boundaries—to improve overall system performance.
This article breaks down MySQL's complex locking mechanisms—global, table, and row locks—explaining how different SELECT statements, transaction isolation levels, and index types affect lock acquisition, and provides practical test scripts and visual examples to help developers master lock behavior.
This article explains how to create optimal MySQL indexes for a service_log table based on various search conditions, covering single‑column, composite, full‑text, prefix, and covering indexes, along with best practices, index size considerations, and practical SQL examples.
This article analyzes a large MySQL table that caused SLA alerts due to master‑slave lag, examines slow‑query logs with pt‑query‑digest, identifies index and delete inefficiencies, proposes index redesign and batch‑delete strategies, and validates the improvements with testing and DDL tools.
This article examines how InnoDB processes UPDATE statements on a table with primary and secondary indexes, analyzes three scenarios to determine which index blocks are modified, explains the internal filtering steps, and validates the behavior by inspecting LSN changes of the affected blocks.
This article explains how MySQL permission modifications behave at table, database, and global levels, demonstrates verification steps with Uproxy's connection pool, and clarifies why query cache settings and USE statements affect the visibility of privilege changes.
This guide outlines a comprehensive MySQL backup solution, covering backup strategy planning, choosing physical versus logical backups, using tools like mysqldump and Percona XtraBackup, automating backups with scripts and cron, storage and retention considerations, and regular recovery testing to ensure data integrity.
This article explains how to create a basic PHP-based question‑and‑answer web application, covering functional requirements, environment setup, MySQL schema design, and providing complete code snippets for user registration, login, posting, answering, listing, and searching questions.
This article explains how to securely expose a MySQL server behind an Nginx stream proxy, covering required modules, configuration directives, IP‑based access control, and complete example setups for both single‑instance and clustered environments.
This guide walks through installing MySQL in Docker, configuring master‑slave replication, verifying synchronization, deploying a Canal server, and integrating it with a Spring Boot application to automatically push database changes into Redis.
This article explains the principle behind MySQL's index‑merge optimization, demonstrates how to set up test data, walks through the three merge algorithms (intersect, union, sort‑union), provides practical SQL examples, and shares tips for tuning and avoiding common pitfalls.
This article explains how to use the mysqldump command to back up MySQL databases, demonstrates various backup and restore options, provides a Bash script that maintains a limited number of daily backups, and shows how to schedule the script with crontab for automated execution.
This article explains how MySQL 8 improves the auto‑increment counter by persisting its maximum value across server restarts, contrasting the behavior with MySQL 5.7 through detailed examples and SQL commands that demonstrate the differences in primary‑key generation.
This article demonstrates how to implement a data merging feature by creating MySQL tables, building a PHP API that retrieves and combines records from two tables, and developing a Vue.js front‑end that fetches and displays the merged data, complete with full code examples.
Learn step‑by‑step how to configure Alibaba’s open‑source Canal to capture MySQL binlog changes, route them through RocketMQ, and index the data into Elasticsearch, covering cluster mode, MySQL and Elasticsearch setup, Canal properties, and consumer implementation details.
This article examines why PostgreSQL appears to outrank MySQL in recent popularity surveys, explains the DB‑Engines ranking methodology, compares architectural choices such as MVCC, multi‑process vs multi‑thread models, transaction rollback behavior, licensing, and real‑world performance benchmarks like TPC‑C, ultimately revealing that MySQL still dominates many high‑throughput OLTP scenarios.
The SQLE 2.2309.0-pre4 release introduces an enterprise‑level SQL lifecycle management panel, a rule knowledge base, new TDSQL audit rules, and intelligent metadata scanning tasks, while providing links to the repository, documentation, and demo environments for both community and enterprise editions.
Learn how to protect MySQL servers by using Nginx as a stream proxy, configure the required modules, set up listening ports, and restrict access to specific IP addresses, enabling safe remote connections for developers.
The article examines a MySQL InnoDB deadlock that occurs when concurrent inventory‑write transactions in a pre‑release environment contend for row and gap locks, explains InnoDB lock types, isolation‑level effects, and proposes solutions such as distributed locks and isolation level adjustments.
This tutorial walks through configuring Canal in cluster mode to capture MySQL row‑based binlog events, publish them to a RocketMQ topic, and consume the ordered messages to update an Elasticsearch index, covering MySQL, RocketMQ, Elasticsearch mappings, and the required Canal properties.
The article explains why master‑master MySQL architectures are discouraged, outlines the pitfalls of dual‑write setups, and provides step‑by‑step instructions using dbops to deploy a master‑slave topology and manually create a reversible master‑master configuration for testing.
This article compares UUID and auto‑increment primary keys in MySQL, explaining how each is generated, their advantages and drawbacks, performance implications, and practical guidance on selecting the right key type for different business scenarios.
This tutorial walks through using Spring Batch with Spring Boot 2.6.12 to create a batch job that reads CSV data, processes each record, and writes the results into a MySQL database, covering architecture, configuration, code components, and execution steps.
This article examines a MySQL deadlock case involving DELETE and UPDATE statements, explains why the primary key index is locked despite non‑key predicates, analyzes execution plans and optimizer trace, and proposes index restructuring and query hints to resolve the issue.
This article summarizes a performance comparison between Spring Boot applications using virtual threads and those built with Spring WebFlux, detailing the test scenario of JWT verification and MySQL queries, the environment, code implementations, benchmark results across various concurrency levels, and concluding that WebFlux outperforms virtual‑threaded Spring Boot.
The article examines the challenges of running MySQL in Docker containers, highlighting data‑security, performance, state, and resource‑isolation concerns, while also presenting scenarios and strategies where containerizing MySQL can be viable.
This article explains why a single MySQL table should stay below about twenty million rows by examining primary‑key limits, InnoDB data‑page structure, B+‑tree storage calculations, and provides a practical recommendation based on row size and fragmentation.
The article explains how large‑offset (deep) pagination in MySQL and distributed databases degrades performance and increases memory and bandwidth usage, and describes VtDriver's streaming and SQL‑rewrite techniques along with practical recommendations such as range queries and sub‑queries to mitigate these issues.
The article evaluates database success by comparing PostgreSQL and MySQL across popularity, features, licensing, transaction behavior, MVCC, concurrency models, and use cases, showing PostgreSQL leads in complex queries and feature richness while MySQL remains the most widely used OLTP system, especially in China.
This article explains how MySQL calculates execution costs for different indexes using IO and CPU metrics, demonstrates with a sample table and queries, compares the optimizer's choices with actual performance, and shows how to use EXPLAIN and optimizer trace to fine‑tune query plans.
The SQLE 2.2309.0-pre3 release introduces a fast SQL audit feature, adds numerous MySQL audit rules, provides detailed community and enterprise version updates, and includes links to the repository, documentation, and previous versions for comprehensive usage and evaluation.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors with genuine four‑byte UTF‑8 symbols, and advises all MySQL/MariaDB users to migrate to the proper “utf8mb4” charset using available conversion guides.
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—covers lock compatibility, deadlock causes and prevention, compares optimistic and pessimistic locking, and shows how SELECT FOR UPDATE behaves under different isolation levels.
The article explains that, although MySQL’s theoretical limits are far higher, practical B+‑tree page and index calculations suggest keeping a single table under about twenty million one‑kilobyte rows to avoid performance degradation, illustrated with interview examples and estimation formulas.
This article explains how to configure Nginx as a stream proxy to securely connect to MySQL servers, covering required modules, stream, server, listen directives, IP access restrictions, and provides complete configuration examples for both single‑instance and clustered environments.
