This article explores four practical methods—batch inserts, transaction wrapping, ordered primary‑key insertion, and their combined use—to dramatically improve MySQL InnoDB insert performance, presents test results across various data volumes, and offers key configuration cautions.
This article explains how MySQL Group Replication (MGR) can provide zero‑RPO high‑availability within a city‑scale data center, why it needs asynchronous replication for WAN‑scale disaster recovery, and walks through a step‑by‑step setup—including code examples—for automatic failover of asynchronous replication channels.
This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.
A production MySQL query that omitted its leftmost indexed column caused a full‑table scan, CPU overload, and a read‑only alert, prompting a rapid investigation, temporary index addition, and a deeper discussion of the left‑most matching principle, Java validation pitfalls, and best‑practice lessons.
The DTLE 3.20.10.0 release introduces full and incremental job status output, a reschedule option to prevent unlimited retries, code refactoring, and provides repository, documentation, and download links for the open‑source MySQL data‑transfer component, followed by a light‑hearted personal note.
When a MySQL table grows large, CRUD performance degrades, so this article presents comprehensive optimization techniques—including field design, indexing, query rewriting, engine selection, system parameters, hardware upgrades, read‑write separation, caching, partitioning, vertical and horizontal splitting, sharding architectures, and recommendations for compatible scalable databases—to help maintain high performance and scalability.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article compiles essential MySQL interview questions covering data types, storage engines, locks, transactions, triggers, index creation and usage, join types, and practical SQL optimization techniques with concrete examples and code snippets for developers to master database fundamentals.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This guide explains how to identify bottlenecks and boost MySQL performance through both soft (query, index, schema) and hard (hardware, configuration, sharding, caching) optimizations, offering practical commands, best‑practice tips, and visual illustrations for each step.
This article explains the structure of MySQL ROW‑mode binlogs, demonstrates how to modify binlog events with Python to recover deleted rows, locate oversized transactions, and split large transactions into smaller ones, providing practical scripts and examples for advanced database manipulation.
This article explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, shows several SELECT queries to list duplicates, and provides multiple DELETE strategies—including sub‑queries and multi‑column handling—to safely remove excess records while keeping one copy.
Database auditing records user actions to detect illegal operations, with approaches ranging from application‑layer and transport‑layer monitoring to kernel‑level and plugin solutions; TXSQL’s MySQL‑compatible audit plugin offers both synchronous and asynchronous modes, delivering flexible rule configuration and only 3‑6 % performance overhead, making it a low‑impact, feature‑rich choice for compliance and forensics.
This article provides a comprehensive overview of MySQL’s logging mechanisms—including Binlog, Undo log, Redo log, and Change Buffer—explaining their concepts, roles in replication and crash‑recovery, recording formats, flush timing, two‑phase commit, checkpoint handling, and how they interact during data modifications.
This article explains MySQL lock types—table, row, and page locks—their algorithms, common causes of deadlocks, and provides multiple practical examples with SQL statements and diagrams, followed by InnoDB lock‑prevention strategies and detailed analysis of concurrent delete scenarios.
This article presents a comprehensive MySQL database standard covering table creation rules, SQL writing conventions, and index design recommendations, explaining the rationale behind each mandatory or suggested practice to improve performance, maintainability, and consistency across projects.
This article compares MySQL's three data‑removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space and auto‑increment values, and best‑practice usage with code examples and optimization tips.
This guide explains how MySQL handles string‑date conversions, shows when to use date_format for query conditions, warns about performance impacts, demonstrates converting query results with str_to_date, and clarifies the meaning of common format placeholders.
The article explains that using LIMIT with DELETE in MySQL improves safety, reduces lock time, prevents long‑running transactions, and enhances performance, and it provides syntax, examples, and expert opinions to illustrate the benefits of this habit.
This article explains MySQL master‑slave replication by covering SBR, RBR, and MBR concepts, step‑by‑step configuration of master and slave servers, binlog format settings, the underlying replication protocol, key SQL commands, and troubleshooting tips, all illustrated with real code snippets and diagrams.
This article explains how to identify which slave in a MySQL one‑master‑multiple‑slave semi‑synchronous setup is slowing the master’s commit performance by using debug logging and examining the error log for the last ACK server ID.
This guide walks you through MySQL's EXPLAIN command, explaining each output column, demonstrating how to read execution plans for simple selects, joins, subqueries, UNIONs, and advanced variants, and showing how to use this information to improve query performance.
This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.
This article explains the various MySQL index implementations—including B‑Tree, B+Tree, hash, clustered, non‑clustered, and covering indexes—illustrates their internal structures with diagrams, provides SQL examples, and offers practical guidelines for selecting and using indexes efficiently.
This article evaluates two techniques for accelerating rollback of large MySQL transactions—dynamically enlarging the InnoDB buffer pool and using innodb_force_recovery=3 with instance shutdown—by presenting experimental steps, code examples, performance measurements, and practical recommendations.
This article introduces MySQL 8.0.21's built‑in multi‑threaded logical backup utility, compares it with legacy tools like mysqldump and mysqlpump, demonstrates usage of dump_instance, dump_schemas and load_dump with Python‑style code snippets, and summarizes performance results and recovery steps.
This guide explains how to recover a lost MySQL root password by editing the MySQL configuration to skip grant tables, restarting the service, and then setting a new password via SQL commands, with platform-specific steps for Windows, macOS, and Linux.
This article compiles the most frequently asked MySQL‑related questions from developers and operations teams in banks, covering why MySQL is chosen over Oracle, compatibility gaps, migration methods, sharding strategies, performance tuning, consistency guarantees, and resource planning.
MySQL 8.0.22 introduces prepared‑statement improvements, a new SHOW PROCESSLIST implementation via Performance Schema, UTC timestamp handling, read‑only schemas, error‑log access through Performance Schema, user‑management enhancements, optimizer push‑down, replication auto‑failover, Router scalability upgrades, and several deprecations and removals.
This article details how a high‑traffic e‑commerce platform migrated from a single MySQL instance to a sharded architecture using Sharding‑JDBC, added Elasticsearch‑HBase for multi‑dimensional queries, and implemented zero‑downtime data migration and scaling strategies.
This article explains why adding a LIMIT clause to MySQL DELETE statements is a good habit, compares it with TRUNCATE, shows the correct syntax, outlines performance and safety advantages, and discusses practical strategies for deleting large numbers of rows.
This article explains why MySQL's default InnoDB storage engine uses B+ trees instead of B trees or hash indexes, covering storage engine basics, query performance, I/O considerations, and the structural advantages that make B+ trees ideal for OLTP workloads.
This article explains how InnoDB’s B+ tree storage works, calculates the approximate number of rows a single tree can hold based on page size, record size, and tree height, and shows how to determine the tree height from the tablespace file.
This article details a real‑world case where an RDS MySQL instance ran out of disk space due to an oversized temporary tablespace, explains the root‑cause analysis of a massive Cartesian‑product query, and provides step‑by‑step remediation and preventive measures for both MySQL 5.7 and 8.0.
This guide walks through the complete deployment of Spinnaker on Kubernetes using Halyard, covering architecture analysis, preparation of Redis, MySQL and MinIO, container image retrieval, BOM preparation, Halyard configuration, external Redis and SQL database integration, authentication, canary analysis, monitoring, and additional settings.
This article explains the three primary ways to remove data in MySQL—DELETE, TRUNCATE, and DROP—comparing their execution speed, underlying mechanisms, impact on disk space, transaction behavior, and how to reclaim space with OPTIMIZE, while providing practical SQL examples and usage warnings.
This article examines MySQL pagination on a multi‑million‑row table, compares plain LIMIT queries with subquery, ID‑range, and temporary‑table techniques, provides SQL examples and performance measurements, and offers recommendations for fast, scalable paging.
This article explains why appending a LIMIT clause to MySQL DELETE statements improves safety, reduces lock time, and boosts performance, while comparing three deletion strategies and recommending the most efficient approach for large‑scale data removal.
This article traces MySQL's four‑decade history, its acquisition by Sun and Oracle, the rise of MariaDB as a compatible fork, and provides a practical guide to installing and using MariaDB with screenshots and tool recommendations.
This article explains how MySQL InnoDB locates and deletes a specific row by describing the buffer‑pool lookup, B‑tree page traversal, page‑directory binary search, linear record scan, the compact physical record format, and the handling of signed integers and next‑record offsets, with GDB debugging examples.
An experiment with a three‑node MySQL Group Replication (MGR) cluster shows that even when flow control is disabled, adding significant network latency to a single node reduces overall throughput, because MGR’s multi‑paxos protocol makes the delayed node a performance bottleneck during its turn as leader.
This article creates three MySQL tables with auto‑increment, UUID, and Snowflake‑generated random keys, runs identical insert workloads using Spring Boot and JdbcTemplate, measures performance, compares index structures, discusses the pros and cons of each key type, and concludes with best‑practice recommendations.
The fifth episode of the MySQL 冲冲冲 livestream explores the trade‑offs between sharding and middleware architectures, discusses distributed transaction challenges, compares DBLE with MyCat and other solutions, and provides practical guidance for developers and DBAs handling high‑throughput MySQL workloads.
After replacing a MySQL server's CPU and motherboard, the instance fails to start due to a missing MLOG_CHECKPOINT marker in the redo log, and the article explains the underlying cause, recovery options using innodb_force_recovery levels, and step‑by‑step data restoration.
This article explains how MySQL indexes work, compares binary trees, B‑Tree and B+Tree structures, describes their implementation in MyISAM and InnoDB storage engines, and provides practical guidelines for creating efficient single‑column, composite, and covering indexes to improve query performance.
This article introduces a free, open‑source MySQL client extension for VSCode that works across Windows, macOS, and Linux, highlights its keyword suggestions, fast execution, and visual database browsing, and provides step‑by‑step installation instructions, source link, and stability details.
The MySQL Clone Plugin, added in 8.0.17, enables fast local or remote cloning of InnoDB data by installing the plugin, issuing CLONE commands, monitoring progress via performance_schema tables, and using the resulting GTID or binlog coordinates to build replicas, while observing limitations such as DDL restrictions and single‑instance operation, and offering advantages over XtraBackup through an extra page‑copy stage and simplified GTID handling.
This article explains the concept of MySQL InnoDB MERGE_THRESHOLD, how page merge and split affect performance, demonstrates how to configure the threshold for tables and indexes, and shows how to monitor and evaluate its impact using InnoDB metrics.
This article explains MySQL’s essential logging mechanisms—binlog, redo log, and undo log—covering their purposes, formats, configuration parameters, flushing strategies, and roles in replication, recovery, and transaction atomicity, while comparing their differences and illustrating concepts with diagrams.
This article presents a curated list of 100 essential MySQL interview questions covering indexes, transaction fundamentals, storage engine differences, table design, query optimization, and common pitfalls, providing concise explanations and practical examples to deepen developers’ understanding and prepare them for technical interviews.
This article investigates how the MySQL 8.0 group_replication_member_expel_timeout parameter influences node expulsion in a group replication cluster when network latency is introduced, describing the test environment, methodology, commands, observations, and configuration recommendations.
This article explains how InnoDB’s page size, row size, and B+‑tree structure determine that a single B+ tree can hold roughly twenty‑million rows, shows how to calculate tree height from the root page, and demonstrates the concepts with SQL queries and practical examples.
This article presents a comprehensive methodology for database performance optimization, covering objectives, key metrics, common pitfalls, a step‑by‑step process, instance‑level best practices, and detailed parameter‑tuning tables for Oracle, MySQL (InnoDB), PostgreSQL, and DM databases.
This article provides a step‑by‑step tutorial on using Sharding-JDBC with Spring Boot to implement horizontal database and table sharding across multiple MySQL instances, including project setup, Maven dependencies, entity and controller code, sharding configuration, and testing via Postman.
This article explains how MySQL implements transaction ACID properties by using redo and undo logs, lock mechanisms, and MVCC, detailing the roles of each component in achieving atomicity, durability, isolation, and consistency for reliable concurrent data processing.
This article explains why using indexes does not guarantee fast queries, analyzes slow‑query causes such as full index scans and poor filter selectivity, and demonstrates advanced MySQL optimizations including virtual columns and index condition push‑down to reduce row scans and costly table lookups.
This article explains how to identify slow‑query symptoms, use system and SQL‑level indicators, apply various database tools, follow best‑practice writing tips, read execution plans, and walk through a complete MySQL optimization case to dramatically improve performance.
This article compares MySQL's B+ tree indexing with Elasticsearch's inverted index, explaining underlying data structures such as hash tables, ordered arrays, balanced binary trees, skip lists, and term dictionaries, and discusses optimization techniques like bitmap intersections for efficient query processing.
This article explains MySQL 8.0 window functions, provides clear definitions, demonstrates basic and advanced usage with sample tables and SQL queries, and lists all available window functions, helping readers apply ranking and cumulative calculations in real‑world database scenarios.
This article outlines the challenges of MySQL backup restoration, categorizes common data loss scenarios, and provides detailed step‑by‑step procedures for full backup recovery, incremental binlog recovery, and optimized workflows using parallel replication and binlog‑server tricks to reduce downtime.
This article explores the underlying mechanisms MySQL uses to enforce transaction isolation levels, detailing how InnoDB leverages locking, multi-version concurrency control, gap locks, and explicit versus implicit locks to prevent dirty reads, non‑repeatable reads, and phantom reads across the four standard isolation levels.
This article details a two‑month journey of reading MySQL 8.0.20 source code to uncover how the DELETE statement performs mark‑delete, describing the experimental setup, trace‑log analysis, query and delete phases, and the exact functions that set the deletion flag in InnoDB.
This article explains MySQL's table, row, and page lock levels, the mechanics of next‑key locks, common deadlock scenarios with practical examples, and offers concrete SQL techniques and InnoDB strategies to prevent and troubleshoot deadlocks.
This article investigates why MySQL often consumes far more physical memory than the configured InnoDB buffer pool size, explains the high‑water‑mark effect, and explores Linux process memory layout, memory allocators, and MySQL’s internal memory management mechanisms.
This article explains how MySQL transactions behave when a client’s network connection is abruptly lost, why the server does not automatically abort the transaction, and provides practical scripts and configuration tips to detect and kill long‑running idle transactions.
This page provides a comprehensive collection of links to over a hundred interview‑focused articles covering Java, Spring, Redis, MySQL, concurrency, design patterns, and other backend development topics, organized by issue number for easy reference.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
After completing a DOM‑based XML parsing class, I tackled the messy Zookeeper configuration hierarchy for MySQL services, exposing unexpected node structures, missing values, and hundreds of database settings, and finally crafted a Groovy script that traverses the XML, filters relevant keys, and logs the extracted configuration details.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article explains how MySQL 8.0’s JSON data type enables range‑based traversal of JSON arrays, demonstrates extracting array elements with path expressions, and provides stored‑procedure examples—both a traditional loop version and an optimized version using the new "$[m to n]" syntax.
This guide provides step‑by‑step instructions for exporting entire MySQL databases or individual tables, importing data, performing common DDL and DML operations such as creating, altering, renaming, and dropping databases and tables, managing fields, and configuring user permissions, all illustrated with concrete command‑line examples.
This article introduces Python's DB‑API for interacting with various databases, explains how to use PyMySQL and SQLAlchemy for MySQL, demonstrates basic CRUD operations, safeguards against SQL injection, and shows how to implement thread‑safe connection pools with DBUtils for scalable backend applications.
This article walks through creating a Python web scraper that extracts financial data for over three thousand listed companies, starting from a simple pandas script and progressively adding error handling, MySQL storage, and multiprocessing to build a robust, production‑ready tool.
Version 3.20.09.0 of the open‑source DTLE data‑transfer component for MySQL has been released, introducing replication‑delay and memory‑usage monitoring with Prometheus, providing configuration examples and Docker commands, and fixing incremental serialization, CPU usage, and uppercase‑where clause handling.
The article explains how MySQL stores persistent data while Redis serves read‑only data, outlines read/write request handling, discusses consistency issues in low and high concurrency scenarios, and provides practical solutions such as cache invalidation, queue‑based updates, and deployment considerations.
This article explains the four transaction isolation levels, defines phantom reads, and details how MySQL's InnoDB engine uses MVCC snapshot reads and next‑key locking (including the next‑key principle and lock composition) to prevent phantom reads under the REPEATABLE READ isolation level.
This article presents a comprehensive Q&A guide for developers covering MySQL index structures, transaction principles, table schema design, storage engine choices, and various practical issues such as char vs varchar, binlog formats, large pagination, and stored procedures.
The article explains how a MySQL instance on a CentOS 7.1 VM repeatedly suffered connection failures due to out‑of‑memory (OOM) events triggered by a mis‑configured 40 GB hugepage allocation that remained reserved for MySQL even after the large‑page setting was disabled.
This article examines why naïve LIMIT‑OFFSET pagination on a multi‑million‑row MySQL table becomes increasingly slow, presents several optimization techniques—including simple LIMIT usage, sub‑query id lookup, ID‑range queries, and temporary‑table tricks—along with concrete benchmark results to guide developers toward faster data paging.
This article explains common MySQL performance pitfalls such as large OFFSET in LIMIT clauses, implicit type conversion, sub‑query updates, mixed sorting, EXISTS usage, condition push‑down limitations, and demonstrates how rewriting queries with indexes, JOINs, UNION ALL, and WITH clauses can dramatically reduce execution time.
This article explains why using an index does not guarantee fast MySQL queries, examines how execution time, index selectivity, full index scans, and costly back‑to‑table lookups affect slow‑query logs, and presents practical optimization techniques such as virtual columns and index condition push‑down.
This article explains how MySQL implements transaction features such as atomicity, durability, and isolation by using redo logs, undo logs, lock mechanisms, and MVCC, and discusses the role of each component in ensuring data consistency and reliability.
This tutorial explains how to create a test database, define tables with foreign key constraints, populate them with appropriate random data using mysql_random_data_load, and work around a known bug that forces the foreign‑key sample size to 100 rows.
This article introduces ClickHouse, a column‑store OLAP database, explains its advantages over row‑store systems, guides installation on macOS, details migration strategies from MySQL, presents performance benchmarks showing 200‑fold speedups, and discusses synchronization methods, pitfalls, and practical tips.
Learn how to compile MySQL 8.0 from source on CentOS 7, including installing dependencies, creating users, configuring build options, setting up systemd services, initializing the database, and securing the root account, with complete command examples and configuration file details.
The livestream discusses MySQL's high‑availability architectures compared with Oracle's, analyzes share‑nothing versus share‑everything designs, logical versus physical logging, and concludes that MySQL's share‑nothing and logical binlog replication are better suited for modern financial‑industry workloads, while also covering audience Q&A and upcoming topics.
This article explains how MySQL calculates table and index statistics, distinguishes between persistent and non‑persistent statistics, details relevant InnoDB parameters, shows when and how statistics are updated, and provides example queries and commands for manual analysis.
This article explains what MySQL master‑slave replication is, outlines its benefits, details the underlying mechanisms and event types, and provides a comprehensive, command‑line tutorial for configuring both master and slave servers, complete with code snippets and illustrative diagrams.
This article explains MySQL’s logical architecture, query processing stages, client‑server protocol, query cache, parsing, optimization, execution engine, and provides practical performance‑tuning advice such as index design, data‑type choices, covering indexes, limit pagination, and handling UNION and JOIN operations.
This article introduces pt-query-digest from Percona Toolkit, explains how to install the toolkit, configure MySQL slow‑query logging, run pt-query-digest on the slow log, interpret its detailed output, and locate specific SQL statements for performance tuning.
This article provides a comprehensive overview of fundamental MySQL concepts, covering DDL, DML, DCL, and TCL commands, detailed explanations of first to third normal forms, common anomalies, and practical examples of converting between horizontal and vertical table structures using SQL scripts.
This guide provides a step‑by‑step tutorial on downloading, installing, configuring, and starting Alibaba Canal and its adapter to achieve real‑time incremental data synchronization from MySQL binlog to destinations such as Kafka, including code snippets and configuration details.
This article explains how MySQL transforms eligible subqueries into semi‑joins, describes the four semi‑join strategies, and details the Semi‑join Materialization approach—including Materialization‑scan and Materialization‑lookup—while showing how to identify the chosen strategy via EXPLAIN output.
This article provides a detailed, step‑by‑step guide to MySQL covering its purpose, installation, directory layout, common commands, data types, table creation and modification, constraints, functions, subqueries, joins, custom functions, stored procedures, transaction control, and user management, illustrated with extensive code examples and screenshots.
This article explains why phantom reads occur under MySQL's REPEATABLE READ isolation, how InnoDB uses MVCC for snapshot reads and next‑key locks for current reads, and demonstrates the mechanisms with code examples and version‑tracking diagrams.
This guide provides a comprehensive overview of MySQL, covering its definition, installation steps for Windows and Linux, connection commands, database and user management, permission handling, table creation and alteration, CRUD operations, and essential data types, all illustrated with practical code examples.
This guide explains what Canal is, its key features and limitations, the underlying binlog replication principle, and provides detailed, step‑by‑step instructions for downloading, configuring, and launching both Canal Server and Canal Adapter to achieve high‑performance real‑time data synchronization.
A hidden bug occurs when a user's VIP subscription is repeatedly extended, causing the stored expiration timestamp to exceed MySQL's signed INT maximum of 2147483647 (January 19, 2038), which prevents the date from being saved correctly.
This article presents twenty practical rules for optimizing MySQL indexes—including when to use or avoid indexes, how to write queries that can leverage them, the importance of index selectivity, prefix indexes, covering indexes, and common pitfalls—to help developers write faster, more efficient SQL statements.
This article introduces ClickHouse as a high‑performance columnar OLAP database, compares it with row‑based systems, details migration methods from MySQL, presents performance benchmarks showing dramatic speed gains, discusses data synchronization strategies, explains why ClickHouse is fast, and shares common pitfalls and solutions.
