Data warehouse modeling organizes data into layered structures—ODS, DWD, DWS, and ADS—to improve performance, reduce costs, ensure data quality, enable traceability, simplify maintenance, and support both batch and real‑time analytics, while outlining best practices for ETL processes and schema design.
This article explains how MySQL performs implicit type conversion during queries, demonstrates the unexpected results caused by joining mismatched column types, outlines the conversion rules, and provides practical tips to avoid performance degradation and incorrect data retrieval.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
This article explains MySQL's native data encryption capabilities, detailing the AES_ENCRYPT/AES_DECRYPT functions, their parameters, storage considerations, example usage, and best‑practice recommendations for secure and efficient encryption of sensitive fields.
This article thoroughly explains how MyBatis binds mapper interfaces to XML files, details the step‑by‑step SQL execution flow—including mapper retrieval, statement preparation, parameter handling, and result mapping—and demonstrates how to create custom typeHandlers for both input parameters and result sets.
This article explains how MySQL uses indexes during JOIN operations, compares indexed and non‑indexed join execution, introduces Index Nested‑Loop Join and Block Nested‑Loop Join algorithms, discusses the role of the join buffer, and provides practical guidance on choosing the optimal driving table for efficient query performance.
This article explains MyBatis's first-level (SqlSession-scoped) and second-level (Mapper-scoped) caching mechanisms, their default behaviors, key generation, configuration options, and the impact of Spring integration, and provides recommendations for disabling caches in distributed environments to avoid data inconsistency.
This article explains MyBatis caching fundamentals, detailing first- and second-level cache concepts, configuration, cache invalidation scenarios, and custom cache integration with EHCache, supplemented by practical code snippets and quiz questions to illustrate cache behavior across SqlSessions.
This article walks through building a low‑code platform that automatically creates Spring Boot REST endpoints from plain SQL definitions, covering datasource configuration, dynamic request mapping, SQL execution, and optional Swagger documentation generation.
This article introduces xlite, a Rust‑based SQLite extension that lets you import Excel files as virtual tables and run full SQL queries for data lookup and analysis, outlining a simple three‑step workflow and noting current read‑only limitations.
The article explains the importance of clear SQL naming conventions, table and index creation rules, and general syntax guidelines in database DevOps, highlighting developer resistance to audits, the rationale behind each rule, and how tools like Bytebase with VCS integration improve the review experience.
This article explains MySQL’s three‑tier logical architecture—from client services to core query processing and storage engines—and details the four ANSI SQL transaction isolation levels, highlighting their behavior, advantages, and trade‑offs in MySQL.
The SQLE 1.2210.0 release introduces integrated CloudBeaver support for multiple database types, new MySQL audit rules, enhanced smart‑scan filtering, independent menus for scan tasks, along with numerous optimizations and bug fixes, and provides full release information and acknowledgments.
This guide explains why a single massive DELETE on a 500‑million‑row table creates long‑running transactions and locks, and shows step‑by‑step techniques—date‑range batching, primary‑key range slicing, and insert‑instead‑of‑delete—to break the operation into manageable chunks and improve performance.
The article explains cardinality counting in Presto, comparing exact set‑based methods with memory‑efficient bitmap, Linear Count, and HyperLogLog approximations, detailing their algorithms, implementation in Presto’s query engine, and offering best‑practice recommendations for choosing the appropriate technique in business workloads.
This article explains the functional differences between the run_sql and sql commands in MySQL Shell, demonstrates how to execute DDL/DML statements, bind variables, and retrieve results using both commands, and provides guidance on selecting the appropriate function for various use cases.
This guide walks you through integrating MyBatis with Spring Boot, covering basic CRUD operations, dynamic SQL constructs like if, choose, where, set, foreach, as well as advanced mapping techniques, pagination plugins, and practical code examples to help you master MyBatis in real-world projects.
Bilibili adopted the open‑source Kyuubi proxy to replace its unstable STS layer, enabling multi‑tenant, multi‑engine (Spark, Presto, Flink) SQL/Scala processing with Hive Thrift compatibility, fine‑grained queue isolation, UI monitoring, stability safeguards, and Kubernetes/YARN deployment, while planning further cloud‑native extensions.
This guide explains ClickHouse’s core concepts, application scenarios, table engine families, detailed engine configurations, SQL development standards, cluster architecture, ZooKeeper’s role, chproxy usage, client tool options, availability considerations, and performance‑tuning parameters for high‑throughput OLAP workloads.
This article explores the inner workings of SQL engines, detailing the end‑to‑end process from query submission through lexical analysis, parsing, abstract syntax tree generation with ANTLR4, and subsequent logical and physical plan optimization, while also demonstrating practical Java examples for SQL parsing and rewriting.
This article provides an in-depth overview of SQL window (OLAP) functions, covering their classification, syntax, window definition methods, differences from aggregate functions, and includes interview-style queries for user behavior and student score analysis.
This article explains the standard SQL CORRESPONDING keyword, shows how HSQLDB implements it with UNION, INTERSECT and EXCEPT, demonstrates the optional BY clause for column selection, and discusses its limited support in other dialects such as PostgreSQL.
This article walks through the performance bottleneck of CPU in data analytics, introduces the Velox vectorized execution engine, and provides a detailed, zero‑to‑one tutorial for downloading Presto source, syncing Velox, fixing build paths, compiling both Java and C++ components, configuring CLion and IntelliJ, launching the servers, and executing SQL queries while noting stability concerns.
This article explains how to handle massive MySQL tables by splitting a single large table into multiple smaller tables, creating a MERGE (union) table to combine them, and using the merged view to implement efficient pagination, including necessary SQL statements and troubleshooting tips.
This article explains the logical execution order of SQL statements, highlights common performance pitfalls such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and demonstrates how to rewrite queries with JOINs, early range reduction, CTEs, and other optimizations to achieve dramatically faster execution times.
This article explains the limitations of ClickHouse for multi‑table joins, describes ByteHouse’s staged execution model, various join strategies (Shuffle, Broadcast, Colocate) and runtime filters, and presents performance benchmarks that show significant speed‑ups over the original ClickHouse engine.
This article introduces the ten most important programming languages for cybersecurity, explains why each language matters, and shows how mastering them can boost your ability to protect systems, detect threats, and build secure applications in an increasingly digital world.
This article explains the fundamental differences between row‑based and column‑based storage in databases, compares their write and read performance, lists pros and cons, presents an openGauss experiment with sample tables, and offers practical guidance on when to choose each storage model.
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.
