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.
This article explains what constitutes a MySQL slow query, why they cause failures, defines quantitative metrics such as micro‑average and macro‑average to assess severity, outlines target goals, presents concrete optimization examples, and shares operational practices for ongoing slow‑SQL governance.
This article provides a comprehensive collection of MySQL command-line instructions for exporting databases or tables, importing data, managing databases and tables, performing DDL and DML operations, handling field types, and configuring user privileges, all illustrated with practical code examples.
This article explains why a naïve Python script for deduplicating millions of rows is too slow, then walks through a series of MySQL queries—including how to identify duplicate names, avoid the 1093 error, and delete duplicates while keeping a single representative row—demonstrating fast, reliable cleanup of large tables.
This comprehensive guide walks you through MySQL performance tuning, covering how to monitor server status, identify slow queries, use EXPLAIN for execution plans, create and manage various index types, and apply practical SQL optimizations such as import tricks, insert batching, efficient GROUP BY/ORDER BY, and pagination improvements.
This article provides a comprehensive collection of MySQL optimization techniques—including storage engine characteristics, data type selection, index design, and query rewriting—targeted at teams without a dedicated DBA, using MySQL 5.5 and InnoDB as the reference platform.
This article introduces a curated list of open‑source MySQL operational tools—including online DDL changers, backup and restore utilities, load‑testing frameworks, flashback solutions, slow‑query analyzers, replication consistency checkers, audit platforms, and graphical clients—explaining their principles, usage scenarios, and visual references.
This article introduces the 'DBA攻坚指南' book, detailing its Oracle and MySQL performance‑tuning content, author credentials, key chapters, SQL health‑check scripts, usage examples, and the accompanying giveaway event organized by the 爱可生开源社区.
This guide walks through configuring Seata’s registry and configuration centers with Nacos, setting up MySQL storage, defining service modules, writing transactional code with @GlobalTransactional, and verifying rollback behavior in a Spring Boot 2.3 microservice project.
This article provides a step‑by‑step tutorial for building a full‑stack online blog platform with Flask, MySQL, Layui, and a Markdown editor, covering environment setup, database schema, model definitions, template inheritance, user authentication, CRUD operations for blogs, comment handling, image upload, and custom error pages.
This article introduces ShardingSphere‑Jdbc as a lightweight Java JDBC framework, details step‑by‑step Docker‑based MySQL master‑slave replication configuration, reviews redo log and binlog mechanisms, explains two‑phase commit, and demonstrates read‑write splitting and sharding strategies with Spring Boot examples.
This article investigates the meaning of the number in parentheses for MySQL INT types, demonstrates through storage tests that INT(4) and INT(11) have identical storage size and range, and explains how the ZEROFILL attribute changes only the displayed width.
This article demonstrates how to export full data from TiDB to MySQL and vice‑versa using TiDB's dumper/dumpling tools, MySQL's mysqldump, and the tidb-lightning importer, covering both SQL and CSV formats, scripts for automation, and verification steps for large tables.
This guide shows how to use a Windows batch file together with MySQL's mysqldump tool to create scheduled backups of one or multiple databases, including command options, full‑path execution, loop timing, and a ping‑based pause to avoid interference.
This article explains how to identify and delete duplicate records in large MySQL tables, discusses why a naïve delete fails, and provides two robust SQL solutions—one that removes all duplicates and another that retains a single row per duplicated key—demonstrating fast execution even on tables with hundreds of thousands of rows.
This article analyzes MySQL 8.0.25 source code, detailing the server‑layer architecture, parser reconstruction, query‑rewrite phases, transformation rules, and both the legacy and hypergraph optimizers, while also comparing key stages with PostgreSQL.
After upgrading MySQL Connector/J from 8.0.18 to 8.0.20 on IBM JDK 1.8, SSL handshake failures occur due to mismatched cipher suite naming, and the article details analysis of MySQL and IBM JSSE2 TLS settings, packet captures, and multiple remediation strategies including JVM parameters and JDK upgrades.
The article explains how to scale MySQL for massive traffic by combining Redis caching, read‑write splitting, data archiving, and sharding techniques, while highlighting their benefits, implementation steps, and potential consistency challenges in modern systems.
This guide demonstrates how to switch a MySQL 5.7.32 deployment between Group Replication (MGR) and semi‑synchronous replication, covering environment checks, node configuration, plugin installation, replication setup, validation, and the limitations encountered when combining the two modes.
This article explains how MySQL index usage affects query performance, demonstrates that hitting an index does not guarantee speed, explores slow‑query logging parameters, shows practical experiments with EXPLAIN, and presents optimization techniques such as composite indexes, index condition push‑down, and virtual columns to reduce row scans.
This article compiles 100 common MySQL interview questions for developers, covering index structures and optimization, transaction ACID properties and isolation levels, primary‑key design, storage‑engine choices, sharding strategies, and miscellaneous SQL nuances such as binlog formats and MyBatis parameter handling.
The article explains why large‑scale e‑commerce systems need sharding and data heterogeneity, compares dual‑write, asynchronous MQ, and Canal‑based binlog solutions, and discusses how to achieve real‑time, ordered data synchronization using MySQL replication, Canal, and message queues.
The Fast App Center’s duplicate OpenID rows were traced to a non‑atomic check‑then‑insert race condition, prompting the team to evaluate a unique‑index safeguard versus application‑level distributed locking, ultimately implementing a Redis‑based lock to serialize inserts and adding a cleanup job to purge existing duplicates.
This article summarizes comprehensive MySQL index design guidelines, covering naming conventions, column selection criteria, index count control, handling of frequently updated columns, function and duplicate indexes, small‑table considerations, and index ordering to optimize query performance.
This article explains why a query that uses an index can still be slow, demonstrates how to diagnose index usage and row scans with EXPLAIN and the slow query log, and presents optimization techniques such as composite indexes, index condition pushdown, and virtual columns.
This article describes how to identify and delete duplicate rows in MySQL tables—first showing a naive Python‑based approach, then presenting efficient SQL queries that locate duplicate names, explain MySQL's limitation on deleting from the same table being queried, and provide a safe sub‑query solution to remove all duplicates while preserving a single representative row.
This article introduces a Java web application that integrates movies, music, and books, detailing its development environment, system architecture, core features such as login, search, and recommendations, and provides step‑by‑step deployment instructions along with the GitHub source link.
This article summarizes frequent problems encountered when using Flink CDC with MySQL—including Kafka version conflicts, checkpoint timeouts, permission errors, global lock issues, and DDL parsing failures—and provides practical configuration tweaks and code examples to resolve them.
This article explains MySQL's SQL Mode concept, lists the default mode values for MySQL 5.7, demonstrates how to view and change the mode with SQL commands, describes the impact of common modes such as STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY, and provides a complete reference table of all supported mode values and predefined mode combinations.
This article demonstrates how to use the RETURNING clause in Tencent Cloud Enterprise MySQL to retrieve inserted, replaced, or deleted rows directly, showing step‑by‑step examples with images and explaining the behavior of RETURNING, REPLACE RETURNING, and DELETE RETURNING statements.
The article explains why deleting rows in MySQL does not shrink the physical table file, describes the underlying InnoDB storage mechanisms, and provides practical methods such as OPTIMIZE TABLE, ALTER TABLE, and Online DDL to reclaim space and reorganize the table.
This article examines common causes of slow MySQL queries, explains index mechanics and failures, then compares ElasticSearch’s fast tokenized search and HBase’s column‑oriented storage, offering practical guidance on when and how to use each technology.
This article explains MySQL’s logical architecture, query processing steps, and the principles behind query optimization, covering topics such as client‑server protocol, query cache, parsing, cost‑based optimizer, execution engine, and practical index design strategies to improve performance.
This article walks through diagnosing MySQL disk‑usage spikes caused by data fragmentation, explains why common cleanup commands may fail, and provides a detailed, repeatable procedure—including stopping the service, removing InnoDB files, and restoring the database—to finally free up the occupied space.
This article explains what MySQL temporary tables and temporary files are, how they are created by users or the optimizer, how to view and clean them up, relevant configuration parameters, monitoring methods, and special cases that may require large storage.
This article introduces the Flash-Waimai project—a full‑stack food delivery system built with Spring Boot, Vue.js, MySQL and MongoDB—detailing its architecture, module breakdown, database setup, quick‑start commands, and step‑by‑step instructions for launching the backend, admin portal, and mobile client.
This article explains MySQL master‑slave replication fundamentals, including basic synchronization, circular replication issues, lag handling, failover strategies, parallel replication techniques, GTID usage, and read‑write splitting, providing practical guidance for reliable high‑availability database deployments.
This article explains the basic principles of MySQL master‑slave architecture, the role of read‑only replicas, loop‑replication problems, master‑slave delay measurement, various failover strategies, parallel replication mechanisms across MySQL versions and MariaDB, GTID usage, and read‑write splitting techniques, providing practical commands and diagrams for each concept.
A detailed recount of four interview rounds for a Java backend position at Alibaba Cainiao, covering the breadth of topics from JVM and garbage collection to Spring, MySQL indexing, Kafka, and project design, plus personal reflections and next‑step plans.
This article explains MySQL deadlocks, describes InnoDB lock types, shows how to read deadlock logs, analyzes classic concurrency cases, and offers practical tips to design indexes and write SQL that minimize lock conflicts and avoid deadlocks.
This article demonstrates how to simulate accidental deletion of a MySQL InnoDB .ibd file, observe the impact on queries, recover the file using the file descriptor in /proc, restart MySQL, and verify that various DDL operations can detect the missing tablespace.
This guide explains how to safely recover MySQL data using full backups, incremental binlog recovery, point‑in‑time restores, single‑table extraction, and methods to skip erroneous SQL statements via GTID or delayed replication, complete with practical command examples.
The article explains how to improve indexing of long string columns in MySQL by using generated columns with CRC32/MD5 hashes, functional indexes introduced in MySQL 8.0.13, and prefix indexes, providing code examples and performance considerations.
The article discusses efficient ways to perform bulk inserts in MySQL while avoiding duplicate records, covering techniques such as INSERT IGNORE, ON DUPLICATE KEY UPDATE, INSERT…SELECT…WHERE NOT EXISTS, REPLACE INTO, and demonstrates a practical MyBatis implementation with a unique mobile_number constraint.
This article presents a comprehensive guide to MySQL performance improvement, covering SQL and index tuning, pagination tricks, join and UNION optimizations, slow‑query logging, schema design choices, and hardware considerations, each illustrated with concrete examples and practical code snippets.
