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.
This article analyzes a severe MySQL performance issue caused by massive user‑access logs, then presents three solution paths—optimizing the existing database, migrating to a compatible high‑performance database, and adopting big‑data technologies—detailing design best practices, indexing, partitioning, sharding, and cloud‑native options to restore query speed and scalability.
This article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query optimizations, ID‑range queries, and temporary‑table methods—by testing them on a table with millions of rows and presenting detailed timing results that reveal significant speed improvements.
This article examines MySQL's recommendation to use auto_increment primary keys instead of UUID or Snowflake IDs by creating three tables, running insertion benchmarks with Spring Boot, comparing index structures, and discussing the performance trade‑offs and practical drawbacks of each approach.
This article demonstrates how to create a test database, export data with mysqldump, and then replay a large SQL dump into a busy MySQL instance while throttling the import speed with the pv tool to keep CPU usage low and avoid disrupting ongoing business operations.
This article analyzes the performance problems of a massive MySQL 5.6 RDS table storing billions of user‑activity records and presents three practical solutions—optimizing the existing schema and queries, migrating to a MySQL‑compatible high‑performance database, and adopting a big‑data platform—detailing design, indexing, partitioning, sharding, and cloud‑native options.
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 explains MySQL's various join algorithms—including Simple Nested Loop, Index Nested Loop, Blocked Nested Loop, and unsupported Hash and Merge joins—detailing their mechanisms, performance trade‑offs, and optimization strategies such as MRR, BKA, join buffers, and driver‑table selection.
This guide walks you through creating and using a one‑click installation package that sets up essential development services—MySQL, Nginx, JDK, Redis, RabbitMQ, and a SpringBoot jar—on Ubuntu 16.x, detailing directory structure, required prerequisites, individual start/stop scripts, and customization options for small‑scale deployments.
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 analyzes a 60‑second MySQL query that retrieves article comments with multiple filters, explains why the optimizer chooses a small table as the driver, and presents a step‑by‑step optimization—including avoiding semi‑joins, improving index usage, refining range conditions, and moving GROUP BY into a subquery—that reduces execution time to 1.3 seconds, achieving a 60‑fold speedup.
This article provides a comprehensive guide to the pt-online-schema-change (pt-osc) tool for MySQL online DDL, covering its underlying mechanism, limitations, installation steps, command‑line syntax, practical examples, and a comparative analysis with native Online DDL and gh‑ost to help users choose the appropriate solution.
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.
This article explains MySQL’s six log files, focusing on the redo log, undo log, and binary log, detailing their purposes, contents, generation and release timing, associated physical files, configuration parameters, and their roles in transaction durability and replication.
This article analyses the root cause of generic "exception" errors during article publishing caused by mismatched MySQL wait_timeout and Druid JDBC pool settings, explains the impact of these parameters, and provides step‑by‑step configuration adjustments and alternative solutions to stabilise the backend database connection pool.
