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.
This article explains why MySQL 8.0 appears to lose about 1 GB of disk space when temporary tables are used, detailing the experiment, the role of TempTable, mmap allocation, and how performance_schema and procfs can be used to observe the process.
The article compares five practical methods—single‑server deployment, IP‑based restriction, database‑driven task selection, Redis expiration with distributed lock, and Quartz clustering—to ensure that a scheduled job runs only once across multiple servers, outlining each approach's advantages, drawbacks, and implementation details.
This article explains the concepts, mechanisms, and practical code examples of optimistic and pessimistic locking in MySQL/InnoDB, including version‑based optimistic locks, shared (read) locks, exclusive (write) locks, and how to apply them with Laravel's query builder.
This article explains how to build a location‑based "nearby people" feature by storing coordinates in MySQL, filtering with rectangular bounds, calculating distances in Java, and then scaling the solution with Redis GEO using GeoHash and Sorted Sets for high‑performance proximity queries.
This article experimentally demonstrates how increasing the number of secondary indexes on MySQL tables progressively degrades write operations—including INSERT, UPDATE, DELETE, and LOAD DATA—by measuring execution times across ten tables with one to ten indexes each.
This article explains the root causes of MySQL deadlocks in InnoDB, walks through transaction and lock fundamentals, analyzes real‑world log cases, and presents practical solutions such as adjusting isolation levels, using SELECT FOR UPDATE, adding unique indexes, or checking existence before deletion to eliminate deadlocks.
This tutorial walks through MySQL join techniques—including WHERE, LEFT, and INNER joins—shows how to retrieve specific columns, and provides clear examples of inserting single or multiple rows, updating records, and deleting data, all illustrated with step‑by‑step SQL code and result screenshots.
This article walks you through using Navicat for MySQL to create databases and tables, design table structures, and perform native SQL SELECT queries—including filtering, multiple conditions, and column selection—illustrated with step‑by‑step screenshots and code examples.
This article explains how MySQL implicitly converts mismatched column types—such as comparing a VARCHAR column with a numeric literal—into floating‑point numbers, causing seemingly equal values to match, losing index usage, and potentially producing incorrect query results.
The article explains why a simple GROUP BY query in MySQL 5.6 returns the earliest rows instead of the most recent ones, demonstrates how to correctly obtain the latest record per name using sub‑queries or window functions, and discusses the impact of the ONLY_FULL_GROUP_BY mode in MySQL 5.7.
This guide explains why database backups are essential, walks through creating a backup directory, writing a mysqldump shell script, compressing the dump, granting execution rights, and scheduling the script with cron to run every minute.
This article explains Didi's end‑to‑end architecture for ingesting MySQL data into Hive using real‑time Binlog collection, a customized Canal component, message queues, HDFS storage, Dquality monitoring, and strategies for handling data drift and sharding in large‑scale big‑data environments.
This article explores common causes of MySQL slow queries such as index misuse and lock contention, explains indexing strategies like index pushdown and covering indexes, and then compares Elasticsearch and HBase as complementary solutions for large‑scale search and write‑intensive workloads, offering practical tips for performance optimization.
This article explains how CPU core count versus clock speed and memory size influence MySQL performance, discusses when to prioritize multi‑core or high‑frequency CPUs for concurrency or query speed, and offers practical guidelines for selecting server hardware based on workload characteristics.
This article explains the history and purpose of Unicode, describes how character sets differ from encodings, details the storage formats of UTF‑8, UTF‑16, and UTF‑32, discusses byte order and BOM, and shows common encoding pitfalls in Redis and MySQL with practical solutions.
MySQL deep pagination can be dramatically accelerated by ordering on the primary key, indexing the sort column, and using keyset pagination or a sub‑query join instead of scanning millions of rows, while only minor tweaks like increasing sort_buffer_size provide negligible gains.
This article presents practical MySQL optimization techniques—including disabling keys for MyISAM, sorting InnoDB input, adjusting UNIQUE_CHECKS and AUTOCOMMIT, using multi‑row INSERT, LOAD DATA INFILE, ORDER BY NULL for GROUP BY, index‑aware ORDER BY, OR condition handling, and SQL hints—to dramatically speed up bulk data loads and common queries.
This article introduces a Python 3.6‑based attendance system that combines PyQt5 for the GUI, OpenCV and DeepFace for real‑time face recognition with liveness detection, and MySQL for storing student and class data, detailing its design, database setup, features, and step‑by‑step usage instructions.
Through a series of experiments using MySQL 8.0, this article demonstrates how group commit reduces I/O operations by consolidating multiple transactions into a single commit group, showing that doubling load increases runtime modestly while transaction count and commit groups rise significantly, highlighting performance benefits.
This article outlines a practical method for migrating MySQL tables with millions of rows by exporting data to an outfile, compressing, transferring, and loading it on the target server, while also addressing strict SQL mode errors that can arise during import.
This article explains how MySQL may rewrite a LEFT JOIN as an INNER JOIN during optimization when the WHERE clause filters the joined table, demonstrates the behavior with sample class and student tables, and presents test cases confirming the optimizer's decision.
After a junior staff member mistakenly ran an unchecked rm‑rf command that erased an entire production server, the author details a step‑by‑step recovery using ext3grep, custom shell scripts, extundelete, and MySQL binlog replay, and concludes with lessons on backup, monitoring, and change management.
This article investigates why MySQL queries with large LIMIT offsets become extremely slow, demonstrates the issue with simulated millions‑of‑row datasets, and presents three optimization strategies—including index‑covering subqueries, remembering the last primary‑key position, and applying offset throttling—to achieve consistent, fast pagination performance.
The DTLE 3.21.06.0 release introduces enhanced MySQL trigger, event, procedure, and function handling, RPM upgrade support, source reconnection improvements, HTTP API updates, and several bug fixes, while providing repository, documentation, and download links for the open‑source data transfer component.
This article walks through a practical MySQL tutorial that identifies duplicate records, explains why naïve row‑by‑row deletion is slow, and provides efficient DELETE statements—including derived‑table tricks—to clean up large tables while preserving one record per duplicate group.
This article details a practical MySQL data migration workflow for moving a million‑row table to a new server, comparing mysqldump, custom Go scripts with Redis pipelines, and direct file‑based import/export, and provides step‑by‑step commands, configuration tips, and performance considerations.
This article explains how to prevent data loss in MySQL by enabling backups and row‑based binlog, then details full‑volume recovery with mysqldump or xtrabackup, point‑in‑time restoration using binlog, table‑level recovery, skipping erroneous SQL statements, and introduces open‑source tools such as binlog2sql and MyFlash.
This article examines four Spring Boot @Transactional test cases using a MySQL InnoDB database to determine whether each method rolls back or commits when exceptions are thrown or caught, providing code examples and inviting readers to vote on the outcomes.
This article explains how MySQL processes ORDER BY clauses, illustrates the execution steps with a full‑field index, discusses the impact of sort_buffer and max_length_for_sort_data, and shows how adding composite and covering indexes can eliminate filesort and improve query performance.
This article examines common causes of slow queries in MySQL, presents practical indexing techniques, and extends the discussion to performance considerations in ElasticSearch and HBase, offering combined solutions such as sharding, read/write splitting, and using ES as a search layer for large‑scale systems.
This article explains how to detect table‑space fragmentation in MySQL, demonstrates the performance impact of deleted rows, and provides step‑by‑step commands—including ALTER TABLE FORCE, OPTIMIZE TABLE, and mysqlcheck—to reclaim space and improve query speed.
This article provides a comprehensive overview of InnoDB architecture, covering its memory structures, background threads, and key features like checkpoint mechanisms, double write, and neighbor page flushing, essential for understanding MySQL's default storage engine.
This article explains how B+Tree improves disk I/O efficiency in MySQL InnoDB by detailing disk storage fundamentals, sector/block/page concepts, the differences between B‑Tree and B+Tree, and practical search examples that illustrate reduced I/O operations and faster queries.
This guide demonstrates how to add a custom AOP‑based caching layer to a SpringBoot application, using Redis to store frequently queried MySQL table data, defining @AopCacheEnable and @AopCacheEvict annotations, implementing an aspect, handling key generation, expiration, and cache eviction on CRUD operations.
This article demonstrates how to use a GDB script to monitor the exact moments MySQL checks for a killed DDL operation, identifies the internal InnoDB functions involved, and explains why kill may not take effect immediately.
This article presents Ctrip Finance's design of a unified high‑availability Redis cache service, covering both eventual‑consistency and strong‑consistency scenarios, the overall architecture, data‑accuracy, completeness and availability mechanisms, lock handling, fault‑tolerant updates, and operational recovery strategies.
This article details Ctrip Finance's end‑to‑end data‑foundation architecture that uses MySQL binlog collection via Canal, Kafka streaming, Spark‑Streaming persistence to HDFS, and a merge process to produce timely MySQL‑Hive snapshots, addressing performance, consistency, and delete‑handling challenges.
This article provides a comprehensive, hands‑on guide to building a MySQL master‑slave cluster with GTID replication and configuring MHA for automatic failover, covering environment preparation, password‑less SSH, MySQL settings, MHA installation, custom scripts, testing, and the key advantages of the solution.
This article examines the challenges of traditional MySQL deployments, explains how containerization with Docker and Kubernetes addresses cost, elasticity, and operational complexity, and introduces RadonDB MySQL's Helm and Operator solutions for high‑availability cloud‑native database management.
This article explains why MySQL's default full‑text index struggles with Chinese, demonstrates how to configure token size parameters, activate the ngram parser plugin, and adjust queries (including Boolean mode) to achieve reliable Chinese full‑text search results.
DiDi’s real‑time MySQL‑to‑Hive pipeline captures row‑mode binlog with a custom Canal component, converts it to JSON, streams it via Kafka to HDFS, restores it into Hive tables, and uses Dquality for integrity, achieving millisecond latency for over 19,000 daily sync tasks handling roughly 50 TB of data.
This tutorial walks through creating a Spring Boot project, configuring Maven dependencies, setting up application.yml and properties, defining POJOs, MyBatis mapper XML, service and controller layers, and building simple Thymeleaf login and registration pages, with full code snippets for each step.
The article investigates whether MySQL is affected by the Unix 2038 bug by adjusting the system date to 2038, documenting the shutdown behavior, analyzing the timestamp limitations in MySQL source code, and discussing the impact on current versions and possible mitigation strategies.
This article explains how MySQL treats backslashes in INSERT and SELECT statements, demonstrates the resulting storage differences with practical examples, and clarifies the double‑escaping rules that developers must handle to avoid unexpected query results.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, then compares Elasticsearch and HBase as complementary storage and search solutions, providing practical code examples and best‑practice recommendations.
This article presents a student‑crafted, high‑fidelity Xiaomi Mall clone built with a Vue‑based front‑end (Vue‑router, Vuex, Element‑ui, Axios) and a Node.js Koa back‑end using MySQL, detailing its architecture, key pages, authentication, shopping cart, order processing, and deployment.
Learn step-by-step how to create a reliable MySQL database backup solution by writing a shell script, compressing the dump, assigning execution permissions, and scheduling it with cron to run automatically, while covering storage media choices, disk space checks, and verification of the backup process.
This article explains MySQL’s logical architecture, the client‑server communication protocol, how queries are parsed, optimized and executed, the role of the query cache, and provides concrete performance‑tuning advice on schema design, indexing, B+Tree mechanics, and common pitfalls.
Learn how to install and use the Python Faker library to quickly generate realistic fake data—such as names, addresses, emails, and network details—and see a complete example of inserting generated records into a MySQL database for testing purposes.
This article explains how to design and implement a follow/unfollow feature using a MySQL table for persistence and Redis ordered‑set commands (zAdd, zRem, zRange, zCard, zScore) in PHP, covering database schema, code examples, pagination, and status detection.
This article provides a comprehensive collection of practical MySQL and SQL performance optimization techniques, covering index usage, query rewriting, avoiding full table scans, proper data types, efficient joins, backup strategies, and other best practices to improve database speed and reliability.
This article analyzes why MySQL connections remain in a KILLED state after a KILL command, detailing the internal execution flow, thread‑state handling, signal‑based wake‑ups, and reproducing cases that expose rollback, lock‑wait, and concurrency issues that delay connection termination.
This article traces the evolution from ASCII to Unicode, explains how Unicode defines universal code points, compares the UTF‑8, UTF‑16 and UTF‑32 encoding schemes, discusses byte order and BOM, and shows practical fixes for common encoding problems in Redis and MySQL.
This article examines why MySQL’s KILL command often leaves a connection in a prolonged “Killed” state, analyzes the 5.7 source‑code flow, explains socket closure, thread flag handling, condition‑variable notifications, and reproduces real‑world cases that illustrate the underlying causes.
This article walks through eight common MySQL performance pitfalls—including inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result push‑down—and shows how to rewrite each query to achieve dramatic speed improvements, often reducing execution time from seconds to milliseconds.
This article explains step‑by‑step how to build a MySQL debug binary inside a Docker container, covering environment preparation, dependency installation, configuring the build with CMake, compiling, and locating the resulting mysqld‑debug executable, while also noting performance trade‑offs of debug builds.
This article explains MySQL index fundamentals, the B+Tree structure, differences between MyISAM and InnoDB implementations, practical indexing strategies, and performance‑tuning techniques such as configuration parameters, EXPLAIN analysis, and optimal index design for real‑world queries.
This guide explains how to protect MySQL data with proper backups and row‑format binlogs, then walk through full restores using mysqldump or xtrabackup, point‑in‑time recovery from binlogs, single‑table restoration, skipping erroneous SQL statements, and flashback tools like binlog2sql and MyFlash.
The article explains how MySQL uses different memory areas such as Thread Memory, Sharing, and InnoDB Buffer Pool, describes the impact of large user tables on query performance, and shows that when a table size exceeds the InnoDB buffer pool capacity, sharding becomes necessary.
This article explains why MySQL chooses Repeatable Read as its default transaction isolation level, contrasting it with Oracle’s default Read Committed, and discusses how binlog formats, GAP locks, and historical bugs influence this design decision.
This article uses a fictional bank scenario to illustrate how database architectures evolve from a single MySQL instance through caching, master‑slave replication, vertical splitting, horizontal sharding, clustering, and finally the adoption of NoSQL and search engines to handle growing traffic and data complexity.
This article explains why MySQL binlog files can become large, demonstrates how to enable the slave_compressed_protocol parameter for binlog compression, presents benchmark results showing reduced network traffic and CPU impact, and outlines compatibility considerations and known bugs.
This article explains MySQL's B-Tree index structure, storage layout, query patterns that benefit from it, common limitations such as left-most prefix rules, and answers why B-Tree is preferred over other structures and why auto-increment primary keys are recommended.
This article examines how misplaced quotation marks in MySQL UPDATE statements can unintentionally set fields to zero, explains MySQL’s implicit type conversion that causes the condition to always evaluate true, and offers practical steps for detecting and preventing such bugs.
Learn how to capture and stream real‑time database changes using Debezium’s distributed CDC framework, configure MySQL binlog, integrate the embedded engine with Spring Boot, and process change events with sample code and Docker setup for robust data pipelines.
A developer’s accidental misplacement of quotation marks in UPDATE statements caused all target fields to become zero, and this article explains the MySQL parsing behavior, implicit type conversion, and how to prevent such silent data corruption.
This weekly roundup presents community news, technical articles, video courses, and open‑source middleware updates, including MySQL and TiDB issue discussions, index design insights, and the latest DBLE and DTLE development progress, bug fixes, and upcoming plans.
Starting with MySQL 8.0.23, columns can be marked INVISIBLE, causing them to be omitted from SELECT * queries unless explicitly referenced; this article explains the feature, demonstrates creation, inspection, DML considerations, schema modifications, backup behavior, and its impact on database design.
This article reviews the history of MySQL and MariaDB, describes a test environment, runs insertion, batch, and query benchmarks (with and without indexes), and concludes that MariaDB generally outperforms MySQL in speed and memory usage while highlighting trade‑offs of indexing.
This guide explains why database backup is essential, shows how to check disk space, create a backup directory, write a MySQL dump shell script, make it executable, schedule it with crontab, and verify the job’s execution on a Linux server.
This article details a step‑by‑step optimization of a Java backend Excel import pipeline, covering requirement analysis, performance bottlenecks, caching strategies, batch insertion techniques, parallel processing, and practical code examples to reduce a 10‑minute import to under two minutes.
This article, the fourth in a MySQL fundamentals series, explains how to diagnose and optimize slow SQL statements using SHOW STATUS, the slow‑query log, SHOW PROCESSLIST, and EXPLAIN, then details index types, creation, usage rules, and how to analyze, check, and optimize tables with MySQL commands.
This article explains why MySQL queries become slow, explores index-related pitfalls and optimization techniques, and then compares ElasticSearch and HBase as complementary solutions for large‑scale data and search scenarios, offering practical tips and code examples.
This article walks through practical MySQL performance tuning techniques, covering how to inspect server status variables, enable and read slow query logs, analyze execution plans with EXPLAIN, understand and apply various index types, and use table analysis, checking, and optimization commands to improve query efficiency.
The article explains why deleting rows in MySQL does not shrink the .ibd file because InnoDB only marks pages as reusable, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains how to monitor MySQL full-text indexes by describing relevant InnoDB parameters, the metadata tables that expose index activity, and step‑by‑step examples that create a sample table, configure monitoring, observe cache behavior, and manage index maintenance operations.
The article explains why an INSERT INTO SELECT statement can lock an entire InnoDB table in MySQL, demonstrates the lock behavior with transaction examples, and offers two solutions—adding an index on the filtered column and adjusting the transaction isolation level—to avoid table‑wide blocking.
The article examines a production MySQL query that suffers severe slowdown due to large LIMIT offsets, demonstrates how to reproduce the issue with massive test data, analyzes the root cause, and presents three optimization strategies—including index covering, keyset pagination, and offset limiting—to dramatically improve query performance.
This article compares the legacy MHA solution with the modern open‑source Xenon tool, explaining Xenon's Raft‑based architecture, automatic leader election, GTID integration, and enterprise‑grade features that make it a superior high‑availability option for MySQL deployments.
This article explains how incorrect placement of quotation marks in MySQL UPDATE statements can cause the SET values to be evaluated as boolean expressions, leading to implicit type conversion that updates fields to zero, and offers practical steps to prevent such errors.
This article explains why traditional databases like MySQL struggle with petabyte‑scale queries, introduces Elasticsearch’s architecture and inverted‑index advantages, and details a practical pipeline using Hive, Canal, Otter, and a custom DTS service to achieve near‑real‑time indexing for massive data volumes.
An engineer describes a production MySQL pagination issue where large offset queries caused severe slowdown, analyzes the root cause, demonstrates data simulation scripts, and presents three optimization strategies—including index covering with subqueries, redefining start positions, and applying offset limits—to improve query performance on massive tables.
This article explains InnoDB's storage hierarchy (sector, block, page), calculates how many rows fit in a 16KB page, shows how B+‑tree height and pointer counts determine total record capacity, and demonstrates the I/O cost of primary and secondary index lookups using practical MySQL commands.
This article presents JD Daojia's practice of containerizing MySQL, detailing the underlying resource platform, custom container scheduling algorithm, high‑availability design, monitoring system, and an automated operations platform that together improve performance, cut costs, and boost operational efficiency.
This article presents a series of MySQL experiments that examine how different isolation levels, partition‑key usage, and comparison operators affect the number and scope of locks acquired on partitioned tables, revealing the impact of partition pruning on lock contention.
