This article analyzes a real‑world MySQL incident where concurrent transactions caused an account balance to become negative, explains the underlying MVCC mechanism, consistency view rules, and how different isolation levels (RR vs RC) affect data visibility and locking behavior.
This article provides an extensive overview of MySQL fundamentals for interview preparation, covering primary key concepts, index types and usage, transaction properties and isolation levels, storage engine differences, query execution order, performance tuning with EXPLAIN, lock mechanisms, replication strategies, and high‑concurrency solutions.
This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.
The article compares MySQL and PostgreSQL across open‑source licensing, ACID compliance, SQL standard support, replication methods, concurrency control, performance, high‑availability solutions, external data sources, and storage models, highlighting each system’s strengths and weaknesses to help readers choose the most suitable database.
This article presents a comprehensive guide to MySQL performance improvement, covering maximum data volume, connection limits, query latency targets, practical implementation principles, optimal table design, index classification and tuning, as well as detailed SQL optimizations such as batch processing, operator rewriting, and pagination techniques.
This weekly newsletter presents the June DB-Engines database popularity ranking, explains its methodology, shares community resources such as Mycat diagnostics and MySQL tips, outlines DBLE and DTLE development updates, bug fixes, upcoming plans, and invites community feedback.
This guide outlines step‑by‑step methods to identify high load causes, troubleshoot replication interruptions, resolve instance hangs, and address common MySQL errors using commands like SHOW PROCESSLIST, EXPLAIN, iostat, and GTID management.
This article examines the functional requirements for likes, comments, and favorites in apps and compares MySQL and Redis based database designs, discussing schemas, queries, scalability challenges, and consistency considerations for handling moderate to massive data volumes.
This article explains MySQL execution order, highlights performance problems with large OFFSET LIMIT queries, demonstrates how implicit type conversion, sub‑query rewriting, EXISTS removal, condition push‑down, and intermediate result push‑down can dramatically improve query speed, and provides practical SQL rewrite examples.
The article defines MySQL response time as total request‑to‑reply latency, illustrates it with a travel analogy, and then details two monitoring solutions—Percona’s tcprstat utility that captures TCP‑level timings and the Query Response Time audit plugin that records latency distribution in INFORMATION_SCHEMA tables for analysis and Grafana visualization.
Meituan’s MySQL database inspection system uses a three‑layer architecture—execution agents managed by Crane, a metadata‑rich inspection database, and an integrated application UI—to run 64 automated checks, resolve over 8,000 hazards with sub‑four‑day remediation, and continuously improve automation and analytics.
This article describes the design and implementation of a data warehouse, covering storage engine choices, dimensional modeling techniques, ETL processes using Python scripts, and workflow management with Apache Airflow to address data integration, scalability, and maintenance challenges.
This article compares MySQL semi‑synchronous and asynchronous replication, presenting performance test results that show only a small difference in TPS and QPS, explains why the gap is minimal, and provides step‑by‑step instructions and sysbench scripts for setting up and benchmarking both replication modes.
A MySQL join between a large and a small table ran for seconds because differing character sets forced implicit conversions that disabled the index, and the article shows how to diagnose the issue with EXPLAIN, fix it by aligning charsets, and avoid similar pitfalls.
This article explains the internal B+Tree layout of InnoDB indexes, detailing page types, record organization, and how different primary‑key data types affect page density and overall index tree height, illustrated with concrete examples and visual diagrams.
This tutorial explains how to create a custom MySQL Docker image that automatically runs .sql, .sh, or .sql.gz initialization scripts on first container start, covering the underlying mechanism, Dockerfile steps, image build, container run, and verification tips.
This article explains the purpose of the MySQL EXPLAIN command, details each column in its output (id, select_type, table, type, possible_keys, etc.), provides real‑world SQL examples, and shows how to interpret the plan to identify indexing, join, and sorting issues for query optimization.
This comprehensive guide explains MySQL's layered architecture, storage engine plugins, index structures, transaction isolation levels, locking mechanisms, performance tuning techniques, partitioning, sharding, and replication, providing practical insights for developers and DBAs to optimize and scale MySQL deployments.
This tutorial demonstrates how to build a simple CRUD application for a WeChat mini‑program by integrating a Spring Boot backend with MyBatis‑Plus, MySQL, and the mini‑program front‑end, covering environment setup, Maven configuration, database schema, API implementation, and client‑side pages.
This article examines why MySQL is deployed on Kubernetes, outlines the technical challenges such as service routing, monitoring isolation, storage and high‑availability, highlights the benefits of cloud‑native networking and scheduling, and reviews popular MySQL operators and their implementation details.
This guide walks you through downloading DataGrip, configuring its appearance, creating MySQL and MongoDB data sources, managing tables, applying powerful SQL editing shortcuts, and leveraging export and execution‑plan features to write higher‑quality database code.
This article provides a comprehensive step‑by‑step guide for DBAs to safely upgrade MySQL from version 5.7 to 8.0, covering motivations, compatibility checks, new features, preparation tasks, upgrade commands, post‑upgrade verification, and rollback considerations.
This article examines how the increased latency of Intel's PAUSE instruction on Skylake CPUs creates a performance bottleneck for MySQL, details the profiling steps that identified ut_delay as the hotspot, and presents a series of optimizations—including spin‑wait parameter tuning and a MySQL 8.0 patch—that restore throughput on affected hardware.
This page compiles a chronological list of 100 WeChat articles covering Java interview topics, including core language concepts, data structures, concurrency, Spring, MySQL, Redis, and system design, providing readers with direct links to each issue for comprehensive preparation.
This article experimentally demonstrates how MySQL's table cache works, showing that it reduces file‑system reads of table definition files, operates per thread, and explains the recommended table_open_cache sizing formula based on concurrency and join complexity.
This article explains the MySQL communication protocol, result‑set structure, client library interfaces, and the difference between store‑result and streaming modes, then details how UPSQL Proxy 2.4.0 adopts streaming to reduce latency and memory usage in distributed database environments.
The article describes Ctrip's Data Replicate Center (DRC), a MySQL middleware that enables real‑time bidirectional replication across data‑center clusters, detailing its architecture, low‑latency optimizations, consistency mechanisms, DDL handling, monitoring, and future high‑availability improvements.
The article explains how MySQL Shell 8.0.17+ can be extended using JavaScript or Python to build reusable reports and plugins, describes the directory layout, provides a complete example of registering a report and a plugin that list tables without primary keys, and shows how to invoke them via \show, \watch or direct function calls.
This article details a lightweight transactional message solution for microservices that stores pending messages in a local MySQL table, defers RabbitMQ publishing until after transaction commit, and includes compensation logic with exponential back‑off to ensure reliable asynchronous communication.
This article compiles essential DevOps interview questions covering version control differences between Git and SVN, MySQL master‑slave replication mechanics, Kafka versus traditional MQ, Kubernetes service types, pod communication, health checks, resource limits, link types, and permanent mounting techniques.
This article examines the purpose, benefits, and drawbacks of MySQL foreign keys, presents various usage scenarios with performance tests, discusses constraints, and offers guidance on when to apply or avoid foreign keys in database design.
This article explains MySQL performance bottlenecks, compares IO and CPU limits, introduces horizontal and vertical sharding and partitioning concepts, presents practical scenarios, outlines common tools such as Sharding‑Sphere, TDDL and Mycat, and provides step‑by‑step guidance for implementation, troubleshooting and scaling.
This guide walks through preparing the environment, installing required tools, configuring firewalls, setting up host files, deploying Nginx with Keepalived for high‑availability, installing Docker, MySQL, and finally installing K3s and Rancher using Helm, including certificate creation and troubleshooting steps.
This article introduces many lesser‑known MySQL client shortcuts—such as \G, \R, \T and others—explaining their syntax, usage examples, and how they can streamline daily database operations for developers and administrators.
The article explains how iQIYI’s order system leverages MySQL binlog and redo log with a two‑phase commit to drive event‑driven processing, reveals hidden consistency gaps where binlog events can precede database state, and proposes retry or direct binlog consumption strategies to ensure reliable order fulfillment.
This article introduces the Percona Toolkit utility pt‑stalk, explains its OS‑level and MySQL‑level monitoring capabilities, details configuration parameters and usage scenarios, shows example command lines, describes the generated diagnostic output, and briefly covers the companion pt‑sift tool for analysis.
This article investigates the Alibaba Java Development Manual's recommendation to avoid joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, index impact, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article explains the concepts, advantages, and drawbacks of MySQL pessimistic and optimistic locks, shows how to implement each with SQL and PHP code, compares their suitability for different traffic patterns, and demonstrates a Redis‑based optimistic lock for high‑concurrency flash‑sale scenarios.
This article explains the importance of database backup and details MySQL Enterprise Backup (MEB) features—Backup Lock, Redo Log Archiving, and Page Tracking—along with usage commands, performance considerations, and comparisons to xtrabackup for MySQL 8.0 environments.
MySQL achieves crash‑safe durability by employing a write‑ahead log architecture that records changes in redo, undo, and binary logs, coordinates them through two‑phase and group commit to maintain order, and uses a structured recovery process to replay or roll back transactions after any failure.
The article explains MySQL 8.0.20's binlog transaction compression feature using the ZSTD algorithm, describes its internal implementation, outlines usage steps, presents performance test results, and lists important considerations for deploying compression in production environments.
This article explains database concurrency problems, describes how pessimistic and optimistic locking work, provides practical MySQL examples with transaction control, and compares their suitability for different read‑write scenarios to help developers prevent dirty data and improve throughput.
This article explains the MySQL group_replication_consistency parameter, describes its five possible values, demonstrates the three most common consistency modes (EVENTUAL, BEFORE, AFTER) with practical multi‑node examples, and discusses their advantages, drawbacks, and impact on query latency and data integrity.
This article explains why MySQL master‑slave replication can suffer from latency, examines the root causes on both master and slave sides, and provides practical architectural, hardware, and configuration solutions—including semi‑sync and parallel replication—to improve data consistency and performance.
This article analyzes a MySQL master‑slave instance with a massive arrival_record table, identifies slow‑query causes using pt‑query‑digest and tcpdump, proposes index redesign, demonstrates backup/restore with mydumper, compares online DDL and pt‑osc, and presents batch‑delete strategies that dramatically reduce SLA‑triggering latency.
This guide explains how to achieve MySQL high availability and scalability through full and incremental backups, binlog-based point‑in‑time recovery, and both asynchronous and synchronous replication, while highlighting trade‑offs, best practices, and practical command examples.
This article details a comprehensive experiment that evaluates MySQL's ability to sustain up to 100,000 simultaneous connections using Percona Server's thread‑pool plugin, sysbench clients, adjusted OS/network limits, and multiple IP addresses, presenting hardware specs, configuration steps, observed errors, performance metrics, and practical recommendations.
This week’s community newsletter highlights the DB‑Engines May 2024 ranking where MySQL shows the highest growth, shares links to MySQL troubleshooting and performance articles, reports DBLE and TXLE development progress, bug fixes, community Q&A, and outlines upcoming plans for the open‑source middleware projects.
This article explains how to monitor and estimate the progress of a massive MySQL UPDATE by observing rows_examined in performance_schema, deriving a progress formula, and adjusting the coefficient based on whether primary keys or only data columns are modified.
The article analyzes a MySQL 5.7 upgrade that broke an application due to SSL being enabled by default in version 5.7.28, explains how the JDBC useSSL=true setting triggered handshake errors, and provides several practical solutions such as disabling SSL, adjusting auto‑generation settings, and updating the connection string.
This guide explains MySQL’s fundamentals, describes a master‑slave high‑availability architecture, and provides a complete step‑by‑step tutorial for deploying the cluster on Kubernetes using ConfigMaps, Services, a StatefulSet, dynamic PVCs, and verification commands.
This article examines recent database deletion incidents and offers DBA‑level guidance on strengthening processes, technical safeguards, and security policies, covering backup‑restore design, performance optimization, high‑availability strategies, and self‑service automation to avoid future data loss.
This guide walks through deploying mysqld_exporter, configuring Prometheus and Grafana, and monitoring essential MySQL metrics such as replication health, query throughput, slow‑query counts, connection usage, and InnoDB buffer‑pool statistics, while also showing how to set up alert rules for proactive database operations.
This article shares practical MySQL performance optimization techniques, covering table size limits, connection settings, data type choices, index design, query rewriting, pagination, and batch processing, with code examples and actionable guidelines to improve query speed and scalability.
This case study details the migration of Weimeng's MySQL databases to Tencent Cloud CDB, describing the testing methodology, performance bottlenecks discovered (NUMA, network parameters, low‑concurrency issues, and version bugs), the step‑by‑step optimizations applied, and the resulting QPS improvements across various workloads.
This article examines why NOT EXISTS can perform poorly in MySQL, demonstrates how rewriting the query with a LEFT JOIN dramatically reduces execution time, and explains the underlying indexing and profiling details that affect query efficiency.
An engineer’s mishap with a massive ‘INSERT INTO … SELECT’ migration exposed how full‑table scans lock MySQL tables, causing payment failures, and shows that adding an index on the filter column prevents the lock and ensures safe, efficient data transfer.
This article explains which components consume CPU on a MySQL server, analyzes the impact of user‑space processing and IO wait, and provides practical strategies—such as optimizing indexes, reducing unnecessary calculations, and improving IO handling—to lower CPU usage and improve overall database performance.
This article explores multiple backend strategies for implementing a location‑based “nearby users” service, covering pure MySQL queries, MySQL combined with GeoHash, Redis Geo commands, and MongoDB geospatial indexes, explaining design principles, pros and cons, and providing complete code examples for each approach.
This guide presents comprehensive MySQL best‑practice standards covering object naming, basic schema design, field definitions, index design, common index column recommendations, covering indexes, SET usage, SQL development rules, and safe operational behaviors to improve performance, maintainability, and security.
This article recounts a February 2020 MySQL data‑loss incident, detailing how the author identified the offending binlog, used open‑source tools like binlog2sql and MyFlash to reconstruct and restore the missing records, and reflects on operational lessons learned.
This article explains MySQL's core architecture, lock and transaction mechanisms, storage engines, and provides practical creation‑time and query‑time optimization techniques such as proper data types, index selection, query rewriting, and performance analysis using EXPLAIN, supplemented with concrete SQL examples.
This report details the background, testing methodology, encountered issues, optimization steps, migration process, and final performance outcomes of moving Weimeng's MySQL databases to Tencent Cloud CDB, providing a comprehensive case study for database engineers and cloud architects.
The article explains why MySQL's AUTO_INCREMENT primary keys can become non‑monotonic and non‑continuous due to early‑version in‑memory counters, restart behavior, concurrency lock modes, and how MySQL 8.0 persists the counter to mitigate these issues while discussing design trade‑offs and best practices.
This tutorial walks you through installing MySQL 5.7 on two CentOS 7.6 machines, configuring the master and slave my.cnf files, setting up replication users, starting the services, and verifying synchronization with detailed commands and troubleshooting tips.
This article introduces Percona QAN, explains its three‑component architecture (QAN‑Agent, QAN‑API, QAN‑APP), describes the MySQL slow‑log format and key fields, and details the web UI metrics such as Load, Count, Latency, and additional diagnostic information for database performance analysis.
This guide compiles essential MySQL security steps, master‑slave replication principles, backup scripts, Linux boot overview, common port services, virus mitigation, monitoring tools, nginx optimization, InnoDB lock troubleshooting, replication lag reduction, high‑availability components, data migration utilities, and automation configuration management techniques for operations engineers.
This article explains how to build a MySQL‑based flash‑sale system that guarantees high performance and strong consistency, discusses common pitfalls such as overselling and duplicate purchases, and presents progressive solutions from simple unique indexes to transaction‑based locking and performance‑oriented designs that may sacrifice consistency when necessary.
This comprehensive guide outlines MySQL database naming conventions, basic design rules, field specifications, index design principles, common index recommendations, and best practices for SQL development and operational behavior to ensure performance, maintainability, and security.
This comprehensive MySQL FAQ covers its definition, implementation language, key features, differences from SQL, storage engines, data types, triggers, security tips, performance considerations, and practical queries such as retrieving the N‑th highest salary, providing a solid reference for developers and DBAs.
This article provides concise answers to common MySQL questions covering default ports, regular expression usage, index limits, query examples for retrieving rows, date functions, savepoints, ENUM types, storage engines, connection functions, ACLs, and other essential database concepts.
The article explains how to approximate the progress of mysqlbinlog when parsing large MySQL binary logs by observing the file handle's read offset, since the tool lacks a built‑in progress indicator.
The article explains why using LIMIT with a large offset in MySQL causes severe performance degradation, demonstrates the problem with real data, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time by avoiding massive random I/O and buffer‑pool pollution.
This article explains MySQL's logical architecture, query processing steps, and the underlying principles of query optimization, covering topics such as client‑server protocol, query cache, parsing, optimizer cost model, index structures, and practical performance‑tuning strategies for efficient database operations.
A developer used INSERT‑INTO‑SELECT to migrate millions of rows, unintentionally locking the source table and causing massive payment failures, but adding an index and forcing its use resolved the issue and restored safe migration.
This article explains Ctrip's high‑availability designs for SQL Server, MySQL, and Redis, describing multi‑replica strategies, evolution from legacy mirroring to AlwaysOn, multi‑node MHA management, and Redis sentinel‑based failover, while emphasizing fault‑tolerance across data centers.
This tutorial walks through creating a PyQt5 desktop program in Python 3.7 that connects to a MySQL 5 database, displays table contents, enables editing, custom right‑click menus, and scroll‑position control, providing a complete example for beginners and intermediate developers.
The article examines why MySQL queries become slow, detailing system‑level issues such as network latency, I/O bottlenecks, and CPU settings, alongside database‑level problems like missing indexes, type conversion, poor execution plans, lock contention, skewed data, and inefficient SQL patterns, and offers optimization strategies.
This article examines MySQL column attributes—especially NULL vs NOT NULL handling, the impact of NULL on concatenation, COUNT and indexing, as well as AUTO_INCREMENT behaviors, lock modes, step/offset settings, and overflow scenarios—to help developers write more efficient and reliable SQL.
This article presents a series of hands‑on big‑data exercises, including Spark Scala data analysis, Kafka topic creation and custom partitioning, and MySQL table design with Scala‑based streaming calculations, providing complete source code and step‑by‑step solutions for each task.
MySQL 8.0.20 introduces a range of enhancements including new SHOW_ROUTINE privilege, hash join improvements, index‑level optimizer hints, performance schema refinements, SQL syntax changes, InnoDB doublewrite buffer relocation, binary log transaction compression, X Plugin and protocol upgrades, and additional replication options.
A production MySQL 8.0.15 MGR cluster suffered from massive internal temporary‑table file leaks due to a known bug, which was diagnosed through log analysis and Oracle documentation and resolved by upgrading to MySQL 8.0.16, confirming the issue is version‑specific and not MGR‑related.
This article details the DRS team's step‑by‑step investigation and optimization of a massive MySQL migration involving 860,000 tables, addressing timeout, OOM, and performance bottlenecks to reduce total migration time from over 19 hours to under 10 hours.
This article investigates why moving MySQL’s LIMIT clause outside a subquery can cause the ORDER BY sorting to be ignored in a LEFT JOIN, analyzes the optimizer’s behavior, and recommends placing both ORDER BY and LIMIT at the outermost query level for correct results.
This article compiles a comprehensive set of PHP interview questions covering fundamentals, session and cookie handling, database transactions, MySQL optimization, version control tools, string manipulation, HTTP status codes, MVC concepts, and practical coding tasks with detailed answers and code examples.
This article examines why MySQL instances are often targeted by the Linux OOM killer, explains proper InnoDB buffer pool sizing, introduces memory leak concepts, and demonstrates how to use Valgrind's Memcheck tool to detect leaks and improve database memory management.
This article explains the differences between MySQL table locks and row locks, how they are implemented by the server and storage engines, the lock modes and types (including intention, auto‑increment, gap, next‑key, and insert‑intention locks), and provides practical SQL examples with diagrams.
This article explains how MySQL 8.0 Resource Groups enable fine‑grained CPU binding and thread‑priority control, provides sample SQL procedures and commands, details Linux environment setup, discusses implementation internals, outlines known limitations especially in containerized environments, and suggests practical usage scenarios.
This article explains how to design a high‑performance, strongly consistent flash‑sale (秒杀) system using MySQL InnoDB, covering table schema, unique indexes, transaction handling, row‑level locking, and trade‑offs between consistency and performance, with concrete SQL examples and flow diagrams.
This guide demonstrates how to reproduce a MySQL crash, enable system‑level core dumps, adjust user limits, configure MySQL to generate core files, and use gdb to extract useful debugging information for complex failure analysis.
This article provides a comprehensive overview of MySQL InnoDB locking, covering shared/exclusive locks, intention locks, record locks, gap locks, next‑key locks, insert‑intention locks, and auto‑increment locks, with examples, compatibility tables, and practical SQL snippets to illustrate each mechanism.
This article presents a cloud‑native solution for MySQL containerization on Kubernetes using CEPH RBD as backend storage, detailing the background, feasibility analysis with performance benchmarks, the operator‑based architecture, and the benefits for development‑test environments.
The author discovers a puzzling MySQL bug where moving the LIMIT clause outside a nested SELECT causes the final result set to be ordered incorrectly, explains how ORDER BY inside subqueries is ignored during LEFT JOIN processing, and recommends placing ORDER BY and LIMIT at the outermost query level.
An engineer’s attempt to migrate millions of orders using INSERT INTO SELECT caused massive table locking, payment failures, and system alerts, but by analyzing the locking behavior and adding an index on the timestamp column, the migration succeeded efficiently, illustrating the importance of proper indexing for large‑scale data moves.
A production database migration from MariaDB 10.4 to MySQL 5.7 caused a previously fast SQL query to become extremely slow due to differing column collations that prevented index usage, and the article explains how to diagnose the issue and resolve it by aligning collations or using explicit conversion.
This article explains how to use ShardingSphere‑JDBC to achieve horizontal database sharding and read/write splitting in a Spring Boot application, covering ShardingSphere concepts, configuration, entity and DAO code, custom sharding algorithm, and practical testing with multiple MySQL instances.
This article explains the programming mindset of splitting and merging, details MySQL partition implementation and internal file types, demonstrates how partitioning improves query performance, and explores sharding strategies, middleware choices, and common issues like distributed transactions and cross‑shard joins.
This article explains the causes of MySQL InnoDB row lock wait timeout errors, distinguishes row and metadata lock waits, and provides practical methods—including manual replication, monitoring scripts, general_log analysis, and Performance Schema queries—to locate, diagnose, and resolve such locking issues.
This article explains the concept of MySQL Undo Log, traces its evolution from version 5.5 to 8.0, and provides step‑by‑step instructions with examples for creating, viewing, configuring, deleting, and moving undo tablespaces, highlighting the new parameters and defaults introduced in MySQL 8.0.
This article explains why using indexes does not guarantee fast queries, explores the relationship between index usage and slow‑query logs, and presents practical MySQL optimization techniques such as improving index selectivity, reducing table‑lookup costs, and leveraging virtual columns.
