This article compares MySQL's INT, TIMESTAMP, and DATETIME column types, explaining their limits, default behaviors, timezone handling, performance implications, and best‑practice recommendations for storing temporal data in modern applications.
This article explains MySQL’s native JSON data type introduced in 5.7, covering its binary storage format, built‑in functions, generated‑column indexing, performance advantages over string storage, and detailed CRUD examples with SQL syntax and practical code snippets.
This comprehensive guide walks you through a systematic methodology for diagnosing and fixing MySQL slow queries, covering proper log configuration, EXPLAIN analysis, index design, query rewriting, table restructuring, and practical caching techniques, while highlighting common misconceptions and real‑world case studies.
This article explains what MySQL composite (multi‑column) indexes are, how they are built and ordered, the leftmost‑prefix rule that governs their usage, factors such as cardinality and selectivity for optimal design, and common scenarios where the index may become ineffective.
Facing a MySQL that suddenly becomes unresponsive in production? This article walks through the exact five‑step investigative process—checking process status, examining connections, locating lock waits, analyzing slow queries and system bottlenecks, and applying emergency recovery—illustrated with real‑world examples and command‑line snippets.
Learn how to efficiently handle upserts in MySQL by leveraging the native DUPLICATE KEY UPDATE feature, simplifying code with a custom MyBatis‑Plus AbstractMethod injector, complete with thread‑safe locking, SQL generation, and integration steps for seamless backend development.
This article explains three frequent MySQL table‑design errors—choosing wrong data types and lengths, missing or redundant indexes, and improper storage structures—illustrating how they affect storage, query speed, and scalability, and offers practical guidelines to prevent them.
This tutorial walks you through preparing the environment, adding the Zabbix repository, installing required packages, configuring MySQL, setting up Zabbix server and agent, customizing the web interface, and adding monitored hosts on Ubuntu 22.04 LTS, complete with commands and screenshots.
A disastrous rm -rf command erased 2.3 TB of production MySQL data, but a meticulously designed multi‑layer backup strategy—including logical, physical, real‑time, and cloud backups—enabled a 99.4% data recovery within 72 hours, highlighting essential lessons and best‑practice guidelines for reliable data protection.
This article explains the complete lifecycle of a MySQL UPDATE—from the initial log buffer, through redo and undo logs, buffer‑pool dirty pages, checkpoint thresholds, binary logging, and the innodb_flush_log_at_trx_commit setting—highlighting how InnoDB guarantees durability, consistency, and crash recovery.
This article walks through the evolution from binary search trees to balanced trees, B‑trees and finally B+ trees, explaining how MySQL's InnoDB and MyISAM storage engines implement these structures to reduce disk I/O and boost query performance.
This article explains how MySQL processes the KILL command, detailing the interaction between threads, the role of the kill flag, and the is_killed() function, all illustrated with diagrams and code examples to help readers grasp thread termination internals.
This article details a step‑by‑step optimization of a large‑scale Excel import workflow, covering the transition from raw POI to EasyExcel, caching database lookups, batch INSERT statements, and parallel stream processing to reduce a 100k‑row import from minutes to under two minutes.
MySQL query optimization involves three distinct index processing stages—Index Key, Index Filter, and Table Filter—each handling different parts of condition matching and data retrieval, and mastering their roles and execution flow can dramatically improve query performance, especially with index condition pushdown.
This article explains MySQL replication architecture, detailing how binary logs are transferred, the roles of dump, I/O receiver, and SQL apply threads, the metadata tables they use, and key system variables for optimizing replication performance and durability.
This guide walks you through preparing the environment, choosing a data‑sync method, configuring both master and slave servers, verifying replication, troubleshooting common issues, and applying advanced settings to achieve a reliable, monitorable MySQL 5.7 master‑slave setup.
This article explains the functional differences, performance considerations, and practical use cases of MySQL's SELECT DISTINCT and GROUP BY clauses, providing syntax details, example tables, query results, and a comparison table to help developers choose the right approach for deduplication or aggregation tasks.
This article explains why MySQL master‑slave replication lag causes dirty reads, breaks down the asynchronous replication pipeline, and presents three practical mitigation strategies—including semi‑synchronous replication, forcing reads from the master, and cache double‑delete techniques—so you can choose the right solution for your workload.
This article explains why selecting only needed columns and ensuring deterministic ordering are essential for MySQL queries, then compares Offset/Limit pagination with cursor‑based pagination, detailing their syntax, advantages, drawbacks such as data drift and performance issues, and offers practical optimization tips.
This tutorial walks through setting up a Django backend for a Vue‑based sports shop, covering environment requirements, project structure, database initialization, API implementation, common pitfalls, and file‑upload handling with complete code snippets and screenshots.
This guide walks you through setting up a Python 3.8/Django 2.2 vulnerability scanner that uses Celery for asynchronous port and background scanning, integrates the AWVS API for vulnerability detection, and requires MySQL and Redis configuration, with detailed deployment steps and troubleshooting tips.
This article walks through a MySQL 5.6 scenario where a sub‑query to find students scoring 100 runs for hours, demonstrates how adding single‑column indexes cuts execution time dramatically, explores join‑based rewrites, composite indexes, left‑most prefix rules, covering indexes and sorting optimizations, and provides practical performance‑tuning tips.
Learn how to use PHP’s mysqli_query function to connect to MySQL, execute SELECT queries, handle results, and perform INSERT, UPDATE, and DELETE operations, with a complete code example and best‑practice tips for managing database connections and error handling.
This article walks through a MySQL 5.6 scenario with three tables, demonstrates why a sub‑query that scans millions of rows runs for hours, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query as a join, reduces execution time from over eight hours to a few milliseconds.
This article explains how to verify whether MySQL uses an index with EXPLAIN, outlines eight common reasons why an index may be ignored—including better alternatives, low selectivity, stale statistics, full‑table scans, structural limitations, function usage, invisible indexes—and provides practical commands and hints to resolve each issue.
This article explains MySQL's optimistic locking strategy, detailing how a version column enables conflict detection during updates, provides step‑by‑step SQL examples, compares it with pessimistic locking, and shows why it improves performance in read‑heavy, low‑conflict environments.
This article recounts a 3 AM MySQL outage, analyzes its root causes, and shares comprehensive operational strategies—including index optimization, connection‑pool tuning, slow‑query fixing, replication lag handling, monitoring metrics, automation scripts, performance tuning, security hardening, and future trends—to help DBAs prevent and resolve similar incidents.
This guide compiles high‑frequency Java interview questions—from core language concepts and collections to JVM tuning, MySQL indexing, Redis caching, Spring frameworks, micro‑services, and MQ reliability—providing a comprehensive reference for candidates targeting small‑to‑mid‑size companies.
This step‑by‑step guide explains how to install Nacos 2.3.2 on Windows and Linux, covering version requirements, hardware recommendations, Docker‑Compose quick start, MySQL integration, production cluster topology, security hardening, monitoring with Prometheus, common troubleshooting, and clean uninstallation.
Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine, dramatically cutting data transferred to the server layer, speeding up queries especially when using composite indexes, and requiring no changes to the original SQL statements.
This article explains why the binlog Position may not advance after executing SQL on an OceanBase MySQL tenant, outlines two main causes, and provides step‑by‑step commands to verify binlog task health and identify unsupported statements.
This article explains why the industry advises keeping MySQL single‑table row counts below ten million by examining InnoDB’s 16KB page structure, B+‑tree indexing mechanics, fan‑out calculations, and how page size and row size together determine the practical limits and performance cliffs of large tables.
This article explains the concept of MySQL pessimistic locking, its implementation via row‑level locks in InnoDB, and provides step‑by‑step SQL examples showing how SELECT … FOR UPDATE secures data against concurrent modifications.
This guide provides a step‑by‑step troubleshooting workflow for Spring Boot microservices using Nacos as a config/registry and MySQL as the database, covering log inspection, process checks, port listening, network connectivity, configuration validation, database connectivity, system resources, startup commands, and an optional one‑click diagnostic script.
This guide walks through the background, environment setup, installation steps, configuration parameters, and testing results for using the Percona audit logging plugin with MySQL 8.0, demonstrating compatibility, key settings, and how to filter logged events.
This article explains how to handle distributed consistency when a business operation writes to MySQL and calls external systems, using a financial reimbursement example to illustrate risks, analyzes the root cause, and presents a step‑by‑step solution that splits the large transaction into small, retryable units with code snippets and Spring optimization.
When a core order table with tens of millions of rows needs a new business field, directly running ALTER TABLE can lock the table and disrupt services, so this article explores master‑slave switching, online DDL tools, extension tables, JSON fields, and clever reuse of existing redundant columns to achieve the change safely and efficiently.
This article explains how a migration from MySQL to GoldenDB can cause an INSERT statement to fail due to differing ANSI_QUOTES settings, demonstrates the problematic SQL, and shows how to enable or disable ANSI_QUOTES to resolve the issue and improve SQL portability.
This article examines the fundamental mismatch between MySQL’s stateful nature and Docker’s container model, detailing performance penalties, data‑persistence risks, resource‑management challenges, security concerns, and operational complexity, while also outlining scenarios where Docker can be acceptable for development or limited production use.
This article compares MySQL and PostgreSQL by outlining their histories, architecture differences, benchmark results for SELECT, INSERT, and UPDATE operations, and discusses which scenarios each database best fits, helping readers decide which system to adopt for their projects.
This tutorial walks through building a MySQL master‑slave cluster on Kubernetes using StatefulSets, covering local persistent volumes, storage classes, ConfigMaps, Secrets, Services for read‑write splitting, and step‑by‑step YAML manifests with verification commands to ensure proper replication and scaling.
As network security threats rise, unpatchable MySQL vulnerabilities can be mitigated by configuring iptables rules that whitelist trusted IPs for port 3306 and drop all other traffic, with clear guidance on rule ordering and the differences between DROP and REJECT actions.
Oracle’s massive September 2025 layoffs hit the MySQL core team, prompting concerns about reduced innovation, a shift toward its Heatwave analytics product, and the broader impact on open‑source database ecosystems and developer choices.
This article explains the concept of table lookups in MySQL, shows how they arise from using secondary indexes, provides concrete query examples, and offers practical optimization techniques such as creating covering indexes, reducing selected columns, and analyzing execution plans to improve performance.
Learn how MySQL window functions—row_number, rank, and dense_rank—can efficiently rank and rank‑tie data such as class scores or sales amounts, with clear syntax examples, differences in handling duplicate values, and practical SQL queries illustrated with real‑world scenarios.
Learn how to diagnose and permanently fix MySQL character set problems—like garbled Chinese text or missing emoji—by checking current settings, altering databases and tables to utf8mb4, updating the my.cnf configuration, and avoiding common pitfalls, all with clear command examples.
MySQL AI, introduced for the database's 30th anniversary, integrates auto‑ML, generative AI, LLM‑driven text‑to‑SQL, and a vector engine directly into MySQL Enterprise, enabling developers to build intelligent applications on familiar SQL tools without moving data.
After a normal DDL change on the OneDBA platform, a table's storage grew nearly 100% and some queries slowed down, revealing a MySQL InnoDB B+Tree page‑split flaw that stores only one row per 16KB page when large records are inserted, which also corrupts statistics and leads to slow SQL.
This article explains the core purpose of MySQL indexes, enumerates twelve typical situations that cause index failure, provides concrete SQL examples and corrections, and shows how to use EXPLAIN to diagnose and verify index usage for better query performance.
Understanding MySQL's clustered indexes—including their definition, selection rules, customization via primary key design, practical creation examples, and key design considerations—helps developers optimize query performance and storage efficiency in InnoDB tables for various workloads.
This article demonstrates how to insert 300,000 rows into a MySQL table using MyBatis and JDBC, compares naïve single‑batch and per‑row approaches, presents optimized batch‑processing code with configurable batch sizes and wait times, and shares practical performance‑tuning tips such as index handling and connection‑pool configuration.
This article provides a comprehensive review of MySQL’s two primary index types—clustered (clustered index) and non‑clustered (non‑clustered index)—explaining their storage mechanisms, performance trade‑offs, typical use cases, and best‑practice guidelines for selecting the appropriate index in database design.
This article explains how MySQL stores data in 16KB pages, uses B+‑tree indexes to locate rows, derives the commonly cited 20‑million‑row limit from page fan‑out and row size calculations, compares B‑tree and B+‑tree structures, and offers practical optimization tips.
This article explains how InnoDB stores data in 16 KB pages, how B+Tree indexes work, derives the formula for the maximum number of rows a single MySQL table can hold, and why the practical limit of about twenty million rows exists, along with performance implications and optimization tips.
This article explains why the SHOW GRANTS command can display permissions that differ from a MySQL user's actual abilities after modifying the host field, analyzes the underlying cause across multiple privilege tables, and provides safe methods—including RENAME USER—to resolve the inconsistency.
This article explains why synchronizing MySQL with Elasticsearch is beneficial and compares five practical solutions—dual‑write, scheduled tasks, binlog, Canal, and MQ asynchronous—detailing their implementation, advantages, disadvantages, and suitable scenarios for each.
Learn how to leverage MySQL's EXPLAIN and EXPLAIN ANALYZE commands to diagnose query performance, understand each column of the output, choose appropriate indexes, and apply practical optimization steps—including multi‑column indexes—to dramatically reduce row scans and speed up queries.
A nighttime incident revealed that a MySQL transaction updated a row but subsequent reads returned the old value, prompting a deep investigation that uncovered InnoDB’s repeatable‑read snapshot behavior, concurrent updates, and how MySQL may skip updates when data appears unchanged, along with reproducible steps and mitigation advice.
This guide provides a comprehensive, step‑by‑step shell script that automates the installation, configuration, and service setup of MySQL 8.4 from source on a wide range of Linux distributions, including Rocky, AlmaLinux, CentOS, openEuler, Anolis, OpenCloudOS, Kylin, UOS, openSUSE, Ubuntu, and Debian.
This article examines why running MySQL in Docker is generally discouraged, covering container‑state mismatches, I/O and network performance penalties, data persistence challenges, resource limits, security concerns, monitoring difficulties, and finally outlines scenarios where Docker may be acceptable and recommended production alternatives.
This comprehensive guide walks you through real-world MySQL performance tuning, covering slow query analysis, index design, query optimization, parameter tuning, hardware considerations, sharding, and fault handling, with practical scripts and case studies to help you transform sluggish databases into high‑throughput, scalable systems.
This article compares MySQL and PostgreSQL by outlining their architectures, presenting benchmark results that show PostgreSQL’s superior throughput and latency, and discussing the scenarios where each database shines to help developers decide which one fits their project best.
MySQL 8.0 will lose official support in April 2026, prompting a migration to the LTS 8.4 release, which removes several system variables and functions while deprecating others, so DBAs must adjust configurations, tools, and scripts to avoid compatibility issues.
This article explains how indexes boost query speed but also incur storage, write‑performance, and maintenance costs, offering practical SQL scripts and guidelines for auditing, testing, and safely removing unnecessary indexes.
Oracle's recent large‑scale layoffs in its MySQL division have sparked community worries about the future of the world’s most popular open‑source database, highlighting shifts toward AI‑focused services and the rise of MariaDB as an alternative.
This comprehensive tutorial explains how to install MySQL 8.0 and 8.4 binary packages on various Linux distributions, covering user creation, package download, environment setup, configuration, initialization, dependency handling, service script creation, password setting, and verification of the installation.
Deploying MySQL in production containers introduces volatile storage, unstable networking, performance variability, and replication challenges that clash with the database's need for persistent, high‑availability, and consistent operation, making native VM or bare‑metal deployments far more reliable.
This article provides a comprehensive overview of MySQL's architecture, covering the client layer, connectors, lexical analysis, query optimizer, executor, storage engines, caching, and logging, and explains how each component contributes to a robust, high‑performance database system.
This guide compiles essential MySQL interview questions covering storage engines, indexes, transactions, locks, and triggers, providing clear explanations and illustrations for both beginners and experienced candidates seeking to excel in database interviews.
This comprehensive guide introduces MySQL’s core concepts, architecture, and features, then walks you through step‑by‑step installation and configuration on popular Linux distributions—including Rocky, AlmaLinux, CentOS, Ubuntu, Debian, and openSUSE—covering network setup, repository configuration, and post‑install security tweaks.
This article explains the distinct yet collaborative roles of MySQL's Redo Log and Binlog, covering two‑phase commit, write‑ahead logging, flushing mechanisms, performance trade‑offs, and their use cases in crash recovery, replication, and point‑in‑time restoration.
This guide walks through MySQL's join operations—inner, left, right, and full outer—using a simple grocery‑items schema, showing how to create tables, write correct queries, avoid common pitfalls like unintended cross joins, apply aliases, and filter unassociated rows with WHERE and UNION.
This article examines why a SELECT … FOR UPDATE query that appears to use a covering index in MySQL actually performs a table‑row lookup, detailing indirect evidence from performance_schema locks and direct proof through InnoDB source code, and explains the necessity of accessing the primary key for transaction isolation.
This article walks through eight practical MySQL optimization techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, handling mixed sorting, replacing EXISTS with joins, pushing predicates, narrowing result sets early, and leveraging WITH clauses—to dramatically reduce query execution time from seconds to milliseconds.
When a core order table with tens of millions of rows needs a new column, naïve ALTER TABLE can lock the table and cause outages, so this article explores master‑slave switching, online schema‑change tools, JSON or redundant fields, and practical tips for low‑risk, production‑grade schema evolution.
This guide explains why MySQL cannot index JSON columns directly and demonstrates two practical work‑arounds—using generated columns in MySQL 5.7 and functional indexes in MySQL 8.0.13—to efficiently index a nested request.email field for fast queries.
This article demonstrates how to generate, insert, and query a ten‑million‑row MySQL table, measures the performance of ordinary LIMIT pagination versus optimized techniques, and provides practical SQL snippets and tips for handling large offsets and massive data sets.
This comprehensive guide walks you through diagnosing MySQL slow queries, from identifying root causes and configuring slow‑query logs to applying advanced indexing, query‑rewriting, and monitoring techniques—complete with real‑world case studies that demonstrate how to cut query times from seconds to milliseconds.
This article explains the main directory layout of MySQL 8, the locations of data files, configuration files, system databases, and how InnoDB and MyISAM storage engines represent tables and indexes on the file system, including the use of system and file‑per‑table tablespaces.
This article explores why MySQL alone struggles with large‑scale queries, introduces Elasticsearch as a complementary search engine, and compares several synchronization methods—including synchronous and asynchronous dual‑write, Logstash, binlog‑based, Canal, and Alibaba Cloud DTS—detailing their advantages, drawbacks, and typical application scenarios.
This guide walks through the reasons for MySQL master‑slave replication, explains its core mechanisms, details step‑by‑step environment planning, configuration, data initialization, replication setup, monitoring, failover with MHA, read‑write splitting using ProxySQL, performance tuning, troubleshooting, and best‑practice recommendations for enterprise‑grade high availability.
The article explains how using OFFSET for pagination can cause severe performance degradation as data grows, and demonstrates how switching to keyset (seek) pagination, optionally combined with cursor encoding, index‑only OFFSET, or materialized views, dramatically reduces query latency.
A recent Stack Overflow survey shows PostgreSQL surpassing MySQL in popularity, and the article explains the technical advantages, real‑world performance tests, and evolving use cases that make PostgreSQL the preferred choice for many developers while still acknowledging MySQL’s enduring role for beginners.
This article examines why containerizing MySQL often leads to data‑security, performance, state, and resource‑isolation problems, outlines five key drawbacks of running MySQL in Docker, and explains the scenarios where containerization can still be viable.
When a core MySQL order table reaches tens of millions of rows, adding a new column can lock the table and disrupt services, so the article walks through master‑slave switching, online DDL tools, log‑based workarounds, extension tables, JSON fields, and a final redundant‑field trick to achieve the change safely.
The article details a real‑world case of splitting a massive MySQL financial flow table, covering the initial problems, sharding strategy selection, technical challenges like multi‑datasource transactions and pagination, data migration plans, and a phased rollout to ensure system stability.
This article explains why a MySQL GROUP BY query can degrade from seconds to minutes as data grows, shows how to locate the bottlenecks with EXPLAIN and profiling tools, and provides practical indexing, query rewriting, temporary‑table tuning, batch aggregation, and distributed‑database strategies to restore performance.
This article deeply explores MySQL's transaction isolation levels, explaining ACID properties, the four standard isolation levels, their phenomena such as dirty, non‑repeatable, and phantom reads, and how InnoDB implements them using MVCC, undo/redo logs, and various lock mechanisms.
To prevent costly data loss during database failures, the XiaoHongShu team designed a resource‑efficient Binlog Server that doubles half‑sync replication speed to over 300 MB/s, enables automatic data recovery without manual intervention, and ensures RPO = 0 across fully deployed MySQL clusters.
This article investigates a puzzling MySQL behavior where a row updated within a transaction sometimes returns its previous value, explains the role of InnoDB's snapshot reads and ReadView, reproduces the issue, and offers practical ways to prevent the “update disappearance” scenario.
The article walks through replacing MyBatis with MyBatis‑Plus in a legacy project, explains the LocalDateTime conversion errors caused by version changes in MyBatis and mysql‑connector‑java, shows how upgrading the JDBC driver resolves the issue, and shares lessons on cautious component upgrades and testing.
This guide walks you through installing and configuring Prometheus and Grafana, comparing Prometheus with Zabbix, adding exporters for system metrics, MySQL, RabbitMQ, Redis and TiDB, setting up dashboards, plugins, and email alerts to create a comprehensive monitoring solution.
This article walks through two enterprise MySQL disaster‑recovery scenarios, detailing backup policies, binlog extraction, temporary database restoration, and step‑by‑step commands to safely recover accidentally dropped tables and ensure data integrity.
In this detailed case study, the author walks through a real‑world MySQL slow‑query incident, analyzes execution plans, logs, and profiling data, and applies a four‑step optimization strategy—including index creation, query rewriting, covering indexes, and materialized views—to reduce execution time from 20 seconds to 200 milliseconds, achieving over 100× speedup.
This article investigates whether repeatedly assigning different or large values to MySQL session variables causes the session's memory usage to grow, presenting two experiments with functions, processlist analysis, and concluding that only large values significantly increase memory consumption.
This article explores why adding columns to massive MySQL tables can lock the database, explains the risks, and presents six practical solutions—including native online DDL, downtime maintenance, PT‑OSC, logical migration with dual writes, gh‑ost, and partition sliding windows—along with best‑practice tips for monitoring, backup, and performance.
This article explains MySQL InnoDB's Next-Key Lock mechanism, how it combines row and gap locks to prevent phantom reads under the REPEATABLE READ isolation level, illustrates lock ranges with examples, discusses when it degrades to row or gap locks, and highlights its advantages and potential deadlock risks.
The article explains the Copy‑On‑Write (COW) technique, its read‑write separation design, step‑by‑step flow, practical demos, Java's CopyOnWriteArrayList implementation, and MySQL InnoDB MVCC usage, while also discussing its performance benefits and inherent limitations.
This article explains the fundamentals of database indexes, how they work, common pitfalls such as slow queries and index misuse, and provides practical solutions, best‑practice design principles, and differences between MySQL and PostgreSQL, all illustrated with clear SQL examples and diagrams.
This article explains the differences between MySQL’s default REPEATABLE READ (RR) and READ COMMITTED (RC) isolation levels, why large internet firms switch to RC, the mechanics of master‑slave replication, the three binlog formats, and how gap locks affect consistency and performance.
