This article explains MyBatis streaming queries using the Cursor interface, demonstrates how to define a Mapper that returns a Cursor, shows common pitfalls with closed connections, and provides three solutions—manual SqlSession handling, TransactionTemplate, and @Transactional—to keep the database connection open while iterating results.
This article introduces the importance of database connections, compares three popular Java connection pools (DBCP, C3P0, Druid) with code examples, runs performance tests inserting 100 k and 1 M rows on MySQL, Oracle and PostgreSQL, and concludes with recommendations on the most stable pool and optimal databases.
This article explains the special nature of NULL in relational databases, demonstrates the correct use of IS NULL versus = NULL, explores three-valued logic, and clarifies how NULL interacts with NOT, NOT IN, and empty strings across different database systems.
This article introduces Redis string data type and explains five basic commands (SET, GET, INCR, DECR, APPEND) as well as five commonly used commands (STRLEN, GETRANGE, SETRANGE, MSET, MGET), providing syntax, behavior, and example outputs.
This article explains how LEFT JOIN works in SQL, illustrates why duplicate rows can appear when joining tables, and provides practical techniques such as DISTINCT, GROUP BY, MAX, and IN subqueries to ensure a one‑to‑one result set while also covering RIGHT JOIN, INNER JOIN, update and delete operations, and the concept of Cartesian products.
This article provides a concise tutorial on essential Redis commands, covering basic operations, database selection, key management, expiration settings, and practical examples demonstrated through the Redis CLI, including how to list keys with patterns, check existence, and clear databases.
This article examines the explosive growth of IoT data, outlines the unique challenges of storing and querying massive time‑series datasets, reviews the evolution of time‑series databases, and explains how Alibaba Cloud's Lindorm TSDB leverages cloud‑native, multi‑model architecture to deliver high‑throughput, low‑cost, and scalable solutions for IoT, industrial and monitoring workloads.
This article explains what NoSQL means, contrasts it with SQL and relational databases, walks through schema design, primary keys, indexing, and then demonstrates how to implement a lightweight key‑value NoSQL store in pure Python with full command parsing and handling.
The article explains why adding conditions after a LEFT JOIN using AND does not filter rows from the left table, contrasts ON and WHERE clauses, and demonstrates the behavior with SQL examples and step‑by‑step illustrations of intermediate result tables.
A developer encountered a duplicate‑key error caused by an unexpected primary‑key update that overflowed the signed INT limit, exposing a hidden MySQL auto‑increment bug that persisted across normal inserts until a later DDL operation triggered failures, a problem present in Percona 5.6/5.7 but fixed in MySQL 8.0.
This article explains why large data sets must be stored on disk, compares disk and memory access characteristics, introduces the B+ tree as an efficient disk‑based index, and details its structure, search process, and dynamic adjustments for insertions and deletions.
This article explains common database high‑availability designs—including master‑slave, master‑master, and automatic failover architectures—their topologies, advantages, disadvantages, and practical considerations such as replication lag, manual intervention, and data consistency challenges.
Yuanfudao's data middle platform leverages the MPP database DorisDB to create a unified OLAP system that supports both real‑time and batch analytics, handling millions of queries daily with sub‑second latency while meeting complex business requirements across its education services.
This article documents a Python web service performance optimization case, detailing the initial bottlenecks, architectural redesign with caching and message queues, load‑testing methodology, Linux TCP time‑wait tuning, and the final results achieving 50k QPS with sub‑70 ms latency.
This article explains what NoSQL means, contrasts it with relational databases, and guides readers through building a simple Python key‑value NoSQL database, covering schema basics, querying, indexing, command parsing, and the trade‑offs between flexibility and query performance.
This article explains why learning MySQL is important, outlines the focus of a multi‑part series covering MySQL's underlying principles, installation on macOS, basic architecture components, InnoDB features, and what readers can gain such as SQL optimization and cluster building skills.
This article explains how to achieve database read‑write separation in a Spring application by using AbstractRoutingDataSource for routing, ThreadLocal for context, AOP interceptors for automatic switching, and provides a complete example with configuration, service code, and test cases.
This article explains why locks are essential in MySQL, categorizes lock types, defines transactions and ACID properties, compares isolation levels, describes how InnoDB implements transactions and resolves deadlocks, and introduces MVCC as a concurrency control technique.
This article outlines Redis's key advantages such as high performance and flexible data types, discusses its limitations like lack of relational features, describes ideal in‑memory use cases, compares it with Memcached, and provides detailed instructions for shutting down, configuring, and connecting to Redis using command‑line and graphical clients.
This article presents a complete logistics management system case study, covering business requirements, technology stack selection, front‑end and back‑end architecture, core modules such as bill management, receipt handling, vehicle dispatch, and includes full Java code snippets for key operations.
This article explains how LEFT JOIN works in SQL, clarifying that the ON clause only matches rows without filtering left‑table records while the WHERE clause filters the joined result, and demonstrates the differences with multiple code examples and table illustrations.
Tencent's TDSQL team introduces a groundbreaking theory that precisely classifies an infinite number of transaction‑related data anomalies, explains the underlying conflict‑ring model, and provides a Linux‑based open‑source tool with step‑by‑step instructions for practical anomaly detection.
This article explains the three‑layer architecture of MySQL, how an execution plan is generated and interpreted, and provides a detailed walkthrough of the columns displayed by the EXPLAIN statement to help developers optimize their SQL queries.
This comprehensive guide covers fundamental SQL clauses, aggregate functions, joins, set operators, Oracle-specific objects, essential DML/DDL commands, and a curated collection of 50 interview questions with detailed query examples and performance tips for database professionals.
This guide explains why MySQL reports "too many connections", shows how to check the current max_connections setting, compares default limits across MySQL versions, and provides four practical methods—including editing my.cnf, using SQL commands, modifying source code, and tweaking mysqld_safe—to increase the connection limit safely.
This article demonstrates how to perform remote physical backups of MySQL databases using the mysqlbackup and Percona XtraBackup tools, covering test objectives, environment setup, step‑by‑step streaming commands, restoration procedures, replication configuration, and a comparison of the two approaches.
This article outlines ten practical strategies—including avoiding chat tools for SQL, compressing statements, using SELECT COUNT, adding LIMIT, updating audit fields, logical deletion, table backups, temporary tables, fully qualified names, and careful column changes—to help developers, testers, and DBAs prevent costly database errors in production environments.
This article provides a comprehensive overview of database system design, covering the historical evolution, classification of relational and NoSQL databases, common architectures, consistency models, indexing techniques, storage formats, compression, and practical considerations for selecting and optimizing database solutions.
This guide provides a thorough walkthrough of SQL fundamentals, including SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, aggregate functions, joins, set operations, Oracle-specific objects, DDL/DML commands, and a collection of classic interview questions with complete query examples and performance tips.
This article explains MySQL's table, row, and page lock levels, describes next‑key, gap, and record locks, illustrates common deadlock scenarios with detailed SQL examples, and outlines InnoDB's lock acquisition and deadlock‑prevention strategies to help developers avoid concurrency issues.
The article presents a comprehensive guide to Python's built‑in functions, standard libraries, and third‑party packages across file I/O, web scraping, databases, data cleaning, statistical analysis, machine learning, visualization, and more, rating each with stars and offering a free e‑book collection for readers.
This article explains three typical situations where Spring's @Transactional annotation fails—non‑public methods, internal method calls, and caught exceptions—detailing the proxy‑based AOP mechanism, source code examples, and how to diagnose and resolve each issue.
This article explains the differences between DROP, DELETE, and TRUNCATE, recommends appropriate MySQL data types for monetary values, compares MyISAM and InnoDB storage engines, outlines transaction ACID properties, describes views, stored procedures versus triggers, indexes, UNION vs UNION ALL, and lists the main SQL language categories.
This article explains NUMA architecture, how its memory allocation policies can cause swap‑related performance issues for MySQL, provides step‑by‑step methods to disable NUMA at BIOS, kernel or MySQL levels, and discusses the innodb_numa_interleave parameter and best‑practice recommendations.
This comprehensive tutorial walks you through installing MongoDB on Windows, configuring data and log directories, setting environment variables, creating and managing databases, collections, indexes, aggregation pipelines, backup and restore procedures, monitoring tools, advanced query operators, user management, and using a visual tool like Navicat for MongoDB.
This article introduces Seata's background, explains the causes of data inconsistency in micro‑service architectures, details Seata's three core roles, transaction flow, isolation levels, common pitfalls with code examples, and provides guidance on high‑availability deployment, multi‑node setups, disaster recovery, and hands‑on demos.
This comprehensive guide explains what MyBatis is, its advantages and drawbacks, usage scenarios, differences from Hibernate, key syntax like #{ } vs ${ }, mapping techniques, pagination, caching, lazy loading, batch operations, dynamic SQL, and how to configure mappers and plugins in Java projects.
EasyJDBC is a Java library built on Spring's JdbcTemplate that adds JPA‑style annotations, dynamic entity mapping, chainable query APIs, and broad database support, with Maven coordinates for both core and Spring Boot starter packages and a detailed changelog for version 2.1.0.
This article explains how the testWhileIdle and keepAlive parameters of the Druid connection pool differ in their idle‑connection validation behavior, demonstrates the effects with a Java test program, and recommends using keepAlive for reliable Dble connectivity.
This article explains MongoDB's document-oriented data model, shows how to create an admin user, configure the mongod service on Linux, and provides detailed command‑line examples for backing up and restoring databases and collections using mongodump, mongorestore, mongoexport, and mongoimport.
This article compiles 40 common Redis interview questions covering fundamentals, data types, persistence, clustering, performance tuning, memory optimization, and practical usage patterns, providing concise answers that help candidates confidently tackle Redis topics and secure job offers during the competitive hiring season.
This article explains the concept of API idempotency, presents common scenarios that cause duplicate data, and details eight practical solutions—including pre‑select before insert, pessimistic and optimistic locking, unique indexes, anti‑duplicate tables, state‑machine checks, distributed locks, and token‑based approaches—along with their implementation steps and caveats.
This article provides a comprehensive, step‑by‑step guide to SQLite, covering table creation, deletion, data manipulation, queries, index management, view creation, trigger usage, and transaction control, complete with code examples and illustrative screenshots for developers of all levels.
The article explains why both equality and inequality comparisons with a NULL value in MySQL return no rows, illustrating common interview misconceptions and emphasizing the need to grasp NULL semantics for reliable database programming.
An in‑depth guide to MySQL master‑slave replication covering replication modes, deployment prerequisites, core mechanisms, common issues with solutions such as semi‑synchronous and parallel replication, configuration commands, monitoring techniques, and error handling to ensure high availability and performance.
The article analyzes three typical situations—non‑public methods, internal self‑calls, and caught exceptions—that cause Spring's @Transactional annotation to be ineffective, explains the underlying proxy and AOP mechanisms, and provides Java code samples and test cases to illustrate each case.
This article explains why Redis can report out‑of‑memory (OOM) errors even with small datasets, describes the two types of OOM, outlines memory‑consumption categories, presents systematic troubleshooting methods, and provides a collection of useful Redis commands for diagnosing and resolving memory‑related issues.
This tutorial demonstrates how to use MySQL Shell's three components—MySQL, MySQL X, and SHELL—to connect to a sample WORLD database, perform CRUD operations, execute transactions, and run advanced queries with code examples for each component.
This article explains why using the KEYS command on large Redis keyspaces can crash the service and demonstrates how the SCAN command provides a non‑blocking, incremental way to iterate over massive data safely.
This article provides a thorough overview of MySQL fundamentals—including schema design, storage engines, transaction mechanisms, logging, indexing, and performance tuning—along with practical SQL examples, code snippets, and optimization strategies for both hardware and database configuration.
This article provides a thorough overview of relational database fundamentals, covering basic concepts, SQL syntax, DML and DDL commands, functions, joins, unions, grouping, ordering, transactions, permission management, stored procedures, cursors, and triggers, complete with illustrative code examples.
This article explores three real‑world cases of TCP anomalies that affect database connectivity—ineffective RST packets, unexpected port exhaustion despite tcp_tw_reuse, and ghost connections caused by a full accept queue—explaining the underlying Linux kernel mechanics and offering practical mitigation strategies.
Redis, traditionally a single‑threaded in‑memory cache, introduces Lazy Free in version 4.0 and multi‑threaded I/O in version 6.0 to mitigate blocking during large key deletions and improve performance, with detailed explanations of event handling, code implementations, limitations, and comparisons to Tair’s threading model.
The article explains how ThinkPHP6 returns query results as a Collection object, introduces its useful methods such as toArray and isEmpty, and provides a PHP code example demonstrating how to check for empty results and convert the collection to an array.
This article walks through a systematic, onion‑layer approach to diagnosing and fixing SQL Server performance problems, focusing on hardware, system scale, internal database factors, software environment, business architecture, and code design, with a real‑world hospital case study and concrete remediation steps.
The article shares eight authentic production incident cases—from frequent JVM Full GC and memory leaks to cache avalanches, DNS hijacking, and database deadlocks—detailing their root causes, diagnostic steps, code snippets, and practical remediation strategies for engineers facing similar challenges.
This article explains how JDBC evolves into MyBatis, detailing the seven-step JDBC process, the motivations for encapsulating JDBC, the design of MyBatis components such as SqlSession, Executor, and StatementHandler, and various optimization strategies including connection pooling, SQL management, dynamic parameters, and caching.
This article presents a collection of practical PHP code snippets covering directory traversal, a MySQLi singleton class, fetching web content, extracting file extensions, converting strings, header redirects, regex cleaning, array filtering, date calculations, file locking, and date formatting.
This article explains the concept of MySQL index cardinality, how it influences index selectivity, factors that affect cardinality values, methods to view them, and demonstrates with practical examples how different data distributions and query patterns impact the optimizer's execution plans.
This article outlines ten practical techniques—from avoiding chat tools for SQL transmission to using soft deletes, backup tables, and proper field management—to help developers, testers, and DBAs safeguard production databases against accidental data loss and costly errors.
This article provides a step‑by‑step guide for using MyBatis‑Plus with Spring Boot, covering database and table creation, entity and mapper definitions, CRUD examples, automatic field filling, optimistic and logical deletion, performance plugins, query wrappers, and a code generator for rapid development.
This article walks through what Redis is, its data types, caching usage with Spring Boot, performance advantages, eviction policies, persistence mechanisms, master‑slave replication, Sentinel high‑availability, and practical interview‑style Q&A, providing a comprehensive guide for backend developers.
This article explains various situations—such as column-to-column comparisons, NULL values, NOT conditions, wildcard LIKE patterns, functions on indexed columns, low‑cardinality leading columns in composite indexes, implicit datatype conversions, arithmetic predicates, virtual and invisible indexes—that can cause an otherwise usable index to be bypassed by the optimizer, leading to full‑table scans.
This article explains MySQL's three lock levels, the algorithms behind next‑key, gap, and record locks, illustrates common deadlock scenarios with real SQL examples, and outlines InnoDB's lock‑prevention strategies to help developers avoid and resolve deadlocks.
This article outlines core database architecture principles, compares four common deployment schemes (master‑slave, dual‑master, master‑slave with read/write separation, and hybrid), and presents practical consistency solutions for both primary‑replica and DB‑cache mismatches, followed by personal evolution insights.
This article explains MySQL index types—including ordinary, unique, primary, composite, and full‑text indexes—their creation syntax, optimal usage scenarios, situations where indexes should be avoided, cases that render indexes ineffective, and how to interpret EXPLAIN output types for query performance analysis.
This article evaluates several Java persistence solutions—JOOQ, MyBatis, Hibernate, JPA, and JDBC Template—by examining SQL encapsulation, DSL adaptability, cross‑database portability, security, and the specific drawbacks of JOOQ, ultimately concluding that JDBC Template best fits the project’s needs.
This article examines InfluxDB's in‑memory and TSI indexing, investigates queue buildup caused by asynchronous batch writes and high I/O, analyzes excessive SHR memory usage, and proposes configuration, retention policy, NUMA, and query optimizations to improve overall database performance.
This article provides a step‑by‑step tutorial on using MyBatis‑Plus with Spring Boot, covering database creation, table definition, Maven dependencies, entity and mapper setup, CRUD operations, automatic field filling, optimistic locking, pagination, logical deletion, performance monitoring, query wrappers, and code generation, all illustrated with complete code examples.
This article compiles 40 frequently asked Redis interview questions, covering its definition, data types, advantages, comparison with Memcached, persistence mechanisms, performance considerations, eviction policies, clustering, client libraries, security commands, usage scenarios, and best‑practice tips for high‑traffic production environments.
This article explains the concept of idempotency, why it is essential for HTTP interfaces, the impact on system design, and presents several practical implementation strategies—including unique primary keys, optimistic locking, anti‑repeat tokens, and downstream sequence numbers—accompanied by a complete Spring Boot example with Redis integration and test code.
This article explains what correlated subqueries are, why they challenge database engines, and how they can be transformed into ordinary SQL using decorrelation, push‑down rules, result reuse, and window‑function tricks, illustrated with TPCH examples and code snippets.
This guide explains how to identify MySQL max_connections issues, check the current limit, and increase it via configuration files, runtime commands, source code changes, or mysqld_safe adjustments, covering version‑specific defaults and maximum caps.
The article provides a comprehensive analysis of China’s Xinchuang industry in 2023, detailing its $104.3 billion market size, the domestic CPU ecosystem, operating system adoption, database and middleware players, and the rapidly growing information‑security sector driven by cloud and IoT technologies.
Learned Index Structures replace conventional B‑Tree and Bloom filter indexes with hierarchical machine‑learning models that predict key positions directly, reducing search overhead, but require careful model selection, training, and handling of updates, making them a promising yet still experimental alternative to traditional database indexing techniques.
This article explains the PDOStatement::fetch() method in PHP, detailing the available fetch style constants, their effects on result set formatting, and shows how to configure the statement to return only associative arrays for cleaner data handling.
This article walks through a systematic SQL optimization workflow—identifying slow queries, interpreting EXPLAIN output, using SHOW PROFILE and TRACE, applying concrete tuning measures, and dissecting ten representative MySQL cases ranging from index ordering to large‑page handling and complex query pitfalls.
This article explains MySQL's lock levels (table, page, row), the next‑key, gap, and record lock algorithms, illustrates common deadlock scenarios with detailed session examples, and discusses InnoDB's deadlock prevention strategies and practical solutions for avoiding lock conflicts.
This article introduces Redis, explains its advantages, describes Redis Cluster architecture, and provides a detailed, hands‑on tutorial for deploying a Redis Cluster on Kubernetes using StatefulSets, PersistentVolumes, services, and a sample hit‑counter application, complete with configuration files and verification commands.
This article explains how MySQL UNIQUE indexes affect data insertion and auto‑increment behavior, compares them with primary keys, and presents three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to prevent duplicate‑key failures, including deadlock considerations.
This article provides a detailed explanation of MySQL InnoDB's lock system, covering lock granularity, shared and exclusive locks, intention and auto‑increment locks, various row‑lock types, deadlock scenarios with examples, and the internal representation of locks in the engine.
This article demonstrates how to perform multi‑field searches in Laravel by chaining multiple where clauses with closure callbacks, showing example code that conditionally filters by username and hospital ID based on request parameters.
This article explains the different SQL join types—including equi‑join, natural join, inner join, self‑join, outer joins, and cross join—detailing their syntax, behavior, and practical usage with example queries and optimization tips.
This article introduces GoDB.js, a TypeScript‑based wrapper for IndexedDB that provides a concise, schema‑aware CRUD API, hides asynchronous connection handling, and offers batch operations, making client‑side data storage in modern web apps much easier.
This article explains how to manage database write and query operations in PHP using PDO, covering initialization, error handling, and providing complete code examples for inserting and selecting records.
This tutorial explains how to perform asynchronous MySQL operations in Python using aiomysql, covering basic connections, CRUD actions, safe parameter handling to prevent SQL injection, connection pooling, transaction management, integration with SQLAlchemy for ORM queries, and a custom reconnection wrapper for resilient database access.
This article explains how to diagnose and resolve MySQL slave replication failures that occur when XtraBackup’s --kill-long-queries-timeout and --kill-long-query-type options interrupt long‑running queries, detailing steps such as checking performance_schema, reviewing error logs, restarting replication, and adjusting backup parameters.
An in-depth case study describes how to dramatically improve the performance of a massive MES reporting query—reducing execution from over half an hour to seconds—by analyzing inefficient SQL, adding proper indexes, avoiding full table scans, redesigning with stored procedures, temporary tables, and selective use of NOLOCK.
This article explains why caching MySQL with Redis is essential, outlines the consistency challenges that arise, and presents four practical solutions—expiration, delete‑then‑repopulate, message‑queue updates, and binlog subscription—along with their pros, cons, and guidance for choosing the right approach in interviews.
This article provides a comprehensive overview of ClickHouse's table engines, explaining the purpose and characteristics of each engine series—including Log, Integration, Special, and the various MergeTree families—while offering detailed SQL examples and guidance on when to use each engine for different data workloads.
This article explains how Redis implements transactions, highlighting the lack of atomicity and isolation, the queuing mechanism, error handling, cancellation methods, and the use of WATCH for optimistic locking, accompanied by step‑by‑step command‑line examples.
This article presents a comprehensive guide to Redis monitoring and alerting, covering metric classification, threshold settings, client traffic collection, host resource usage, instance health checks, cluster failover diagnostics, and detailed explanations of Redis INFO sections with practical code examples.
This article introduces PostgreSQL as a powerful open‑source object‑relational database, outlines its key features and typical use cases, and provides a detailed, screenshot‑rich walkthrough for downloading, installing, and configuring PostgreSQL and pgAdmin on Windows.
This article examines several MySQL pagination techniques—including basic LIMIT, subquery‑based, ID‑range, and temporary‑table methods—provides detailed performance measurements on a multi‑million‑row table, and offers practical recommendations for fast and scalable data retrieval.
This article compiles 52 practical SQL performance‑tuning strategies—including indexing rules, query‑writing shortcuts, join optimizations, lock handling, temporary tables, storage‑engine choices, data‑type recommendations, backup tips, and useful code examples—to help developers dramatically improve query speed and overall database efficiency.
This article explains why a small table should drive a large table in SQL queries, demonstrates data preparation, compares IN and EXISTS performance when the small table is either the inner or outer query, and concludes with practical guidelines for choosing between IN and EXISTS.
This article presents a systematic benchmark of MySQL 5.7.22 and MySQL 8.0.15 using sysbench to measure TPS and QPS across read‑write, read‑only, and write‑only modes under two persistence configurations, analyzes the results, and provides a Bash script for reproducing the tests.
Testing shows that omitting single quotes around a string primary key in MySQL queries can cause a full table scan and increase execution time by about 100‑fold, while quoting the value enables index usage and dramatically improves performance, a difference that disappears when the column is an INT.
This article explains how to achieve database read‑write separation in high‑concurrency Java applications by configuring master‑slave data sources, implementing a routing data source, managing context with ThreadLocal, defining a custom @DataSourceSwitcher annotation, and applying AOP to dynamically switch between read and write databases.
This article explains the meaning of the Extra column in MySQL EXPLAIN output, covering how "Using index", "Using index condition", and "Using where" appear, when index coverage and index condition push‑down occur, and provides multiple practical examples with code.
