This article explains how MySQL InnoDB guarantees the ACID properties—Atomicity, Consistency, Isolation, and Durability—through its lock mechanisms, multi‑version concurrency control, undo/redo logs, buffer pool management, and the interaction between binlog and redo log.
This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.
This article examines MySQL InnoDB spin‑lock tuning by adjusting innodb_spin_wait_delay and innodb_sync_spin_loops, describing spin‑lock mechanics, detailing an experiment with performance_schema metrics, and concluding that modest spin settings are preferable to aggressive tuning.
This article explains why MySQL deadlocks occur in a test module, details table and row lock mechanisms, reproduces the deadlock scenario with two sessions, analyzes lock logs, and provides a solution by adding a composite index to prevent the conflict.
This article explains why deleting rows in MySQL does not decrease the physical table file size, describes the underlying InnoDB storage mechanisms, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains MySQL full-text (inverted) indexes, their underlying auxiliary tables, buffer pool configuration, document ID handling, and the special transaction semantics required for inserting and querying indexed data, providing practical SQL examples and performance considerations.
This article explains why learning MySQL is important, outlines the focus of a multi‑part series covering MySQL's underlying principles, installation on macOS, basic architecture components, InnoDB features, and what readers can gain such as SQL optimization and cluster building skills.
This article examines a real‑world MySQL deadlock caused by the Index Merge optimizer, explains InnoDB's lock‑on‑index mechanism, walks through the deadlock logs, and presents four practical solutions including force‑index hints, disabling Index Merge, creating a composite index, and a two‑step update approach.
This article explains MySQL InnoDB’s four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—detailing the phenomena of dirty reads, non‑repeatable reads, and phantom reads, and provides step‑by‑step SQL demonstrations to illustrate each level’s behavior and practical implications.
This article explains MySQL indexing—especially InnoDB indexes—covering what indexes are, the various index types, underlying B‑Tree and B+Tree structures, clustered vs. secondary indexes, hash indexes, when to create indexes, and optimization techniques such as index condition push‑down.
This article explains how many rows a single InnoDB B+‑tree can hold by describing InnoDB’s 16 KB page size, the storage of data and index pages, calculating rows per leaf page, pointer capacity, tree height for typical workloads, and how to determine the height from the page‑level metadata.
This article explains MySQL's table‑lookup (回表) process, the difference between primary‑key and secondary indexes, and how the index‑pushdown feature introduced in MySQL 5.6 reduces unnecessary lookups by filtering rows directly in the index.
The article explains why MySQL raises a GTID consistency error when a single transaction updates both InnoDB and MyISAM tables, demonstrates how to reproduce the issue, and evaluates three mitigation strategies: separating the updates, converting MyISAM to InnoDB, or disabling ENFORCE_GTID_CONSISTENCY.
This article explains MySQL's lock granularity (table, row, page), the different lock modes such as next‑key, gap and record locks, illustrates common deadlock scenarios with detailed SQL examples, and discusses InnoDB's deadlock prevention strategies and best‑practice solutions.
This article explains MySQL's table, row, and page lock levels, describes next‑key, gap, and record locks, illustrates common deadlock scenarios with detailed SQL examples, and outlines InnoDB's lock acquisition and deadlock‑prevention strategies to help developers avoid concurrency issues.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree leaf page can hold, derives the total record capacity for trees of different heights, and shows how to determine the actual B+‑tree height in a MySQL table using page metadata.
This article demonstrates a step‑by‑step GDB experiment that isolates the InnoDB dirty‑page flushing thread, applies pressure to MySQL, observes metrics and log checkpoints, and confirms that slow dirty‑page flushing can block business operations.
This article explains how InnoDB’s 16 KB pages form B+‑tree indexes, calculates the number of rows a tree can hold at different heights, shows how to determine the tree’s height from the tablespace file, and why MySQL prefers B+ trees for indexing.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree can hold based on page size, record size and pointer size, demonstrates how to determine the tree height from the tablespace file, and summarizes the impact on query I/O.
This article explains MySQL's three lock levels, the algorithms behind next‑key, gap, and record locks, illustrates common deadlock scenarios with real SQL examples, and outlines InnoDB's lock‑prevention strategies to help developers avoid and resolve deadlocks.
This article explains the fundamentals of MySQL indexes, the B+Tree data structure, differences between MyISAM and InnoDB implementations, practical tips for creating effective composite indexes, and essential server configuration and SQL tuning techniques to dramatically improve query performance.
This article explains why multiple InnoDB buffer pool instances are recommended, describes a simple experiment that shows how a table's data pages are distributed across two buffer pool instances in a 64‑page alternating pattern, and discusses the performance reasons behind this behavior.
This article explains the concept of locks in computing and databases, compares InnoDB and MyISAM storage engines, details transaction isolation phenomena such as dirty reads, non‑repeatable reads and phantom reads, and explores InnoDB lock types, lock algorithms, intention locks, auto‑increment lock modes, and MVCC mechanisms.
This article explains MySQL's lock levels (table, page, row), the next‑key, gap, and record lock algorithms, illustrates common deadlock scenarios with detailed session examples, and discusses InnoDB's deadlock prevention strategies and practical solutions for avoiding lock conflicts.
This article provides a detailed explanation of MySQL InnoDB's lock system, covering lock granularity, shared and exclusive locks, intention and auto‑increment locks, various row‑lock types, deadlock scenarios with examples, and the internal representation of locks in the engine.
This article explains InnoDB's multi‑granularity locking system—including table, row, intention, auto‑increment, and gap locks—detailing their modes, compatibility, internal representation, usage guidelines, deadlock scenarios, and best practices for avoiding concurrency issues in MySQL databases.
This article provides a comprehensive overview of MySQL InnoDB's lock subsystem, describing its data structures, lock compatibility matrices, lock/unlock workflows, lock splitting, inheritance and migration, and presents several optimization techniques such as avoiding lock_sys mutex, asynchronous deadlock detection, and lock_sys mutex sharding to alleviate contention in MySQL 5.7 and later versions.
This article experimentally demonstrates how increasing MySQL's innodb_log_buffer_size reduces redo‑log write I/O, showing fewer write operations but similar total data volume, and explains why performance gains may be subtle in typical virtual‑machine storage environments.
During a holiday‑time alert, a seemingly harmless pair of UPDATE statements on the same InnoDB table caused a deadlock, and the article walks through log analysis, reproduction steps, lock mechanics, and practical ways to avoid such deadlocks in MySQL.
This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.
This article traces MySQL’s evolution, introduces MariaDB’s origins, and presents a comprehensive benchmark comparing insert and query performance of MySQL 8.0.19 and MariaDB 10.4.12 on identical hardware, highlighting speed differences with and without indexes.
This article explains the fundamentals of MySQL table lookup (回表操作), compares low‑version and high‑version handling, and demonstrates how index pushdown reduces unnecessary lookups, using clear diagrams and a practical user‑table example.
This article provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
This article explains how MySQL's index‑merge optimization can cause row‑level deadlocks during inventory updates, analyzes the lock sequence and deadlock logs, and presents practical solutions such as forcing a specific index, disabling index‑merge, and creating a composite index.
The article explains MySQL transaction ACID properties, details the four isolation levels and their associated anomalies, describes InnoDB's record, gap, and next‑key locks, and provides concrete SQL examples illustrating how these mechanisms prevent phantom reads and cause lock‑wait timeouts.
This article explains InnoDB's record storage, primary and secondary index mechanics, composite index rules, and provides practical e‑commerce sharding and table‑design guidelines with real‑world case studies to help developers optimize MySQL performance and storage efficiency.
This article examines the root causes of MySQL deadlocks in online services, explains transaction and lock mechanisms such as UndoLog and RedoLog, analyzes real‑world cases, and presents practical strategies—including isolation level adjustments and pre‑delete checks—to avoid and resolve deadlock issues.
This article explains how MySQL InnoDB generates AUTO_INCREMENT values, compares the full‑table locking mechanism before version 5.1 with the lightweight mutex introduced in 5.1, and describes the further improvements in 8.0 that rely on row‑based replication and lock‑mode settings.
The article reviews MySQL 8.0.23's maintenance release, detailing new features such as invisible columns, query attributes, security enhancements, InnoDB auto‑extend size, replication terminology changes, X protocol improvements, and various deprecations, providing code examples and links to official documentation.
The article explains the concept and requirements of primary keys in MySQL, outlines best practices such as avoiding updates and reuse, and discusses how primary keys serve as clustered indexes in InnoDB, highlighting the performance advantages of using business‑agnostic auto‑increment IDs over random keys.
This article explains the complete MySQL architecture, covering its connection, service, storage‑engine, and system‑file layers, the role of each component such as connection pools, query cache, parser, optimizer, executor, and the differences between InnoDB and MyISAM, providing practical insights for developers and interview preparation.
This article explains how to use MySQL's SHOW FULL PROCESSLIST command, interpret its output, kill problematic threads, and address metadata lock issues by inspecting InnoDB transactions and adjusting lock_wait_timeout, providing practical code examples for database administrators.
This article investigates a MySQL InnoDB deadlock observed during a holiday period, explains how gap locks and next‑key locks on a composite index cause mutual waiting, reproduces the issue with large test data, and offers practical guidelines to avoid similar deadlocks in production environments.
This article explains MySQL transaction fundamentals, covering ACID concepts, isolation levels, MVCC implementation, transaction start methods, and practical examples that illustrate how InnoDB ensures atomicity, consistency, isolation, and durability while avoiding common pitfalls such as dirty reads and long‑running transactions.
This article explains why MySQL uses B+‑tree indexes, describes the principles of B‑tree and B+‑tree structures, compares MyISAM and InnoDB index implementations, and provides practical optimization tips such as using auto‑increment primary keys, the left‑most prefix rule, and proper configuration settings.
This article explains why MySQL uses B+ trees for indexing, describes the underlying principles of various index types, compares MyISAM and InnoDB implementations, and provides practical optimization guidelines such as using auto‑increment primary keys, left‑most prefix rules, and configuration tuning.
This article explains MySQL locking mechanisms, covering lock concepts, isolation level interactions, lock granularity (row, table, page), lock types (shared, exclusive, record, gap, next-key), InnoDB row‑lock implementation, deadlock causes and remedies, and the differences between optimistic and pessimistic locking.
The article investigates why a MySQL instance experiences frequent "Opening tables" states, analyzing stack traces, identifying a data dictionary lock held by the dict_stats_thread, and concluding that slow disk writes trigger InnoDB's synchronous dirty‑page flush, blocking many operations.
This article explains the different index mechanisms used by MySQL (MyISAM and InnoDB) and Lucene, compares them with Elasticsearch's inverted index, and discusses how these structures affect storage, memory usage, and query performance.
This article explains how MySQL's InnoDB engine applies row‑level locks for DELETE and SELECT statements under various index configurations and isolation levels, covering MVCC, snapshot vs current reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
The article explains how MySQL's Online DDL handles VARCHAR column expansions, demonstrates performance differences across character sets using sysbench, and clarifies why certain size changes trigger in‑place alterations while others require full table copies, providing practical guidance for DBAs.
This comprehensive guide explains MySQL's binary log, undo log, redo log, and ChangeBuffer, covering their concepts, purposes, recording formats, flush timing, recovery processes, and how they interact during transactions and crash‑safe recovery.
This article explains the fundamentals of MySQL InnoDB page size, how it interacts with operating system pages, and the performance implications for rows, indexes, and tablespaces, providing practical guidance for choosing optimal page sizes in production environments.
The article examines the hidden lock‑contention issue in InnoDB’s Adaptive Hash Index (AHI) observed during high‑concurrency sysbench runs, explains why multiple threads block on the AHI hash table’s exclusive lock, and presents a lightweight fallback optimization that checks the lock before building AHI, reducing lock wait time and stabilizing QPS performance.
The article details a MySQL server startup failure caused by insufficient memory, shows command outputs and error logs, and explains how reducing the innodb_buffer_pool_size to 64 MB in the my.cnf configuration resolves the issue on a 2 GB Alibaba Cloud instance.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
This article explains the distinct purposes of MySQL's InnoDB redo log and server‑level binlog, how they work together during large update operations, the concepts of write position, checkpoint, and two‑phase commit, and why both logs are essential for durability and replication.
This article investigates why MySQL 5.7 crash recovery can take hours on a development instance with thousands of tables, detailing stack‑frame analysis, GDB debugging, InnoDB source inspection of validation logic, performance testing, and methods to bypass the table‑space verification step.
This article details how Vivo's cloud service tackled explosive data growth by applying horizontal and vertical sharding, routing‑table based dynamic expansion, and MySQL InnoDB compression, providing step‑by‑step guidance, performance results, and practical recommendations for large‑scale database deployments.
This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
This article explains MySQL's lock levels, describes various lock algorithms, presents multiple deadlock scenarios with SQL examples, analyzes the root causes, and outlines InnoDB's deadlock prevention mechanisms and detection methods for developers dealing with concurrency issues.
After upgrading a critical MySQL 5.6 table to 5.7, the system began reporting duplicate‑key errors on master, replica and read‑only instances due to unexpected changes in the AUTO_INCREMENT value, prompting a deep kernel investigation, bug analysis, on‑site reproduction, and practical mitigation steps.
The author recounts his 2012 MySQL interview experience, describes the global InnoDB team structure, outlines the bug‑fix and feature‑development workflow, lists key contributions such as spatial indexes and transparent encryption, and reflects on leaving Oracle for Tencent Cloud.
Vivo Cloud Service scaled from millions to tens of millions of users by applying four sharding strategies—horizontal table partitioning, horizontal database splitting, vertical partitioning, and dynamic routing—while enabling InnoDB compression, which halved storage size, kept CPU impact modest, and preserved ample free capacity for future growth.
Allen Lai recounts his unexpected recruitment by Oracle, four interview rounds with MySQL's InnoDB leaders, the global and flat team structure, the disciplined bug‑fix workflow, his major feature contributions, and why he later moved to Tencent Cloud's CDB engine.
The author recounts his 2012 MySQL interview experience, describes the flat, globally distributed InnoDB team structure, details the typical bug‑fix workflow, lists major InnoDB features he contributed to, and reflects on the lessons learned before moving to Tencent Cloud's database division.
This article explains how MySQL indexes work, comparing binary search trees, red‑black trees, hash tables, B‑trees, B+‑trees, and the differences between MyISAM and InnoDB implementations, and it details primary, secondary, covering, and composite index usage with practical examples.
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.
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 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 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 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.
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.
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 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 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 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 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 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 how InnoDB implements row locks, gap locks, and next‑key locks to prevent phantom reads, describes how the lock ranges are determined, and demonstrates their effects through multiple transaction scenarios with detailed SQL code examples.
This article provides a comprehensive examination of MySQL/InnoDB locking, covering MVCC, snapshot vs. current reads, cluster indexes, two‑phase locking, isolation levels, detailed lock‑behaviour for various index and isolation combinations, complex query lock analysis, and deadlock detection techniques.
This article examines whether MySQL's count(1) is faster than count(*), explains how InnoDB processes both expressions identically, compares them with count(column), and offers practical advice on avoiding costly full‑table counts.
An overview of MySQL 8.0’s release timeline, including major GA versions, and a detailed summary of its new capabilities such as transactional data dictionary, atomic DDL, enhanced security, role support, InnoDB improvements, JSON enhancements, optimizer extensions, backup lock, connection management, and other performance and management features.
This article demonstrates how to recover mistakenly deleted InnoDB .ibd files by locating the open file handle through Linux procfs, locking database traffic, copying the hidden file, and restoring it to a running MySQL instance.
This article explains the InnoDB logical storage hierarchy of segments, extents, and pages, then details MySQL horizontal partitioning concepts, types (RANGE, LIST, HASH, KEY), example DDL and DML, and discusses how partitioning impacts performance for OLTP and OLAP workloads.
This article explains the scenarios where MySQL compressed tables are appropriate, describes how to monitor their health using InnoDB CMP tables in information_schema, and provides practical examples of creation, performance comparison, and update/delete operations to illustrate best‑practice usage.
The article explains how CDB introduced an InnoDB index physical space usage statistic to help users decide when to run OPTIMIZE TABLE, describes the background of page fragmentation, compares incremental and full‑scan methods, and details a sequential I/O optimization that dramatically reduces measurement time.
This article explains the purpose, structure, and usage scenarios of MySQL's binlog, redo log, and undo log, detailing how they support replication, crash recovery, and transaction atomicity while balancing performance and data safety.
This article explains why using indexes does not guarantee fast MySQL queries, examines full index scans, index selectivity, the cost of back‑table lookups, and demonstrates advanced techniques such as index condition push‑down and virtual columns to improve performance.
This article explains what database indexes are, outlines their advantages and disadvantages, advises when to create or avoid them, compares common index data structures such as B+Tree, Hash, and FullText, and details MySQL's MyISAM and InnoDB index implementations.
This article explains the concept of MySQL compressed tables, highlights strong compression products, discusses why and when to use them, outlines their advantages and drawbacks, details supported compression algorithms, and provides step‑by‑step examples for both MyISAM and InnoDB engines along with their impact on B‑tree pages and the InnoDB buffer pool.
This article explains the different lock mechanisms used by MySQL storage engines—including MyISAM table‑level locks and InnoDB row‑level locks—covers lock compatibility, scheduling, transaction properties, optimistic locking, and practical strategies to avoid lock conflicts and deadlocks.
The article repeats a performance test on a 140 GB MySQL 8 database (innodb_buffer_pool_size=140G) using various innodb_buffer_pool_instances values, showing that increasing the instances improves throughput up to 64 instances, after which no further gains are observed.
