This article explains why MySQL uses B+ trees for indexing, describes the underlying principles of various index types, compares MyISAM and InnoDB implementations, and provides practical optimization guidelines such as using auto‑increment primary keys, left‑most prefix rules, and configuration tuning.
This article provides a comprehensive guide to MySQL best practices, covering storage engine characteristics, optimal data type selection, index design strategies, and query‑performance tuning techniques for teams without dedicated DBAs.
This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
This article provides a comprehensive guide to improving MySQL performance by optimizing single‑table schemas, indexes, queries, engine settings, system parameters, and then discusses read/write separation, caching layers, table partitioning, vertical and horizontal sharding, compatible scalable databases and when to consider NoSQL alternatives.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
This article examines MySQL pagination strategies for large tables, detailing preparation, general LIMIT‑based pagination, subquery and ID‑range optimizations, performance test results, and practical recommendations for improving query speed on millions of rows.
This article explains how to use MariaDB's Query Profiling, Slow Query Log, and Performance Schema together with the Monyog monitoring tool to diagnose, visualize, and optimize database performance, covering configuration, log analysis, schema queries, and dashboard reporting.
This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
The article explains how MySQL processes SELECT and UPDATE statements, detailing each execution stage—from client authentication and cache lookup to parsing, optimization, and execution—while highlighting the additional redo log and binlog handling required for UPDATE operations and offering practical code examples.
This article details how to improve performance of massive MySQL tables on Alibaba Cloud by redesigning schemas and indexes, migrating to PolarDB with read‑write separation, partitioning historical data using the X‑Engine storage engine, and leveraging PolarDB's parallel query capabilities for faster analytics.
When a MySQL table grows large, CRUD performance degrades, so this article presents comprehensive optimization techniques—including field design, indexing, query rewriting, engine selection, system parameters, hardware upgrades, read‑write separation, caching, partitioning, vertical and horizontal splitting, sharding architectures, and recommendations for compatible scalable databases—to help maintain high performance and scalability.
This guide explains how to identify bottlenecks and boost MySQL performance through both soft (query, index, schema) and hard (hardware, configuration, sharding, caching) optimizations, offering practical commands, best‑practice tips, and visual illustrations for each step.
This article explains why adding a LIMIT clause to MySQL DELETE statements is a good habit, compares it with TRUNCATE, shows the correct syntax, outlines performance and safety advantages, and discusses practical strategies for deleting large numbers of rows.
A high‑traffic Java service using MongoDB experienced intermittent latency spikes and a full‑scale outage caused by excessive connection churn, kernel‑level random‑number generation bottlenecks, and mis‑configured client timeouts, which were diagnosed through log analysis, packet captures, and performance testing, leading to concrete mitigation steps.
This article explains how MySQL indexes work, compares binary trees, B‑Tree and B+Tree structures, describes their implementation in MyISAM and InnoDB storage engines, and provides practical guidelines for creating efficient single‑column, composite, and covering indexes to improve query performance.
This article explains the concept of MySQL InnoDB MERGE_THRESHOLD, how page merge and split affect performance, demonstrates how to configure the threshold for tables and indexes, and shows how to monitor and evaluate its impact using InnoDB metrics.
This article presents a comprehensive collection of SQL query optimization techniques, covering index usage, avoiding full table scans, proper handling of NULLs, functions, LIKE patterns, temporary tables, cursors, and other best practices to improve database performance and maintainability.
This article examines why naïve LIMIT‑OFFSET pagination on a multi‑million‑row MySQL table becomes increasingly slow, presents several optimization techniques—including simple LIMIT usage, sub‑query id lookup, ID‑range queries, and temporary‑table tricks—along with concrete benchmark results to guide developers toward faster data paging.
This article analyzes a severe MySQL performance issue caused by massive user‑access logs, then presents three solution paths—optimizing the existing database, migrating to a compatible high‑performance database, and adopting big‑data technologies—detailing design best practices, indexing, partitioning, sharding, and cloud‑native options to restore query speed and scalability.
The article details how a half‑hour SSRS report query on a massive MES database was transformed into a sub‑second operation by analyzing the original SQL, eliminating full table scans, adding proper indexes, using temporary tables and a well‑designed stored procedure, while also discussing common pitfalls and best‑practice tips for high‑performance database querying.
This article analyses the root cause of generic "exception" errors during article publishing caused by mismatched MySQL wait_timeout and Druid JDBC pool settings, explains the impact of these parameters, and provides step‑by‑step configuration adjustments and alternative solutions to stabilise the backend database connection pool.
The article explains MySQL 8.0's new statement_digest() and statement_digest_text() functions, showing how they generate normalized SQL texts and SHA2 hashes to group similar queries, and demonstrates practical applications in performance schema analysis and query‑rewrite plugins with concrete code examples.
This article examines performance problems with a 20‑million‑row MySQL user‑log table on Alibaba Cloud RDS, outlines three solution paths—optimizing the existing database, migrating to a MySQL‑compatible high‑performance service, and adopting a big‑data engine—and provides detailed guidance on schema design, indexing, partitioning, and practical SQL tweaks.
This article examines why a MySQL 5.6 RDS table with tens of millions of rows becomes unbearably slow, then presents three concrete approaches—optimizing the existing database, migrating to a MySQL‑compatible service, and adopting a big‑data engine—detailing design, indexing, partitioning, sharding, and cloud options to restore performance.
The article explains why traditional OFFSET and LIMIT pagination becomes inefficient for large tables due to full‑table scans, demonstrates the performance gap with real‑world examples, and presents keyset (cursor‑based) pagination as a fast, index‑driven alternative with practical query patterns.
This article details how to transform a painfully slow SSRS report that scanned billions of rows into a high‑performance solution by analyzing the original SQL, fixing indexing and partitioning mistakes, and rewriting the logic as a flexible stored procedure that runs in seconds.
This article details ByteDance's engineering practices for using ClickHouse to ingest, store, and query massive real‑time recommendation and advertising data, covering early external‑transaction mechanisms, the risks of direct INSERTs, the design and evaluation of Kafka Engine versus Flink pipelines, and a series of performance and reliability improvements implemented to support high‑frequency workloads.
This article investigates the practical limits of joining more than three tables in MySQL by designing experiments with up to 1.5 billion rows, comparing indexed and non‑indexed queries, and contrasting the results with Oracle's performance, while providing full SQL scripts for data generation and analysis.
A production MySQL query that should have returned instantly took 44 seconds because the optimizer selected the primary key index instead of a suitable composite index, and the article explains the root cause, the optimizer's decision process, and multiple practical solutions to prevent such slow‑query incidents.
This article explains the concept of MySQL compressed tables, highlights strong compression products, discusses why and when to use them, outlines their advantages and drawbacks, details supported compression algorithms, and provides step‑by‑step examples for both MyISAM and InnoDB engines along with their impact on B‑tree pages and the InnoDB buffer pool.
This article explains why using NULL columns in MySQL can hurt performance and increase storage, debunks common myths with official documentation, and provides practical reasons and code examples for preferring NOT NULL constraints in table design.
This article explains why using indexes does not guarantee fast queries in MySQL, analyzes full index scans, index selectivity, the cost of row look‑ups (back‑to‑table), and presents optimization techniques such as index condition pushdown and virtual columns to improve performance.
This article details how ByteDance leverages ClickHouse to power large‑scale advertising audience estimation, profiling, and statistical analysis, describing the challenges of massive data, strict latency requirements, and the evolution from a simple tag‑uid table to a bitmap‑based architecture with extensive parallel and cache optimizations.
This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
QQ Music replaced its slow Hive warehouse with a massive ClickHouse cluster, achieving sub‑second to ten‑second query latency on petabyte‑scale data, enabling real‑time analytics for non‑technical users, and following five operational best practices—ZooKeeper planning, idempotent writes, sensible partitions, read‑write separation, and localized joins—while leveraging Tencent Cloud’s managed ClickHouse service.
This article details a comprehensive investigation of MySQL out‑of‑memory (OOM) alerts on a Tencent Cloud CDB instance during a high‑traffic marketing event, covering background, observed symptoms, step‑by‑step debugging, analysis of server and engine memory usage, relevant MySQL bugs, fetch_cache behavior, and the final configuration recommendations to prevent OOM.
Discover the true execution sequence of SQL queries, why SELECT runs fifth, how window functions and GROUP BY interact, and the impact of engine optimizations, illustrated with diagrams and code examples spanning aliases, LINQ, and pandas.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
The article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) for existence checks in SQL and shows how to simplify the corresponding Java code for better performance.
This article presents a thorough overview of MySQL optimization, covering the query execution process, philosophical principles, risk assessment, required participants, optimization dimensions, commonly used diagnostic tools, emergency and regular tuning procedures, hardware and system level adjustments, as well as detailed configuration parameters for both the server and the database engine.
This article explains how a friend loaded over 2 billion rows into MySQL using XeLabs TokuDB, detailing configuration, bulk‑load commands, benchmark results of roughly 574 k rows per second, and comparisons with InnoDB performance on a cloud server.
This article details a real‑world test that loads 200 million rows into a MySQL TokuDB table in under six minutes, achieving roughly 570 k writes per second, and compares the results with InnoDB while providing configuration tips and bulk‑loader considerations.
This article explains how to load over 20 billion rows into MySQL using the XeLabs‑compiled TokuDB engine, detailing configuration tweaks, table schema, bulk‑loader commands, benchmark results that reach 574 k rows per second, and practical tips for handling auto‑increment keys.
This article explains various MySQL pagination methods—including simple LIMIT queries, sub‑query optimizations, ID‑range filtering, and temporary‑table approaches—provides detailed performance measurements on a large order_history table, and concludes with practical recommendations while also promoting a comprehensive interview‑question PDF collection.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
The article explains why using SELECT 1 with LIMIT 1 to test for record existence is more efficient than counting rows with SELECT count(*), and shows how to rewrite both SQL and Java code for better performance.
This guide introduces MySQL 8's Explain Analyze tool, demonstrates how to prepend EXPLAIN ANALYZE to a query, interprets its detailed output—including cost, actual time, rows, and loops—using a sample query on the Sakila database, and provides steps to set up the test environment.
This article presents comprehensive techniques for improving MySQL performance on large single tables, covering column choices, index design, query writing, engine selection, system parameters, hardware upgrades, read‑write splitting, caching, table partitioning, vertical and horizontal sharding, and guidance on selecting mature sharding solutions.
The article investigates the Alibaba Java development manual's rule that joins involving more than three tables are prohibited, by designing massive MySQL and Oracle experiments, generating billions of rows, measuring query times, analyzing indexing effects, and concluding that the rule is MySQL‑specific and driven by large‑scale performance limits.
This article presents a thorough guide to improving MySQL performance for large single tables, covering field choices, indexing best practices, query tuning, engine selection, system parameters, hardware upgrades, read/write separation, caching layers, partitioning, vertical and horizontal sharding, and compatible scalable database alternatives.
This article explains how to monitor and estimate the progress of a massive MySQL UPDATE by observing rows_examined in performance_schema, deriving a progress formula, and adjusting the coefficient based on whether primary keys or only data columns are modified.
This article explains MySQL's core architecture, lock and transaction mechanisms, storage engines, and provides practical creation‑time and query‑time optimization techniques such as proper data types, index selection, query rewriting, and performance analysis using EXPLAIN, supplemented with concrete SQL examples.
This article details a real‑world case of write‑hotspot performance degradation in a TiDB 3.0.9 cluster, explains TiDB’s architecture, analyzes monitoring data and hot‑region tables, and presents a redesign using varchar primary keys and SHARD_ROW_ID_BITS to disperse writes and dramatically improve latency.
This article presents a detailed MySQL performance benchmark of Alibaba Cloud RDS, Tencent Cloud RDS, and AWS RDS using sysbench, analyzes configuration differences, shows results across mixed, read‑only, and no‑index update workloads, and offers practical recommendations for cloud database tuning.
This guide introduces four open‑source MySQL diagnostic utilities—MySQLTuner, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run them against a live server, interpret the generated AWR‑style reports, and apply the recommended configuration changes for better stability and speed.
This guide introduces four key MySQL optimization utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—explains how to download, install, and run each tool, and shows how to interpret their health‑check reports to identify performance bottlenecks, configuration issues, and security risks.
During the COVID‑19 school closures, the education platform rapidly expanded its backend and database architecture, achieving a 20‑fold traffic increase through vertical upgrades, read/write separation, high‑availability redesign, and meticulous performance tuning, all within a few days of planning and execution.
This tutorial explains MySQL fundamentals, the advantages of using MySQL, detailed concepts of indexes, how they work, step‑by‑step SQL examples for creating, viewing, and dropping indexes, best‑practice principles, and query‑optimization techniques to improve performance.
This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
This article presents a concise collection of twelve actionable SQL tuning techniques—including using proper comparison operators, LIMIT 1, appropriate data types, breaking large statements, UNION ALL, consistent query formatting, avoiding SELECT *, indexing WHERE/JOIN/ORDER BY columns, pagination with LIMIT, and leveraging EXPLAIN—to help developers improve database performance.
The article chronicles Alibaba’s five‑year journey from the early CloudDBA diagnostic tool to the self‑driving Database Platform (SDDP), detailing product pivots, data‑driven engineering, AI collaborations, and the technical and organizational challenges overcome along the way.
This article details Hema's approach to guaranteeing system stability for its offline and delivery operations, covering the complete smart‑dispatch architecture, exhaustive dependency analysis, database and middleware safeguards, monitoring strategies, gray‑release practices, testing methods, and emergency response procedures that together enabled a year of zero failures.
This article explains how MySQL's Adaptive Hash Index (AHI) works, why it is needed to reduce B‑tree lookup costs, the three stages of building an AHI, practical examples of its construction, performance benefits, and operational recommendations for managing AHI in production environments.
The article explains PostgreSQL materialized views, their evolution, how they differ from regular views, demonstrates creating and refreshing them, and compares them with incremental rollup tables using upsert to help decide which method best fits performance and scalability needs.
This article provides a thorough overview of MySQL optimization, covering the underlying philosophy, risk considerations, participants, optimization scope across hardware, system, application, and database layers, practical tools, command‑line techniques, and detailed tuning recommendations for both system and database parameters.
This article investigates the claim from Alibaba's Java Development Manual that joining more than three tables should be avoided, by setting up a MySQL 5.7 environment, generating massive synthetic data, executing multi‑table join queries, analyzing execution times, and comparing the results with Oracle, ultimately revealing the practical limits of MySQL joins on large data sets.
This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.
This article introduces the open‑source SQL optimization tool Soar, explains its core features, configuration methods, heuristic rules, multiple report formats, syntax checking, SQL rewrite capabilities, EXPLAIN analysis, markdown‑to‑HTML conversion, and how to clean up test artifacts, providing concrete command‑line examples throughout.
This article explains the different forms of MySQL's COUNT function, compares COUNT(*), COUNT(1) and COUNT(column), reveals engine‑specific optimizations for MyISAM and InnoDB, and provides guidance on which form to use in practice.
This guide explores advanced MySQL techniques—including ON DUPLICATE KEY UPDATE for upserts, INSERT IGNORE to bypass errors, replacing subqueries with JOINs, handling LEFT JOIN pitfalls, efficient pagination strategies, multi‑table UPDATE/DELETE via JOIN, and the nuances of COUNT(*) versus COUNT(1).
This article explores MySQL's hidden rowid concept, demonstrates how to observe its existence, explains its relationship with primary keys, highlights the risks of primary‑key misuse, and provides a step‑by‑step test that reveals rowid's potential overflow bottleneck.
This article introduces MySQL fundamentals, covering logical architecture, locking, transactions, storage engines, schema and data type design, as well as index types and their constraints, plus practical creation‑time and query‑time optimization techniques with code examples and best‑practice recommendations.
This comprehensive guide walks you through MySQL optimization philosophy, risk management, stakeholder involvement, practical tools, hardware and system tuning, kernel parameter adjustments, and detailed MySQL configuration tweaks to achieve both performance gains and reliable stability in production environments.
This article explains the functional and performance differences among MySQL's count(*), count(1), and count(column) expressions, illustrates how NULL values affect each, and provides a practical example with query results to guide optimal usage.
This comprehensive guide explains MySQL optimization philosophy, required participants, key dimensions, essential tools, step‑by‑step tuning procedures, hardware and system tuning, and detailed MySQL and InnoDB parameter settings to improve performance and reliability.
This guide walks through MySQL's query process, optimization philosophy, required participants, dimensions, tools, and detailed hardware, system, and configuration tuning steps, providing actionable commands and code snippets to improve database stability, security, and performance.
This article details Ctrip's hotel data platform challenges with billions of daily updates and near‑million queries, evaluates various storage options, explains why ClickHouse was chosen, and describes the full‑load and incremental pipelines, monitoring, server clustering, and practical tips that enable sub‑second query performance at massive scale.
This guide walks you through MySQL's query process, the philosophy and risks of optimization, the key participants, core dimensions such as hardware, system, schema and SQL, essential diagnostic tools, emergency and regular tuning steps, system‑level monitoring, kernel and user limits, as well as detailed MySQL and InnoDB configuration parameters for achieving high performance and reliability.
The article explains five ways to obtain a table's row count in MySQL, discusses the deprecation of the sql_calc_found_rows feature in MySQL 8.0, compares the performance of the old and new approaches using EXPLAIN JSON, and recommends using the standard COUNT(*) query for accurate results.
This article presents a detailed performance comparison of ClickHouse using two real‑world cases, analyzes query timings, explores parameter tuning, schema and SQL adjustments, and summarizes practical conclusions for achieving sub‑second analytics on billion‑row datasets.
This article explains how CB‑SQL improves distributed transaction performance by introducing a transaction record table, parallel prepare requests, one‑phase commit for single‑shard writes, latch‑based consistent reads, transaction pipelines, and a staged parallel‑commit mode, dramatically reducing latency and I/O overhead.
This article details a real‑world performance test of loading over two billion rows from a big‑data platform into MySQL using XeLabs TokuDB, covering requirements, TokuDB features, table schema, bulk‑load commands, results, and practical recommendations for high‑throughput data ingestion.
This article debunks the myth that COUNT(1) is faster than COUNT(*), explains how MySQL engines handle these functions, compares their performance with COUNT(column), and offers practical tips for avoiding costly full‑table counts.
This article provides a comprehensive, step‑by‑step guide to MySQL performance tuning, covering the underlying query process, optimization philosophy, practical tools, system‑level diagnostics, hardware and OS recommendations, as well as detailed configuration parameters for both the server and storage engine.
This article presents a comprehensive guide to Java application performance optimization, covering OS‑level and JVM‑level diagnostics, profiling tools, GC analysis, JVM tuning, application‑code refactoring, and database‑layer adjustments, illustrated with real‑world case studies and code examples.
An in‑depth performance benchmark compares MySQL on CentOS 6 and CentOS 7 using sysbench with 10 tables of 5 million rows, revealing up to 40% higher QPS on CentOS 7, detailing test setup, results, and key configuration tweaks that dramatically improve throughput.
This article explains how a high‑traffic messaging platform with tens of millions of users and billions of daily records can be optimized using MySQL partitioning, sharding (both client‑side and proxy‑side), vertical database splitting, and practical migration scripts to maintain performance and availability.
This article presents twelve practical SQL optimization techniques—from using proper comparison operators and LIMIT clauses to indexing strategies and query consistency—to dramatically improve database query speed and overall application performance.
This article explains MySQL 8.0's invisible index feature, outlines practical scenarios for its use, provides step‑by‑step SQL examples to create, hide, and test indexes, and shows how to enable the optimizer to consider invisible indexes for better performance and safer schema changes.
The article traces website architecture evolution from a single‑server LAMP setup to distributed clusters, highlighting stages such as service separation, caching, load‑balanced application servers, read‑write database splitting, CDN/reverse proxy use, distributed storage, NoSQL/search integration, and finally SOA‑based business and service segmentation.
This guide compiles essential MySQL 5.5 techniques for teams without a DBA, covering storage engine characteristics, data type selection, index design—including composite, prefix, and covering indexes—and practical query optimizations such as reducing scanned rows, avoiding redundant indexes, and improving execution plans.
This article compiles practical MySQL tips for teams without a DBA, covering MySQL's storage‑engine characteristics, data‑type choices, index design strategies, and query‑performance improvements with concrete examples, code snippets, and best‑practice recommendations.
This article describes how to ingest more than two billion records into MySQL by leveraging XeLabs TokuDB’s bulk‑loader, detailing configuration, table schema, performance metrics, and a comparison with InnoDB to demonstrate a three‑to‑fourfold speed improvement.
This article examines MySQL indexing by exploring underlying data structures such as B‑Tree and B+Tree, common search algorithms, storage engine differences, and practical optimization techniques like composite and prefix indexes, providing a comprehensive guide for designing efficient database indexes.
This article explains the main index structures used in MySQL—including hash tables, ordered arrays, search trees, and B+ trees—covers why InnoDB prefers B+ trees, the benefits of auto‑increment primary keys, and key concepts such as covering indexes, the left‑most prefix rule, and index‑pushdown for query optimization.
When a critical Oracle 10g database on AIX spikes to 100% CPU, this detailed case study walks through emergency actions, AWR analysis, hidden bottlenecks, and practical tuning steps that reduced CPU usage by half and restored system stability.
This article details a real‑world test of loading over 2 billion rows into MySQL using XeLabs TokuDB, compares InnoDB and TokuDB performance, shows configuration, table schema, bulk‑loader commands, and provides practical conclusions for high‑throughput data ingestion.
This article describes how to quickly import more than two billion rows from a big‑data source into MySQL by leveraging XeLabs TokuDB’s bulk‑loader, showing configuration, code examples, performance results, and practical recommendations for handling large‑scale data ingestion.
