The article explains how InnoDB handles transaction commit by modifying Insert and Update Undo segment states, generating a transaction commit number, adding rollback segments to the purge queue, and finally cleaning or caching Undo segments based on their usage and state.
The article examines why deploying MySQL in Docker containers can lead to data‑security, performance, state‑management and resource‑isolation problems, outlines mitigation strategies, discusses scenarios where containerization is feasible, and concludes with a call for community discussion while also featuring promotional material for AI‑related services.
An in‑depth investigation reveals why MySQL versions prior to 5.7.31 incorrectly treat back‑ticked asterisks as column names, causing “Unknown column ‘*’” errors, and shows how systematic testing and version‑specific patches resolved the issue for DBAs and developers.
The paper details a live‑streaming highlight system that integrates heterogeneous data sources, uses a three‑stage pipeline with MySQL/Redis storage, applies sliding‑window interval optimization and AI‑driven title generation, scoring, and segment selection, managed by a shared state‑machine, and outlines future stability and observability improvements.
This article explains how to speed up PHP database interactions by using persistent connections, implementing a connection pool, and caching query results, providing clear code examples for each technique to improve overall efficiency.
This tutorial explains how to implement dynamic master‑slave MySQL switching in a SpringBoot 3.0.4 project using AOP and a custom @DataSource annotation, covering configuration, enum definitions, utility classes, aspect handling, and usage examples for both single and multiple data sources, including Oracle integration.
This article thoroughly examines MySQL join operations, detailing the fundamentals, inner workings, and performance characteristics of Nested‑Loop Join, Block Nested‑Loop Join, and Hash Join, while providing practical optimization techniques, parameter tuning, and decision‑making guidance for different data sizes and query patterns.
This article explains how to read MySQL's EXPLAIN EXTRA column, covering SEMI JOIN hints such as FirstMatch, Start/End temporary, LooseScan, and the Range‑checked‑for‑each‑record optimization, with concrete examples and command‑line output to illustrate each case.
This article explores four practical methods for synchronizing MySQL data to Elasticsearch—including synchronous and asynchronous double writes, SQL extraction, and binlog real‑time replication—while reviewing popular migration tools such as Canal, Alibaba DTS, and Databus to help you choose the right solution.
This guide explains MySQL 8's main directory layout, the locations of command binaries and configuration files, how databases and system schemas are represented on the file system, and the differences between InnoDB and MyISAM storage engines, including tablespace and file naming conventions.
This article explains how to use PHP's mysqli_num_rows function to retrieve the number of rows in a MySQL result set, providing a complete example that connects to a database, executes a query, checks for errors, obtains the row count, and outputs it.
This article explores advanced SQL query optimization techniques for large datasets, covering sorting methods, GROUP BY improvements, pagination strategies, covering indexes, and prefix index considerations, with practical examples, EXPLAIN analyses, and recommendations to reduce filesort usage and enhance performance.
This guide walks through identifying slow MySQL queries using EXPLAIN, explains each output field, and presents practical optimization techniques—including proper index usage, pagination tricks, join improvements, and ORDER/GROUP BY tuning—to dramatically boost query performance.
This article explains how to monitor system resources using commands like top, iostat, and vmstat, shows how to enable and view MySQL slow query logs, offers troubleshooting tips, and also promotes a ChatGPT community with related services and offers.
The article explains ACID properties, details how inadequate isolation levels cause dirty reads, non‑repeatable reads, and phantom reads, illustrates each with transaction scenarios and diagrams, and shows how MySQL’s lock types and isolation settings prevent these anomalies, noting the trade‑off between isolation and concurrency.
The article recounts a developer’s experience modernizing a legacy PHP and MySQL codebase that lacked version control, detailing the challenges of disorganized code, security risks, and inefficient workflows, and describing the step‑by‑step solutions implemented to introduce backups, local Git, security hardening, and a structured development process.
Learn how to implement dynamic MySQL master‑slave switching in a SpringBoot project using AOP and custom annotations, ensuring automatic failover to the master when a slave fails, with detailed configuration, code examples, and guidance for multiple data sources and Oracle integration.
This article explains how to read and parse the Undo log generated by a DELETE operation in MySQL 8.0.32 InnoDB, covering preparation, log extraction, detailed field parsing, locating the primary index record, and the step‑by‑step rollback of secondary and primary index records.
This article explains how to handle MySQL disk‑space alerts by using the pt‑online‑schema‑change (pt‑osc) tool to selectively retain needed rows, migrate data to a temporary table, and drop the original table, thereby achieving fast, low‑impact data cleanup.
This guide explains why VARCHAR, float, and double are unsuitable for monetary values, and recommends using BIGINT/Long for cent‑level precision or BigDecimal/DECIMAL for higher precision, with practical tips and code examples for Java and MySQL.
This guide explains how to use Spring Boot, AOP, and custom annotations to implement dynamic MySQL master‑slave data source switching, automatically falling back to the master when a slave fails, covering configuration, Maven dependencies, enum definitions, data source beans, routing logic, and usage examples.
This article explains the fundamentals of MySQL master‑slave replication, detailing how binlog‑based data syncing works and providing a complete Docker‑based deployment guide—including configuration files, user creation, replication commands, and troubleshooting tips for a one‑master‑two‑slave cluster.
This guide introduces four powerful MySQL performance‑tuning utilities—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run, and interpret their reports to identify configuration issues, memory overload, and slow‑query problems.
table_open_cache limits the total simultaneously opened tables in MySQL, using a global cache complemented by per‑thread Table_cache instances to reduce lock contention; mis‑sizing this cache—especially when DTS forces many full‑table scans on key‑less tables—can cause latency, memory bloat, and LRU evictions, so monitor Opened_tables and adjust the setting judiciously.
This article introduces ShardingSphere's three components—Sharding-JDBC, Sharding-Proxy, and the upcoming Sharding-Sidecar—explains why sharding is needed for relational databases, demonstrates horizontal data sharding concepts, and provides a complete Spring Boot example with configuration, code, and asynchronous batch insertion.
This article compiles essential MySQL interview questions covering data types, char vs varchar, TRUNCATE vs DELETE, triggers, storage engines, lock mechanisms, transactions, stored procedures, index fundamentals, creation syntax, performance impact, and practical optimization techniques with example SQL code.
This guide explains MySQL's regular‑expression capabilities—including REGEXP, NOT REGEXP, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR and their RLIKE equivalents—covering syntax, multibyte safety, match‑type flags, and practical usage examples.
This MySQL course provides an extensive curriculum covering advanced functions, transaction management, backup and recovery, user permission handling, and multi‑table relationships, presented through a series of video modules that guide learners from fundamental concepts to practical, production‑level implementations.
The article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and finally SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for multiple business units and their dynamic product lists while respecting development constraints.
This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.
This article investigates the time cost of establishing and closing a MySQL connection from a Java web application, using Wireshark packet captures and simple code examples to quantify latency and illustrate why connection pooling is essential for high‑traffic services.
This article explains how MySQL 8.0 InnoDB generates and stores Delete Undo logs, detailing the log structure, field meanings, type_flag and info_bits decoding, address composition via DB_ROLL_PTR, and provides concrete SQL examples and shell calculations to illustrate the process.
After a careless rm -rf command wiped an entire production server—including MySQL data—the author chronicles a two‑day rescue using ext3grep, extundelete, and binary log restoration, sharing step‑by‑step commands, scripts, and hard‑won lessons to prevent future catastrophes.
This article walks through optimizing a Java‑based XML‑to‑MySQL import, showing how to cut processing time from 300 seconds to just 4 seconds by enabling JDBC batch writes, using rewriteBatchedStatements, applying async writes with Disruptor, and tuning MySQL settings.
This article presents a detailed case study where the ChatDBA AI assistant diagnoses and resolves a gh-ost migration stall caused by large table size and VARCHAR length expansion, compares its response with DeepSeek, and showcases the tool's expert-mode root‑cause analysis and actionable recommendations.
This article explains the fundamentals of SQL injection attacks, demonstrates step‑by‑step exploitation using the DVWA platform—including data extraction, login bypass, and injection point detection—and outlines practical prevention techniques such as input validation, regex filtering, and prepared statements.
This guide explains why real‑time data synchronization between MySQL and services like Redis, Memcached, Solr, or Elasticsearch is essential in modern micro‑service architectures, and demonstrates four synchronization methods—inline code, scheduled tasks, message queues, and Canal—complete with setup, configuration, and Java code examples.
This article presents two real‑world MySQL recovery scenarios—full backup restoration after an accidental table deletion and incremental recovery using XtraBackup—detailing backup strategies, binlog extraction, step‑by‑step commands, and validation procedures to safely restore production databases.
This guide walks through building a three‑node lab, installing MariaDB, configuring master‑slave binary‑log replication, creating a test database, and then deploying MySQL‑Proxy on an application server to achieve transparent read/write splitting between the master and slave.
The article explains how inserts work in relational databases, why fixed‑size pages and batch transactions dramatically reduce I/O and lock contention, and provides practical MyBatis guidelines—using the foreach tag, ExecutorType.BATCH, and an appropriate batchSize while estimating memory and disk limits to choose an optimal batch size and commit only once.
This article explains MySQL’s transaction isolation mechanisms, compares the four isolation levels with concrete examples, shows how they affect phenomena like dirty reads, non‑repeatable reads and phantom reads, and provides practical guidance on configuring and avoiding long transactions.
This article recounts the development of ZzoOnlineDDL, a MySQL schema‑change platform, detailing its 1.0 limitations, the 2.0 architectural upgrades, feature set—including intelligent tool selection, timed execution, sharding support, monitoring, and retry mechanisms—and lessons learned from real‑world incidents such as MDL locks, disk pressure, and unique‑index pitfalls.
This guide shows how to implement dynamic MySQL master‑slave switching in a SpringBoot 3 project using AOP and a custom @DataSource annotation, configuring Druid pools, defining a routing datasource, and automatically falling back to the master when a slave fails for high availability.
After publishing 50 installments of the 'MySQL Core Modules' series covering transactions, locks, and undo, the author expresses gratitude to readers and the community, outlines upcoming articles on Undo, MVCC, and Redo, and reviews past content while planning a compiled book.
The article walks through a common database requirement—grouping rows, sorting within each group, and extracting the first (or top N) records—by preparing sample data, comparing MySQL window functions, subqueries, and JOIN solutions, adding index optimizations, and demonstrating equivalent Elasticsearch aggregations, all backed by concrete performance measurements.
This article explains MySQL's storage engine architecture, compares built‑in engines such as InnoDB and MyISAM, details their file system interactions, table‑space types, and transaction features—including ACID properties, isolation levels, locking mechanisms, and practical migration steps for production environments.
This article experimentally compares the performance of various MySQL COUNT queries on an InnoDB table with one million rows, explains the underlying execution plans using EXPLAIN, analyzes primary‑key versus secondary‑index behavior, and contrasts InnoDB’s fast count implementation.
By switching from a naïve per‑record insert to JDBC batch writes with rewriteBatchedStatements and then off‑loading those batches to multiple consumer threads via a LMAX Disruptor ring buffer, the XML‑to‑MySQL import of 60,000 rows dropped from roughly 300 seconds to about 4 seconds while keeping memory usage modest.
This article explains why MySQL alone struggles with large‑scale, complex queries, introduces Elasticsearch as a high‑performance search engine, and compares several synchronization approaches—including synchronous and asynchronous dual‑write, Logstash, binlog real‑time sync, Canal, and Alibaba Cloud DTS—highlighting their pros, cons, and suitable scenarios.
This article provides a comprehensive, step‑by‑step guide to essential MySQL commands—including connection, process inspection, variable queries, lock information, transaction status, engine details, character set checks, import/export, index management, table creation, stored procedures, and view creation—complete with example code snippets for each operation.
This guide explains MySQL’s error, general, binary, and slow query logs, covering default settings, how to enable or modify them, binary log formats and recovery procedures, as well as tools for analyzing slow queries, providing a comprehensive reference for database administrators.
This article explains MySQL’s logical architecture, details the storage‑engine API and its key components such as connectors, parsers, optimizer, caches, and file system interaction, and provides step‑by‑step guidance on creating, opening, locking, scanning tables and building a custom storage engine.
This guide explains MySQL transaction fundamentals, ACID properties, row and table locking mechanisms, and demonstrates how to implement them in Python using pymysql, including connection pooling, context managers, and a reusable SQL helper class for robust database operations.
This article explains how to analyze MySQL slow queries by examining response time, scanned rows, and returned rows, demonstrates EXPLAIN output with and without indexes, and covers index types, column ordering, left‑most prefix rules, and sorting strategies to improve database performance.
MySQL 9.2 introduces a host of updates—including new CREATE_SPATIAL_REFERENCE_SYSTEM privileges, memory‑optimised statement binding, corrected character‑set handling, revamped compilation options, component enhancements, deprecations, optimizer tweaks, performance‑schema improvements, pluggable authentication fixes, and numerous bug fixes across InnoDB, replication, and utilities—providing a comprehensive overview of the release’s technical changes.
This article explains how MySQL 8.0.32's InnoDB engine generates, reads, and parses Undo logs for an UPDATE operation, and details the step‑by‑step process of locating the primary record, constructing a rollback record, and restoring both secondary and primary index entries.
This article experimentally investigates whether the length of VARCHAR columns (e.g., 50 vs 500) affects MySQL storage size and query performance, covering table creation, bulk data insertion, storage queries, index and full‑table scans, and explains the underlying reasons for any differences observed.
This article explores practical strategies for handling the high‑concurrency inventory deduction problem in flash‑sale scenarios, covering lock‑based approaches, Redis caching, partitioned stock management, asynchronous updates, and distributed scaling techniques to prevent overselling and improve throughput.
MySQL 9.2.0 introduces the CREATE_SPATIAL_REFERENCE_SYSTEM privilege, JavaScript library support, enhanced SQL stored routine APIs, ENUM and SET handling, EXPLAIN FORMAT=JSON version 2, while deprecating several version‑token functions and privileges and removing keyword restrictions, providing a comprehensive overview of additions, deprecations, and removals.
This article explains the meaning and optimization strategies for various MySQL EXPLAIN EXTRA hints—including Impossible WHERE/HAVING, No matching rows, Recursive queries, Rematerialize, and canned N databases—provides example execution plans, code snippets, and performance comparisons to help developers write more efficient SQL statements.
The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves index and range‑query performance, outlines the storage savings compared to VARCHAR, mentions the need for manual conversion, and provides MySQL and Java code examples for converting between string and integer representations.
This article explains why MySQL‑Elasticsearch synchronization is needed for large‑scale queries, compares several synchronization approaches such as synchronous and asynchronous dual‑write, Logstash, Binlog, Canal, and Alibaba DTS, and discusses their advantages, disadvantages, and typical application scenarios.
This article analyzes why MySQL 8.0.13 introduced the sql_require_primary_key parameter, demonstrates how adding auto‑increment primary keys can cause master‑slave data inconsistencies, explains the underlying InnoDB row‑id behavior, and provides a step‑by‑step solution to synchronize tables.
This article examines the core challenges of high‑concurrency inventory pre‑reservation, evaluates async throttling, horizontal stock splitting, and Redis‑based write‑shielding, and presents concrete implementations, performance results, thread‑safety techniques, deadlock avoidance, and data‑consistency safeguards for robust backend systems.
This article presents a MySQL interview case that examines e‑commerce repurchase behavior using orders and users tables, defines repeat purchasers, computes average repurchase intervals and amounts, identifies high‑value customers, and aggregates results by city through CTEs, window functions, and grouped statistics.
This article explains MySQL covering indexes, how table lookups (back‑table operations) work, and the leftmost prefix rule for composite indexes, providing practical examples and tips for optimizing query performance in interview scenarios.
The article shows how to use MySQL CTEs and window functions to identify e‑commerce users who spent over 10,000 CNY in 2023, calculate their average spend and order count, determine their share of all users, and break the results down by city.
The article explains why deploying MySQL in Docker containers can lead to data loss, performance degradation, and insufficient resource isolation, while also offering strategies and scenarios where containerizing MySQL may still be feasible.
The article compares MySQL and PostgreSQL multi‑table join capabilities, explains why MySQL’s nested‑loop joins can be less efficient for complex queries, and discusses how decomposing joins into separate single‑table queries or moving logic to the service layer can improve performance, scalability, and caching.
This article explains various methods for synchronizing data between MySQL and Elasticsearch—including synchronous and asynchronous double‑write, Logstash pipelines, real‑time Binlog replication, Canal parsing, and Alibaba Cloud DTS—detailing their implementation approaches, advantages, disadvantages, and typical application scenarios.
This article describes how to dramatically speed up the import of 60,000+ XML records into MySQL by analyzing the original environment, measuring baseline performance, and applying a series of backend optimizations—including MySQL batch mode, rewriteBatchedStatements, multithreaded asynchronous writes with Disruptor, and XML parsing improvements—ultimately cutting the total runtime from five minutes to just a few seconds.
This article explains MySQL’s internal architecture, covering how data pages, B+‑tree and hash indexes, the Buffer Pool, Adaptive Hash Index, Change Buffer, Undo/Redo logs, the InnoDB storage engine, and the server layer work together to provide fast, reliable CRUD operations and support replication.
The article investigates a MyBatis batch‑insert job that consumes excessive CPU and takes 14 minutes, explains why the foreach‑generated giant INSERT statement is inefficient, and demonstrates how switching to ExecutorType.BATCH or MyBatis dynamic‑SQL batch support reduces the runtime to under two seconds.
This article investigates why MySQL recommends auto_increment primary keys over UUID or random Snowflake IDs by creating three tables with different key strategies, running Spring‑Boot/JdbcTemplate performance tests, analyzing insertion speed, index structures, and the trade‑offs of each approach.
This article explains how MySQL 8.0.32 InnoDB generates and stores Update undo logs, detailing the log format, the meaning of each field, how to interpret the log address, and provides concrete SQL examples and shell calculations for practical insight.
This article explains key MySQL interview topics—including covering indexes that let queries avoid table lookups, the back‑table process when secondary indexes lack needed columns, and the leftmost prefix rule for composite indexes—providing clear examples and optimization tips.
This article presents an e‑commerce interview case that defines three tables (orders, order_items, products), loads sample data, and walks through a step‑by‑step SQL solution using CTEs, aggregation, window functions and HAVING to calculate each customer's total spend, most expensive order, and favorite product category, filtering and sorting the results.
This article provides a comprehensive analysis of MySQL’s internal architecture, detailing the four-layer design, the server service layer’s parsing, optimization and execution components, and the critical roles of Undo Log, Redo Log, and Binlog in ensuring ACID transaction properties.
This article explains the concept of database indexes, compares various index structures such as hash tables, ordered arrays, binary trees, B‑trees and B+‑trees, details InnoDB primary, secondary, covering and composite indexes, discusses prefix indexes and order‑by optimization, and provides practical advice and SQL examples for improving MySQL query performance.
This article explains the meaning of the Extra column in MySQL EXPLAIN results, covering common values such as Using index, Backward index scan, Select tables optimized away, and Zero limit, and shows how to interpret and optimize each case with concrete examples.
This course introduces MySQL 8's latest features—including performance optimizations, security enhancements, and new data types—while deeply exploring its network communication mechanisms, advanced optimization techniques, and hands‑on projects to improve database performance and security.
This article investigates the detailed steps and time consumption of establishing and closing a MySQL connection from a Java web application, using Wireshark packet captures and code examples to demonstrate why connection pooling is essential for high‑traffic services.
This article explains how to migrate sharded Mycat data to ActionDB with ActionOMS, addressing duplicate primary keys by modifying both source and target schemas, configuring migration tools, and completing validation and post‑migration steps.
Learn practical methods to identify users who exceed three failed login attempts within ten seconds, covering both a MySQL log‑table approach with window functions and a Redis‑based solution with key expiration, plus discussion of performance trade‑offs and atomicity considerations.
This article walks through a real‑world case of importing over 60,000 XML rows into MySQL, profiling the initial 300‑second runtime, and applying a series of optimizations—including JDBC batch processing, rewriteBatchedStatements, multithreaded asynchronous writes with Disruptor, and MySQL tuning—to achieve a final import time of around 4 seconds while managing memory usage.
This comprehensive guide explains MySQL's underlying index structures (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the mechanics of query execution, the role of locks and transaction isolation levels, the MVCC model, and the InnoDB buffer‑pool caching strategy.
This article walks you through MySQL's default ports, provides a concise introduction, details how to download the Community Server, explains extraction, environment variable setup, configuration file creation, service registration, and shows essential commands for initializing, starting, stopping, and managing MySQL users on Windows.
This article investigates whether the length of VARCHAR columns in MySQL affects both the physical storage size of tables and the execution speed of various queries, revealing that storage remains unchanged while long VARCHARs can dramatically slow down full‑table sorts.
A cross‑border e‑commerce company replaced its massive MySQL instance with OceanBase, detailing the business drivers, selection criteria, two‑phase migration process, performance gains, cost savings, remaining incompatibilities, and practical operational lessons learned.
This guide compiles the most common MySQL interview questions asked by leading Chinese tech firms, covering fundamentals, data types, storage engines, indexes, transactions, locks, views, stored procedures, triggers, and optimization, with clear explanations, diagrams, and a free downloadable resource.
This article walks through a real MySQL deadlock case, explaining the background, reproducing the issue, analyzing lock information from InnoDB status, and presenting practical steps to identify root causes and fix the deadlock safely.
This article examines the core challenges of distributed data systems—including consistency, availability, and partition tolerance—then details master‑slave replication mechanisms for MySQL and Redis, various replication modes and binlog formats, and explores data partitioning, sharding, and hot‑key mitigation techniques for scalable, high‑availability deployments.
The article reports that MySQL versions 8.4.3 and 9.1.0 deliver notable performance gains across a variety of workloads, explains the benchmark methodology, presents quantitative QPS improvements, and details four key internal changes—including a binlog data‑structure switch, JOIN optimizations, and enhanced index range scans—that together raise write and read throughput by several percent.
The article investigates a MySQL deadlock that arises when multiple threads concurrently execute INSERT … ON DUPLICATE KEY UPDATE for logistics cancellations, reproduces the issue on MySQL 5.7 and 8.0, analyzes InnoDB lock handling and internal mini‑transactions, and explains how batch processing of each value leads to the deadlock.
In its 2024 year‑end review, vivo Internet Technology published 44 technical articles, delivered 19 presentations on cloud‑native, AI, security and more, joined major open‑source foundations such as Linux, CNCF and CCF, and grew a community of over 70,000 technical professionals.
This article explains the complete InnoDB undo‑log rollback process on MySQL 8.0, covering preparation, reading and parsing undo logs, constructing primary‑key index records, and the detailed steps for deleting secondary and primary index entries during a transaction rollback.
This guide explains how to overcome MySQL analytical performance bottlenecks by integrating AnalyticDB MySQL with Data Transmission Service (DTS), covering architecture, resource setup, test data creation, real‑time synchronization, and high‑performance SQL queries using window functions.
To recover accidentally deleted MySQL rows, ensure binary logging is enabled and set to ROW mode, locate the appropriate binlog files, use mysqlbinlog (or tools like binlog2sql) to extract the original INSERT events within a time window, and replay or reconstruct them to restore the data.
A MySQL data‑migration using INSERT INTO SELECT caused massive payment‑record loss due to full‑table scans and lock contention, highlighting the need for proper indexing and awareness of transaction isolation when moving large datasets.
This article explains MySQL EXPLAIN type column, detailing the inefficiencies of type=ALL, how adding ORDER BY can change it to index, and demonstrates the use of INDEX_MERGE for UNION and INTERSECT queries, including practical examples and cost comparisons to improve query performance.
