This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.
This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.
This article enumerates twelve typical reasons why MySQL queries become slow—ranging from missing or ineffective indexes, type conversions, OR/LIKE patterns, composite‑index misuse, built‑in functions, large tables, deep pagination, join overload, oversized IN clauses, dirty‑page flushing, order‑by filesort, lock contention, delete‑in pitfalls, and group‑by temp tables—and provides concrete SQL examples, execution‑plan explanations, and optimization techniques for each case.
This article explains how Meituan tackles MySQL slow‑query problems by leveraging the database cost‑based optimizer to generate index suggestions, evaluate their quality, and operate a full‑stack governance workflow that includes data collection, statistical modeling, validation, and continuous improvement.
This article explains that MySQL imposes no fixed row limit, discusses the common 5‑million‑row myth and Alibaba's 2 GB recommendation, shows a Java insertion test that slows after millions of rows, and highlights how InnoDB buffer size and memory constraints affect table performance.
This article outlines a systematic approach for backend engineers to identify root causes of database slowness and presents eight concrete optimization techniques—ranging from data reduction and space‑for‑performance trade‑offs to appropriate storage system selection—while explaining when each method is most effective and what trade‑offs to consider.
The article examines how slow‑query‑induced avalanches arise in MySQL, explains why naïvely killing slow queries is fraught with accuracy, automation and responsibility issues, and proposes a signature‑based timeout mechanism that lets applications safely abort problematic queries.
This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
The article analyzes why running MySQL in Docker containers can cause data loss, performance bottlenecks, state management problems, and insufficient resource isolation, while also offering scenarios and strategies where containerizing MySQL might still be feasible.
This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.
This comprehensive guide explains MySQL's architecture, storage engines, and the critical role of indexes, walks through the SQL parsing process, details how to read and interpret EXPLAIN output, and provides practical optimization examples for single‑table and multi‑table queries.
The article analyses why indiscriminately killing slow MySQL queries is risky, enumerates the drawbacks of common kill‑based approaches, and proposes more reliable designs such as registration, signature‑based killing, and source‑code modifications to safely mitigate query‑induced database avalanches.
This article explains MySQL master‑slave read/write separation, the binlog and relay log mechanisms, master‑slave delay, and how different transaction isolation levels affect whether the master or slave may return stale data, illustrated with practical examples and SQL snippets.
This article explains why the IN and NOT IN operators often lead to poor performance and incorrect results in SQL queries, demonstrates the issues with concrete examples involving large tables and NULL values, and recommends using EXISTS, NOT EXISTS or JOIN as more reliable alternatives.
This article explains how Apache Doris adopts CPU vectorization techniques—such as SIMD, columnar storage, and cache‑friendly designs—to boost query performance, detailing its current vectorized engine architecture, recent benchmarks, ongoing work on JOIN, storage, import, and future enhancements.
This article presents a detailed benchmark comparing MySQL 8.0.19 and MariaDB 10.4.12 on a Windows 10 i7 machine, covering single‑row and batch inserts, various SELECT queries with and without indexes, and analyzes the trade‑off between speed and memory usage.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
By redesigning Hologres tables with column orientation, shard‑controlled Table Groups, distribution and clustering keys, adding bitmap indexes, refreshing statistics, caching external data, and tuning optimizer join order and resource scaling, Alibaba’s Mom advertising inventory system cut query latency by up to 35 % and memory use by 98 %, achieving a 5‑10× performance boost.
This article extends the previous discussion on MySQL time‑based partition implementation by detailing how to write efficient SQL queries, demonstrating partition pruning with supported functions, and offering practical optimization techniques for tables that use unsupported partition functions.
This article explains MySQL master‑slave replication fundamentals, why it’s used, the replication workflow, causes of lag, and presents multiple practical solutions—including system tuning, partitioning, caching, multi‑threaded relay log replay, and read‑from‑master strategies—to improve performance and reliability.
The article explains that using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, especially with large tables or NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
The article examines why containerizing MySQL with Docker is problematic, highlighting risks to data safety, performance bottlenecks, state management challenges, and insufficient resource isolation, while also noting scenarios where containerized databases may be viable.
The article explains why large‑offset LIMIT queries on a MySQL table with millions of rows are slow, demonstrates how rewriting the query to use a subquery that first selects primary‑key IDs dramatically reduces execution time, and validates the improvement by measuring buffer‑pool page usage.
This article explains TiDB's SQL layer architecture, details how SQL requests are processed and optimized, demonstrates the use of EXPLAIN and EXPLAIN ANALYZE, and presents two real‑world case studies that illustrate common performance pitfalls and practical optimization techniques.
This article explains how to inspect and clear Linux memory buffers, details the information shown by the free command, and provides a comprehensive overview of SQL join types, their performance impact, and optimization techniques such as indexing, join buffer tuning, and algorithm choices.
This article presents JD Daojia's data platform built on StarRocks, detailing business background, challenges such as pagination inconsistency, case‑when performance, and array‑type issues, and provides concrete SQL solutions, best‑practice tips, and future optimization directions.
This article explains how Redis, originally a single‑threaded in‑memory cache, introduced Lazy Free and multi‑threaded I/O to eliminate blocking during large key deletions, improve QPS, and lay the groundwork for future multithreading strategies.
This article explains why MyBatis batch inserts using a foreach loop can become extremely slow when inserting thousands of rows, analyzes the underlying cost of parsing large prepared statements, and presents practical solutions such as using ExecutorType.BATCH, limiting foreach batch size, and employing JDBC batch execution to achieve sub‑second performance.
This article examines workload analysis and query performance differences between PostgreSQL and MySQL, focusing on JSON handling, indexing strategies, concurrency control, benchmark results, and replication features, while offering configuration tips to improve each database’s speed and efficiency.
This article explains why MyBatis foreach batch inserts can become extremely slow when inserting thousands of rows, analyzes the underlying SQL and PreparedStatement overhead, and demonstrates how using ExecutorType.BATCH together with a limited batch size (20‑50 rows per statement) dramatically improves insertion speed.
This article explains why SQL queries become slow in MySQL, describes the server architecture, details how to use the EXPLAIN statement to interpret execution plans, and outlines how to read and optimize key fields such as type, key, and extra for better performance.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
The first StarRocks Hacker Meetup recapped essential techniques for building high‑performance databases, covering CPU vectorization basics, key optimization strategies, and a preview of the upcoming session on real‑time updates and storage engine redesign, all presented by community experts.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
The article investigates how changing the operating system clock while MySQL is running influences functions such as SLEEP, metadata lock (MDL) wait calculations, and other timing‑related mechanisms, demonstrating that forward or backward adjustments can cause premature exits or inaccurate wait measurements.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article examines why containerizing MySQL often leads to data‑security, performance, state‑management, and resource‑isolation problems, while also outlining scenarios where MySQL can safely run in Docker with proper strategies and examples.
This article presents a comprehensive MySQL slow‑query risk‑scoring model that quantifies each slow query's impact using metrics such as query count, execution time, lock wait, bytes sent and rows examined, assigns weighted scores up to 100, and demonstrates how the model enables proactive, business‑aligned remediation.
This article explains what MySQL slow queries are, why they cause system failures, proposes multi‑dimensional metrics to assess their severity, outlines concrete guidelines and change standards, and shares real‑world optimization cases and daily operational practices for eliminating slow SQL.
The article explains why slow SQL queries drain I/O and CPU resources, outlines prioritization rules, details MySQL execution steps, identifies performance‑affecting factors, and provides practical solutions and best‑practice guidelines for diagnosing and optimizing slow MySQL queries in production environments.
This article explains the fundamentals of MySQL indexing, including the structure of InnoDB pages, the evolution from simple page reads to B+ tree indexes, and how clustered and non‑clustered indexes affect query performance and I/O efficiency.
This article presents a comprehensive approach to quantifying MySQL slow‑query risk by defining scoring items, establishing boundary values, applying various scoring functions, and integrating business‑level weighting, ultimately enabling proactive identification and remediation of high‑impact slow queries.
The article explains Tencent Cloud's enterprise‑level MySQL dynamic thread‑pool capability, describing the one‑thread‑per‑connection and thread‑pool modes, the new thread_handling_switch_mode parameter with its four options, status‑monitoring commands, and added columns for process‑list visibility, enabling seamless mode switching without server restarts.
This tutorial demonstrates how to reproduce a MySQL memory‑spike bug, monitor the process with Linux perf to capture mmap system calls, and analyze the resulting perf.out to identify which SQL statements trigger large memory allocations, while also discussing the method’s limitations.
This article presents a comprehensive guide to improving MySQL query performance by addressing common issues such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and demonstrates how using WITH can simplify complex statements.
This article walks through common interview questions on Linux memory inspection and cache clearing, explains the fields shown by the free command, and then delves into SQL join types, performance bottlenecks, buffer settings, and practical optimization techniques for MySQL databases.
This article walks through a real‑world case where a simple INSERT in TiDB runs for tens of seconds, explains how transaction modes and write conflicts trigger SQL retries, and outlines how to diagnose and configure retry behavior to avoid slow writes.
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow, and the inner workings of the optimizer, cache, and storage engine, then offers practical schema, index, and specific‑query tuning techniques to boost database performance.
This article explains what constitutes a MySQL slow query, why they cause failures, defines quantitative metrics such as micro‑average and macro‑average to assess severity, outlines target goals, presents concrete optimization examples, and shares operational practices for ongoing slow‑SQL governance.
This article explains how to identify and resolve Redis slow queries by using the slow query log, understanding command complexity, replacing inefficient commands with more efficient alternatives, and configuring slowlog parameters for optimal performance.
This article examines common causes of slow MySQL queries, explains index mechanics and failures, then compares ElasticSearch’s fast tokenized search and HBase’s column‑oriented storage, offering practical guidance on when and how to use each technology.
This article describes how the Beaver search engine’s many performance‑related configuration parameters can be automatically tuned using machine‑learning techniques from OtterTune and AutoTiKV, detailing the background research, Gaussian Process regression model, Bayesian optimization process, implementation steps, test results, and future improvements.
This article explains MySQL’s logical architecture, query processing steps, and the principles behind query optimization, covering topics such as client‑server protocol, query cache, parsing, cost‑based optimizer, execution engine, and practical index design strategies to improve performance.
This article explains what MySQL temporary tables and temporary files are, how they are created by users or the optimizer, how to view and clean them up, relevant configuration parameters, monitoring methods, and special cases that may require large storage.
The article explains how PolarDB's Polar Index redesigns InnoDB B‑Tree structural modification (SMO) by splitting the operation into two phases, eliminating the global index latch, reducing lock granularity, and achieving up to 11× performance gains in high‑concurrency workloads such as TPCC.
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 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 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 explains how PostgreSQL's prepared statements cache execution plans, describing the default behavior of generating custom plans for the first five executions before switching to a generic plan, and shows how PostgreSQL 14 adds generic_plans and custom_plans columns to the pg_prepared_statements view for monitoring.
OceanBase achieved a world‑record TPC‑H performance of 15.26 million QphH, illustrating its HTAP capabilities and detailing the technical evolution, challenges, and future direction of hybrid transactional/analytical processing in modern distributed databases.
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 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 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 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.
The article explores why MySQL pagination with large offsets is slow, explains index structures such as clustered and secondary indexes, describes logical query operators, and presents two practical optimization strategies to improve query performance.
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.
The article explains that inserting ‘WHERE 1=1’ in dynamically built SQL statements prevents syntax errors when appending additional conditions, illustrates its use with Java string concatenation examples, discusses performance considerations, and shows how to copy tables or duplicate structures using this technique.
This article explains how Redis, originally a single‑threaded in‑memory cache, introduced Lazy Free in version 4.0 and multi‑threaded I/O in version 6.0 to mitigate blocking deletions and improve I/O throughput, detailing the underlying event model, code implementations, performance trade‑offs, and comparisons with Tair's threading design.
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.
Redis, traditionally single‑threaded for network I/O and key‑value operations, introduced multithreading in version 6.0 to improve network I/O handling; this article explains the original single‑thread design, when multithreading is appropriate, its drawbacks, the role of I/O multiplexing, and why Redis still keeps most work single‑threaded.
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.
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.
The article explains how Redis evolves from a single‑threaded in‑memory cache to using Lazy Free and multi‑threaded I/O to alleviate blocking on large key deletions, describing the underlying event model, code implementations, performance trade‑offs, and comparisons with Tair's threading approach.
This article explains the characteristics of OLTP and OLAP workloads, compares their I/O patterns, and provides Oracle database performance best‑practice guidelines, including storage planning, SAN architecture, operating‑system queue‑depth settings, and SwingBench testing results for optimal configuration.
This article explains how Redis evolves from a single‑threaded event‑driven cache to using Lazy Free for asynchronous key deletion and multi‑threaded I/O for improved performance, detailing the underlying mechanisms, code implementations, limitations, and comparisons with Tair's threading model.
This article reviews the history of MySQL, introduces MariaDB as its open‑source fork, and presents a series of benchmark tests—including single‑row inserts, batch inserts, and various query scenarios—showing that MariaDB generally outperforms MySQL in speed while using more memory.
This article translates and expands on a HikariCP wiki post, explaining why smaller database connection pools often yield better performance, presenting benchmark videos and data, describing the underlying CPU, disk, and network constraints, and offering a simple formula to calculate an optimal pool size for typical server configurations.
This article explores why MySQL queries become slow, delves into index pitfalls and optimization techniques, then compares ElasticSearch and HBase architectures, offering practical guidance on when to use each technology and how to combine them for high‑performance data retrieval.
This article provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
This article shares practical MyBatis tips, detailing what different return types yield when no records match, common exceptions you may encounter, transaction lock behavior with FOR UPDATE, and essential SQL optimization strategies such as using LIMIT, proper indexing, and avoiding inefficient patterns.
The article explains why appending LIMIT to MySQL DELETE statements improves performance, reduces lock contention, and safeguards data by limiting rows affected, provides syntax details, compares deletion strategies for removing large numbers of rows, and offers practical recommendations for safe and efficient data removal.
This article explains why MySQL queries that use indexes can still become slow, examines full‑index scans, index selectivity, back‑table costs, and shows how features like index condition pushdown and virtual columns can dramatically improve performance.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
This article examines how MySQL binlog timestamps and the SET TIMESTAMP command affect the exec_time values of statements, especially the BEGIN event, through step‑by‑step experiments that reveal timing discrepancies and their impact on log accuracy.
This article explains why massive traffic spikes can cripple database performance, introduces cache layers and common caching patterns, and offers practical solutions to consistency, concurrency, penetration, and avalanche problems for large‑scale internet applications.
The article explains the reasons behind rapid growth of MySQL slow query logs, introduces four key variables that affect logging, describes the decision process for recording queries, and provides practical verification scenarios to help DBAs fine‑tune slow‑log settings.
This tutorial explains MySQL benchmarking fundamentals and walks you through installing sysbench, configuring test parameters, preparing data, running the benchmark, analyzing results, and applying best‑practice recommendations to accurately assess database performance.
This article explains why using indexes in MySQL does not always prevent slow queries, analyzes full‑index scans, index selectivity, back‑table lookups, virtual columns, and provides practical optimization techniques to reduce scanned rows and improve performance.
This article explains why Redis memory usage stays high after deleting keys, describes memory fragmentation causes, shows how to detect fragmentation with INFO memory and mem_fragmentation_ratio, and provides practical steps and configuration options to clean and prevent fragmentation.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, pre‑filtering, and intermediate result handling, and provides rewritten SQL examples that dramatically reduce execution time.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article explains why indexes dramatically improve MySQL query speed on large tables, outlines common reasons for slow SQL, shows how to use EXPLAIN and SHOW PROFILE to diagnose problems, and provides step‑by‑step examples of creating and evaluating indexes for optimal performance.
This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.
The article explains that using LIMIT with DELETE in MySQL improves safety, reduces lock time, prevents long‑running transactions, and enhances performance, and it provides syntax, examples, and expert opinions to illustrate the benefits of this habit.
This guide explains the conditions under which MySQL can use indexes for range scans, LIKE patterns, BETWEEN clauses, and sorting, detailing key_len calculation, index key vs. filter vs. table filter, and practical examples to demystify index utilization.
This article explains MySQL’s logical architecture, query processing stages, client‑server protocol, query cache, parsing, optimization, execution engine, and provides practical performance‑tuning advice such as index design, data‑type choices, covering indexes, limit pagination, and handling UNION and JOIN operations.
This article explains how MySQL transforms eligible subqueries into semi‑joins, describes the four semi‑join strategies, and details the Semi‑join Materialization approach—including Materialization‑scan and Materialization‑lookup—while showing how to identify the chosen strategy via EXPLAIN output.
This article presents twenty practical rules for optimizing MySQL indexes—including when to use or avoid indexes, how to write queries that can leverage them, the importance of index selectivity, prefix indexes, covering indexes, and common pitfalls—to help developers write faster, more efficient SQL statements.
This article explains MySQL's various join algorithms—including Simple Nested Loop, Index Nested Loop, Blocked Nested Loop, and unsupported Hash and Merge joins—detailing their mechanisms, performance trade‑offs, and optimization strategies such as MRR, BKA, join buffers, and driver‑table selection.
