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