This article explains MySQL storage engine concepts, compares InnoDB, MyISAM, and MEMORY, describes how data is stored in shared tablespace versus file‑per‑table modes, and provides configuration commands for switching modes, changing default engines, and inspecting or altering a table’s engine.
This guide walks through recovering a deleted MySQL database stored in Docker volumes by locating the mysql.ibd file, recreating a container with the same image, and mounting the original volume, allowing the lost data to be restored without a backup.
This comprehensive guide explains MySQL indexing fundamentals, B+Tree advantages, index classifications, optimal creation strategies, and how to use EXPLAIN to diagnose and fine‑tune queries for maximum performance while avoiding common pitfalls.
This article demonstrates how to encapsulate MySQL operations in a Python MySQLWrapper class, providing modular methods for connecting, querying, inserting, updating, and deleting records, along with a complete example and usage instructions.
This guide walks through verifying MySQL 8.0.16+, copying and editing the mysqld.service file, setting the Service Type to notify, adding NotifyAccess, adjusting ExecStart, reloading systemd, restarting MySQL, and checking its status, while covering SELinux, firewall, and notification‑delay optimizations.
The article explains MySQL’s Multi-Range Read (MRR) optimization, detailing how it scans secondary indexes, buffers and sorts primary keys, leverages disk pre‑read and cache locality to convert random I/O into sequential I/O, and shows configuration, monitoring methods, and a concrete orders‑table example.
This article evaluates MySQL's recommendation to avoid UUID or non‑sequential snowflake IDs by benchmarking three tables—auto‑increment, UUID, and random key—using Spring Boot and JdbcTemplate, then analyzes their index structures, insertion speed, and trade‑offs.
This article walks through converting a SpringBoot + MybatisPlus application from MySQL to PostgreSQL, covering driver addition, JDBC URL changes, dozens of SQL syntax incompatibilities, custom conversion functions, and handy PostgreSQL scripts for bulk column type and default value updates.
This guide explains how to regularly purge oversized log tables using time‑based DELETE statements, interprets related cloud RDS and ECS alerts, and offers troubleshooting steps and batch‑deletion techniques to address MySQL read‑replica synchronization delays caused by large transactions.
This article explains why a simple full‑table UPDATE on massive MySQL tables can cripple master‑slave replication, analyzes deep‑pagination inefficiencies, and presents a step‑by‑step batch‑update strategy using NO_CACHE and FORCE INDEX to keep binlog size and buffer‑pool impact under control.
This article walks through practical ways to perform batch updates in MyBatis, compares foreach, case‑when, and INSERT…ON DUPLICATE KEY techniques, explains common Druid WallFilter restrictions, and shows how to enable multi‑statement execution via JDBC and Spring configuration.
This tutorial demonstrates, using MySQL 8.0.32 InnoDB source code, how REPEATABLE-READ and READ-COMMITTED isolation levels affect the locking behavior of SELECT ... FOR SHARE statements on primary‑key rows, including preparation, execution, and detailed lock analysis.
This article explains how to monitor MySQL slow queries by attaching eBPF uprobe/uretprobe probes to the dispatch_command function, handling symbol mangling across MySQL versions, and processing the captured data in a Go program to display latency and SQL statements.
A batch INSERT triggers a massive lock record buildup, and a concurrent SELECT on performance_schema.data_locks causes a memory allocation error that leaves trx_sys‑mutex unreleased, leading to a full MySQL hang; the article reproduces the bug, analyses the stack, explains the root cause, and shows the official fix in MySQL 8.0.37.
This article explains the fundamental concepts of MySQL index structures, detailing how clustered (primary) indexes store data physically in order, how non‑clustered (secondary) indexes reference primary keys, and compares their performance implications and practical usage scenarios.
Bilibili’s game live‑stream platform implements interactive features ‘Play Together’ and ‘Help Me Play’ by using Redis ZSET queues, MySQL persistence, real‑time streamer recommendation, ticket‑based purchase flows, state‑machine order handling, and comprehensive monitoring to ensure reliable, scalable viewer‑streamer gameplay collaboration.
The article explains how using a large OFFSET with MySQL LIMIT forces the engine to scan and sort hundreds of thousands of index and data pages, then presents three concrete optimizations—covering index scans, subqueries, and partitioned tables—to dramatically reduce the work.
This guide explains why MySQL and Java processes can exceed 900% CPU usage in production, and provides step‑by‑step methods—using top, show processlist, jstack, index tuning, caching, and code adjustments—to diagnose, mitigate, and prevent such spikes.
A collection of Zhihu users compares SQL Server and MySQL, highlighting SQL Server's superior locking, performance on massive tables, richer backup and restore tools, tighter C# integration, and enterprise adoption, while acknowledging MySQL's free, lightweight nature and its historical limitations.
This article explains how to assess MySQL table size using table capacity, disk usage, and instance resources, describes why large tables degrade performance, and presents three practical solutions—partitioning, sharding (horizontal/vertical), and hot‑cold data archiving—along with their trade‑offs and implementation tips.
This article explains how using the '_' and '%' wildcards in MySQL GRANT statements can unintentionally broaden privileges, illustrates the security risks with concrete examples and version‑specific behavior, and provides practical guidance on escaping wildcards, auditing existing grants, and preventing privilege loss.
This article explains what MySQL deadlocks are, outlines the four necessary conditions that cause them, provides a concrete example with SQL code, and offers practical strategies such as lock ordering, timeout settings, and MySQL’s built‑in detection to prevent and resolve deadlocks.
This article explains how InnoDB’s buffer pool works, covering its purpose, configuration parameters, internal structures such as free, flush, and LRU lists, multi‑instance handling, adaptive hash indexing, and strategies for page eviction and performance optimization.
This article explains the core components of MySQL's InnoDB storage engine—including undo log, redo log, buffer pool, change buffer, and binlog—detailing their roles in data writing, crash recovery, MVCC, and master‑slave synchronization, and describing related flushing strategies and LRU management.
This guide provides a collection of ready‑to‑use Bash scripts for daily Nginx log rotation, log analysis, server resource monitoring, inode warnings, malicious IP blocking, bulk file renaming, scheduled log clearing, Apache process supervision, and both single‑ and multi‑database MySQL backups.
This article examines common backend anti‑patterns such as inefficient MySQL for‑loops, heavy multi‑query methods, full‑table scans, improper thread‑pool creation, and long‑running transactions, and provides practical optimization recommendations including query consolidation, caching, indexing, and proper resource management.
The article chronicles the evolution of database technology from early hierarchical and network models through relational, column‑store, document, key‑value, graph, time‑series, HTAP, and finally vector databases, detailing each system’s architecture, strengths, limitations, typical uses, and future trends toward specialization, distributed cloud‑native designs, and AI‑driven applications.
This step‑by‑step tutorial shows how to install Percona XtraBackup 2.4, create full, incremental and compressed backups of MySQL 5.1‑5.7, prepare the backups, and restore data using Docker or native Linux packages.
This article provides a detailed, beginner‑friendly tutorial on purchasing an Alibaba Cloud ECS instance, configuring the operating system, and installing JDK 8, Redis, and MySQL 8.0—including necessary firewall and security‑group settings—to launch a functional web‑site environment.
This article explains the design and implementation of MySQL's SQL flow‑control feature, covering its purpose, architecture, external commands, rule management, matching logic, data‑access layer, performance considerations, usage guidelines, and known limitations.
This article investigates whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting a series of current‑read and snapshot‑read experiments, analyzing transaction behavior, MVCC mechanisms, and offering recommendations to avoid phantom anomalies.
This article compares ClickHouse and MySQL by examining benchmark results, MPP architecture, columnar storage, compression techniques, vectorized execution, and index designs, showing why ClickHouse delivers dramatically higher query performance on massive data sets.
This guide shows how to edit the MySQL server startup script to add skip-grant and skip-networking options, restart the MySQL service, change the root user password, and then revert the script changes, providing step‑by‑step commands and screenshots for each operation.
This article walks through converting a SpringBoot + MyBatisPlus project from MySQL to PostgreSQL, detailing driver setup, JDBC changes, common SQL pitfalls, type‑conversion issues, and provides helper scripts for bulk schema adjustments.
This article examines how MySQL 8.0.32 InnoDB handles locking when inserting a row that violates a unique index under the READ‑COMMITTED isolation level, detailing the preparation steps, observed lock types, the underlying mechanism, and the final cleanup actions.
This guide explains the MariaDB MaxScale NoSQL protocol module, its key features, typical use cases, step‑by‑step installation, configuration, and how to perform MongoDB‑compatible operations that are transparently stored in MySQL/MariaDB.
This article investigates whether defining a VARCHAR column with a larger length (e.g., VARCHAR(500) vs VARCHAR(50)) changes storage consumption and query speed in MySQL by creating two identical tables, inserting one million rows, measuring space usage, and benchmarking various SELECT and ORDER BY operations.
The article compares MySQL and MariaDB, tracing MySQL’s historic rise, explaining why MariaDB was forked, detailing their governance, enumerating MariaDB’s additional storage engines and performance optimizations, highlighting new features, and analyzing recent adoption trends that suggest MariaDB may eventually overtake MySQL.
This article provides a thorough Java backend interview preparation guide covering collection frameworks, JVM architecture and garbage‑collection, operating‑system thread vs process concepts, TCP handshake and termination, MySQL transaction ACID properties and concurrency anomalies, Redis cache consistency strategies, and a sample algorithm problem.
This tutorial guides beginners through creating a complete user registration and login system using PHP for the backend, MySQL for data storage, and HTML, CSS, and vanilla JavaScript for the frontend, covering environment setup, database creation, form design, server‑side logic, and basic client‑side validation.
Learn how to identify and resolve extreme CPU usage spikes—up to 900%—in MySQL and Java applications by using top, show processlist, indexing, caching, thread analysis, and code adjustments, with real‑world examples and step‑by‑step command guides.
This guide details essential MySQL upgrade considerations, backup warnings, version‑specific changes, and step‑by‑step procedures for both in‑place and logical migrations, helping administrators safely move from MySQL 5.6 to 5.7 while avoiding common pitfalls.
This article compares MySQL's DISTINCT and GROUP BY clauses, detailing when they deliver identical performance with indexed columns, why DISTINCT can be faster without indexes, how each handles NULLs and multi‑column deduplication, and provides practical syntax examples and optimization guidance.
This article explains MySQL index classifications, the inner workings of B+‑tree and hash indexes, the differences between clustered and secondary indexes, and advanced concepts such as covering indexes, index push‑down, index merge, cost‑based index selection, and common pitfalls that cause index inefficiency.
This article examines how InnoDB handles row locking when an INSERT triggers a primary‑key index conflict, detailing the lock types on primary and unique indexes, the implicit‑to‑explicit lock conversion, and the role of the supremum record in the locking process.
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 tips for interpreting their diagnostic reports to improve database configuration and query efficiency.
This article provides a comprehensive overview of MySQL indexing, covering index classifications, B+Tree and Hash structures, primary and secondary (clustered and non‑clustered) indexes, covering index push‑down, index merge, covering indexes, cost‑based index selection, and common pitfalls that cause index inefficiency.
This article explains MySQL's SQL_MODE system variable, its default values for different versions, common configuration options, how it influences data validation and migration, and provides practical code examples for adjusting and testing SQL_MODE settings.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering and non‑clustering indexes, hash indexes, B+Tree mechanics, index lookup, covering indexes, index push‑down, index merge, cost‑based index selection, common pitfalls, and best practices for designing effective indexes.
The article describes a MySQL 8.0.18 backup failure caused by an undo log remaining in cleanup state for over ten hours, details the error logs, shows how the ChatDBA AI tool analyses and resolves the issue, compares it with ChatGPT‑4o, and provides related activity previews.
This article explains why the number in MySQL int(N) does not restrict the column’s numeric range, demonstrates that int(1) can store the full unsigned range, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
The article compares two backend implementations for drag‑and‑drop ordering—using a simple position column (array model) and a doubly linked list with prev_id and sibling_id—detailing their MySQL schemas, update logic, performance trade‑offs, and practical considerations such as pagination and locking.
MySQL 8.4 becomes the new LTS release, offering detailed upgrade‑downgrade compatibility tables, dozens of InnoDB and temporary‑table parameter defaults, MGR and replication enhancements, new commands, and a list of deprecated features to help DBAs plan migrations efficiently.
This article compiles ten practical operations‑engineer guidelines—ranging from change rollbacks and safe command aliases to backup verification, monitoring, and cautious automated failover—to help maintain high availability and avoid costly production incidents.
This tutorial explains how to connect to a MySQL database using PHP's mysqli extension, execute queries, and retrieve results with mysqli_fetch_assoc, providing step-by-step code examples for connection, querying, and a complete script.
This article walks through a real‑world MySQL InnoDB deadlock case, detailing log analysis, reproducing the issue with test data, explaining gap and insert‑intention locks, and presenting a practical solution that checks existence before updating or inserting to prevent deadlocks.
This guide explains how to export MongoDB collections to JSON, load them into DuckDB, generate compatible table schemas, and then transfer the data efficiently into MySQL using DuckDB as an intermediate processing engine.
This article walks you through designing a lightweight, high‑performance MySQL helper class in Python, covering singleton connection management, robust exception handling, logging, context‑managed connections, and practical query/execute methods with example code and usage scenarios.
This article describes a Python Flask‑based system that crawls tourism site reviews, processes the data with pandas, stores it in MySQL, and visualizes sentiment analysis, keyword frequencies, and rating trends through interactive ECharts charts on a responsive web dashboard.
This article explains the mechanisms behind MySQL auto‑increment primary keys, why gaps appear after failed inserts, unique‑index violations, transaction rollbacks, and batch operations, and how storage engines and innodb_autoinc_lock_mode settings influence the continuity of generated IDs.
To import one billion 1 KB log records stored in HDFS or S3 into MySQL efficiently, the article examines data partitioning, B‑tree index limits, batch insertion, storage engine choices, concurrency control, file‑reading methods, task scheduling with Redis, Redisson, and Zookeeper for reliable, ordered, high‑throughput loading.
This guide introduces AMPHP’s event‑driven async MySQL library for PHP, explains its non‑blocking API, connection‑pool architecture, supported features, and provides step‑by‑step installation and code examples for basic queries, iterators, and transactional operations.
This article examines how InnoDB acquires shared record locks during a duplicate‑primary‑key INSERT, explains the underlying lock‑checking mechanism, demonstrates lock behavior with concurrent DELETE and INSERT transactions, and provides SQL examples and performance_schema queries to illustrate the process.
This article explains how MySQL uses B‑tree‑based indexes, covering balanced binary trees, B‑trees, B+‑trees, various index types, data‑type effects, and best practices for creating and using composite indexes to optimize query performance.
This article compares three inventory deduction approaches—single‑field MySQL updates, sharded MySQL records, and Redis INCRBY with Lua scripting—analyzes their concurrency drawbacks, and provides a complete Java implementation using Redis Lua scripts, distributed locks, and callback‑based stock initialization.
This article explains the challenges of executing full‑table UPDATE statements on large MySQL tables using row‑based binlog replication, analyzes deep pagination issues, and presents a batch‑processing strategy with FORCE INDEX, SQL_NO_CACHE and controlled rate limiting to safely migrate data at scale.
The article reviews MySQL's 29‑year evolution, detailing the 8.0 release’s extensive contributions, new features, bug‑fix statistics, and the vital role of the global community and Oracle’s ongoing investment in both the open‑source edition and the HeatWave cloud service.
This article demonstrates how a non‑unique indexed column that allows NULL values behaves with the != operator in MySQL, showing why null rows are omitted from result sets, how index usage changes, and how to rewrite queries with OR or UNION to obtain correct results while preserving performance.
The article analyzes a production out‑of‑memory crash triggered by a pagination bug that misused the OFFSET parameter, explains why the error escaped testing and code review, and presents corrected pagination techniques for Oracle, MySQL and MyBatis to prevent similar failures.
This article examines whether the REPEATABLE‑READ isolation level in InnoDB fully prevents phantom reads by conducting systematic current‑read and snapshot‑read experiments, analyzing lock mechanisms, MVCC behavior, and presenting detailed SQL scripts, results, and recommendations for avoiding phantom reads.
This guide compares three free MySQL client tools—DBeaver, MySQL Workbench, and HeidiSQL—detailing their features, installation steps, and why MySQL Workbench is especially recommended as a Navicat replacement.
The guide equips candidates for backend interviews by covering essential networking (HTTP/HTTPS, TLS handshake, status codes, headers, OCSP, session resumption, CSRF), RPC frameworks, Java class-loading and JVM memory/GC, OS process/thread scheduling, Spring bean lifecycle, Redis caching pitfalls, and MySQL indexing and query optimization.
GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
The article details how the vivo Game Center’s appointment service migrated billions of rows to a dedicated MySQL database with zero‑downtime by implementing a double‑write strategy via a custom MyBatis interceptor that mirrors updates and queries to the new database, handling full and incremental sync, primary‑key consistency, transaction limits, and final cut‑over steps.
This article explains what implicit locks are in MySQL InnoDB, how to identify their presence on primary and secondary index records, the conditions under which they appear, and the process by which they are converted into explicit locks that can be inspected via performance_schema.
This article presents a detailed walkthrough of a MySQL InnoDB deadlock case, covering background, log inspection, data preparation, reproduction steps, lock analysis, root‑cause explanation, and practical solutions to prevent and resolve similar deadlock issues.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, shows how to create and drop full‑text indexes, and demonstrates the three query modes—Natural Language, Boolean, and Query Expansion—along with their syntax, operators, relevance calculation, and practical examples.
This article details the background, requirements, and evaluation of migration options for the appointment service, explains why a double‑write approach with a custom MyBatis plugin was chosen, and walks through the full‑sync, incremental sync, code refactoring, plugin implementation, switch‑over procedures, and post‑migration validation to achieve reliable data isolation and system stability.
This guide explains how to use MySQL 8.0's --init-file startup option to automate password resets by creating an init script, launching the server with it, and securely cleaning up, highlighting its speed, minimal downtime, and automation suitability.
This article explains the purpose, evolution, configuration parameters, and step‑by‑step maintenance procedures for MySQL Undo tablespaces, covering versions from pre‑5.6 to 8.0 and providing practical commands for viewing, creating, truncating, and dropping Undo tablespaces.
This article introduces ReadySet, an open‑source SQL caching layer for MySQL and PostgreSQL, explains its architecture and advantages, and provides step‑by‑step instructions for installing the Docker image, configuring connections, creating caches, and testing query performance to achieve millisecond‑level response times.
This guide walks through checking the OS architecture, preparing the environment, downloading the appropriate MySQL 5.7 packages for ARM, installing and configuring MySQL, setting up automatic startup and environment variables, initializing the server, securing the root account, and finally configuring master‑slave replication between two servers.
This article shares ten practical operations strategies—including change rollbacks, safe handling of destructive commands, prompt customization, rigorous backup and verification, production environment discipline, careful handovers, robust alerting, cautious automatic failover, meticulous checks, and simplicity—to dramatically improve system reliability and availability.
This article shares ten practical operations strategies—ranging from change‑rollback procedures and cautious handling of destructive commands to robust backup verification, alerting, and meticulous hand‑over practices—that together help teams dramatically reduce system outages and maintain high availability.
The article details a zero‑downtime migration of a high‑traffic reservation service from a shared MySQL instance to an isolated database by employing a double‑write strategy implemented through a custom MyBatis plugin, covering full and incremental sync, consistency verification, traffic shifting, and key lessons on primary‑key handling and asynchronous write side‑effects.
This article provides a step‑by‑step analysis of MySQL 8.0.32 InnoDB deadlock logs, covering preparation, log extraction, auxiliary information, transaction details, row‑lock structures, and the final summary, with full SQL examples and code snippets for deep understanding.
This article explains the MySQL Index Merge access method, details the underlying merge‑sort algorithm, enumerates the three index‑merge strategies, analyzes source‑code snippets, presents a real‑world case where index merge hurts performance, and offers practical optimization recommendations.
This article explains the challenges of performing full‑table updates on large MySQL tables using row‑based binlog replication and presents a step‑by‑step batch‑update strategy that leverages SQL_NO_CACHE, FORCE INDEX, and controlled pagination to minimize binlog volume and maintain performance.
This guide walks through practical MySQL 8.0 security hardening steps using SQL statements to enforce strong passwords, enable SSL, configure connection control, manage user authentication, set file permissions, and verify encryption of logs and tables.
This article demonstrates how to diagnose and fix the MySQL ERROR 1292 caused by assigning an empty string to a DECIMAL column, using the ChatDBA AI assistant to analyze the issue, explain strict mode, and provide a safe SQL solution.
This guide covers practical MySQL performance improvements, including choosing proper data types, avoiding SELECT *, optimizing joins and UNION usage, batch inserts, query cache settings, configuration parameters like innodb_buffer_pool_size, and comprehensive index strategies to accelerate queries and reduce resource consumption.
This guide walks you through preparing multiple virtual machines, configuring MySQL 8.0 server IDs, setting up both classic and GTID‑based master‑slave replication, and managing replication commands, providing clear code snippets and commands for a reliable MySQL clustering environment.
Canva’s media service, handling billions of assets, evolved from a MySQL‑based microservice architecture to a DynamoDB‑backed solution by incrementally migrating metadata, sharding tables, employing real‑time dual writes via SQS, and implementing zero‑downtime cut‑over, dramatically improving latency and scalability.
This article outlines the progressive evolution of database architectures—from simple monolithic designs to cold‑hot separation, read‑write splitting, and horizontal sharding—explaining each pattern, its components, and the performance and scalability benefits they bring to large‑scale systems.
Both DISTINCT and GROUP BY can use indexes when available, giving similar performance, but without indexes DISTINCT is usually faster because GROUP BY may invoke implicit sorting; MySQL 8.0 removed this sorting, making their performance nearly equal, and GROUP BY offers clearer semantics and more flexibility for complex queries.
This article explains Linux I/O schedulers, compares the noop, cfq, and deadline algorithms, shows how to view and modify them on CentOS 6/7, and presents benchmark results indicating that the noop scheduler delivers the best performance on SSDs for MySQL workloads.
This article explains how InnoDB in MySQL 8.0.32 grants table and row locks, detailing the active versus passive acquisition, the lock‑granting algorithms for tables and rows, and the priority, weight, and FIFO rules that determine lock ordering and deadlock handling.
This article explains MySQL InnoDB’s linear and random prefetch mechanisms, the role of the innodb_read_ahead_threshold and innodb_random_read_ahead variables, and how the LRU buffer‑pool list manages hot and cold pages to improve overall query efficiency.
An in‑depth analysis of database change workflows reveals three key players—business developers, DBAs, and change tools—outlines the full lifecycle of a change, illustrates a MySQL VARCHAR length mishap, and argues why the business team should bear primary responsibility while DBAs assume secondary accountability.
This article explains the MySQL 8.0 Clone plugin introduced in version 8.0.17, compares it with Xtrabackup, details its five‑step cloning process, describes the code structure across SQL, plugin, and InnoDB layers, and outlines the page‑archiving subsystem used to track dirty pages during cloning.
