This technical report details the requirement analysis, environment setup, monitoring tools, load‑test scripts, data design, execution results, and optimization recommendations for stress‑testing ClickHouse and Elasticsearch to ensure they can handle high‑concurrency business peaks.
This article analyzes the performance and storage problems of the Windows version of WeChat, explains why message growth causes slowness, large file size, and corruption risk, and presents a multi‑pronged solution involving sharding, indexing, payload compression, and robustness improvements that halve database size and boost I/O performance by about ten percent.
ByteHouse, built on ClickHouse, addresses key limitations such as missing upsert/delete, weak multi‑table joins, scalability issues, and lack of resource isolation by introducing a modular, stage‑based execution engine, advanced join strategies, runtime filters, and a custom optimizer, delivering dramatically faster query performance.
This article examines various multi‑table join scenarios in MySQL, demonstrating when partition tables can accelerate queries and when they degrade performance, supported by concrete examples, execution times, and explain plans to guide proper partition key usage.
This article explains the concept and operation of Bloom filters, discusses their limitations such as false positives and deletion difficulties, introduces the Counting Bloom filter and Cuckoo filter as improvements, and explores their underlying hash mechanisms, performance trade‑offs, and optimization strategies.
This article explains how to identify and resolve performance bottlenecks in MySQL by locating slow queries, analyzing execution plans with EXPLAIN, focusing on type, rows and extra information, using profiling and trace tools, and applying practical optimization techniques illustrated through multiple real‑world cases.
This article presents a systematic guide for backend engineers on diagnosing why databases become slow and offers eight practical optimization solutions—including data reduction, space‑for‑performance techniques, and appropriate storage system choices—organized by the layer of the stack they target.
This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.
The guide recommends fine‑grained MySQL management in a MyBatis‑based order system—avoiding generic or dynamic SQL, explicitly using indexes, listing columns, limiting results, batching inserts, and checking row counts—while applying appropriate concurrency control (pessimistic FOR UPDATE or optimistic versioning) to improve performance, stability, and data integrity.
This article explains why executing a single DELETE on a half‑billion‑row table can cause long‑running transactions, lock contention, and replication lag, and demonstrates how to split the operation into indexed batch deletes or replace it with an insert‑into‑new‑table workflow to improve safety and performance.
This article presents thirty practical tips for optimizing SQL queries—covering index creation, avoiding costly operators, rewriting predicates, using set‑based logic, and managing temporary objects—to dramatically reduce full table scans and improve overall database performance.
This article outlines practical guidance for DBAs transitioning to Chinese domestic databases, highlighting why standard Oracle benchmark tweaks often fail, the limited relevance of buffer‑hit ratios, the importance of SQL execution efficiency, and specific strategies for handling high‑concurrency workloads.
This article demonstrates how to calculate a running total for each code and date in a MySQL table by using user-defined variables, addresses the need to reset totals per code, and shows an optimized version with the WITH clause and indexing recommendations.
This article details NetEase Interactive Entertainment's adoption of Apache Doris for large‑scale game data analytics, covering background, Doris architecture, cluster governance, tablet and compaction tuning, scaling strategies, monitoring, alerting, and fault‑handling practices to improve performance and stability.
By streamlining packet capture, cutting Goroutine count, employing Sync.Pool for object reuse, and filtering empty packets, the database audit tool’s CPU usage dropped from six cores to one, object allocation fell 90%, packet processing halved, and data loss was reduced 60%, achieving near‑zero loss with minimal performance impact.
This article explains MySQL index fundamentals, covering B+Tree structures, various index types, creation syntax, and practical optimization techniques such as prefix, covering, and composite indexes, while illustrating how to avoid common pitfalls like index misuse and performance‑degrading scenarios.
This article explains MySQL hash partition tables, illustrating their suitable use cases such as equality queries on IDs or dates, demonstrating creation, data loading, and query plans, and comparing standard hash partitions with linear hash partitions to highlight performance and data distribution considerations.
The article investigates a production MySQL outage caused by FLUSH NO_WRITE_TO_BINLOG TABLES blocking during backups, presents three experimental scenarios to identify the root cause, and offers practical recommendations such as setting query timeouts, adding indexes, and using Percona XtraBackup options to avoid backup stalls.
This article explains why databases become slow, introduces a four‑layer thinking model, and presents eight practical solutions—including reducing data volume, leveraging space for performance, and selecting the right storage system—to help backend engineers resolve 80‑90% of common performance problems.
The article explains how Meituan uses MySQL's cost‑based optimizer to analyze slow queries, generate virtual index candidates, evaluate their costs with detailed statistics, and deploy a recommendation system that validates, tracks, and governs index suggestions to reduce CPU/IO waste and prevent database failures.
This article explains how to retrieve sales statistics for multiple business units and their associated products in MySQL by comparing rows, discusses alternative approaches such as loop queries, OR concatenation, and mixed filtering, and demonstrates why row‑by‑row comparison is the most suitable solution.
This article explains why databases become slow, introduces a four‑layer thinking model, and presents eight practical optimization techniques—including data reduction, caching, sharding, master‑slave replication, and CQRS—along with their benefits, drawbacks, and suitable scenarios.
Explore MySQL's comprehensive index guide, covering basic concepts, B‑tree and B+‑tree structures, storage engine differences, index types, optimization strategies, and practical tips for designing effective primary, secondary, and composite indexes to boost query performance and reduce I/O overhead.
This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.
This article details the architecture, core services, user interfaces, and technical challenges of Baidu's product promotion system, explaining the roles of traffic owners and advertisers, the CPS billing model, dynamic library reporting, and database batch‑write optimizations for high‑throughput e‑commerce scenarios.
This article evaluates single‑threaded bulk‑insert performance in MySQL by testing various batch sizes, analyzes memory usage and transaction merging, and provides practical recommendations for efficiently generating millions of records.
This article presents a systematic method for parsing massive MySQL binlog files, identifying which fields are truly updated in each SQL statement, quantifying effective versus ineffective updates, and applying the results to optimize database design and reduce unnecessary binlog generation.
This article presents a detailed case study of optimizing a massive MySQL table using time‑based partitioning, describing the problem background, partition design, migration scripts, performance benchmarks, and ongoing maintenance procedures for a ten‑year data archive.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales data for multiple business units and their associated products in a single indexed query.
This article explains the classification of InnoDB indexes into clustered and secondary types, describes how they are stored and accessed, and introduces optimization concepts such as back‑to‑table queries, covering indexes, and index pushdown to improve MySQL performance.
This article explains how IN and EXISTS work in MySQL, compares their execution strategies, shows example queries, and provides guidance on when to use each based on the relative sizes of the involved tables.
This article investigates how the innodb_ddl_threads, innodb_ddl_buffer_size, and innodb_parallel_read_threads variables introduced in MySQL 8.0.27 affect parallel DDL execution speed and resource consumption, using a 50‑million‑row test table and presenting detailed benchmark results.
The article reviews major cache‑consistency strategies—Cache‑Aside, Read‑Through, Write‑Through, and Write‑Behind—detailing their performance and consistency trade‑offs, discusses compensation techniques such as delayed double‑delete and MySQL binlog‑based incremental parsing (DTS) for reliable deletion and HA, and advises selecting the appropriate approach based on specific business requirements.
This article explains the principles, advantages, and pitfalls of MySQL composite indexes, covering the left‑most matching rule, field order effects, when single columns can use an index, and whether a composite index can replace single‑column indexes, with practical EXPLAIN examples.
From architecture tweaks like distributed caching and read‑write separation to hardware upgrades, DB instance tuning, and concrete SQL refactoring, this guide walks through four optimization layers, illustrates each with diagrams and a full‑scale MySQL case study, and shows how performance can improve up to twentyfold.
This article explains how Bloom filters, counting Bloom filters, and Cuckoo filters work, their advantages and limitations, and how they can be used to reduce unnecessary database I/O by efficiently testing element membership before querying the database.
This article presents practical MySQL optimization methods—including proper column definitions, NOT NULL usage, JOIN over sub‑queries, UNION, transaction handling, foreign keys, table locking, indexing strategies, and a collection of query‑tuning tips—each illustrated with clear examples and code snippets to improve database speed and reliability.
The article guides readers from grasping hardware, OS, and software layers—including Intel Xeon, virtualization, VPC, and containers—to gathering performance data with tools like JProfiler and perf, then applying data‑driven optimization across business logic, architecture, code, caching, databases, runtime and hardware, illustrated by real‑world case studies and emphasizing continual learning.
This article examines seven typical scenarios where MySQL indexes unexpectedly fail—such as OR conditions, leading wildcards in LIKE, missing quotes, functions on indexed columns, NULL checks, arithmetic operations, and composite index ordering—providing SQL examples, execution plans, and practical tips to avoid costly full‑table scans.
The article explains MySQL 5.7's engine‑dependent prefix index length limits, demonstrates how utf8mb4 reduces the maximum indexable column size for MyISAM tables, provides test cases with SQL code, and offers practical solutions such as prefix indexes, composite indexes, and CTAS for large datasets.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
This article explains the concepts of driving (outer) and inner tables in MySQL join algorithms, covering Nested‑Loop, Block Nested‑Loop, and Hash Join implementations, how the optimizer selects the outer table, and comparisons with other database systems.
ByteDance’s ByteHouse, a ClickHouse enterprise edition, showcases large‑scale real‑time analytics through two detailed case studies—recommendation system metrics and ad‑delivery data—detailing technical selection, challenges, multi‑threaded Kafka Engine, async indexing, buffer engine enhancements, and the resulting performance gains.
This article provides a comprehensive overview of MySQL fundamentals—including logical architecture, locks, transactions, and storage engines—and offers practical optimization strategies for schema design, data types, indexing, and query execution, supplemented with code examples and performance‑analysis tips.
This article explains how PolarDB MySQL introduces an In‑Memory Column Index (IMCI) to combine row‑store OLTP strength with column‑store analytical speed, detailing the architectural innovations, optimizer decisions, data organization, resource isolation strategies, and benchmark results that show IMCI achieving tens to hundreds of times faster query execution than native MySQL and matching ClickHouse performance.
This article explores PolarDB MySQL’s In‑Memory Column Index (IMCI) technology, detailing its hybrid row‑column storage architecture, optimizer enhancements, parallel execution engine, and performance gains that enable real‑time analytical queries alongside OLTP workloads, and compares its benchmarks against MySQL and ClickHouse.
This article presents a comprehensive guide to MySQL performance, covering maximum data volume and concurrency limits, recommended query response times, practical table‑design rules, index classification and optimization techniques, as well as detailed SQL tuning patterns such as batch processing, operator, OR, IN, LIKE, JOIN and LIMIT improvements.
This article explains how Tencent Cloud MongoDB improves the maxTimeMS server-side timeout feature to prevent request backlog and service avalanche, covering native MongoDB limitations, optimizations in mongos write command support, and default configuration implementation.
The article explains ClickHouse's new Projection feature, how it overcomes MergeTree's single‑sort limitation and materialized view drawbacks, provides step‑by‑step commands to create, materialize, and query projections, demonstrates massive performance gains, and outlines the rules for automatic projection selection.
This article explains the impact of slow SQL on database resources and user experience, outlines priority rules for addressing slow queries, details MySQL execution steps, identifies key performance factors, and provides concrete optimization methods including enabling slow‑query logs, indexing, I/O merging, and distributed architecture.
The article explains why storing IPv4 addresses as a 32‑bit unsigned integer in MySQL saves space and improves range‑query performance, outlines the trade‑offs such as readability, and demonstrates conversion using MySQL functions and Java code examples.
The article explains why TiDB discourages large transactions, outlines the historical transaction limits, shows how to configure size thresholds, and provides concrete scripts for splitting big updates based on primary‑key characteristics to keep transaction size within safe bounds.
This article presents an in‑depth overview of ClickHouse Projection, covering its background, definition, practical use cases, underlying architecture, query analysis, consistency guarantees, performance comparisons, and real‑world production results, highlighting how it enhances OLAP workloads while maintaining strong data consistency.
This article explains MySQL 8.0's new indexing features—including function indexes via virtual columns, Index Skip Scan for non‑leftmost predicates, descending indexes to avoid filesort, and invisible indexes for optimizer control—providing examples and usage guidelines.
Using MySQL's ORDER BY with LIMIT can yield an unexpected row order because duplicate values in the sorting column cause nondeterministic results; adding a unique secondary column like id stabilizes the order, and the official documentation explains how LIMIT influences query planning and optimization.
During a performance test a MySQL server showed one CPU core constantly at 99% while the others were idle, prompting a detailed investigation that checks I/O thread settings, multi‑core utilization, transaction locks, slow‑query logging, and index optimization to resolve the bottleneck.
This article explains why MySQL queries become slow, how proper indexing—including B+‑tree, left‑most prefix, index push‑down, and covering indexes—can improve performance, outlines common causes of index failure, and then introduces ElasticSearch search capabilities and HBase column‑family storage as complementary solutions for large‑scale data handling.
This article explains why MySQL chooses B+ trees for its indexes, covers the left‑most prefix rule for composite indexes, offers practical index‑design tips, discusses MyBatis caching, master‑slave replication, and sharding strategies for large‑scale databases.
Storing IPv4 addresses as a 32‑bit UNSIGNED INT in MySQL saves space, enables faster range queries, and leverages built‑in conversion functions like INET_ATON/INET_NTOA, while requiring manual readability handling, and can be complemented by Java utilities for application‑level conversions.
This article explains which components (user processes, IO wait, system interrupts) consume CPU in MySQL, shows their impact on performance, and provides practical strategies to reduce CPU usage through IO optimization, query tuning, data modeling, caching, and hardware upgrades.
This article explains why a naïve Python script for deduplicating millions of rows is too slow, then walks through a series of MySQL queries—including how to identify duplicate names, avoid the 1093 error, and delete duplicates while keeping a single representative row—demonstrating fast, reliable cleanup of large tables.
This article provides a comprehensive collection of MySQL optimization techniques—including storage engine characteristics, data type selection, index design, and query rewriting—targeted at teams without a dedicated DBA, using MySQL 5.5 and InnoDB as the reference platform.
This article examines a real‑world Oracle SQL query, identifies costly full‑table scans and low‑selectivity columns, and demonstrates how creating an index on a key column reduces execution time from 24 minutes to just one second, highlighting essential SQL performance‑tuning techniques.
Developers often use SELECT count(*) to check record existence, but replacing it with SELECT 1 … LIMIT 1 improves performance by stopping after the first match, allowing simpler Java null checks and potentially reducing index usage, especially when only a binary presence test is needed.
This article compiles over fifty practical tips covering database design, code practices, and cache usage to help developers dramatically reduce bugs, improve reliability, and boost overall software quality in everyday backend projects.
This article experimentally demonstrates how increasing the number of secondary indexes on MySQL tables progressively degrades write operations—including INSERT, UPDATE, DELETE, and LOAD DATA—by measuring execution times across ten tables with one to ten indexes each.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, then compares Elasticsearch and HBase as complementary storage and search solutions, providing practical code examples and best‑practice recommendations.
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.
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.
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 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 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 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 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 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 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 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.
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.
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 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.
The article explains why developers insert the clause "WHERE 1=1" in dynamically generated SQL statements, shows example Java code, discusses how it prevents syntax errors when concatenating AND conditions, and offers advice on adding selective filters and indexes to avoid full table scans.
The article investigates why an InfluxDB 1.8 instance on a 32‑core, 64 GB production server consumes over 95% memory and generates heavy disk I/O, analyzes runtime statistics, pprof data, and Go memory‑release behavior, and presents configuration and runtime tweaks that reduce memory usage to ~55% and I/O load to acceptable levels.
This article explains how to conduct stress testing using JMeter, monitor Java applications with jconsole and jvisualvm, optimize gateway and microservice performance, improve database query speed with indexing, and tune garbage collection by enlarging the Eden space, providing step‑by‑step instructions and results.
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 what a prefix (partial) index is in MySQL, why it can reduce index size and improve query speed, outlines suitable scenarios and limitations, and demonstrates performance differences through a series of tests with varying prefix lengths on a large test table.
This article analyzes common causes of slow MySQL queries—especially index misuse—offers practical indexing techniques, explains MDL locks and large‑table bottlenecks, and then compares ElasticSearch and HBase as complementary solutions for high‑performance search and storage.
This article demonstrates how to create a test table, build indexes, and analyze their effectiveness across multiple query scenarios using MySQL's EXPLAIN output, highlighting the best‑left‑prefix rule, range condition impacts, ORDER BY considerations, and common pitfalls such as Using filesort and temporary tables.
This article investigates why the Alibaba Java Development Manual advises against joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, indexing effects, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article explains the fundamentals of MySQL table lookup (回表操作), compares low‑version and high‑version handling, and demonstrates how index pushdown reduces unnecessary lookups, using clear diagrams and a practical user‑table example.
The article investigates the Alibaba Java Development Manual's rule against joining more than three tables by designing and executing extensive MySQL and Oracle experiments, generating massive test data, measuring query performance, and concluding that the restriction stems from join scalability limits on large datasets.
This article explains why MySQL queries become slow, how proper index design, MDL locks, sharding, read‑write separation, and the use of ElasticSearch or HBase can improve query performance in large‑scale systems, and provides practical tips and code examples for each technique.
This article explains InnoDB's record storage, primary and secondary index mechanics, composite index rules, and provides practical e‑commerce sharding and table‑design guidelines with real‑world case studies to help developers optimize MySQL performance and storage efficiency.
The article explains how HikariCP outperforms other JDBC pools by using bytecode‑generated proxies, a custom FastList, and a thread‑local ConcurrentBag, delivering superior speed, low resource use, and easy migration, making it the default high‑performance connection pool for Spring Boot 2.0.
Concurrent ProgrammingConcurrentBagDatabase Connection Pool
This article describes a real‑world requirement to import over 2 billion records into MySQL, analyzes the challenges, introduces XeLabs TokuDB with its optimizations, details the test schema and configuration, demonstrates bulk loading commands, presents performance metrics showing up to 570 k rows per second, and concludes with practical recommendations and environment details.
This article introduces MySQL index basics, covering index concepts, advantages and disadvantages, various index types such as B‑Tree, B+Tree, hash, and full‑text, their storage structures, creation syntax, left‑most prefix and covering indexes, and practical design guidelines for effective indexing.