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.
iQIYI adopts SphereEx’s Database Mesh 2.0 by extending ShardingSphere‑JDBC and integrating the cloud‑native Pisanix proxy, creating a unified, encrypted configuration center that enables dynamic sharding, load‑balancing, read‑write separation, hot‑updates and observability, dramatically simplifying database governance and cloud migration.
This article explains how to design and implement a friend/follow microservice using MySQL for persistence and Redis sets for efficient set operations, covering database schema, Spring Boot configuration, Redis template setup, service and controller logic, and testing of follow, unfollow, and mutual‑follow features.
This article explains the functional differences between DISTINCT and GROUP BY in MySQL, analyzes their performance with and without indexes, describes how implicit sorting affects GROUP BY before MySQL 8.0, and provides practical recommendations for choosing the appropriate clause.
This article explains how an improperly configured MTU in a VPN environment caused MySQL SHOW DATABASES commands to hang, details the troubleshooting steps taken, and presents three practical solutions to resolve the MTU‑related packet loss.
This article explains MySQL 5.7 InnoDB's four isolation levels, the structure and purpose of undo logs, the mechanics of multi‑version concurrency control (MVCC), read‑view creation and closure, and why REPEATABLE READ still allows phantom reads, illustrated with code examples.
This article explains how to design the storage layer for an e‑commerce shopping cart, covering core functionalities, required attributes, client‑side temporary storage, server‑side persistence options with MySQL and Redis, and the trade‑offs between reliability, performance, and complexity.
This article explains how to configure ClickHouse as a MySQL replica using the MaterializeMySQL engine, covering code acquisition, MySQL master setup, ClickHouse slave configuration, handling of delete and update operations, and the underlying replication mechanism with practical code examples.
This article analyzes a MySQL 8.0 deadlock caused by concurrent update‑then‑insert logic that creates X GAP locks and conflicting insert‑intention locks, explains the lock trace, and proposes using INSERT ON DUPLICATE KEY to avoid the deadlock.
This article presents a comprehensive collection of MySQL optimization tips, covering everything from avoiding SELECT * and OR conditions to proper use of indexes, joins, UNION, batch operations, and query planning, each illustrated with clear examples and practical reasons for better performance.
This article delves into MySQL InnoDB's storage engine, explaining the COMPACT row format, hidden columns, data page structure, page header, directory, and how B+‑tree indexes—including clustered and secondary indexes—operate and are optimized for fast queries.
This article explains the principles, benefits, and challenges of master‑slave, multi‑master, and leaderless database replication, discusses replication lag, quorum consistency, conflict resolution, and presents various data partitioning techniques with practical examples and diagrams.
This article explains the fundamentals of database sharding, why and when to apply it, various vertical and horizontal splitting strategies, routing algorithms, common pitfalls such as pagination and distributed transactions, and compares client‑side and proxy‑based ShardingSphere deployment patterns.
The article introduces the open‑source SQLE tool, outlines its project background, details the new IDE plugin support for MyBatis XML and SQL file auditing, and provides a comprehensive list of features, optimizations, and bug fixes included in the 2.2212.0-pre1 preview release.
This article describes the design, implementation, and usage of a custom MySQL data consistency checking tool that extends pt-table-checksum, handling composite primary keys, network throttling, scheduled execution, and detailed logging for both successful and failed verification scenarios.
This article explains how MySQL stores NULL values and clarifies the distinct mechanisms of HTTP Keep‑Alive (application‑level long connections) versus TCP Keepalive (kernel‑level health checks), detailing their implementations, usage, and practical considerations.
This article walks through a practical MySQL Group Replication case study, demonstrating how failure detection works, how network partitions affect node states, the role of XCom Cache, and the impact of configuration parameters on cluster availability and performance.
This article walks through a step‑by‑step troubleshooting of MySQL LOAD DATA LOCAL INFILE failures caused by date truncation, quoted CSV fields, and incorrect line terminators, showing how to enable local_infile, use enclosed‑by, @‑variables, and str_to_date to successfully import the data.
This article explains how to design and implement a friend follow microservice by combining MySQL for persistent storage with Redis sets for efficient follow/unfollow operations and mutual follow queries, covering database schema, Maven dependencies, Spring Boot configuration, RedisTemplate setup, service and controller logic, and testing procedures.
Vivo’s Database‑as‑a‑Service platform tackles cloud‑native challenges by automating massive instance management with self‑service work orders and self‑healing, enabling elastic scaling through mixed‑deployment and multi‑threaded Redis tools, optimizing costs via automatic package shrinkage, and safeguarding personal data with full‑chain encryption, while outlining a roadmap toward AI‑driven fault handling, container‑based resources, and advanced privacy governance.
This article demonstrates how to create a score table in MySQL, insert sample data, and implement ranking of students using both user‑defined variables for pre‑8.0 versions and modern window functions like RANK(), DENSE_RANK() and ROW_NUMBER() to handle ties and gaps.
This article explains how to use the MySQL innodb_metrics table to transform the unreadable output of the show engine innodb status command into clear, queryable metrics, including buffer pool statistics and CPU counters, and shows how to enable or disable specific counters as needed.
The article demonstrates via example how REPLACE INTO can cause data inconsistency and auto‑increment anomalies in master‑slave MySQL setups, explains the underlying binlog behavior, highlights risk points, and advises against using REPLACE when business logic depends on auto‑increment IDs.
This article explains how to replace Zabbix with Prometheus for MySQL monitoring by configuring mysqld_exporter to collect metrics from multiple MySQL instances, details the required user accounts, shows common errors, and provides step‑by‑step solutions including building a newer exporter, adjusting configuration files, and using auth_module for password management.
This article explains the purpose and advantages of MySQL indexes, compares MyISAM and InnoDB storage engines, details B+‑tree, full‑text, and hash indexes, and provides practical optimization techniques to improve query performance while noting their trade‑offs.
This article explains where MySQL keeps its data files, how InnoDB organizes tablespaces into segments, extents, pages and rows, details the Compact row format—including variable‑length field length lists, NULL‑value lists, and hidden fields—and clarifies the limits of VARCHAR and row‑overflow handling.
This article explains how to collect extensive MySQL performance metrics—including connections, buffer cache, locks, SQL status, statement counts, throughput, server configuration, and slow‑query logs—using only MySQL's native SHOW commands and the performance_schema, providing practical code snippets and optimization tips.
This article provides a comprehensive step‑by‑step guide on integrating Spring Batch with Spring Boot, covering configuration, entity and mapper definitions, job, step, reader, processor, writer implementations, database setup, error handling, and execution via a REST controller, complete with code examples.
This article explains how a large‑scale membership system achieves high performance and high availability by using a dual‑center Elasticsearch cluster, traffic‑isolated three‑cluster architecture, Redis caching with dual‑center clusters, and a MySQL partitioned dual‑center setup, while also detailing optimization, migration, and fine‑grained flow‑control strategies.
This article introduces eleven popular MySQL graphical management tools—including DBeaver, DataGrip, phpMyAdmin, MySQLDumper, Navicat, MySQL GUI Tools, MySQL ODBC Connector, MySQL Workbench, SQLyog, MySQL‑Front, and dbForge Studio—detailing their features, platform support, and official download links to help developers choose the right solution.
This article explores MySQL’s write‑ahead log, two‑phase commit, engine plugins, memory management, lock strategies, and state‑machine design, showing how these internal mechanisms can inspire more reliable, scalable, and maintainable business system architectures.
This article explains how MySQL's InnoDB engine guarantees ACID properties by describing the four components of ACID, the four isolation levels, lock granularity and types, the MVCC mechanism with version chains and read views, and the roles of undo, redo, and bin logs in ensuring atomicity, consistency, isolation, and durability.
This article evaluates the efficiency of inserting massive datasets into MySQL using three Java strategies—MyBatis without transactions, direct JDBC with and without transactions, and JDBC batch processing—by measuring execution times for millions of records and analyzing the impact of transactions and batching.
This article explains how to use PHP's mysqli extension to establish and close MySQL connections, handle connection errors, perform CRUD operations, process query results, execute multiple statements, and manage transactions, providing code examples for each step.
This comprehensive guide explains how MySQL's InnoDB engine applies row‑level locks—including shared, exclusive, gap, and next‑key locks—based on different SQL statements, isolation levels, and index types, and shows how to analyze and avoid phantom reads with practical experiments.
Index Condition Pushdown (ICP) in MySQL pushes filter conditions to the storage engine to reduce row lookups, but this article demonstrates through experiments that while ICP lowers runtime by decreasing back‑table accesses, the optimizer’s cost model often ignores its benefits, leading to suboptimal plan choices.
This article explains MySQL's theoretical row limits, why performance degrades long before reaching them, and how horizontal and vertical database sharding can distribute massive datasets across multiple instances to improve load handling and enable seamless scaling.
The article details a step‑by‑step investigation of an intermittent deadlock occurring during session creation in a video‑conference system, explains how InnoDB lock types caused the conflict between DELETE and INSERT statements on the session and session_endpoint tables, reproduces the issue, debugs MySQL source code, and presents a simple code‑order fix to eliminate the deadlock.
This article evaluates the efficiency of inserting massive synthetic person data into MySQL using Java, comparing MyBatis, plain JDBC (with and without transactions), and JDBC batch processing, and presents detailed timing results for each strategy across tens of millions of rows.
This article details the overall backend architecture, module relationships, RESTful API design, database schemas, authentication flow, activity creation process, JSON schema validation, component platform APIs, and server‑side rendering considerations for a Node.js‑based service supporting editors, components, and end‑user pages.
A detailed investigation of intermittent MySQL InnoDB deadlocks occurring during video‑conference session creation, covering log extraction, lock analysis, source‑code debugging, reproducible test cases, and a simple code change that eliminates the deadlock while explaining the underlying locking mechanisms.
This weekly digest curates top industry technical shares, featuring articles on database cloud migration, MySQL hash join evolution, OceanBase distributed query performance, MySQL subquery execution, and the latest SQLE SQL audit tool release, along with community development progress and upcoming plans.
This article explores MySQL InnoDB table size limits, explains how page and B+ tree structures affect performance, provides scripts for generating massive test data, and derives a practical recommendation of around 20 million rows based on storage and index calculations.
Change Buffer in MySQL InnoDB (formerly insert buffer) caches modifications to secondary index pages when they are not in the buffer pool, using a B‑tree structure to batch‑apply inserts, delete‑marks, and deletes while avoiding index page splits, merges, and ensuring purge safety.
This article explains why many MySQL queries cause index inefficiency, details the leftmost prefix rule for composite indexes, illustrates common index‑breaking patterns such as SELECT *, functions, range queries, LIKE, OR, IN, ORDER BY, and introduces Index Condition Pushdown (ICP) as an optimization to reduce row lookups.
This article explains how Orchestrator selects a candidate replica during MySQL master failover, detailing the GetCandidateReplica and RegroupReplicasGTID functions, their sorting logic, promotion rules, GTID-based regrouping, and differences from MHA, while highlighting potential data loss issues and related bugs.
This article describes how to replace Zabbix with Prometheus for MySQL monitoring by configuring mysqld_exporter to collect metrics from multiple MySQL instances, including environment setup, user creation, exporter configuration, troubleshooting common errors, and Prometheus job adjustments, providing step‑by‑step commands and code examples.
This article explains how to collect a wide range of MySQL performance metrics—including connections, buffer pool usage, locks, SQL statistics, statement counts, throughput, server variables, and slow‑query logs—using only MySQL's native SHOW commands and performance_schema tables, while also offering practical tuning tips and analysis tools.
This article presents a comprehensive set of MySQL optimization guidelines, covering the avoidance of SELECT *, proper use of specific columns, eliminating OR and != in WHERE clauses, preferring numeric types, choosing VARCHAR over CHAR, using TRUNCATE, batch operations, index design, join selection, GROUP BY efficiency, and other practical tips to improve query performance.
This article introduces eleven widely used graphical tools for MySQL administration, describing each tool's features, platform support, and official download links, helping developers and DBAs choose the most suitable solution for managing MySQL databases efficiently.
This guide walks through MySQL performance tuning, covering the architectural tuning pyramid, query optimization techniques, slow‑query logging, detailed EXPLAIN execution‑plan analysis, and high‑performance indexing strategies to help you diagnose and accelerate database workloads.
This article walks through a MySQL performance case study, showing how nested subqueries on large tables can take hours, and demonstrates step‑by‑step how creating single‑column, composite, and covering indexes, as well as rewriting queries as joins, reduces execution time from tens of thousands of seconds to milliseconds.
This article explores how MySQL tables store data in 16 KB pages, demonstrates how to generate massive test data, explains the impact of primary‑key size and B+Tree depth on query performance, and derives the practical 20 million‑row recommendation for single‑table limits.
DTLE 4.22.11.0 introduces major job configuration changes, new log settings, a TwoWaySync parameter for bidirectional replication, beta Kafka credentials, and fixes for Decimal column defaults, while providing updated documentation links and download resources for the open‑source MySQL data transfer component.
The article explains how using a Redis cache can boost read performance but introduces consistency challenges, compares four cache‑aside write strategies, and recommends the reliable “update database then delete cache” approach combined with short expiration times and asynchronous message‑queue invalidation to keep data fresh.
This article explains how Java architects should select appropriate MySQL database machine configurations, collaborate with DBAs for deployment, and conduct essential performance and load testing, covering QPS vs TPS, IOPS, throughput, latency, and other critical metrics for production environments.
This article explains the complete DeadMaster recovery workflow of Orchestrator, detailing how the system selects the appropriate check‑and‑recover function, handles emergency grace periods, reads topology information, registers recovery attempts, validates promotion constraints, executes the actual failover, and runs post‑recovery hooks, with extensive Go code examples.
This guide shows the original Maven dependency for the MySQL Connector/J library and provides the updated coordinates with the newer groupId, artifactId, and version, helping Java developers keep their projects up to date.
This article explains why short URLs are useful, describes the principle of mapping short codes to original URLs, and provides two complete PHP implementations—one using MySQL for storage and another using an INI file—along with the necessary rewrite rules for Apache and Nginx.
This article explains why MySQL's count(*) can become a performance bottleneck, especially with InnoDB, and presents practical optimization techniques such as Redis caching, second‑level in‑memory caches, parallel execution, reducing unnecessary joins, and using column‑store databases like ClickHouse.
The article examines MySQL partition tables that lack primary keys, compares query performance against tables with primary keys, and provides guidance on index design for such partitioned tables, concluding that partition keys need not be included in indexes.
This article explains why MySQL's count(*) can be slow on InnoDB, compares different count variations, and presents practical optimization techniques such as Redis caching, second‑level caches, multithreading, reducing joins, and using ClickHouse for massive datasets.
This article walks through the complete lifecycle of a MySQL UPDATE statement, covering InnoDB page structure, tablespaces, the buffer pool, redo and undo logs, binlog, and the two‑phase commit process, illustrating each step with examples and command outputs.
This article explains MySQL’s internal execution flow, covering the connector’s role, permission verification, caching, parsing, optimization, execution stages, processlist states, SQL execution order, and best practices for arranging WHERE‑clause conditions to improve query performance.
This article explains how to transform a simple personal‑blog backend into a high‑performance, horizontally scalable system by introducing caching with Redis, load balancing with Nginx/LVS, read/write separation, database sharding, and modern NewSQL solutions such as TiDB, while also discussing related challenges like cache consistency and data migration.
This article analyzes a MySQL InnoDB lock wait timeout caused by batch INSERT operations, examines configuration parameters and slow‑query diagnostics, and presents a rewritten SQL solution that reduces execution time from over 100 seconds to under one second.
The article presents a detailed performance comparison between auto‑increment integer primary keys and UUID string primary keys in MySQL, including table definitions, 5‑million‑row and 10‑million‑row test results for read, range, write, backup, and storage efficiency, and offers practical recommendations for choosing the appropriate key strategy in distributed environments.
This article investigates a historical MySQL bug where dropping large tables with a sizable buffer pool and Adaptive Hash Index (AHI) can cause severe latency, presents test configurations for 5.7.29 and 8.0.28, compares AHI‑on/off results, shows stack traces, and explains the lazy‑delete fix introduced in 8.0.23.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters such as emojis, demonstrates the resulting insertion error, and shows how switching to the utf8mb4 charset resolves the issue while also covering the historical reasons behind MySQL's limited utf8 implementation.
This article explains MySQL's crash recovery process, covering how the doublewrite buffer repairs corrupted pages, how redo logs are applied to restore data, and how undo tablespaces and unfinished transactions are handled during server startup.
This article explains MySQL's client‑server architecture and walks through the three main stages—connection handling, query parsing and optimization, and storage engine selection—detailing protocols, authentication, cache behavior, optimizer mechanics, and engine characteristics with practical commands and examples.
A MySQL DBA recounts a 4‑TB InnoDB instance that stalled during startup, analyzes top, stack traces, and source code to pinpoint a bug involving truncate operations and the buf_flush_event, then presents three practical solutions—including adjusting innodb_flush_sync, using GDB to break the wait loop, or removing truncate logs—to restore normal operation.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters like emojis, demonstrates the resulting insert errors, and shows how switching to the utf8mb4 charset resolves the issue while providing a brief history of MySQL's encoding decisions.
This article explains why traditional ORDER BY … LIMIT pagination becomes slow on tens of millions of rows, analyzes the underlying index scan cost, and presents two practical optimizations—delayed join using covering indexes and a bookmark‑based approach—showing how they can cut execution time to a third or even a tenth of the original.
This article explains how MySQL executes ORDER BY queries, compares the default sorting algorithm with the rowid method, and provides practical optimization steps such as indexing, adjusting sort_buffer settings, and choosing the appropriate algorithm based on data size and memory.
This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
This article explains why MySQL’s legacy utf8 charset only supports three‑byte characters, causing emoji insertion failures, and shows how switching tables, columns, and the database to utf8mb4 resolves the issue while detailing the historical reasons behind MySQL’s limited utf8 implementation.
This weekly roundup curates top database‑related technical articles from the MySQL community, shares the latest SQLE audit tool release and development progress, and invites readers to contribute feedback and questions about DBLE, DTLE, SQLE, and TXLE middleware.
This article explains how MySQL master‑slave replication enables read/write splitting, details the asynchronous replication process, discusses latency and its impact on business logic, and presents practical mitigation strategies along with middleware options for accessing replicated databases.
This article explains why high‑concurrency big‑data systems need database sharding, describes table‑level partitioning methods (modulus and range), shows how to implement them with MySQL and Java code, compares vertical and horizontal database splitting, and evaluates middleware solutions such as Sharding‑JDBC and Mycat.
This article presents a systematic approach to transform MySQL slow‑query handling from reactive troubleshooting to proactive risk management by defining a scoring model, selecting key metrics, calculating weighted risk indices, and testing the model to prioritize and reduce harmful queries.
This article explains how to handle billions of order records by classifying data into hot and cold tiers, using MySQL sharding with database‑and‑table partitioning, storing cold data in Elasticsearch and Hive, and outlines combined routing formulas for scalable backend architecture.
This article walks through a step‑by‑step investigation of why incremental backups failed on a DBaaS platform, revealing that the PERCONA_SCHEMA.xtrabackup_history table’s binlog_pos column was too short, and shows how altering its length resolves the problem.
This announcement introduces the MySQL DBA Elite Practical Course book, detailing its comprehensive coverage of core and advanced MySQL technologies, and outlines a limited-time giveaway event where readers can win copies by following the community’s participation rules.
This article walks through a real‑world MySQL slow‑query case, detailing analysis, index checks, character‑set alignment, forced index usage, and IN‑clause considerations that together reduced a 5‑second query to under one second.
