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.
This article explains how to design and implement a business operation logging feature in a Spring Boot application using custom annotations and Spring AOP, covering requirement analysis, pitfalls of a naïve implementation, AOP design, code examples, and testing procedures.
This article explains MySQL's four‑layer logical architecture, describes high‑performance read strategies, details data pages, buffer pool and LRU algorithm, and provides comprehensive guidance on index structures, optimization techniques, and best practices for improving query performance.
This article explains OceanBase's MySQL‑compatible security features, covering the MAPI authentication process, user naming conventions, password complexity and expiration settings, and mechanisms for handling repeated login failures, with detailed comparisons to MySQL.
This article explains the differences between MySQL DDL and DML, describes how online DDL works—including copy and inplace algorithms, execution phases, common pitfalls, limitations, and recent MySQL 8.0 enhancements—providing practical guidance for safely altering large tables.
This article explains MySQL InnoDB index fundamentals, covering index types, data structures, clustered and secondary indexes, covering indexes, index pushdown, index merge, cost‑based index selection, common index‑inefficiency scenarios, and practical guidelines for designing effective indexes.
Facing a MySQL transaction table exceeding 50 million rows and growing rapidly, a finance team redesigned the schema using sharding‑jdbc, chose transaction time as the shard key, tackled multi‑data‑source transactions and cross‑shard pagination, and executed a phased migration to ensure stability and performance.
This guide demonstrates how to create a powerful web-based Redis CRUD interface by integrating Django for the backend API, MySQL for metadata storage, and Vue for a responsive frontend, covering environment setup, project scaffolding, model and view implementation, routing, and deployment steps.
This article provides a systematic, interview‑focused overview of MySQL fundamentals—including normal forms, DML vs DDL, primary and foreign keys, storage engines, redo and binlog mechanisms, indexing strategies, transaction properties, MVCC, lock types, replication, and common performance pitfalls—helping candidates quickly master essential database knowledge for interview success.
This weekly newsletter curates top industry tech articles covering SQL deployment capabilities, MySQL replication issues, LLM‑to‑SQL benchmarks, connection limit errors, OceanBase tracing, and provides an open‑source SQL quality management platform update with development progress and upcoming plans.
This article details the design and implementation of a highly available, high‑performance membership platform serving over a billion users, covering Elasticsearch dual‑center clusters, traffic‑isolated clusters, deep ES optimizations, Redis caching strategies, MySQL dual‑center partitioning, seamless migration, and fine‑grained flow‑control and degradation mechanisms.
This article describes a MySQL replication issue where the I/O thread could not connect to the master due to the replication user’s SSL requirement, explains the analysis steps, reproduces the error, and provides a solution by disabling the SSL enforcement and adjusting CHANGE MASTER parameters.
This article explains how MySQL stores and updates auto‑increment values, why gaps appear after failed inserts, unique‑key conflicts, transaction rollbacks or batch operations, and how different innodb_autoinc_lock_mode settings and MySQL 8.0 improvements can mitigate the issue.
This article explains the differences between MySQL logical and physical backups, introduces Percona XtraBackup, provides step‑by‑step installation commands, details common parameters, and demonstrates full and incremental backup and recovery workflows with practical code examples.
This article explains how Python decorators can abstract common functionalities such as logging, authentication, and performance monitoring in API automation testing, providing code examples, recommended use‑cases, and a complete demonstration that integrates logging and MySQL data storage to improve test efficiency and maintainability.
This article presents a comprehensive performance comparison of OceanBase with MySQL and TiDB, details step‑by‑step deployment of OceanBase (both single‑node and cluster), explains configuration and resource‑pool management, and shares practical migration techniques and troubleshooting tips for production workloads.
The article explains the structural differences between B+ trees and skip lists, compares their read‑write performance in MySQL and Redis, and shows why MySQL prefers B+ trees while Redis adopts skip lists for its in‑memory ZSET implementation.
This article provides a step‑by‑step guide to creating a mock service tool using Django, MySQL, and Vue, covering backend environment setup, model and view implementation, API routing, frontend Vue project configuration, component development, and how to run both servers for efficient front‑end development and testing.
This article reproduces a MySQL 8.0.32 InnoDB scenario where inserting a row that violates a unique index causes a next‑key exclusive lock on the primary‑key supremum record, explains the underlying implicit‑lock mechanism, walks through the relevant source‑code stack, and details the lock‑conversion and rollback steps under REPEATABLE‑READ isolation.
Bilibili’s relationship‑chain service, which handles follows, blacklists, whispers and mutual follows, migrated from a single sharded MySQL instance to an internal distributed KV store and introduced a three‑tier cache (memcached, Redis and a Bloom filter) plus automated hotspot routing, achieving near‑million QPS, lower latency, and preparing for multi‑tenant reuse.
This guide explains MySQL indexes—what they are, their data structures, advantages and drawbacks, when to create them, various index types, how to interpret EXPLAIN output, common pitfalls that cause index loss, and practical optimization techniques for queries, joins, ordering, grouping, and slow‑query logging.
This article breaks down MySQL's query optimizer cost model by detailing how full‑table‑scan cost is computed, covering the underlying formulas, required statistics, memory‑vs‑disk page ratios, and configurable cost constants with concrete code examples.
This tutorial explains how to create a basic real-time chat application by combining PHP backend logic, MySQL storage, AJAX polling, and WebSocket communication to enable instant message exchange in web browsers.
This weekly digest curates top industry technical articles, featuring MySQL XA transaction crash safety, SQL optimization case study, MySQL 9.0 new features, database sharding origins, vector database workings, and a Chinese graph database product guide, offering readers concise insights into current database developments.
This article explains MySQL's binlog, its role in master‑slave replication, and how businesses can harness it for data heterogeneity, cache synchronization, and task dispatch, illustrating practical middleware designs that transform raw changes into valuable services.
The SQLE 2.2305.0 release introduces SQL‑execution abort, webhook notifications, intelligent DB2 and MySQL slow‑SQL scanning, rollback statement generation for Oracle and PostgreSQL, plus numerous UI improvements and bug fixes, providing a more robust and automated database audit experience.
This article examines the evolution of database high‑availability architectures in large‑scale internet environments, covering basic direct‑connect setups, scale‑up/scale‑out sharding, master‑slave/master‑master with Keepalived, and advanced solutions such as MHA, Percona XtraDB Cluster, and MySQL Group Replication, plus smooth scaling steps.
This tutorial explains how to build a WeChat Mini Program that captures photos, sends them to a PHP backend for Baidu AI image recognition, and stores the results in a MySQL database, covering front‑end UI, JavaScript logic, and server‑side processing.
This article explains the definition, data structures, advantages, disadvantages, and appropriate usage scenarios of MySQL indexes, details primary, unique, single‑column and composite index types, analyzes common performance bottlenecks with EXPLAIN, demonstrates index creation, invalidation cases, query and join optimizations, and shows how to configure and use the slow‑query log.
This article details the design and implementation of a highly available, high‑performance membership system that serves over a billion users, covering Elasticsearch dual‑center HA, traffic‑isolated clusters, Redis caching, MySQL dual‑center partitioning, seamless migration, and refined flow‑control and degradation strategies.
This article explores MySQL character set implementation by analyzing system tables (CHARACTER_SETS and COLLATIONS) in information_schema and tracing internal source code structures like CHARSET_INFO, initialization logic, and client connection handling.
This article provides a detailed source‑code walkthrough of MySQL 8.0.33’s two‑phase commit process, explaining the Prepare and Commit stages, internal structures such as binlog queues, GTID generation, redo‑log flushing, sync handling, and the interactions between InnoDB and the binary log.
This guide walks through setting up MySQL master‑slave replication in Docker, configuring the instances, testing synchronization, then deploying Canal and integrating it with a Spring Boot 2.7.8 application to capture database changes and push them into Redis.
The article explains how MySQL auto‑increment primary keys, InnoDB invisible row_id, transaction Xid and trx_id, and thread identifiers behave when they reach their numeric limits, showing the resulting errors, data overwrites, and potential visibility bugs in long‑running instances.
This article explains why MySQL count(*) can become a performance bottleneck on InnoDB tables, compares different count() variants, and presents practical optimization techniques such as Redis caching, second‑level caches, parallel execution, reducing joins, and offloading analytics to ClickHouse.
This article explains MySQL index fundamentals—including classification, B+‑tree and hash structures, clustered and secondary indexes, single‑ and composite‑column indexes, covering indexes, index condition pushdown, index merge strategies, cost‑based index selection, common index‑invalidating scenarios, and practical guidelines for creating effective indexes.
This article explains how MySQL handles overflow of various auto‑generated identifiers—including table auto_increment, InnoDB row_id, Xid, trx_id, and thread_id—detailing the wrap‑around behavior, potential data loss, and the rare edge cases that can lead to bugs.
This article demonstrates how to ensure data consistency between Redis and MySQL using Python by implementing a synchronization mechanism that monitors MySQL binlog changes and updates Redis cache in real-time.
Facing a 50‑million‑row financial transaction table, the team implemented horizontal sharding with sharding‑jdbc, designed migration and pagination strategies, tackled multi‑datasource transaction issues, and outlined a phased rollout, providing practical insights for large‑scale MySQL table splitting and backend system stability.
The article explains why storing IPv4 addresses as VARCHAR is suboptimal, demonstrates that a 32‑bit unsigned INT perfectly fits an IPv4 address, compares storage size and query performance, and shows how to use MySQL's inet_aton and inet_ntoa conversion functions.
