Tagged articles
database-index
41 articles · Page 1 of 1
This article explains how database indexes accelerate queries by leveraging sorted data structures and binary search, detailing disk I/O mechanics, clustered vs. non-clustered indexes, index trade-offs on write performance, and common SQL optimization pitfalls like OR conditions and leading wildcards that cause index invalidation.
MySQLQuery PerformanceSQL Optimization
0 likes · 13 min read

The article explains how Spring’s latest multitenancy support, combined with Spring Security, JWT and Hibernate’s @TenantId, lets a single Spring Boot application safely serve thousands of SaaS tenants by automatically isolating data, avoiding manual tenant_id filters, and handling indexes, native SQL, and async tasks.
HibernateJWTSaaS
0 likes · 11 min read

The article explains how database indexes, built on sorted data and binary search, dramatically reduce query time compared to full table scans, while also covering storage fundamentals, the cost of excessive indexes, clustered indexes, index failure scenarios, and practical SQL optimization techniques.
SQL Optimizationbinary searchclustered index
0 likes · 12 min read

An in‑depth comparison of B+Tree and B‑Tree reveals how their differing node designs affect storage capacity, tree height, disk I/O, range queries, caching, and insert/delete costs, with concrete MySQL InnoDB examples illustrating why B+Tree is the preferred index structure.
B+TreeB-TreeInnoDB
0 likes · 7 min read

To prevent duplicate operations caused by network glitches, user errors, or retries, this article explains four common idempotency strategies—token, database unique index, distributed lock, and request payload hashing—providing clear concepts, key considerations, and ready‑to‑copy Java/Spring code examples.
Idempotencydatabase-indexdistributed-lock
0 likes · 9 min read

This article explains MySQL index fundamentals, covering what indexes are, their advantages and drawbacks, underlying data structures such as hash tables and B‑/B+‑trees, and the classification of clustered, non‑clustered, primary, secondary, unique, prefix, and full‑text indexes, with practical SQL examples and diagrams.
B-TreeMySQLNon-clustered Index
0 likes · 9 min read

This article explains four practical idempotency solutions—token tokens, database unique indexes, distributed locks, and request content digests—detailing their concepts, core keywords, and providing ready‑to‑copy Spring Boot code examples, along with implementation tips and a comparison table to help you choose the right approach for high‑concurrency APIs.
Distributed LockIdempotencyRedis
0 likes · 10 min read

This article explains why front‑end debouncing is insufficient for preventing duplicate orders, then walks through four backend strategies—local cache with AOP, Redis atomic operations, database unique indexes, and token verification—providing core principles, code examples, and pros/cons for each.
AOPRedisSpringBoot
0 likes · 18 min read

This article explains how databases store data on various storage devices, why indexes dramatically improve query performance through sorted structures and binary search, and outlines practical SQL optimization techniques while warning about the trade‑offs of excessive indexing.
SQL Performancebinary searchclustered index
0 likes · 11 min read

This article demystifies database indexes by explaining their purpose, inner workings, and best‑practice design, covering common pitfalls such as slow queries, over‑indexing, NULL handling, composite index ordering, covering indexes, sorting, grouping, and maintenance across different database systems.
Composite IndexCovering IndexPerformance Tuning
0 likes · 11 min read

This article explains how databases store data on various storage media, how indexes—especially clustered indexes—leverage sorted structures and binary search to dramatically reduce query time, and outlines best practices and pitfalls of index usage and SQL optimization.
Performance TuningSQL Optimizationbinary search
0 likes · 12 min read

This article provides comprehensive interview guidance and technical deep‑dives covering Java self‑introduction, the B‑T‑A‑R project‑highlight model, differences between abstract classes and interfaces, varargs usage, database index pros and cons, Redis pipeline vs Lua scripting, cache‑breakdown mitigation, distributed session consistency, and memory‑efficient Excel export techniques.
Interview TipsRedisdatabase-index
0 likes · 18 min read

This article dissects a Pinduoduo Java backend interview, covering Redis caching strategies, MQ usage, lock mechanisms, thread‑pool tuning, database index differences, and data‑structure choices like ZSet and B+‑Tree, providing concrete examples and code snippets for each topic.
B+TreeInterviewJava
0 likes · 22 min read

This article investigates MySQL's recommendation against using UUID or random Snowflake IDs as primary keys by creating three tables with different key strategies, running Spring Boot/JDBC performance tests, analyzing index structures, and concluding that auto‑increment keys offer superior insertion efficiency and fewer drawbacks.
JDBCMySQLSpring Boot
0 likes · 10 min read

This article analyzes why MySQL recommends auto‑increment primary keys over UUIDs or random snowflake IDs by designing three tables, running insert‑select benchmarks with Spring Boot's JdbcTemplate, presenting the test results, and discussing the underlying index‑structure impacts and trade‑offs.
MySQLPrimary KeyUUID
0 likes · 10 min read

This article presents a step‑by‑step investigation of a SELECT statement that became 1000× slower in OceanBase 3.2.3 BP8, explains why the optimizer chose an inefficient I5 index, describes the plan‑expiration logic, and provides reproducible scripts and practical recommendations for fixing the problem.
OceanBasePerformance TuningPlan Cache
0 likes · 19 min read

This article explains the myth of a 2 million‑row limit for MySQL tables, shows how primary‑key size and InnoDB page structure determine theoretical row limits, details B+‑tree indexing mechanics, calculates practical capacities, and compares B+‑tree with B‑tree performance.
B+TreeInnoDBMySQL
0 likes · 14 min read

This article analyzes why MySQL recommends auto_increment primary keys over UUID or non‑sequential Snowflake IDs by creating three tables, running Spring Boot/JdbcTemplate performance tests, and examining index structures, lock contention, and fragmentation to guide optimal primary‑key selection.
MySQLPrimary KeySpring Boot
0 likes · 9 min read

The article explains the fundamentals of tree data structures, compares B, B+, B*, and red‑black trees, and shows how B+ trees reduce disk I/O and improve range query performance, which is why MySQL adopts B+ trees for its indexes.
B+TreeMySQLdata structures
0 likes · 15 min read

This article investigates MySQL's recommendation against using UUID or non‑sequential keys, builds three tables with auto‑increment, UUID, and random (snowflake) primary keys, runs insertion and query benchmarks using Spring Boot and JdbcTemplate, analyzes index structures, and discusses the advantages and drawbacks of each approach.
MySQLPrimary KeyUUID
0 likes · 11 min read

This article explains how databases store data on various storage media, why indexes dramatically speed up queries through sorted structures and binary search, discusses different index types such as clustered indexes, and outlines common SQL optimization practices while warning against excessive indexing and typical pitfalls.
Performance TuningSQL Optimizationbinary search
0 likes · 12 min read

This article explains how InnoDB stores data in 16 KB pages, calculates how many rows a B+Tree index can hold, shows how to determine the tree height from the page level, and answers why MySQL uses B+Tree rather than other tree structures.
B+TreeInnoDBMySQL
0 likes · 10 min read

This article introduces MySQL indexes, explains why they are needed, compares B+Tree and hash index structures, describes how indexes improve query performance, outlines their limitations, shows how to create single‑ and multi‑column indexes, and discusses the trade‑offs of using them.
B+TreeMySQLSQL
0 likes · 10 min read

This article explains the storage units of InnoDB, calculates how many rows a B+ tree can hold at different heights, shows how to determine the tree height from the page level, and answers why MySQL uses B+ trees for indexing.
B+TreeInnoDBInterview
0 likes · 9 min read

This article explains why MySQL LIMIT with large offsets becomes slow, analyzes the execution flow, and presents four optimization techniques—including subquery, INNER JOIN, tag‑record, and BETWEEN…AND…—with real‑world performance numbers and sample SQL code.
MySQLPaginationdatabase-index
0 likes · 9 min read

This article explains how B+Tree improves disk I/O efficiency in MySQL InnoDB by detailing disk storage fundamentals, sector/block/page concepts, the differences between B‑Tree and B+Tree, and practical search examples that illustrate reduced I/O operations and faster queries.
B+TreeInnoDBMySQL
0 likes · 15 min read

This article explains how many rows a single InnoDB B+‑tree can hold by describing InnoDB’s 16 KB page size, the storage of data and index pages, calculating rows per leaf page, pointer capacity, tree height for typical workloads, and how to determine the height from the page‑level metadata.
B+TreeInnoDBMySQL
0 likes · 10 min read

This article explains how InnoDB’s 16 KB pages form B+‑tree indexes, calculates the number of rows a tree can hold at different heights, shows how to determine the tree’s height from the tablespace file, and why MySQL prefers B+ trees for indexing.
B+TreeInnoDBMySQL
0 likes · 9 min read

This article evaluates the insertion speed and index impact of three MySQL primary‑key strategies—auto‑increment, UUID, and Snowflake‑generated long IDs—by building identical tables, running a Spring Boot/JdbcTemplate benchmark, and analyzing the resulting performance and structural differences.
MySQLPrimary KeySnowflake ID
0 likes · 9 min read


ITPUBJan 19, 2021 · Databases
This article explains how database indexes improve query performance by exploring binary trees, binary search, balanced trees, B‑trees, and B+‑trees, illustrating their structures, advantages, disadvantages, and the impact of disk I/O on overall efficiency.
B+TreeB-treeQuery Performance
0 likes · 17 min read

This article explains the fundamentals of database indexing, detailing B‑Tree structures, the differences between clustered and non‑clustered indexes, their storage formats, and how they affect query, insert, and delete operations, including the concept of index covering.
B+TreeIndex CoveringNon-clustered Index
0 likes · 15 min read

This article investigates why MySQL recommends auto_increment primary keys over UUID or snowflake IDs by creating three tables with different key strategies, running insertion benchmarks using Spring Boot, and analyzing index structures, performance results, and the trade‑offs of each approach.
MySQLPrimary KeyUUID
0 likes · 10 min read

This article explains why B+ trees are the dominant data structure for MySQL indexes, compares them with hash tables, linked lists, and skip lists, and details page splits, merges, and how index values map to row records, helping readers master high‑frequency interview questions.
B+TreeMySQLSQL
0 likes · 14 min read


ITPUBAug 22, 2019 · Databases
This article explains how InnoDB’s 16 KB pages, row size assumptions, and B+‑tree node capacities combine to allow roughly 20 million rows per tree, demonstrates how to calculate tree height from page metadata, and shows why MySQL chooses B+‑trees for primary‑key indexes.
B+TreeInnoDBMySQL
0 likes · 11 min read

This article explains how library indexing inspires database indexing, introduces binary search trees, AVL trees, B‑Tree and B+Tree structures, and details InnoDB and MyISAM storage mechanisms, page organization, clustered versus non‑clustered indexes, and practical index‑optimization advice.
B+TreeB-TreeInnoDB
0 likes · 19 min read

This article explains how InnoDB’s B+‑tree index stores data, calculates the maximum number of rows a single tree can hold (around 20 million), shows how to determine the tree height from the tablespace file, and answers why MySQL uses B+ trees instead of other structures.
B+TreeInnoDBMySQL
0 likes · 10 min read

Database indexes, akin to a dictionary’s table of contents, dramatically speed up data retrieval, and this article explains what indexes are, how composite indexes work, the left‑most prefix rule, and the difference between clustered and non‑clustered indexes, using clear analogies and examples.
Composite IndexSQLclustered index
0 likes · 8 min read

This article explains how database indexes are implemented using B+ trees, describing their structure, advantages such as shallow depth and ordered leaf nodes, and how they reduce disk I/O, illustrating calculations of node capacity and emphasizing the importance of index size for performance.
B+TreeInnoDBdata structures
0 likes · 6 min read

This article explains the evolution from binary search trees to AVL and red‑black trees, introduces B and B+ trees, compares their properties and performance, and clarifies why B+ trees are the preferred indexing structure for MySQL databases.
B+TreeMySQLTree Algorithms
0 likes · 14 min read

This article examines the often‑overlooked downsides of database indexes, detailing the overhead of maintenance, hot‑block contention, lookup and update costs, the impact of index creation, logical and physical invalidation, and practical guidelines for choosing and monitoring indexes to avoid performance degradation.
database-indexindex invalidationindex overhead
0 likes · 11 min read

This article explains the GeoHash algorithm, how it converts latitude‑longitude coordinates into hierarchical string codes, the precision trade‑offs of different code lengths, the binary encoding process, the use of space‑filling curves, and practical considerations when applying GeoHash for nearby point‑of‑interest searches.
GeoHashGeospatialdatabase-index
0 likes · 9 min read
