A collection of Zhihu answers explains that MySQL’s original utf8 charset only supports three‑byte characters, causing data loss for emojis and rare symbols, and shows how the newer utf8mb4 charset provides full Unicode support, becoming the default in MySQL 8.0.
This article investigates a deadlock that occurs when using INSERT … ON DUPLICATE KEY UPDATE in a multi‑threaded MySQL 8.0 environment, reproduces the issue, examines InnoDB lock logs, walks through the relevant source‑code call chain, and demonstrates how gap locks cause the deadlock.
This tutorial explains step‑by‑step how to set up a MySQL database, create PHP scripts to retrieve taxi trajectory data, and use Baidu Maps JavaScript API in an HTML page to render dynamic taxi movement paths with interactive features.
This tutorial walks through MySQL's EXPLAIN TYPE column, illustrating how different join types such as const, eq_ref, ref, range, and index affect query performance using concrete SQL examples and execution‑plan analysis on a sample table with a composite primary key.
The article outlines several reasons why running MySQL in Docker containers is problematic—data security, performance bottlenecks, statefulness, and resource isolation—while also promoting a ChatGPT community and related services.
This article explains how MySQL 8.0.32 InnoDB generates and stores Insert Undo logs, detailing their binary format, field meanings, example values, and how the hidden DB_ROLL_PTR field encodes the log address, with accompanying SQL and shell examples.
This article dives deep into MySQL's charset and collation system, explaining concepts, configuration levels, system variables, string literals, conversion rules, Unicode sorting algorithms, binary collations, and practical tips to avoid common encoding pitfalls and ensure correct string handling.
This article reproduces a MySQL replication error triggered by an incorrect host entry in the mysql.user table, analyzes the version‑specific host field differences, and provides a step‑by‑step fix using the slave_type_conversions parameter and proper user‑renaming practices.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and closes the connection.
This article explains why MySQL subqueries can hurt performance, outlines common pitfalls such as temporary tables, index loss, and optimizer complexity, and presents ten concrete examples that replace subqueries with IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT to achieve faster, more maintainable queries.
This article explains the primary‑key duplication issue caused by MyBatis‑Plus in clustered Docker/K8S environments, analyzes the limitations of the standard Snowflake algorithm, and presents Seata’s improved Snowflake implementation that provides globally unique, high‑performance IDs while minimizing database page splits.
This article examines how MySQL pagination performance degrades as table size grows, presents real‑world timing tests on a million‑row customer table, and offers three practical solutions—including selecting only primary keys, ID‑range filtering, and leveraging ElasticSearch—to dramatically improve query speed.
This article demonstrates how to store SQL queries in .ini files and use Python's mysql-connector-python library together with configparser and logging to perform MySQL CRUD operations, providing full code examples and explanations for configuration, connection handling, query execution, and log management.
This article explains how MySQL stores and manages data by introducing the InnoDB storage engine, its buffer pool, data pages, B+Tree and adaptive hash indexes, change buffer, undo/redo logs, binlog, and the server layer that parses, optimizes, and executes SQL statements.
This article explains the hidden performance costs of traditional OFFSET pagination in MySQL, analyzes why deep paging becomes a bottleneck, and presents keyset (cursor) pagination and other optimization techniques as efficient alternatives for handling massive or frequently changing datasets.
The article shares a senior architect’s experience with large‑scale MySQL tables, discussing why sharding and Elasticsearch were introduced, the pagination bug caused by Sharding‑JDBC, and a comprehensive set of hardware, software, and SQL tuning recommendations, including a full MySQL configuration example.
This article examines various methods for synchronizing MySQL data with Elasticsearch, including synchronous and asynchronous dual writes, Logstash pipelines, binlog streaming, and Alibaba Cloud DTS, outlining implementation approaches, advantages, disadvantages, and suitable application scenarios for each solution.
Xiaohongshu built a lock‑free, non‑disruptive data‑consistency verification tool that automatically selects optimal methods, handles heterogeneous sources and dynamic changes, performs full and incremental checks via chunked checksums or row‑by‑row comparison, quickly isolates mismatches, and supports automatic remediation, ensuring reliable migrations and sharding.
This guide explains how to prevent data loss in MySQL by enabling backups and binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements, and using flashback tools like binlog2sql and MyFlash.
This article explains the internal process MySQL 8.0.32 InnoDB uses to allocate, create, and manage Undo segments—including cached segment retrieval, new segment creation, insertion into rollback segment lists, conflict avoidance through mutexes, and a concise step‑by‑step summary of the entire workflow.
This article walks through the process of swapping MyBatis for MyBatis‑Plus in a legacy Java project, explains why upgrading MyBatis to 3.5.1 triggers a LocalDateTime conversion error, shows how updating mysql‑connector‑java resolves the issue, and shares the broader pitfalls of component upgrades and bug fixes.
This article introduces four open‑source MySQL performance tools—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining their purpose, how to download and run them, and how to interpret the diagnostic reports they generate for database optimization.
Even when scanning tables with tens of millions of rows, MySQL avoids out‑of‑memory crashes by streaming data in small 16 KB net buffers, using socket buffers, and employing an improved LRU algorithm that isolates cold data in the buffer pool’s old generation.
When processing massive MySQL result sets, loading all rows into JVM memory can cause OOM and slow performance, so this guide compares three approaches—regular pagination, streaming queries using server-side cursors, and cursor‑based fetchSize control—detailing their implementations, MyBatis configurations, and trade‑offs.
This article investigates whether the declared length of VARCHAR columns (e.g., VARCHAR(50) vs VARCHAR(500)) affects MySQL storage size and query performance by creating two tables, inserting one million rows, measuring disk usage, and benchmarking various SELECT and ORDER BY operations.
This article provides a comprehensive overview of MySQL's layered architecture, the functions and core components of the server layer, and detailed explanations of Undo Log, Redo Log, and Binlog mechanisms that together ensure the ACID properties of transactions, including practical execution flows for queries and updates.
Testing shows that VARCHAR(50) and VARCHAR(500) occupy identical storage, yet while simple lookups perform similarly, sorting on the longer column triggers disk‑based mergesort and can be several times slower, demonstrating that excessive VARCHAR length harms query performance without saving space.
This document details the purpose, requirements, architecture, detailed design, performance impact, and limitations of a MySQL kernel extension that throttles SQL statements based on configurable flow‑control rules to protect core services from CPU overload caused by heavy or slow queries.
This article demonstrates how to use MySQL's EXPLAIN statement to examine execution plans, interpret key fields such as possible_keys, key, rows, and Extra, and apply index adjustments—including adding and forcing indexes—to improve query performance while weighing sorting costs.
The article explains the benefits of handling data association at the application layer, outlines scenarios where this approach shines, details why joins and subqueries can degrade MySQL performance, and offers practical alternatives and best‑practice tips for large‑scale databases.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), covering its basic concepts, hidden columns, isolation levels, the creation and evaluation of Read Views, and includes detailed source code examples and step‑by‑step experiments to illustrate transaction visibility.
This article examines MySQL 8.0.32 InnoDB's rollback segment memory layout and details the algorithms used to allocate rollback segments for user temporary tables and regular tables, including the role of system variables, counters, and the distribution matrix across undo tablespaces.
The article examines why MySQL sharding (分库分表) is adopted for high‑traffic e‑commerce, outlines its inherent problems such as distributed transaction handling, index and global key constraints, operational overhead, and argues that distributed databases like OceanBase offer a more robust alternative.
This article analyzes the challenges of distributed transaction consistency when combining MySQL writes with third‑party system calls, presents a concrete financial reimbursement case, and proposes a solution that splits a big transaction into small, retryable units using a task table, scheduled jobs, and Spring's after‑commit hook to achieve reliable consistency without excessive latency.
The article explains that while Docker is convenient for learning environments, deploying MySQL in containers poses serious data‑safety, performance, and resource‑isolation problems, and it outlines scenarios where containerizing MySQL may still be viable before shifting to promotional content about AI communities.
This article walks through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, various INSERT variations, SELECT FOR UPDATE, on duplicate key update, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—providing clear examples, SQL snippets, and screenshots to help developers write more efficient and reliable queries.
This article provides a practical guide on monitoring database system resources using Linux commands, configuring MySQL slow query logging, analyzing performance issues, and outlines best practices, while also promoting a ChatGPT community and related services.
This article explores MySQL's join processing methods—Simple Nested‑Loop, Block Nested‑Loop, Hash, and Index Nested‑Loop joins—demonstrates how to create test tables, analyze execution plans, and apply practical optimizations such as indexing and query rewriting to dramatically improve multi‑table query performance.
The article explains how JDBC’s setQueryTimeout and Spring’s transaction timeout work, reveals why SQL statements can become indefinitely blocked in the socket read phase, and provides practical configuration steps—including driver URL parameters—to prevent such blocking in MySQL, SQL Server, and Oracle environments.
When migrating an old Java project from MyBatis 3.5.0 to MyBatis‑Plus 3.1.1, a test failed with “Conversion not supported for type java.time.LocalDateTime” because MyBatis 3.5.1 stopped handling Java 8 time types and the existing mysql‑connector‑java 5.1.26 driver lacked support, which was resolved by upgrading the connector to version 5.1.37 or later, highlighting the need for thorough compatibility testing during framework upgrades.
This article explains why COUNT(*) on a large InnoDB table can take seconds while MyISAM returns instantly, explores the underlying storage‑engine differences, and presents six practical techniques—including Redis counters, counting tables with transactions or triggers, parallel read threads, secondary indexes, and SHOW TABLE STATUS—to dramatically speed up row counting in MySQL.
After a mistaken rm -rf command wiped an entire production server, the author details the step‑by‑step recovery using ext3grep, extundelete, and MySQL binlogs, while sharing hard‑earned lessons on backup, permissions, and incident response.
This article examines MySQL's recommendation against UUID primary keys by creating three tables with different key strategies, running large‑scale insert tests using Spring JdbcTemplate, and analyzing the resulting performance, index behavior, and trade‑offs of auto‑increment, UUID, and random keys.
This article explains how database administrators can monitor system resource usage with commands like top, iostat, and vmstat, and configure MySQL slow query logging, including enabling the log, setting thresholds, viewing logs, and applying best‑practice recommendations for analysis and issue resolution.
This article explains common MySQL performance pitfalls such as large‑offset LIMIT queries, implicit type conversion, sub‑query updates, mixed sorting, inefficient EXISTS clauses, predicate push‑down limitations, and demonstrates how rewriting with proper indexes, JOINs, early range reduction, and WITH (CTE) statements can reduce execution time from seconds to milliseconds.
This article compiles thirty‑five essential Linux operations interview questions covering server management, RAID configurations, load‑balancing choices, middleware concepts, MySQL troubleshooting, networking tools, security practices, scripting examples, and system‑level optimizations, providing concise expert answers for each topic.
vivo migrated five 20‑TB MySQL clusters to OceanBase using OCP, oblogproxy, and OMS, eliminating sharding costs, achieving over 70% storage savings, improving consistency and performance, and leveraging native distributed architecture, tenant isolation, and strong compression to support scalable, reliable operations.
This article demonstrates how to create a test table, bulk‑insert two million rows via a stored procedure, convert the table to the MEMORY engine, and compare query performance against the original InnoDB table, highlighting speed gains, configuration tweaks, and practical usage considerations.
This article explains the four‑layer logical structure of InnoDB Undo logs—Undo pages, Undo segments, rollback segments, and Undo tablespaces—detailing their roles, management, related system variables, and how they affect transaction rollback and storage in MySQL 8.0.32.
This article explains why MySQL adopts B+ trees for its indexes, examining the impact of disk I/O latency, comparing B‑tree variants, and showing how B+ trees reduce I/O operations while supporting efficient range queries and balanced performance for large datasets.
This article investigates MySQL's recommendation to avoid UUID or random Snowflake IDs as primary keys, comparing auto‑increment, UUID, and random long keys through Java/JDBC performance tests, analyzing their insertion speed, index structure impact, and the trade‑offs of each approach.
The article explains how to alleviate MySQL bottlenecks in high‑traffic product services by introducing Redis as a local and remote cache, covering data structures, expiration policies, eviction strategies, persistence mechanisms, and a lightweight TCP protocol to achieve scalable, reliable performance.
This guide explains how to back up single or multiple MySQL databases, entire server instances, specific tables, or only schema, using mysqldump with options like -B, gzip compression, add‑drop flags, and provides restore procedures and an automated backup script.
The article explains why multi‑table JOINs in MySQL can degrade performance, readability, and index usage, and it presents optimization strategies such as query decomposition, data redundancy, wide tables, and introduces the hash join algorithm with detailed build and probe phases, including disk‑based handling.
This article explains how to extend an existing MySQL master‑slave replication filter to include additional databases by using a low‑cost backup‑restore workflow, detailing two solution options, the chosen approach, and step‑by‑step commands for stopping replication, backing up, setting GTID points, restoring, and updating filter rules.
This article explores the core functions of e‑commerce shopping carts and compares client‑side storage methods such as Cookies and LocalStorage with server‑side solutions like Redis and MySQL, offering guidance on choosing the appropriate approach based on business scale and reliability requirements.
After a junior engineer mistakenly ran an unguarded rm -rf command that wiped an entire production server—including MySQL and Tomcat—I documented the step‑by‑step recovery using ext3grep, extundelete, and MySQL binlog, highlighting the lessons learned for future operations.
The article explains why MySQL alone struggles with large‑scale, complex queries, introduces Elasticsearch as a complementary search engine, and details multiple synchronization approaches—including synchronous and asynchronous double‑write, Logstash, Binlog, Canal, and Alibaba DTS—along with their advantages, disadvantages, and typical use cases.
This article outlines fifteen typical MySQL pitfalls—including missing WHERE clauses, lack of indexes, improper NULL handling, wrong data types, deep pagination, missing EXPLAIN analysis, charset misconfiguration, SQL injection risks, transaction misuse, collation issues, overusing SELECT *, index loss, frequent schema changes, missing backups, and unarchived historical data—and provides concrete examples and best‑practice solutions to improve performance, reliability, and security.
This article explains the concept and importance of MySQL database transactions, describes the ACID properties, shows how to use transaction control statements and savepoints, and demonstrates each isolation level (read uncommitted, read committed, repeatable read, serializable) with practical SQL examples.
This article provides a concise English recap of the 27 previously published InnoDB lock module articles, covering theoretical concepts such as table and row locks, lock waiting, deadlock handling, and practical scenarios like lock behavior during inserts and duplicate key operations.
This article reviews the long‑standing performance problems of TRUNCATE and DROP TABLE in MySQL, tracing their origins through official manuals and historical bugs, summarizing the optimizations introduced in MySQL 5.5.23, 5.7, 8.0, and 8.4, and offering practical guidance for mitigating remaining latency.
This article outlines fifteen frequent MySQL mistakes—from missing WHERE clauses and absent indexes to improper data types, deep pagination, SQL injection risks, and inadequate backups—providing clear examples and practical solutions to improve query performance, data integrity, and overall database reliability.
This course introduces MySQL 8's latest features, deep dives into its network communication mechanisms, and teaches advanced optimization techniques such as connection pooling, compression, and SSL encryption, enabling students to build efficient, secure, and high‑performance database applications.
This article walks through practical MySQL performance tuning methods—including pagination, join, subquery, ORDER BY, GROUP BY, and COUNT optimizations—illustrated with real‑world data volumes, step‑by‑step SQL examples, EXPLAIN analyses, index creation, and measurable query‑time improvements.
This article explains how slow MySQL queries degrade user experience, impact business operations, and consume resources, then details how to enable and interpret the slow query log, use EXPLAIN for query analysis, avoid common testing misconceptions, and apply four practical optimization strategies.
This article examines how a Java web application connects to a MySQL database, measuring each step of the TCP handshake, authentication, and setup with Wireshark, and demonstrates that a single connection can take over 200 ms, highlighting the importance of connection pooling.
This article explains the different MySQL JOIN types—including INNER, LEFT, RIGHT, and simulated FULL OUTER joins—by creating sample order and customer tables, inserting test data, and showing the corresponding SQL statements and result sets with visual diagrams.
This article demonstrates how to achieve real‑time synchronization of MySQL data to Redis using Spring Boot 3, the open‑source mysql‑binlog‑connector‑java library, and optional JMX exposure, providing step‑by‑step setup, code examples for parsing binlog events, listening to changes, and configuring Maven dependencies.
This guide explains why missing WHERE clauses can trigger dangerous full‑table updates in MySQL, shows how to enable the sql_safe_updates mode, demonstrates MyBatis‑Plus’s IllegalSQLInnerInterceptor plugin, and offers IDE and code‑review tips to safeguard database operations.
This article explains why syncing Redis with MySQL is essential for performance and consistency, and details three implementation methods—database triggers, application-level double writes, and message queues—providing code examples, practical tips, and key considerations for reliable data synchronization.
This article explains MySQL's various numeric, date/time, and string data types, discusses their characteristics such as signed/unsigned integers, floating‑point precision, fixed‑point accuracy, and provides practical best‑practice recommendations for choosing optimal types in database design.
This article walks through step‑by‑step how to create a simple CRUD service using Go's Gin framework with Gorm and MySQL, then shows the equivalent implementation with Java Spring Boot, MyBatis‑Plus and MySQL, comparing the two approaches and providing complete source code.
This article examines how InnoDB acquires various row, gap, and next‑key locks during INSERT … ON DUPLICATE KEY UPDATE statements under REPEATABLE‑READ and READ‑COMMITTED isolation levels, illustrating the lock behavior with detailed SQL examples and performance‑schema queries.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, including a complete example that creates a connection, runs a query, processes results, and closes the connection.
This article investigates how many records a plain SELECT * query can return in MySQL 8 by examining max_allowed_packet, packet definitions, single‑row size limits, column count restrictions, InnoDB row formats, and practical experiments that reveal the real constraints on query result size.
If you need a powerful yet free MySQL client to replace Navicat, this guide compares three popular tools—DBeaver, MySQL Workbench, and HeidiSQL—covering their download sources, installation steps, key features, and why each may suit different user needs.
This article explains InnoDB lock types, their characteristics, and how MySQL 5.6 and 8.0 handle adding a column without fully locking the table, detailing online DDL, atomic DDL, and performance considerations for large tables.
This article explains why MySQL alone may struggle with large‑scale, complex queries, introduces Elasticsearch as a high‑performance search engine, and compares several synchronization approaches—including synchronous and asynchronous dual‑write, Logstash, Binlog, Canal, and Alibaba Cloud DTS—detailing their advantages, disadvantages, and suitable scenarios.
The article examines why deploying MySQL in Docker containers can be problematic, highlighting data‑security risks, performance bottlenecks, state‑management challenges, and limited resource isolation, while also noting specific scenarios where containerizing MySQL might still be viable.
This article examines how pagination queries on massive MySQL tables become dramatically slower as the offset grows, defines what constitutes a slow SQL, and presents three practical optimization techniques—including returning only primary keys, range filtering, and using Elasticsearch—to dramatically improve query performance.
This article explains MySQL transaction fundamentals, the ACID properties, MVCC implementation, isolation levels, snapshot versus current reads, and the various lock types and modes, and provides practical examples of how different SQL statements acquire locks under InnoDB.
This article dissects the MySQL connection process using Java and Wireshark, revealing that establishing a single connection can consume over 200 ms, which scales to hours of latency for high‑traffic sites, highlighting the necessity of connection pooling and related optimizations.
This guide explains how to design and build a privacy‑focused, view‑once image sharing system using Spring Boot, MySQL, Thymeleaf, and optional cloud storage, covering requirements analysis, architecture, environment setup, code implementation, security considerations, performance optimizations, testing, and deployment.
This guide explains why MySQL and Java processes can suddenly consume 900% CPU, walks through systematic diagnosis using Linux tools, and provides concrete remediation steps such as indexing, caching, thread analysis, and code adjustments to bring CPU usage back to normal levels.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and closes the database connection.
This guide walks through building a Spring Boot and MySQL‑backed “burn after viewing” image sharing service, covering architecture, environment setup, entity and repository design, upload and automatic deletion logic, Thymeleaf UI with burn animation, error handling, optimization, and Docker deployment.
This article examines how InnoDB acquires row‑level locks during INSERT … ON DUPLICATE KEY operations under REPEATABLE‑READ and READ‑COMMITTED isolation levels, explains the lock types on primary and unique indexes, and shows the rollback and lock‑conversion process with concrete SQL examples.
This article describes how a senior architect designed, configured, and implemented a 50‑table sharding solution for loan and repayment request data using ShardingSphere, Spring Boot 3, MySQL, and custom synchronization scripts, while also addressing historical data migration, backend query changes, and operational safeguards.
This article explains why using Docker to host MySQL is generally discouraged, highlighting challenges in database scaling due to container‑exclusive storage, difficulties sharing data files, and memory contention among containers, while also outlining possible synchronization solutions and scenarios where containerized MySQL may still be viable.
MySQL 8.3 introduces a new iterator‑aware JSON output for EXPLAIN and EXPLAIN ANALYZE, selectable via the explain_json_format_version variable, which aligns JSON with the tree format, provides richer execution statistics, and can be accessed programmatically through EXPLAIN INTO across all recent MySQL releases.
This article examines how MySQL InnoDB decides whether to modify index blocks during UPDATE statements, walks through the internal mysql_update workflow, shows debugging with GDB, explains three test scenarios, and validates the behavior by inspecting block LSNs with the innblock tool.
This article examines why a MySQL table with a case‑insensitive collation returns uppercase brand names when searching for lowercase input, analyzes the underlying charset and collation settings, and proposes backend pagination with a case‑insensitive fuzzy search and a unique index to reliably prevent duplicate brand records.
This article analyzes the detailed steps and timing of establishing and closing a MySQL connection from Java, using Wireshark traces to show that even a minimal connection can consume over 200 ms, highlighting the need for connection pooling in high‑traffic web applications.
This tutorial walks you through downloading MySQL binaries, creating a dedicated mysql user, extracting files to /usr/local, setting up separate data directories, initializing each instance, configuring my.cnf, managing the services with systemd, setting root passwords, and troubleshooting common errors, enabling you to run three isolated MySQL servers on a single host.
Effective database administration relies on continuous monitoring of system resources—CPU, memory, disk I/O, and network—using tools like top, iostat, and vmstat, alongside logging slow MySQL queries, analyzing performance bottlenecks, and following best practices such as automated monitoring, centralized log management, regular audits, and log backups.
This article examines frequent MySQL performance problems such as inefficient LIMIT pagination, implicit type conversion, sub‑query updates, mixed sorting, misuse of EXISTS, condition push‑down limitations, early result narrowing, intermediate result push‑down, and demonstrates how rewriting queries with JOINs, derived tables, and WITH clauses can dramatically improve execution speed.
This article explains why a MySQL 8 InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL or when logical deletion is used—and presents several practical strategies, including status counters, timestamps, extra IDs, hash fields, and batch‑insert techniques, to enforce true uniqueness.
This article introduces three free, open-source MySQL client tools—MySQL Workbench, DBeaver, and phpMyAdmin—detailing their main features, cross‑platform support, and suitability for developers and administrators seeking powerful yet user‑friendly database management solutions.
This article describes how a senior architect designed and implemented a sharding solution for loan and repayment tables, covering database design, table naming, sharding algorithms, historical data migration, three‑write synchronization, dynamic switches, and the necessary Spring Boot, MyBatis‑Plus, and ShardingSphere configurations with code examples.
