The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.
This article explains how MySQL COUNT() works under different storage engines, why counting rows becomes slow on large InnoDB tables, and presents practical methods such as using EXPLAIN rows, auxiliary count tables, batch processing, and transaction‑based updates to obtain approximate or exact row counts efficiently.
The article details Redis’s core low‑level data structures—Simple Dynamic Strings (SDS) with length‑aware headers, a custom doubly linked list for O(1) head/tail access, and a hash‑table dictionary employing MurmurHash, incremental rehashing, and packed headers to optimize memory and performance across versions.
This article explains Redis's internal storage mechanisms and presents seven practical optimization techniques—including key‑value compression, small‑collection encoding, object sharing, bitmap usage, hash structuring, memory‑fragment defragmentation, and 32‑bit deployment—to dramatically reduce memory consumption while increasing data capacity.
This article provides a detailed, English-language tutorial on preparing the network environment, creating necessary directories, configuring Redis instance files, launching six Redis nodes, and initializing a clustered Redis deployment across three servers using Linux command‑line tools.
The article investigates a MySQL 5.6 instance that consumed nearly all memory on a 32 GB test server, identifies misconfigurations, excessive MyISAM file handles, and severe memory fragmentation, and demonstrates how switching to tcmalloc reduced memory usage to a stable 5 GB.
The article analyzes Wanli Database’s rise to the leader quadrant in the CCID “14th Five‑Year Plan” key‑application database market report, detailing its technical advancement, security credentials, distributed architecture, industry collaborations, and open‑source initiatives that underpin its competitive edge.
RedKV, Xiaohongshu’s self‑developed distributed NoSQL key‑value store, combines a Redis‑compatible interface, RocksDB‑backed storage, and a shard‑managed multi‑cloud architecture that delivers three‑times higher write throughput, 1.5‑times read throughput, 40 % cost savings versus HBase, and sub‑5 ms P999 latency for petabyte‑scale workloads.
This weekly community newsletter summarizes the latest MySQL‑related articles, open‑source middleware updates for DBLE, DTLE and SQLE, bug fixes, feature developments, upcoming plans, and invites community feedback, providing a concise technical snapshot for database practitioners.
This article explains Redis high‑availability strategies, covering basic hash sharding, the advantages of consistent hashing, client‑side versus proxy‑based partitioning, master‑slave replication, and the Sentinel failover mechanism, with diagrams illustrating node addition, removal, and failover decision processes.
Redis is a single‑threaded, in‑memory data store offering rich structures, configurable expiration, multiple eviction policies, asynchronous master‑slave replication with Sentinel failover, durable RDB snapshots plus AOF logging, and native clustering that shards data across many masters for horizontal scalability and high availability.
This article analyzes MySQL replication anomalies caused by master failures or network interruptions that lead to incomplete transaction replay on slaves, demonstrates a reproducible experiment using network delay and iptables, and provides practical guidance for both recovering and permanently handling stalled slave IO threads.
This article explains MySQL's support for multi‑column partition tables, provides a stored‑procedure to create them, compares performance against single‑column partitions under various query conditions, discusses partition column order impact, and offers practical guidance for when and how to use multi‑column partitions.
This article compiles essential MySQL interview questions covering primary and candidate keys, date functions, trigger limits, index column counts, data copying techniques, core MySQL concepts, feature highlights, transaction ACID properties, heap versus clustered tables, numeric type differences, and objects creatable with CREATE statements.
This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.
This article explains the design and implementation of distributed locks, covering database pessimistic and optimistic locks, various Redis lock patterns, Redisson and RedLock, as well as Zookeeper's lock mechanism, and compares their advantages, drawbacks, and suitable use cases.
This article explains the XA specification, demonstrates MySQL XA commands, and shows how Sharding-JDBC integrates XA transactions using Atomikos, including required dependencies, configuration, annotations, and testing steps, while also providing practical code examples and deployment tips.
This article explains the concept of idempotency in distributed systems, why it is crucial for scenarios like payments and order submission, and presents practical solutions such as database unique keys, optimistic locking, the Post/Redirect/Get pattern, and anti‑repeat token mechanisms, complete with code examples.
This article compiles 66 MySQL interview questions covering fundamentals such as joins, data types, indexes, storage engines, transaction properties, locking mechanisms, replication, sharding, performance tuning, and high‑availability concepts for interview preparation.
This article examines MySQL's recommendation to use auto_increment primary keys instead of UUIDs or random keys by creating three comparable tables, inserting large data sets with Spring JdbcTemplate, and analyzing insertion speed, index structure impact, and the trade‑offs of each key strategy.
The article explains how Go's database/sql pool retains connections until Rows, Stmt, or Tx objects are explicitly closed or a timeout context is cancelled, demonstrates experiments showing blocked goroutines when resources aren't released, and offers practical guidelines—using QueryContext with timeouts, deferring Close/Cancel, and always committing or rolling back transactions—to prevent pool exhaustion and improve MySQL reliability.
This article explains how the SELECT ... FOR UPDATE statement in MySQL adds a pessimistic lock, describes when it results in a row lock versus a table lock based on index usage, provides verification SQL, example scenarios, and practical tips for transaction handling.
This guide details how to set up Redis Sentinel for high‑availability in a master‑slave architecture, covering configuration file creation, essential sentinel parameters, starting Redis and Sentinel instances, and verifying operation via log inspection.
This article compiles a comprehensive list of over a hundred Linux, networking, load‑balancing, database, caching, monitoring, container and cloud‑native interview questions, providing candidates with essential knowledge to ace system‑administration and DevOps job interviews.
This guide walks through preparing the environment, creating necessary directories, editing Redis configuration files for both master and slave nodes, and launching a three‑node Redis cluster with appropriate authentication and replication settings.
This article explains MySQL's internal execution process—including the connector, cache, parser, optimizer, and executor—describes privilege tables and verification, discusses query caching removal, outlines logical SQL execution order, and provides practical tips for ordering WHERE conditions to improve performance.
This article explains the fundamentals of database transactions, the ACID properties, Spring's @Transactional support, various propagation behaviors and isolation levels, and provides practical code examples for both declarative and programmatic transaction management in Java backend development.
This article provides a detailed guide on optimizing MySQL performance, covering SQL statement tuning, index usage, table design normalization, system configuration, hardware choices, and the internal architecture of MySQL to help developers write faster and more efficient queries.
This article outlines comprehensive guidelines for Alibaba Cloud Redis, covering readable and concise key naming, value size limits, appropriate data types, key lifecycle management, safe command usage, client connection pooling, encryption, eviction policies, and tools for synchronization, big‑key detection, and efficient deletion.
This article describes how a multi‑layered cache system—including a DAO‑level Redis cache, shared cache components, and global cache planning—was designed, implemented, and deployed to reduce database QPS by over 80%, cut response latency, and improve overall system stability in a high‑traffic e‑commerce platform.
This comprehensive guide explores MongoDB’s core features—including schema flexibility, high availability, sharding, storage engine internals, indexing, and performance tuning—while providing practical examples, configuration tips, and best‑practice recommendations for developers and architects seeking to efficiently deploy and operate MongoDB in production environments.
This article explains the concept of MySQL auto‑increment locks, describes different insertion methods and lock modes, analyzes how InnoDB allocates and manages auto‑increment IDs—including simple, bulk, and mixed inserts—and provides source‑code insights, practical experiments, and mitigation strategies for ID continuity issues.
This article explains the concept of a schema—from its Greek origins and psychological meaning to its role as metadata in databases and Kubernetes—detailing different database schema models, Kubernetes resource definitions, and how to extend and register custom schemas in Go.
This article explains how Redis RDB persistence works, detailing the save configuration parameters, snapshot generation methods, differences between SAVE and BGSAVE, storage locations, and the advantages and disadvantages of using RDB snapshots.
This guide explains how to identify when Redis is slow, measure baseline latency, monitor slow commands and latency, troubleshoot network, fork, huge pages, swap, AOF, expiration, and big keys, and provides a practical checklist of solutions.
This article compiles frequently asked questions and detailed solutions covering Apache Doris operational problems, data handling errors, and SQL query issues, providing step‑by‑step guidance, configuration tips, and command examples to help administrators troubleshoot and maintain a stable Doris cluster.
The article explains why Oracle can release MySQL under a dual GPLv2/commercial licensing model, how the GPL works, the impossibility of changing licenses for existing versions, and proposes creating a domestically controlled GPL‑v2 MySQL fork to avoid future licensing risks.
This article provides an in‑depth tutorial on Activiti 7 workflow management, covering workflow concepts, system architecture, BPMN modeling, environment setup, Maven dependencies, database configuration, deployment methods, process execution, task handling, and historical data retrieval for Java backend development.
This article details Meituan's journey from rapid database growth and operational bottlenecks to a multi‑year roadmap that combines platform‑level monitoring, rule‑based and AI‑enhanced root‑cause analysis, and automated remediation, ultimately delivering measurable improvements in alert accuracy, recall rates, and overall database reliability.
This article provides a comprehensive overview of MySQL's internal execution flow—from client connection handling, permission verification, caching, parsing, optimization, and execution—to the detailed SQL execution order, helping readers grasp how queries are processed and optimized within the database engine.
This tutorial explains why read/write splitting is useful, shows step‑by‑step how to build a MySQL master‑slave cluster with Docker, configure the master and slave instances, and then use Sharding‑JDBC to transparently route writes to the master and reads to the slave.
This article provides a comprehensive, step‑by‑step explanation of MySQL InnoDB's MVCC mechanism, covering its fundamentals, the role of Undo Log and Read View, and how it enables repeatable‑read and read‑committed isolation levels while preventing dirty reads and other concurrency anomalies.
The article walks through building a lightweight Java ORM framework from scratch, showing JDBC drawbacks, defining XML configuration and mapper files, implementing core components like SqlSessionFactory, SimpleExecutor, and reflection‑based parameter and result mapping, and demonstrating a working test that returns POJOs.
This article explains the complete MySQL query processing pipeline, covering the connector, authentication, privilege checks, caching, parser, optimizer, executor, and the detailed SQL execution order with examples of each stage, helping readers understand and optimize their SQL statements.
This article explains InnoDB’s role as a MySQL storage engine, how it reads and writes data using 16 KB pages, the mechanics of row formats—especially the dynamic format—and why varchar columns are limited to 16383 characters, illustrated with SQL examples and diagrams.
This guide walks beginners through downloading, installing, and configuring MySQL 8.0.28 on Windows and Linux, covering environment variables, service management, password recovery, user and role creation, and JDBC connection setup with Maven, providing detailed commands, code snippets, and troubleshooting tips for a complete setup.
This article explains how to replace raw PyMySQL usage with SQLAlchemy ORM in Python, covering environment setup, database connection initialization, MVC‑style project structure, model class definitions, field attributes, session handling with context managers, and practical code examples for saving and querying data.
This tutorial walks through building a PostgreSQL audit plugin for the SQLE platform using Go, covering project setup, minimal plugin code, adding a SELECT‑star prohibition rule, and implementing a configurable rule that limits table column counts, complete with deployment and testing instructions.
This article walks through MySQL's InnoDB storage engine, explaining how pages, locality, skip‑list concepts and B+‑tree structures work together to make indexes efficient, and shows how clustered, non‑clustered and covering indexes affect query performance and I/O behavior.
This article demonstrates how a functional, persistent key‑value store can be created with just two shell commands—one to append data and another to retrieve the latest entry—while explaining the underlying grep and sed techniques, performance trade‑offs, and basic indexing concepts.
This article outlines thirteen practical Redis performance‑optimization rules, covering command complexity, key expiration, data‑structure selection, persistence settings, hardware choices, clustering, and memory‑fragmentation monitoring to help developers maximize throughput and reduce latency in production environments.
This article demonstrates how to efficiently update up to 50,000 MySQL records in a Spring Boot‑MyBatis application by comparing simple loops, manual transaction control, multithreaded execution with CountDownLatch coordination, and union‑based batch updates, while analyzing performance and connection‑pool considerations.
Redis 7.0, now officially stable for production, introduces nearly 50 new commands, major features like Redis Functions, ACLv2, command introspection, and Sharded Pub/Sub, while enhancing memory, compute, network, and storage subsystems for better performance and efficiency.
This article explains why using COUNT(*) for simple existence queries is unnecessary and demonstrates how replacing it with SELECT 1 LIMIT 1 in SQL, together with a Java DAO method that checks for non‑null results, can significantly improve performance and reduce index complexity.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, covers creation and usage of full‑text indexes, demonstrates natural language, boolean, and query‑expansion modes, and shows how to delete full‑text indexes with practical SQL examples.
This article explains what SQL window functions are, shows their syntax, and provides step‑by‑step basic and advanced examples—including how to rank rows within groups and reset rankings when key columns change—complete with runnable SQL scripts and result screenshots.
This article explains why using SELECT 1 with LIMIT 1 is more efficient than COUNT(*) for existence checks in SQL, shows the typical count‑based approach, provides optimized SQL and Java examples, and discusses the performance benefits and reduced indexing requirements.
This article provides a comprehensive technical guide on Redis performance, covering single‑threaded characteristics, master‑slave replication, Sentinel high‑availability mechanisms, Redis Cluster architecture, configuration steps, code examples, and alternative middleware solutions for scaling and fault tolerance.
This article explains Redis 6's client‑side caching feature, describing its purpose, the two tracking modes (default and broadcast), a forward mode for RESP 2 clients, their advantages over traditional remote caching, and provides step‑by‑step command‑line examples with code snippets.
Bean Searcher is an open‑source Java library that dramatically simplifies backend list retrieval by offering a high‑performance search engine with built‑in multi‑table joins, pagination, sorting, field selection and aggregation, allowing developers to implement complex queries in just one line of code.
This article explains how SQL injection can exploit a Java Spring course‑selection system by concatenating user input into SQL statements, demonstrates various injection techniques such as always‑true conditions and UNION queries, and provides multiple defensive measures including prepared statements, type checking, permission restrictions, and request‑parameter filtering.
This article demonstrates how to dramatically speed up updating tens of thousands of records in a Spring‑MyBatis application by switching from automatic per‑operation commits to manual transaction control, applying multithreaded processing, tuning the Hikari connection pool, and using SQL UNION‑based batch updates.
This article provides a comprehensive tutorial on using ShardingSphere‑JDBC, configuring MySQL master‑slave replication with Docker, explaining binlog and redo‑log mechanisms, demonstrating two‑phase commit, and detailing Sharding‑JDBC read/write splitting and sharding configurations with code examples.
The February 2022 DB‑Engines ranking shows Snowflake’s rapid rise, a sharp decline for Oracle, MySQL and Microsoft SQL Server, stable positions for Elasticsearch, and detailed score trends across relational, key‑value, document, time‑series and graph databases, highlighting five popularity metrics used for the assessment.
In MySQL, an UPDATE that modifies no actual data returns an affected‑rows count of zero, making it unreliable for business logic that distinguishes between unchanged rows and non‑existent records, so developers should avoid using this count for critical decisions.
Redis 7.0 has been officially launched, bringing nearly 50 new commands, major enhancements like Redis Functions, ACLv2, command introspection, and Sharded Pub/Sub, plus extensive performance and stability improvements across memory, compute, networking, and storage subsystems.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
This article introduces Redis, explains how to install it on various platforms, demonstrates integrating Redis with Spring Boot using Spring Cache annotations, configures connection pools for Lettuce and Jedis, and provides custom Redis service implementations for flexible cache operations.
This article analyzes a MySQL InnoDB lock wait timeout error, explains how gap locks cause blocked inserts and phantom reads under repeatable‑read isolation, and offers optimization strategies such as primary‑key deletions, while concluding with a recruitment invitation for the Zero technology team.
The article walks through building a minimal SQLite‑style database in Go, covering memory‑disk page storage, B‑tree indexing, simple SQL parsing for INSERT and SELECT, row serialization, a pager that flushes pages, and a basic REPL, while highlighting current single‑page limits and future extensions.
This article provides a comprehensive guide to optimizing MySQL queries and indexes, covering best practices such as avoiding subqueries, using IN instead of OR, proper LIMIT usage, eliminating unnecessary ORDER BY, leveraging UNION ALL, batch inserts, selective column retrieval, distinguishing IN and EXISTS, improving GROUP BY, using numeric fields, optimizing JOINs, and applying index rules like left‑most prefix, avoiding functions on indexed columns, handling type conversions, covering indexes, and effective use of composite and prefix indexes.
This article outlines several frequent MySQL query pitfalls—including missing table aliases in multi‑table operations, unsupported FULL JOIN syntax, unexpected REPLACE INTO side effects, auto‑increment edge cases, data‑type mismatches, nondeterministic functions, and warning‑driven query behavior—providing examples and best‑practice recommendations to prevent data loss and performance issues.
This article introduces FaunaDB, explains how to create a free account and access key, and provides a step‑by‑step Python tutorial that demonstrates creating a collection and performing all CRUD operations with concise code examples.
Apache Doris (incubating) announced its 1.0 release on April 18, 2022, featuring a new vectorized execution engine, Hive external tables, Lateral View syntax, Z‑Order indexing, SeaTunnel integration, numerous performance optimizations, new bitmap functions, security enhancements, and detailed upgrade instructions for users.
This article explains the concept of streaming queries, introduces MyBatis's Cursor interface with its key methods, and provides three practical solutions—using SqlSessionFactory, TransactionTemplate, or @Transactional—to keep database connections open and avoid cursor‑closed errors in Spring MVC applications.
This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.
This article examines frequent SQL anti‑patterns—such as inefficient LIMIT usage, implicit type conversion, suboptimal UPDATE/DELETE joins, mixed ordering, unnecessary EXISTS clauses, condition push‑down failures, early limiting, and intermediate result handling—and provides rewritten queries that dramatically improve performance.
This guide walks through installing PostgreSQL 14 on a CentOS server via a shell script, covering repository setup, package installation, initialization, user and database creation, remote‑connection configuration, firewall adjustments, service verification, and connecting with Navicat.
This article recounts the end‑to‑end experience of planning, executing, and reviewing a database sharding project for a high‑traffic order platform, detailing pre‑implementation risk assessment, the detailed testing lifecycle, gray‑release strategies, performance validation, and post‑mortem lessons learned.
The article recounts how a simple misclick made the popular HTTPie repository private, instantly erasing over 54,000 GitHub stars, and extracts practical lessons on UI warnings, backup policies, and repository‑management best practices for developers and platform teams.
This article explains Redis's two expiration strategies—periodic active deletion and lazy deletion—detailing how the EXPIRE command works, the new NX/XX/GT/LT options introduced in version 7.0, the impact of unsynchronized clocks, and the underlying implementation code.
This article explains three common situations where Spring's @Transactional annotation becomes ineffective—non‑public methods, internal self‑calls, and swallowed exceptions—detailing the underlying proxy mechanism, code examples, test results, and practical workarounds to ensure proper transaction management.
A recent hardware failure left a physical server's disk read‑only, causing Redis to hang; this article explains the AOF mechanism, its potential pitfalls, log strategies, and practical steps to prevent and mitigate such issues in production environments.
This article explains the mathematical and computer‑science definition of idempotency, why it is essential for reliable distributed services, how to handle timeout scenarios, and presents eight concrete design patterns—including unique IDs, database constraints, token mechanisms, optimistic/pessimistic locks, and distributed locks—along with code examples and HTTP method analysis.
This article provides a comprehensive overview of ClickHouse, an open‑source column‑oriented MPP analytical database, covering its advantages and drawbacks, key features, typical use cases, data access flow, installation steps, core directories, indexes, data types, database and table engines, as well as detailed cluster architecture and deployment patterns.
The article explains why using IN and NOT IN in SQL can lead to poor performance, incorrect results with NULLs, and index misuse, and demonstrates how EXISTS, NOT EXISTS, and JOIN provide safer and faster alternatives for database queries.
This article investigates MySQL's recommendation against UUID primary keys by creating three tables—auto‑increment, UUID, and random (snowflake) keys—running insert and query benchmarks with SpringBoot/JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and security considerations of each key strategy.
This article explains how to replace parent‑id based tree storage with the nested set model, showing SQL queries to retrieve descendants, count them, detect leaf nodes, and perform insert, delete, and other operations, plus a JavaScript example for rebuilding the hierarchy.
This article provides a thorough guide to SQL fundamentals—including SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, aggregate functions, joins, set operations, and Oracle-specific objects—followed by a collection of common interview questions with detailed solutions, top‑N queries, and row‑to‑column transformations, while also containing promotional notes for related resources.
This article explains how to use the nested set (left‑right) model to efficiently query, count, and manipulate hierarchical department data in MySQL, providing SQL examples for retrieving descendants, ancestors, leaf detection, as well as insert, delete, and JavaScript tree‑building techniques.
This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.
Redis 7.0-rc3 introduces new cluster commands, configuration ordering changes, ACL restrictions, function load adjustments, performance optimizations, enhanced CLI behavior, expanded INFO fields, module API extensions, and a comprehensive set of bug fixes, providing developers with important updates and potential migration considerations.
This article explains MySQL high‑availability concepts, defines HA, RPO and RTO, outlines common HA architectures such as master‑slave+VIP, MHA and MGR+Proxy, and details AutoHome's evolution from simple master‑slave setups to a container‑based MGR solution with automated failover and monitoring platforms.
This article examines the critical role of databases in finance, defines disaster recovery and backup concepts, outlines industry requirements and regulations, analyzes market growth, and compares distributed database disaster‑recovery architectures such as single‑center, city‑level mutual backup, active‑active, and two‑site three‑center solutions.
This article presents a detailed experiment comparing graceful and kill‑9 shutdown methods on a MySQL 5.7 semi‑synchronous master‑slave cluster, analyzes their impact on data consistency, and outlines the official shutdown sequence with practical observations and recommendations for production environments.
This article walks through a real‑world TiDB/TiKV disk‑space alarm case, diagnosing why the GC worker got stuck, how TiCDC’s lingering changefeed caused outdated MVCC versions, and the step‑by‑step commands and monitoring tricks used to restore normal GC and reclaim storage.
Learn how to identify and fix slow MySQL queries by analyzing slow‑query logs, using EXPLAIN, profiling, and optimizer trace, understanding index types and extra flags, and applying practical case‑by‑case optimizations such as index tuning, query rewriting, pagination strategies, and handling large data sets.
This article explains the core components of database systems, including their client‑server architecture, query processing, storage engine modules, classification by storage media (memory vs. disk), and the differences between row‑oriented and column‑oriented data layouts, concluding with future topics to explore.
The article explains how ORM bridges Python objects and relational databases, compares options, and justifies choosing the lightweight Peewee library, then details its workflow—model generation, inserting, updating, deleting, and querying records—showcasing how this approach reduces manual data‑creation time from hours to seconds.
The article analyzes how comprehensive Western sanctions against Russia are prompting China to accelerate self‑reliance in foundational software, examines Oracle's pivotal role in Chinese enterprises, and discusses the strategic, technical, and market obstacles to achieving a successful "de‑IOE" transition.
