This article walks through MyBatis's complete query lifecycle—from configuration parsing, SqlSessionFactory creation, and mapper loading to the actual SELECT execution—while reproducing a pre‑3.4.5 bug caused by foreach variable leakage and presenting the official fix and upgrade recommendations.
This article analyzes why MySQL LIMIT pagination becomes slower on large tables, demonstrates the performance impact with real query timings, explains the underlying full‑table scan behavior, and presents three practical optimization strategies—including ordered primary‑key scans, sub‑queries, and join‑based approaches—to improve query speed.
This article explains how X‑Select’s Data Quality Platform (DQC) addresses common data quality problems in large‑scale data development by defining six quality dimensions, leveraging open‑source solutions such as Apache Griffin and Qualitis, and implementing rule definition, execution, alerting, and workflow interruption within a Spark‑based architecture.
This article provides a comprehensive guide on using Flink CDC with Debezium to capture MySQL changes, covering serialization, adding dependencies, configuring SQL client and Java/Scala APIs, creating source and sink tables, enabling checkpoints, and streaming data into a Hudi data lake.
This article analyzes the limitations of traditional BI multi‑dimensional analysis that relies on wide tables and complex SQL JOINs, introduces a new DQL language that redefines JOIN operations into three plus one patterns, and demonstrates how DQL simplifies data modeling, reduces errors, and enables truly self‑service analytics.
This guide presents thirty practical SQL Server optimization techniques—including proper indexing, avoiding full table scans, rewriting predicates, and minimizing temporary objects—to help developers write faster, more efficient queries and improve overall database performance.
This article explains why sharding tables improves read/write speed, then compares three pagination strategies—global query, no‑skip pagination, and two‑phase query—detailing their SQL rewrites, execution steps, performance trade‑offs, and practical examples.
This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
This guide presents 21 practical MySQL table design guidelines covering naming conventions, field types, primary key strategies, indexing, logical deletion, common columns, storage engines, character sets, time types, relationship modeling, sharding considerations, and SQL optimization tips for robust backend development.
This article introduces RobustDB, a client‑side read‑write splitting framework that replaces the problematic Atlas proxy by routing SQL statements to master or slave databases based on DML/DQL detection, using Spring, AspectJ, thread‑local context, and dynamic data‑source management.
This article provides a detailed tutorial on JetBrains DataGrip, covering installation, driver management, connection configuration, SQL editing shortcuts, data import/export, schema visualization, and various productivity features for efficiently working with relational databases.
This article examines the challenges of pagination after database sharding and presents three practical solutions—global query, jump‑page prohibition, and two‑step query—detailing their implementations, trade‑offs, and performance considerations for large‑scale order services.
This article explains why using LIMIT with large offsets slows MySQL queries, analyzes the execution flow, and presents four practical optimization techniques—including subqueries, INNER JOIN, bookmark (tag‑record) method, and BETWEEN range scans—backed by real‑world performance data and code examples.
This article breaks down the step-by-step SQL execution sequence—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, to LIMIT—illustrating each phase with examples, diagrams, and code snippets to help you understand how queries are processed and optimized.
This article explains how MyBatis‑Plus maps Mapper interface methods to executable SQL statements, detailing the auto‑configuration of SqlSessionFactory, XML parsing, mapper registration, SQL injection, and the dynamic proxy mechanism used at runtime.
This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, UPSERT syntax, column‑renaming support, and other API changes, while comparing SQLite's capabilities to other major database systems.
This article explains why using OFFSET and LIMIT for pagination becomes inefficient as data grows, leading to costly full table scans, and demonstrates a high‑performance cursor‑based pagination alternative that leverages indexed primary keys to retrieve pages quickly and reliably.
This article explains why thoughtful database design is critical for scalable applications and outlines ten best‑practice guidelines—including stakeholder input, choosing the right DB type, consistent naming, normalization, documentation, security, scalability, stored procedures, modeling effort, and thorough testing—to help developers build robust, high‑performance data stores.
This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.
This article explains the architecture of Apache Doris, introduces Apache Hudi as a data‑lake format, compares Lambda and Kappa approaches, and details the design, implementation steps, and future roadmap for querying Hudi tables directly from Doris.
StarRocks is a high‑performance MPP database whose simplified FE/BE architecture, fully vectorized engine, and CBO optimizer enable fast multi‑table joins, while its partition‑bucket‑tablet storage model supports real‑time metric services and dashboard migrations, accompanied by practical troubleshooting guidance and upcoming enhancements.
This article explains the standard SQL query execution sequence—including FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with examples, code snippets, and diagrams to clarify how data is retrieved and processed.
The article explains the logical execution order of a SQL query—starting with FROM/JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—using diagrams, code snippets, and practical examples to illustrate each step.
This guide explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, how to delete the extra rows while keeping the earliest entry, and how to handle duplicate detection across multiple columns with correct query patterns and common pitfalls.
This article describes how AutoHome tackled the complexity of managing multiple relational, NoSQL, and Hive data stores by adopting openLooKeng for unified, cross‑source SQL queries, outlines its key features such as ANSI‑SQL support, diverse connectors, and query optimizations, and details the custom enhancements made to the Apache Kylin connector to better serve their commercial data analysis workloads.
This article demonstrates how to mask sensitive data such as phone numbers and ID cards using SQL functions, introduces Java masking libraries, and provides a complete MyBatis-Mate Sensitive Jackson example with configuration, custom strategies, entity annotations, controller endpoints, and sample responses.
This article explains how to collect comprehensive MySQL performance metrics using only native SHOW commands, covering connections, buffer pool, locks, SQL statements, throughput, server variables, and slow‑query analysis, while also offering practical tips for interpreting and optimizing the results.
This article introduces Flink Table Store v0.2, explains its four primary application scenarios—offline warehouse acceleration, partial update, pre‑aggregation rollup, and real‑time warehouse enhancement—details the core lake‑storage architecture, bucket management, append‑only mode, and outlines the project’s future roadmap and trade‑off considerations.
SQLite 3.39.3, the latest version of the widely used embedded SQL database engine, introduces RIGHT and FULL OUTER JOIN support, new binary comparison operators IS NOT DISTINCT FROM and IS DISTINCT FROM, additional APIs such as sqlite3_db_name, enhanced file‑name handling, HAVING clause for aggregates, and overall CPU optimizations reducing cycles by about 2.3%.
This article demonstrates how Spring's @Transactional annotation rolls back only for RuntimeException subclasses, explains why checked exceptions like Exception do not trigger rollback by default, and shows how to configure rollbackFor to handle custom exceptions, illustrated with SQL updates and Java code examples.
This article compares Fluent MyBatis, native MyBatis, and MyBatis Plus by implementing a student score aggregation query, showcasing code examples, generator settings, and a detailed feature matrix to help developers choose the most efficient ORM for complex SQL construction.
This article walks through installing and using the golang-migrate CLI to manage database schema changes, covering supported drivers, creating migration files, applying and rolling back migrations in PostgreSQL and MySQL, handling errors, and advanced scenarios such as multiple sequential migrations.
This comprehensive guide explains MyBatis fundamentals, compares it with Hibernate, details its lifecycle, configuration, dynamic SQL, caching, lazy loading, batch operations, pagination, and plugin development, providing code examples and best‑practice recommendations for Java backend developers.
Despite the rise of newer data tools, SQL continues to dominate the developer landscape in 2022, offering universal applicability, high demand, strong career prospects, and a rich history that makes it an essential skill for data analysts, developers, and high‑paying tech roles.
This article presents OpenMLDB, an open‑source machine learning database that unifies offline and online feature computation with millisecond‑level latency, outlines its development history, architecture, recent 0.6.0 enhancements, ecosystem integrations, and multiple real‑world deployment case studies across finance, banking, research, and marketing domains.
This article investigates why MySQL treats strings with trailing spaces as equal during "=" comparisons, explains the underlying PAD SPACE collation rule, and demonstrates how to perform exact matches using LIKE and BINARY operators with practical examples and code snippets.
The article explains why MyBatis batch inserts using a foreach-generated single INSERT statement can cause severe CPU and latency issues, and demonstrates how to achieve fast bulk insertion by limiting batch size or switching to ExecutorType.BATCH with code examples.
When loading massive amounts of data into PostgreSQL, disabling autocommit, postponing index and foreign‑key creation, adjusting memory settings, using COPY or pg_bulkload, and fine‑tuning WAL and trigger settings can dramatically improve import speed and overall performance.
The gpdb‑faiss‑vector plugin embeds Facebook’s Faiss library into the Dolphin (Greenplum‑compatible) engine, exposing SQL functions for distributed, high‑performance approximate nearest‑neighbor vector retrieval with caching, parallel search, configurable indexes, and sub‑millisecond latency, enabling scalable recommendation and advertising workloads.
This article introduces the mysqlreplicate utility, explains how to run it with common command‑line options, and provides a step‑by‑step source‑code analysis that checks server‑id and UUID uniqueness, InnoDB compatibility, storage‑engine consistency, binary‑log settings, and finally creates and starts the replication process.
This article examines unexpected bugs caused by PageHelper in a Java/MyBatis project—such as duplicate registrations, limited dropdown results, and password‑reset errors—traces them to ThreadLocal misuse, explains the library’s internal pagination flow, and offers practical fixes to avoid these pitfalls.
This article provides a comprehensive collection of SQL Server commands and techniques, covering database creation, table manipulation, queries, indexing, backup, replication, and advanced operations, offering practical examples and code snippets for developers and database administrators.
This article explains how to collect comprehensive MySQL performance metrics—including connections, buffer cache, locks, statement counts, throughput, server variables, and slow‑query analysis—using only MySQL's native SHOW commands, providing a fast, low‑overhead monitoring solution.
This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article benchmarks ClickHouse, Oracle, and the open‑source esProc SPL on the TPC‑H suite, showing ClickHouse excels at simple scans, Oracle handles many complex queries, while SPL consistently outperforms both in speed and code simplicity across a range of workloads.
This guide walks you through ten essential advanced SQL techniques—including CTEs, recursive queries, temporary functions, CASE pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—providing clear explanations and practical code examples to boost your interview performance.
This article presents five practical rules—return only needed columns, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers craft efficient SQL statements across major relational databases.
Time management in programming spans human concepts of time, language-specific handling of zones and timestamps, 32‑bit overflow risks, sync versus async processing, log timestamping, business‑level period calculations, and common pitfalls, emphasizing that mastering these nuances prevents bugs, improves performance, and enables reliable analytics.
This article explains Doris's fundamental concepts such as rows, columns, tablets, and partitions, provides guidelines for column definition, partitioning and bucketing strategies, and details table creation syntax and property settings for optimal big‑data storage and query performance.
The article explains how to query sales statistics for multiple business units and their associated products in MySQL, compares several implementation strategies such as looping, OR‑concatenation, mixed filtering, and finally adopts the SQL‑92 row‑by‑row comparison technique to achieve a single‑query solution that respects indexing and corporate coding standards.
This article provides an extensive collection of SQL Server commands and techniques covering basic database creation, table manipulation, advanced queries, backup and maintenance, as well as detailed steps for setting up linked servers, snapshot replication, and synchronization procedures between servers.
This tutorial demonstrates how to use OpenMLDB together with OneFlow to build an end‑to‑end machine‑learning pipeline for predicting high‑potential JD users' purchase intent, covering environment setup, data loading, SQL table creation, offline feature extraction, DeepFM model training, model serving, online feature extraction, deployment, and real‑time inference.
CeresDB, a distributed high‑availability time‑series database originally built at Ant Group, is now open‑sourced with version 0.2.0, offering high‑throughput writes, multi‑dimensional queries, SQL support, compatibility with Prometheus and OpenTSDB, and a range of features targeting both monitoring and analytical workloads.
This article demonstrates how to mask sensitive data such as phone numbers, ID cards, and emails in MySQL and Java applications by combining SQL string functions with the MyBatis‑Mate Sensitive Jackson plugin, providing complete configuration, custom strategies, and runnable Spring Boot examples.
This article explains how to identify and resolve performance bottlenecks in MySQL by locating slow queries, analyzing execution plans with EXPLAIN, focusing on type, rows and extra information, using profiling and trace tools, and applying practical optimization techniques illustrated through multiple real‑world cases.
When executing a Spark SQL query with dozens of UNION subqueries that each contain JOIN operations on Spark 3.1.2, the job fails because the total serialized result size of the tasks exceeds the driver’s maxResultSize limit, and the issue can be resolved by reducing the initial partition number used by Adaptive Query Execution.
MongoDB is evolving from a purely operational document store to a hybrid system that embeds native analytics, cloud‑native features, and SQL access, aiming to boost developer productivity, support real‑time insights, and complement rather than replace traditional data warehouses.
This article walks through a real MySQL deadlock scenario, explaining the four necessary deadlock conditions, illustrating how Next‑Key locks on non‑unique indexes cause gap locks, and presenting step‑by‑step analysis, experimental verification, and practical recommendations to avoid such deadlocks in production systems.
This article presents ten crucial intermediate‑to‑advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—to help data professionals ace interview questions and write cleaner, more powerful queries.
This article provides a comprehensive tutorial on setting up Flink 1.11 with Iceberg 0.11.1, creating Hive catalogs, building databases and tables, inserting data, and exploring Iceberg components, file structures, partitioned tables, execution plans, and programmatic access via Scala.
This guide explains how to use Percona Toolkit's pt-archiver for MySQL data archiving, covering installation, basic and bulk commands, underlying two‑phase‑commit logic, performance trade‑offs of various parameters, and best‑practice tips for safe, efficient archiving and deletion.
This article explains the differences between row-based and column-based storage in databases, compares their advantages and disadvantages, presents experimental results using openGauss including space usage and insert performance, and offers practical guidance on selecting the appropriate storage model for various workloads.
This article recounts NetEase Game's evolution of its real‑time computation platform Streamfly, detailing the transition from a client‑side Flink SQL solution (StreamflySQL v1) to a server‑side architecture using SQL Gateway (StreamflySQL v2), the challenges faced, and future work.
This article explains the different join types supported by Apache Flink—including regular, interval, temporal, and lookup joins—provides SQL examples, details how the Flink optimizer transforms logical plans into efficient physical plans, and describes the underlying code generation and execution mechanisms.
This article explains Apache Doris’s architecture, introduces the Hudi data‑lake format, compares Lambda and Kappa approaches, and details the design and implementation of Doris’s Hudi external table support, including practical steps, code examples, and future roadmap.
The article examines MongoDB’s evolution toward built‑in analytics, detailing new features like native search, time‑series support, change streams, Atlas analytics nodes, and the upcoming Atlas SQL interface, while arguing that these capabilities aim to empower developers rather than replace dedicated data‑warehouse solutions.
This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, upsert support, column renaming, and related API changes, while highlighting limitations and compatibility notes compared with other major databases.
The article explains SQLite’s 2018 enhancements—including boolean literals, window functions, FILTER clause, UPSERT syntax, and column‑renaming—detailing how these features work, their syntax, limitations, and providing code examples for developers seeking to use SQLite in production environments.
This article explains what Flyway is, its migration types and common commands, shows how to integrate Flyway with Spring Boot via Maven dependencies and YAML configuration, provides sample versioned and repeatable migration scripts, and demonstrates the migration process with log output.
This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.
This article analyzes the coupon over‑issuance issue caused by concurrent requests, demonstrates how simple SQL updates can fail under load, and presents four solutions—including Java synchronized blocks, SQL row‑level locking, optimistic locking, and Redis‑based distributed locks with Redisson—to ensure atomic coupon allocation.
This article presents ten core SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, cumulative sums, and date‑time manipulation—each explained with clear descriptions and practical query examples to help candidates ace data‑analysis interviews.
The article explains the performance problems of using OFFSET and LIMIT for large‑scale database pagination, demonstrates their inefficiency with full‑table scans, and presents a faster cursor‑based alternative that relies on storing the last primary key and using LIMIT instead of OFFSET.
This guide explains how to use MySQL stored procedures, events (timers), and triggers—including their creation, variable handling, control structures, parameter passing, loops, scheduling options, and practical examples—to automate reporting, data monitoring, and business logic directly at the database layer.
This article explains the fundamental differences between row‑based and column‑based storage in databases, compares their read/write performance, presents experimental results on openGauss showing space and speed trade‑offs, and offers practical guidance on when to choose each storage model.
This article introduces Apache Doris, an MPP analytical database, and explains how it integrates with the Hudi data lake format, covering architectural features, design choices, implementation steps including external table creation and query processing, and outlines future enhancements for supporting MOR snapshots and incremental queries.
This article provides a detailed survey of database fundamentals, historical evolution from early relational systems to modern distributed and cloud‑native solutions, explains various architectural components and core technologies such as query processing, indexing, transaction management, and discusses business‑driven classifications, distributed designs, SMP versus MPP, and resource‑access models.
This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.
SQLite, often underestimated, introduced several powerful SQL enhancements in 2018—including boolean literals, window functions, FILTER clauses, UPSERT syntax, and column renaming—bringing its capabilities closer to major RDBMSes while retaining its lightweight, file‑based design.
The article reviews SQLite's 2018 enhancements—including boolean literals, window functions, FILTER clause, UPSERT syntax, and column‑renaming—explaining their syntax, behavior with NULLs, and practical examples, while also noting SQLite's reliability and deployment advantages despite lacking a network layer.
This article presents a comprehensive list of practical SQL query optimization techniques, emphasizing how to avoid full table scans by using proper indexing, rewriting WHERE clauses, avoiding certain operators, functions, and expressions, and providing example code snippets to improve database performance.
This article reviews the SQLite SQL enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering boolean literals, window functions, FILTER clauses, UPSERT syntax, column renaming, and related API changes with code examples and practical notes.
This article introduces the mybatis-plus-join library (v1.2.4) that adds join capabilities to MyBatis‑Plus, explains how to install it, shows usage with MPJLambdaWrapper and MPJQueryWrapper—including simple, paginated, and advanced queries—while providing complete code examples and generated SQL.
This article explains Spark SQL's physical execution plan, detailing each operator, how to interpret the plan, and practical optimization tips for data warehouse developers to improve SQL performance and resource utilization.
This article examines why coupon stock can become negative under concurrent requests and presents four practical solutions—including Java synchronized blocks, SQL conditional updates, Redis distributed locks, and Redisson client usage—to reliably prevent coupon over‑issuance in high‑traffic systems.
The guide recommends fine‑grained MySQL management in a MyBatis‑based order system—avoiding generic or dynamic SQL, explicitly using indexes, listing columns, limiting results, batching inserts, and checking row counts—while applying appropriate concurrency control (pessimistic FOR UPDATE or optimistic versioning) to improve performance, stability, and data integrity.
This article provides a detailed technical comparison between PostgreSQL and MySQL, covering architecture, stored procedures, transactions, performance, high availability, replication, permission control, SQL support, data types, fault tolerance, table organization, development interfaces, and community maintenance.
This article reviews the SQLite enhancements introduced between versions 3.22.0 and 3.26.0 in 2018, covering Boolean literals, window functions, the FILTER clause, UPSERT syntax, column renaming, and related API changes, while comparing them to other major database systems.
This article explains why executing a single DELETE on a half‑billion‑row table can cause long‑running transactions, lock contention, and replication lag, and demonstrates how to split the operation into indexed batch deletes or replace it with an insert‑into‑new‑table workflow to improve safety and performance.
This article presents thirty practical tips for optimizing SQL queries—covering index creation, avoiding costly operators, rewriting predicates, using set‑based logic, and managing temporary objects—to dramatically reduce full table scans and improve overall database performance.
This article explains Apache Iceberg’s architecture, core capabilities such as time‑travel, fast scans, delete handling, and schema evolution, and provides a step‑by‑step guide for configuring Flink to use Iceberg with Hive and Hadoop catalogs, including DDL commands and streaming queries.
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.
When using MessageFormat to inject integer parameters into dynamically generated MyBatis SQL, large numbers are automatically formatted with commas (e.g., 10000 becomes 10,000), which can break SQL statements, and the article explains why this happens and how to prevent it by converting numbers to strings before formatting.
SQLite, often underestimated, has added powerful SQL capabilities in 2018—including true/false literals, advanced boolean checks, window functions, FILTER clauses, upsert support via INSERT ON CONFLICT, and column renaming—bringing its feature set closer to major databases while retaining its lightweight, file‑based architecture.
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 presents ten essential advanced SQL techniques—including CTEs, recursive CTEs, temporary functions, CASE‑WHEN pivots, EXCEPT vs NOT IN, self‑joins, ranking windows, delta calculations, cumulative totals, and date‑time manipulation—each explained with clear examples and code snippets for interview preparation.
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 guide shows how to use Python and pandas to prepare datetime data, then insert it in bulk into Oracle and MySQL databases, handling type conversions with Oracle's TO_DATE and MySQL's STR_TO_DATE functions while managing transactions safely.
This article explains Apache Spark’s role in large‑scale data processing, traces the evolution from Shark to Spark SQL’s DataFrame and Dataset APIs, and details the internal Catalyst optimizer—including its rule‑based and cost‑based strategies—through step‑by‑step examples and code snippets.
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.
