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.
This article examines common MySQL performance pitfalls such as inefficient LIMIT offsets, implicit type conversions, suboptimal UPDATE/DELETE joins, mixed sorting, misuse of EXISTS, and limited condition pushdown, then demonstrates concrete rewrites—including range reduction and WITH clauses—that reduce execution times from seconds to milliseconds.
This article explains MySQL's three time types, highlights the quirks and automatic defaults of TIMESTAMP—including the 2038 problem, high‑concurrency issues, and unexpected behavior in non‑strict mode—provides practical examples, and offers guidance on configuring server variables or switching to DATETIME for safer handling.
The article explains the functional differences and performance characteristics of MySQL's count(1), count(*), and count(column) expressions, showing when each is appropriate and providing a concrete example with query results to illustrate their behavior.
This article explains the concept of database transactions, the ACID properties, MySQL's four isolation levels, the phenomena they cause such as dirty reads, non‑repeatable reads and phantom reads, and provides step‑by‑step experiments demonstrating each isolation level in practice.
This article explains how MySQL, when run by a non‑root user with limited system resources, automatically adjusts the open_files_limit, max_connections, and table_open_cache parameters by calculating required file descriptors, comparing them to system limits, and correcting the configuration values accordingly.
This article explains two methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers combined with a UDF function and parsing MySQL binlog via Alibaba's Canal—detailing their principles, implementation steps, advantages, limitations, and practical deployment considerations.
The article explains why multithreaded inserts can be faster than single‑threaded ones in MySQL, breaks down the time spent on each step of an insert operation, and presents practical techniques such as using PreparedStatement, multi‑value INSERT statements, and transaction batching to dramatically reduce insertion time.
Learn how to completely uninstall any existing MySQL, install MySQL 8.0 via dnf or yum, verify the installation, configure it to start on boot, manage root passwords, and set up remote access with Navicat, all with clear commands and screenshots.
A developer’s production fix using dozens of UPDATE statements unintentionally set a column to zero because of misplaced quotation marks, illustrating how MySQL’s implicit conversion and syntax quirks can turn seemingly correct SQL into disastrous data loss.
This article introduces the TXSQL query optimizer built on MySQL 8.0.22, detailing its cascades‑style framework, transformation rewrite rules such as outer‑join elimination and subquery flattening, join‑order heuristics, cost‑model configuration, and execution strategies, providing a comprehensive overview of its design and enhancements.
This article explains common causes of slow MySQL queries, how proper indexing and lock handling can improve performance, introduces Elasticsearch’s inverted‑index advantages and suitable use cases, and outlines HBase’s column‑family storage model and row‑key design for large‑scale data.
This article explains how Alibaba's Druid JDBC pool validates MySQL connections, compares the default com_ping method with the validationQuery approach, shows configuration pitfalls, provides Java code examples, and demonstrates verification using tcpdump and MySQL general logs.
This article explains how Canal, Maxwell, Databus, and Alibaba Cloud DTS capture and stream MySQL binlog changes in real time, detailing their architectures, processing steps, and key features such as filtering, routing, and high‑availability delivery.
This article explains the concepts, usage scenarios, and differences of various database locks—including shared (S) lock, exclusive (X) lock, mutex, pessimistic and optimistic locks—as well as row‑level, table‑level, and page‑level locks, and it discusses common concurrency problems such as lost updates, non‑repeatable reads, dirty reads, and deadlocks.
This guide presents a detailed MySQL inspection checklist covering operating‑system metrics, critical MySQL parameters, status queries, replication health, high‑availability components, and useful SQL scripts, enabling DBAs to efficiently monitor performance, detect issues, and maintain reliable database services.
This guide explains how to configure a Spring Boot application with MySQL and SQL Server data sources, use Nginx to expose capture‑server images as URLs, and implement Vue components for on‑demand photo preview of traffic‑violation records.
This article explores the common causes of MySQL slow queries, explains index usage and pitfalls, introduces ElasticSearch architecture and its fast search capabilities, and outlines HBase's column‑family storage model and suitable use cases for large‑scale data systems.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, including detailed explanations of key output fields and practical code examples.
This article details the billing group's challenges with single‑node MySQL, the evaluation of alternative databases such as TiDB and CockroachDB, performance testing, migration strategies, operational best practices, and the final decision to adopt TiDB for scalable, high‑availability data services.
This article explains why MySQL queries become slow, how indexes work and fail, the impact of MDL locks, large‑table challenges, sharding and read‑write splitting strategies, then introduces Elasticsearch’s search capabilities and HBase’s column‑family storage, offering practical guidance for each technology.
This article explains how MySQL InnoDB guarantees the ACID properties—Atomicity, Consistency, Isolation, and Durability—through its lock mechanisms, multi‑version concurrency control, undo/redo logs, buffer pool management, and the interaction between binlog and redo log.
This article explains MySQL full‑text indexing, compares its syntax with ordinary SQL, demonstrates how to create and query a full‑text index using natural language, boolean, and query‑expansion modes, and shows performance differences through execution‑plan analysis and relevance ranking.
The article investigates the Alibaba Java development guideline that forbids joining more than three tables by designing MySQL and Oracle experiments, generating massive test data, measuring query performance, analyzing index usage, and concluding why the rule exists and when it can be relaxed.
This article explains the concepts of strong, weak, and eventual consistency, introduces three classic cache patterns (Cache‑Aside, Read‑Through/Write‑Through, Write‑Behind), and discusses practical strategies such as delayed double‑delete, retry mechanisms, and binlog‑based asynchronous eviction to keep Redis and MySQL data in sync.
A large‑scale IoT service migrated 40 billion rows from MySQL to MongoDB, solving capacity, cost, and data‑skew issues while achieving higher performance, lower storage consumption, and a flexible, highly available architecture, with detailed resource evaluation, migration steps, optimization tactics, and cost‑benefit analysis.
The article explains how incorrectly placed double‑quotation marks in MySQL UPDATE statements change the intended string assignment into a boolean comparison that evaluates to 0, leading to all affected rows being updated with zero values, and shows how to detect and avoid this pitfall.
This article explains why multithreaded inserts into MySQL can outperform single‑threaded writes, breaks down the time spent on connection, parsing and insertion, and presents practical techniques such as prepared statements, batch processing, multi‑value inserts, and transaction batching that together can reduce insert time to seconds for massive data sets.
This comprehensive guide covers MySQL single‑table optimization, field and index design, query best practices, engine choices, system parameters, hardware scaling, read/write splitting, caching strategies, partitioning, vertical and horizontal sharding, client vs. proxy architectures, and alternative databases, providing actionable steps for high‑performance data handling.
This article presents a detailed performance benchmark of MariaDB and MySQL, covering single‑row and batch inserts, as well as various query scenarios with and without indexes, and concludes that MariaDB generally outperforms MySQL while using more memory.
This article explains the fundamentals of MySQL indexing, covering the purpose, optimization principles, various index types such as hash, B‑tree and B+‑tree, the structure of primary‑key and secondary indexes, index pages, and the concept of back‑table lookups, providing clear diagrams and examples.
This article explains MySQL's Unicode character set support, compares the deprecated utf8mb3 with the modern utf8mb4, and provides step‑by‑step SQL commands for safely converting existing databases to the newer charset.
This weekly community digest compiles recent MySQL technical articles, open‑source middleware progress for DBLE and DTLE, and community news links, offering developers concise insights into database engine best practices, bug fixes, new features, and upcoming plans.
This article explains MySQL's B‑Tree index fundamentals, compares MyISAM and InnoDB implementations, outlines how B+Tree structures work, and provides practical guidelines for designing effective indexes and tuning MySQL configuration to boost query performance.
This article examines MySQL InnoDB spin‑lock tuning by adjusting innodb_spin_wait_delay and innodb_sync_spin_loops, describing spin‑lock mechanics, detailing an experiment with performance_schema metrics, and concluding that modest spin settings are preferable to aggressive tuning.
This article analyzes common causes of slow MySQL queries such as index misuse, MDL locks, and large‑table bottlenecks, then presents practical solutions like proper indexing, sharding, read/write splitting, and evaluates when to complement MySQL with ElasticSearch or HBase for better performance.
This article explains how to resolve incompatibility between SyBase's default datetime export format and MySQL's datetime format by using the convert() function to reformat dates and str_replace() to adjust delimiters, then exporting the data via bcp for seamless import.
This article provides a complete tutorial for building a food‑delivery system with a mobile app, backend management, and API, detailing the technology stack, module structure, database setup, code snippets, and step‑by‑step instructions to launch both the admin and mobile front‑ends.
This article explains how MySQL 8.0's WITH clause can be combined with INSERT, UPDATE, DELETE, recursive common table expressions, multiple CTEs and derived tables to generate data, create date sequences, and simplify everyday SQL programming with practical code examples.
This article analyzes a puzzling MySQL update where the operation reports success yet the data remains unchanged, explains the WAL mechanism and two‑phase commit process, and provides a step‑by‑step binlog investigation script to uncover the root cause.
This article explains how to enable and configure MySQL's Federated storage engine to access remote tables without data synchronization, covering engine architecture, supported features, environment setup, server configuration, table creation, data manipulation, and binlog verification, along with practical code examples.
This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.
This article explains how to implement a user check‑in feature using two approaches—direct MySQL storage and Redis bitmap—detailing database schema, sign‑in logic, code examples, and a comparison of their advantages and disadvantages for backend development.
This article explains the MySQL password expiration mechanism, introduces the relevant columns in the mysql.user table, demonstrates how to set passwords to never expire, expire after a specific interval, or expire immediately, and shows how to configure the global default_password_lifetime variable.
This article explains why MySQL deadlocks occur in a test module, details table and row lock mechanisms, reproduces the deadlock scenario with two sessions, analyzes lock logs, and provides a solution by adding a composite index to prevent the conflict.
This article explains why using SELECT * in MySQL queries hurts performance, explores the underlying reasons such as extra data transfer, I/O overhead, and loss of covering index optimization, and provides practical guidance on index usage and best practices.
This article explains why deleting rows in MySQL does not decrease the physical table file size, describes the underlying InnoDB storage mechanisms, and shows how to reclaim space using OPTIMIZE TABLE, ALTER TABLE, and Online DDL options.
This article explains the hidden costs of using SELECT * in MySQL—such as extra data transfer, increased I/O, and loss of covering‑index optimization—while detailing how proper column selection and composite indexes can dramatically boost query efficiency.
This article investigates the meaning of exec_time in MySQL binary logs, demonstrates how row and statement binlog formats record execution times through systematic tests, analyzes the underlying principles, and shows how exec_time can be used to diagnose replication lag caused by CPU, disk, or network bottlenecks.
A developer encountered a duplicate‑key error caused by an unexpected primary‑key update that overflowed the signed INT limit, exposing a hidden MySQL auto‑increment bug that persisted across normal inserts until a later DDL operation triggered failures, a problem present in Percona 5.6/5.7 but fixed in MySQL 8.0.
This article explains MySQL full-text (inverted) indexes, their underlying auxiliary tables, buffer pool configuration, document ID handling, and the special transaction semantics required for inserting and querying indexed data, providing practical SQL examples and performance considerations.
This article provides a step‑by‑step guide to build a data pipeline that captures MySQL binlog, streams it through Canal into Kafka, processes it with Flink, and finally writes the results into TiDB, covering environment setup, component deployment, configuration, and verification.
This article presents an extensive collection of MySQL performance‑tuning techniques, covering index design, query rewriting, use of UNION and EXISTS, temporary tables, NOLOCK hints, join limits, backup strategies, storage engine choices, data‑type selection, and other practical tips to improve query efficiency and overall database reliability.
This article analyzes six MySQL pagination techniques, presents benchmark results showing how query time grows with offset, and offers practical index‑based optimizations—including covering indexes, subqueries, and composite index design—to make LIMIT pagination fast even on very large tables.
This article introduces the DBLE MySQL‑compatible distributed middleware, details its new pure read/write splitting feature in version 3.20.10.0, and provides a comprehensive table showing which SQL statements trigger implicit commits and their support status.
This article explains why learning MySQL is important, outlines the focus of a multi‑part series covering MySQL's underlying principles, installation on macOS, basic architecture components, InnoDB features, and what readers can gain such as SQL optimization and cluster building skills.
This article explains why locks are essential in MySQL, categorizes lock types, defines transactions and ACID properties, compares isolation levels, describes how InnoDB implements transactions and resolves deadlocks, and introduces MVCC as a concurrency control technique.
This article explains MySQL index fundamentals, covering primary and unique indexes, best practices for choosing indexed columns, handling functions and large fields, avoiding excessive or frequently updated indexes, and enumerates typical scenarios where indexes become ineffective.
This article explains MySQL indexing fundamentals, covering index types, underlying data structures such as B‑Tree and Hash, the differences between clustered and non‑clustered indexes, practical performance tips, and advanced features like adaptive hash indexes and index condition pushdown.
This article presents a complete food‑delivery system with mobile, admin, and API modules, detailing its Spring Boot and Vue.js technology stack, module structure, database setup, quick‑start commands, and how to run both the management console and mobile client for learning purposes.
This article examines a real‑world MySQL deadlock caused by the Index Merge optimizer, explains InnoDB's lock‑on‑index mechanism, walks through the deadlock logs, and presents four practical solutions including force‑index hints, disabling Index Merge, creating a composite index, and a two‑step update approach.
This article explains MySQL InnoDB’s four transaction isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—detailing the phenomena of dirty reads, non‑repeatable reads, and phantom reads, and provides step‑by‑step SQL demonstrations to illustrate each level’s behavior and practical implications.
This article explains MySQL indexing—especially InnoDB indexes—covering what indexes are, the various index types, underlying B‑Tree and B+Tree structures, clustered vs. secondary indexes, hash indexes, when to create indexes, and optimization techniques such as index condition push‑down.
This weekly community bulletin, presented by the Gold Medal Broadcaster Yongzheng, compiles selected MySQL and DBLE technical news, open‑source developments, bug fixes, community Q&A, and upcoming feature plans, providing readers with concise updates and links to detailed discussions.
This article examines the performance drawbacks of MySQL's LIMIT pagination on large tables, presents six practical query methods—including direct LIMIT, primary‑key indexing, index‑based ordering, prepared statements, covering indexes, and sub‑query/join techniques—provides extensive benchmark results, and offers concrete indexing recommendations to achieve fast, stable pagination even with millions of rows.
This article examines why database caching is needed, outlines the consistency challenges of using Redis as a MySQL cache, and compares four practical solutions—from simple TTL expiration to binlog subscription—helping developers select the most suitable approach for their latency and reliability requirements.
This article examines why caching is needed for MySQL, discusses consistency challenges, and compares four Redis‑based solutions—including expiration, synchronous updates, Kafka‑mediated async updates, and binlog replication—offering guidance on selecting the appropriate approach based on latency and reliability requirements.
This article examines the MHA open‑source MySQL high‑availability solution, detailing the master‑crash failover workflow, source‑code analysis, configuration checks, binlog handling, new‑master selection, and slave recovery, and provides a concise step‑by‑step checklist for practitioners.
A production MySQL server suffered CPU overload and half‑hour alerts because a high‑volume query skipped a crucial composite index field, leading to full‑table scans; the post explains the root cause, the temporary index fix, the left‑most index principle, code validation oversights, and lessons learned.
This article explains typical reasons why @Transactional may not work in Spring applications—such as non‑public methods, beans not managed by the container, internal method calls, non‑RuntimeException throws, swallowed exceptions, wrong propagation settings, and unsupported MySQL storage engines—and provides concrete code examples and fixes for each case.
This article explains how MySQL's sys.diagnostics stored procedure collects performance metrics, the information it reports, required permissions, command examples, configurable parameters, and how to generate and interpret diagnostic reports for effective database analysis.
This article analyzes the root causes of MySQL 'wait_timeout' related exceptions in a backend system using MHA read/write separation, explains how oversized connection pool timeout settings and MySQL wait_timeout lead to idle connections being closed, and provides practical Druid configuration adjustments and alternative solutions to eliminate the errors.
