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.
This comprehensive guide explains what MyBatis is, compares it with Hibernate, details its core components, configuration, dynamic SQL, caching, plugins, pagination, and practical usage patterns, providing Java developers with clear examples, code snippets, and visual diagrams to master the framework efficiently.
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 the background, benefits, and measurement criteria of Kubernetes Horizontal‑Pod‑Autoscaler (HPA), describes the creation of metric tables and SQL queries for collecting scaling events and CPU usage, and presents a Python‑based workflow that aggregates the data, stores daily reports, validates results, and sends automated email summaries.
This article explains how to use MyBatis dynamic SQL tags—including if, where, set, choose, foreach, sql fragments, and bind—to build flexible queries and updates, with detailed code examples and tips for handling null parameters and avoiding syntax errors.
This article examines a MyBatis bug present in versions prior to 3.4.5 by reproducing the issue, walking through the complete query execution flow from configuration parsing to result handling, identifying the root cause in foreach variable binding, and presenting official fixes and practical work‑arounds.
This guide explains ClickHouse fundamentals, OLAP versus OLTP concepts, columnar storage benefits, core performance techniques, the MergeTree family and its indexing, specialized table engines, installation on Linux, Docker deployment, and integration with HDFS, MySQL, and Kafka for modern analytical workloads.
This article explains why MySQL upgrades can cause syntax, semantic and performance incompatibilities, introduces the Percona Toolkit's pt‑upgrade tool, details its detection logic and common usage patterns, provides a full demo with real query results, and lists essential parameters and precautions for safely migrating from MySQL 5.7 to 8.0.
This article compares the open‑source monitoring systems Zabbix and Prometheus, detailing their architectures, component roles, strengths, and weaknesses, then describes how to integrate Zabbix data into Prometheus using a custom super_exporter and visualise the combined metrics with Grafana.
This guide demonstrates how to construct flexible MyBatis dynamic SQL statements using tags such as if, where, set, choose, foreach, sql fragments, and bind, showing practical examples for conditional queries, updates, and reusable SQL components to streamline backend data access.
This article explains MySQL index fundamentals, covering clustered and secondary indexes, best‑practice design rules such as using auto‑increment primary keys, avoiding large or low‑cardinality columns, respecting the left‑most prefix, and common scenarios that cause index loss, with practical SQL examples.
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 guide walks you through installing DataGrip, configuring database connections, customizing the IDE, and leveraging its powerful SQL editing, navigation, and data import/export features, providing a comprehensive overview for developers seeking a streamlined database client experience.
This article introduces MyBatis-Mate, an official MyBatis-Plus extension that offers enterprise‑grade capabilities such as dictionary binding, field encryption, data masking, dynamic DDL generation, multi‑datasource sharding, data‑scope filtering, and SQL performance logging, complete with Maven dependency setup and code examples.
This article presents ten practical techniques—ranging from avoiding chat‑tool SQL transmission to using logical deletes, backups, and temporary tables—to help developers and DBAs safely execute and modify data in production environments while minimizing accidental data loss.
This guide explains three SQL techniques—DISTINCT, GROUP BY, and the ROW_NUMBER window function—for deduplicating records, compares their behavior and performance, and provides concrete query examples with a sample task table and additional test cases.
This article demonstrates practical techniques for data masking, covering MySQL string functions for phone and ID masking, a Java library for various sensitive fields, and a MyBatis‑Mate extension that applies custom and built‑in masking strategies through annotations and REST endpoints.
This article demonstrates how to mask sensitive phone numbers and ID cards directly in MySQL, introduces a Java masking library, and provides a complete MyBatis‑Mate extension with custom strategies, configuration files, and sample Spring Boot code to protect personal data in applications.
Zuoyebang’s data team replaced fragmented, slow query solutions with Apache Doris, building a unified real‑time data warehouse that dramatically cut query latency from hours to seconds, streamlined data modeling, and improved reliability across diverse business scenarios, while integrating with Flink, Kafka, and ES via a unified API.
This article examines ClickHouse's join mechanisms, detailing the limitations of standard joins, the advantages of Global joins, and optimization strategies such as hash and merge joins, subquery filtering, and memory considerations, illustrated with SQL examples and source‑code analysis.
This article walks through the complete lifecycle of a MySQL SELECT statement, from establishing the TCP connection and authentication, through query cache, parsing, optimization, and execution, detailing how the server and storage engine collaborate and explaining concepts like index usage, connection handling, and memory management.
This article provides a comprehensive overview of MySQL indexes, explaining their underlying data structures, classification methods, practical usage scenarios, and optimization strategies, while illustrating concepts with diagrams and SQL examples to help developers design efficient query performance.
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.
This article explains MySQL index fundamentals, covering B+Tree structures, various index types, creation syntax, and practical optimization techniques such as prefix, covering, and composite indexes, while illustrating how to avoid common pitfalls like index misuse and performance‑degrading scenarios.
This article explains how classic SQL execution engines use the volcano model and expression trees, discusses their performance drawbacks, introduces vectorized execution to reduce overhead, and describes Roaring Bitmap compression methods with container types for efficient storage and processing of integer sets.
This article provides a comprehensive overview of Doris's architecture—including its FE and BE components, metadata management, data organization, execution planning—and details its major features such as adaptive join aggregation, vectorized execution, materialized views, and Elasticsearch integration, supplemented with example DDL and query code.
The article examines the inherent trade‑offs of data lakes—maintaining raw data, enabling efficient computation, and keeping costs low—explains why traditional data‑warehouse approaches fall short, and introduces the open‑source SPL engine that provides multi‑source, file‑based, high‑performance analytics to overcome these limitations.
This article explains how MySQL's EXPLAIN command reveals execution plans through twelve key fields, demonstrates practical analysis of a slow query on a large table, and provides actionable tips such as adding or forcing indexes to dramatically improve query performance.
This article provides a comprehensive overview of Oracle's database products, explaining why they matter, detailing the physical and logical storage structures, instance processes and memory areas, and tracing the evolution from early versions through RAC, multitenancy, and the latest 18c features.
This article provides a comprehensive guide to Apache Doris, covering how to create databases and tables with single and composite partitions, import data via broker and routine loads, understand its aggregate, uniq, and duplicate data models, and leverage rollup and prefix index features for optimized querying.
This article explains how to create a custom MyBatis Interceptor using Java dynamic proxies, detailing the Interceptor interface, the plugin generation process, the wrap and invoke methods, and common use‑cases such as pagination, SQL analysis, encryption, and performance monitoring.
This article translates and expands on a HikariCP wiki post, explaining why a small database connection pool often yields better performance than a large one, presenting benchmark data, a practical sizing formula, and guidance for tuning pools in various environments.
Apache Kylin leverages pre‑computed OLAP cubes on Hadoop/Spark/Flink to deliver sub‑second query responses for massive datasets, detailing its architecture, integration with BI platforms, user security, cube building, monitoring, and storage using HBase, illustrating how it overcomes big‑data analytical challenges.
This article explains ClickHouse's single‑node and distributed JOIN mechanisms, compares ordinary, GLOBAL, Broadcast, Shuffle and Colocate JOINs, illustrates execution flows with code examples, and provides practical recommendations to reduce join size, avoid query amplification, and leverage data pre‑distribution for optimal performance.
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 how to identify slow SQL statements, analyze execution plans with EXPLAIN, use profiling and tracing tools, and apply specific optimization techniques such as index tuning, query rewriting, and alternative storage solutions across a range of real‑world cases.
This article walks through the challenges of inserting massive data sets with MyBatis, explains the inner workings of batch execution, highlights common mistakes with commit, clearCache and flushStatements, and presents progressively refined code examples—including Oracle-specific optimizations—to dramatically improve performance.
This article details Bilibili's end‑to‑end Presto on Hadoop architecture, covering the multi‑engine SQL stack, dispatcher routing, cluster scale, stability enhancements like coordinator HA and real‑time punish, query limits, Hive UDF compatibility, insert‑overwrite support, Alluxio caching, multi‑datacenter routing, query result caching, Raptorx local cache, JDK upgrades, dynamic filtering, and future roadmap, illustrating how these innovations boosted query throughput and reduced latency.
This article walks readers through MyBatis batch processing from basics to advanced, highlighting common mistakes, explaining the roles of commit, clearCache, and flushStatements, and presenting three progressively refined code versions plus Oracle-specific optimizations to dramatically improve bulk insert performance.
This article details JD Logistics' journey from fragmented, massive‑scale data to a unified, real‑time analytics platform, covering business needs, pain points, tool evaluation, a new Doris‑based architecture, table management, data import procedures, automation scripts, and future roadmap for data engineering.
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.
Zuoyebang’s big‑data team shares how they evolved from SparkStreaming to a Flink‑SQL‑centric real‑time platform, detailing three development stages, challenges in DAG optimization, Redis‑based table design, and platform features for unified deployment, ease of use, and operational governance.
This article introduces a Spark offline development framework that separates configuration from code, supports SQL and Java applications, and provides fast, automated data backtracking with reduced environment preparation time, lower failure rates, and significant performance gains for large‑scale data warehouses.
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.
MySQL creates two kinds of temporary tables—external and internal—based on CREATE TEMPORARY TABLE statements or internal query processing, and uses the MEMORY engine by default unless large objects or the big_tables variable force MyISAM or InnoDB, with detailed guidance on scenarios, storage engine selection, conversion thresholds, indexing, and performance tuning.
This article compares Elasticsearch and ClickHouse in architecture, query capabilities, and performance for log search workloads, presenting Docker‑compose setups, data ingestion pipelines, sample queries, and benchmark results that show ClickHouse generally outperforms Elasticsearch, especially in aggregation scenarios.
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.
Facing a sudden MySQL table lock? This guide walks through real‑world troubleshooting steps—from checking table usage and process lists to inspecting InnoDB transaction and lock tables, identifying lock‑wait relationships, and safely killing offending sessions, while also explaining lock types and common metadata‑lock scenarios.
This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article explains Flink's window table-valued functions (TVF), shows how they replace the old grouped‑window syntax with concrete SQL examples, describes the physical planning rules, introduces sliced windows for state efficiency, and presents a small incremental‑output improvement for cumulative windows.
The article asks how many tables can be created in a single MySQL database using the InnoDB storage engine, prompting readers to provide the correct limit without consulting external sources and discuss the theoretical maximum based on MySQL specifications.
Facing server‑hardware constraints, Bilibili’s data platform replaced wasteful full‑partition tables with a zipper‑table approach—preserving change history while cutting storage from petabytes to terabytes—and complemented it with Hudi + Flink CDC for near‑real‑time updates, dramatically lowering I/O, compute usage and latency.
This article explains two techniques for creating an org.apache.ibatis.jdbc.SQL object in MyBatis—using an anonymous inner class with an initializer block and using method‑chaining builder style—provides code examples, explains the underlying Java syntax, and summarizes their differences.
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 presents eight common MySQL error scenarios—including forgotten passwords, password policy violations, case‑sensitivity issues, service startup failures, export/import restrictions, connection limits, binary log overflow, and primary‑key replication errors—along with detailed troubleshooting steps and configuration commands to resolve each problem.
This article explains how to retrieve sales statistics for multiple business units and their associated products in MySQL by comparing rows, discusses alternative approaches such as loop queries, OR concatenation, and mixed filtering, and demonstrates why row‑by‑row comparison is the most suitable solution.
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.
The article explains Propensity Score Matching as a causal inference method, detailing treatment effect concepts, required assumptions, score estimation, various matching algorithms, SQL implementation, quality assessment metrics, and how to estimate ATT using Difference-in-Differences, while outlining workflow steps, trade-offs, and alternatives.
This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.
This guide explains how to set up the Flink SQL client to work with Apache Iceberg, covering Scala version requirements, downloading and deploying Iceberg jars, configuring Hive and HDFS catalogs, creating databases and tables, performing insert and overwrite operations, and querying data in both batch and streaming modes.
This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.
The article explains Presto’s join processing by detailing the business need to limit multi‑table joins, then describing nested‑loop, sort‑merge, and hash join algorithms with Java examples, and finally showing how the Volcano model, columnar pages, and planner integration enable scalable, efficient OLAP join execution.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This article explains three common SQL deduplication techniques—using DISTINCT, GROUP BY, and the ROW_NUMBER window function—illustrating their syntax, performance considerations, and example queries for counting unique task identifiers in relational databases.
This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.
This article presents a collection of intermediate to advanced SQL interview problems covering grouping, window functions, sessionization, ranking, and join techniques, each accompanied by detailed solution queries and explanations to help data engineers prepare for technical interviews.
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 details Bilibili's end‑to‑end Presto‑based SQL‑on‑Hadoop architecture, covering overall system components, query routing, Presto feature set, extensive stability and availability enhancements, performance boosts through caching and multi‑datacenter deployment, and outlines future development plans.
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 explains how using IN and NOT IN in SQL queries can lead to poor performance, index misuse, and incorrect results—especially with NULLs—and shows how EXISTS, NOT EXISTS, and JOINs provide more reliable and efficient solutions.
