This article explains how MySQL treats backslashes as escape characters in INSERT and SELECT queries, demonstrates the effect of different numbers of backslashes through practical tests, and clarifies the double‑escaping required when using LIKE patterns.
This article explores multiple MySQL techniques—triggers with a counter table, range partitioning, general tablespace limits, and application‑side buffering—to enforce a hard cap of 10,000 rows per table, discussing implementation steps, code examples, and the trade‑offs of each method.
This article examines how Oracle, PostgreSQL, and MySQL implement and use database sequences for primary keys, covering explicit calls, trigger‑based, DEFAULT, and AUTO_INCREMENT methods, version support, and the effects of INSERT, UPDATE, DELETE, and TRUNCATE operations, concluding with a comparative summary.
The article explains why storing IPv4 addresses as a 32‑bit unsigned integer in MySQL saves space and improves range‑query performance, outlines the trade‑offs such as readability, and demonstrates conversion using MySQL functions and Java code examples.
This guide walks through selecting a clustering strategy, installing Percona XtraDB Cluster (PXC) with Docker, configuring a five‑node cluster, adding HAProxy for load balancing, testing access, and handling node failures to achieve strong consistency and high availability for critical data.
This tutorial demonstrates how to reproduce a MySQL memory‑spike bug, monitor the process with Linux perf to capture mmap system calls, and analyze the resulting perf.out to identify which SQL statements trigger large memory allocations, while also discussing the method’s limitations.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors when storing true four‑byte UTF‑8 symbols, and shows how the newer “utf8mb4” charset provides full Unicode support, with historical context and migration guidance.
This article explains why MySQL master‑slave replication is used, how the binary‑log based mechanism works, compares statement‑based and row‑based modes, provides key configuration parameters, and offers practical solutions for reducing replication lag.
This tutorial demonstrates how to set up a CentOS 7.5 environment, install RabbitMQ, and use Spring Boot to create a distributed transaction system that ensures data consistency across order and dispatch services by leveraging RabbitMQ's confirm and ACK mechanisms, complete with database schema, configuration, and code examples.
This article explains the definitions of QPS (queries per second) and TPS (transactions per second) in MySQL, compares the various calculation methods—including Queries‑per‑second, Questions‑per‑second, and GTID‑based approaches—and recommends the most reliable ways to measure database load and transaction throughput.
This article traces two decades of application data architecture evolution, comparing traditional single‑system LAMP designs with modern multi‑component cloud‑native stacks, and offers practical guidance on scaling, component selection, CDC‑based data derivation, and cloud‑native implementations such as Tablestore.
The article examines the challenges of running MySQL in Docker containers, highlighting data‑security risks, performance bottlenecks, state‑management problems, and resource‑isolation limits, while also outlining scenarios where containerizing MySQL can still be appropriate.
This article explains the purpose and interpretation of each column in MySQL's EXPLAIN output, covering identifiers, select types, table sources, partition info, access types, possible keys, actual keys, key lengths, row estimates, filtering percentages, and extra execution details, with visual examples and code snippets.
This article compares MySQL‑based single and multi‑record stock deduction methods with a Redis‑based approach that uses Lua scripting and distributed locks, providing full Java implementation details and REST examples for high‑concurrency inventory management.
This guide introduces the sa‑plus framework—a SpringBoot‑based rapid‑development scaffold with an integrated code generator that can automatically produce backend, frontend, and API documentation code from database tables, and walks through its architecture, technology stack, module structure, quick start steps, and code‑generation features.
This guide explains how to set up a Percona XtraDB Cluster (PXC) using Docker containers, configure five-node replication with strong consistency, and implement HAProxy as a TCP load balancer to achieve high availability and balanced read/write traffic across the cluster.
The article explains why running MySQL in Docker containers can lead to data loss, performance degradation, and inadequate resource isolation, while also outlining specific scenarios where containerizing MySQL may still be appropriate.
This article explains the principles of master‑slave read/write separation, the MySQL binlog‑based replication process, its side effects, practical ways to mitigate replication lag, and how middleware or proxy layers can simplify database access in high‑concurrency environments.
This article provides a comprehensive guide to MySQL performance tuning, covering SQL pagination tricks, index design, join strategies, sorting, UNION handling, slow‑query logging, schema design, data‑type choices, normalization, and hardware considerations, all illustrated with concrete code examples.
This article compiles a comprehensive set of MySQL interview questions covering index types, the differences between MyISAM and InnoDB, the rationale for auto‑increment primary keys, sharding strategies, ACID properties, isolation levels, storage‑engine trade‑offs, normalization, index best practices, data types, performance tuning, locking mechanisms, B+Tree characteristics, and common SQL operations.
This article explains how MySQL handles internal temporary tables, detailing the evolution of storage mechanisms and related parameters from version 5.6 through 5.7 to 8.0, including the shift to TempTable, shared session temporary tables, memory‑mapped files, and configuration tips to avoid space issues.
This article presents a detailed performance benchmark comparing MariaDB 10.4.12 and MySQL 8.0.19 on an i7 Windows 10 system, covering single and batch inserts, indexed and non-indexed queries, and analyzes speed and memory usage differences between the two databases.
This article presents a comprehensive guide to improving MySQL query performance by addressing common issues 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 demonstrates how using WITH can simplify complex statements.
This article explains MySQL 8.0's new indexing features—including function indexes via virtual columns, Index Skip Scan for non‑leftmost predicates, descending indexes to avoid filesort, and invisible indexes for optimizer control—providing examples and usage guidelines.
This article demonstrates how to use Google’s tcmalloc library and the pprof tool to capture and analyze MySQL heap dumps, revealing memory allocations that performance_schema cannot track, and explains the experimental setup, data collection, and interpretation of the resulting memory allocation graphs.
This article provides a comprehensive technical overview of financial‑grade database disaster recovery, covering backup and recovery methods, MySQL replication options, automatic failover architectures, distributed transaction protection, and application‑level stress mitigation techniques.
This article dissects MySQL's InnoDB storage engine by first exposing the internal page layout, then explaining the clustered index organization, and finally detailing the slot mechanism used for intra‑page queries, while illustrating insertion strategies and space‑reclamation techniques.
Shopee’s Logistics Channel Service migrated from a shared‑cluster MySQL setup to dedicated clusters, added TiDB for transient tracking data, and ultimately adopted hash‑based sharding with separate order and tracking services plus an asynchronous compensation mechanism, enabling uniform data distribution, cross‑database consistency, six‑month archiving, and scalable growth without rebalancing.
This article explains why MySQL uses locks, describes table‑level, row‑level, and page‑level locking, outlines lock types such as shared, exclusive, and intention locks, and provides practical usage examples and common troubleshooting tips for InnoDB and other storage engines.
This article presents a complete MySQL interview guide, including a script to create and populate tables for students, teachers, courses, scores, and grades, followed by 33 common interview questions with detailed SQL solutions covering selection, aggregation, joins, subqueries, and ordering.
Using MySQL's ORDER BY with LIMIT can yield an unexpected row order because duplicate values in the sorting column cause nondeterministic results; adding a unique secondary column like id stabilizes the order, and the official documentation explains how LIMIT influences query planning and optimization.
MySQL Group Replication, introduced in 2016, delivers high‑availability, strong consistency, fault‑tolerant and scalable MySQL clusters through a Paxos‑based, plugin‑driven architecture that supports both single‑primary and multi‑primary modes, with detailed installation, configuration, deployment and testing guidance, while noting its limited current adoption due to ecosystem maturity.
This article explains the dangers of slow SQL in MySQL, walks through the query execution flow, details InnoDB storage engine architecture and index types, and provides practical strategies for indexing, query rewriting, and configuration to eliminate performance bottlenecks.
An in‑depth analysis of MySQL Group Replication (MGR) transaction certification failures, covering error symptoms, root‑cause investigation, replication‑group transaction set mismatches, and step‑by‑step recovery procedures with code examples and best‑practice recommendations to keep MGR clusters clean.
Kettle, a pure‑Java open‑source ETL tool (Chinese name "Shuihu"), enables efficient, installation‑free data extraction and synchronization, allowing you to copy tables or views from a SQL Server database directly into a MySQL table using a straightforward workflow.
MySQL 8.0 replaces duplicated server‑level and engine‑level metadata with a unified data dictionary stored in InnoDB, introduces a two‑level cache (local and shared) built on templated hash maps, and provides atomic DDL operations, dramatically improving metadata consistency, performance, and management simplicity.
This article demonstrates how to dramatically speed up importing tens of thousands of records into MySQL by evolving a naïve list‑to‑MySQL approach into grouped batch inserts and finally a multi‑threaded MyBatis solution, while also addressing packet size limits and configuration tweaks.
The article explains how the MySQL "Can't create a new thread (errno 11)" error on SLES12SP5 was diagnosed as a TaskMax limit issue and resolved by increasing the system‑wide DefaultTasksMax or setting a per‑service TasksMax, followed by a MySQL restart to apply the changes.
This article explains MySQL transaction isolation levels, the four concurrency problems (dirty write, dirty read, non‑repeatable read, phantom), how MVCC and version chains work, and the differences between READ COMMITTED and REPEATABLE READ using practical examples and diagrams.
This article explains how MySQL’s InnoDB engine stores data on disk and in memory, detailing the structure of data pages, the role of hidden columns, record linking, page headers, footers, and directory mechanisms that ensure efficient access and durability.
This article explains the concept of phantom reads, distinguishes them from non‑repeatable reads, critiques the ANSI SQL isolation‑level definitions, and shows how MySQL implements repeatable‑read using locks and MVCC to prevent both strict and loosely defined phantom anomalies.
This article explains how to retrieve sales statistics for multiple business units and their associated products from a MySQL table, compares several implementation approaches—including loop queries, OR concatenation, mixed filtering, and row‑comparison using MyBatis dynamic SQL—evaluates index usage, and provides the final recommended solution.
During a performance test a MySQL server showed one CPU core constantly at 99% while the others were idle, prompting a detailed investigation that checks I/O thread settings, multi‑core utilization, transaction locks, slow‑query logging, and index optimization to resolve the bottleneck.
This article explains MySQL's logging system—including binlog, redo log, and undo log—their purposes, formats, flush timing, usage in replication and recovery, and how they work together to ensure durability and atomicity in InnoDB transactions.
When using MySQL pagination with LIMIT together with ORDER BY, the second page can unexpectedly contain rows from the first page because MySQL 5.6's priority‑queue optimization performs an unstable heap sort, causing nondeterministic ordering for equal values and leading to duplicate results.
The article explains why storing IPv4 addresses as 32‑bit unsigned integers in MySQL saves space and improves range queries, outlines the pros and cons, shows MySQL conversion functions, and provides Java code for converting between string and numeric IP representations.
This article explains why a single MySQL instance becomes insufficient as data volume and concurrency grow, and walks through master‑slave replication, asynchronous and semi‑synchronous modes, group replication, and various high‑availability solutions such as MHA, MySQL InnoDB Cluster, and Orchestrator, while also covering read/write splitting techniques and practical implementation tips.
This article analyses why MHA reports a "bad new master" error during master‑slave switches, explains the internal candidate‑master and bad‑master selection algorithms, demonstrates how MySQL version differences affect cross‑version promotion through test scenarios, and provides detailed code excerpts to illustrate the decision process.
This tutorial walks you through installing Flask‑SQLAlchemy, configuring MySQL connections, defining models, handling various relationships, and performing full CRUD operations with code examples, enabling you to build a functional backend for a web application.
This article explains why MySQL queries become slow, how proper indexing and index‑pushdown can improve performance, discusses common index‑failure causes, and then introduces Elasticsearch and HBase as complementary search and storage solutions for large‑scale data, including practical usage tips and architectural considerations.
The article explains the drawbacks of strict relational normalization, introduces the concept of anti‑normalization (controlled redundancy) to simplify queries, demonstrates it with a set of employee, department, salary tables and four typical query scenarios, and shows how denormalizing the schema can dramatically improve MySQL query performance.
This article introduces several MySQL performance‑diagnostic utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run them, and describes how to interpret their reports to identify configuration and query‑level issues.
This tutorial walks through pulling the MySQL image, creating NFS‑backed PersistentVolume and PersistentVolumeClaim, deploying MySQL on Kubernetes, verifying the pod, inserting sample data, and confirming the data is stored on the NFS server.
The article describes how a bank built a Kubernetes‑based, containerized MySQL service platform (CDD) to improve database high availability, resource utilization, automated operations, and agile delivery by addressing network, storage, scheduling, and management challenges through custom networking, hybrid storage, scheduler extensions, and multi‑AZ deployment.
MySQL 8.0 introduces several relational‑database enhancements, including invisible indexes for performance tuning, persistent configuration via SET PERSIST, default utf8mb4 character set, support for Common Table Expressions, and window functions, each illustrated with practical SQL examples.
The article explains four classic cache‑update design patterns—Cache Aside, Read Through, Write Through, and Write Behind—detailing their workflows, consistency trade‑offs, and practical optimizations for high‑concurrency backend services that combine Redis with MySQL.
This article provides an in‑depth technical overview of Flink CDC 2.0, covering its CDC fundamentals, comparison of query‑based and log‑based approaches, the new lock‑free chunk algorithm, FLIP‑27 based parallel snapshot reading, performance benchmarks, documentation improvements, and future roadmap for stability and ecosystem integration.
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
This article explains the various types of auto‑increment identifiers in MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, thread_id—and discusses their maximum values, overflow behavior, and alternative solutions such as using Redis for external unique keys.
This article explains why MySQL 8's clone plugin may report a restart failure (error 3707), analyzes the role of the monitoring process, and provides systemd service configurations—including RestartForceExitStatus=16 and Environment=MYSQLD_PARENT_PID=1—to ensure automatic restarts succeed after clone operations while preventing unwanted restarts in other failure scenarios.
The article details Qunar's data synchronization platform that aggregates MySQL data into Elasticsearch, covering its architecture, component choices, ES5‑to‑ES7 migration, hot‑plugging, reindexing, high‑availability design, consistency guarantees, operational optimizations, and future roadmap.
This article details how to dramatically speed up the import of massive Excel fee‑record files in a Java backend by switching to EasyExcel, caching database lookups, using batch INSERT statements, and applying parallel streams while eliminating excessive logging.
The article describes how to design a MySQL table for product sales statistics, initialize large data sets, and compare several approaches—including loop queries, OR concatenation, mixed filtering, and row‑comparison techniques—ultimately selecting row‑comparison as the most efficient solution within MyBatis dynamic SQL constraints.
This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, row locks, and large‑table bottlenecks—then introduces ElasticSearch’s inverted‑index architecture and HBase’s column‑family storage, offering practical tips for performance tuning and hybrid solutions.
When a MySQL query uses LIMIT with a large offset on a table of millions of rows, the database must scan hundreds of thousands of index entries and corresponding clustered rows, causing massive random I/O; rewriting the query with a sub‑query join reduces I/O dramatically, cutting execution time from over a minute to under a second while also preventing buffer‑pool pollution.
This article explains why MySQL queries become slow, how proper indexing—including B+‑tree, left‑most prefix, index push‑down, and covering indexes—can improve performance, outlines common causes of index failure, and then introduces ElasticSearch search capabilities and HBase column‑family storage as complementary solutions for large‑scale data handling.
This article explains common MySQL performance pitfalls such as large‑offset LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, and condition pushdown, and shows how to rewrite each pattern using indexes, JOINs, UNION ALL, CTEs and early filtering to achieve execution times measured in milliseconds instead of seconds.
This article explains the different Cloud MySQL instance architectures, details asynchronous, semi‑synchronous, and strong synchronous replication, describes high‑availability failover mechanisms, outlines upgrade procedures, and covers binlog usage, rollback methods, slow‑query optimization, and storage fragmentation.
This guide introduces four practical MySQL performance‑tuning utilities—MySQLTuner, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run, and interpret their reports to identify configuration issues, memory overload, and slow‑query problems.
The article explains why MySQL can hit the max_connections limit, introduces the MySQL 8.0 administrative connection interface and its configuration variables, demonstrates how to create a privileged user and test connection limits with code examples, and offers best‑practice recommendations to prevent connection saturation.
This article explains how MySQL's InnoDB engine implements ACID properties by detailing transaction atomicity, consistency, isolation levels, lock granularity, MVCC version chains, undo/redo logs, buffer pool behavior, and the interplay between redo log and binlog to ensure durability and data integrity.
This guide explains step‑by‑step how to identify and delete duplicate records in MySQL tables, covering simple SELECT checks, handling MySQL’s update‑from limitation, and fast deletion techniques that keep one record per duplicate group.
The article details a step‑by‑step investigation of a MySQL server that ran out of memory due to massive slab cache consumption by inode and dentry objects, showing how Linux commands, scripts, and cache‑dropping techniques resolved the issue and revealed the root cause of excessive partition tables.
This article explains why a naïve Python script for deleting duplicate MySQL rows is too slow, demonstrates the MySQL error caused by deleting from the same table you query, and provides two pure‑SQL solutions: one that removes all duplicates and another that keeps a single row per duplicate key.
This article provides a step‑by‑step Bash script for installing, configuring, and starting MySQL 8.0 on a Linux system, including user creation, directory setup, permission handling, initialization checks, and service management, and ensures proper environment variables.
This guide introduces four popular MySQL performance‑tuning utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—provides download commands, usage examples, and detailed guidance on interpreting their reports to identify configuration issues and query bottlenecks.
The article records a video interview where the candidate answers technical questions on MySQL isolation levels, InnoDB indexing, locking, distributed locks, Redis sharding, PHP‑FPM architecture, RabbitMQ, AOP, Hyperf, and other backend topics, reflecting on the challenges faced during the session.
Facebook detailed its multi‑year journey from MySQL 5.6 to 8.0, explaining why it stayed on 5.6 for MyRocks development, the massive patch‑porting effort, replica‑set migration steps, adoption of row‑based replication, extensive automation testing, and the performance and compatibility issues uncovered along the way.
This article recounts the author’s experience building a simple Java web application for a university anniversary, covering planning, environment setup, documentation, database design, coding challenges with Spring Boot, Redis, and session handling, deployment on Alibaba Cloud, and the lessons learned about architecture, logging, and monitoring.
This article examines MySQL 8.0.18’s ANALYZE command, explains its role in updating table statistics, demonstrates a series of tests on a CentOS server—including data insertion and case‑sensitive table naming—and reveals a bug where uppercase table names prevent statistics from being refreshed.
This article conducts a hands‑on experiment to demonstrate how a transaction that acquires many InnoDB locks consumes buffer‑pool memory, leading to the "total number of locks exceeds the lock table size" error when buffer‑pool usage approaches its theoretical 75% threshold.
This article explains the internal execution process of MySQL's ORDER BY, compares full‑field and rowid sorting, shows how indexes, sort_buffer size and max_length_for_sort_data affect performance, and provides practical optimization techniques such as composite and covering indexes.
To prevent duplicate OpenID records caused by concurrent synchronization requests in the Fast App Center, this article analyzes the root cause, evaluates database‑level unique indexes versus application‑level distributed locks, and presents a Redis‑based lock implementation with cleanup procedures to ensure data consistency.
The article presents a detailed interview‑style walkthrough of MySQL InnoDB locking mechanisms, covering table‑ versus row‑level locks, shared and exclusive locks, intention locks, gap and next‑key locks, auto‑increment locks, predicate locks, and related SQL statements, illustrated with tables and code examples.
This article explains the importance of relational table design for database performance, introduces the six normal forms from 1NF to 5NF, and demonstrates step‑by‑step how to normalize employee and student tables using concrete MySQL examples to reduce redundancy.
This guide explains MySQL binary log fundamentals, the replication architecture, step‑by‑step configuration of master‑slave instances, essential commands, and troubleshooting tips for common replication problems such as lag, errors, and security settings.
This article provides a comprehensive tutorial on preparing the development environment, configuring backend and frontend components, and launching a Jeecg‑boot powered logistics warehouse management system built with SpringBoot, Vue, MySQL, Redis, and related tools.
This article experimentally determines the maximum number of characters a MySQL 5.7 VARCHAR column can hold under different character sets, explains the underlying row‑size and index‑key restrictions, and provides concrete CREATE TABLE examples for latin1, utf8, and utf8mb4.
This article analyzes MySQL 8.0.25 source code, detailing the server architecture, parser reconstruction, prepare/rewrite stages, the optimizer transformations, and the new hypergraph optimizer, while also comparing these mechanisms with PostgreSQL’s processing pipeline.
This article introduces MySQL 8.0's resource group feature, detailing its concepts, configuration commands, query hint usage, and the underlying implementation including new parser classes, platform APIs, runtime components, performance schema integration, and persistence mechanisms, with code examples throughout.
Storing IPv4 addresses as a 32‑bit UNSIGNED INT in MySQL saves space, enables faster range queries, and leverages built‑in conversion functions like INET_ATON/INET_NTOA, while requiring manual readability handling, and can be complemented by Java utilities for application‑level conversions.
This article explains which components (user processes, IO wait, system interrupts) consume CPU in MySQL, shows their impact on performance, and provides practical strategies to reduce CPU usage through IO optimization, query tuning, data modeling, caching, and hardware upgrades.
This article explains why MySQL's auto‑increment counter can be lost after a server restart for InnoDB tables, compares the behavior with MyISAM, demonstrates the problem with practical SQL examples, and shows how MySQL 8.0 fixes the counter logic.
This guide introduces a PHP utility that automatically assigns slow MySQL statements (running over 10 seconds) to a dedicated CPU core using MySQL 8.0 resource groups, explaining installation, configuration, execution, and verification steps, along with required system capabilities.
This guide introduces four powerful MySQL performance‑tuning utilities—MySQLTuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—detailing their installation, command‑line usage, and how to interpret the generated reports to optimize database configuration and query efficiency.
This tutorial explains how to connect a Python program to a MySQL database in three clear steps—installing the driver, using it with basic connection code, and optionally employing an ORM like Peewee for higher‑level database interactions—complete with ready‑to‑run examples.
This article explains several MySQL techniques—INSERT IGNORE, ON DUPLICATE KEY UPDATE, and REPLACE INTO—to efficiently insert large volumes of data while preventing duplicate primary‑key or unique‑key rows, and provides practical code examples for single and batch operations.
This article details the practical challenges encountered when migrating a MariaDB 10.3 database to MySQL 5.7.25, explains why certain defaults and engine-specific options cause import errors, and provides step‑by‑step fixes such as adjusting default expressions, removing Aria‑specific parameters, and configuring sql_mode.
This page aggregates over 300 curated interview question articles covering Java backend development topics such as Spring, MySQL, Redis, concurrency, data structures, and system design, organized by issue numbers to help candidates prepare effectively for technical interviews.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow, and the inner workings of the optimizer, cache, and storage engine, then offers practical schema, index, and specific‑query tuning techniques to boost database performance.
