The article explains the limitations of standalone MySQL for large‑scale workloads, explores sharding, read/write separation, and middleware approaches, and argues that modern distributed databases and cloud‑native solutions are the sustainable path forward for data‑intensive applications.
This article breaks down MySQL's InnoDB storage engine, detailing where data files reside, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑tree indexes (clustered and secondary) are built and searched.
This article explains MySQL master‑slave replication, covering its basic concepts, the advantages of read‑write separation, data backup and high availability, the underlying binlog‑based mechanism, the four replication modes (full, asynchronous, semi‑synchronous, enhanced semi‑synchronous), common consistency challenges, and practical ways to mitigate them.
This article walks through installing Docker and Docker‑Compose, creating a project directory, writing a docker‑compose.yml that defines Redis, MySQL, Nginx, Java, and a file‑preview service, configuring each container, and using docker‑compose commands to build, start, monitor, and manage the full SpringBoot‑Vue application stack.
This comprehensive guide explores MySQL architecture, index types, query execution plans, and practical optimization techniques, offering detailed examples of creating, modifying, and analyzing indexes, avoiding common pitfalls, and improving query performance through proper use of EXPLAIN, composite indexes, and query rewriting.
This article explains why an UPDATE on a MySQL row that changes a regular column proceeds without blocking, while an UPDATE that modifies the primary‑key value gets blocked, by analyzing the locks set by preceding transactions, the B+‑tree index structure, and the delete‑plus‑insert rewrite performed by InnoDB.
This article explains MySQL row‑level locking by analyzing three transactions—A, B, and C—to show why an UPDATE on a primary‑key column can block while an UPDATE on a non‑indexed column proceeds without waiting.
This article explains how MySQL 8.0.32 introduces the @@explain_format system variable that allows administrators to set different execution‑plan output formats (tree, traditional, json, etc.) for individual users, and demonstrates the configuration steps with concrete SQL commands and examples.
This article explains common MySQL database performance optimization techniques, underlying storage architecture, index construction and usage guidelines, and presents several real‑world cases from an internet hospital project, helping developers understand optimization strategies, avoid hidden pitfalls, and improve query efficiency.
This article explains how DISTINCT and GROUP BY differ in MySQL, comparing their index usage, execution speed with and without indexes, the impact of implicit sorting in older versions, and why GROUP BY is often preferred for clearer semantics and advanced aggregations.
This article analyzes why MySQL 8.0 reports an illegal mix of collations when querying a view that joins tables with different character sets, demonstrates reproducing the issue, explains the role of the CONVERT function and default collation variables, and provides practical recommendations to avoid such errors.
This article explains why database performance monitoring becomes critical as data grows, outlines common optimization techniques, dives into MySQL's storage architecture and index structures, and shares three real‑world cases that illustrate how proper indexing and execution‑plan analysis can prevent severe latency and CPU spikes.
This weekly newsletter curates high‑quality MySQL and database articles, shares the latest SQLE release and development progress, and invites community feedback, providing a concise overview of recent open‑source database developments and upcoming plans.
This article explains the evolution of tree-based index structures—from basic binary search trees through AVL and red‑black trees to B‑trees and B+‑trees—highlighting their trade‑offs and why MySQL adopts B+‑trees for efficient disk‑based indexing.
This guide explains how to install DataX, set up MySQL environments, configure JSON job files, and run both full and incremental data synchronization between heterogeneous databases using DataX's Reader/Writer framework and job scheduling features.
This article explains MySQL transaction fundamentals, including the ACID properties, isolation levels, MVCC mechanics, transaction start methods, and practical examples that illustrate how different isolation levels affect data visibility and consistency.
This article provides a step‑by‑step tutorial on installing, configuring, and using Alibaba's open‑source DataX tool to perform both full and incremental data synchronization between MySQL databases on Linux, covering framework design, job architecture, JSON job files, and practical command‑line examples.
This article details a robust solution for exporting massive MySQL datasets—potentially millions of rows—to Excel by leveraging asynchronous processing with jobs or MQ, the memory‑efficient EasyExcel library, pagination, multi‑sheet handling, OSS storage, and WebSocket notifications, ensuring performance and usability.
This article analyzes the overselling problem in high‑concurrency flash‑sale scenarios, demonstrates seven concrete solutions—including improved locks, AOP locks, pessimistic and optimistic database locks, and queue‑based approaches using BlockingQueue and Disruptor—provides full SpringBoot code, JMeter test results, and practical recommendations for reliable stock reduction.
This free Cantonese-language course introduces PHP7 fundamentals, MySQL database connection, and simple website development using professional tools, empowering learners to build their own web projects, with all materials available online via the provided link.
This article demonstrates how creating a covering composite index on MySQL tables can reduce a 55‑second aggregation query on five million rows to about two seconds by eliminating the need for row lookups and fully utilizing index data.
This article explores four common approaches for synchronizing product data from MySQL to Elasticsearch in e‑commerce systems—synchronous dual write, asynchronous dual write with message queues, scheduled tasks, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations.
This article examines typical MySQL out‑of‑memory (OOM) situations in production, explains how to diagnose memory usage with Performance Schema and other tools, and presents a series of TDSQL‑C‑specific optimization techniques—including server‑parameter tuning, process‑list monitoring, cold‑page detection, buffer‑pool limits, and dynamic resizing—to mitigate OOM risks.
This article presents a collection of practical MySQL functions and SQL commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—explaining their purposes, usage patterns, and providing concrete example queries with results.
This article explores four common approaches for synchronizing product data from MySQL to Elasticsearch—including synchronous dual writes, asynchronous messaging, scheduled jobs, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations for e‑commerce search systems.
This article presents nine practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting subqueries as joins, handling mixed sorting, replacing EXISTS with joins, pushing conditions into subqueries, early range reduction, and using CTEs—to dramatically improve query execution time across common scenarios.
Processing massive datasets in MySQL can cause OOM and slow performance; this article explains when to use regular pagination, streaming queries, and cursor-based fetching, compares their trade‑offs, and provides MyBatis code examples with @Options and @ResultType to handle millions of rows efficiently.
This article examines four common approaches for synchronizing product data from MySQL to Elasticsearch—synchronous dual write, asynchronous dual write with message queues, scheduled batch jobs, and binlog‑based data subscription—detailing their advantages, drawbacks, and implementation considerations.
This article explains why MySQL count(*) queries can become a performance bottleneck on InnoDB tables and presents practical optimization techniques such as Redis caching, secondary caches, multithreading, reducing joins, and even switching to ClickHouse for massive datasets.
The article explains how single‑instance MySQL struggles with huge tables, resource‑starved databases, and high read traffic, and argues that sharding middleware is being replaced by modern distributed databases such as TiDB, OceanBase, Aurora, and PolarDB as the sustainable scaling solution.
This article explains how to install DataX, configure MySQL readers and writers, and execute both full and incremental data synchronization jobs between two MySQL instances, providing step‑by‑step commands, JSON job templates, and troubleshooting tips for large‑scale data transfers.
This article introduces Alibaba DataX, explains its architecture and role in offline heterogeneous data synchronization, provides step‑by‑step Linux installation, demonstrates full‑load and incremental MySQL‑to‑MySQL sync with JSON job templates, and shares practical tips for handling large data volumes.
This guide explains how to install, configure, and run a lightweight Redis Monitor that visualizes connection count, QPS, memory usage and replication delay, supports standalone, sentinel and cluster modes, and provides email and WeChat alerts via simple PHP scripts.
This article experimentally demonstrates how MySQL's SELECT … FOR UPDATE statement acquires various locks—IX, X, and gap locks—under RC (read‑committed) and RR (repeatable‑read) isolation levels with unique, primary, normal, and missing indexes, revealing when row‑level versus table‑level locking occurs.
This article examines eight common SQL pitfalls—from inefficient LIMIT usage and implicit type conversion to suboptimal joins and condition push‑down—offering concrete rewrite examples and performance‑boosting techniques that can shrink query times from seconds to milliseconds.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the B+Tree storage mechanism compared with B‑Tree, and the distinction between clustered and non‑clustered (auxiliary) indexes, providing practical insights for database performance optimization.
This article provides a comprehensive technical walkthrough of MySQL‑8.0.22 InnoDB's buffer pool, detailing its physical and logical structures, page management algorithms, fast read/write optimizations, change buffer, adaptive hash index, and the complete page‑read call stack, complete with code excerpts and diagrams.
This article shares practical experience comparing ClickHouse and StarRocks as real‑time data warehouses, outlines the project requirements, evaluates each system's suitability for log‑type and business‑type data, and describes CDC‑based synchronization methods from MySQL to both platforms.
This article presents a series of MySQL 5.7.26 experiments that reveal how record locks on non‑indexed columns are applied to the primary (clustered) index under different isolation levels, how full‑table scans affect locking behavior, and why updates and inserts may or may not be blocked.
This article examines MySQL’s transition from dual‑master to master‑slave replication, highlights the complexities of position‑based syncing, introduces GTID as a more reliable alternative, and provides practical steps to enable GTID and accurately monitor replication lag using seconds_behind_master, binlog positions, and GTID sets.
This article experimentally verifies how SELECT FOR UPDATE behaves under different MySQL versions, isolation levels, and index conditions, revealing when it acquires row‑level, gap, or table‑level locks across 20 distinct scenarios.
This article introduces four open‑source MySQL performance‑tuning utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—explains how to download, install, run them, and interpret their reports to identify configuration issues and slow‑query problems.
This article details the requirements analysis, design approach, database schema, and step‑by‑step implementation of a follow/unfollow microservice using MySQL for persistence and Redis Sets for efficient set operations, including Maven dependencies, Spring Boot configuration, RedisTemplate setup, service, controller, and testing of common‑follow queries.
This weekly community bulletin curates high‑quality technical shares and release notes from the MySQL and DBLE ecosystems, highlighting new features, bug fixes, and upcoming development plans while inviting reader feedback and questions about the middleware tools.
This article walks through the complete design and implementation of a friend‑follow microservice, covering requirement analysis, MySQL table schema, Redis set operations, Spring Boot dependencies, configuration, service and controller code, gateway routing, and end‑to‑end testing with concrete examples and performance insights.
The article introduces the SQLE project, details the new preview version 2.2302.0-pre2 with features such as external data source import and DMP integration, and provides the complete release notes, optimization items, links to previous versions, and additional reading resources.
This article explains MySQL's transaction concepts, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their effects on dirty reads, non‑repeatable reads and phantom reads, how they are implemented with locks and MVCC, and provides practical SQL examples and diagrams.
This article provides a comprehensive tutorial on configuring MySQL master‑slave replication with Docker, explains the roles of binlog and redo log, describes two‑phase commit, and demonstrates how to use Sharding‑JDBC (ShardingSphere) for read‑write splitting and horizontal sharding in a Spring Boot application, complete with configuration files and code examples.
This article presents a practical guide to implementing monthly sharding for high‑volume billing data using MySQL stored procedures, dynamic table creation, and SpringBoot‑MyBatis integration, covering schema design, code examples, and query strategies for inserting, retrieving, and managing partitioned tables.
The article explains how MySQL Shell 8.0.31's util.debug component provides built‑in functions to gather comprehensive diagnostic data—including error logs, replica status, InnoDB metrics, and OS metrics—and demonstrates practical command‑line examples for generating and analyzing zip‑packed diagnostic reports.
This article explains the transition from dual‑primary MySQL to a master‑slave read/write split, outlines the pain points of position‑based replication, and demonstrates how GTID simplifies setup, failover, and monitoring of replication lag while ensuring data consistency.
This guide compiles a comprehensive set of MySQL command‑line scripts for exporting whole databases, individual tables or schemas, importing data, and performing common database and table operations such as creation, selection, alteration, deletion, granting privileges, and viewing structure, all illustrated with ready‑to‑use code examples.
The article analyzes a MySQL mysqldump failure where tables appear missing due to the lower_case_table_names setting, reproduces the issue through a controlled experiment, and provides practical solutions and operational recommendations for handling case‑sensitivity during database migrations.
This article explains how InnoDB implements row‑level locking in MySQL, detailing the lock types (record, gap, next‑key), the scenarios that trigger each lock, how unique and non‑unique indexes affect locking, and provides step‑by‑step commands and examples for analyzing locks with performance_schema.
This comprehensive guide walks you through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, insert‑select variations, on duplicate key update, show create table, explain, show processlist, and mysqldump—complete with clear examples, SQL snippets, and result screenshots to help you manage and optimize your data effectively.
This article explains the complete MySQL execution flow, covering the connector, permission verification, caching, parser, optimizer, executor, process states, SQL execution order, and the impact of WHERE‑clause condition ordering on query performance.
The preview release of SQLE 2.2302.0-pre1 introduces manual deployment support, UI enhancements, and several bug fixes, providing a detailed overview of the project's purpose, new functionalities, and complete release information for database users and administrators.
This article investigates MySQL's recommendation against using UUIDs as primary keys by creating three tables with auto‑increment, UUID, and random (snowflake) keys, running insertion benchmarks with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the advantages and drawbacks of each key strategy.
This article explains how to handle massive order volumes by classifying data into hot and cold tiers, storing them in MySQL, Elasticsearch, and Hive, and implementing sharding and partitioning strategies—including shard keys, modulo routing, and combined database‑table distribution—to achieve high throughput and low cost.
This article analyzes why simple @Transactional and lock annotations still cause overselling in flash‑sale scenarios, then presents seven backend solutions—including improved controller locking, AOP locking, pessimistic and optimistic database locks, and queue‑based approaches with BlockingQueue and Disruptor—along with code samples and JMeter test results.
This guide explains why MySQL monitoring is critical, defines five key metric dimensions, shows how to collect them with Prometheus and the MySQL Exporter, provides ready‑to‑use alert rules, and walks through the full setup and dashboard creation on Alibaba Cloud.
This article presents a Go-based MySQL-to-MySQL data synchronization solution built on go-mysql-transfer, detailing its architecture, multi‑threaded design, binlog handling, DDL/DML ordering, and code implementation for reliable, ordered, and fault‑tolerant replication.
This article walks through practical MySQL index usage, illustrating space and time costs, back‑table overhead, covering indexes and index condition pushdown, common scenarios where indexes fail, and concrete best‑practice guidelines, all demonstrated with a 5‑million‑row InnoDB table and real SQL examples.
This article analyzes a MySQL 8.0 deadlock caused by an update that locks an auxiliary index followed by a delete that locks the primary key, explains the lock sequence that leads to a circular wait, and offers a practical solution of using primary‑key updates to avoid the deadlock.
This article explains how to identify MySQL injection points, determine the number of columns with ORDER BY, enumerate fields via UNION SELECT, leverage common MySQL functions, and exploit the load_file() function—including char() encoding, replace, substring, and INTO OUTFILE—to read or write files on the target system.
This article breaks down the functional requirements, data models, layered architecture, push‑pull messaging choices, and message flow for building a robust one‑to‑one instant‑messaging service using Redis, MySQL and asynchronous queues.
This guide walks through a collection of practical MySQL functions and commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST and mysqldump—providing clear syntax, example queries, result screenshots and key usage notes to help developers write more efficient and reliable SQL.
This article presents a series of practical MySQL performance‑tuning methods—including smarter LIMIT usage, handling implicit type conversion, rewriting UPDATE/DELETE with JOIN, mixed‑order optimization, EXISTS replacement, condition push‑down, early result filtering, and CTE usage—to dramatically reduce query execution time across common scenarios.
Facing massive user and order data growth, this article examines storage options, compares relational, NoSQL, and NewSQL databases, and provides a detailed guide to implementing MySQL‑based sharding with proxy and client modes, key selection, partition strategies, migration steps, and future scaling considerations.
Even non-programmers notice MySQL’s growing pains—large tables, oversized databases, and read-heavy workloads—prompting a shift toward distributed databases, where sharding, read/write separation, and cloud-native solutions like TiDB or Aurora replace traditional middleware, marking the end of single-node MySQL dominance.
This article explains why and when to split databases and tables, how to choose sharding keys, various sharding strategies such as range, hash and consistent hash, handling cross‑node joins, pagination, distributed IDs, middleware choices, and step‑by‑step zero‑downtime migration techniques.
The article explains how to handle billions of daily orders by classifying data into hot and cold segments, storing them in MySQL, Elasticsearch, and Hive, and applying sharding and partitioning techniques at both table and database levels to achieve scalable performance.
This article demonstrates how to locate the source of abnormal MySQL connection traffic by identifying the offending socket, tracing its system‑call stack with strace, and correlating the activity to specific application logic such as create/drop database and workload tasks.
The article explains how traditional single‑node MySQL faces scalability challenges such as massive tables, resource exhaustion, and read‑heavy workloads, and argues that adopting sharding, middleware, or modern distributed databases like TiDB, OceanBase, Aurora, and PolarDB is the inevitable path forward for robust data management.
This article provides a comprehensive guide on using Spring Batch within a Spring Boot application, covering CSV and database data sources, detailed configuration beans, custom readers, processors, writers, listeners, validation, and execution via a REST controller, along with practical troubleshooting tips.
This article introduces an open‑source MySQL Shell plugin collection, explains how to install it on Fedora, and demonstrates practical usage of its replication, locks, profiling, InnoDB fragmentation, and binlog‑check components with concrete command‑line examples and output.
The article explains the limitations of standalone MySQL, outlines common scaling problems such as oversized tables and high read traffic, and compares middleware‑based sharding solutions with modern distributed databases, highlighting why the latter are becoming the preferred choice for growing applications.
This article walks through the design and implementation of a friend‑follow microservice using MySQL for persistence, Redis Sets for fast set operations, and Spring Boot, covering requirement analysis, data modeling, dependency setup, configuration, service logic, and testing with concrete code examples and performance insights.
This article explains the process by which MySQL transforms uncorrelated IN subqueries into correlated subqueries, details the differences in EXPLAIN output columns, explores optimizer trace information, compares materialization versus correlated execution costs, and provides best‑practice recommendations for writing efficient queries.
This article provides an in‑depth overview of MySQL backup classifications—including hot, warm, and cold backups—explains physical versus logical backup methods, compares built‑in and third‑party tools such as mysqldump, mysqlhotcopy, LVM snapshots, XtraBackup, and presents step‑by‑step command examples for each approach.
The article explains why transaction isolation is needed, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads and phantom reads, outlines the ACID properties of a transaction, and details MySQL's four isolation levels and their impact on data consistency.
This article introduces DataX, an open‑source data integration tool, explains its architecture, and provides step‑by‑step instructions—including environment setup, installation, job JSON creation, and command execution—to achieve fast full‑ and incremental synchronization between MySQL databases.
The article describes a MySQL 5.7.31 replication delay where Seconds_Behind_Master fluctuates between 0 and over 10,000 seconds, analyzes root causes such as single‑threaded replication, large or long‑running transactions, and provides preventive, diagnostic, and remediation steps including enabling parallel replication and using the infobin tool.
This guide walks you through 13 practical MySQL techniques—from aggregating values with GROUP_CONCAT and measuring string length with CHAR_LENGTH, to locating substrings, replacing text, retrieving timestamps, bulk inserting data, handling duplicate keys, inspecting table structures, and using diagnostic commands like EXPLAIN and SHOW PROCESSLIST—complete with ready‑to‑run SQL examples and visual results.
This article explains the ACID properties of database transactions, clarifies the often‑confused concepts of consistency and isolation, details the four SQL standard isolation levels, compares lock‑based and MVCC concurrency controls, and examines concrete implementations in MySQL, PostgreSQL, and the distributed TDSQL system.
The SQLE 2.2212.0-pre3 pre‑release introduces DingTalk‑integrated workflow approvals, adds phone‑number based user binding, and includes several feature enhancements, optimizations, and bug fixes, with full release details, download links, and references to prior versions and related resources.
This article explains MySQL 5.7’s enhanced multi‑threaded slave (MTS) parallel replication, contrasting it with the schema‑based approach of MySQL 5.6, detailing its group‑commit mechanism, configuration parameters, GTID handling, performance testing, and practical tuning tips for optimal replication throughput.
This article explains why MySQL's traditional utf8 charset cannot store 4‑byte characters such as emojis, demonstrates the resulting insert errors, and shows how switching the database, system, and column collations to utf8mb4 resolves the issue while also providing a brief history of MySQL's charset implementation.
The article explains MySQL InnoDB’s internal storage hierarchy—from tablespaces, segments, and extents to pages and rows—its physical components such as buffer pool, change buffer, adaptive hash index, redo and binary logs, persistence techniques like double‑write buffering and log‑flushing policies, and how two‑phase commit and SQL execution interact, helping developers write correct, high‑performance applications.
This article introduces thirteen widely used MySQL graphical management tools, describing each tool's main features, platform support, and official download links to help developers choose the most suitable database client for their workflow.
MySQL 8.0.31 introduces a new syntax that allows importing pre‑computed histogram data directly into a table, dramatically reducing the time needed to update histogram statistics compared with the traditional online creation method.
This guide walks through fourteen practical MySQL techniques—from aggregating rows with group_concat and measuring string length with char_length, to locating substrings, replacing text, retrieving timestamps, bulk inserting, handling duplicates, using pessimistic locks, inspecting tables, explaining queries, monitoring processes, and exporting data with mysqldump.
This article examines how MySQL uses secondary indexes to find the K‑th largest value, analyzes the O(N) time complexity caused by offset pagination, explains the underlying B+‑tree double‑linked list behavior, and presents three optimization strategies—including index covering, pagination redesign, and boundary prediction—to dramatically improve performance.
This article evaluates how MySQL stores time using datetime, timestamp, and bigint fields by inserting 500,000 records and measuring query, grouping, and sorting speeds, concluding that bigint generally offers the best performance for time‑range operations while timestamp saves space when no manipulation is needed.
This guide walks through creating a backup directory, performing a full XtraBackup of MySQL, adding data, executing an incremental backup, simulating accidental database deletion, and then restoring the instance step‑by‑step using preparation, copy‑back, permission fixing, and binlog recovery.
This article investigates MySQL's recommendation against using UUID or non‑sequential keys, builds three tables with auto‑increment, UUID, and random (snowflake) primary keys, runs insertion and query benchmarks using Spring Boot and JdbcTemplate, analyzes index structures, and discusses the advantages and drawbacks of each approach.
This article explains how MySQL stores AUTO_INCREMENT values for InnoDB and MyISAM tables, describes the algorithm that updates these values during inserts, and analyzes why generated IDs may become non‑sequential due to unique‑key conflicts, transaction rollbacks, and batch insert strategies.
This article walks through a collection of practical MySQL techniques—including group_concat, char_length, locate, replace, now, insert‑select variations, pessimistic locking, on duplicate key update, show create table, explain, processlist, and mysqldump—illustrated with SQL examples and screenshots to help developers efficiently query, manipulate, and maintain their databases.
This article explores the challenges of cache‑database consistency under the cache‑aside pattern, compares four cache‑update strategies, and provides practical techniques—including delayed double delete, MQ‑driven invalidation, and binlog subscription—to achieve near‑real‑time consistency while maintaining high read performance.
The SQLE 2.2212.0-pre2 preview release introduces rule template import/export for cross‑platform reuse, adds a new role permission for SQL release, and provides detailed release notes including features, UI improvements, and bug fixes for this open‑source database auditing tool.
