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.
This weekly community newsletter compiles the latest technical updates, including MySQL and ProxySQL troubleshooting articles, Redis memory diagnostics, InnoDB performance insights, as well as progress reports and upcoming plans for the open‑source middleware projects DBLE and DTLE.
This article details a financial company's exploration of Apache Flink for real‑time processing, covering its unique business constraints, end‑to‑end data pipeline, single‑table and multi‑table use cases, implementation challenges, code snippets, data initialization, testing strategies, and lessons learned.
This article details how a Python Flask backend with a 36‑second settings page was diagnosed using Chrome Network and flame‑graph profiling, then optimized by redesigning UI interactions, eliminating per‑gid threading, and batching MySQL queries, ultimately reducing response time to 1.47 seconds.
This article demonstrates a step‑by‑step GDB experiment that isolates the InnoDB dirty‑page flushing thread, applies pressure to MySQL, observes metrics and log checkpoints, and confirms that slow dirty‑page flushing can block business operations.
The article explains why both equality and inequality comparisons with a NULL value in MySQL return no rows, illustrating common interview misconceptions and emphasizing the need to grasp NULL semantics for reliable database programming.
An in‑depth guide to MySQL master‑slave replication covering replication modes, deployment prerequisites, core mechanisms, common issues with solutions such as semi‑synchronous and parallel replication, configuration commands, monitoring techniques, and error handling to ensure high availability and performance.
This article explains how InnoDB’s 16 KB pages form B+‑tree indexes, calculates the number of rows a tree can hold at different heights, shows how to determine the tree’s height from the tablespace file, and why MySQL prefers B+ trees for indexing.
This article demystifies MySQL’s query execution by exploring its logical architecture, client‑server protocol, query cache, parsing, optimization, and execution engine, then offers practical indexing and performance‑tuning techniques—including B‑Tree fundamentals, covering indexes, and pagination tricks—to help developers write faster, more efficient SQL.
A production MySQL query that uses huge OFFSET values caused massive slowdown, and the article explains the root cause, demonstrates data‑generation scripts, benchmarks the problem, and presents three practical solutions—including index‑covering subqueries, remembering the last primary‑key, and offset throttling—to dramatically improve pagination performance.
This tutorial walks through building a scalable like feature using Spring Cloud, Redis caching, MySQL persistence, and Quartz scheduling, covering Redis installation, Spring Boot integration, data modeling, service implementation, and periodic synchronization of high‑frequency like operations to the database.
This tutorial demonstrates how to use MySQL Shell's three components—MySQL, MySQL X, and SHELL—to connect to a sample WORLD database, perform CRUD operations, execute transactions, and run advanced queries with code examples for each component.
This article examines why MySQL pagination with large OFFSET values becomes painfully slow, explains the underlying B+‑tree index mechanics, and presents two practical solutions—keyset pagination and index‑covering subqueries—to dramatically improve query performance.
This article explains the multiple reasons why SELECT * slows down MySQL queries—including extra parsing, unnecessary data transfer, increased I/O, and loss of covering index optimization—while also covering index fundamentals, composite indexes, and practical tips for writing efficient SELECT statements.
This article explains MySQL's ORDER BY execution process, comparing full-field sorting with rowid sorting, detailing how sort_buffer, temporary files, and index usage affect performance, and showing how composite and covering indexes can eliminate sorting altogether.
This article explains MySQL master‑slave replication, covering replication modes, use cases, required configurations, underlying mechanisms, common issues, and solutions such as semi‑synchronous and parallel replication, along with practical configuration examples and monitoring commands.
This guide explains how to use ProxySQL's stats_mysql_query_digest to detect rarely accessed MySQL tables, export table lists, script the retrieval of last access times, and safely rename or drop unused tables after verification, helping reduce storage waste and metadata overhead.
This article walks through a systematic MySQL SQL optimization workflow, explains EXPLAIN output types and Extra flags, demonstrates profiling and trace usage, and presents ten real‑world cases—from left‑most index matching to large pagination—offering practical measures and reference resources.
This article provides a thorough overview of MySQL fundamentals—including schema design, storage engines, transaction mechanisms, logging, indexing, and performance tuning—along with practical SQL examples, code snippets, and optimization strategies for both hardware and database configuration.
This article explains why defining columns as NOT NULL in MySQL improves query optimization, reduces index complexity and storage overhead, and avoids unexpected behavior in aggregates and calculations, while also covering default values, row formats, and practical testing results.
This article analyzes how MySQL slave crashes can cause inconsistencies between master info and relay log positions during sync_master_info and sync_relay_log_info processes, presents case studies of duplicated events, explains the impact of different storage repositories (FILE vs TABLE), and recommends configuration settings to achieve server‑crash‑safe replication.
This article explains the storage units of InnoDB, calculates how many rows a B+‑tree can hold based on page size, record size and pointer size, demonstrates how to determine the tree height from the tablespace file, and summarizes the impact on query I/O.
This article demonstrates how to build a MySQL master‑slave replication cluster on Kubernetes by leveraging StatefulSet for stateful pod management, local persistent volumes for storage, and a series of YAML manifests and commands to configure storage classes, PVs, ConfigMaps, Secrets, Services, and the StatefulSet itself, while also showing how to verify replication and scale the replica set.
This article investigates why MySQL encounters out‑of‑memory errors during sysbench stress testing with high concurrency, examines system memory metrics, explains Linux vm.overcommit_memory settings, and provides a practical solution to prevent OOM by adjusting kernel parameters.
This article examines how slave crashes in MySQL replication can cause inconsistencies between master info and relay log positions, leading to duplicate events and errors such as 1062 and 1032, analyzes underlying mechanisms, presents case studies, and proposes configuration settings to achieve server‑crash‑safe replication.
This article explains the concept of MySQL index cardinality, how it influences index selectivity, factors that affect cardinality values, methods to view them, and demonstrates with practical examples how different data distributions and query patterns impact the optimizer's execution plans.
This article presents nine practical MyBatis techniques—including pagination, preset column selection, one‑to‑many and one‑to‑one associations, foreach‑in queries, dynamic where clauses, choose/otherwise logic, hidden _parameter usage, and dynamic set updates—illustrated with SpringBoot and MySQL code examples.
This article outlines ten practical techniques—from avoiding chat tools for SQL transmission to using soft deletes, backup tables, and proper field management—to help developers, testers, and DBAs safeguard production databases against accidental data loss and costly errors.
This article explores why MySQL queries with large LIMIT‑OFFSET pagination become painfully slow, explains the underlying B+‑tree index mechanics and MySQL's logical operator layers, and presents two practical solutions—key‑based pagination and index‑covering queries—to dramatically improve performance.
The DB-Engines April 2021 popularity ranking shows the top ten databases unchanged, major score drops for Oracle, MySQL and SQL Server, steady gains for PostgreSQL and MongoDB, and rapid climbs for Snowflake and ClickHouse, with methodology based on five key popularity indicators.
This article explains why Elasticsearch handles complex conditional queries more efficiently than MySQL by using inverted indexes, term dictionaries, skip‑list and roaring bitmap structures, while also discussing the trade‑offs such as slower write performance.
This article explains MySQL's three lock levels, the algorithms behind next‑key, gap, and record locks, illustrates common deadlock scenarios with real SQL examples, and outlines InnoDB's lock‑prevention strategies to help developers avoid and resolve deadlocks.
This article explains why MySQL 5.6 may return duplicate rows on the second page when using ORDER BY with LIMIT, analyzes the optimizer’s priority‑queue behavior, and provides practical solutions such as indexing, proper pagination understanding, and handling of sorting edge cases.
The DTLE 3.21.03.0 release introduces new MySQL data‑transfer configurations, performance improvements for Kafka full copies, and several bug fixes, while providing repository, documentation, and download instructions for users seeking a reliable open‑source replication component.
This article outlines core database architecture principles, compares four common deployment schemes (master‑slave, dual‑master, master‑slave with read/write separation, and hybrid), and presents practical consistency solutions for both primary‑replica and DB‑cache mismatches, followed by personal evolution insights.
This article examines core MySQL architecture principles—high availability, performance, consistency, and scalability—then compares four common deployment patterns, analyzes their trade‑offs, and presents practical consistency solutions for both master‑slave replication and cache synchronization, ending with personal insights on evolution and sharding.
This article explains why MySQL may still use a composite index as a covering or range index despite the query not following the leftmost‑prefix rule, by analyzing optimizer trace output and demonstrating the behavior with concrete experiments.
This article explains MySQL index types—including ordinary, unique, primary, composite, and full‑text indexes—their creation syntax, optimal usage scenarios, situations where indexes should be avoided, cases that render indexes ineffective, and how to interpret EXPLAIN output types for query performance analysis.
Discover three effective strategies for mastering a technology, illustrated with MySQL replication, and learn how the new book 'Deep Understanding of MySQL Master‑Slave Principles' combines code analysis, comparative study, and expert insight to guide readers through GTID, binary‑log events, and high‑availability architectures.
The article explains why some MySQL 8.0.18 production environments enforce a password policy despite lacking the validate_password plugin, by examining configuration files, server parameters, and the role of the validate_password component introduced in MySQL 8.0.
This article explains how to use Alibaba's Canal to capture MySQL binlog changes, forward them to Kafka, and process the incremental data in real time with Spark Streaming, including installation, configuration, client development, Spark code, testing, and troubleshooting dependency conflicts.
This article demonstrates how a MySQL DBA can use the open‑source analysis_binlog tool to efficiently parse large binlog files, count DML operations, convert binlog to SQL, and perform parallel analysis after setting up a CentOS test environment and running sysbench stress tests.
This article explains the fundamentals of MySQL indexes, the B+Tree data structure, differences between MyISAM and InnoDB implementations, practical tips for creating effective composite indexes, and essential server configuration and SQL tuning techniques to dramatically improve query performance.
This article outlines ten practical MySQL development guidelines—from always using InnoDB and simple data types to avoiding SELECT *, ENUM, and low‑cardinality indexes, while emphasizing proper naming, charset choices, NOT NULL constraints, and continuous SQL monitoring with Percona Toolkit.
When running php artisan migrate in a Laravel project on Windows with Docker, the command cannot reach the MySQL container, producing a SQLSTATE[HY000] [2002] error, and the issue is resolved by wrapping the artisan command in a custom shell script that executes inside the container.
This article explains why combining LIMIT with ORDER BY in MySQL can cause duplicate rows on subsequent pages, analyzes the priority‑queue optimization that makes sorting unstable, and offers practical solutions such as indexing, stable ORDER BY clauses, and a deeper understanding of pagination behavior.
This article outlines ten practical MySQL development guidelines—from using InnoDB tables and simple data types to avoiding SELECT * and unnecessary ENUMs—helping DBAs and developers collaborate more efficiently and write cleaner, faster SQL.
This article explains how to build a distributed read‑write lock using MySQL tables and transactions, covering the business scenario, lock table design, detailed SQL procedures for write and read operations, logging, dead‑lock handling, and a cleanup job.
This guide explains how to verify that MySQL binlog is enabled, locate binlog files, use the mysqlbinlog utility to extract change logs for specific time ranges or tables, and apply the extracted SQL to recover or roll back erroneous data modifications.
This article introduces ClickHouse, explains its column‑oriented architecture versus row‑oriented databases, details a MySQL‑to‑ClickHouse migration, shows a performance benchmark that reduces query time from minutes to seconds, and shares practical deployment tips and common pitfalls.
This guide explains how to identify MySQL max_connections issues, check the current limit, and increase it via configuration files, runtime commands, source code changes, or mysqld_safe adjustments, covering version‑specific defaults and maximum caps.
This article details the step‑by‑step creation of a Python‑based API automation testing framework, covering project structure, configuration files, a reusable logging module, HTTP request handling with requests, common utilities for Excel and XML, MySQL database access, email reporting, and the test runner setup.
This article explains why multiple InnoDB buffer pool instances are recommended, describes a simple experiment that shows how a table's data pages are distributed across two buffer pool instances in a 64‑page alternating pattern, and discusses the performance reasons behind this behavior.
This article explains the concept of locks in computing and databases, compares InnoDB and MyISAM storage engines, details transaction isolation phenomena such as dirty reads, non‑repeatable reads and phantom reads, and explores InnoDB lock types, lock algorithms, intention locks, auto‑increment lock modes, and MVCC mechanisms.
This article explains how the creator red‑packet feature was engineered to guarantee idempotent, fault‑tolerant, and high‑throughput red‑packet claims by using multi‑level caching, empty placeholders, binlog‑driven synchronization, active cache invalidation, ordered Kafka consumption, and fallback strategies to resolve cache‑DB consistency issues.
This article explains how to use MySQL's LOAD DATA command to import CSV and fixed‑length text files, covering basic parameters, sample data and table structures, four practical scenarios, and best‑practice tips for handling mismatched schemas and large files.
This article presents a comprehensive set of mandatory and recommended MySQL database standards covering table creation, SQL writing, and index design, explaining the rationale behind each rule to improve performance, maintainability, and consistency across projects.
This article provides a curated collection of over 250 interview question links covering Java fundamentals, Spring framework, databases, Redis, messaging, concurrency, and system design, organized by issue numbers to help developers prepare for technical interviews.
This article explains the difference between MySQL multi-value indexes and composite indexes, demonstrates how to create and query multi-value indexes using JSON columns, and shows performance benefits and practical scenarios when dealing with tables that have a large number of columns.
This article details how the government talent housing system adopted the open‑source Seata distributed‑transaction framework, identified severe performance bottlenecks during large‑scale batch operations, and applied a series of optimizations—including query‑lock rewriting, rollback‑info compression, JDBC batch tuning, and multithreaded processing—to reduce a 30000‑record transaction from 280 seconds to under 7 seconds, achieving production‑grade reliability.
This article evaluates the insertion speed and index impact of three MySQL primary‑key strategies—auto‑increment, UUID, and Snowflake‑generated long IDs—by building identical tables, running a Spring Boot/JdbcTemplate benchmark, and analyzing the resulting performance and structural differences.
This article shares an interview experience that compares MongoDB and MySQL, explains the advantages and disadvantages of document‑oriented and relational databases, introduces other NoSQL solutions such as Elasticsearch, and offers guidance on choosing the right storage technology based on workload characteristics.
This article explains why a MySQL join query may ignore an index because of implicit character‑set conversion, demonstrates how to diagnose the issue with EXPLAIN and schema queries, and provides a fix by converting the column to UTF8 in the SQL statement.
This article explains the principles, architecture models, and concrete implementations of high‑availability for MySQL and PostgreSQL, covering data‑sync modules, HA management, unified access, and cloud‑native considerations with detailed examples and best‑practice recommendations.
This article explains the purpose, usage, and return codes of the MHA masterha_secondary_check Perl script, demonstrates how to simulate network failures with iptables, and presents a series of tests that verify the script’s ability to detect master reachability and handle various error conditions.
This guide walks through setting up a Spring Boot 2.2.11 project with Activiti 7.1, configuring Maven dependencies, explaining Activiti table prefixes, defining core services, creating a BPMN leave request process, configuring security and datasource, and exposing REST APIs for deployment, execution, and task handling.
This article explains why growing MySQL workloads require moving from a single instance to a clustered architecture, details the mechanics of master‑slave replication, asynchronous, semi‑synchronous and group replication, and evaluates various high‑availability solutions and read‑write splitting techniques.
This article compiles a comprehensive set of MySQL interview questions covering index structures, B‑tree vs. hash trade‑offs, primary‑key and clustered‑index design, ACID properties, isolation levels, lock types, storage‑engine choices, data‑type considerations, binlog formats, pagination strategies, sharding, stored procedures, normalization, and MyBatis parameter syntax.
This tutorial shows how to create a multithreaded Python web scraper that fetches news from Sina and NetEase, stores the results in MySQL, generates a live word‑cloud, and serves the data through a simple Flask web application with an HTML front‑end.
This article walks through a systematic SQL optimization workflow—identifying slow queries, interpreting EXPLAIN output, using SHOW PROFILE and TRACE, applying concrete tuning measures, and dissecting ten representative MySQL cases ranging from index ordering to large‑page handling and complex query pitfalls.
This comprehensive guide walks through MySQL's advanced concepts—including lock types, transaction control, SQL security, mode settings, regular expressions, common tricks, case sensitivity, foreign‑key support, and a wide range of built‑in functions—providing clear examples and practical commands for each topic.
This article explains MySQL's lock levels (table, page, row), the next‑key, gap, and record lock algorithms, illustrates common deadlock scenarios with detailed session examples, and discusses InnoDB's deadlock prevention strategies and practical solutions for avoiding lock conflicts.
The article experimentally evaluates three ways of creating replication users—only on the master, separately on master and slave with CREATE statements, and separately with GRANT statements—to show how each method affects MySQL 5.7 master‑slave replication, error handling, and compatibility with GTID and MySQL 8.0.
The article explains two technical methods for keeping MySQL and Redis in sync—using MySQL triggers with a custom UDF to write directly to Redis, and parsing MySQL binlog streams (or using Alibaba's Canal) to propagate changes, while discussing their suitable scenarios, challenges, and implementation details.
This article explains two practical methods for keeping MySQL and Redis cache in sync—using MySQL triggers with UDF functions or parsing binlog events—and introduces the open‑source Canal project for incremental log parsing, while also discussing the pitfalls of reverse synchronization.
This tutorial walks through MySQL's transaction control, lock statements, autocommit settings, manual commit and rollback, savepoints, differences between transactional and non‑transactional tables, SQL mode configurations, common SQL tricks, regular expressions, and a comprehensive catalog of built‑in functions, all illustrated with step‑by‑step examples and code snippets.
This article explains how MySQL unique indexes influence auto‑increment behavior, compares unique constraints with primary keys, and demonstrates three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to avoid duplicate‑key errors, including their effects on auto‑increment values and potential deadlocks.
This article analyzes how adjusting the MHA ping_interval and enabling secondary_check_script can increase MySQL high‑availability cluster tolerance to network packet loss and corruption, presenting test setups, parameter configurations, log observations, and conclusions on their impact on failover behavior.
This article describes how to improve the performance of a massive MySQL 5.6 user‑log table by redesigning schema, applying proper indexes, using partitioning, considering table sharding, and evaluating upgrade paths to compatible cloud or big‑data databases, with concrete SQL examples and cost analysis.
This article explains how MySQL UNIQUE indexes affect data insertion and auto‑increment behavior, compares them with primary keys, and presents three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to prevent duplicate‑key failures, including deadlock considerations.
A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
This article explains how to identify which MySQL tables in an Alibaba Cloud RDS environment should be synchronized across clouds and which can be excluded, covering key concepts, design and operational practices, a real‑world failure case, and recommended mitigation and improvement steps for application‑level disaster recovery.
