Learn how to speed up navigation with the "z" command, enhance Vim with a ready-made config, add a clear MySQL prompt, accurately check OS version, and visualize disk usage using concise terminal commands and scripts.
The article explains MySQL 8.0's new statement_digest() and statement_digest_text() functions, showing how they generate normalized SQL texts and SHA2 hashes to group similar queries, and demonstrates practical applications in performance schema analysis and query‑rewrite plugins with concrete code examples.
This article details how to dramatically speed up Excel‑driven fee‑record imports by replacing naïve POI row‑by‑row processing with EasyExcel, caching database look‑ups, using MySQL batch INSERTs, and finally applying parallel streams for multi‑threaded insertion, while also trimming excessive logging.
This article explains how to restore a single table on a MySQL replica after GTID‑based replication errors by using backup snapshots, replication filters, and controlled start‑up points to keep data consistent across master and slave.
This article explores MySQL performance limits, concurrency settings, query‑time best practices, table design choices, index classifications, and a comprehensive set of SQL optimizations—including batch processing, operator rewrites, and pagination tricks—to help developers eliminate slow queries and build scalable database solutions.
This article explains the SQL standard restriction that only grouped keys, aggregate functions, or constants may appear in the SELECT clause of a GROUP BY query, demonstrates the resulting error in MySQL, and discusses how SQL modes like ONLY_FULL_GROUP_BY enforce this rule.
This guide shows how to speed up Linux command‑line work by installing the z directory‑jump script, applying a feature‑rich Vim configuration, customizing MySQL prompts, accurately checking OS version, and using a more visual disk‑usage tool.
An overview of MySQL 8.0’s release timeline, including major GA versions, and a detailed summary of its new capabilities such as transactional data dictionary, atomic DDL, enhanced security, role support, InnoDB improvements, JSON enhancements, optimizer extensions, backup lock, connection management, and other performance and management features.
This article demonstrates how to recover mistakenly deleted InnoDB .ibd files by locating the open file handle through Linux procfs, locking database traffic, copying the hidden file, and restoring it to a running MySQL instance.
This article explains the InnoDB logical storage hierarchy of segments, extents, and pages, then details MySQL horizontal partitioning concepts, types (RANGE, LIST, HASH, KEY), example DDL and DML, and discusses how partitioning impacts performance for OLTP and OLAP workloads.
This article investigates why MySQL recommends auto_increment primary keys over UUID or snowflake IDs by creating three tables with different key strategies, running insertion benchmarks using Spring Boot, and analyzing index structures, performance results, and the trade‑offs of each approach.
This article details a performance comparison between UCloud's KuaiJie‑based UDB service and self‑built MySQL instances, covering environment setup, sysbench benchmarking, result analysis, and cost evaluation to guide optimal database instance selection.
This article examines performance problems with a 20‑million‑row MySQL user‑log table on Alibaba Cloud RDS, outlines three solution paths—optimizing the existing database, migrating to a MySQL‑compatible high‑performance service, and adopting a big‑data engine—and provides detailed guidance on schema design, indexing, partitioning, and practical SQL tweaks.
This article explains the scenarios where MySQL compressed tables are appropriate, describes how to monitor their health using InnoDB CMP tables in information_schema, and provides practical examples of creation, performance comparison, and update/delete operations to illustrate best‑practice usage.
This tutorial walks you through downloading RuoYi-Vue 3.1, creating a MySQL database, importing required SQL files, configuring Redis and application settings, and then launching the Spring Boot back‑end and Vue front‑end so you can access the admin console at http://localhost:81/.
This article explains the concept of caching, why database caching with Redis is needed for MySQL, the consistency challenges it introduces, and compares four practical solutions—TTL, write‑through, message‑queue, and binlog replication—offering guidance on selecting the appropriate approach for different latency and reliability requirements.
The article explains autocomplete’s user‑friendly benefits and compares three backend approaches—simple MySQL LIKE queries, Redis sorted‑set range scans, and Elasticsearch’s completion suggester with FST indexing—highlighting their performance, scalability, and feature trade‑offs to help choose the best solution for a given dataset and latency requirement.
This article explains the three core MySQL logs—binlog, redo log, and undo log—their structures, formats, configuration parameters, and how they work together to provide replication, crash recovery, and transactional atomicity while balancing performance and consistency.
A production MySQL server was overwhelmed by a high‑volume query that ignored a crucial composite index because the leftmost key was missing, leading to full‑table scans, CPU saturation, and a half‑hour outage, and the post explains how the issue was diagnosed and fixed.
This guide explains how to obtain the MariaDB audit plugin, migrate it to a MySQL 5.7 environment, install and load the plugin, configure audit parameters, and manage audit log files and formats for effective database activity monitoring.
This article investigates MySQL's recommendation against UUID primary keys by creating three tables with auto‑increment, UUID, and random Snowflake IDs, benchmarking insert and query speeds using Spring JDBC, analyzing index structures, and discussing the trade‑offs of each approach.
This article explains why database caching is essential, outlines the consistency challenges of using Redis as a MySQL cache, and compares four practical synchronization solutions—ranging from simple TTL expiration to binlog subscription—highlighting their advantages, drawbacks, and appropriate selection criteria.
A production MySQL server was overwhelmed by a full‑table‑scan query that omitted the leftmost index column, causing CPU saturation and read‑only alerts; the article explains the root cause, temporary mitigation, the leftmost‑match principle, code‑level bug, and key lessons learned.
This article explains how MySQL implements transaction features such as atomicity, durability, and isolation by using redo logs, undo logs, lock mechanisms, and MVCC, detailing their roles, generation processes, and the impact of different isolation levels on data consistency.
This article examines why a MySQL 5.6 RDS table with tens of millions of rows becomes unbearably slow, then presents three concrete approaches—optimizing the existing database, migrating to a MySQL‑compatible service, and adopting a big‑data engine—detailing design, indexing, partitioning, sharding, and cloud options to restore performance.
This article explains why caching is essential for high‑traffic MySQL applications, outlines the consistency challenges of using Redis as a cache, and compares four practical synchronization solutions—TTL‑only, write‑through, message‑queue‑based, and binlog‑driven—highlighting their advantages, drawbacks, and selection guidelines.
The article explains why MySQL databases need regular backup, describes common data‑loss scenarios, classifies backup types (hot, cold, warm) and methods (logical, physical), lists popular tools, and provides detailed command‑line examples for backing up, restoring, migrating, and exporting data.
This article explains MySQL 8.0.20's lexical analysis, describing the lexer’s role, the state‑machine implementation, step‑by‑step debugging with LLDB on macOS, the init_state_maps function, key source code snippets, and the optimizations introduced in this version.
This article details the design, evolution, and operational practices of Ctrip's MySQL database release system, covering its three major versions, the adoption of gh‑ost, safety mechanisms, real‑world case studies, and performance improvements that enable reliable, low‑impact schema changes at scale.
The first episode of the “MySQL Chōng Chōng Chōng” livestream discusses why now is an excellent time to upgrade to MySQL 8.0, covering fast add‑column, Atomic DDL, Hash Join, Group Replication, authentication changes, and includes thanks to sponsors and a preview of the next show.
This guide demonstrates a complete MySQL disaster‑recovery workflow that combines LVM snapshot technology with binary‑log point‑in‑time recovery, allowing you to restore a dropped database by creating a snapshot, extracting it, and replaying binlog events up to the failure moment.
This article benchmarks UCloud’s Kuaijie‑based UDB against a self‑built MySQL instance on UHost, detailing environment setup, sysbench testing parameters, QPS, insert/delete and update results, and evaluates performance gains of over 20% alongside a cost analysis showing UDB’s better price‑performance ratio.
This article presents a systematic guide to real‑time MySQL binlog ingestion, outlining three core principles—decoupling from business data, handling schema changes, and ensuring traceability—followed by concrete Debezium‑Kafka‑Hive solutions, scenario‑specific tactics, and practical tips for reliable data pipelines.
This article presents a thorough overview of MySQL performance tuning, covering step‑by‑step SQL optimization techniques, common pitfalls, index and table‑structure improvements, cache parameter adjustments, and practical guidelines to reduce I/O and CPU bottlenecks while maintaining data integrity.
This article introduces the SlightShift MySQL high‑availability solution, detailing cloud‑native design principles, architecture, key technologies such as Raft‑based leader election, Local PV storage, ProxySQL routing, automated failover, backup/recovery, and the operator‑driven declarative management that enable scalable, low‑cost, resilient MySQL deployments on Kubernetes.
This article presents a practical, step‑by‑step walkthrough of identifying, diagnosing, and resolving high‑concurrency performance problems in a Java‑based SSM e‑commerce application, covering monitoring, root‑cause analysis, JVM/Tomcat/Redis/JDBC tuning, code improvements, and the resulting system stability.
This comprehensive guide covers MySQL fundamentals—including primary, foreign, candidate, and super keys—auto‑increment primary keys, triggers, stored procedures, views, cursor usage, index types and design, transaction properties and isolation levels, storage engine differences, query execution order, EXPLAIN analysis, lock mechanisms, replication strategies, high‑concurrency solutions, and crash‑recovery via REDO and UNDO logs, providing practical examples and code snippets for each concept.
This article explains the purpose, structure, and usage scenarios of MySQL's binlog, redo log, and undo log, detailing how they support replication, crash recovery, and transaction atomicity while balancing performance and data safety.
This article shows how to use MyBatis XML mappers to write MySQL queries that count employees meeting specific criteria, such as those in the current month or before today, while explaining required aliasing and date‑format handling.
This article compiles a comprehensive set of MySQL interview questions covering lock types, storage engines, data types, indexes, transactions, SQL functions, and optimization techniques, providing concise explanations and practical examples to help readers master essential database concepts.
This article analyzes a real‑world incident where using INSERT INTO SELECT to migrate large order data caused full‑table scans and locking in MySQL, explains the underlying lock behavior, and presents a solution that adds an index and forces its use to avoid the problem.
This guide explains each column returned by MySQL's EXPLAIN output, details join types, possible keys, extra information, and shows how to use EXPLAIN EXTENDED, SHOW PROFILE, and optimizer trace to analyze and optimize SQL queries effectively.
Facing high licensing costs and poor scalability, Lujinso embarked on an 18‑month, self‑driven project to replace 90% of its Oracle databases—spanning billions of rows across financial services—with MySQL, supplemented by Elasticsearch, Redis, TiDB and HBase, using an automated migration platform.
This article outlines twenty practical principles for optimizing MySQL indexes, covering topics such as LIKE wildcards, UNION/IN/OR usage, negative conditions, composite index prefix rules, range queries, function avoidance, covering indexes, prefix indexes, pagination strategies, and common misconceptions, all illustrated with SQL examples and performance tips.
This article explains why using indexes does not guarantee fast MySQL queries, examines full index scans, index selectivity, the cost of back‑table lookups, and demonstrates advanced techniques such as index condition push‑down and virtual columns to improve performance.
This article explains what database indexes are, outlines their advantages and disadvantages, advises when to create or avoid them, compares common index data structures such as B+Tree, Hash, and FullText, and details MySQL's MyISAM and InnoDB index implementations.
This article explains the data‑delay problem inherent in asynchronous MySQL master‑slave replication, demonstrates how Sharding‑JDBC provides read‑write splitting and forced‑master routing via HintManager, and offers configuration and code examples as well as alternative async‑query strategies for real‑time consistency.
iQIYI’s custom MySQL HA solution replaces the single‑point‑failure MHA manager with a Raft‑based trio of HA Masters and HA Agents that continuously monitor instances, automatically perform same‑DC, cross‑DC, or cross‑region failovers, reconcile diff binlogs, ensure data consistency, and integrate with CMDB, tracing and chaos‑engineering platforms.
MySQL 8.x adds a lightweight backup lock, LOCK INSTANCE FOR BACKUP, which permits DML during online backups while restricting only file creation, account changes, and certain table operations, requiring BACKUP_ADMIN privilege and offering a less disruptive alternative to FTWRL, used by MySQL Enterprise Backup and Percona Xtrabackup.
This article investigates MySQL binlog numbering, discovers that the maximum sequence number is 2^31‑1 (2147483647), shows through three experiments how MySQL behaves when approaching or hitting this limit, and explains the resulting warnings and server shutdown.
This article walks through implementing an avatar upload feature in a front‑end/back‑end separated project using Vue with ElementUI, SpringBoot, and MySQL, covering configuration, image handling, database storage, and front‑end display, and analyzes the RuoYi source code with illustrative screenshots.
This article explains how MySQL's EXPLAIN command reveals the execution plan of a query, detailing each column such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, extra and filtered, while also covering its limitations and a practical case study.
Three leading database experts from Tencent and cloud firms share how deep curiosity, hands‑on experimentation, persistent learning—combined with mastering distributed, cloud, and autonomous databases—forge an outstanding data craftsman, while advising newcomers to master performance, security, one DB per category, and start with open‑source tools.
The article shares the author's recent move to Xi'an, discusses the local job market, and provides detailed interview questions and answers on Java backend topics such as Redis replication, Kafka performance, MySQL transactions, and JVM garbage collection to help job seekers prepare effectively.
This article investigates why the Seconds_Behind_Master metric fluctuates in a dual‑master MySQL setup, explains the underlying replication logic, reproduces the issue, and analyses the handling of ROTATE_EVENT and other binlog events in both parallel and non‑parallel replication modes using source code excerpts.
This article provides a comprehensive interview‑style overview of MySQL fundamentals, covering relational database concepts, data types, storage engines, SQL syntax, version checking, normal forms, views, stored procedures, and performance tips, all essential for backend developers and database professionals.
This guide explains what MySQL temporary tables are, how they exist only for the current connection, provides step‑by‑step SQL examples for creating, inserting, querying, and dropping a temporary table, and discusses practical scenarios such as high‑concurrency writes.
This article presents a systematic performance test of Hibernate and MyBatis covering bulk inserts, single‑table queries, and various association queries on MySQL, analyzes the impact of lazy versus eager loading, caching, and data volume, and provides practical recommendations for choosing the appropriate ORM framework.
This article investigates the practical limits of joining more than three tables in MySQL by designing experiments with up to 1.5 billion rows, comparing indexed and non‑indexed queries, and contrasting the results with Oracle's performance, while providing full SQL scripts for data generation and analysis.
This document explains the differences between OLTP and OLAP workloads, introduces the CSTORE columnar storage engine architecture, its core technologies, performance advantages, typical use cases, benchmark results, and future development plans for MySQL 8.0.
Tencent Cloud MySQL 8.0 builds on official MySQL improvements with eight enterprise‑grade enhancements—TDE encryption, kernel‑level audit, multi‑queue thread pool, strong consistency, AEP SSD support, lightweight AP mode, hotspot updates, and SQL rate‑limiting—delivering high‑performance, secure, and scalable solutions for e‑commerce, gaming, finance, and new‑retail workloads while paving the way for cloud‑native, AI‑driven database evolution.
The article demonstrates how moving the LIMIT‑OFFSET condition into a subquery that selects only primary‑key IDs and then joining back to the main table can cut execution time from over 16 seconds to a few hundred milliseconds by avoiding massive index and data‑page scans.
This article explains how MySQL parses SQL statements using Yacc, detailing the grammar structure, definition and rule sections, relevant source files, parse‑tree node declarations, common error messages, and the call stacks involved in both parsing and pre‑parsing phases.
This article introduces MySQL 8.0.20 Group Replication, covering single‑master and multi‑master modes, monitoring, failover procedures, abnormal recovery, flow control, performance testing, encountered issues, and limitations, and provides a downloadable PDF with detailed documentation hosted on Baidu Cloud.
This article examines how MySQL Group Replication's message cache fills under load, the impact of the group_replication_message_cache_size and group_replication_member_expel_timeout parameters, and provides experimental observations and practical recommendations for balancing reliability, memory usage, and data consistency.
The article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete examples and best‑practice recommendations to prefer NOT NULL constraints whenever possible.
A production MySQL query that should have returned instantly took 44 seconds because the optimizer selected the primary key index instead of a suitable composite index, and the article explains the root cause, the optimizer's decision process, and multiple practical solutions to prevent such slow‑query incidents.
This tutorial explains how to obtain geographic coordinates, store them as MySQL POLYGON types following OGC standards, insert campus area data, and query with spatial functions and SPATIAL indexes to determine whether a user is within a defined check‑in region.
The article details the architecture of 58.com’s SSP advertising platform, identifies three key reliability challenges—data consistency, interface regression, and storage synchronization—and presents a three‑layer testing quality system comprising web‑layer validation, service‑layer automated testing, and data‑layer monitoring with concrete tools and future improvement plans.
This article explains the concept of MySQL compressed tables, highlights strong compression products, discusses why and when to use them, outlines their advantages and drawbacks, details supported compression algorithms, and provides step‑by‑step examples for both MyISAM and InnoDB engines along with their impact on B‑tree pages and the InnoDB buffer pool.
This article explains how to recover MySQL data by preparing backups with binlog enabled, performing full and point‑in‑time restores using mysqldump, xtrabackup, or binlog, restoring individual tables, skipping unwanted SQL statements, and using flashback tools such as binlog2sql and MyFlash.
This article explains the different lock mechanisms used by MySQL storage engines—including MyISAM table‑level locks and InnoDB row‑level locks—covers lock compatibility, scheduling, transaction properties, optimistic locking, and practical strategies to avoid lock conflicts and deadlocks.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
The article repeats a performance test on a 140 GB MySQL 8 database (innodb_buffer_pool_size=140G) using various innodb_buffer_pool_instances values, showing that increasing the instances improves throughput up to 64 instances, after which no further gains are observed.
This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
This article explains how MySQL's AUTO_INCREMENT generates unique identifiers, the constraints on its use, how deletions affect the sequence, the role of LAST_INSERT_ID in concurrent environments, and best practices for managing auto‑increment columns in InnoDB tables.
Using sysbench oltp_read_write benchmarks on a 100 GB database with a 25 GB buffer pool, this article evaluates the impact of varying innodb_buffer_pool_instances (1, 2, 4, 8, 16, 32, 64) on throughput and variability, concluding that 64 instances yields the highest performance.
This article examines common requirements for like, comment, and collection features in apps, compares MySQL partition‑and‑cache designs with Redis storage and caching solutions, provides schema examples, query patterns, and discusses trade‑offs, scalability, and data consistency considerations.
This article explains why using NULL columns in MySQL can hurt performance and increase storage, debunks common myths with official documentation, and provides practical reasons and code examples for preferring NOT NULL constraints in table design.
This article explores practical database designs for app features like likes, comments, and collections, comparing MySQL partition‑plus‑cache approaches with Redis storage and caching solutions, detailing schema definitions, query examples, Redis commands for various scenarios, and discussing scalability and consistency trade‑offs.
The article presents a detailed benchmark of ScaleFlux CSD 2000 storage against Intel SSDs using Percona Server for MySQL 8.0.19 and sysbench, showing how built‑in compression and atomic‑write features give ScaleFlux a clear advantage in large, compressible data sets and high‑concurrency scenarios.
This article presents a comprehensive guide for transferring massive MySQL datasets to HBase, covering environment setup on Ubuntu, three synchronization methods—MySQL LOAD DATA, a Kafka‑Thrift pipeline using Maxwell, and real‑time Flink processing—along with performance comparisons and practical tips for Hadoop, HBase, Kafka, Zookeeper, Phoenix, and related tools.
This guide introduces several handy Linux command-line tricks, including the 'z' shortcut for rapid directory navigation, a feature‑rich Vim configuration, customizing MySQL prompts, reliable methods to display system version details, and concise commands for checking disk usage, helping users streamline their workflow.
When a business platform’s settings page took 36 seconds to load, we used Chrome’s Network timing, Python profiling, and MySQL query analysis to identify backend bottlenecks, then applied redesign, thread‑pooling, batch queries, and ORM optimizations, reducing response time from 37.6 s to 1.47 s.
This article explains why using indexes does not guarantee fast queries in MySQL, analyzes full index scans, index selectivity, the cost of row look‑ups (back‑to‑table), and presents optimization techniques such as index condition pushdown and virtual columns to improve performance.
This comprehensive guide details three practical methods for syncing massive MySQL datasets to HBase—including Sqoop, Kafka‑Thrift, and Flink pipelines—covering environment setup, configuration, code examples, performance comparisons, and optimization tips for large‑scale data ingestion and querying.
This article explains how to install, configure, and test MySQL 8.x query‑rewriter and DDL‑rewriter plugins, demonstrating environment setup, sample SQL scripts, rule creation, and the impact on query performance and CREATE TABLE statements.
This article analyzes MySQL data‑update loss caused by writes on master‑slave pairs, explains BEFORE and AFTER IMAGE handling in row‑based binlogs, examines the binlog_row_image and slave_rows_search_algorithms parameters, and details the implementation of hash, index, and table scans during slave replay.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
This article explains database concurrency control mechanisms, detailing transaction fundamentals, isolation levels, associated anomalies, and how DB2, MySQL, and Oracle implement locks and isolation to ensure data consistency while balancing performance.
This article explains the principles of MySQL performance optimization, distinguishes active and passive approaches, and provides concrete solutions for slow single queries, partially slow queries, and overall slow queries through proper indexing, data partitioning, slow‑query‑log configuration, and read‑write splitting.
This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.
This article explains MySQL's implicit and explicit locking mechanisms—including table, row, and intention locks—covers lock modes, MVCC, transaction isolation levels, optimistic and pessimistic locking, gap locks, and deadlock causes and solutions, providing practical SQL examples for each concept.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This comprehensive guide covers MySQL index creation syntax, various index types—including primary, unique, full‑text, and composite—explains their advantages and drawbacks, details underlying B‑Tree and B+Tree structures, and provides practical strategies and optimization tips for effective index usage.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains how to locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
This article explains the three types of MySQL InnoDB tablespaces—system, single, and general—detailing their structures, configuration options, advantages, disadvantages, and practical procedures for creation, migration, optimization, and removal, with concrete command‑line examples.
Learn step‑by‑step how to set up a binlog‑based MySQL master‑slave replication using Docker‑Compose and Dockerfiles, covering directory structure, configuration files, container creation, verification, and troubleshooting, so you can quickly deploy a reliable MySQL replication environment.
