This tutorial walks you through setting up a complete music‑streaming web application built with Vue on the front end and Spring Boot, MyBatis, and MySQL on the back end, covering features, installation, database configuration, and launch commands.
This article explains why MySQL 8.0’s JDBC driver processes executeBatch() calls as separate statements, demonstrates the root cause in the driver’s rewriteBatchedStatements logic, and shows how configuring rewriteBatchedStatements=true (and optionally allowMultiQueries=true) enables genuine batch execution.
This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.
This article investigates MySQL's recommendation against UUID primary keys by creating three tables—auto‑increment, UUID, and random (snowflake) keys—running insert and query benchmarks with SpringBoot/JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and security considerations of each key strategy.
This article evaluates several PHP approaches—including plain inserts, unsigned mode, MySQL transactions, file locks, and Redis queues—to prevent product inventory overselling under high concurrency, presenting test setups, results, and a final recommendation favoring Redis for its superior performance.
This article walks through the complete process of creating a custom SQL audit rule in the open‑source SQLE platform, covering API authentication, request construction, response analysis, rule insertion into the database, and the necessary Go backend code implementation.
This article describes a seat‑temporary‑leave management system built with Java Spring Boot and Vue, detailing its front‑end and back‑end modules, technology stack, functional pages, and provides a representative controller code snippet illustrating the back‑end API design.
This article explains how to use the nested set (left‑right) model to efficiently query, count, and manipulate hierarchical department data in MySQL, providing SQL examples for retrieving descendants, ancestors, leaf detection, as well as insert, delete, and JavaScript tree‑building techniques.
This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.
This comprehensive guide covers MySQL fundamentals including the three normal forms, permission tables, ACID properties, index design principles, SQL statement categories, sharding, deadlock resolution, isolation anomalies, view characteristics, SQL lifecycle, primary key choices, performance troubleshooting, replication, GTID, backup tools, and backup planning.
This article explores the precise moment a Spring @Transactional transaction is committed relative to a surrounding lock release, revealing that commits occur after the method finishes, and demonstrates how to verify this through source code inspection, debugging techniques, and proper lock‑usage to prevent overselling.
This article explains how to use the nested set model to efficiently query, count, and manage hierarchical department data in MySQL, covering recursive retrieval, leaf detection, insertion, deletion, and traversal techniques with practical SQL examples and JavaScript code for building tree structures.
The article analyzes why running MySQL in Docker containers can cause data loss, performance bottlenecks, state management problems, and insufficient resource isolation, while also offering scenarios and strategies where containerizing MySQL might still be feasible.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+Tree fan‑out, showing why a table with 100 million rows can still query quickly when the index depth remains low.
This article explains how to install and configure MySQL 8.0's component_log_filter_dragnet, describes its core filter fields and actions, and provides practical examples for customizing error‑log output such as dropping, throttling, setting, and unsetting log entries based on time, priority, error codes, symbols, and subsystems.
This article explains MySQL high‑availability concepts, defines HA, RPO and RTO, outlines common HA architectures such as master‑slave+VIP, MHA and MGR+Proxy, and details AutoHome's evolution from simple master‑slave setups to a container‑based MGR solution with automated failover and monitoring platforms.
This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.
This article describes how a finance team tackled a 500 million‑row MySQL transaction table by analyzing pre‑split issues, defining sharding goals, selecting sharding‑jdbc, addressing multi‑datasource transaction and pagination challenges, designing a hybrid data‑migration plan, and executing a three‑stage rollout to ensure system stability and performance.
This article details the design and implementation of a highly available, high‑performance membership system serving over a billion users, covering Elasticsearch dual‑center clusters, traffic isolation, Redis caching, MySQL migration, and fine‑grained flow‑control and degradation strategies.
This tutorial demonstrates how to add Create, Read, Update, and Delete functionality to a SpringBoot application using MyBatis XML mappers, configure the datasource in application.yml, and expose REST endpoints through a service and controller layer.
Sequel Pro is a user‑friendly MySQL client that lets you connect via standard, socket or SSH methods, configure connection details, test and save them, and then explore databases through panels for selecting schemas, viewing table structures, contents, relationships, triggers, table info, and running queries.
This article examines common reasons why MySQL queries become slow, including index misuse, insufficient connections, and small InnoDB buffer pools, and provides practical solutions such as profiling, using EXPLAIN, adjusting connection limits, and tuning buffer pool size.
Explore MySQL's comprehensive index guide, covering basic concepts, B‑tree and B+‑tree structures, storage engine differences, index types, optimization strategies, and practical tips for designing effective primary, secondary, and composite indexes to boost query performance and reduce I/O overhead.
This comprehensive guide explores MySQL's architecture, explains how indexes work, details the use of EXPLAIN to analyze query execution, and provides practical optimization strategies—including index creation, avoiding common pitfalls, and improving single- and multi-table queries—to dramatically boost database performance.
This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.
This beginner-friendly tutorial walks through creating a Spring Boot project, adding MySQL and MyBatis dependencies, configuring the application.yml file, and implementing UserMapper, User, and UserController classes to demonstrate a complete MySQL query demo.
This guide walks through systematic MySQL query optimization—from identifying slow statements via logs and EXPLAIN analysis, to profiling, tracing, and applying targeted fixes such as index tuning, query rewrites, and handling special cases like left‑most matching, implicit conversion, large pagination, and IN‑order‑by pitfalls.
This article details the design and implementation of a highly available, high‑performance membership system serving billions of users, covering Elasticsearch dual‑center clusters, traffic‑isolated architectures, deep ES optimizations, Redis caching with distributed locks, dual‑center MySQL partitioning, migration strategies, abnormal account handling, and future fine‑grained flow‑control and degradation policies.
The article explains why MySQL's HEX and UNHEX functions sometimes return hexadecimal representations instead of plain strings, demonstrates the issue with example queries, clarifies the meaning of related functions, and shows how to disable the client’s binary‑as‑hex option to get correct output.
This article breaks down how MySQL's InnoDB engine guarantees the four ACID properties—atomicity, consistency, isolation, and durability—by leveraging undo and redo logs, the binary log, hidden transaction metadata, and MVCC with ReadView, illustrating each mechanism with concrete examples.
This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.
This article explains why a naïve Python‑based row‑deletion approach is slow for large MySQL tables and provides step‑by‑step SQL techniques—including identifying duplicate names, handling MySQL’s update‑from‑same‑table limitation, and deleting duplicates while preserving a single record per group—complete with executable code examples.
This article explores memory management concepts from both operating system and MySQL perspectives, covering virtual vs physical memory, process memory usage analysis using tools like top and /proc, and experimental verification of memory allocation behaviors.
This article evaluates single‑threaded bulk‑insert performance in MySQL by testing various batch sizes, analyzes memory usage and transaction merging, and provides practical recommendations for efficiently generating millions of records.
This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.
This comprehensive guide explains MySQL's architecture, storage engines, and the critical role of indexes, walks through the SQL parsing process, details how to read and interpret EXPLAIN output, and provides practical optimization examples for single‑table and multi‑table queries.
GitHub experienced multiple service outages over recent weeks due to resource contention in its MySQL1 cluster, leading to prolonged downtimes, and the company disclosed detailed timelines, root causes, and ongoing mitigation measures such as load audits, traffic shifting, and infrastructure scaling to prevent future incidents.
This article presents a detailed experiment comparing graceful and kill‑9 shutdown methods on a MySQL 5.7 semi‑synchronous master‑slave cluster, analyzes their impact on data consistency, and outlines the official shutdown sequence with practical observations and recommendations for production environments.
This article presents a Java Spring Boot vaccination management system that supports user registration, vaccine record handling, case history, nucleic‑acid test reporting, travel tracking, risk‑area management, and epidemic‑prevention knowledge, detailing its roles, technology stack, database design, UI screenshots, and core backend source code.
This article compiles essential MySQL interview topics, covering normal forms, privilege tables, ACID properties, SQL statement categories, sharding, deadlocks, isolation anomalies, view characteristics, statement lifecycle, primary key choices, CPU troubleshooting, replication, GTID, backup tools, and backup planning strategies.
This article walks through optimizing a slow MySQL batch import by first inserting a list directly, then using grouped inserts, and finally applying multithreaded batch processing with MyBatis, showing code examples and practical tips to reduce import time from over a minute to under ten seconds.
Learn how to identify and fix slow MySQL queries by analyzing slow‑query logs, using EXPLAIN, profiling, and optimizer trace, understanding index types and extra flags, and applying practical case‑by‑case optimizations such as index tuning, query rewriting, pagination strategies, and handling large data sets.
This article explains MySQL's various character sets and collations, describes important system variables such as character_set_client and character_set_connection, demonstrates how to query supported encodings, and provides practical examples for avoiding and fixing garbled text caused by mismatched encodings.
This article walks through generating a 10‑million‑row MySQL table, inserting data with a fast batch script, measuring plain LIMIT pagination performance, and applying several optimization techniques—including sub‑queries, ID‑range filtering, and column selection—to dramatically reduce query time on large data sets.
This article explains the usage of four MySQL 8.0 window functions—first_value, last_value, nth_value, and ntile—by describing their purpose, the required window frame syntax, and providing concrete SQL examples with sample data.
This article walks through why SQL queries become bottlenecks as data grows and presents a step‑by‑step guide—including slow‑query analysis, EXPLAIN interpretation, profiling, optimizer tracing, index tuning, and scenario‑specific tricks—to dramatically improve MySQL performance.
This article details the design and implementation of a high‑availability membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis multi‑center caching, MySQL dual‑center partitioning, data migration strategies, and refined flow‑control and degradation mechanisms to ensure stable, low‑latency service under massive concurrent load.
This article explains the purpose and principles of database indexes, compares various tree structures such as binary search trees, AVL trees, B‑Trees and B+Trees, and shows how MySQL uses B+Tree indexes to minimize disk I/O and boost query performance.
This article provides a set of Bash scripts for Linux server administration, including DDoS IP blocking, email alert configuration, MySQL backup (single and multi-loop), Nginx log rotation and analysis, network interface traffic monitoring, system initialization, and disk usage monitoring across multiple servers.
This article provides a step‑by‑step tutorial on deploying a DBLE cluster, configuring sharding.xml, db.xml and user.xml, creating test tables, installing DTLE, performing full MySQL‑to‑DBLE data migration, then switching to GTID‑based incremental synchronization with detailed troubleshooting tips and best‑practice recommendations.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for dynamic sets of business institutions and their products in a single indexed query.
This guide walks through essential MySQL performance tuning methods, from identifying slow queries via logs and EXPLAIN analysis to interpreting Extra information, using profiling and optimizer trace, and applying concrete index, query, and schema adjustments for common scenarios such as left‑most matching, implicit conversion, large pagination, IN‑order‑by, and complex queries.
The article details a comprehensive high‑availability solution for a large‑scale membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster designs, deep ES optimizations, Redis caching with consistency safeguards, MySQL partitioned migration, and fine‑grained flow‑control and degradation mechanisms.
This guide explains how to prevent data loss in MySQL by enabling row‑based binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements using binlog or GTID, and leveraging flashback tools such as binlog2sql and MyFlash.
This article details the design and implementation of a high‑performance, highly available membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis cache strategies, MySQL dual‑center partitioning, seamless migration, abnormal member handling, and fine‑grained flow‑control and degradation policies.
This article analyzes a production incident where massive OFFSET values in MySQL pagination caused severe query slowdown, demonstrates the problem with simulated data, benchmarks the impact, and presents three practical solutions—including index‑covering subqueries, remembering the last primary key, and limiting offset size—to dramatically improve performance and protect the database.
This article presents a detailed case study of optimizing a massive MySQL table using time‑based partitioning, describing the problem background, partition design, migration scripts, performance benchmarks, and ongoing maintenance procedures for a ten‑year data archive.
The article analyses why indiscriminately killing slow MySQL queries is risky, enumerates the drawbacks of common kill‑based approaches, and proposes more reliable designs such as registration, signature‑based killing, and source‑code modifications to safely mitigate query‑induced database avalanches.
This article explains InnoDB's lock system in MySQL, covering lock granularity, intent, shared, exclusive and auto‑increment locks, row‑lock types, the underlying C++ structures, lock mode and type encoding, and the compatibility and strength matrices that govern lock acquisition and waiting.
This article introduces Change Data Capture (CDC), compares query‑based and log‑based CDC, explains Debezium and ClickHouse, and provides step‑by‑step Flink CDC and Flink SQL CDC examples—including full Java code—to stream MySQL binlog changes into ClickHouse for real‑time analytics.
This guide explains the Percona XtraDB Cluster (PXC) architecture, its advantages and limitations, and provides step‑by‑step commands for removing MariaDB, opening firewall ports, disabling SELinux, downloading packages, configuring MySQL, bootstrapping the first node, adding additional nodes, and verifying the cluster status.
This article explains how MySQL 8.0.27 adds multi‑factor authentication (MFA) via the new authentication_policy system variable, demonstrates configuring the variable, creating users with single or multiple authentication plugins, installing external plugins, and verifies MFA behavior with practical Docker‑based examples.
This article provides a comprehensive guide to MySQL performance improvement, covering pagination, index, join, sorting, UNION, slow‑query‑log, schema design, and hardware optimizations with concrete SQL examples, best‑practice tips, and cautionary notes for real‑world deployments.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales data for multiple business units and their associated products in a single indexed query.
Although MySQL’s int type always occupies 4 bytes, the number in parentheses (e.g., int(1) or int(10)) does not restrict its range; only when combined with ZEROFILL does it affect display formatting, as demonstrated by inserting the maximum unsigned int value and observing zero‑padded outputs.
This tutorial walks through creating a MySQL database, setting up a backup user, performing full and incremental backups with XtraBackup, compressing and transferring backup files, and restoring the database by preparing logs, copying back data, fixing permissions, restarting MySQL, and verifying the restored data.
This article explains the classification of InnoDB indexes into clustered and secondary types, describes how they are stored and accessed, and introduces optimization concepts such as back‑to‑table queries, covering indexes, and index pushdown to improve MySQL performance.
This post shares a comprehensive Java interview handbook containing over 1,000 questions covering MyBatis, Zookeeper, Dubbo, Redis, MySQL, concurrency, Spring, Kafka, micro‑services and more, hosted on GitHub with 30K+ stars, and explains how to obtain the latest PDF via a WeChat QR code.
This article explains MySQL high availability concepts, how master‑slave replication works, how to measure and mitigate replication lag, and compares reliable‑first and availability‑first failover strategies with practical experiments on binlog formats.
This article explains the basics of MySQL master‑slave replication, illustrates how read/write splitting works to alleviate performance bottlenecks, and shares practical test results showing reduced latency on the master and improved overall throughput when directing reads to replica servers.
This article explains the four SQL language categories (DDL, DML, DCL, TCL), various backup and recovery types, common MySQL backup methods, and flashback recovery techniques, providing detailed guidance and tool comparisons for database administrators.
This guide walks you through preparing the environment, adding the RadonDB MySQL operator repository, deploying the operator, installing a MySQL cluster, and verifying the deployment on KubeSphere, providing all necessary commands and screenshots for a successful high‑availability setup.
This guide demonstrates how to perform a full MySQL backup, create incremental backups, handle accidental database deletions, and restore the data using Percona XtraBackup on a Linux server, including detailed command-line steps and permission adjustments.
This article explores how to query sales data for multiple business units and selected products in MySQL, comparing loop queries, OR concatenation, mixed filtering, and SQL‑92 row‑value comparison, ultimately recommending the row‑comparison technique for efficiency and maintainability.
This article explains how a large‑scale membership platform achieved over 20,000 TPS with high performance and zero downtime by deploying dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis caching with distributed locks, and a seamless MySQL migration and HA strategy.
This article explains MySQL master‑slave read/write separation, the binlog and relay log mechanisms, master‑slave delay, and how different transaction isolation levels affect whether the master or slave may return stale data, illustrated with practical examples and SQL snippets.
The article investigates why a MySQL server shows a large number of TCP connections in TIME_WAIT state, examines OS and MySQL logs, tests sysctl adjustments, captures network packets to reveal short‑lived client connections, and concludes that using persistent connections can eliminate the issue.
This article introduces the fundamentals of Flink CDC, explains its application scenarios and underlying technologies, compares query‑based and log‑based CDC, showcases open‑source solutions, and provides detailed Java and SQL examples for building real‑time ETL pipelines with MySQL and Flink.
This article explains the differences between TLS and SSL, details the TLS handshake and certificate mechanisms, and provides step‑by‑step guidance for configuring encrypted connections on MySQL servers, clients, and JDBC drivers.
The article explains MySQL InnoDB lock scopes and modes—including record, gap, next‑key and insert‑intention locks—illustrates a deadlock caused by concurrent inserts on a distributed lock table, and recommends limiting concurrency, sharding by business key, and splitting lock operations into smaller sub‑transactions to avoid such deadlocks.
This article delves into MySQL’s internal logging mechanisms—redo log, undo log, and binlog—explaining their structures, purposes, and interactions, and then explores transaction concepts such as dirty reads/writes, non‑repeatable reads, phantom reads, isolation levels, and the MVCC implementation that ensures data consistency.
The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.
This weekly digest summarizes the latest MySQL 8.0 enhancements, replication delay case, user connection memory limits, Oracle‑to‑MySQL synchronization, new SQLE release, DBLE middleware developments, bug fixes, community Q&A, recruitment notices, and upcoming plans, providing concise technical insights for the open‑source community.
This article explores MySQL's client‑server interaction, connection methods, query parsing and optimization, InnoDB storage engine design, data page layout, BufferPool structures, and the free, flush, and LRU linked lists, including their challenges and optimization techniques.
This article provides a step‑by‑step guide on configuring DTLE 4.22.01.0 to perform Oracle‑to‑MySQL incremental data synchronization, covering type mapping, DML/DDL support, environment setup, job creation, data verification, and handling of compatibility limitations.
The article compares MySQL’s relational tables with MongoDB’s schema‑free documents, detailing equivalent concepts, CRUD syntax, and benchmark results that show MongoDB achieving higher write and query throughput, easier schema evolution, and built‑in sharding, recommending it for new agile projects while suggesting MySQL remain viable for legacy systems.
This article explains why traditional B‑Tree indexes fail for keyword searches, introduces the concept of inverted indexes, shows how to create and use MySQL InnoDB full‑text indexes with MATCH‑AGAINST in various modes, and covers index maintenance and query‑expansion techniques.
MySQL 8.0.28 introduces the connection_memory_limit parameter and related settings that let administrators cap the memory used by each client connection, and this article demonstrates how to configure the limits, test them with sample queries, and observe the behavior for regular and privileged users.
By migrating a latency‑heavy MySQL query to AnalyticDB, employing binlog‑based real‑time sync, refining composite indexes, adding clustering and filter hints, and batching inserts, the team cut execution time from about two seconds to roughly one hundred milliseconds, achieving over 99 % of queries under one second.
This article explains various methods for scheduled MySQL backups, covering mysqldump command usage, example backup commands for whole or specific databases, a Bash script to retain a fixed number of backups, and configuring cron jobs to automate the backup and restoration processes.
The MHA Re-Edition tool revives the discontinued MHA manager for MySQL, adding GTID‑based failover, password‑only SSH authentication, lightweight binaries, VIP migration, WeChat alerts, remote‑card reboot, and detailed configuration options, with step‑by‑step deployment instructions and sample app1.cnf parameters for high‑availability clusters.
The article explains how a MySQL 5.6 master‑slave setup experienced growing replication lag on an off‑site replica, how the issue was traced to page‑compression on a large table using show slave status, perf and pstack, and how disabling compression resolved the delay.
This article investigates MySQL's recommendation to use auto_increment primary keys instead of UUID or random snowflake IDs by comparing table designs, running SpringBoot‑based insert benchmarks, analyzing index structures, and discussing the performance trade‑offs and practical drawbacks of each approach.
This article explains how IN and EXISTS work in MySQL, compares their execution strategies, shows example queries, and provides guidance on when to use each based on the relative sizes of the involved tables.
This article explains the purpose, syntax rules, and practical examples of the UNION operator in MySQL, covering distinct and ALL modes, ORDER BY restrictions, new TABLE and VALUES usages introduced in MySQL 8.0.19, and differences from MySQL 5.7.
This weekly community bulletin summarizes recent MySQL problem reports, new features and bug fixes in the DBLE distributed middleware, DTLE data‑transfer component, and SQLE SQL audit tool, while also highlighting upcoming development plans and inviting community feedback.
The SQLE 1.2202.0 release introduces new user group and permission management, scoring for audit results, expanded support for MySQL, Oracle, Java application SQL capture, Db2 audit plugins, additional MySQL audit rules, UI optimizations, and several critical bug fixes.
This article recounts the author's experience of identifying and solving various backend interface performance bottlenecks—including slow MySQL queries, complex business logic, thread‑pool and lock design flaws, and machine issues—by applying targeted optimizations such as pagination redesign, indexing, concurrency, and caching.
This article analyzes why a MySQL query with a large LIMIT offset can take seconds to run, demonstrates the underlying index and buffer‑pool behavior, and shows how rewriting the query with a sub‑select of primary keys reduces execution time from over 16 seconds to under half a second.
A mysterious issue where some MySQL queries return only one row after executing SET SQL_SELECT_LIMIT=1 is traced to the MySQL driver’s Statement.setMaxRows method, which sets a session variable that can pollute pooled connections, leading to unexpected row limits.
