This article demonstrates, with step‑by‑step examples, how to capture MySQL changes via Flink CDC and stream them in real time into Apache Doris using the Doris Flink Connector, covering CDC concepts, connector features, environment setup, SQL client usage, and data verification.
This article debunks the common belief that MySQL VARCHAR can store up to 65535 characters, showing how the true limit depends on the chosen character set, NULLability, column count, and row‑format details, and explains how oversized values are stored or require TEXT/BLOB types.
This article explains why MySQL master‑slave replication can become delayed, outlines the differences between DDL and DML, shows how to diagnose lag with show slave status, and provides architectural, hardware, and configuration solutions—including semi‑synchronous replication and tuning parameters—to reduce latency.
To import one billion 1 KB log records into MySQL efficiently, the article examines data size constraints, B‑tree index limits, batch insertion strategies, storage engine choices, file‑reading techniques, task coordination with Redis, Redisson semaphores, and distributed lock handling to ensure ordered, reliable, high‑throughput loading.
Running ANALYZE TABLE on MySQL, Percona Server, or MariaDB can block queries in older versions, but from MySQL 8.0.24, Percona Server 5.6.38+, 5.7.20+, and MariaDB 10.5.4+, the operation is safe and no longer causes disruptive locks.
This article explains MySQL's fault‑tolerance mechanisms, covering data persistence, storage engines, the binary, redo, and undo logs, WAL, two‑phase commit, and step‑by‑step recovery procedures for accidental data loss or crashes.
This article explains why MySQL's LIMIT offset,size becomes slower as the offset grows, compares it with LIMIT size, details the internal execution using primary and secondary indexes, discusses deep pagination issues, and presents practical optimization techniques to improve query performance.
This article examines eight frequent MySQL performance problems—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, early limiting, and intermediate result push‑down—and provides concrete rewrite techniques and example code that dramatically reduce execution time.
This article explains why database sharding is needed, the problems caused by oversized databases and tables, and how to apply vertical and horizontal splitting using MyCat, including configuration examples, splitting strategies, and testing procedures.
This article demonstrates how to set up the open‑source delay-job system, configure its database, start the server, and integrate a client via HTTP to register and trigger delayed tasks, providing a concise end‑to‑end backend scheduling solution.
The article explains MySQL’s new versioning model, introducing Innovation and Long‑Term Support (LTS) releases, detailing transition timelines, feature deprecation, upgrade and downgrade strategies, and the role of MySQL HeatWave, while also providing links to download and community resources.
This article compares MySQL and MongoDB, outlining their core concepts, shared features such as open‑source licensing and indexing, and key differences in data model, scalability, performance, flexibility, and security, while summarizing pros and cons and recommending suitable application scenarios for each database.
This guide walks through setting up a Percona XtraDB Cluster (PXC) version 5.7.43 on three Docker‑installed machines, configuring hostnames, loading images, creating a Swarm overlay network, deploying the cluster, adjusting MySQL settings, handling common failures, and adding load balancing with Nginx or HAProxy.
This tutorial demonstrates how to enable and use MySQL 8.2 read/write splitting with MySQL‑Connector/Python in an InnoDB Cluster, covering cluster status inspection, autocommit configuration, routing attributes, DML testing, and transaction handling.
This article explains MySQL InnoDB locking mechanisms, detailing the twelve lock types, their granularity, compatibility rules, how to view lock information, and how different SQL statements acquire locks under various isolation levels, with practical test cases and code examples.
This article explains the differences between Read Committed (RC) and Repeatable Read (RR) isolation levels in MySQL, detailing how snapshot reads work, why other levels avoid them, and illustrating their behavior with concrete transaction examples.
This article explains why a DELETE statement with a table alias fails in MySQL 5.7, shows the correct syntax without alias, compares the DELETE syntax of MySQL 5.7 and 8.0, and concludes that alias support in single‑table DELETE was added starting with version 8.0.16.
This guide explains how to implement read‑write separation in Spring Boot applications by configuring master and slave databases, creating dynamic routing data sources, using custom annotations and AOP, integrating HikariCP and MyBatis, and handling thread‑local context for optimal performance.
This guide presents ten practical MySQL performance optimization methods—including index creation, query rewriting, limiting result sets, avoiding SELECT *, reducing joins, using proper data types, caching with Redis, and server configuration tweaks—each illustrated with concrete SQL code examples to help you speed up your database operations.
The article analyzes a MySQL binlog parsing failure caused by a large transaction that exhausts temporary disk space, investigates the root cause by examining MySQL source code and configuration, and provides a solution by adjusting the TMPDIR environment variable without restarting MySQL.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—by creating a simple table, inserting test data, and running step‑by‑step SQL scripts that illustrate the behavior of each level, including phenomena such as dirty reads, non‑repeatable reads, phantom reads, and locking.
This tutorial demonstrates how to enable MySQL 8.2's transparent read/write splitting with InnoDB Cluster, configure MySQL Router, use MySQL Connector/Python for read‑only and read‑write queries, test DML statements, and manage transactions across primary and secondary nodes.
This article presents a comprehensive analysis of the shortcomings of an existing database backup solution and details the architecture, high‑performance backup and restore mechanisms, dynamic throttling, storage abstraction, and experimental results of a newly designed, scalable backup‑recovery platform for MySQL databases.
Through a series of local experiments on MySQL tables with varying data volumes, this article analyzes the performance trade‑offs between JOIN queries and IN‑list queries, detailing execution plans, PHP benchmarking, and the impact of large IN lists on query speed and reliability.
This article demonstrates how to dramatically improve MySQL query performance by analyzing execution plans, adding appropriate single‑column and multi‑column indexes, rewriting subqueries as joins, and applying covering indexes, showing step‑by‑step measurements and visual explanations.
This article introduces a generic asynchronous processing SDK for Java Spring applications, covering its purpose, advantages, underlying principles, component choices such as Kafka, XXL‑Job, MySQL, design patterns, database schema, configuration via Apollo, usage instructions, safety considerations, and provides code examples and a GitHub repository link.
This article explains MySQL transaction fundamentals, the necessity of ACID properties, details the four isolation levels and their impact on phenomena like dirty reads, non‑repeatable reads, and phantom reads, and provides practical SQL examples and strategies for ensuring data consistency in high‑concurrency environments.
This article explains the benefits of using short‑link services for marketing, sharing, and SMS optimization, and provides step‑by‑step Flask and Django tutorials—including installation commands, MySQL integration, URL generation, routing, and database models—to create a functional short URL platform.
A real‑world MySQL case study shows how using INSERT INTO SELECT for large data migrations can trigger full‑table scans, lock contention, and massive data loss, emphasizing the need for proper indexing and careful testing before deployment.
This article explains MySQL index concepts, advantages, and pitfalls, walks through EXPLAIN output fields, offers practical tips for avoiding index invalidation and ORDER BY inefficiencies, and presents six real‑world slow‑query cases with step‑by‑step optimization solutions.
This article explains MySQL's transaction implementation, covering write‑ahead logging, transaction IDs, multi‑version concurrency control, buffer pool management, and the roles of redo and undo logs in ensuring durability and atomicity.
This article explains how the update_time column in the information_schema.tables view is populated, why its values can be unreliable, and details the mechanisms of active and passive persistence of table statistics in MySQL 8.0, including source‑code walkthroughs.
This article explains how MySQL's SELECT … FOR UPDATE statement applies different lock types—row, gap, or table—depending on whether the WHERE clause uses primary keys, unique indexes, regular indexes, range queries, or empty results, and demonstrates each case with practical SQL examples and screenshots.
This article walks through the complete lifecycle of a MySQL query—from the client request, through connection handling, parsing, optimization, execution, and storage engine processing—illustrating each component with diagrams, code snippets, and a detailed analysis of execution order for both simple and join queries.
This article examines why MySQL permits creating multiple identical indexes on the same column, demonstrates the behavior through several tests, compares it with Oracle's stricter restrictions, and explains how the optimizer selects indexes, highlighting practical implications for database design.
This article explains MySQL's locking mechanisms, covering when locks are applied, how they are acquired—including explicit and implicit locks—and guidelines on when to use or avoid them, with examples of lock behavior under different isolation levels and query scenarios.
An urgent overnight incident revealed a MySQL server’s CPU soaring to 400% due to poorly written queries, prompting a detailed analysis of execution plans, identification of costly operations like filesort and temporary tables, and concrete recommendations for query and team improvements.
This article explains why master‑slave lag occurs in MySQL, describes the evolution of parallel replication schemes—including group‑commit, Commit‑Parent‑Based, Lock‑Based, and WRITESET approaches—shows benchmark results, and provides practical configuration steps to enable high‑performance parallel replication.
This article explains why asynchronous processing is needed in evolving systems, outlines the goals of guaranteeing execution without blocking the main flow, and walks through a complete SDK design that uses Spring transaction events, Kafka, XXL‑Job, MySQL, and a Vue UI, including configuration, code snippets, and deployment details.
This article examines MySQL's various lock types—including shared, exclusive, gap, next‑key, and insert‑intention locks—illustrates how they interact during concurrent transactions, analyzes a real‑world deadlock incident, and provides recommendations for preventing similar concurrency issues in high‑traffic systems.
This article explains why MySQL backups are essential, defines RTO and RPO, compares physical and logical backup methods, describes snapshot, binary‑log, incremental and differential strategies, and provides best‑practice recommendations and example commands for reliable backup and recovery.
The AIKOS community weekly newsletter shares the SQLE 3.0 release, recent development work, upcoming plans, curated MySQL and OceanBase technical articles, industry highlights, and announcements for future events, providing valuable insights for database practitioners and open‑source contributors.
This comprehensive guide explores MySQL’s architecture, explains how queries are parsed and executed, and provides detailed techniques for optimizing SQL performance through effective indexing, understanding EXPLAIN output, and avoiding common pitfalls that cause index misuse and slow query execution.
The article explains that while MySQL foreign keys ensure data consistency and integrity, they introduce performance overhead, lock contention, and scalability issues—especially in high‑concurrency and sharded environments—leading many large internet firms to forego them in favor of application‑level solutions.
This article explains the concept of a local message table, shows how it can be used to achieve reliable distributed transaction consistency with MQ, and provides complete MySQL DDL and Java service code along with a retry mechanism and analysis of failure scenarios.
This article explains what MySQL master‑slave replication lag is, walks through the replication workflow, identifies the main technical reasons for delay such as single‑threaded replay and lock contention, and provides concrete configuration and architectural solutions to reduce or eliminate the lag.
This article analyzes the performance problems of LIMIT‑based pagination on massive MySQL tables and presents three progressively more efficient solutions—including a simple LIMIT approach, a tag‑record method using the last primary key, and a range‑limit method with cached min‑ID—along with best‑practice indexing recommendations to keep query latency in the tens of milliseconds even for tables containing billions of rows.
This article explains the fundamentals of MySQL locking—including explicit and implicit locks, transaction isolation levels, next‑key and gap locks, and practical session‑based examples—so developers can decide when to apply pessimistic locks and when to prefer safer alternatives.
This article analyzes the popularity, success metrics, distributed versus centralized architectures, TPC‑C benchmark relevance, and a detailed feature comparison between MySQL and PostgreSQL, concluding which system currently leads in real‑world OLTP deployments.
This article explains what a MySQL deadlock is, shows a concrete example using two conflicting transactions on a simple test table, demonstrates how to detect the deadlock with InnoDB status output, and provides practical tips for preventing deadlocks in production systems.
This article examines MySQL's comprehensive lock taxonomy, starting from common row and table locks, then delving into lesser‑known mechanisms such as global locks, intention locks, auto‑increment locks, and Latch, while explaining their commands, use‑cases, and performance implications for developers and DBAs.
This article provides a comprehensive technical analysis of how MySQL processes ORDER BY and LIMIT clauses during pagination queries, detailing index utilization, filesort mechanisms, memory-based sorting algorithms, and practical optimization strategies for resolving deep pagination performance bottlenecks.
This article explains MySQL's basic architecture, demonstrates how to view and set storage engines, and provides comprehensive guidance on SQL optimization methods, index types, creation, usage, and performance analysis using EXPLAIN, helping readers improve query efficiency and avoid common pitfalls.
This article explains MySQL InnoDB's concurrency control mechanisms—including read‑write locks, transaction isolation levels, MVCC, redo/undo logs, and rollback segments—and shows how they work together to ensure data consistency while maximizing read/write performance in high‑traffic environments.
This article explains the circumstances under which MySQL creates internal temporary tables, illustrating the behavior with UNION, UNION ALL, GROUP BY, various hints, and additional query patterns, and provides practical examples and execution plans to help developers understand and optimise their queries.
This article explains why full‑text search is needed, how InnoDB implements it with inverted indexes, the syntax for creating and using full‑text indexes, the different query modes (natural language, boolean, query expansion), and how to delete full‑text indexes in MySQL.
This article explains how to tune MySQL's InnoDB settings—including buffer pool size, log file configuration, I/O threads, flush behavior, and timeout values—by providing detailed explanations and exact configuration snippets to improve performance and resource usage.
Despite PostgreSQL’s rapid global growth, MySQL remains the leading database in China, a gap explained by early Windows support, the entrenched LAMP stack, lower entry barriers, extensive tooling, and strong backing from major tech firms, as illustrated by trend data and ecosystem analysis.
The article explains MySQL’s explicit and implicit lock mechanisms—including SELECT … FOR UPDATE, metadata, row, gap, and next‑key locks—how isolation levels affect them, and offers practical guidelines such as using optimistic locking, indexing locked columns, and avoiding long transactions to prevent phantom reads, deadlocks, and performance issues.
MySQL 8.2, released on October 25, 2023, is the second Innovation version that brings numerous community contributions, new client options, InnoDB fixes, optimizer improvements, Clone plugin updates, and documentation changes, while inviting developers to contribute via GitHub or MySQL Bugs.
This article sets up a MySQL 5.7 environment, runs a series of SELECT FOR UPDATE queries on primary keys, unique indexes, normal indexes, range scans and non‑indexed columns, then analyzes the resulting lock types—row, gap, next‑key and table locks—and distills practical locking rules.
The article recounts three real‑world pagination bugs—batch‑processing loops, misuse of MyBatis PageHelper, and nondeterministic results from identical ORDER BY values—explaining why they occur, how they manifest in production, and concrete fixes to avoid data loss or endless loops.
This article explains how MySQL establishes connections, the supported communication protocols and message formats, the lexical and syntactic parsing process, query optimization and execution plan generation, the role of different storage engines, and how the execution engine uses the plan to operate on the storage layer.
This article explains MySQL InnoDB's various buffers—including the buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their purposes, internal mechanisms such as LRU variants, configuration options, and how they improve performance and ensure data integrity.
This article presents a collection of SQL statements—including SELECT, DELETE, DESCRIBE, and JOIN queries—targeting various tables in the idc_evaluating database, accompanied by detailed English explanations of their purpose, syntax, and result set structure.
This article outlines the typical reasons MySQL databases crash—such as hardware failures, software issues, external attacks, and poor design—and provides practical remedies including regular backups, using myisamchk for repair, leveraging binary logs, optimizing schemas, and upgrading to newer MySQL versions.
This article explains MySQL's various lock mechanisms—including shared (S) and exclusive (X) locks, intention locks, record, gap, next‑key, and insert‑intention locks—illustrates their behavior with SQL examples, analyzes a concurrent insert deadlock scenario, and offers practical mitigation strategies.
While Docker simplifies many development tasks, this article examines five key drawbacks of running MySQL in containers—including data loss risks, performance bottlenecks, statefulness issues, limited resource isolation, and unsuitable use cases—offering guidance on when to avoid or adapt containerization for databases.
This article reviews ten open‑source content management systems—including Joomla!, Drupal, Mambo, DEDECMS, phpCMS, EmpireCMS, php168, HBcms, SupSite, and WordPress—highlighting their features, platform compatibility, strengths, and limitations to help individuals choose the most suitable CMS for building a personal website.
This article experimentally compares three Java‑based insertion strategies—MyBatis without transaction, plain JDBC with and without transaction, and JDBC batch processing—with and without transactions, measuring how they affect the time required to insert tens of millions of rows into MySQL.
This article explains how to evaluate MySQL table size, understand B+‑tree limitations, and improve query performance by using table partitioning, horizontal/vertical sharding, and hot‑cold data archiving, while also comparing the trade‑offs of each approach for practical selection.
This article explains the purpose, recommended settings, and tuning tips for MyISAM‑related MySQL variables such as key_buffer_size, read_buffer_size, read_rnd_buffer_size, bulk_insert_buffer_size, myisam_sort_buffer_size, myisam_max_sort_file_size, myisam_repair_threads, and myisam_recover, helping DBAs optimize indexing and query performance.
This article recounts the step‑by‑step evolution of a ride‑hailing order service’s data layer—from a single SQLServer database through SQL optimization, read‑write splitting, business‑domain sharding, caching and MQ integration, to a custom sharding component and smooth migration to MySQL—highlighting challenges, solutions, and lessons learned.
This article analyzes the constraints of loading one billion 1 KB log records from distributed storage into MySQL, evaluates single‑table limits, proposes batch‑insert, sharding, storage‑engine, file‑reading, and distributed‑task coordination techniques to achieve high‑speed, ordered, and reliable data ingestion.
This article explains why slow SQL queries become a major risk for service stability, outlines a step‑by‑step investigation process using EXPLAIN, and presents multiple real‑world case studies with code snippets and optimization principles to quickly locate and resolve performance bottlenecks.
When MySQL tables grow to millions of rows, insert and query latency increase, schema changes become costly, and irrelevant data bloats storage, so this guide evaluates table size, explains why B+‑tree depth hurts performance, and presents partitioning, sharding, and hot‑cold archiving solutions with practical SQL examples.
This article presents a detailed reference of MySQL server configuration options (my.cnf), explaining each setting such as storage engine, networking, replication, logging, cache sizes, and performance‑related parameters to help administrators fine‑tune their databases.
MySQL 8.2.0, released on October 25, 2023, introduces audit‑log cleanup scripts, FIDO‑based authentication, named‑parameter support, numerous deprecations, firewall reload scheduling, expanded SQL syntax options, and new client and server features while dropping outdated functions and variables.
This article explains the technical reasons why a MySQL single table should not exceed about 20 million rows, covering limits from auto‑increment primary keys, data‑page structures, B+‑tree storage calculations, and practical partitioning recommendations for large‑scale applications.
This article explains how a hidden non‑breaking space character copied from documentation caused the MySQL lower_case_table_names parameter to be unrecognizable, details the reproduction steps, and presents three methods (hexdump, od, editor) to detect and resolve such encoding issues.
This article explains MySQL InnoDB’s key buffering mechanisms—buffer pool, change buffer, log buffer, and doublewrite buffer—detailing their roles, internal structures, eviction policies, and how they improve performance and data durability while handling page reads, writes, and crash recovery.
This article explains the principles of disaster recovery for MySQL, covering RTO/RPO metrics, national backup level standards, common master‑slave topologies, a comparative analysis of high‑availability solutions, and a detailed three‑center multi‑region replication design with code patches to avoid replication loops.
The article explains mysql_sniffer, a MySQL‑protocol packet sniffer that captures queries in real time, details its benefits for MySQL 8.0 upgrades, shows how to interpret incompatibilities, provides full command‑line options, usage examples, testing procedures, and a GitHub download link.
This article explores common reasons MySQL queries become sluggish—beyond missing indexes—including connection limits, insufficient InnoDB buffer pool size, and query cache issues, and provides practical steps such as profiling, using FORCE INDEX, adjusting max_connections, enlarging the buffer pool, and tuning connection pools to improve performance.
MySQL 8.2 introduces transparent read/write splitting via MySQL Router, allowing automatic routing of read traffic to replica instances and write traffic to the primary source without any changes to the application, demonstrated with a simple InnoDB ReplicaSet deployment and configuration examples.
The newsletter announces the SQLE 3.0 release with new UI and demo environments, highlights the SQLE 2.2310.0‑pre3 update, reviews technical articles on MySQL cloning, OceanBase indexing, data replication, charset issues, and connection‑pool design, recaps recent conferences, and previews upcoming OB Cloud open class events.
The article explains what MySQL master‑slave replication lag is, analyzes why it occurs—especially during relay‑log replay and large DDL/DML operations—and provides concrete configuration, hardware, and architectural solutions to reduce or eliminate the delay.
MySQL 8.0.28 and earlier can deadlock in multi‑threaded slave replication when the internal commit sequence counter overflows after 2³¹ transactions, causing workers to stop waking and the replica to hang; the article explains the underlying slave_preserve_commit_order mechanism, the overflow bug, and a step‑by‑step reproduction method.
This article explains the concept of database transactions, the ACID properties, how MySQL implements transactions with InnoDB, how to manage autocommit, the essential transaction control statements, a practical example using savepoints, and the four isolation levels supported by MySQL.
This article explains why indexing long character columns can bloat MySQL indexes, introduces the concept of index selectivity, shows how to calculate the best prefix length with real‑world queries, and demonstrates performance differences between short and optimal index prefixes.
This guide explains why case‑sensitivity issues arise in databases, how naming conventions can prevent them, and compares the behavior of Snowflake, Databend, MySQL, and PostgreSQL for database, table, column names and string literals, complete with practical examples and a concise checklist.
This announcement details a book giveaway sponsored by Tsinghua University Press and presents a comprehensive SQL learning curriculum covering SQLite, MySQL, query fundamentals, subqueries, joins, and advanced database features, complete with micro‑lecture videos and hands‑on exercises.
This tutorial walks through creating an Ansible role that structures directories, defines configuration templates, sets variables, writes task files, and runs a playbook to install MySQL 8.0.32 on a Linux host, illustrating a complete automation workflow for database deployment.
This article systematically investigates how MySQL's SELECT FOR UPDATE behaves under different versions (5.7 and 8.0) and isolation levels (RR and RC), covering twenty scenarios that vary by index type and query range, and summarizes whether the statement acquires row‑level, gap, or table‑level locks.
This tutorial walks you through preparing a Kubernetes cluster, deploying MySQL and Typecho containers with detailed YAML configurations, creating the necessary services and ingress, testing the setup, and highlighting Kubernetes' high‑availability and auto‑scaling benefits for a reliable blog platform.
This article explains MySQL InnoDB's Multi-Version Concurrency Control (MVCC), covering its basic concepts, read/write scenarios, implicit fields, undo logs, read view mechanisms, transaction handling, and differences between RC and RR isolation levels, providing a comprehensive guide for developers.
This tutorial explains how to set up the environment, configure Elasticsearch and Logstash, create the necessary MySQL tables, and use a Logstash pipeline to continuously sync MySQL records into an Elasticsearch index, while also covering common pitfalls and troubleshooting steps.
This article provides a step‑by‑step walkthrough of MySQL’s execution pipeline—from the client connection and parsing, through optimization, execution, undo/redo logging, change buffer handling, and binlog recording—illustrated with diagrams and detailed explanations of each component.
This article explains how to safely run MySQL's CLONE INSTANCE command with the DATA DIRECTORY option on a replica that lacks enough disk space for both source and target data sets, offering step‑by‑step strategies, SQL examples, and a final recommendation.
This article reproduces MySQL's official LIMIT‑optimization example, shows how adding rows changes the ORDER BY + LIMIT result, explains the hidden priority‑queue mechanism revealed by optimizer_trace, and derives the exact row‑count threshold where the behavior switches.
