The article demonstrates how a large OFFSET in a MySQL LIMIT query forces MySQL to scan millions of index rows and data pages, causing severe slowdown, and shows that rewriting the query with an inner‑join sub‑select dramatically reduces I/O and buffer‑pool usage, confirming the performance difference through experiments.
This article explains the meaning of the 'Pages flushed up to' value in MySQL's InnoDB engine, how it differs from the 'Last checkpoint' LSN, the underlying logic for its calculation, and provides test results demonstrating its behavior during writes and checkpoints.
The article explains how MySQL 8.0’s new doublewrite buffer implementation dramatically slows LOAD performance on a 1 GB file, identifies the innodb_doublewrite_pages default setting as the root cause, and shows that increasing it to 64 restores speeds comparable to MySQL 5.7.
This article compares MySQL's COUNT(*), COUNT(1) and COUNT(column) functions, explains how each handles NULL values, analyzes performance differences on MyISAM and InnoDB engines, and provides practical recommendations for writing efficient count queries and existence checks.
This article explains why using a large OFFSET with MySQL LIMIT can cause severe performance degradation, demonstrates the problem with a real‑world 9.5 million‑row table, and shows how rewriting the query with a sub‑query that selects only primary keys can cut execution time from seconds to milliseconds.
This article examines MySQL’s locking mechanisms—including shared and exclusive row locks, gap and next‑key locks, table‑level and metadata locks—detailing how different SQL statements and index types affect lock acquisition, illustrating lock behavior with examples, and offering practical strategies to avoid deadlocks.
An in-depth investigation of a MySQL InnoDB startup deadlock caused by truncate operations and the innodb_flush_sync parameter, detailing log observations, stack traces, source code examination, and three practical solutions including GDB intervention, parameter adjustment, and data directory cleanup.
This article compiles a comprehensive set of MySQL interview questions covering index types, the differences between MyISAM and InnoDB, the rationale for auto‑increment primary keys, sharding strategies, ACID properties, isolation levels, storage‑engine trade‑offs, normalization, index best practices, data types, performance tuning, locking mechanisms, B+Tree characteristics, and common SQL operations.
This article explains why MySQL's DELETE command only marks rows as reusable without shrinking the .ibd file, describes the underlying B+‑tree storage mechanism, and provides practical solutions such as OPTIMIZE TABLE, ALTER TABLE, and online DDL options to reclaim space and avoid table‑level locks.
This article dissects MySQL's InnoDB storage engine by first exposing the internal page layout, then explaining the clustered index organization, and finally detailing the slot mechanism used for intra‑page queries, while illustrating insertion strategies and space‑reclamation techniques.
This article explains why MySQL uses locks, describes table‑level, row‑level, and page‑level locking, outlines lock types such as shared, exclusive, and intention locks, and provides practical usage examples and common troubleshooting tips for InnoDB and other storage engines.
This article explains the dangers of slow SQL in MySQL, walks through the query execution flow, details InnoDB storage engine architecture and index types, and provides practical strategies for indexing, query rewriting, and configuration to eliminate performance bottlenecks.
This article explains how MySQL’s InnoDB engine stores data on disk and in memory, detailing the structure of data pages, the role of hidden columns, record linking, page headers, footers, and directory mechanisms that ensure efficient access and durability.
During a performance test a MySQL server showed one CPU core constantly at 99% while the others were idle, prompting a detailed investigation that checks I/O thread settings, multi‑core utilization, transaction locks, slow‑query logging, and index optimization to resolve the bottleneck.
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
This article explains the various types of auto‑increment identifiers in MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, thread_id—and discusses their maximum values, overflow behavior, and alternative solutions such as using Redis for external unique keys.
When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.
This article explains how MySQL's InnoDB engine implements ACID properties by detailing transaction atomicity, consistency, isolation levels, lock granularity, MVCC version chains, undo/redo logs, buffer pool behavior, and the interplay between redo log and binlog to ensure durability and data integrity.
The article presents a detailed interview‑style walkthrough of MySQL InnoDB locking mechanisms, covering table‑ versus row‑level locks, shared and exclusive locks, intention locks, gap and next‑key locks, auto‑increment locks, predicate locks, and related SQL statements, illustrated with tables and code examples.
This article explains why MySQL's auto‑increment counter can be lost after a server restart for InnoDB tables, compares the behavior with MyISAM, demonstrates the problem with practical SQL examples, and shows how MySQL 8.0 fixes the counter logic.
This article provides a comprehensive collection of MySQL optimization techniques—including storage engine characteristics, data type selection, index design, and query rewriting—targeted at teams without a dedicated DBA, using MySQL 5.5 and InnoDB as the reference platform.
The article explains why deleting rows in MySQL does not shrink the physical table file, describes the underlying InnoDB storage mechanisms, and provides practical methods such as OPTIMIZE TABLE, ALTER TABLE, and Online DDL to reclaim space and reorganize the table.
This article explains MySQL deadlocks, describes InnoDB lock types, shows how to read deadlock logs, analyzes classic concurrency cases, and offers practical tips to design indexes and write SQL that minimize lock conflicts and avoid deadlocks.
This article demonstrates how to simulate accidental deletion of a MySQL InnoDB .ibd file, observe the impact on queries, recover the file using the file descriptor in /proc, restart MySQL, and verify that various DDL operations can detect the missing tablespace.
This article explains the root causes of MySQL deadlocks in InnoDB, walks through transaction and lock fundamentals, analyzes real‑world log cases, and presents practical solutions such as adjusting isolation levels, using SELECT FOR UPDATE, adding unique indexes, or checking existence before deletion to eliminate deadlocks.
This article provides a comprehensive overview of InnoDB architecture, covering its memory structures, background threads, and key features like checkpoint mechanisms, double write, and neighbor page flushing, essential for understanding MySQL's default storage engine.
This article explains how B+Tree improves disk I/O efficiency in MySQL InnoDB by detailing disk storage fundamentals, sector/block/page concepts, the differences between B‑Tree and B+Tree, and practical search examples that illustrate reduced I/O operations and faster queries.
This article demonstrates how to use a GDB script to monitor the exact moments MySQL checks for a killed DDL operation, identifies the internal InnoDB functions involved, and explains why kill may not take effect immediately.
This article analyzes why MySQL connections remain in a KILLED state after a KILL command, detailing the internal execution flow, thread‑state handling, signal‑based wake‑ups, and reproducing cases that expose rollback, lock‑wait, and concurrency issues that delay connection termination.
The article explains how PolarDB's Polar Index redesigns InnoDB B‑Tree structural modification (SMO) by splitting the operation into two phases, eliminating the global index latch, reducing lock granularity, and achieving up to 11× performance gains in high‑concurrency workloads such as TPCC.
This article explains MySQL index fundamentals, the B+Tree structure, differences between MyISAM and InnoDB implementations, practical indexing strategies, and performance‑tuning techniques such as configuration parameters, EXPLAIN analysis, and optimal index design for real‑world queries.
The article explains how MySQL uses different memory areas such as Thread Memory, Sharing, and InnoDB Buffer Pool, describes the impact of large user tables on query performance, and shows that when a table size exceeds the InnoDB buffer pool capacity, sharding becomes necessary.
This article reviews the history of MySQL and MariaDB, describes a test environment, runs insertion, batch, and query benchmarks (with and without indexes), and concludes that MariaDB generally outperforms MySQL in speed and memory usage while highlighting trade‑offs of indexing.
The article explains why deleting rows in MySQL does not shrink the .ibd file because InnoDB only marks pages as reusable, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains how to monitor MySQL full-text indexes by describing relevant InnoDB parameters, the metadata tables that expose index activity, and step‑by‑step examples that create a sample table, configure monitoring, observe cache behavior, and manage index maintenance operations.
The article explains why an INSERT INTO SELECT statement can lock an entire InnoDB table in MySQL, demonstrates the lock behavior with transaction examples, and offers two solutions—adding an index on the filtered column and adjusting the transaction isolation level—to avoid table‑wide blocking.
This article explains InnoDB's storage hierarchy (sector, block, page), calculates how many rows fit in a 16KB page, shows how B+‑tree height and pointer counts determine total record capacity, and demonstrates the I/O cost of primary and secondary index lookups using practical MySQL commands.
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.
