This article presents a detailed case study of optimizing a massive MySQL table using time‑based partitioning, describing the problem background, partition design, migration scripts, performance benchmarks, and ongoing maintenance procedures for a ten‑year data archive.
The article analyses why indiscriminately killing slow MySQL queries is risky, enumerates the drawbacks of common kill‑based approaches, and proposes more reliable designs such as registration, signature‑based killing, and source‑code modifications to safely mitigate query‑induced database avalanches.
This article explains InnoDB's lock system in MySQL, covering lock granularity, intent, shared, exclusive and auto‑increment locks, row‑lock types, the underlying C++ structures, lock mode and type encoding, and the compatibility and strength matrices that govern lock acquisition and waiting.
This article introduces Change Data Capture (CDC), compares query‑based and log‑based CDC, explains Debezium and ClickHouse, and provides step‑by‑step Flink CDC and Flink SQL CDC examples—including full Java code—to stream MySQL binlog changes into ClickHouse for real‑time analytics.
This guide explains the Percona XtraDB Cluster (PXC) architecture, its advantages and limitations, and provides step‑by‑step commands for removing MariaDB, opening firewall ports, disabling SELinux, downloading packages, configuring MySQL, bootstrapping the first node, adding additional nodes, and verifying the cluster status.
This article explains how MySQL 8.0.27 adds multi‑factor authentication (MFA) via the new authentication_policy system variable, demonstrates configuring the variable, creating users with single or multiple authentication plugins, installing external plugins, and verifies MFA behavior with practical Docker‑based examples.
This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales data for multiple business units and their associated products in a single indexed query.
Although MySQL’s int type always occupies 4 bytes, the number in parentheses (e.g., int(1) or int(10)) does not restrict its range; only when combined with ZEROFILL does it affect display formatting, as demonstrated by inserting the maximum unsigned int value and observing zero‑padded outputs.
This tutorial walks through creating a MySQL database, setting up a backup user, performing full and incremental backups with XtraBackup, compressing and transferring backup files, and restoring the database by preparing logs, copying back data, fixing permissions, restarting MySQL, and verifying the restored data.
This article explains the classification of InnoDB indexes into clustered and secondary types, describes how they are stored and accessed, and introduces optimization concepts such as back‑to‑table queries, covering indexes, and index pushdown to improve MySQL performance.
This post shares a comprehensive Java interview handbook containing over 1,000 questions covering MyBatis, Zookeeper, Dubbo, Redis, MySQL, concurrency, Spring, Kafka, micro‑services and more, hosted on GitHub with 30K+ stars, and explains how to obtain the latest PDF via a WeChat QR code.
This article explains MySQL high availability concepts, how master‑slave replication works, how to measure and mitigate replication lag, and compares reliable‑first and availability‑first failover strategies with practical experiments on binlog formats.
This article explains the basics of MySQL master‑slave replication, illustrates how read/write splitting works to alleviate performance bottlenecks, and shares practical test results showing reduced latency on the master and improved overall throughput when directing reads to replica servers.
This article explains the four SQL language categories (DDL, DML, DCL, TCL), various backup and recovery types, common MySQL backup methods, and flashback recovery techniques, providing detailed guidance and tool comparisons for database administrators.
This guide walks you through preparing the environment, adding the RadonDB MySQL operator repository, deploying the operator, installing a MySQL cluster, and verifying the deployment on KubeSphere, providing all necessary commands and screenshots for a successful high‑availability setup.
This guide demonstrates how to perform a full MySQL backup, create incremental backups, handle accidental database deletions, and restore the data using Percona XtraBackup on a Linux server, including detailed command-line steps and permission adjustments.
This article explores how to query sales data for multiple business units and selected products in MySQL, comparing loop queries, OR concatenation, mixed filtering, and SQL‑92 row‑value comparison, ultimately recommending the row‑comparison technique for efficiency and maintainability.
This article explains how a large‑scale membership platform achieved over 20,000 TPS with high performance and zero downtime by deploying dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis caching with distributed locks, and a seamless MySQL migration and HA strategy.
This article explains MySQL master‑slave read/write separation, the binlog and relay log mechanisms, master‑slave delay, and how different transaction isolation levels affect whether the master or slave may return stale data, illustrated with practical examples and SQL snippets.
The article investigates why a MySQL server shows a large number of TCP connections in TIME_WAIT state, examines OS and MySQL logs, tests sysctl adjustments, captures network packets to reveal short‑lived client connections, and concludes that using persistent connections can eliminate the issue.
This article introduces the fundamentals of Flink CDC, explains its application scenarios and underlying technologies, compares query‑based and log‑based CDC, showcases open‑source solutions, and provides detailed Java and SQL examples for building real‑time ETL pipelines with MySQL and Flink.
This article explains the differences between TLS and SSL, details the TLS handshake and certificate mechanisms, and provides step‑by‑step guidance for configuring encrypted connections on MySQL servers, clients, and JDBC drivers.
The article explains MySQL InnoDB lock scopes and modes—including record, gap, next‑key and insert‑intention locks—illustrates a deadlock caused by concurrent inserts on a distributed lock table, and recommends limiting concurrency, sharding by business key, and splitting lock operations into smaller sub‑transactions to avoid such deadlocks.
This article delves into MySQL’s internal logging mechanisms—redo log, undo log, and binlog—explaining their structures, purposes, and interactions, and then explores transaction concepts such as dirty reads/writes, non‑repeatable reads, phantom reads, isolation levels, and the MVCC implementation that ensures data consistency.
The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.
This weekly digest summarizes the latest MySQL 8.0 enhancements, replication delay case, user connection memory limits, Oracle‑to‑MySQL synchronization, new SQLE release, DBLE middleware developments, bug fixes, community Q&A, recruitment notices, and upcoming plans, providing concise technical insights for the open‑source community.
This article explores MySQL's client‑server interaction, connection methods, query parsing and optimization, InnoDB storage engine design, data page layout, BufferPool structures, and the free, flush, and LRU linked lists, including their challenges and optimization techniques.
This article provides a step‑by‑step guide on configuring DTLE 4.22.01.0 to perform Oracle‑to‑MySQL incremental data synchronization, covering type mapping, DML/DDL support, environment setup, job creation, data verification, and handling of compatibility limitations.
The article compares MySQL’s relational tables with MongoDB’s schema‑free documents, detailing equivalent concepts, CRUD syntax, and benchmark results that show MongoDB achieving higher write and query throughput, easier schema evolution, and built‑in sharding, recommending it for new agile projects while suggesting MySQL remain viable for legacy systems.
This article explains why traditional B‑Tree indexes fail for keyword searches, introduces the concept of inverted indexes, shows how to create and use MySQL InnoDB full‑text indexes with MATCH‑AGAINST in various modes, and covers index maintenance and query‑expansion techniques.
MySQL 8.0.28 introduces the connection_memory_limit parameter and related settings that let administrators cap the memory used by each client connection, and this article demonstrates how to configure the limits, test them with sample queries, and observe the behavior for regular and privileged users.
By migrating a latency‑heavy MySQL query to AnalyticDB, employing binlog‑based real‑time sync, refining composite indexes, adding clustering and filter hints, and batching inserts, the team cut execution time from about two seconds to roughly one hundred milliseconds, achieving over 99 % of queries under one second.
This article explains various methods for scheduled MySQL backups, covering mysqldump command usage, example backup commands for whole or specific databases, a Bash script to retain a fixed number of backups, and configuring cron jobs to automate the backup and restoration processes.
The MHA Re-Edition tool revives the discontinued MHA manager for MySQL, adding GTID‑based failover, password‑only SSH authentication, lightweight binaries, VIP migration, WeChat alerts, remote‑card reboot, and detailed configuration options, with step‑by‑step deployment instructions and sample app1.cnf parameters for high‑availability clusters.
The article explains how a MySQL 5.6 master‑slave setup experienced growing replication lag on an off‑site replica, how the issue was traced to page‑compression on a large table using show slave status, perf and pstack, and how disabling compression resolved the delay.
This article investigates MySQL's recommendation to use auto_increment primary keys instead of UUID or random snowflake IDs by comparing table designs, running SpringBoot‑based insert benchmarks, analyzing index structures, and discussing the performance trade‑offs and practical drawbacks of each approach.
This article explains how IN and EXISTS work in MySQL, compares their execution strategies, shows example queries, and provides guidance on when to use each based on the relative sizes of the involved tables.
This article explains the purpose, syntax rules, and practical examples of the UNION operator in MySQL, covering distinct and ALL modes, ORDER BY restrictions, new TABLE and VALUES usages introduced in MySQL 8.0.19, and differences from MySQL 5.7.
This weekly community bulletin summarizes recent MySQL problem reports, new features and bug fixes in the DBLE distributed middleware, DTLE data‑transfer component, and SQLE SQL audit tool, while also highlighting upcoming development plans and inviting community feedback.
The SQLE 1.2202.0 release introduces new user group and permission management, scoring for audit results, expanded support for MySQL, Oracle, Java application SQL capture, Db2 audit plugins, additional MySQL audit rules, UI optimizations, and several critical bug fixes.
This article recounts the author's experience of identifying and solving various backend interface performance bottlenecks—including slow MySQL queries, complex business logic, thread‑pool and lock design flaws, and machine issues—by applying targeted optimizations such as pagination redesign, indexing, concurrency, and caching.
This article analyzes why a MySQL query with a large LIMIT offset can take seconds to run, demonstrates the underlying index and buffer‑pool behavior, and shows how rewriting the query with a sub‑select of primary keys reduces execution time from over 16 seconds to under half a second.
A mysterious issue where some MySQL queries return only one row after executing SET SQL_SELECT_LIMIT=1 is traced to the MySQL driver’s Statement.setMaxRows method, which sets a session variable that can pollute pooled connections, leading to unexpected row limits.
This article introduces Sequel Pro, a lightweight macOS client for MySQL and MariaDB, explains how to configure standard connections, describes its main interface modules such as database selection, table structure, query editor, and provides visual guidance for common operations.
This article explains how InnoDB stores data in 16 KB pages, links pages via a double‑linked list, uses page directories and primary‑key directories for binary search, and builds B+‑tree index pages to accelerate MySQL queries, covering free space, page splits, and the overall search flow.
This article explains why MySQL may ignore an index on range queries, shows example queries with EXPLAIN output, describes the optimizer's row‑count threshold that triggers a full table scan, and offers guidance on keeping indexes effective.
This article presents a detailed benchmark comparing MySQL 8.0.19 and MariaDB 10.4.12 on a Windows 10 i7 machine, covering single‑row and batch inserts, various SELECT queries with and without indexes, and analyzes the trade‑off between speed and memory usage.
This article examines how MySQL slaves recover their replication state in GTID‑based and binary‑log‑file‑position scenarios, identifies unsafe factors that can cause crash‑unsafe behavior, and explains how enabling relay_log_recovery=on mitigates these risks while outlining remaining limitations.
This article analyzes a MySQL replication failure caused by stale GTID execution data and unsynchronized binlog persistence, explains the underlying mechanisms, demonstrates reproducible testing, and provides two practical remediation methods to skip duplicate-key errors and restore replication.
Through a series of MySQL 5.7.26 experiments the article reveals how record‑level locks are applied to primary‑key indexes when non‑indexed columns are locked, how isolation levels (READ COMMITTED vs REPEATABLE READ) change the locking order, and why both SELECT … FOR UPDATE and UPDATE statements can block other transactions.
The article examines the drawbacks of running MySQL in Docker containers, highlighting data safety, performance, state management, and resource isolation concerns while also discussing scenarios where containerizing MySQL might be feasible.
This article explains why using MySQL's --force option with default extended‑insert backups can cause missing rows during import, demonstrates the issue with a reproducible example, and shows how disabling extended‑insert with --skip-extended-insert restores all data while providing practical usage recommendations.
This article provides a comprehensive guide to Linux Virtual Server (LVS), covering its history, four operating modes, scheduling algorithms, installation steps, MySQL load‑balancing configuration, ARP suppression techniques, and high‑availability setup using keepalived, all illustrated with diagrams and code snippets.
The article examines tiered data storage and evaluates four cache‑synchronization strategies—updating the database before the cache, deleting the cache before updating the database, updating the cache before the database, and deleting the cache after a database update—highlighting their trade‑offs and practical solutions such as delayed double deletion, message‑queue retries, and binlog‑driven cache updates.
The SQLE 1.2202.0-pre1 release introduces a preview version of the open‑source SQL audit platform with new user‑group permissions, task scoring, support for MySQL schema audits, Oracle TopSQL capture, and Java application SQL capture, along with links to the repository, documentation, and plugin development guide.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
The article explains why MHA’s default long‑connection ping cannot detect a hung MySQL instance, introduces three configurable ping_type options (select, connect, insert), shows how to modify the configuration and test the behavior with Perl code and shell scripts, and details the internal health‑check workflow.
This article explains how InnoDB stores data on disk, the role of 16 KB pages in read/write operations, the dynamic row format’s length and NULL lists, overflow handling, and why varchar columns are limited to 16 383 characters under utf8mb4, illustrated with SQL examples.
This article extends the previous discussion on MySQL time‑based partition implementation by detailing how to write efficient SQL queries, demonstrating partition pruning with supported functions, and offering practical optimization techniques for tables that use unsupported partition functions.
This article compiles a comprehensive set of Tencent interview questions and detailed answers covering HashMap internals, thread safety, red‑black trees, Redis performance, MySQL indexing, TCP reliability, IO multiplexing, RPC, and system bottleneck analysis to help candidates prepare effectively.
This article introduces Change Data Capture (CDC), compares query‑based and log‑based approaches, explains Debezium and ClickHouse, and provides detailed Flink CDC and Flink SQL CDC examples—including Java source code, custom deserialization schema, ClickHouse sink implementation, and required Maven dependencies—to synchronize MySQL data into ClickHouse in real time.
This article explains InnoDB's storage engine mechanics, page-based I/O, the default Dynamic row format, how variable‑length fields like VARCHAR are recorded, why the maximum usable length is 16383 characters in utf8mb4, and the handling of NULL lists and overflow columns.
This article explains MySQL indexes—what they are, their advantages and disadvantages, how they improve query performance, and the scenarios where you should or shouldn't create them—plus a free PDF of interview questions for deeper study.
The article explains why using LIMIT together with ORDER BY in MySQL 5.6 can cause duplicate records on subsequent pages, analyzes the priority‑queue optimization that makes the sort unstable, and offers practical solutions such as indexing, proper pagination logic, and query adjustments.
This article explains how to prevent data loss in MySQL by enabling binlog in ROW format, performing full and incremental restores using mysqldump or XtraBackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements, and using flashback tools such as binlog2sql and MyFlash.
The article explains why data inconsistency occurs when MySQL and Redis are used together under heavy load, analyzes cache‑delete ordering issues, and presents two practical solutions—delayed double‑delete and asynchronous cache updates via MySQL binlog—along with their trade‑offs and implementation steps.
This article explains how MySQL treats NULL values, the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, and demonstrates with practical examples how NULL affects indexing, query performance, aggregation functions, and sorting, while offering recommendations for schema design.
This guide walks through MySQL's query execution sequence, then presents concrete optimization techniques—from selecting specific columns and avoiding OR conditions to proper indexing, using EXPLAIN, handling VARCHAR vs CHAR, limiting result sets, and improving joins, GROUP BY, and UNION operations—complete with code examples and visual explain plans.
This article compiles a collection of practical Bash scripts for Linux system administrators, covering automatic DDoS IP blocking, email alert setup, single‑ and multi‑database MySQL backups, Nginx log rotation and analysis, real‑time network traffic monitoring, server initialization, and bulk disk‑usage checks across many hosts.
This article analyzes why data inconsistencies arise when MySQL and Redis are used together under high load, compares cache‑delete ordering problems, and presents two practical solutions—delayed double‑delete and asynchronous binlog‑driven cache updates—along with implementation tips and trade‑offs.
This guide explains how to collect, store, clean, and aggregate daily user metrics—including new users, active users, and multi‑day retention—using shell scripts, cron jobs, MySQL partitioned tables, and stored procedures, suitable for small‑to‑medium sized products.
This article explains that the numeric value in MySQL INT definitions (e.g., int(1) or int(10)) does not limit the column's range, demonstrates inserting the maximum unsigned value, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
The article explains that the number in MySQL's INT definition (e.g., INT(1) or INT(4)) does not limit the column's numeric range, but only affects display width when combined with ZEROFILL, illustrating the concept with practical CREATE and INSERT examples.
This article explains why MySQL’s DELETE only sets a delete‑mark, how InnoDB’s MVCC and purge thread reuse freed pages, the impact of B+‑tree storage on I/O, page merge and split mechanisms, and the proper way to rebuild tables to recover space.
This article analyzes why using SET unique_checks=0 during large MySQL imports can bypass unique‑key checks when data is not cached in the InnoDB buffer pool, explains the underlying change‑buffer mechanism, demonstrates the behavior with sysbench tests, and offers a safe workaround.
This guide explains MySQL custom functions, covering their definition, advantages over stored procedures, step‑by‑step creation syntax, parameter handling, invocation, and management commands such as showing, altering, and dropping functions, with practical examples for both parameterless and parameterized functions and a concise comparison table.
This article explains MySQL master‑slave replication fundamentals, why it’s used, the replication workflow, causes of lag, and presents multiple practical solutions—including system tuning, partitioning, caching, multi‑threaded relay log replay, and read‑from‑master strategies—to improve performance and reliability.
This article investigates how the innodb_ddl_threads, innodb_ddl_buffer_size, and innodb_parallel_read_threads variables introduced in MySQL 8.0.27 affect parallel DDL execution speed and resource consumption, using a 50‑million‑row test table and presenting detailed benchmark results.
Even when scanning a 200 GB InnoDB table on a server with only 100 GB RAM, MySQL streams results using a small net buffer and an optimized InnoDB buffer‑pool LRU, so the server’s memory never blows up, though I/O load remains high.
This guide explains why InnoDB full‑text search is needed, how inverted indexes work, how to create and use full‑text indexes with MATCH AGAINST in natural language, boolean, and query‑expansion modes, and how to drop them safely.
A comprehensive collection of MySQL interview questions and answers covering storage engines, lock types, gap locks, deadlock avoidance, isolation levels, index types, covering indexes, the left‑most prefix rule, SQL tuning, master‑slave replication, latency handling, sharding challenges, and global ID generation.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This article explains why InnoDB full‑text search is needed for keyword‑based queries, introduces inverted index structures, shows how to create, use, and delete full‑text indexes in MySQL, and demonstrates natural language, boolean, and query‑expansion search modes with practical SQL examples.
This article evaluates how MySQL handles time data stored as datetime, timestamp, and bigint by inserting half a million records and measuring query, grouping, and sorting speeds, concluding that bigint generally offers the best performance for time‑range operations.
This article presents a collection of practical Bash scripts for Linux servers, covering DOS attack IP blocking, alert emailing, MySQL backup (single and multi‑loop), Nginx log rotation and analysis, real‑time network traffic monitoring, system initialization, and disk usage checks across multiple hosts.
This article explains how InnoDB stores data on disk using 16 KB pages, describes the dynamic row format and its handling of variable‑length columns such as VARCHAR, and clarifies why MySQL limits VARCHAR to a maximum of 16 383 characters under utf8mb4.
This article shares a detailed account of a backend engineer's multi‑stage interview process at Huanju (formerly YY), covering personal background, common Java and system design questions, database and caching strategies, distributed lock handling, and reflections on preparation and outcomes.
This guide explains the concepts, benefits, and step‑by‑step configuration of MySQL master‑slave replication and read/write splitting, including required master and slave settings, practical VM setup, and Mycat configuration files for routing reads and writes.
This article presents a curated, non‑ranked collection of Chinese database community public accounts and resources covering PostgreSQL, MongoDB, MySQL, and miscellaneous IT knowledge, thanking followers and outlining future content plans for professional development.
This guide explains MySQL’s permission system, shows how to configure remote access for the root user by setting the host field to ‘%’, granting all privileges, flushing privileges, and clarifies the role of the skip‑grant‑tables option, while detailing the underlying tables and privilege levels.
This weekly community newsletter summarizes the latest MySQL change‑buffer insights, data‑consistency checks, new password policies, releases of DBLE, DTLE, and SQLE middleware, along with bug fixes, feature improvements, community Q&A, and plans for the upcoming week.
The article examines why containerizing MySQL with Docker is problematic, highlighting risks to data safety, performance bottlenecks, state management challenges, and insufficient resource isolation, while also noting scenarios where containerized databases may be viable.
This article explains MySQL's index storage structures, the difference between clustered and secondary indexes, and demonstrates six typical scenarios—wildcard LIKE, functions on indexed columns, expression calculations, implicit type conversion, left‑most rule violations in composite indexes, and OR conditions—that cause indexes to become ineffective and lead to full‑table scans.
The SQLE 1.2201.0 release introduces workflow optimizations, new MySQL and SQL Server audit rules, enhanced pre‑check capabilities, several UI improvements, and numerous bug fixes, while providing links to the source repository, documentation, and plugin development guide.
This article shares a step‑by‑step account of a candidate's three‑round ByteDance interview, covering background, technical questions on Java, JVM, Redis, MySQL, Kafka, and algorithm challenges, along with personal reflections on pacing, preparation, and why concise answers matter.
The article explores why knowledge retention varies among engineers, proposes concrete learning practices such as systematic note‑taking, focused "nail" learning, hands‑on experimentation with tools like tcpdump and SSH, and distinguishes between knowledge efficiency and engineering efficiency to help readers build lasting, applicable expertise.
DTLE 4.22.01.0, an open‑source MySQL‑focused data transfer component, has been released, introducing Oracle‑MySQL incremental DDL/DML sync, enhanced WHERE clause support, initial Kafka schema‑change handling, and numerous bug fixes including binlog relay issues, character‑set handling, and CREATE TABLE … LIKE renaming problems.
This tutorial provides step‑by‑step instructions for installing Hadoop 3.1.1, Homebrew, Hive, and configuring MySQL as Hive's metastore on macOS, including environment variable setup, hive‑site.xml configuration, MySQL connector placement, schema initialization, and verification commands.
This article explains MySQL's client‑server architecture, detailing how the server processes connections, parses and optimizes queries, generates execution plans, and interacts with various storage engines, while also covering configuration variables, connection monitoring, and practical command examples.
