This article explains the different types of auto‑increment identifiers used by MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, and thread_id—describes how they reach their maximum values, the consequences of overflow, and compares external solutions such as Redis‑based keys.
This article outlines HuoLaLa's journey from a fragmented multi‑cloud environment to a unified, platform‑driven database governance system, detailing the technical challenges, architectural decisions, key components for MySQL, Redis, Kafka, and other middleware, and the measurable stability and cost improvements achieved after a year of operation.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, using UNION ALL, driving large tables with small ones, batch operations, LIMIT usage, index control, proper data types, and efficient GROUP BY—to help developers improve query speed, reduce resource consumption, and avoid common performance pitfalls.
This article demonstrates how to design and implement a high‑frequency like/unlike feature using Spring Cloud, Redis for caching, MySQL for persistence, and Quartz for scheduled data synchronization, covering cache design, data structures, service interfaces, database schema, and deployment scripts.
This article documents a reproducible test that shows gh-ost can lose rows when used on a MySQL 5.7 AFTER_SYNC semi‑synchronous replica, explains the underlying cause, and presents a source‑code modification that prevents the loss.
This guide presents practical MySQL performance tricks—including choosing minimal field types, using NOT NULL, preferring JOIN over sub‑queries, leveraging UNION, applying proper transaction control, foreign keys, table locking, indexing strategies, and query‑writing best practices—to dramatically speed up data retrieval and reduce resource consumption.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
This article explains how to use the mysqldump command to back up MySQL databases, provides various command examples for full or partial backups, demonstrates a Bash script for automated daily backups with log management, and shows how to schedule these tasks using crontab.
The article analyzes why xtrabackup full backups fail on MySQL 8.0 when undo log truncation occurs, identifies a bug triggered by the super_read_only setting, and provides reproducible steps and configuration tweaks to prevent the issue.
This article introduces Flink CDC 2.0, explains its distributed full‑load and incremental reading mechanisms, details the slice partitioning, snapshot correction, and binlog handling logic, and provides a complete Java example that demonstrates how to configure Flink SQL, MySQL source, and Kafka sink.
This article explains the fundamentals of MySQL indexes, focusing on B+Tree structures, differences between MyISAM and InnoDB implementations, practical indexing strategies, configuration and SQL tuning tips, and provides a detailed case study with EXPLAIN analysis to help developers design efficient indexes.
This article explains the concepts, characteristics, and practical scenarios of database sharding—including vertical and horizontal table partitioning as well as vertical and horizontal database partitioning—helping developers decide when and how to apply each strategy to improve scalability and performance.
This article explains how PolarDB MySQL introduces an In‑Memory Column Index (IMCI) to combine row‑store OLTP strength with column‑store analytical speed, detailing the architectural innovations, optimizer decisions, data organization, resource isolation strategies, and benchmark results that show IMCI achieving tens to hundreds of times faster query execution than native MySQL and matching ClickHouse performance.
This article presents a full‑stack Java SpringBoot project that manages community users and tracks epidemic data, employing RBAC for role‑based permissions, MySQL for storage, and various Spring modules such as SpringMVC, AOP, interceptors, and global exception handling.
The article explains a common MySQL UPDATE mistake where developers use AND between column assignments, shows the unexpected result of owner_code becoming 0, analyzes the logical expression behind it, and provides the correct comma‑separated syntax to reliably update multiple fields.
This guide walks through SQL’s language categories—DDL, DML, DQL, DCL, and TCL—then demonstrates practical database and table management commands such as creating and dropping databases, defining tables with various constraints (not‑null, default, primary/foreign keys, unique, auto‑increment), and modifying or copying fields and tables.
This article explains the purpose and mechanics of MySQL's binlog, redo log, and undo log, covering their structures, use cases such as replication and recovery, flush timing parameters, format options, and how they together ensure data durability and consistency in InnoDB.
This tutorial explains how MySQL implements full‑text search, covering the creation of full‑text indexes (including Chinese ngram support), the three query modes (natural language, boolean, and query expansion), relevance ranking, underlying inverted‑index structures, cache handling, and common DML operations.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds a pessimistic lock, detailing when it results in row-level locking versus table-level locking based on the presence of indexes or primary keys, and demonstrates the behavior with multiple practical examples.
The article explains how MySQL's explicit_defaults_for_timestamp setting and strict SQL mode cause TIMESTAMP columns with default CURRENT_TIMESTAMP to produce "Invalid default value" errors during migration from 5.7.28 to 5.7.20, especially when synchronization tools omit NULL and DEFAULT attributes.
The article describes how Qunar.com upgraded its Nagios/NRPE‑based database monitoring by inserting a Kafka‑driven alarm component, centralizing alert configuration in MySQL, adding flexible shielding and multi‑channel notifications, and exploring intelligent features such as slow‑query and disk‑space management.
This article compares MySQL and HBase across architecture, storage engine, indexing structures (B+ tree vs LSM tree), data access features, and ecosystem integration, highlighting each system's strengths, limitations, and the scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
Vitess 12, the latest major release of the MySQL clustering solution, introduces the experimental Gen4 query planner, enhanced VTAdmin multi‑cluster management, role‑based access control, updated benchmarking tools, and more inclusive naming conventions to improve scalability and cloud deployment.
This article explains the fundamentals, architecture, deployment patterns, recovery procedures, performance tuning, and common troubleshooting of MySQL Group Replication (MGR) as a high‑availability solution for private deployments, illustrated with real‑world examples and practical SQL commands.
The article investigates how changing the operating system clock while MySQL is running influences functions such as SLEEP, metadata lock (MDL) wait calculations, and other timing‑related mechanisms, demonstrating that forward or backward adjustments can cause premature exits or inaccurate wait measurements.
This comprehensive guide covers MySQL fundamentals such as the three normal forms, differences between InnoDB and MyISAM, auto‑increment primary key behavior, index types and design principles, transaction isolation levels, MVCC, logging mechanisms, two‑phase commit, query execution, replication, high‑availability architectures, and practical performance tuning techniques.
In a production environment, a pagination export feature caused duplicate and missing records due to MySQL’s nondeterministic ordering when multiple rows share the same ORDER BY value, and the article explains the root cause, official documentation, and a reliable solution using additional sorting columns.
This article provides a step‑by‑step tutorial on configuring MySQL master‑slave replication with Docker, explains redo log and binlog mechanisms, demonstrates two‑phase commit, and shows how to use ShardingSphere‑Jdbc for read/write splitting and sharding with detailed Spring Boot examples and code snippets.
This article examines a real‑world Percona XtraDB Cluster (PXC) fault where simultaneous write‑node connection spikes were caused by flow‑control triggered by 100% disk I/O on a shared read node, ultimately traced to slow sorting queries involving a mediumtext column, and offers diagnostic steps and mitigation strategies.
This article outlines the principles, recommended practices, and detailed steps for upgrading MySQL from 5.7 to 8.0, covering version compatibility, major 8.0 feature changes, pre‑upgrade checks, in‑place and logical migration procedures, and essential configuration adjustments with example commands.
This article explains MySQL InnoDB lock types, analyzes common deadlock scenarios with detailed SQL examples, describes the underlying locking mechanisms such as next‑key and gap locks, and provides practical prevention and resolution techniques for developers working with transactional databases.
This article explains why MySQL 8.0 no longer requires the legacy proxy user feature, shows how to replace it with roles, and provides a step‑by‑step demonstration—including plugin installation, user creation, privilege granting, and the two main limitations of proxy users—using concrete MySQL commands and code snippets.
This article explains the background, design criteria, and practical implementations of horizontal database sharding—including range, hash, gene, routing‑table, and consistent‑hash methods—while discussing common pitfalls, data‑skew mitigation, and both doubling and consistent‑hash expansion techniques for MySQL clusters.
This article explains how the MySQL system variable explicit_defaults_for_timestamp controls the default and NULL handling of TIMESTAMP columns, demonstrates the differences between OFF and ON settings with practical tests, and discusses why the parameter is being deprecated for stricter time‑data management.
This article explains how the MySQL SELECT … FOR UPDATE statement adds a pessimistic lock, why the lock type (row or table) depends on the presence of an index or primary key, and demonstrates the behavior with practical examples and SQL scripts.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article explores PolarDB MySQL’s In‑Memory Column Index (IMCI) technology, detailing its hybrid row‑column storage architecture, optimizer enhancements, parallel execution engine, and performance gains that enable real‑time analytical queries alongside OLTP workloads, and compares its benchmarks against MySQL and ClickHouse.
This article explains the four necessary conditions that cause deadlocks, outlines four major strategies to prevent or resolve them, and provides a step‑by‑step MySQL example with code snippets and diagrams illustrating how deadlocks occur and can be avoided.
This guide walks you through setting up a Spring Boot‑based logistics warehouse system, detailing required software versions, the project’s modular features, technology stack, database and backend configuration, frontend setup with Vue, and provides screenshots of key interfaces.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds pessimistic locks, detailing when it results in row‑level locks versus table‑level locks based on the presence of indexes or primary keys, and provides verification examples with code and transaction scenarios.
This article details how a rapidly growing e‑commerce platform migrated from a single MySQL instance to a 16‑shard architecture using Sharding‑Jdbc, introduced consistent‑hashing to mitigate data skew, leveraged ES+HBase for multi‑dimensional queries, and implemented zero‑downtime migration strategies such as dual‑write and Canal replication.
This article examines a real‑world MySQL master‑slave replication delay case, analyzes the root causes using slave status, InnoDB metrics, OS‑level I/O and error logs, and provides concrete configuration and operational recommendations to reduce lag.
This article walks through MySQL's core concepts—including ACID guarantees, the four isolation levels, undo and redo logs, buffer pool flushing, lock types, gap locks, deadlock scenarios, and the B+‑tree based index architecture—providing interview‑ready insights and practical examples.
Effective MySQL sharding requires sustainable, low‑skew designs, favoring hash‑based methods with proper coprime counts, two‑stage partitioning, routing tables, or consistent hashing, while supporting expansion via doubling or flexible consistent‑hash growth to avoid hot spots and uneven data distribution.
This article explains the background, principles, architecture, and detailed implementation of Percona's thread pool, and describes TXSQL's dynamic thread‑pool activation, load‑balancing strategies, disconnection optimizations, related configuration parameters, status variables, and a concise summary.
This article describes a real‑time slow‑query monitoring solution for MySQL, detailing the overall architecture, the agent that tails slow‑log files and pushes entries to Redis, and the consumer that processes logs, stores them, and alerts DBAs, enabling near‑instant detection of performance issues.
This article presents a comprehensive guide to MySQL performance, covering maximum data volume and concurrency limits, recommended query response times, practical table‑design rules, index classification and optimization techniques, as well as detailed SQL tuning patterns such as batch processing, operator, OR, IN, LIKE, JOIN and LIMIT improvements.
This article provides a detailed, online, in‑place upgrade procedure for MySQL InnoDB Cluster (MGR) from version 5.7.25 single‑primary mode to version 8.0.26, covering preparation, backup, node‑by‑node upgrade, configuration changes, and communication‑protocol updates.
This article describes the background, functional requirements, architecture, packet‑capture implementation using libpcap, MySQL protocol parsing, result processing, and storage strategy (Kafka + ClickHouse) of a custom MySQL sniffer developed at Qunar to provide complete database access logs for auditing, performance analysis, and operations.
This tutorial introduces a music website project built with a Vue.js front‑end and a Spring Boot + MyBatis back‑end using MySQL, and provides step‑by‑step deployment instructions, configuration details, and commands to run the full‑stack application.
This tutorial walks you through creating a complete music‑streaming site using Vue.js for the front‑end and Spring Boot/MyBatis for the back‑end, covering core features, required resources, configuration, and deployment commands in a clear, practical manner.
This article explains what database indexes are, classifies them by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates how InnoDB and MyISAM handle clustered and secondary indexes with practical SQL examples.
This article explains which components—user processes, I/O wait, and system interrupts—consume most CPU in MySQL, describes the performance impact of high CPU, and provides concrete strategies such as reducing I/O, simplifying calculations, optimizing indexes, and judiciously scaling hardware to lower CPU usage.
A detailed forensic analysis reveals that a MySQL 5.7 instance stalls during startup due to a mismatch between InnoDB flush‑sync settings and truncate‑related redo logs, and the article explains how to diagnose the issue with TOP, stack traces, and source‑code inspection, then offers three practical work‑arounds.
This article introduces a lightweight Spring Boot‑based mini Tmall e‑commerce project, detailing its core features—from user registration and product browsing to cart, order processing, and admin management—along with the technology stack, UI screenshots, and a four‑step deployment guide for developers.
This article explains the MySQL InnoDB undo log—its definition, role in transaction rollback and MVCC, when it is created, where and how it is stored across different MySQL versions, the internal tablespace and rollback‑segment structures, log types, and real‑world examples of its usage.
This article compares MySQL and HBase by examining their architectural designs, storage engines (B‑Tree vs LSM‑Tree), performance characteristics, ecosystem features such as TTL and multi‑versioning, and identifies scenarios where HBase is a suitable complement to MySQL for large‑scale data workloads.
This tutorial explains how to embed Dataway—a DataQL‑based API configuration UI—into a Spring Boot application, covering Maven dependencies, property settings, database schema creation, datasource integration, Hasor module registration, enabling annotations, and step‑by‑step usage to publish and test APIs without writing traditional controller code.
This article explains what data consistency means for MySQL and Redis, analyzes inconsistency cases in both single‑threaded and concurrent environments, and proposes practical strategies—including read‑only and read‑write cache handling, message‑queue retries, binlog subscription, delayed double‑delete, and distributed locking—to achieve eventual or strong consistency.
This article introduces three MyBatis batch insertion techniques—looped single inserts, MyBatis‑Plus batch inserts, and native SQL batch inserts—provides complete implementation code, performance test results for inserting 100,000 records, analyzes the causes of differences, and offers practical solutions to common pitfalls such as MySQL packet size limits.
This article explains when and how to use caching with Redis, compares recursive and table‑lookup algorithms for factorial calculation, discusses cache design, expiration policies, and presents several write‑through and write‑behind strategies to maintain data consistency between Redis and MySQL under various concurrency scenarios.
This article compares MySQL and HBase by examining their architectural designs, storage engines, data access patterns, and ecosystem features, highlighting the strengths and trade‑offs of each system and outlining the scenarios where HBase is a suitable complement to MySQL.
This article explains the difference between MySQL’s utf8 and utf8mb4 character sets, why utf8 cannot store emojis or complex Chinese characters, and provides step‑by‑step examples showing how to configure tables and columns with utf8mb4 to avoid encoding errors.
To keep MySQL and Redis synchronized, the article defines consistency, examines read‑only and read‑write cache inconsistency cases, and recommends practical strategies such as update‑DB‑then‑delete‑cache with message‑queue retries, binlog‑driven updates, delayed double‑delete, and Redis distributed locks, while outlining stronger protocols and key design considerations.
This article explains MySQL's native horizontal sharding using MERGE tables, covering the concepts of horizontal versus vertical splitting, table‑level sharding and partitioning, practical examples, advantages, limitations, and suitable use cases for MERGE tables.
After a client mistakenly imported 60,000 useless rows and a developer’s faulty delete command erased over 100,000 critical records, this guide explains how to use MySQL binlog to locate, extract, and replay the missing SQL statements, restore the data, and prevent future disasters.
When a MySQL table is sharded into multiple databases or tables, the usual LIMIT offset, pagesize pagination breaks, and this article examines why simple merge fails, then presents three practical approaches—global limit, secondary queries, and jump‑page restriction—highlighting their trade‑offs and limitations.
This guide walks through setting up a full‑stack web chatroom demo built with Spring Boot, Vue, WebSocket, MySQL, Redis, RabbitMQ and FastDFS, covering the technology stack, core features, database schema, configuration files, and step‑by‑step deployment instructions.
This article examines why a MySQL composite index on (first_name, last_name) initially fails to be used for ordering, demonstrates how adding a range predicate reduces the scanned row count, and explains the threshold at which MySQL switches from filesort to index‑based retrieval.
This article examines why containerizing MySQL often leads to data‑security, performance, state‑management, and resource‑isolation problems, while also outlining scenarios where MySQL can safely run in Docker with proper strategies and examples.
This article documents a real‑world incident where a MyISAM table in a Percona XtraDB Cluster caused data inconsistency and node self‑shutdown, analyzes the root cause, and describes how using pt-online-schema-change together with proper engine conversion restored consistency across the cluster.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates clustered and secondary indexes with practical SQL examples and diagrams.
This guide walks through installing Redis, MariaDB, Go, configuring Open-Falcon's backend and frontend components, setting up Python virtual environments, and launching the monitoring system, providing complete command-line instructions and configuration details for a functional deployment.
This article presents a comprehensive MySQL slow‑query risk‑scoring model that quantifies each slow query's impact using metrics such as query count, execution time, lock wait, bytes sent and rows examined, assigns weighted scores up to 100, and demonstrates how the model enables proactive, business‑aligned remediation.
This guide details how to protect MySQL databases from errors and crashes by using mysqldump for manual dumps, creating Bash scripts for automated daily backups, and scheduling them with Linux cron, including command examples, restoration methods, and crontab syntax for reliable periodic backups.
This article explains MySQL InnoDB's locking mechanisms—including record locks, gap locks, and next‑key locks—detailing how they work, their interaction with isolation levels such as Repeatable Read, and the principles, optimizations, and examples that illustrate their impact on concurrency and phantom reads.
This article demonstrates three progressive techniques—direct list insertion, grouped batch insertion, and multithreaded batch insertion—using MyBatis to dramatically reduce MySQL bulk‑import time from over a minute to under ten seconds while handling packet size limits.
This article explains how MySQL handles concurrent transactions using MVCC, lock mechanisms, and various isolation levels, detailing dirty writes, dirty reads, non‑repeatable reads, phantom reads, and the internal structures such as ReadView that enable reliable data consistency.
This article examines how enabling the MTS (writeset‑based incremental parallel replay) feature in DTLE and MySQL affects data transfer speed, CPU and memory usage, and network latency, presenting test procedures, configurations, and performance results that show up to 28‑fold speed improvements.
This article explains how to diagnose a sudden rise in MySQL file handle usage by reproducing the issue with large tables, using Linux perf to trace open system calls, analyzing hash join behavior, and querying performance_schema.file_instances to pinpoint the underlying cause.
This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.
This guide explains why database backups are essential, walks through checking disk space, creating a backup directory, writing a MySQL dump script, making it executable, scheduling it with cron, and verifying that backups run reliably on a Linux server.
This article explains what MySQL slow queries are, why they cause system failures, proposes multi‑dimensional metrics to assess their severity, outlines concrete guidelines and change standards, and shares real‑world optimization cases and daily operational practices for eliminating slow SQL.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering key constraints, storage functions, GeoJSON support, indexing, in‑place updates, essential JSON utilities, path expressions, extraction, modification techniques, and the powerful JSON_TABLE feature with clear code examples.
This article explains the concepts, characteristics, and practical scenarios of vertical and horizontal table partitioning as well as vertical and horizontal database sharding in MySQL, helping developers decide when and how to apply each strategy for large‑scale applications.
This article explains MySQL indexes, covering their definition, benefits, various types such as B‑tree and B+‑tree, practical techniques for creating high‑performance indexes like prefix, composite, and clustering indexes, and how to inspect and maintain index information and fragmentation.
This article examines MySQL's INSERT locking behavior, explains why phantom reads do not occur under REPEATABLE READ isolation, and walks through compiling the MySQL source, debugging lock acquisition, and the role of insert‑intention and gap locks using detailed code examples and execution traces.
This guide explains how to use the open‑source Screw tool to automatically produce database schema documentation in HTML, Word, or Markdown and to generate Java entity classes, offering step‑by‑step examples with Maven plugin and Java code configurations for MySQL, Oracle, and PostgreSQL.
The article explains why slow SQL queries drain I/O and CPU resources, outlines prioritization rules, details MySQL execution steps, identifies performance‑affecting factors, and provides practical solutions and best‑practice guidelines for diagnosing and optimizing slow MySQL queries in production environments.
This article provides a comprehensive guide on using ShardingSphere-JDBC for read‑write splitting, configuring MySQL master‑slave replication with Docker, explaining redo log and binlog mechanisms, demonstrating two‑phase commit, and showing how to implement database sharding and distributed primary keys in a Spring Boot application.
This article recounts a real-world incident where a client mistakenly imported 60,000 rows of erroneous Excel data into a MySQL production database, leading to an accidental bulk delete, and explains how the team used MySQL binlog to recover the lost data while offering practical safeguards to prevent similar disasters.
The article explains why a simple GROUP BY query on a large user‑view table can become inefficient due to temporary tables, filesort, and memory allocation, demonstrates the execution steps with EXPLAIN, and shows how adding a composite index on user_id, viewed_user_age, and viewed_user_sex eliminates temporary tables and improves performance.
MySQL 8.0 replaces the default mysql_native_password with the caching_sha2_password plugin, which stores a 70‑byte salted SHA‑256 hash with configurable rounds, caches SHA256(password) for fast logins, and falls back to RSA‑encrypted full verification on insecure connections, supporting user creation, replication and key management.
This article investigates why a DELETE IN subquery in MySQL 5.7 performs a full table scan instead of using an index, explains the optimizer’s behavior compared to SELECT IN, and provides practical solutions such as rewriting the statement as a JOIN or adding table aliases to enable index usage.
This article details the design and implementation of a high‑performance, highly available membership system for the merged Tongcheng‑eLong platform, covering Elasticsearch dual‑center clusters, traffic‑isolated three‑cluster architecture, deep ES optimizations, Redis caching and dual‑center clusters, MySQL dual‑center partitioning, migration strategies, and future fine‑grained flow‑control and degradation measures.
This article explains how MySQL implements trusted IP access control via user@host definitions, demonstrates creating and updating user accounts and associated metadata tables, and provides step‑by‑step procedures—including DCL commands, metadata updates, and handling of stored objects—to safely modify trusted IPs in production environments.
The article examines a MySQL optimizer bug where a query with a WHERE clause on uid, ORDER BY id ASC, and LIMIT 1 incorrectly triggers a full table scan despite a usable composite index, explains the optimizer’s cost calculations, and offers workarounds such as FORCE INDEX or a harmless arithmetic trick to force index usage.
A careless UPDATE without an indexed WHERE clause can trigger full‑table locking in InnoDB, halting business operations, but by understanding InnoDB's next‑key locks, enabling sql_safe_updates, and using FORCE INDEX you can avoid this costly mistake.
This article compares MySQL's INSERT INTO and LOAD DATA INFILE statements, explains their syntax, shows practical examples, highlights when each should be used for bulk data loading, discusses error handling with IGNORE, and explains why LOAD DATA INFILE is typically faster.
