This article explains MySQL’s server and storage‑engine architecture, walks through the step‑by‑step execution of a SELECT statement, describes how indexes are organized, defines slow queries, shows how to detect and analyze them, and provides practical optimization examples and additional MySQL pitfalls.
This article presents practical MySQL performance tuning techniques, covering query, pagination, join, subquery, order‑by, group‑by, and count optimizations with example code, index strategies, and execution‑plan analysis to improve latency in high‑traffic systems.
This article explains the three primary ways to delete data in MySQL—using DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, storage‑engine differences, and best‑practice considerations such as space reclamation and auto‑increment handling.
This article reviews six theoretical approaches for maintaining MySQL‑Redis data consistency, evaluates their pros and cons, and presents a practical implementation using Java Spring, transactional updates, cache deletion, and asynchronous queue handling to achieve both real‑time and eventual consistency in high‑concurrency systems.
This article explains four design patterns for implementing an audit workflow, compares their advantages and disadvantages, and provides complete SpringBoot backend, MySQL table definitions, and Vue front‑end code to build a functional audit system with file upload support.
This article explains how to query sales statistics for multiple business units and their associated products in MySQL by preparing the environment, presenting several candidate solutions—including loop queries, OR concatenation, mixed filtering, and finally row‑comparison—and selecting the row‑comparison approach as the optimal implementation.
This article explains why MySQL's Adaptive Hash Index (AHI) can cause slow TRUNCATE operations, describes its benefits, and provides a detailed step‑by‑step walkthrough of the AHI construction process—including index counting, construction‑info counting, data‑page counting, and the final hash‑index building—using MySQL 8.0.32 source code examples.
This article explains how MySQL 8.0 updates the AUTO_INCREMENT column in INFORMATION_SCHEMA.TABLES, describes the underlying statistics caching mechanism, shows how the information_schema_stats_expiry parameter controls refresh frequency, and provides step‑by‑step tests demonstrating real‑time behavior with code examples.
This article examines the differences between MySQL's stop slave and reset slave all commands and OceanBase's replication controls, presenting experimental results that show how clog synchronization affects cluster availability, data consistency, and the procedures for decoupling standby clusters.
This tutorial guides you through creating a simple PHP message board, covering MySQL database setup, table design, and step‑by‑step PHP scripts for displaying a form, handling submissions, and listing comments, while also noting security and extension considerations.
Oracle introduces a new MySQL versioning strategy that separates Innovation releases, featuring frequent new features and fixes, from Long‑Term Support (LTS) releases with stable behavior, outlining transition timelines, upgrade/downgrade paths, HeatWave service options, and support lifecycles for developers and DBAs.
The article examines the drawbacks of running MySQL in Docker containers, highlighting data safety risks, I/O performance bottlenecks, state management challenges, and resource isolation limits, while also outlining scenarios where containerizing MySQL can be feasible.
This article explains how Percona XtraBackup 8.0.33-28 introduces a new design that reduces the time required for the backup‑prepare phase, lowers memory and I/O usage, and speeds up SST when adding new nodes to a Percona XtraDB Cluster, with detailed performance benchmarks and code examples.
This article explains the myth of a 2 million‑row limit for MySQL tables, shows how primary‑key size and InnoDB page structure determine theoretical row limits, details B+‑tree indexing mechanics, calculates practical capacities, and compares B+‑tree with B‑tree performance.
This article compares SQLite and MySQL, outlines SQLite’s advantages and disadvantages, describes scenarios where SQLite is appropriate, and provides a step‑by‑step PHP tutorial with complete code for connecting, creating tables, inserting, querying, updating, deleting, and closing an SQLite database.
This article introduces a Java‑based private cinema reservation system built on Spring Boot and MyBatis, outlines its core technologies, provides step‑by‑step deployment instructions, and details all functional URLs and the source‑code download method.
The article compares four MySQL‑to‑Elasticsearch synchronization approaches—synchronous dual‑write, asynchronous dual‑write via MQ, timer‑based SQL extraction, and binlog‑based real‑time sync—evaluates their trade‑offs, and recommends practical tools such as Canal, DTS, and Databus for implementation.
This article explains InnoDB's three row‑lock types, when MySQL statements acquire implicit or explicit locks, and walks through concrete examples for unique and non‑unique index queries, showing how Next‑key, Record and Gap locks are determined.
This article explains why a single MySQL instance is insufficient for production, introduces binlog concepts and formats, details the master‑slave replication threads and workflow, explores parallel replication mechanisms, analyzes causes of replication lag, and provides practical strategies to minimize that delay.
This article investigates a MySQL 8.0 TDSQL client experiencing connection interruptions during batch streaming, analyzes error 1161 (ER_NET_WRITE_INTERRUPTED) root causes in the MySQL source code, presents debugging steps with log and packet captures, and offers practical mitigation recommendations.
This article examines how JD Logistics tackled MySQL connection limits by redesigning its sharding strategy using ShardingSphere's Sharding-JDBC, detailing the background, concepts of database and table sharding, analysis of bottlenecks, and step‑by‑step refactoring to reduce connections and improve scalability.
MySQL introduces Innovation and Long‑Term Support (LTS) release tracks, offering faster feature delivery for Innovation builds and stable, minimal‑change updates for LTS, while outlining new version cadence, HeatWave service options, deprecation policies, and detailed upgrade‑downgrade paths for developers and DBAs.
This guide explains how to perform regular MySQL backups, automate them with crontab, compress and rotate old backups, and keep multiple servers in sync using rsync and inotify, providing complete command examples and configuration files for both server and client sides.
This article analyzes why the MariaDB audit plugin crashes on MySQL 5.7, presents compatibility tables before and after source modification, details the verification steps, and provides a step‑by‑step solution including source code changes and recompilation to make the plugin work reliably.
This article explains MySQL's doublewrite buffer mechanism, how it ensures data consistency by writing to a redo log before disk, provides configuration steps to enable it, and outlines performance optimization techniques such as adjusting redo log size, separating log files, and tuning flush settings.
The article analyzes a replication failure where mysqldump creates duplicate primary‑key errors on a MyISAM table, explains why the --single-transaction option only guarantees consistency for InnoDB, and provides solutions such as converting tables to InnoDB or using Xtrabackup.
This article explains common MySQL index‑inefficiency scenarios—including left‑most prefix violations, range queries, functions, LIKE patterns, OR/IN misuse, and ORDER BY—provides concrete SQL examples, visual illustrations of B+‑tree indexing, and practical tips such as index jump scans, ICP, and query rewriting to ensure optimal index usage.
This article walks through a real‑world MySQL deep‑pagination incident, detailing the timeline, root‑cause analysis, and multiple optimization techniques—including ID‑based queries, sub‑queries, and cursor pagination—that ultimately stabilized the system and eliminated slow‑SQL overload.
Oracle announces a new MySQL version model that separates Innovation releases, which deliver new features and frequent updates, from Long‑Term Support (LTS) releases that provide stable, minimal‑change versions with extended support, outlining transition paths, upgrade‑downgrade strategies, and product portfolio impacts.
A detailed 2023 comparison of PostgreSQL and MySQL examines licensing, performance, features, extensibility, usability, connection models, ecosystem, and operability, helping developers decide which open‑source relational database best fits their workloads and long‑term strategy.
This article compares PostgreSQL and MySQL across licensing, performance, features, scalability, security, query optimization, replication, JSON support, CTEs, window functions, and ecosystem, using data from the 2023 Stack Overflow survey and practical experience to help developers choose the right open‑source relational database.
This article introduces MySQL’s three primary logs—redo log, binlog, and undo log—explaining their purposes, how they ensure durability and recovery, the mechanisms for flushing them to disk, and the key differences among them, along with practical configuration recommendations.
MySQL 8.1 and 8.0.34, released on July 18, 2023, introduce a mix of innovative features—such as JSON EXPLAIN INTO, enhanced replication controls, TLS certificate enforcement, and new SHOW PARSE_TREE—while also aligning many adjustments with 8.0.34, adding user‑management options, audit enhancements, binary‑log APIs, and a series of deprecations for future versions.
MySQL 8.1, the first innovation release, and the stable 8.0.34 bring a host of new capabilities—including JSON‑based EXPLAIN INTO, enhanced replication controls, expanded security variables, audit improvements, binary‑log functions, and several deprecations—while also fixing numerous bugs to solidify MySQL’s stability.
This article explains why a MySQL user granted both USAGE and GRANT OPTION on a database can see schema information but cannot query table data, demonstrates the issue with local tests, and provides proper REVOKE commands to cleanly remove the conflicting permissions.
This article explains how InnoDB stores data on disk, describing tablespaces, segments, extents, pages, row formats, record headers, page directories, B+‑tree indexes, and the differences between clustered and secondary indexes, with examples and diagrams to illustrate each component.
reverse_sql is an open‑source utility that parses MySQL binary logs in ROW format, generates both original and reverse SQL statements, and helps recover data from accidental DML operations using multithreaded processing and flexible filtering options.
This article provides a detailed interview recap covering Redis in‑memory database concepts, MySQL transaction and indexing fundamentals, OSI network layers, operating‑system memory layout, Java versus Go differences, and key takeaways for future study, all aimed at backend developers.
This article describes a MySQL replication outage caused by an incorrectly set max_binlog_cache_size parameter, explains the underlying mechanism of binlog caching, and provides a step‑by‑step solution to adjust the setting and restore normal replication.
This article explains three MySQL pagination methods—simple LIMIT syntax, MyBatis RowBounds logical pagination, and the powerful MyBatis-PageHelper plugin—detailing their usage, configuration, code examples, and performance considerations to help Java developers efficiently handle large data sets.
This article explains MySQL deadlocks through practical examples, covering lock types, exclusive vs. shared locks, lock‑in‑share‑mode vs. FOR UPDATE differences, real‑world deadlock scenarios, and multiple strategies—including isolation level tweaks, optimistic locking, and distributed locks—to prevent and resolve them.
The article explains a puzzling MySQL DELETE failure caused by a missing primary key, shows how Navicat adds an ESCAPE clause to the generated SQL, clarifies the purpose of ESCAPE in LIKE patterns, and reveals that the real issue was a mistaken condition order in the query.
Facing over a hundred million daily slow queries, Meituan’s Database Autonomous Service combines traditional cost‑based index selection with an AI‑powered, data‑driven classifier to automatically generate and validate missing indexes, achieving a 12.16% uplift in index adoption and saving thousands of hours of query execution time.
This article explains how MySQL 8.x window functions such as OVER, PARTITION BY, and ORDER BY can simplify complex ranking and aggregation queries, demonstrates creating a sample scores table, and provides practical examples of functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, and LEAD with their results.
The article describes how a membership system serving billions of users across multiple platforms achieves high performance and high availability through dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis caching with distributed locks, dual‑center MySQL partitioning, and fine‑grained flow‑control and degradation strategies.
This article explains InnoDB's three row‑lock types, when they are applied implicitly or explicitly, the two core locking principles, and walks through concrete examples for unique and non‑unique index equality and range queries, showing exact lock ranges and lock‑type degradations.
This article explains how to design a MySQL table for product sales statistics, prepares sample data, describes the business requirement of querying sales for multiple business units and their associated products, and evaluates several query strategies—looping, OR concatenation, mixed filtering, and finally row‑row comparison—highlighting why the latter is chosen as the optimal solution.
The AiKeSheng Open Source Community announces a giveaway of a comprehensive MySQL‑based database systems textbook sponsored by Tsinghua University Press, detailing its four‑part structure, extensive learning resources, and participation rules for the July 2023 event.
This guide outlines a five‑level learning path for MySQL, starting with installation and basic queries, progressing through proficient use, deep understanding of locks, indexes and transactions, advanced performance tuning, and finally product‑level redesign, while recommending essential books and resources.
WeBlog is a front‑end/back‑end separated blogging platform built with Spring Boot, MyBatis‑Plus, MySQL, Spring Security, JWT, Minio, and Vue 3.2 + Element Plus, offering Markdown editing, category/tag management, dashboard statistics, and a complete set of admin and front‑end features.
This article analyzes a sluggish MySQL query, explains why the original plan performs a full table scan, and demonstrates how rewriting the EXISTS clause as an INNER JOIN and substituting the OR condition with a UNION dramatically reduces execution time from 42 seconds to 18 milliseconds.
This guide compiles essential MySQL command‑line scripts covering database and table export, import, creation, alteration, permission granting, and common DDL/DML operations, providing clear examples for managing schemas, data, and user privileges in a MySQL environment.
This article explains why MySQL’s show slave hosts command on a master displays the master’s own IP instead of the replica’s, by examining the report_host parameter, deprecated syntax, and configuration copying practices, and provides steps to reproduce and resolve the issue.
This article presents twenty essential principles for optimizing MySQL indexes, covering topics such as the limitations of leading wildcard LIKE queries, the advantages of IN over UNION, handling negative conditions, composite index ordering, range queries, covering indexes, prefix indexes, and common pitfalls to avoid for better query performance.
This article explains why MySQL reports error 1040 "Too many connections", shows how Percona Server can reserve an admin port to keep DBA access, and details MySQL 8.0.14's built‑in admin connection feature with configuration steps, commands, and a performance experiment.
This comprehensive guide explains 20 practical MySQL index optimization rules—including when NOT to use indexes, how to choose the right index type, and techniques such as prefix indexes, covering indexes, and query rewriting—to dramatically improve SQL query efficiency and reduce database load.
This weekly newsletter curates top database and MySQL technical articles, announces new SQLE open‑source features and roadmap, and highlights upcoming community events, providing readers with concise, actionable insights into performance optimization, tooling updates, and open‑source initiatives.
The article details how Meituan built a MySQL autonomous platform by constructing kernel observability to split OnCPU/OffCPU wait time, capturing full SQL directly from the kernel with compression, designing a safe exception‑handling workflow, and generating cost‑based index‑tuning suggestions—including what‑if analysis and workload‑driven recommendations—to enable comprehensive SQL governance.
The article explains the design of Meituan’s MySQL autonomous platform, detailing its four‑layer architecture, statistical dynamic‑threshold anomaly detection, model selection based on time‑series distribution, kernel‑level root‑cause analysis for replication lag, large‑transaction diagnostics, and crash investigation using core‑dump and signal analysis.
This article analyzes MySQL's recommendation against UUIDs and non‑sequential keys by benchmarking three tables—auto‑increment, UUID, and random snowflake IDs—using a Spring Boot JdbcTemplate test, revealing that sequential primary keys provide superior insert performance and lower index fragmentation.
This article investigates how differing MySQL lower_case_table_names settings between source and target affect DTLE's ability to synchronize data, presenting test scenarios, observed behaviors, and recommendations for configuration and job management.
This article demonstrates how to generate, insert, and query millions of rows in a MySQL 5.7 table, measures pagination performance under varying offsets and result sizes, and presents several optimization techniques—including sub‑queries, indexed look‑ups, and column selection—to dramatically reduce query latency.
The article details how Bilibili's relationship‑chain service scaled from a MySQL‑based design to a distributed KV store, introduced successive caching layers (memcached, Redis hash, Redis KV, bloom filter), and implemented hotspot mitigation techniques to sustain near‑million QPS traffic while ensuring high availability and data consistency.
This article explains the MySQL event_scheduler thread, why killing it during master‑slave failover produces an "Unknown thread id" warning in MySQL 8.0, how to manage events, and the impact of these internal daemon sessions on replication and failover processes.
This article explains why the MySQL 8.0 admin_port parameter may not take effect, demonstrates how to reproduce the issue, analyzes the root cause related to admin_address binding and socket connections, and provides a step‑by‑step solution with configuration tips and optimization recommendations.
This guide walks through building a SpringBoot architecture that updates MySQL first, deletes Redis cache asynchronously via Canal and RabbitMQ, resolves common Canal meta‑data mismatches, and configures manual RabbitMQ acknowledgments to guarantee reliable cache invalidation.
This article walks through MySQL's three‑layer logical architecture, explains the roles of redo, undo, bin, and relay logs, shows how they ensure crash‑safety and point‑in‑time recovery, and provides practical commands for inspecting and configuring these logs.
This article details a real‑world incident of severe pagination slow queries, walks through the timeline of detection, diagnosis, and mitigation steps, and presents multiple MySQL optimization techniques—including ID‑based queries, sub‑queries, scroll queries, and join‑based solutions—to resolve deep pagination performance issues.
Certain MySQL queries, such as those using functions, leading wildcards in LIKE, OR operators, inequality comparisons, or NULL checks, prevent the database engine from utilizing indexes, leading to slower performance, and should be rewritten for optimal query efficiency.
This article explains MySQL master‑slave architecture, why it’s used, the replication process, consistency challenges, causes of replication lag, and practical strategies—including binlog formats, mixed mode, monitoring, caching, and failover setups—to optimize performance and ensure high availability.
This article explains five common MySQL query patterns—using functions, leading wildcards with LIKE, OR operators, inequality comparisons, and NULL checks—that prevent the database engine from utilizing indexes, and provides example SQL statements illustrating each case.
This article examines why MySQL LIMIT pagination becomes slower with deeper offsets on a 500,000‑row table, demonstrates the performance impact with concrete queries, and presents three optimization strategies—including using ordered primary keys, subqueries, and join‑based approaches—to reduce scan range and improve query speed.
The article explains a real‑world MySQL replication failure caused by a 4 GB binlog, walks through the replication architecture, binlog write mechanics, diagnostic commands, mode differences, and shows how a large row‑based transaction with a BLOB field inflated the log, concluding with a practical fix.
This article explains why master‑slave replication is needed in high‑concurrency scenarios, describes the consistency challenges it introduces, and presents three practical solutions—half‑synchronous replication, database middleware, and cache‑key recording—detailing their principles, advantages, and drawbacks.
This article examines the common consistency challenges between Redis cache and MySQL, explains why strict consistency is hard to achieve, and presents four practical approaches—cache‑first delete, delayed double delete, DB‑first update with lock, and MQ‑based retry—to minimize inconsistency windows and ensure reliable data synchronization.
This article analyzes a Java 1.8 application that suffers from long G1GC pauses caused by mis‑configured JVM flags and a MySQL driver that creates massive phantom references, explains the GC log findings, and presents concrete JVM, connection‑pool and driver‑upgrade solutions to eliminate the pauses.
This article introduces Chat2DB, an open‑source AI‑enhanced multi‑database client, walks through its download, installation, configuration, demonstrates natural‑language‑to‑SQL, SQL explanation and optimization features using sample MySQL tables, and outlines future roadmap for developers.
The article investigates why an UPDATE statement on a MySQL 5.7 InnoDB table with RR isolation experiences high lock time, demonstrates how missing indexes cause full‑table scans and blocking, and shows how adding an index and using optimizer trace can diagnose and resolve the issue.
This article explores whether AI agents such as ChatGPT can help business owners quickly perform data analysis by linking large language models with local MySQL databases via LangChain, presenting the experimental setup, schema creation, sample queries, results, and practical insights.
This article demonstrates how to create a 10‑million‑row MySQL table, batch‑insert data via a stored procedure, measure ordinary LIMIT pagination versus offset‑optimized queries, and provides practical tips for improving query speed on massive tables.
This article details a step‑by‑step optimization of a high‑volume Excel import workflow in Java, covering the migration from raw POI to EasyPOI and EasyExcel, caching database lookups, using MySQL batch inserts, and leveraging parallel streams to reduce import time from minutes to under two minutes.
The article investigates intermittent long G1GC pauses in a Java 1.8 application caused by mis‑configured G1 parameters and MySQL driver phantom references that retain many idle connections, then presents detailed log analysis and multiple remediation steps including JVM flag tuning, connection‑pool adjustments, and driver upgrades.
This article compares OceanBase (MySQL mode) and MySQL in terms of user management, password syntax, user locking, permission levels, grant statements, grant tables, network white‑list access control, row‑level security, and role management, highlighting similarities, differences, and migration considerations.
This guide explains how to use the open‑source binlog_parse_sql tool to parse MySQL 8.0 binary logs, generate SQL statements, and seamlessly migrate data to MariaDB or ClickHouse, covering installation, execution modes, troubleshooting, and practical ETL scenarios.
This article explains how to proactively identify non‑conforming date values during Oracle or MySQL data migrations by creating test tables, leveraging DBMS_ERRLOG for Oracle, using strict SQL_MODE and temporary tables for MySQL, and optionally applying regular‑expression checks.
The SQLE 2.2306.0‑pre3 release introduces Baidu Cloud RDS MySQL slow‑log scanning, SQL formatting, DB2 rollback generation and context‑aware auditing, along with numerous optimizations and bug fixes, and provides updated links to repositories, documentation, and demo environments for both community and enterprise editions.
This guide explains MySQL transaction fundamentals, the ACID properties, how to use explicit and implicit transactions, savepoints, and demonstrates each isolation level with commands and examples to help developers write reliable, concurrent database code.
This article compares four common approaches for synchronizing product data from MySQL to Elasticsearch—synchronous dual write, asynchronous dual write via message queues, scheduled batch jobs, and binlog‑based data subscription—detailing their implementation steps, advantages, drawbacks, and practical considerations.
This article presents a detailed Java backend interview recap covering self‑introduction, thread‑pool choices, Snowflake ID usage, List implementations, List vs Set differences, common HTTP status codes, flow and congestion control, URL request flow, TCP three‑way handshake, HTTP vs HTTPS, database index types, B+‑tree advantages, transaction isolation problems, and practical interview preparation tips.
This article walks through nine common MySQL performance pitfalls—such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and more—showing how to rewrite each query and its execution plan to achieve dramatic speed improvements.
This article analyzes why MySQL recommends auto_increment primary keys over UUID or non‑sequential Snowflake IDs by creating three tables, running Spring Boot/JdbcTemplate performance tests, and examining index structures, lock contention, and fragmentation to guide optimal primary‑key selection.
This article investigates why TRUNCATE and DROP TABLE statements appear in MySQL slow‑query logs, analyzes the internal execution paths and performance bottlenecks in MySQL 5.7 and 8.0, and proposes configuration‑based optimizations and debugging techniques to mitigate the latency.
The SQLE 2.2306.0-pre2 release introduces three new MySQL audit rules, configurable operation‑record expiration, expanded DB2 rule support, numerous optimizations and bug fixes, and provides download links, documentation, and demo environments for both community and enterprise editions.
An analysis of the 2023 Stack Overflow Developer Survey of 90,000 respondents reveals PostgreSQL surpassing MySQL as the preferred database, highlights shifting language popularity, emerging cloud platforms, and growing yet cautious adoption of AI tools among developers.
This article introduces twenty commonly used MySQL functions, explains their purposes, and provides clear SQL examples with expected results to help developers efficiently manipulate and process data, including string, numeric, date, and aggregate functions, each illustrated with sample queries and output.
This article explores the challenges of distributed join queries, detailing MySQL sharding‑jdbc join implementation, routing strategies, and code examples, then examines Elasticsearch‑SQL join capabilities, various join algorithms, and practical considerations for using nested types, offering insights for optimizing performance in distributed data environments.
This article explains the different types of InnoDB tablespaces, why the ibdata1 file can grow uncontrollably, and provides a step‑by‑step procedure—including full backup, instance recreation, and configuration of UNDO and file‑per‑table settings—to safely reduce its size in MySQL environments.
The article describes how a finance team tackled a 50‑million‑row MySQL transaction table by analyzing the pain points, defining split goals, selecting sharding‑jdbc, designing multi‑source transaction handling, pagination across shards, data migration strategies, and a staged rollout to ensure stability and performance.
This article investigates how the MyBatis‑Plus saveOrUpdate method can trigger gap‑lock deadlocks in MySQL, explains the underlying concepts of deadlocks and gap locks, reproduces the issue with concrete SQL scripts, and proposes both risky and recommended mitigation strategies.
This article explores a real‑world scenario where a support manager faces nightly batch job interruptions, analyzes common database and environment failures, and presents a systematic redesign of the batch framework and executor to enable automatic retry, reducing manual intervention and improving operational reliability.
