An in‑depth guide to MySQL optimization covering the query execution process, risk‑aware philosophy, hardware and system tuning, essential diagnostic tools, practical SQL and index adjustments, and comprehensive configuration settings to boost performance while maintaining stability and security.
This article outlines the philosophy, risks, participants, and step‑by‑step strategies for MySQL optimization, covering query flow, hardware and system tuning, configuration parameters, monitoring tools, and SQL‑level improvements to achieve stable, high‑performance database operations.
The 2018 3306π Shenzhen annual conference, co‑hosted by the 3306π community, Zhishu Hall and vivo, attracted over 150 attendees and featured expert talks on MySQL 8.0, open‑source data‑transfer middleware, TDSQL for finance, RadonDB, InnoDB enhancements, large‑scale Redis clusters, and Youzan database operations, with presentation materials available online.
The article details Tencent’s TXSQL (Tencent MySQL) architecture and optimizations—including asynchronous table deletion, DDL NO_WAIT/WAIT, MDL visibility, lock splitting, parallel replication, query push‑down, read‑view enhancements, low‑overhead auditing and KMS‑integrated encryption—highlighting its role as the core engine powering CDB for diverse industries such as gaming, finance, e‑commerce and internal services like WeChat and QQ.
This article provides a comprehensive guide to MySQL optimization, covering the underlying query process, risk-aware philosophy, practical troubleshooting steps, hardware and system tuning, essential monitoring tools, and detailed configuration adjustments to improve performance and stability in production environments.
This article provides a comprehensive guide to MySQL optimization, covering the underlying query process, philosophical considerations, practical troubleshooting steps, hardware and system tuning, essential tools, and detailed configuration adjustments to achieve stable and high‑performance database operations.
This article provides a comprehensive overview of MySQL indexing, covering B‑Tree and B+Tree structures, common search algorithms, storage engine differences, and practical optimization techniques such as composite and prefix indexes to improve query performance.
This article explains how to reduce TFS database growth by managing document, source code, build package, and test artifact storage, offering best‑practice recommendations, configuration tips, and SQL scripts for analyzing and cleaning up large data tables and optimizing repository structures.
This article presents a thorough guide to improving web performance by optimizing browser loading, leveraging CDNs and reverse proxies, applying server‑side caching and clustering techniques, and refining database queries and indexing to achieve faster, more reliable user experiences.
This article provides a comprehensive overview of SAP's product portfolio, focusing on ERP architecture, common performance bottlenecks, and detailed recommendations for database and storage optimization to improve SAP ERP responsiveness and scalability.
This guide shows how simple MySQL configuration changes—switching to InnoDB, allocating most RAM to the buffer pool, and enabling multiple buffer pool instances—can deliver up to an 80% performance boost for typical web‑site workloads.
This article presents comprehensive best‑practice guidelines for database design, indexing, SQL query tuning, Java data‑handling techniques, and hardware adjustments to dramatically improve database performance and reduce resource consumption.
This article provides a comprehensive guide to MySQL optimization, covering the query process, risk‑aware philosophy, collaborative participation, multi‑layer optimization dimensions, essential diagnostic tools, system‑level tuning, hardware recommendations, and key database configuration tweaks to achieve stable and high‑performance database operations.
This article explains how to use MySQL's EXPLAIN to verify index usage and outlines ten common situations—such as using !=, OR, functions, or low‑cardinality columns—that can cause indexes to become ineffective, helping developers and interviewees avoid performance pitfalls.
This article explains MySQL's row‑level, table‑level, and page‑level locks, maps them to storage engines, details MyISAM and InnoDB lock types, describes the four internal lock queues, and provides practical optimization and monitoring techniques to improve concurrency and performance.
This article presents seven practical MySQL performance‑tuning techniques—including mastering EXPLAIN, building effective indexes, overriding default settings, optimizing memory usage, adopting SSD storage, scaling horizontally, and improving visibility—while explaining key configuration variables and monitoring tools to keep your database fast and reliable.
This article outlines practical strategies for building and scaling web applications—covering caching, static page generation, database optimization, read/write separation, NoSQL, Hadoop, distributed deployment, service separation, and CDN—to handle massive data volumes and high‑traffic loads efficiently.
The article analyzes why dropping large InnoDB tables in MySQL can drastically degrade overall database performance due to buffer‑pool mutex and file‑system I/O bottlenecks, and presents both operational work‑arounds and source‑code modifications to alleviate the issue.
This article summarizes the most compelling MySQL 8.0 enhancements—including a unified Data Dictionary, revamped Information Schema, role‑based privilege management, persistent configuration, auto‑increment fixes, InnoDB histogram and invisible index support, mutex refinements, row‑cache improvements, temporary‑table changes, character‑set updates, default parameter tweaks, and atomic DDL operations—providing practical guidance for DBAs and developers.
This article explains InnoDB's record storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and shares practical table‑design guidelines and real‑world case studies from ZhaiZhai's MySQL usage.
This article explains InnoDB storage mechanics, compares auto‑increment and business primary keys, details primary, secondary, and composite index structures, and presents practical table‑design principles and real‑world case studies from ZhaiZhai's services to guide developers in building efficient MySQL schemas.
This article describes LinkedIn's network‑level MySQL query analyzer, explaining why it was needed, its three‑component architecture (agent, central server, UI), fingerprinting and hash‑map techniques, performance benchmarks, metric collection, security considerations, and the benefits it brings to database operations.
This article describes the design and implementation of a five‑layer one‑stop fund processing platform, covering architecture, rapid business support, hotspot data handling, storage optimization, and query improvements, including sharding, asynchronous processing, and dynamic data source strategies to ensure scalability and reliability.
This article explains UDB's high‑availability architecture, detailing its dual‑node design with virtual IP and HAProxy, and describes the kernel‑level optimizations applied to MySQL's native semi‑synchronous replication, relay log handling, master.info management, and lock contention to boost stability and performance.
This article details UDB's high‑availability architecture and four kernel‑level optimizations—binlog replication, relay‑log recording, master.info handling, and relay‑log locking—that together improve MySQL semi‑synchronous replication performance and reliability.
This article details Alibaba's CloudDBA system, explaining its business and user demands, architecture layers, SQL and space optimization techniques, data‑driven decision making, and ongoing AI‑based intelligent optimization research, illustrating how large‑scale database services are automated and self‑service for developers.
This article walks through a real‑world MySQL performance case at Meituan‑Dianping, showing how systematic monitoring, packet capture, and step‑by‑step analysis identified the Thread‑Pool as the bottleneck and how disabling and fine‑tuning its parameters reduced slow queries from thousands to just a few dozen.
This guide explains how to analyze MySQL performance with EXPLAIN statements, composite indexes, slow‑query logging, storage engine locking differences, and transaction settings, providing practical examples and detailed descriptions of each EXPLAIN output column.
This guide presents ten practical MySQL performance techniques—from workload profiling and resource awareness to smart indexing, pagination, and monitoring—offering concrete steps, tool recommendations, and common pitfalls to help database administrators keep their servers fast, stable, and scalable.
The article presents several large‑scale Alibaba use cases—real‑time deduplication of shopping‑guide articles, precision ad targeting, TOB real‑time profiling, arbitrary field combination queries, and fuzzy matching—demonstrating how PostgreSQL’s array types, GIN indexes and pg_trgm extensions enable millisecond‑level responses on billions of records.
Before profiling performance you must locate slow queries, enable MySQL's slow query log, use EXPLAIN to analyze execution plans, and add appropriate indexes—such as on picture.album_id—to dramatically reduce scanned rows and boost query speed by hundreds of times, while balancing read/write trade‑offs.
Facing a 0.02% SQLite corruption rate that threatened years of chat history, the WeChat mobile team identified three main causes—insufficient space, power loss, and sync failures—and implemented space management, full sync settings, and master‑table backups, halving damage and doubling repair success.
This article details the design, performance enhancements, feature extensions, stability improvements, and benchmark results of TXSQL—a heavily customized MySQL branch used by Tencent Cloud’s CDB service—to illustrate how it achieves higher throughput, stronger consistency, and better reliability for large‑scale cloud databases.
This guide outlines practical MySQL optimization strategies—including table schema design, index creation, SQL tuning, connection‑pool configuration, and historical data archiving—to help developers maintain fast, scalable databases as data volume grows.
This article introduces the architecture, key modules, and implementation details of the Little Boy platform, a Greenplum optimization and operations system that parses SQL, applies index and distribution‑key tuning, manages resources, and outlines future enhancements for large‑scale data warehouses.
This article outlines the five evolutionary stages of a MySQL deployment—table design, deployment, performance tuning, architecture scaling, and maintenance—detailing practical configuration tweaks, kernel and hardware optimizations, replication strategies, caching, sharding, partitioning, and essential monitoring and backup techniques.
This article provides a step‑by‑step tutorial on preparing the environment, configuring the query analyzer, using SQL Server Profiler, and applying common commands such as clearing caches and enabling statistics to identify and resolve performance bottlenecks in SQL Server queries.
Facing a tight September release deadline, the author tackled two critical Oracle SQL performance issues by analyzing execution plans, replacing a costly view, applying driver‑table hints, removing unnecessary CASTs and a random‑number function, ultimately cutting query time from 11 seconds to under 2 seconds.
This article walks through a real‑world SQL Server case study, explaining how to assess system hardware, interpret performance counters and wait statistics, identify CPU, memory, disk and indexing issues, and apply a three‑stage optimization process to dramatically improve database performance.
A client reported intermittent database slowness, which was traced to high CXPACKET wait times; the article explains the CXPACKET mechanism, provides a diagnostic script, analyzes disk I/O bottlenecks, and offers practical recommendations such as faster disks, separating data and log files, and isolating large log tables.
The article details how Alibaba’s database team engineered multiple breakthroughs—PK_Access query optimization, a high‑performance Memcached plugin, batch‑processed inventory hotspot handling, full‑scale SQL collection, and an upgraded Data Replication Center—to dramatically boost Double‑11 transaction throughput, cut latency, and enhance overall system resilience.
This article outlines practical techniques for optimizing large MySQL tables—including column and index tuning, query improvements, engine selection, system parameters, hardware upgrades, caching, partitioning, vertical and horizontal sharding, and discusses client‑side and proxy‑based sharding solutions and compatible databases.
This article outlines practical architectural approaches—including caching, static page generation, database optimization, read‑write separation, NoSQL, distributed deployment, and CDN usage—to efficiently handle massive data volumes and high concurrency in modern web applications.
This article explains what execution plans are, why seemingly identical SQL statements can produce different plans, and offers practical tips—such as standardizing query syntax, simplifying nested queries, using temporary tables, bind variables, appropriate transaction scopes, and careful use of NOLOCK—to improve database performance and avoid common pitfalls.
MySQL 8.0 introduces invisible indexes, allowing you to hide indexes from the optimizer while keeping them maintained, which serves as an index recycle bin for safe testing and removal, and the article explains their purpose, usage, and limitations with practical SQL examples.
The article explains the CXPACKET wait type in SQL Server, shows how to identify it with diagnostic queries, illustrates its parallelism mechanism with analogies and diagrams, and provides practical steps and hardware recommendations to resolve performance slowdowns caused by this wait.
This guide explains when and why Oracle statistics are needed, how to control automatic and manual collection, prevent out‑of‑range errors, boost efficiency with parallel and incremental methods, and handle special cases like volatile or temporary tables for optimal query performance.
The article explains how Oracle’s default DBMS_STATS.AUTO_SAMPLE_SIZE can produce wildly inaccurate cardinality estimates on highly skewed columns, examines the NewDensity and OldDensity algorithms, demonstrates the problem with real‑world examples, and offers practical solutions such as forcing estimate_percent or disabling NewDensity.
This article explores Oracle's Hint feature, reviewing its classification, demonstrating how improper use can degrade performance, presenting real‑world negative cases, and summarizing Oracle's official stance and best‑practice recommendations for when and how to apply Hints effectively.
A production MySQL 5.5 instance suffered intermittent latency and bursts of slow queries, which were traced to an oversized enabled query cache that caused mutex contention during frequent DML operations, and disabling the cache resolved the issue.
This article presents practical MySQL optimization techniques covering table schema design, character set choices, primary key strategies, field definitions, index creation, SQL query tuning, pagination tricks, join handling, connection‑pool parameter tuning, and historical data archiving for large‑scale databases.
This guide shares ten practical MongoDB performance tips—including proper index ordering, using explain() and hint(), avoiding costly query operators, leveraging capped collections, upsert, sorting limits, aggregation, drop vs remove, batch inserts, and optimal indexing for arrays and range queries—to help developers boost database efficiency.
This guide outlines practical recommendations for selecting MySQL field types, optimizing indexes, interpreting EXPLAIN output, and understanding its limitations, helping developers design efficient schemas and improve query performance while also highlighting common pitfalls such as misuse of NULL columns and inappropriate enum expansions.
This article explains why Zabbix's original table design slows down data insertion and query in large‑scale deployments, and provides a step‑by‑step code‑level optimization that creates separate "latest" tables with primary‑key indexes, modifies the server source, and updates the front‑end query to dramatically improve performance without affecting historical data ingestion.
This article outlines the lifecycle of a mature MySQL database architecture, covering table design, deployment, performance tuning, scaling strategies such as replication, sharding and partitioning, as well as maintenance tasks like monitoring, backup, and repair, with practical configuration examples.
This article explains how major companies keep MySQL fast at massive scale and provides practical techniques—including schema design, InnoDB advantages, table compression, bulk loading order, and hardware choices—to optimize MySQL performance for billions‑row tables.
This article walks through a real‑world case study of a hospital HIS running on SQL Server 2008R2, detailing the system’s hardware specs, performance metrics, three‑phase optimization process—including general tuning, statement‑level fixes, and deep CPU/memory analysis—and the measurable results achieved without adding new hardware.
This article explains how WeChat's iOS client tackled SQLite performance bottlenecks by introducing multi‑handle threading, enabling WAL mode, redesigning the Busy‑Retry mechanism, and applying I/O enhancements such as preserving WAL size and mmap usage, resulting in over 90% reduction in perceived lag.
This comprehensive presentation explores SQL’s history, its enduring relevance amid NoSQL and NewSQL trends, explains the execution order and optimizer mechanics, offers practical optimization rules and step‑by‑step execution processes, and illustrates concepts through multiple real‑world case studies and a detailed Q&A.
This article walks through practical MySQL performance tuning from a developer's perspective, covering a real‑world example, data‑type selection, index design, and SQL query refinements, while providing concrete commands, visual illustrations, and expert Q&A for deeper understanding.
This article explains the classic Oracle batch‑processing issue caused by inaccurate statistics on tables with sharply changing row counts, reviews traditional mitigation methods and their drawbacks, and introduces Oracle 12c's online statistics gathering for bulk‑load as an automated solution while noting its current limitations.
Learn ten practical strategies—from careful schema design and precise SELECT statements to query caching, index creation, and smart use of JOINs, LIMIT, UNION, and wildcards—that dramatically improve database speed and reduce server load on high‑traffic websites.
This article explains how to use MySQL's built‑in mysqlslap tool to benchmark database performance, covering basic commands, adding concurrency, generating complex tables, and testing with custom schemas and queries, while interpreting the resulting metrics for optimization decisions.
This article walks through diagnosing severe I/O spikes in a MySQL project, explains SSD characteristics, and provides step‑by‑step InnoDB parameter tweaks, I/O scheduler changes, and monitoring results that lead to a stable, balanced database performance on SSD storage.
The article explains why traditional LIMIT OFFSET pagination becomes inefficient on large MySQL tables, analyzes the performance impact of counting rows and using SQL_CALC_FOUND_ROWS, and presents several optimized techniques—including index‑based counting, keyset pagination, page‑number caching, and temporary‑table approaches—to achieve fast and scalable pagination.
This article explains how to enable MySQL scheduled events, shows the full CREATE EVENT syntax with practical timing examples, covers advanced operations like multi‑statement events and disabling, and then dives into table partitioning techniques (RANGE, HASH, KEY, LIST) with detailed commands and troubleshooting for large InnoDB tables.
This guide outlines comprehensive MySQL performance tuning techniques, covering architectural replication, system‑level hardware adjustments, MySQL configuration parameters, and application‑level query optimizations with specific recommended values for buffers, caches, and connection limits.
This article analyzes a poorly performing MES reporting SQL query that scans billions of rows, identifies indexing and partitioning shortcomings, and proposes a redesign using stored procedures, temporary tables, and query‑writing best practices to achieve sub‑second response times.
This article examines the common reasons MySQL performance may lag—outdated hardware, sub‑optimal database design, and poorly written application code—and provides concrete guidance on CPU, memory, storage, schema, and query optimizations to improve response times.
This article presents practical database optimization techniques, including SQL Server table partitioning to handle massive datasets and using the XML data type to replace traditional master‑detail designs, offering clearer schemas and faster queries for large‑scale applications.
This guide outlines concrete strategies to reduce I/O and CPU overhead in Oracle queries, covering scope reduction, partition pruning, join simplification, hint usage, bind variables, and common pitfalls like Cartesian products and inefficient filters.
This guide explains how to use MySQL's SHOW GLOBAL STATUS and SHOW VARIABLES outputs to identify and adjust key parameters such as slow query logging, connection limits, key buffer size, temporary tables, open tables, thread cache, query cache, sorting, file handles, table locks, and table scans for optimal database performance.
This article explains MySQL index fundamentals, B+‑tree structures, disk I/O considerations, and practical steps for diagnosing and optimizing slow queries, illustrated with multiple real‑world examples and concrete indexing guidelines.
This guide explains practical MySQL optimization techniques—including selecting InnoDB vs. MyISAM, avoiding COUNT(*), using EXPLAIN, and adding selective indexes—to dramatically reduce query time and prevent full‑table scans.
This article explains how InnoDB's Adaptive Hash Index works, covering its initialization, information gathering, index and block statistics updates, page‑level hash construction, usage conditions, maintenance procedures, shortcut query mode, and monitoring via performance metrics.
This article outlines a five‑layer website architecture—web cache, load balancer, web server, file server, and database—illustrated with high‑traffic e‑commerce, ad, and CDN portals, and provides practical recommendations on software choices, high‑availability setups, and performance optimization for each layer.
This article explains DB2 index scan methods, how predicates are applied on indexed columns, practical guidelines for designing efficient indexes, ways to avoid redundant or overly complex indexes, and step‑by‑step techniques for deep index cleanup using DB2 utilities and system catalog queries.
This article explains DB2's B+‑tree index structure, the difference between Indexable and Sargable predicates, how the DB2 runtime components (BM, IM, DM, RDS) process queries, and practical tips for writing efficient predicates and choosing scan strategies.
This guide explains why Zabbix monitoring can become sluggish with large MySQL tables, and provides step‑by‑step instructions—including table modifications, stored procedures for creating, dropping, maintaining, and verifying partitions, plus automation scripts—to shrink the database and improve responsiveness.
Design a flash‑sale (秒杀) platform that handles millions of concurrent purchase requests without overselling by combining web‑server load shedding, ZooKeeper‑based inventory quotas, message‑queue consumers, stored‑procedure database updates, slot‑based row locking, and hot‑item isolation, all detailed with code examples.
This comprehensive guide explains the essence of Oracle SQL tuning, presents a step‑by‑step optimization roadmap, walks through real‑world case studies—including a high‑CPU query fixed with hints and query rewrites—and details how to capture and interpret accurate execution plans while addressing optimizer parameters and common pitfalls.
This article presents a detailed case study of an Oracle RAC system performance bottleneck, walks through comprehensive monitoring, storage and partitioning analysis, and outlines a step‑by‑step optimization plan—including KEEP pool tuning and partition redesign—that dramatically improved I/O latency and overall throughput.
This article explains how Oracle's Cost‑Based Optimizer estimates the selectivity of range predicates without histograms, demonstrates calculations with sample data, shows PL/SQL code for converting string values to internal numbers, and discusses pitfalls such as identical internal values causing cardinality errors, offering solutions like gathering histograms or using DATE columns.
This article explains how a web system can grow from 100,000 to 100 million daily visits by introducing multi‑level caching, various load‑balancing strategies, MySQL performance tuning, distributed database setups, and geographic deployment to maintain stability and performance under massive traffic.
This guide explains how to monitor MySQL's Query Cache using the 'SHOW GLOBAL STATUS' command, interprets key metrics such as Qcache_hits, Qcache_inserts, and Qcache_free_memory, and shows how to assess cache fragmentation and calculate hit rates for performance tuning.
This guide explains how to use MySQL's show processlist command, interprets each column and common state values, and offers practical tips for diagnosing and resolving performance problems such as sleeping connections, locked queries, temporary tables, and query cache issues.
This article examines MySQL indexing theory, covering B‑Tree and B+Tree structures, storage‑engine differences between MyISAM and InnoDB, left‑most prefix rules, index selectivity, prefix indexes, and practical optimization techniques for high‑performance query execution.
This article walks through the background of I/O issues in a MySQL project, explains SSD characteristics, and details step‑by‑step InnoDB parameter adjustments—including I/O scheduler changes, innodb_io_capacity, and adaptive flushing—both before and after a master‑slave switch, supported by performance data and diagrams.
This article explains the characteristics and usage scenarios of B-Tree and Hash indexes in MySQL, including supported operators, example queries, when indexes are applied or ignored, and performance considerations for different storage engines such as MEMORY.
This article explains HBase's column‑oriented data model, demonstrates Java API examples for creating, reading, and deleting tables, and provides practical optimization techniques—including pre‑splitting, Rowkey design, ColumnFamily reduction, caching, and compaction settings—to improve read/write performance in large‑scale deployments.
The article explains how MySQL's Query Cache, while simple and fast, can incur CPU overhead from hash calculations, suffer frequent invalidations on table changes, and consume excessive memory, making its use potentially counter‑productive under high‑concurrency or volatile data scenarios.
This article presents a comprehensive set of MySQL performance optimization techniques for web applications, covering query caching, EXPLAIN analysis, LIMIT usage, indexing strategies, join type consistency, avoiding ORDER BY RAND(), selective column retrieval, proper primary keys, ENUM usage, prepared statements, unbuffered queries, IP storage, fixed‑length tables, vertical partitioning, batch deletions, column size reduction, storage engine selection, ORM benefits, and persistent connections.
This article outlines eight key MySQL index usage restrictions, including key length limits, unsupported data types, lack of functional and hash indexes, and conditions that prevent the optimizer from leveraging indexes, helping developers avoid performance pitfalls.
This guide explains how to enable MySQL’s Query Profiler, retrieve summary and detailed profiling data, and analyze CPU and I/O usage to pinpoint performance bottlenecks in your queries, including commands to list profiles, view specific query details, and interpret the results for optimization.
This article explains four situations where MySQL will ignore indexes—using a non‑leading column in a composite index, leading wildcards in LIKE queries, OR conditions with non‑indexed fields, and unquoted string literals—offering guidance to improve query performance.
This article provides a conceptual summary of SQL Server performance tuning, explaining the goals of optimizing response time and throughput, the importance of baselines and costs, factors influencing performance, and a step‑by‑step DETECT methodology for systematic optimization.
This guide walks you through essential MySQL and MariaDB performance‑tuning techniques—including storage engine settings, disk placement, buffer pool sizing, swappiness, connection limits, thread cache, DNS lookup, query cache, temporary tables, slow‑query logging, idle connections, file‑system choices, packet size, and testing tools—to help you optimize and maintain a high‑speed, reliable database server.
The article discusses how to redesign a high‑traffic web order platform by addressing concurrency challenges, modularizing large systems, and reducing database load through caching, sharding, and eliminating costly transactions and joins.
This article explains HBase's column‑oriented architecture, key concepts such as Rowkey, ColumnFamily, and Region, provides Java API examples for table operations, and offers practical optimization techniques—including pre‑splitting, Rowkey design, caching, and compaction settings—to improve read/write performance.
The article shares five practical insights—balancing hardware cost and capability, testing NoSQL yourself, monitoring memory and CPU metrics, accounting for network latency, and avoiding virtualization—to help engineers achieve cost‑effective, high‑performance scaling of distributed databases.
