This article breaks down the reasoning behind MySQL’s commonly cited 20 million‑row limit for a single table by analyzing page size, B+‑tree structure, storage overhead, and how leaf and index pages combine to estimate maximum row counts.
This article explains how InnoDB resolves a deadlock by ordering the involved transactions, selecting a victim based on priority, rollback cost and table modifications, updating transaction weights, optionally logging the deadlock details, and finally waking the victim transaction to roll back.
This article examines whether a MySQL replica can safely omit setting sync_binlog=1, explains the underlying InnoDB and GTID mechanisms, presents an experiment reproducing power‑loss scenarios, and analyzes how different master_auto_position settings affect data consistency and GTID continuity during recovery.
This technical report analyzes a high‑traffic MySQL deployment with three servers, detailing disk usage, table space, QPS and slow‑SQL issues, and presents a comprehensive governance plan that includes data migration, read‑traffic offloading to replicas, MyBatis interceptor implementation, JSF monitoring, and Python automation to reduce disk pressure and improve query performance.
This article provides a comprehensive guide to MySQL indexing, covering index data structures, maintenance mechanisms, query execution, optimization techniques such as left‑most prefix, index push‑down, unique indexes, index selection, and common pitfalls that cause index inefficiency.
This guide shows how to create a Bash script that backs up a specific MySQL database using mysqldump, compresses the dump, automatically deletes old backups, explains the find -mtime option, and schedules the process with cron, plus detailed steps for restoring the data.
This guide walks through the complete process of creating a MySQL dual‑master replication cluster, configuring replication users, synchronizing binary logs, setting up keepalived for virtual IP failover, and testing both data consistency and high‑availability monitoring.
Recent investigations reveal that MySQL versions 8.038, 8.4.1, and 9.0 contain severe bugs causing system crashes when handling over 10,000 tables, with additional performance bottlenecks in InnoDB startup, prompting experts to advise cautious, delayed upgrades and careful code review of related functions.
This article explores why MySQL queries can become sluggish beyond just missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection limits, buffer pool sizing, and practical configuration tips to dramatically improve performance.
This article provides a comprehensive guide to MySQL's DQL, DML, and DDL sections, explaining execution order, core clauses, and practical code examples for creating, altering, and managing databases and tables.
This article explains what constitutes a slow SQL query, how to detect and analyze it using MySQL tools, and provides practical optimization steps—including indexing, business logic review, caching, scheduling, and partitioning—to dramatically reduce execution time.
The article analyzes the challenges of keeping MySQL and Redis data consistent under high concurrency, compares four write‑order strategies, explains why they can cause stale cache data, and presents recommended solutions such as Cache‑Aside, distributed locks, message queues, and Canal‑based binlog syncing.
This guide compares four MySQL‑to‑Elasticsearch synchronization methods—synchronous dual‑write, asynchronous MQ‑based dual‑write, timer‑driven SQL extraction, and real‑time Binlog replication—and reviews popular CDC tools such as Canal, Alibaba Cloud DTS, Databus, and others to help you choose the right solution.
Through a series of MySQL 8.0 experiments, the article demonstrates that InnoDB inserts run concurrently, explains why phantom reads can appear, and details how MVCC, insert‑intention locks, and the ReadView mechanism together prevent phantom read anomalies.
This article explains how to connect to a MySQL database with PHP, execute a SELECT query, and use the mysqli_fetch_assoc function to fetch rows as associative arrays, including code examples and tips for handling multiple rows.
This article explains how to use PHP functions to create a user registration system with input validation, password hashing, and MySQL insertion, and also demonstrates a generic data‑storage function that connects to a database and saves arbitrary data, highlighting key security and implementation steps.
Learn eight practical MySQL optimization strategies—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries with JOINs, handling mixed sorting, eliminating costly EXISTS clauses, pushing conditions, pre‑filtering data, and leveraging intermediate result sets—to dramatically reduce query execution time from seconds to milliseconds.
A fatal bug (ID #36808732) in MySQL 8.0.38, 8.4.1 and 9.0.0 causes the server to crash on restart when thousands of tables exist, prompting Percona to warn users not to upgrade until patched versions 8.0.39, 8.4.2 and 9.0.1 are released.
MySQL’s TINYINT(1) stores a full byte integer allowing any value from 0‑255 (or –128‑127 signed) and works with arithmetic, offering broad compatibility, while BIT(1) stores a single bit limited to 0 or 1, is MySQL‑specific, and is best used only when true/false storage and minimal space are essential, making TINYINT(1) the generally preferred choice unless strict boolean constraints apply.
This article provides 10 detailed Python code examples demonstrating how to connect to, query, insert, update, delete, and manage MySQL databases with proper error handling and resource management.
This article examines why MySQL pagination using large LIMIT OFFSET values becomes increasingly slow, explains the underlying scanning behavior, and presents six practical optimization techniques—including indexed subqueries, cursor‑based pagination, column selection, partitioning, caching, and hardware considerations—to dramatically improve query performance on massive tables.
This article explains how MySQL's InnoDB deadlock detection thread works, when it checks for deadlocks based on the innodb_deadlock_detect variable, how it discovers lock‑wait cycles, performs a secondary verification using snapshot and slot information, and finally summarizes the overall process.
The article analyzes a MySQL replication error where a MEMORY table is cleared after a master restart, explains why the binlog records only a statement despite row format, and provides step‑by‑step troubleshooting and remediation measures to ensure data consistency.
This article evaluates MySQL backup strategies using Percona XtraBackup, comparing local, remote, and streaming modes in terms of backup duration, primary server load impact, and backup file size, and provides practical recommendations based on detailed test results.
This article examines the detailed steps and timing of establishing a MySQL database connection using Java, measures the latency of the TCP handshake and authentication phases, and demonstrates why connection pooling is essential for high‑traffic web applications.
This article explains MySQL’s composite index leftmost‑prefix rule, demonstrates how the Skip Scan Range Access Method introduced in MySQL 8.0.13 can use indexes without following the rule, provides step‑by‑step examples, code snippets, and discusses its implementation, benefits, and limitations.
Both TINYINT(1) and BIT(1) are common MySQL data types for storing Boolean values, but they differ in storage size, range, syntax, compatibility, and ideal use cases, with TINYINT offering broader integer support and portability, while BIT provides true single‑bit storage for strict Boolean fields.
To avoid common MySQL pitfalls, the article compiles practical SQL writing guidelines—selecting specific columns instead of *, using LIMIT 1, avoiding OR in WHERE, optimizing pagination, handling LIKE patterns, minimizing redundant indexes, preferring INNER JOIN, and leveraging covering indexes and EXPLAIN for efficient query execution.
The article details a real‑world backend engineering project where a loan repayment service is refactored to use table sharding with ShardingSphere, covering design decisions, table naming, sharding algorithms, historical data migration, dynamic switches, transaction management, code examples, and practical pitfalls.
This article provides an in-depth overview of common backend interview topics, covering MySQL index structures and storage engines, Redis single‑threaded design and persistence mechanisms, Java transaction ACID properties, SpringBoot auto‑configuration principles, JVM memory regions, class loading, and garbage‑collection algorithms, all illustrated with code examples.
This article investigates the time consumption of creating and closing a MySQL connection from a Java application, measuring each network and protocol step with Wireshark and code examples, and demonstrates why connection pooling is essential for high‑traffic web services.
The article examines Oracle's release of MySQL 9.0, highlighting developer disappointment, serious bugs warned by Percona, the company's shift toward the proprietary HeatWave analytics engine, and the broader implications for the open‑source MySQL community.
The article describes how a one‑year‑old loan/repayment service was refactored by introducing a 50‑table sharding scheme with ShardingSphere, detailing the design decisions, historical data migration, code changes, configuration files, pitfalls, and runtime synchronization strategies to achieve efficient query performance on billions of rows.
This article presents two practical database migration schemes—dual‑write and a gray‑scale switch—detailing their workflows, code refactoring, data synchronization using Syncer, consistency verification with sync‑diff‑inspector, and operational trade‑offs for minimizing downtime and ensuring data integrity.
Explore nine major database technologies—from traditional relational MySQL to NoSQL Redis, columnar HBase and ClickHouse, document-oriented MongoDB, search engine Elasticsearch, graph Neo4j, time‑series Prometheus, and vector Milvus—plus practical best‑practice guides, real‑world polyglot persistence scenarios, and recommended resources for mastering modern data storage.
This article provides an in‑depth overview of nine major database categories—including relational, key‑value, columnar, document, graph, time‑series, and vector databases—detailing their strengths, weaknesses, best practices, and typical application scenarios, and explains how polyglot persistence combines multiple databases for optimal performance and scalability.
This article outlines a business approval workflow where a commercial user adds a company and an administrator reviews it, and details the backend implementation using Java, IKAnalyzer for tokenization, MySQL regex and full‑text search for fuzzy matching, and code examples.
This article explains how InnoDB simulates deadlocks, how the ib_srv_lock_to background thread checks for deadlocks, how it builds lock‑wait snapshots, constructs lock‑wait graphs, calculates transaction weights, and finally resolves deadlocks, illustrated with detailed SQL examples and code snippets.
Upgrading to MySQL 8.0.38 (or later) causes crashes when an instance contains more than ten thousand tables, even with validate_tablespace_paths disabled, but using a shared or general tablespace configuration can reliably avoid the failure.
This article analyzes the MySQL 1709/1071 index‑column‑size‑too‑large error that appears after upgrading from 5.6 to 8.0.21, explains its relation to compact/redundant row formats, references the underlying bug, and provides practical steps and SQL queries to detect and fix affected tables before or after the upgrade.
This article analyzes why two identical DELETE statements on a MySQL table with a unique index can deadlock, explains InnoDB lock types, walks through the execution flow, reproduces the issue with SQL and Java code, and discusses mitigation strategies.
This article demonstrates how to insert 300,000 rows into a MySQL table using MyBatis and JDBC, compares direct batch insertion, row‑by‑row insertion, and optimized batch strategies, and provides performance measurements and practical tips for efficient large‑scale data loading.
This article investigates why a MySQL replication slave stops when a GRANT statement fails after directly updating the mysql.user table, explains the implicit privilege reload mechanism, demonstrates the issue with a reproducible test, and concludes that GRANT is not an atomic operation.
This article investigates whether MySQL VARCHAR(50) and VARCHAR(500) occupy identical storage and how column length impacts query performance, providing step‑by‑step table creation, bulk data insertion, storage queries, and detailed benchmark results with analysis of the underlying causes.
This article presents Alibaba's concise 10‑point MySQL schema standard, covering boolean field naming, lowercase identifiers, singular table names, reserved word avoidance, index naming conventions, decimal usage, fixed‑length char, variable‑length varchar/text, mandatory audit fields, and the prohibition of physical deletions, offering practical guidance for clean database design.
MySQL 9.0, released July 2 2024, introduces EXPLAIN JSON output stored in variables, native EVENT DDL statements, and two new performance‑schema tables for variable metadata, while deprecating the old variables_info columns and removing the mysql_native_password plugin in favor of caching_sha2_password, enhancing observability and security.
This article demonstrates how to implement a high‑performance file upload system using Vue's Element Plus component on the frontend and Spring Boot on the backend, comparing single‑row inserts, batch inserts, and a multithreaded producer‑consumer approach to dramatically reduce MySQL insertion time.
This article presents 15 classic MySQL log interview questions and provides detailed explanations of redo log, WAL, binlog, undo log, two‑phase commit, log flushing mechanisms, and recovery strategies, helping readers deepen their database knowledge and ace technical interviews.
This article explains why slow queries occur in high‑traffic applications, outlines effective index‑optimization steps, and demonstrates how to use MySQL's EXPLAIN command to analyze execution plans, interpret each column, and improve database performance.
This article introduces a collection of practical open‑source utilities—including SQLTXPLAIN, SOAR, SQLAdvisor, and Percona Toolkit tools—that streamline common MySQL DBA tasks such as schema changes, replication checks, slow‑query analysis, and data consistency verification, helping engineers work faster and more efficiently.
This article explains MySQL's hash join algorithm, compares it with nested‑loop joins, shows how to read execution steps and EXPLAIN output, discusses extended hash‑join scenarios, and weighs the trade‑offs of performing multi‑table joins inside the database versus in application code.
This article explains the inner workings of InnoDB’s lock‑wait timeout thread, detailing how it scans waiting slots, identifies timed‑out transactions, processes timeout logic, and notifies the affected transactions, while also covering related data structures and configuration variables.
This article explains how the LIMIT clause can cause massive I/O and CPU overhead in MySQL queries, analyzes the underlying B+‑tree index structure and execution plans, and presents practical optimization techniques such as covering indexes, sub‑queries, and partitioning.
This article explains how MySQL Router 8.2 enables read/write splitting for an InnoDB ReplicaSet, showing configuration steps, routing behavior, sample code, and the resulting performance and management benefits without requiring application changes.
The article describes a MySQL‑based fuzzy matching solution for preventing duplicate company entries in a business‑approval workflow, detailing preprocessing of Chinese company names, word segmentation with IKAnalyzer, RegExp pattern generation, and a custom SQL query that ranks results by keyword‑match ratio without using external search engines.
This article explains how MySQL GROUP BY queries can be dramatically accelerated by using appropriate index scans, compares Loose Index Scan and Tight Index Scan, shows practical test data, execution plans, and provides guidelines for when each method is most effective.
Oracle’s July release introduces MySQL 9.0 Innovation alongside updates to 8.0 and the LTS 8.4 series, bringing new Vector data types, JavaScript stored procedures, upgraded libraries, event scheduling enhancements, and deprecations such as SHA‑1, while outlining future release cadence and upgrade considerations.
This guide presents a thorough MySQL performance checklist, covering efficient SQL writing, data‑type choices, selective column retrieval, smart joins, batch inserts, and query‑cache settings; it details key InnoDB and server variable tuning, proper index creation and usage, common pitfalls, and how to analyze and improve queries with EXPLAIN.
This article investigates MySQL's recommendation against using UUID or non‑sequential random IDs as primary keys by benchmarking auto‑increment, UUID, and Snowflake‑derived keys, analyzing their insertion speed, index behavior, and discussing the advantages and drawbacks of each approach.
Percona’s analysis of MySQL version adoption reveals a slow migration to 8.x, prompting extensive performance tests that compare default and tuned configurations of MySQL 5.7, 8.0.36, 8.4, Percona Server, and MariaDB using sysbench and TPC‑C benchmarks, with detailed results showing consistent performance regressions in newer releases.
This article uses a realistic interview scenario to explore why multi-table joins in MySQL are not inherently slow, detailing four join algorithms—simple nested-loop, index nested-loop, block nested-loop, and batched key access—along with their pseudo‑code, execution steps, performance trade‑offs, and the impact of features like MRR and recent optimizer changes.
This article explains how two identical DELETE statements on a table with a unique index can cause a deadlock in MySQL 5.7, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies and behavior changes in MySQL 8.
This article analyzes a MySQL query that originally took nine seconds, identifies redundant joins and an EXISTS subquery on the same table, and demonstrates how restructuring the query eliminates duplicate scans, reducing execution time to 0.2 seconds.
The Vivo Internet Storage R&D team's article describes an end‑to‑end MySQL data‑consistency verification tool for DTS that uses fixed‑size chunking and CRC32/MD5 fingerprint aggregation to quickly compare source and target tables, pinpoint mismatched rows, and enable automated or manual correction while minimizing impact on replication.
This article examines why identical DELETE statements on a MySQL table with a unique index can cause deadlocks, reviews InnoDB lock types, reproduces the issue with SQL and Java code, and discusses mitigation strategies such as version upgrades, isolation level changes, and distributed locks.
A client complained about a 35‑second MySQL query that ignored indexes; by analyzing column selectivity and rewriting the condition to use the auto‑increment primary key instead of a secondary index, the execution time dropped to 2.55 seconds, illustrating why primary‑key access is often faster.
This guide explains how to set up automated MySQL database backups using a shell script that runs mysqldump (optionally compressed), stores the files on a local disk, makes the script executable, and schedules it with a cron job after verifying disk space and cron availability.
The article explains MySQL’s new release model, detailing the shift to a two‑year LTS cycle, the upcoming 9.0 innovation series with vector data types and security improvements, library updates, client tools, and the scheduled October releases of 8.4.2, 8.0.39, and 9.1.0.
This article explains how InnoDB handles lock waiting by allocating lock structures, registering waiting slots, recording start times, and notifying transactions, outlining each step of the queue‑registration‑notification workflow in MySQL 8.0.32.
MySQL 9.0 Innovation Edition, released on July 1, 2024, introduces major changes such as the removal of the mysql_native_password plugin, new PREPARE support for event statements, expanded Performance Schema tables, JSON‑formatted EXPLAIN ANALYZE output, Windows installer fixes, and several MySQL Shell enhancements.
When upgrading MySQL 8.0 to 8.4 on a host running multiple instances, some instances fail to start with a series of InnoDB errors caused by io_setup() returning EAGAIN, which can be resolved by increasing the system's aio‑max‑nr limit and adjusting InnoDB I/O thread settings.
An in‑depth guide explains MySQL’s write‑ahead log mechanism, covering buffer pool, redo and undo logs, checkpoint types, and how the system recovers from power failures, with step‑by‑step examples and practical configuration tips for reliable data consistency.
This guide demonstrates how to install PyMySQL or mysql‑connector‑python, connect to a MySQL database from Python, execute LIKE‑based fuzzy queries, handle results, and safely close resources, with detailed code examples and practical tips.
The article analyzes why deploying MySQL in Docker containers can lead to data‑security, performance, state‑management and resource‑isolation problems, while also outlining scenarios where containerizing MySQL might be feasible and offering practical mitigation strategies.
This guide explains why MySQL blocks a host after many failed connections, outlines common causes such as wrong credentials, network issues, and configuration errors, and provides step‑by‑step solutions including log inspection, credential verification, network checks, application fixes, and commands to unblock the host while also offering preventive measures.
This article explains the slow‑path row‑locking algorithm used by InnoDB in MySQL 8.0.32, detailing how the engine checks whether a transaction already holds a lock, determines if it must wait, reuses existing lock structures, and allocates new ones when necessary.
This article details the end‑to‑end design, configuration, and code implementation of a 50‑table sharding solution for cash repayment and loan application data, covering database schema planning, historical data migration, backend query adaptation, dynamic switches, scheduled consistency checks, and practical pitfalls encountered with Spring Boot 3 and ShardingSphere.
This article investigates why BenchmarkSQL stalls during MySQL performance testing, tracing the issue to REPEATABLE‑READ isolation causing a dead‑loop in delete‑select transactions, and demonstrates how switching to READ‑COMMITTED resolves the problem through detailed code inspection and experimental verification.
This article explains why upgrading MySQL from 5.7 to 8.0 can cause an "Illegal mix of collations" error in FIND_IN_SET queries, analyzes the root cause, and presents three practical solutions—adjusting server parameters, altering table collations, or rewriting the SQL—to restore correct operation.
This article investigates whether defining a VARCHAR column with a larger length affects MySQL storage space and query performance by creating two tables with VARCHAR(50) and VARCHAR(500), inserting one million rows, and measuring storage usage and execution times for various queries.
This article explores various MySQL write performance tests using different concurrency levels—Statement, Connection, and database-level parallelism—provides benchmark results, sample Java/Groovy code, and discusses key configuration and architectural factors that influence write throughput.
The article measures the exact time cost of establishing and closing a MySQL connection from Java, breaks down each network and protocol step with Wireshark captures, and shows how these latencies add up to hundreds of milliseconds, making connection pooling essential for high‑traffic web services.
While MySQL INT and BIGINT auto‑increment columns usually suffice, this article explains their numeric limits, calculates how many records each can store over time (e.g., at 100 inserts per second), and provides practical steps to switch from INT to BIGINT safely.
This tutorial explains how to connect to a MySQL database using PHP's mysqli extension, execute queries with mysqli_query, and retrieve each row with mysqli_fetch_assoc, providing complete code examples and a full script for efficient database handling.
To maintain data consistency between Redis caches and MySQL databases, this article examines common pitfalls and presents three robust solutions—deleting cache before DB writes, updating the DB then removing cache, and implementing delete‑retry mechanisms—plus optional locking for strong consistency.
This article explains how InnoDB implements row locking by dividing the process into fast and slow paths, describing the hash‑based lock table, token acquisition, lock‑structure creation, bitmap handling, and the conditions that trigger slow‑lock logic.
This article explains why replacing MyBatis with MyBatis‑Plus caused a LocalDateTime conversion exception, identifies the root cause as an outdated mysql‑connector‑java version, and shows how upgrading the connector (to 5.1.37 or later) resolves the issue while providing code examples and debugging steps.
This article presents a comprehensive case study of a high‑traffic business middle‑platform built with SpringBoot microservices, Kong API Gateway, MyCat‑sharded MySQL, Docker containerization, Kubernetes orchestration, CI/CD pipelines, and monitoring tools, detailing design decisions, implementation tricks, and operational lessons learned.
This article presents a comprehensive design and implementation for achieving MySQL high availability by replacing the single‑master architecture with Orchestrator‑driven automatic failover, integrating DBProxy for transparent routing, and addressing topology changes and data compensation to ensure continuous, reliable service.
This article explains why MySQL columns defined as INT(10) appear as INT after upgrading from 5.7 to 8.0, demonstrates the change with reproducible examples, and discusses the storage limits, proper selection, and display‑width behavior of integer types including ZEROFILL.
This article examines the practical limits of MySQL single‑table row counts, explains how InnoDB stores data in 16 KB pages, analyzes B+‑tree index structures, and provides formulas and experiments that justify the commonly cited 20 million‑row guideline.
This guide introduces a collection of practical MySQL DBA utilities—including SQLTXPLAIN, SOAR, SQLAdvisor, and Percona Toolkit commands such as pt‑duplicate-key-checker, pt‑kill, pt‑mysql-summary, pt‑query-digest, pt‑table-checksum, and pt‑table-sync—showing how to install them, run key commands, and interpret their outputs to streamline database administration tasks.
Facing MySQL 5.7’s February 2024 end‑of‑life, the team devised a zero‑impact blue‑green migration on AWS RDS to MySQL 8.0.35, using util.checkForServerUpgrade() compatibility checks, pt‑upgrade SQL validation, and a rapid 2‑minute switchover that completed within the planned low‑traffic window.
This article walks through a practical experiment where the AI‑driven assistant ChatDBA diagnoses and resolves a MySQL “Too many open files” error by collecting system metrics, analyzing slow queries, identifying index misuse, and proposing concrete configuration and query adjustments.
This article presents a comprehensive guide to designing and implementing a Java‑based MySQL large‑table migration tool using microservice architecture, multiple design patterns, multithreading, custom logging, and flexible migration modes, complete with code snippets, repository links, and deployment considerations.
This guide details how to use mysqldump and related commands to back up single or multiple MySQL databases, specific tables, compress backups, include drop statements, export only schema, and automate the process with scripts and cron jobs, plus instructions for restoring data.
This article explains common scenarios that cause MySQL or Java processes to consume 900% CPU, walks through step‑by‑step diagnosis using Linux tools, and provides concrete optimization techniques such as indexing, caching, thread analysis, and code adjustments to bring CPU usage back to normal levels.
This article explains why database sharding is needed, how to identify performance bottlenecks, and provides step‑by‑step guidance on SQL tuning, table redesign, architectural changes, and practical horizontal and vertical sharding techniques with real‑world e‑commerce examples.
An in‑depth comparison of PostgreSQL and MySQL covers their histories, ACID compliance, performance, scalability, advanced features, security, backup strategies, installation on Linux, and provides practical SQL code examples to help full‑stack developers choose the right open‑source relational database for their projects.
This article introduces a Python‑Flask‑MySQL student training plan management system, detailing its features such as visualized progress, SVD‑based course recommendation, forum discussion, simulated enrollment, project structure, environment setup, step‑by‑step installation, configuration, and usage instructions.
