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.
This article examines why full-table scans become slow on large MySQL tables, demonstrates various pagination techniques—including simple LIMIT, sub‑query, ID‑range, and IN‑clause methods—provides benchmark results for different record counts and offsets, and offers practical tips to dramatically speed up paginated queries.
This article examines the pain points of MySQL DDL operations for large tables, analyzes the limitations of online DDL, compares tools like gh‑ost and pt‑osc, and outlines a comprehensive workflow and implementation details for an automated online DDL work‑order system.
This article explains how Canal simulates a MySQL slave to capture binary logs, outlines the parsing workflow, compares Maxwell’s JSON output approach, introduces LinkedIn’s low‑latency Databus, and reviews Alibaba Cloud’s Data Transmission Service (DTS) as a managed change‑data‑capture solution for MySQL.
This article explains MySQL’s internal architecture—including server and storage engine layers—and walks through each stage of query processing from connection and authentication, through query cache, parsing, optimization, and execution, illustrated with a concrete SELECT example.
This article explains how MySQL maintains primary and composite (non‑primary) indexes using B+ trees, outlines the essential equality‑matching, left‑most‑prefix, and range‑scan principles, and shows how to combine them with ORDER BY, LIMIT, and GROUP BY for optimal query performance.
This article explains how MySQL processes a GROUP BY query on a user view table, detailing the creation and use of memory and disk temporary tables, the MEM_ROOT allocation mechanism, the execution stages revealed by EXPLAIN, and how adding a composite index can eliminate temporary tables and filesort for better performance.
This article explains how many rows a single InnoDB B+‑tree can hold by describing InnoDB’s 16 KB page size, the storage of data and index pages, calculating rows per leaf page, pointer capacity, tree height for typical workloads, and how to determine the height from the page‑level metadata.
This article explains MySQL's table‑lookup (回表) process, the difference between primary‑key and secondary indexes, and how the index‑pushdown feature introduced in MySQL 5.6 reduces unnecessary lookups by filtering rows directly in the index.
The article explains that mysqlslap appears to produce smoother latency results than sysbench because mysqlslap reports metrics per test round rather than per individual SQL statement, leading to mis‑interpretation of Max/Avg/Min values, while sysbench can reveal per‑statement latency variations.
This article explains MySQL's logical architecture, query execution flow, client‑server protocol, query cache behavior, parsing and optimization stages, cost‑based optimizer, execution engine, and provides practical performance‑tuning advice such as schema design, data‑type choices, index creation, B‑Tree fundamentals, covering indexes, and handling COUNT, JOIN, LIMIT, and UNION queries.
The article explains why MySQL raises a GTID consistency error when a single transaction updates both InnoDB and MyISAM tables, demonstrates how to reproduce the issue, and evaluates three mitigation strategies: separating the updates, converting MyISAM to InnoDB, or disabling ENFORCE_GTID_CONSISTENCY.
This article details a real‑world test of loading over 200 million rows into MySQL using XeLabs TokuDB, showing configuration tweaks, bulk‑loader commands, performance metrics (≈570 k rows per second), file‑size reductions, and comparisons with InnoDB under a 8‑core, 8 GB RAM, 500 GB SSD environment.
This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.
This article explains the fundamentals of MySQL table‑lookup (回表) operations, compares primary‑key and secondary‑index structures, illustrates how low‑version MySQL performs lookups for every matching row, and shows how index pushdown in MySQL 5.6+ reduces unnecessary lookups for better performance.
The article explains why massive MySQL tables need periodic cleanup, compares risky drop/truncate approaches, and provides a step‑by‑step guide to safely delete historical data in batches using pt‑archiver with proper parameters, session handling, and post‑deletion maintenance.
This article introduces MySQL Shell’s import_table utility, demonstrates its core features and configuration options such as parallel import, speed throttling, and custom chunk sizes, provides side‑by‑side command examples with LOAD DATA, and shows that import_table can achieve more than double the import speed in practical tests.
This article explains MySQL's query cache settings, clustered and non‑clustered index design, optimizer cost calculations, how to interpret EXPLAIN output, and the roles of redo/undo logs and transaction isolation levels, providing practical examples and performance tips.
This article explains how to break the barrier between heterogeneous databases by synchronizing MySQL to ClickHouse using QingCloud MySQL Plus and the MaterializeMySQL engine, covering MySQL replication history, architecture details, HTAP scenarios, and providing practical code examples for setup and operation.
This article explains why MySQL subqueries often cause performance bottlenecks, describes the underlying execution mechanism, and presents practical optimization strategies—including semi‑join, materialization, and EXISTS—plus a real‑world case study showing how to rewrite a slow query into a fast join.
This guide explains why MySQL reports "too many connections", shows how to check the current max_connections setting, compares default limits across MySQL versions, and provides four practical methods—including editing my.cnf, using SQL commands, modifying source code, and tweaking mysqld_safe—to increase the connection limit safely.
Learn comprehensive MySQL security strategies covering storage RAID configurations, network whitelisting, OS hardening, account management, privilege restrictions, audit logging, regular backups, data encryption, and disaster recovery to protect enterprise data against breaches and ensure stable, high‑performance operations.
This article explains the XA protocol, its two‑phase and three‑phase commit mechanisms, the limitations of traditional two‑phase commit, and how Seata implements an optimized XA mode on MySQL with detailed code examples and architectural diagrams.
This article demonstrates how to perform remote physical backups of MySQL databases using the mysqlbackup and Percona XtraBackup tools, covering test objectives, environment setup, step‑by‑step streaming commands, restoration procedures, replication configuration, and a comparison of the two approaches.
This article explains MySQL's lock granularity (table, row, page), the different lock modes such as next‑key, gap and record locks, illustrates common deadlock scenarios with detailed SQL examples, and discusses InnoDB's deadlock prevention strategies and best‑practice solutions.
This article outlines a systematic approach to identifying and fixing inefficient MySQL queries, covering slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and practical case studies that demonstrate index tuning, query rewriting, and handling large‑scale pagination and complex conditions.
This article compiles 50 practical SQL performance optimization techniques—from indexing strategies and query rewriting to avoiding full table scans, using temporary tables, proper data types, and efficient backup methods—helping developers write faster, more scalable database queries.
This article explains MySQL's table, row, and page lock levels, describes next‑key, gap, and record locks, illustrates common deadlock scenarios with detailed SQL examples, and outlines InnoDB's lock acquisition and deadlock‑prevention strategies to help developers avoid concurrency issues.
This article documents a practical test of MySQL Enterprise Backup 4.0, examining whether the tool can perform remote physical backups using the --host option, describing the test environment, commands, observed behaviours for both local and remote scenarios, and the conclusions drawn from the results.
This article explains the differences between DROP, DELETE, and TRUNCATE, recommends appropriate MySQL data types for monetary values, compares MyISAM and InnoDB storage engines, outlines transaction ACID properties, describes views, stored procedures versus triggers, indexes, UNION vs UNION ALL, and lists the main SQL language categories.
This article explains NUMA architecture, how its memory allocation policies can cause swap‑related performance issues for MySQL, provides step‑by‑step methods to disable NUMA at BIOS, kernel or MySQL levels, and discusses the innodb_numa_interleave parameter and best‑practice recommendations.
The article explains how MySQL indexes rely on ordered data structures—binary trees, red‑black trees, hash tables, and especially B‑Trees—to accelerate queries, compares their performance characteristics, illustrates how tree height affects I/O operations, and shows why B‑Tree is preferred for range searches.
This article explains MySQL's four transaction isolation levels, focusing on the default Repeatable Read, how MVCC enables snapshot reads to avoid phantom reads, the differences between snapshot and current reads, and practical techniques such as serializable isolation and next‑key locking to fully prevent phantom anomalies.
This tutorial walks through creating a MySQL master‑slave replication cluster on Kubernetes using a StatefulSet, local persistent volumes, custom StorageClass, ConfigMap, Secret, and Services, showing how to initialize, scale, and verify data synchronization across pods.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree leaf page can hold, derives the total record capacity for trees of different heights, and shows how to determine the actual B+‑tree height in a MySQL table using page metadata.
This comprehensive guide explores MySQL’s origins, key characteristics, logical architecture, storage engines, transaction handling, concurrency control, and detailed indexing strategies, offering practical insights and best‑practice recommendations for developers and database administrators seeking to master MySQL performance and reliability.
Index Condition Pushdown (ICP) in MySQL 5.6 optimizes secondary index scans by pushing filter conditions to the storage engine, reducing row lookups and data transfer, with examples showing performance gains, usage guidelines, EXPLAIN checks, and limitations.
This article explains the core principles of MySQL indexes, covering their essence, various types such as hash, binary, B‑tree and B+‑tree, the structure of primary key directories and index pages, clustered versus non‑clustered indexes, and the back‑table lookup process.
This guide explains how to identify MySQL max‑connection errors, check the current limit, and increase it using configuration files, global variables, source‑code changes, or mysqld_safe adjustments, with detailed commands and code examples for various MySQL versions.
This article provides a step‑by‑step tutorial for installing and configuring a Spring Cloud microservice platform on a Mac M1, covering Nacos setup, MySQL installation, microservice startup, frontend portal preparation, and admin backend deployment with troubleshooting tips.
This guide walks through a step‑by‑step experiment that shows how to recover MySQL files from a Linux virtual machine that cannot boot, by mounting its disk on a new VM, activating LVM volumes, copying configuration and data files, and restarting the database.
This article demonstrates how to recover MySQL data from a crashed Linux virtual machine by mounting its disk on a new VM, activating LVM logical volumes, copying configuration and data files, and restarting MySQL, illustrating a practical data rescue workflow.
This article explains why MySQL uses B+‑tree indexes, describes the left‑most prefix rule for composite indexes, offers practical index‑design and optimization tips, outlines MyBatis first‑ and second‑level caching, details master‑slave replication, and introduces common sharding strategies and their implementation considerations.
This comprehensive guide explains how to migrate Oracle databases to MySQL by detailing type differences, migration steps, performance tuning, and validation techniques, while highlighting common pitfalls such as data‑type mismatches, character‑set issues, LOB handling, and transaction isolation nuances.
This article explains the concept, advantages, and inner workings of master‑slave database replication, outlines the key threads and step‑by‑step process, and provides common interview questions with concise answers for candidates preparing for database engineering roles.
