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.
This article explains how to customize the OBClient client’s welcome output by installing dependencies, cloning the OceanBase OBClient source, editing specific source files to replace default strings, recompiling the client, installing the new package, and verifying the changed prompts.
This technical guide details a real‑world Kubernetes deployment, covering a 10‑node cluster specification, master‑worker architecture, containerized frontend and backend services, MySQL persistence, load‑balancing, step‑by‑step deployment procedures, and monitoring setup.
This tutorial explains how to build a PHP-based single sign‑on system using a main login application, a shared authentication service backed by MySQL and Redis, and multiple external applications, providing complete code examples for database setup, login handling, session sharing, and logout.
This article explains how to enable fuzzy search on encrypted sensitive fields such as phone numbers and ID numbers in a MySQL database by using Spring Boot, AOP, tokenized ciphertext mapping tables, and encrypted keyword queries to balance security and query performance.
This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.
This article details a November 2022 database outage caused by a sudden CPU and load surge, explains how the team diagnosed the issue, outlines the emergency steps taken, and shares practical SQL performance optimization recommendations to prevent similar incidents.
This article walks through creating a 10‑million‑row MySQL table, measuring pagination performance with LIMIT, and applying practical optimizations—such as index‑based subqueries, ID‑range queries, and column selection—to dramatically reduce query latency on large data sets.
This article introduces Sequel Pro, a macOS GUI client for MySQL and MariaDB, explains how to configure standard connections with required fields, describes the main interface features such as database selection, table structure and content viewing, and highlights additional tools like Query and Table Info.
This article compares B+ trees and skip lists, explaining their structures, insertion and search complexities, and why MySQL chooses B+ trees for disk‑based indexing while Redis prefers skip lists for in‑memory sorted sets, highlighting trade‑offs in read/write performance and I/O costs.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.
This article explains how MySQL's lock_time metric is calculated by summing table‑level and row‑level lock wait times, details the InnoDB implementation, shows source‑code paths and functions involved, and clarifies why lock_time may appear small even when query execution is long.
The article investigates a MySQL 8.0 instance where many sessions were stuck in the executing state, analyzes InnoDB thread‑concurrency settings, stack traces and source code to reveal that exhausted InnoDB tickets caused nanosleep‑based blocking, and proposes configuration and SQL‑optimisation solutions.
This tutorial walks through setting up an Ubuntu 16.04 vulnerable environment, gathering information, uploading a webshell via MySQL into outfile or log injection, establishing a reverse shell with Metasploit, and finally exploiting CVE‑2021‑4034 for kernel privilege escalation, while also covering post‑exploitation persistence techniques.
This tutorial walks through the rapid installation of Nacos in MySQL mode, the configuration of Seata 1.5.0, the creation of required database tables, the development of three micro‑services (stock, order, account) with Spring Cloud Alibaba, and the verification of global transaction rollback using a sample purchase scenario.
This article explains how MySQL handles auto‑increment primary keys, InnoDB’s internal row_id, transaction Xid and trx_id counters when they reach their maximum values, the resulting primary‑key conflicts, data overwrites, and a theoretical dirty‑read bug caused by counter wrap‑around.
The SQLE 2.2305.0-pre3 release introduces Webhook-based work‑order status notifications, Oracle rollback statement generation for the enterprise edition, updated documentation links, a detailed feature list, bug fixes, and references to previous versions and further reading resources.
This article demonstrates multiple strategies for inserting 300,000 rows into a MySQL table using MyBatis and JDBC, comparing batch‑insert, single‑row loops, packet size limits, transaction handling, and performance tuning techniques such as batch size, waiting intervals, and index management.
This article explains MySQL’s various lock types—global, table, metadata, intention, and row‑level—and shows how SELECT statements acquire different locks under each isolation level, with examples for existing and missing rows, range scans, unique and non‑unique indexes, and full‑table scans.
This article introduces a Python‑based online movie website built with the Flask framework, detailing its project structure, front‑end using HTML5 and Bootstrap, back‑end in Python 3, MySQL database integration, deployment instructions, and includes screenshots of the UI and architecture.
This article shows how a concise 10‑line shell script, created with ChatGPT, can continuously capture MySQL QPS, TPS, and connection counts and append the metrics to a log file for performance monitoring.
This article explains how to identify when a MySQL DDL operation is blocked, locate the blocking session using the sys.schema_table_lock_waits view, and resolve the issue by enabling necessary instruments and killing the appropriate connection, with examples for MySQL 5.7 and 8.0.
This tutorial walks through the complete environment setup, including rapid installation of Nacos with MySQL mode, configuring Seata 1.5.0 for distributed transactions, creating the required MySQL tables, building micro‑services with Spring Boot, defining Feign clients, enabling global transactions, testing the workflow, and noting important version compatibility issues.
This article recounts how a finance team tackled a 50‑million‑row MySQL table by analyzing system bottlenecks, defining splitting goals, selecting sharding‑jdbc, addressing multi‑datasource transactions and pagination, designing data migration strategies, and executing a staged rollout to ensure stability and performance.
This article examines why long‑running SELECT COUNT(*) statements are not recorded in MySQL's slow query log, analyzes the relevant source‑code functions, demonstrates debugging with gdb, identifies the min_examined_row_limit setting as the root cause, and provides practical solutions and bug‑report details.
This article presents a comprehensive engineering case study of a high‑traffic membership system, detailing the dual‑center Elasticsearch high‑availability design, traffic‑isolated three‑cluster ES architecture, Redis caching strategy, dual‑center MySQL partitioning and migration plan, abnormal member relationship governance, and future fine‑grained flow‑control and downgrade policies.
Through hands‑on experiments with a 10‑million‑row MySQL 5.7 table, this article shows how standard LIMIT pagination becomes slower as offset and data volume grow, then presents practical optimizations—sub‑queries, ID‑based ranges, and column selection—to dramatically improve query performance.
This article details how a finance team tackled a 50‑million‑row MySQL transaction table by designing a horizontal sharding strategy with Sharding‑JDBC, addressing performance bottlenecks, multi‑source transaction handling, pagination across shards, and a phased data migration and rollout plan.
The article explains why adding a LIMIT clause to MySQL DELETE statements is a recommended habit, detailing performance gains, reduced locking, safer error handling, and practical execution strategies for large‑scale deletions.
The article details how a finance team tackled a 50‑million‑row MySQL transaction table by analyzing the pre‑split problems, defining split goals, choosing sharding‑jdbc, addressing multi‑datasource transaction and pagination issues, designing a phased migration and rollout plan, and summarizing lessons learned.
After upgrading from MySQL 5.7 to 8.0, a client experienced a flood of slow queries despite low system load; the root cause was a change in the sys.innodb_lock_waits view that now relies on performance_schema.data_locks, whose large lock table holds a global mutex and blocks other transactions, a problem solved by adding an index to reduce lock volume.
This guide demonstrates how to decouple data change logging from business logic in a SpringBoot application by leveraging MySQL binlog monitoring with Canal, forwarding change events through RabbitMQ, and persisting both new and old record states using Docker‑compose, configuration files, and Java client code.
An engineer troubleshoots a .NET application login freeze by capturing a dump with WinDbg, discovers the thread blocked in MySQL SSL handshake, verifies the MySQL driver version, and resolves the issue by disabling SSL via the connection string, illustrating effective debugging without source code or logs.
This article explains how to obtain distinct code entries together with their cdate and ctotal fields in MySQL, covering the use of DISTINCT, GROUP BY, handling ONLY_FULL_GROUP_BY mode, and alternative solutions such as GROUP_CONCAT with DISTINCT.
This article examines the challenges of data consistency verification during database migrations, reviews existing tools like pt-table-checksum, sync‑diff‑inspector and gt‑checksum, outlines essential capabilities, and proposes a detailed workflow and future enhancements for a versatile, low‑impact consistency checking solution.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures the latency of ordinary LIMIT pagination, analyzes the impact of offset size and result set size, and presents practical optimization techniques such as sub‑query pagination and ID‑range filtering to dramatically improve query speed.
This article walks through creating a 10‑million‑row MySQL table, measuring pagination query times under different offsets and result sizes, and presents practical optimizations such as sub‑queries, ID‑range filtering, and column selection to dramatically improve performance while explaining why using SELECT * is discouraged.
This article summarizes the key technical questions and answers from a MiHoYo spring recruitment interview, covering Java string handling, synchronized locks, exception types, operating‑system IPC mechanisms, MySQL indexing and isolation levels, as well as DNS resolution and TCP packet issues.
This article details the design and implementation of a high‑performance, highly available membership platform serving billions of users, covering dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis caching strategies, MySQL dual‑center partitioning, seamless migration, and fine‑grained flow‑control and degradation mechanisms.
This article provides a comprehensive 15‑question guide on MySQL indexes, covering their definition, various types across data‑structure, physical and logical dimensions, situations where indexes fail or are unsuitable, B+‑tree advantages, search process, covering indexes, left‑most prefix, index push‑down, adding indexes to huge tables, using EXPLAIN to verify index usage, hash versus B+‑tree differences, pros and cons, and the distinction between clustered and non‑clustered indexes.
The SQLE 2.2305.0‑pre1 release introduces a MySQL‑instance‑based smart scan for slow queries, adds configurable data source defaults, fixes several MySQL rule and workflow bugs, and provides comprehensive documentation, download links, and usage examples for both community and enterprise editions.
This article demonstrates how to achieve cache‑database consistency by updating MySQL first, deleting Redis cache asynchronously via Canal, publishing change events to RabbitMQ, and handling acknowledgments manually in a Spring Boot application, complete with configuration steps and troubleshooting tips.
This guide explains how to pinpoint the MySQL statements that use the most CPU by mapping OS thread IDs to MySQL sessions, querying performance_schema and information_schema, and reviewing the execution plan to identify optimization opportunities.
This article explains why SQL queries become slow, how to capture slow queries using MySQL's slow query log, and provides detailed step‑by‑step methods for analyzing execution plans, profiling, optimizer tracing, and I/O parameters to identify and resolve performance bottlenecks.
This comprehensive guide covers MySQL basics such as join types, normal forms, data types, indexing strategies, lock mechanisms, transaction isolation levels, replication, sharding, and practical optimization techniques for handling large tables and high‑traffic workloads.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This article explains how to export large MySQL datasets in SpringBoot by streaming data directly to CSV, avoiding full‑memory loads that cause OutOfMemoryError, and provides complete JPA and MyBatis implementations, performance testing, and practical code examples for production use.
This guide walks through a real‑world MySQL master‑slave sync failure caused by a corrupted relay log, explains the replication mechanics, shows how to analyze slave status, use GTID to skip the bad transaction, and fully restore synchronization with step‑by‑step commands and screenshots.
This article compares four common approaches for synchronizing MySQL data to Elasticsearch—synchronous dual write, asynchronous dual write via message queues, scheduled tasks, and binlog‑based data subscription—detailing their advantages, drawbacks, implementation steps, and tool choices such as Canal, Maxwell, and Python‑MySQL‑Replication.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains why MySQL slave lag is a critical issue, compares the official parallel replication schemes—including Commit‑Parent‑Based, Lock‑Based, and WRITESET—details their internal mechanisms, presents benchmark results, and provides the exact configuration parameters needed to enable high‑performance parallel replication.
This tutorial shows how to quickly set up a full PHP 8.2, Nginx, MySQL 8, Redis 6, and Composer environment on Ubuntu 20.04 by configuring Alibaba Cloud apt mirrors, installing required packages, adjusting configurations, and verifying the stack, all in roughly twenty minutes.
This article explains how MySQL handles queries that involve two separate indexed columns, demonstrates the index_merge execution plan with real‑world examples, details the three index‑merge strategies (intersect, union, sort_union), and shows how to control or disable the feature for better performance.
This guide explains why traditional mysqldump and file‑based methods fail for massive cross‑database sync, introduces Alibaba’s open‑source DataX middleware, details its framework and plugin architecture, walks through installation on Linux, shows how to configure MySQL source and target, and demonstrates both full and incremental data synchronization with practical JSON job examples.
This article walks you through downloading the MySQL 5.7 Docker image, configuring IPv4 networking, launching the container with proper volume mounts, troubleshooting common startup errors, verifying the setup, and discussing production‑grade considerations such as data persistence, memory isolation, and scaling.
This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.
This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.
This article walks through MySQL 8.0.32 source code to explain how the server builds a tree for a WHERE clause, evaluates each condition using classes such as Item_cond_and, Item_cond_or, Item_func_gt, and Item_func_eq, and ultimately decides whether a row satisfies the query.
A detailed postmortem shows how a poorly optimized SQL query, triggered by a cache‑removal update, saturated MySQL's CPU and connection pool, causing cascading failures across a sales‑critical system despite low overall traffic.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This article explains what ClickHouse is, why a large MySQL table caused multi‑minute queries, and how migrating the data to ClickHouse using Docker, CREATE TABLE AS SELECT, and a lightweight sync tool reduced query time to under one second, while highlighting pitfalls and performance reasons.
