This article examines common techniques for implementing a user check‑in function in web and mobile applications, comparing a straightforward MySQL table solution with a Redis bitmap approach, detailing the data structures, code examples, workflow, and the advantages and disadvantages of each method.
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.
This article provides a comprehensive overview of MySQL fundamentals, covering transaction properties and isolation levels, MVCC mechanics, differences between InnoDB and MyISAM, query execution flow, redo and binlog, index structures, common pitfalls, and practical optimization and scaling strategies.
Lufax completed a two‑year, zero‑downtime, 100% Oracle‑to‑MySQL migration across its entire financial platform, detailing the dual‑write strategy, fine‑grained batch switching, tool‑driven automation, and the resulting cost and operational benefits for over 45 million users and supporting critical transaction services.
This article introduces ClickHouse, compares column‑store and row‑store databases, shows how migrating a 50‑million‑row MySQL table to ClickHouse reduced query time from minutes to under one second, and shares practical installation, migration, performance testing, and synchronization tips.
MySQL startup failures are typically caused by either insufficient system resource access (such as file permissions or security policies) or incorrect configuration parameters, and can be diagnosed by checking permissions, security policies, and the order and content of MySQL option files.
This article explains the severe impact of MySQL slow queries, shows how to enable and configure the slow query log, demonstrates using EXPLAIN to analyze query execution, and provides concrete optimization techniques for indexes, pagination, and common pitfalls.
This guide walks you through removing old MySQL versions, installing MySQL 8.0 via YUM or binary packages, configuring a secure initial password, setting up multiple instances on different ports, and addressing typical MySQL 8.0 pitfalls such as user creation, remote root access, authentication plugins, and character set settings.
This article provides a detailed walkthrough of MySQL protocol analysis using Wireshark, covering SSL disabling, packet capture commands, step‑by‑step examination of handshake, authentication, database selection, query execution packets, and an in‑depth look at related source‑code functions and command enums.
This comprehensive guide explains what NoSQL is, contrasts non‑relational and relational databases, outlines the main representatives of each, dives deep into MongoDB architecture, features, use‑cases and limitations, and also covers MySQL fundamentals, replication, backup, and common monitoring tools like Prometheus and Zabbix.
This article explains how to investigate a MySQL crash when core dumps are disabled by extracting the crash address from the error log, obtaining the matching binary, using GDB to map the address to source code, and decoding encoded function signatures to pinpoint the underlying bug.
This article explains the functional and performance differences among MySQL's count(1), count(*), and count(column) functions, illustrates how NULL handling varies, and provides a concrete example demonstrating their results and execution efficiency under different table schemas.
This article introduces ClickHouse, compares column‑ and row‑oriented storage, explains a real‑world migration from MySQL to ClickHouse that reduced a 3‑minute query to under one second, details installation, migration methods, performance results, synchronization options, and common pitfalls.
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.
In the eighth episode of the MySQL冲冲冲 live series, experts discuss the rise of Chinese domestic databases such as TiDB, OceanBase, and Jusheng, comparing their architectures, ecosystems, and market prospects while reflecting on the broader implications for MySQL‑based distributed systems.
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.
The article describes how the Wukong Activity Platform’s data‑persistence layer uses Node.js as a BFF with MySQL, combines raw drivers, optional ORMs, and a custom lightweight Node‑MyBatis framework that offers dynamic SQL templating, built‑in injection protection, declarative transaction decorators, and automatic TypeScript type generation for full‑stack JavaScript development.
This article explains why multithreaded inserts into a single MySQL table can be faster than single-threaded inserts, analyzes the time distribution of insertion operations, and presents techniques such as prepared statements, multi-value inserts, batch execution, and transaction batching to significantly improve insert throughput.
The article explains how MySQL processes SELECT and UPDATE statements, detailing each execution stage—from client authentication and cache lookup to parsing, optimization, and execution—while highlighting the additional redo log and binlog handling required for UPDATE operations and offering practical code examples.
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 why MySQL indexes dramatically improve query speed, outlines common reasons for slow SQL, shows how to use EXPLAIN to analyze execution plans, and provides step‑by‑step optimization examples with code and visual illustrations.
This article explains why GTID‑based replication is preferred for synchronizing data from four regional factories to an IDC, and provides a step‑by‑step guide—including data export, GTID configuration, master‑slave channel setup, adding a new slave, and important precautions—for successful multi‑source MySQL replication.
This article explains why a MySQL query that uses an index can still become a slow query, explores the difference between index usage and execution time, and presents practical optimization techniques such as improving selectivity, reducing back‑table lookups, using index condition push‑down, and leveraging virtual columns.
This article details how to improve performance of massive MySQL tables on Alibaba Cloud by redesigning schemas and indexes, migrating to PolarDB with read‑write separation, partitioning historical data using the X‑Engine storage engine, and leveraging PolarDB's parallel query capabilities for faster analytics.
This article explains how MySQL's CASE WHEN, IF, and IFNULL functions can be used to implement conditional logic in SQL queries, providing syntax, examples, and combinations with aggregate functions for common data‑analysis scenarios.
This article compiles 100 common MySQL interview questions covering index structures, hash vs B‑tree trade‑offs, clustering, transaction isolation levels, lock types, primary key choices, storage engines, data types, binlog formats, pagination strategies, and best practices for schema design and query optimization.
This article examines common MySQL pitfalls—including signed vs. unsigned integers, auto‑increment key requirements, varchar length versus byte count, case‑insensitive collations, and storing emoji characters—providing concrete SQL examples, error screenshots, and configuration tips to help developers avoid unexpected errors.
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 explores four practical methods—batch inserts, transaction wrapping, ordered primary‑key insertion, and their combined use—to dramatically improve MySQL InnoDB insert performance, presents test results across various data volumes, and offers key configuration cautions.
This article explains how MySQL Group Replication (MGR) can provide zero‑RPO high‑availability within a city‑scale data center, why it needs asynchronous replication for WAN‑scale disaster recovery, and walks through a step‑by‑step setup—including code examples—for automatic failover of asynchronous replication channels.
This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.
A production MySQL query that omitted its leftmost indexed column caused a full‑table scan, CPU overload, and a read‑only alert, prompting a rapid investigation, temporary index addition, and a deeper discussion of the left‑most matching principle, Java validation pitfalls, and best‑practice lessons.
The DTLE 3.20.10.0 release introduces full and incremental job status output, a reschedule option to prevent unlimited retries, code refactoring, and provides repository, documentation, and download links for the open‑source MySQL data‑transfer component, followed by a light‑hearted personal note.
When a MySQL table grows large, CRUD performance degrades, so this article presents comprehensive optimization techniques—including field design, indexing, query rewriting, engine selection, system parameters, hardware upgrades, read‑write separation, caching, partitioning, vertical and horizontal splitting, sharding architectures, and recommendations for compatible scalable databases—to help maintain high performance and scalability.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article compiles essential MySQL interview questions covering data types, storage engines, locks, transactions, triggers, index creation and usage, join types, and practical SQL optimization techniques with concrete examples and code snippets for developers to master database fundamentals.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This guide explains how to identify bottlenecks and boost MySQL performance through both soft (query, index, schema) and hard (hardware, configuration, sharding, caching) optimizations, offering practical commands, best‑practice tips, and visual illustrations for each step.
This article explains the structure of MySQL ROW‑mode binlogs, demonstrates how to modify binlog events with Python to recover deleted rows, locate oversized transactions, and split large transactions into smaller ones, providing practical scripts and examples for advanced database manipulation.
This article explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, shows several SELECT queries to list duplicates, and provides multiple DELETE strategies—including sub‑queries and multi‑column handling—to safely remove excess records while keeping one copy.
Database auditing records user actions to detect illegal operations, with approaches ranging from application‑layer and transport‑layer monitoring to kernel‑level and plugin solutions; TXSQL’s MySQL‑compatible audit plugin offers both synchronous and asynchronous modes, delivering flexible rule configuration and only 3‑6 % performance overhead, making it a low‑impact, feature‑rich choice for compliance and forensics.
This article provides a comprehensive overview of MySQL’s logging mechanisms—including Binlog, Undo log, Redo log, and Change Buffer—explaining their concepts, roles in replication and crash‑recovery, recording formats, flush timing, two‑phase commit, checkpoint handling, and how they interact during data modifications.
This article explains MySQL lock types—table, row, and page locks—their algorithms, common causes of deadlocks, and provides multiple practical examples with SQL statements and diagrams, followed by InnoDB lock‑prevention strategies and detailed analysis of concurrent delete scenarios.
This article presents a comprehensive MySQL database standard covering table creation rules, SQL writing conventions, and index design recommendations, explaining the rationale behind each mandatory or suggested practice to improve performance, maintainability, and consistency across projects.
This article compares MySQL's three data‑removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space and auto‑increment values, and best‑practice usage with code examples and optimization tips.
This guide explains how MySQL handles string‑date conversions, shows when to use date_format for query conditions, warns about performance impacts, demonstrates converting query results with str_to_date, and clarifies the meaning of common format placeholders.
The article explains that using LIMIT with DELETE in MySQL improves safety, reduces lock time, prevents long‑running transactions, and enhances performance, and it provides syntax, examples, and expert opinions to illustrate the benefits of this habit.
This article explains MySQL master‑slave replication by covering SBR, RBR, and MBR concepts, step‑by‑step configuration of master and slave servers, binlog format settings, the underlying replication protocol, key SQL commands, and troubleshooting tips, all illustrated with real code snippets and diagrams.
This article explains how to identify which slave in a MySQL one‑master‑multiple‑slave semi‑synchronous setup is slowing the master’s commit performance by using debug logging and examining the error log for the last ACK server ID.
This guide walks you through MySQL's EXPLAIN command, explaining each output column, demonstrating how to read execution plans for simple selects, joins, subqueries, UNIONs, and advanced variants, and showing how to use this information to improve query performance.
This article explores eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, subquery updates, mixed sorting, EXISTS clauses, condition push‑down, early result narrowing, and intermediate result push‑down—and provides rewritten SQL examples that dramatically reduce execution time.
This article explains the various MySQL index implementations—including B‑Tree, B+Tree, hash, clustered, non‑clustered, and covering indexes—illustrates their internal structures with diagrams, provides SQL examples, and offers practical guidelines for selecting and using indexes efficiently.
This article evaluates two techniques for accelerating rollback of large MySQL transactions—dynamically enlarging the InnoDB buffer pool and using innodb_force_recovery=3 with instance shutdown—by presenting experimental steps, code examples, performance measurements, and practical recommendations.
This article introduces MySQL 8.0.21's built‑in multi‑threaded logical backup utility, compares it with legacy tools like mysqldump and mysqlpump, demonstrates usage of dump_instance, dump_schemas and load_dump with Python‑style code snippets, and summarizes performance results and recovery steps.
This guide explains how to recover a lost MySQL root password by editing the MySQL configuration to skip grant tables, restarting the service, and then setting a new password via SQL commands, with platform-specific steps for Windows, macOS, and Linux.
This article compiles the most frequently asked MySQL‑related questions from developers and operations teams in banks, covering why MySQL is chosen over Oracle, compatibility gaps, migration methods, sharding strategies, performance tuning, consistency guarantees, and resource planning.
MySQL 8.0.22 introduces prepared‑statement improvements, a new SHOW PROCESSLIST implementation via Performance Schema, UTC timestamp handling, read‑only schemas, error‑log access through Performance Schema, user‑management enhancements, optimizer push‑down, replication auto‑failover, Router scalability upgrades, and several deprecations and removals.
This article details how a high‑traffic e‑commerce platform migrated from a single MySQL instance to a sharded architecture using Sharding‑JDBC, added Elasticsearch‑HBase for multi‑dimensional queries, and implemented zero‑downtime data migration and scaling strategies.
This article explains why adding a LIMIT clause to MySQL DELETE statements is a good habit, compares it with TRUNCATE, shows the correct syntax, outlines performance and safety advantages, and discusses practical strategies for deleting large numbers of rows.
This article explains why MySQL's default InnoDB storage engine uses B+ trees instead of B trees or hash indexes, covering storage engine basics, query performance, I/O considerations, and the structural advantages that make B+ trees ideal for OLTP workloads.
This article explains how InnoDB’s B+ tree storage works, calculates the approximate number of rows a single tree can hold based on page size, record size, and tree height, and shows how to determine the tree height from the tablespace file.
This article details a real‑world case where an RDS MySQL instance ran out of disk space due to an oversized temporary tablespace, explains the root‑cause analysis of a massive Cartesian‑product query, and provides step‑by‑step remediation and preventive measures for both MySQL 5.7 and 8.0.
This guide walks through the complete deployment of Spinnaker on Kubernetes using Halyard, covering architecture analysis, preparation of Redis, MySQL and MinIO, container image retrieval, BOM preparation, Halyard configuration, external Redis and SQL database integration, authentication, canary analysis, monitoring, and additional settings.
This article explains the three primary ways to remove data in MySQL—DELETE, TRUNCATE, and DROP—comparing their execution speed, underlying mechanisms, impact on disk space, transaction behavior, and how to reclaim space with OPTIMIZE, while providing practical SQL examples and usage warnings.
This article examines MySQL pagination on a multi‑million‑row table, compares plain LIMIT queries with subquery, ID‑range, and temporary‑table techniques, provides SQL examples and performance measurements, and offers recommendations for fast, scalable paging.
This article explains why appending a LIMIT clause to MySQL DELETE statements improves safety, reduces lock time, and boosts performance, while comparing three deletion strategies and recommending the most efficient approach for large‑scale data removal.
This article traces MySQL's four‑decade history, its acquisition by Sun and Oracle, the rise of MariaDB as a compatible fork, and provides a practical guide to installing and using MariaDB with screenshots and tool recommendations.
This article explains how MySQL InnoDB locates and deletes a specific row by describing the buffer‑pool lookup, B‑tree page traversal, page‑directory binary search, linear record scan, the compact physical record format, and the handling of signed integers and next‑record offsets, with GDB debugging examples.
An experiment with a three‑node MySQL Group Replication (MGR) cluster shows that even when flow control is disabled, adding significant network latency to a single node reduces overall throughput, because MGR’s multi‑paxos protocol makes the delayed node a performance bottleneck during its turn as leader.
This article creates three MySQL tables with auto‑increment, UUID, and Snowflake‑generated random keys, runs identical insert workloads using Spring Boot and JdbcTemplate, measures performance, compares index structures, discusses the pros and cons of each key type, and concludes with best‑practice recommendations.
The fifth episode of the MySQL 冲冲冲 livestream explores the trade‑offs between sharding and middleware architectures, discusses distributed transaction challenges, compares DBLE with MyCat and other solutions, and provides practical guidance for developers and DBAs handling high‑throughput MySQL workloads.
After replacing a MySQL server's CPU and motherboard, the instance fails to start due to a missing MLOG_CHECKPOINT marker in the redo log, and the article explains the underlying cause, recovery options using innodb_force_recovery levels, and step‑by‑step data restoration.
This article explains how MySQL indexes work, compares binary trees, B‑Tree and B+Tree structures, describes their implementation in MyISAM and InnoDB storage engines, and provides practical guidelines for creating efficient single‑column, composite, and covering indexes to improve query performance.
This article introduces a free, open‑source MySQL client extension for VSCode that works across Windows, macOS, and Linux, highlights its keyword suggestions, fast execution, and visual database browsing, and provides step‑by‑step installation instructions, source link, and stability details.
The MySQL Clone Plugin, added in 8.0.17, enables fast local or remote cloning of InnoDB data by installing the plugin, issuing CLONE commands, monitoring progress via performance_schema tables, and using the resulting GTID or binlog coordinates to build replicas, while observing limitations such as DDL restrictions and single‑instance operation, and offering advantages over XtraBackup through an extra page‑copy stage and simplified GTID handling.
This article explains the concept of MySQL InnoDB MERGE_THRESHOLD, how page merge and split affect performance, demonstrates how to configure the threshold for tables and indexes, and shows how to monitor and evaluate its impact using InnoDB metrics.
This article explains MySQL’s essential logging mechanisms—binlog, redo log, and undo log—covering their purposes, formats, configuration parameters, flushing strategies, and roles in replication, recovery, and transaction atomicity, while comparing their differences and illustrating concepts with diagrams.
This article presents a curated list of 100 essential MySQL interview questions covering indexes, transaction fundamentals, storage engine differences, table design, query optimization, and common pitfalls, providing concise explanations and practical examples to deepen developers’ understanding and prepare them for technical interviews.
This article investigates how the MySQL 8.0 group_replication_member_expel_timeout parameter influences node expulsion in a group replication cluster when network latency is introduced, describing the test environment, methodology, commands, observations, and configuration recommendations.
This article explains how InnoDB’s page size, row size, and B+‑tree structure determine that a single B+ tree can hold roughly twenty‑million rows, shows how to calculate tree height from the root page, and demonstrates the concepts with SQL queries and practical examples.
This article presents a comprehensive methodology for database performance optimization, covering objectives, key metrics, common pitfalls, a step‑by‑step process, instance‑level best practices, and detailed parameter‑tuning tables for Oracle, MySQL (InnoDB), PostgreSQL, and DM databases.
This article provides a step‑by‑step tutorial on using Sharding-JDBC with Spring Boot to implement horizontal database and table sharding across multiple MySQL instances, including project setup, Maven dependencies, entity and controller code, sharding configuration, and testing via Postman.
This article explains how MySQL implements transaction ACID properties by using redo and undo logs, lock mechanisms, and MVCC, detailing the roles of each component in achieving atomicity, durability, isolation, and consistency for reliable concurrent data processing.
This article explains why using indexes does not guarantee fast queries, analyzes slow‑query causes such as full index scans and poor filter selectivity, and demonstrates advanced MySQL optimizations including virtual columns and index condition push‑down to reduce row scans and costly table lookups.
This article explains how to identify slow‑query symptoms, use system and SQL‑level indicators, apply various database tools, follow best‑practice writing tips, read execution plans, and walk through a complete MySQL optimization case to dramatically improve performance.
This article compares MySQL's B+ tree indexing with Elasticsearch's inverted index, explaining underlying data structures such as hash tables, ordered arrays, balanced binary trees, skip lists, and term dictionaries, and discusses optimization techniques like bitmap intersections for efficient query processing.
This article explains MySQL 8.0 window functions, provides clear definitions, demonstrates basic and advanced usage with sample tables and SQL queries, and lists all available window functions, helping readers apply ranking and cumulative calculations in real‑world database scenarios.
This article outlines the challenges of MySQL backup restoration, categorizes common data loss scenarios, and provides detailed step‑by‑step procedures for full backup recovery, incremental binlog recovery, and optimized workflows using parallel replication and binlog‑server tricks to reduce downtime.
This article explores the underlying mechanisms MySQL uses to enforce transaction isolation levels, detailing how InnoDB leverages locking, multi-version concurrency control, gap locks, and explicit versus implicit locks to prevent dirty reads, non‑repeatable reads, and phantom reads across the four standard isolation levels.
This article details a two‑month journey of reading MySQL 8.0.20 source code to uncover how the DELETE statement performs mark‑delete, describing the experimental setup, trace‑log analysis, query and delete phases, and the exact functions that set the deletion flag in InnoDB.
This article explains MySQL's table, row, and page lock levels, the mechanics of next‑key locks, common deadlock scenarios with practical examples, and offers concrete SQL techniques and InnoDB strategies to prevent and troubleshoot deadlocks.
This article investigates why MySQL often consumes far more physical memory than the configured InnoDB buffer pool size, explains the high‑water‑mark effect, and explores Linux process memory layout, memory allocators, and MySQL’s internal memory management mechanisms.
This article explains how MySQL transactions behave when a client’s network connection is abruptly lost, why the server does not automatically abort the transaction, and provides practical scripts and configuration tips to detect and kill long‑running idle transactions.
This page provides a comprehensive collection of links to over a hundred interview‑focused articles covering Java, Spring, Redis, MySQL, concurrency, design patterns, and other backend development topics, organized by issue number for easy reference.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
After completing a DOM‑based XML parsing class, I tackled the messy Zookeeper configuration hierarchy for MySQL services, exposing unexpected node structures, missing values, and hundreds of database settings, and finally crafted a Groovy script that traverses the XML, filters relevant keys, and logs the extracted configuration details.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article explains how MySQL 8.0’s JSON data type enables range‑based traversal of JSON arrays, demonstrates extracting array elements with path expressions, and provides stored‑procedure examples—both a traditional loop version and an optimized version using the new "$[m to n]" syntax.
