This article walks through nine common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to sub‑query updates, mixed sorting, EXISTS usage, condition pushdown, early range reduction, and intermediate result pushdown—showing how to rewrite each pattern for dramatically faster execution.
This article explains MySQL's date and time manipulation functions—including addtime, adddate, date_add, subtime, subdate, and date_sub—showing their syntax, usage with numeric and INTERVAL arguments, and providing example queries for adding or subtracting seconds, minutes, days, weeks, months, and years.
The article investigates why a MySQL‑slave machine shows low free memory despite modest MySQL and Redis usage, revealing that massive hidden files under /run/systemd/users and tmpfs‑based shared memory consume several gigabytes, and proposes practical remediation steps.
This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
This article explains MySQL master‑slave replication, covering its architecture, binlog‑based replication process, causes and mitigation of replication lag, and strategies for master‑slave failover, helping readers grasp why and how to use replication for read/write separation, high availability, and backup.
The article explains how to efficiently retrieve and process massive MySQL result sets in Java using MyBatis, comparing regular pagination, streaming queries via Cursor, and cursor-based fetchSize techniques, and provides practical code examples and best‑practice tips to avoid OOM and improve performance.
This article explains the challenges of slow DDL operations in MySQL, introduces TXSQL’s Parallel DDL framework—including Copy, Inplace, and Instant algorithms—details their design, implementation, code examples, and performance evaluations, and compares them with other database solutions.
This article compiles a technical interview recap covering MySQL MVCC, atomicity and persistence mechanisms, operating‑system page cache and deadlock concepts, TCP reliability and flow‑control, Redis persistence and clustering, as well as distributed transaction and consensus fundamentals.
This article shares a concise 10‑line Bash script, generated by ChatGPT, that captures MySQL QPS, TPS, and connection counts, timestamps each record, and appends the metrics to a log file for easy performance monitoring.
This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.
This article explains MySQL index covering and how avoiding table lookups (back‑to‑table) can reduce retrieval steps, improve query performance, and provides practical examples, execution‑plan analysis, and optimization tips for large tables in production.
This article explains why MySQL uses MVCC, how undo‑log version chains and read views implement multi‑version concurrency control, and details the various lock types—including global, table, metadata, intention, row, gap, and next‑key locks—under different isolation levels with practical code examples.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
The article investigates why MySQL 8.0.27 repeatedly logs a deprecation warning for the sha256_password plugin, tracing the issue to authentication attempts with non‑existent users, analyzing source code, and proposing configuration and code‑level fixes.
A detailed post‑mortem of a MySQL dual‑master cluster failure caused by a missing mysql-bin.index file, showing step‑by‑step diagnostics, container logs, replication position fixes, and preventive measures for high‑availability deployments.
This article introduces Alibaba's open‑source Canal middleware, explains its architecture and high‑availability design, walks through MySQL binlog configuration, Canal setup, and provides a complete Java client example for real‑time data synchronization, cache refresh, and task dispatch scenarios.
Echo is a full‑stack open‑source Java community system built with Spring Boot, MyBatis, MySQL, Redis, Kafka and Elasticsearch, offering modules like posts, comments and notifications, and the article provides its core tech stack, development environment, local setup steps, deployment architecture, demo screenshots and source code access.
This article explains the MySQL Buffer Pool architecture, describing data pages, cache pages, the three page states, the various linked lists (Free, Flush, LRU), pre‑read mechanisms, hot‑cold separation, concurrency handling, and dynamic resizing using the chunk mechanism.
This article details a comprehensive performance testing workflow—including requirement analysis, script recording with BlazeMeter, data construction, iterative optimization, and final results—targeting a web application that must handle over 5,000 TPS with 2 million database records, highlighting bottlenecks in Redis, MySQL, and code logic.
This article explains how MySQL InnoDB achieves the ACID properties—Atomicity, Consistency, Isolation, Durability—by detailing the roles of undo logs, redo logs, buffer pool, lock mechanisms, and MVCC, and outlines transaction lifecycle commands, isolation levels, and consistency guarantees.
The article examines how to maintain data consistency between caches and databases during dual-write operations, categorizes data into three levels, evaluates four update strategies, and proposes solutions such as delayed double deletion, message‑queue compensation, and binlog‑driven cache synchronization.
The article details how a rapidly growing airline ticket order system was re‑architected by identifying performance bottlenecks, applying vertical and horizontal sharding, optimizing cache layers, implementing dual‑write mechanisms, and planning a phased migration to achieve ten‑fold QPS growth while reducing resource usage and operational risk.
This article reveals frequent SQL mistakes—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, premature filtering, and intermediate result push‑down—and provides concrete rewrite techniques that dramatically improve MySQL query performance.
The article explains what OceanBase BUFFER (Queuing) tables are, why frequent bulk updates cause performance degradation, and provides three optimization methods—including binding execution plans, manual data transfer, and setting table_mode='queuing'—with concrete MySQL and Oracle examples and query scripts to monitor merges.
This article explains how to design and implement a real‑time, multi‑tenant site search system by migrating data from MySQL to Elasticsearch using Kafka and MySQL binlog, covering architecture decisions, data schema, Docker deployment, and core SpringBoot/Kotlin code for event collection and indexing.
This article explains the MySQL sql_mode setting, details each of its ten modes—including STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and ONLY_FULL_GROUP_BY—provides a command to view the current mode, and also includes a brief promotion for a PHP training class.
This article provides a comprehensive overview of MySQL 8.0 enhancements, covering performance improvements such as contention‑aware scheduling and hash joins, security upgrades including authentication plugins and password policies, optimizer refinements, and various other features like persistent variables, GIS support, and binlog expiration handling.
This article explains two methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers with a UDF function and parsing MySQL binlog streams—detailing their processes, advantages, limitations, and related open‑source tools like Canal.
This article compiles a detailed interview guide covering TCP/UDP differences, three‑way handshake and four‑way termination, flow and congestion control, select/poll/epoll mechanisms, zero‑copy techniques, Redis advantages and persistence, MySQL MVCC and repeatable‑read semantics, as well as core C++ topics and common algorithm questions.
MySQL 8.0 introduces numerous enhancements including InnoDB engine defaults, atomic DDL, instant column addition, CTE support, UTF8MB4 default charset, Clone plugin, resource groups, role management, multi-valued and functional indexes, invisible indexes, descending indexes, SET_VAR syntax, persistent parameters, window functions, online DDL, and improved backup and binlog capabilities.
This article explains common scenarios that cause MySQL (and related services) to consume 900% CPU, outlines step‑by‑step diagnosis using tools like top and show processlist, and provides practical remediation such as killing offending threads, adding missing indexes, disabling slow‑log, and leveraging Redis caching to bring CPU usage down to acceptable levels.
While containers and Docker dominate modern infrastructure, this article critically examines whether MySQL should be containerized, highlighting data safety, performance, statefulness, and resource isolation concerns, and offering practical strategies such as separating data from the program, using lightweight or distributed databases, and appropriate deployment choices.
Upgrading MySQL 5.6/5.7 databases to 8.0 can introduce charset mismatches between existing utf8 (utf8mb3) tables and newly created utf8mb4 tables, causing index loss and performance degradation that can be resolved by unifying the character set across all tables.
This guide walks through the background of MySQL read/write splitting, records troubleshooting steps for an unexpected slave crash, explains the two splitting models, and provides detailed commands and configuration examples for using Keepalived to ensure high availability of the slave node.
The article explains how to process massive MySQL data sets—covering data migration, export, and batch handling—by comparing regular pagination, streaming queries using server‑side cursors, and cursor‑based fetchSize techniques, and provides concrete MyBatis code examples for each approach.
This article examines how MySQL decides whether to use an index or perform a full table scan for COUNT(*) and other queries, explains the IO and CPU cost model, demonstrates the calculations with a sample table, and shows how optimizer trace can reveal mismatches between estimated and actual performance.
This article explains the need for database sharding as applications scale, describes horizontal and vertical partitioning techniques, compares popular sharding middleware, and offers practical guidance on choosing and deploying sharding solutions for different workloads.
This article explains how to assess MySQL table size, identify performance issues caused by massive data volumes, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—along with detailed SQL examples and considerations for implementation.
This article explains the concepts of database sharding, compares vertical and horizontal partitioning, discusses ID‑range and modulo sharding methods, analyzes the read‑amplification problem caused by non‑sharding keys, and presents practical solutions such as auxiliary index tables, Elasticsearch integration, and TiDB adoption.
This article explains two technical solutions for keeping MySQL data in sync with Redis cache—using a MySQL trigger with a UDF function and parsing MySQL binlog events—while also reviewing the Canal open‑source tool and discussing their trade‑offs and implementation details.
The article examines why running MySQL in Docker containers often leads to data‑security, performance, state, and resource‑isolation problems, outlines practical mitigation strategies, and identifies scenarios where containerizing MySQL can still be viable.
The article explains how a MySQL backup using xtrabackup failed due to two concurrently running cron daemons launching the same backup script, resulting in corrupted compressed files, and provides practical solutions such as using flock, regular restore drills, and monitoring crond processes.
This article explains common scenarios that cause CPU usage to soar above 200% in production, outlines step‑by‑step diagnosis and remediation for MySQL and Java processes, and shares real‑world case studies with command‑line tools, indexing, caching, and code adjustments to bring CPU load back to normal levels.
This article analyses how MySQL 5.7 InnoDB processes COUNT(*) versus COUNT(1), COUNT(id) and COUNT on indexed or non‑indexed columns, showing execution‑plan differences across various index configurations, explaining why count(*) can be unexpectedly slower and offering practical guidance for query optimization.
When upgrading MySQL 5.7 tables that use utf8 or utf8mb3 to MySQL 8.0, mismatched character sets and collations can cause index loss and inefficient joins, but converting all tables to utf8mb4 and adjusting the driver table can restore optimal query performance, as demonstrated with detailed examples and profiling results.
Database sharding—splitting data across multiple databases and tables—is essential as applications scale, and this article explains the motivations, differences between sharding and partitioning, horizontal and vertical splits, common middleware options like Cobar, TDDL, Atlas, ShardingSphere, and Mycat, and how to choose the right solution.
This article explains what a database transaction is, details the ACID properties, compares MySQL's four isolation levels, clarifies why Repeatable Read is the default due to binlog formats and consistency, examines concurrency anomalies of each level, and provides practical guidance on handling large transactions, optimizing performance, and understanding InnoDB's MVCC implementation.
This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.
This article analyzes why MyBatis‑Plus's default saveBatch method does not achieve true batch insertion, explains how to enable JDBC batch mode with rewriteBatchedStatements, and demonstrates creating a custom SQL injector (InsertBatchSomeColumn) to perform real batch inserts, including performance test results showing significant speed improvements.
This article explains how pressing Ctrl+C in the MySQL client triggers a KILL QUERY command, details the server's multi‑step handling—including thread coordination, flag setting, and transaction rollback—and clarifies differences when autocommit is on or off.
In this interview, ICBC senior manager Wei Yadong shares the bank's challenges with massive data, the five‑point criteria for database selection, the DevOps‑driven MySQL governance framework, evolving security demands, future database trends for finance, and practical advice for database professionals.
This article presents nine useful Bash scripts that automate common Linux system administration tasks such as DOS attack mitigation, email alerts, MySQL backups, Nginx log management, network traffic monitoring, server initialization, and multi‑host disk usage checks.
This article compares MySQL's DISTINCT and GROUP BY clauses, explaining how index availability, implicit sorting, and MySQL version differences affect their execution speed, and provides practical guidance on choosing the right clause for optimal query performance.
This article explores web pagination across database, backend, and frontend layers, detailing MySQL LIMIT syntax, Java pagination implementations with PageHelper and Spring Data JDBC, and UI approaches using Thymeleaf and Element UI, providing code examples and comparative insights for developers.
This article explains OceanBase's three tenant deletion approaches—normal, delayed, and immediate—detailing the DROP TENANT syntax, recycle‑bin behavior, and providing step‑by‑step SQL examples for creating, dropping, restoring, and permanently removing tenants.
The article explains the classic cache consistency problem between MySQL and Redis, analyzes how inconsistencies arise, and details four cache‑update design patterns—delete‑then‑update, cache‑aside, read/write‑through, and write‑behind—highlighting their workflows, advantages, and drawbacks.
The article explains how to prevent OutOfMemoryError when exporting massive MySQL datasets by streaming records with JPA or MyBatis, writing them directly to CSV files, and demonstrates significant memory savings compared to traditional batch export methods.
This article documents a MySQL read‑write splitting architecture, investigates an unexpected slave‑node crash, and provides a step‑by‑step guide to using Keepalived for monitoring and automatically restarting the MySQL container to achieve high availability of the replica.
This article presents a comprehensive set of MySQL performance guidelines, including avoiding SELECT *, preferring specific columns, eliminating OR in WHERE clauses, using numeric types, choosing VARCHAR over CHAR, applying proper JOINs, leveraging LIMIT, UNION ALL, composite indexes, and other practical tips to improve query efficiency and resource usage.
This article compiles fifteen typical scenarios where MySQL indexes become ineffective—covering composite index left‑most matching, SELECT *, arithmetic on indexed columns, functions, LIKE patterns, type conversion, OR/AND logic, range queries, ORDER BY, and optimizer decisions—providing example SQL, EXPLAIN output, and practical tips to help developers prevent costly full‑table scans.
This article walks through a real‑world incident where an update to a MySQL row was not visible to a subsequent read, explains how SkyWalking tracing revealed the exact timing of binlog writing, MQ delivery, and query execution, and offers practical mitigation strategies.
This article explains how to add unique indexes to large MySQL tables using Online DDL, pt‑osc, and gh‑ost, compares their advantages and drawbacks, outlines common data‑loss risks, and provides practical hook scripts and best‑practice recommendations to mitigate those risks.
This article details the background, migration options, and step‑by‑step automated procedures used by a large‑scale e‑commerce platform to safely move over 400 MySQL clusters, comparing expansion‑plus‑master‑slave switching with cascading replication and explaining the chosen fast, reliable solution.
This article explains why large MySQL tables cause slow queries, analyzes table size, disk usage, and instance capacity, and presents three practical solutions—table partitioning, database sharding (horizontal/vertical), and hot‑cold data archiving—helping developers choose the appropriate strategy for their workloads.
This article walks through a hands‑on process for detecting problematic SQL statements, interpreting execution plans, and applying concrete optimizations such as index adjustments and data‑type fixes to dramatically improve query performance, illustrated with a real MySQL case study.
This article explains how to identify slow SQL statements, analyze execution plans with EXPLAIN, use profiling and optimizer trace, and apply practical solutions such as index optimization, query rewriting, and alternative storage, illustrated with ten real‑world MySQL case studies.
After a month of testing a MySQL dual‑master high‑availability setup, the team encountered numerous replication pitfalls and decided to migrate to a simpler master‑slave architecture, detailing the reasons, differences, and a concrete manual conversion procedure.
To efficiently process massive MySQL result sets without exhausting JVM memory, this article explains three query strategies—regular pagination, stream-based retrieval using MyBatis cursors, and cursor-based fetching with configurable fetchSize—detailing their implementations, advantages, and practical considerations.
This article explains how to prepare and tune MySQL SQL statements before execution, covering logical and physical optimization, table and view analysis, join‑key checks, query rewriting, and execution‑plan evaluation to achieve faster, more efficient database queries.
This article explains how InnoDB uses undo segment states for crash‑safe recovery, details the XA transaction protocol, describes bugs in earlier MySQL versions, and shows how MySQL 8.0.30 introduces a new undo state and revised prepare order to make external XA transactions crash‑safe, including code examples and recovery steps.
This article details a comprehensive approach to optimizing a large MySQL table, including slow‑query analysis with pt‑query‑digest, index redesign, backup and restore using mydumper, online DDL versus pt‑osc, and batch deletion techniques to reduce latency and storage fragmentation.
This article introduces the open‑source sync‑diff‑inspector tool, explains how to download and install it, and provides detailed configuration and command‑line examples for schema‑level, table‑level, and range‑based data validation between MySQL instances, including automatic SQL repair generation and common troubleshooting tips.
This article explains InnoDB’s clustered and secondary index structures, why secondary index leaf nodes store primary key values, how lookups trigger “row‑lookup” (回表) operations, and how covering indexes can eliminate those extra scans, illustrated with MySQL examples.
This article recounts a real‑world investigation of a MySQL high‑availability outage where HikariCP connections appeared to dead‑lock, detailing the architecture, observed symptoms, code analysis, thread‑dump findings, and the final fix of adding socket timeout parameters to the JDBC URL.
This guide explains how to decouple business logic in a SpringBoot application by using Canal to listen to MySQL binlog changes, forwarding those events through RabbitMQ, and processing them with a Java client to record both new and old data for insert, update, and delete operations.
Ctrip evaluated and extended OceanBase Migration Assessment tools, built a one‑click migration workflow, implemented comprehensive monitoring and automatic fault‑diagnosis pipelines, and addressed compatibility challenges such as .NET charset issues and Druid parser errors, ultimately achieving a smooth MySQL‑to‑OceanBase transition.
The article advises against using MySQL subqueries and joins for large datasets, recommending single‑table queries merged in application code, and outlines the benefits, scenarios, drawbacks, and alternatives for application‑level data association while also noting when joins can still be useful.
This article compiles frequent Flink job submission problems on YARN—including WordCount jar errors, HBase dependency conflicts, MySQL timeout, checkpoint restoration failures, parallelism limits, and unexpected container termination—provides root‑cause analysis and step‑by‑step remediation instructions.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause MySQL Next‑Key lock deadlocks, explains the underlying gap‑lock and row‑lock mechanisms, demonstrates practical experiments, and proposes strategies such as postponing locks or using primary‑key updates to avoid such deadlocks.
The article describes how a large‑scale membership system serving over a billion users achieves high performance and availability through dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster designs, Redis caching with distributed locks, and a seamless migration from SQL Server to sharded MySQL, while also detailing operational safeguards and fine‑grained flow‑control strategies.
This article explains how to assess MySQL table size, why massive tables degrade performance due to B+‑tree depth, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—along with guidance on selecting the appropriate approach.
This article explains MySQL's file‑sort mechanism in depth, covering the sort buffer, handling of long sort keys, three sort modes, priority‑queue and read‑rnd‑buffer optimizations, internal vs. external sorting, descending sort implementation, and how to inspect details with optimizer trace.
This article details the background, migration plan selection, and step‑by‑step procedures—including pre‑building cascades, service pause, automated batch operations, cluster tiering, pre‑ and post‑checks, and gray‑scale validation—to achieve a fast, stable MySQL data‑center migration for a large‑scale production environment.
This article examines how common business operations using SELECT ... FOR UPDATE and INSERT can cause Next‑Key lock deadlocks in MySQL under repeatable‑read isolation, demonstrates the locking behavior with practical SQL examples, and proposes strategies such as post‑locking and primary‑key updates to prevent such deadlocks.
Discover 18 essential MySQL 8.0 management enhancements—including persistent global variables, admin IP/port settings, minimal installation packages, resource groups, read‑only databases, improved SHOW PROCESSLIST, instant DDL, faster DROP/TRUNCATE, OS thread visibility, connection memory limits, enriched slow‑log details, backup options, Clone plugin, upgrade process, client defaults, and RESTART command usage.
This article examines eight frequent SQL anti‑patterns—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, premature data reduction, and intermediate result handling—explaining why they degrade performance and providing concrete rewrite strategies and code examples to dramatically speed up queries.
This guide introduces Sequel Pro, explains how to configure Standard, Socket, or SSH connections with required fields, describes the main interface modules for browsing schemas, tables, relationships, triggers, and running queries, and highlights useful shortcuts for efficient MySQL management.
This comprehensive guide outlines 46 practical MySQL optimization rules—from understanding the query execution flow and naming conventions to index design, join strategies, pagination techniques, and safe use of data types—providing concrete examples and code snippets that help developers dramatically improve database performance and reliability.
This 2023 comparison examines MySQL and PostgreSQL across history, features, performance, scalability, cost, and migration trends, helping developers decide which relational database best fits their specific application requirements.
In MySQL 5.7 replication, disabling unique_checks can cause duplicate‑key errors on a unique index that is not a primary key, leading to replication stalls, and this guide explains the root cause, temporary and permanent fixes, and step‑by‑step reproduction instructions.
The article explains why an UPDATE on the same row in MySQL can block in one transaction but not in another, by analyzing the types of row‑level locks (record, next‑key, gap) acquired during SELECT … FOR UPDATE, normal updates, and updates that modify primary‑key values, illustrating the lock behavior with B+‑tree examples.
This article explains why MySQL needs locking, details every InnoDB lock type—including shared, exclusive, intention, record, gap, next‑key, insert‑intention and auto‑increment locks—shows how they interact, and provides practical guidance for avoiding deadlocks and safely updating rows.
This article explains the differences between DISTINCT and GROUP BY in MySQL, showing that with an index they have similar efficiency, while without an index DISTINCT is usually faster because GROUP BY may trigger sorting and temporary tables, and it provides syntax, examples, and recommendations.
This guide shows how to decouple change‑recording logic in a SpringBoot application by using Alibaba Canal to listen to MySQL binlog events, forwarding them through RabbitMQ, and persisting both new and old row data for insert and update operations.
This article explains how DBAs can improve MySQL query performance by applying logical and physical optimizations, analyzing table structures, indexes, and execution plans, and provides a step‑by‑step checklist for diagnosing and rewriting slow or poorly optimized SQL statements.
This article explains how to determine the optimal order of columns in MySQL composite indexes by analyzing B+Tree structure, index selectivity, and real‑world query performance, providing practical examples and SQL demonstrations to help developers create more efficient indexes.
This article describes how a membership platform serving over ten billion users achieves high performance and fault tolerance through a dual‑center Elasticsearch cluster, traffic‑isolated three‑cluster ES design, Redis multi‑center caching, and a seamless migration from SQL Server to a partitioned MySQL architecture, while detailing operational safeguards and fine‑grained flow‑control strategies.
The article analyzes why an identical MySQL query runs faster in a QA environment than in DEV, examines execution‑plan differences caused by table driving choices, optimizer settings, and unnecessary conditions, and demonstrates how rewriting the SQL and adjusting hints can achieve a stable, efficient plan.
This article explains the fundamentals of MySQL indexes, compares them to a library catalog, details various index types and their physical implementations, demonstrates how indexes dramatically speed up queries on large tables, and provides practical SQL syntax for creating and managing indexes.
