This article presents a JD.com BI engineer's case study on applying Flink SQL to real‑time dimension modeling, detailing two complex streaming scenarios, the technical difficulties of handling historical data and performance, and a component‑based solution architecture with future roadmap considerations.
The article explains how ORM bridges Python objects and relational databases, compares options, and justifies choosing the lightweight Peewee library, then details its workflow—model generation, inserting, updating, deleting, and querying records—showcasing how this approach reduces manual data‑creation time from hours to seconds.
This article provides a comprehensive guide to ClickHouse SQL, covering database creation, table definitions, column defaults, temporary and partitioned tables, view types, DDL commands, data import/export formats, and mutation‑based update and delete operations with practical code examples.
This article presents a systematic method for parsing massive MySQL binlog files, identifying which fields are truly updated in each SQL statement, quantifying effective versus ineffective updates, and applying the results to optimize database design and reduce unnecessary binlog generation.
This tutorial walks through setting up Citus, creating and distributing PostgreSQL tables for a multi‑tenant ad‑analytics application, loading sample data, executing CRUD and analytical queries, and extending the approach to real‑time GitHub event analysis, demonstrating end‑to‑end database operations in a distributed environment.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for dynamic sets of business institutions and their products in a single indexed query.
This article examines fifteen common Java pitfalls and best‑practice guidelines—ranging from property copying and date formatting to HashMap sizing, thread‑pool creation, collection handling, logging, and serialization—explaining why each recommendation exists and how it impacts performance and safety.
This article analyzes a production incident where massive OFFSET values in MySQL pagination caused severe query slowdown, demonstrates the problem with simulated data, benchmarks the impact, and presents three practical solutions—including index‑covering subqueries, remembering the last primary key, and limiting offset size—to dramatically improve performance and protect the database.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑comparison technique—to efficiently retrieve sales data for multiple business units and their associated products in a single indexed query.
This article explains the four SQL language categories (DDL, DML, DCL, TCL), various backup and recovery types, common MySQL backup methods, and flashback recovery techniques, providing detailed guidance and tool comparisons for database administrators.
This article explains why querying the most recent record for a specific device in massive time‑series tables can be painfully slow in PostgreSQL, demonstrates the impact of using only a timestamp index, and presents several indexing and query‑design strategies—including composite indexes, lateral joins, SkipScan, recursive CTEs, and logging tables with triggers—to dramatically improve performance.
This article explores how to query sales data for multiple business units and selected products in MySQL, comparing loop queries, OR concatenation, mixed filtering, and SQL‑92 row‑value comparison, ultimately recommending the row‑comparison technique for efficiency and maintainability.
This article explains the core concepts of ClickHouse's MergeTree engine, its main features, how to create tables with various clauses, and detailed settings that control data storage, partitioning, replication, sampling, TTL, and granule management for efficient analytical workloads.
This article explains the concept, syntax, and practical applications of SQL CASE expressions, covering simple and searched forms, row‑to‑column pivots, statistical transformations, conditional SELECT and UPDATE statements, and CHECK constraints, with detailed code examples and best‑practice tips.
This article introduces the new full‑process features of OpenMLDB 0.4.0, explains its unified online/offline storage, high‑availability task management, and end‑to‑end AI workflow, and provides step‑by‑step instructions for quickly deploying both single‑node and cluster versions to run a complete online AI application.
This article explains why the IN and NOT IN operators often lead to poor performance and incorrect results in SQL queries, demonstrates the issues with concrete examples involving large tables and NULL values, and recommends using EXISTS, NOT EXISTS or JOIN as more reliable alternatives.
The article demonstrates that MySQL queries using LIMIT with a large offset cause heavy random I/O by scanning many rows, and shows how rewriting the query with a sub‑query that selects only primary keys dramatically reduces execution time from seconds to milliseconds.
The article compares MySQL’s relational tables with MongoDB’s schema‑free documents, detailing equivalent concepts, CRUD syntax, and benchmark results that show MongoDB achieving higher write and query throughput, easier schema evolution, and built‑in sharding, recommending it for new agile projects while suggesting MySQL remain viable for legacy systems.
SPL is an open‑source structured data processing language that extends full SQL‑92 functionality to a wide range of data sources—including CSV, Excel, JSON, NoSQL and Hadoop—allowing developers to perform complex queries, multi‑step calculations, and mixed‑source analytics without a traditional relational database.
This article explains why traditional B‑Tree indexes fail for keyword searches, introduces the concept of inverted indexes, shows how to create and use MySQL InnoDB full‑text indexes with MATCH‑AGAINST in various modes, and covers index maintenance and query‑expansion techniques.
Following the Navicat Premium malware incident, this guide introduces DBeaver—a free, open‑source, multi‑platform database management tool, explains its features, installation steps, driver setup, and where to obtain both community and commercial versions.
This article explains how IN and EXISTS work in MySQL, compares their execution strategies, shows example queries, and provides guidance on when to use each based on the relative sizes of the involved tables.
The article explains how using IN and NOT IN in SQL can cause performance problems and subtle bugs, especially with large tables or NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
This article explains Flink SQL’s streaming aggregation Mini‑Batch feature, covering its purpose, configuration parameters, underlying optimizer rules, operator implementations, watermark handling, buffer processing, and the optional Local‑Global two‑phase aggregation optimization for improving throughput and reducing state overhead in large‑scale data pipelines.
This article presents a detailed benchmark comparing MySQL 8.0.19 and MariaDB 10.4.12 on a Windows 10 i7 machine, covering single‑row and batch inserts, various SELECT queries with and without indexes, and analyzes the trade‑off between speed and memory usage.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
This article provides a step‑by‑step tutorial on using Apache Atlas for data lineage, including SQL execution, custom data maps, tagging, field search, detailed installation procedures, runtime commands, and the configuration of Hive and Sqoop hooks for a complete big‑data governance solution.
This tutorial explains how to install, configure, and operate Phinx within a ThinkPHP project to manage database schema migrations, create and run seed data, update tables, roll back changes, and keep development environments synchronized.
This article explains how InnoDB stores data on disk, the role of 16 KB pages in read/write operations, the dynamic row format’s length and NULL lists, overflow handling, and why varchar columns are limited to 16 383 characters under utf8mb4, illustrated with SQL examples.
This article explains MySQL indexes—what they are, their advantages and disadvantages, how they improve query performance, and the scenarios where you should or shouldn't create them—plus a free PDF of interview questions for deeper study.
Kuaishou dramatically increased Flink SQL adoption, introduced Group Window Aggregate and Window TVF extensions, applied aggregation state reuse and mini‑batch techniques, and enhanced stability through data‑skew mitigation and aggregate‑state compatibility, outlining future plans for streaming and batch SQL improvements.
The article explains why using COUNT(*) to test for record existence is inefficient and demonstrates how replacing it with SELECT 1 … LIMIT 1 in SQL and a corresponding Java existence check can significantly improve database query performance.
This article walks through how MyBatis parses the transaction manager factory from XML configuration, builds the Environment, and applies the transaction manager during SqlSession creation, including detailed code examples of XMLConfigBuilder, transaction factories, and the JdbcTransaction implementation.
This article explains how MySQL treats NULL values, the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, and demonstrates with practical examples how NULL affects indexing, query performance, aggregation functions, and sorting, while offering recommendations for schema design.
This guide walks through MySQL's query execution sequence, then presents concrete optimization techniques—from selecting specific columns and avoiding OR conditions to proper indexing, using EXPLAIN, handling VARCHAR vs CHAR, limiting result sets, and improving joins, GROUP BY, and UNION operations—complete with code examples and visual explain plans.
This article explains how the open‑source Structured Process Language (SPL) streamlines report data preparation, replaces complex SQL and Java code, supports multi‑source processing, enables hot‑swap, and dramatically reduces development effort and performance bottlenecks.
This guide explains how to collect, store, clean, and aggregate daily user metrics—including new users, active users, and multi‑day retention—using shell scripts, cron jobs, MySQL partitioned tables, and stored procedures, suitable for small‑to‑medium sized products.
When selecting a storage engine, evaluate business metrics such as user and data volume, read/write bias, and consistency needs, then match those requirements to the strengths and limits of relational databases, key‑value caches, column‑stores, document stores, search engines, and other NoSQL options to achieve the right balance of performance, scalability, and operational cost.
This article explains that the numeric value in MySQL INT definitions (e.g., int(1) or int(10)) does not limit the column's range, demonstrates inserting the maximum unsigned value, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
The article explains that the number in MySQL's INT definition (e.g., INT(1) or INT(4)) does not limit the column's numeric range, but only affects display width when combined with ZEROFILL, illustrating the concept with practical CREATE and INSERT examples.
This guide explains MySQL custom functions, covering their definition, advantages over stored procedures, step‑by‑step creation syntax, parameter handling, invocation, and management commands such as showing, altering, and dropping functions, with practical examples for both parameterless and parameterized functions and a concise comparison table.
This guide explains why InnoDB full‑text search is needed, how inverted indexes work, how to create and use full‑text indexes with MATCH AGAINST in natural language, boolean, and query‑expansion modes, and how to drop them safely.
The article explains that using IN and NOT IN in SQL queries often leads to poor performance and incorrect results, especially with large tables or NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This article explains why InnoDB full‑text search is needed for keyword‑based queries, introduces inverted index structures, shows how to create, use, and delete full‑text indexes in MySQL, and demonstrates natural language, boolean, and query‑expansion search modes with practical SQL examples.
This article evaluates how MySQL handles time data stored as datetime, timestamp, and bigint by inserting half a million records and measuring query, grouping, and sorting speeds, concluding that bigint generally offers the best performance for time‑range operations.
This article outlines a comprehensive data quality assurance framework for a massive reporting platform, covering the data pipeline architecture, detailed testing methods for timeliness, completeness, and accuracy, as well as application‑level checks, downgrade and backup strategies, and future automation plans.
This article explains why adding conditions after a LEFT JOIN's ON clause does not filter rows, contrasts ON and WHERE behavior, and demonstrates the differences with multiple SQL examples and visual illustrations of intermediate result tables.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures pagination query times under different offsets and limits, and presents practical optimization techniques such as sub‑queries, id‑range filtering, and column selection to improve performance on massive data sets.
This article explains how to inspect and clear Linux memory buffers, details the information shown by the free command, and provides a comprehensive overview of SQL join types, their performance impact, and optimization techniques such as indexing, join buffer tuning, and algorithm choices.
This article analyzes a production MySQL deadlock caused by two identical UPDATE statements that trigger an index‑merge optimization, explains the lock interactions between the idx_status and PRIMARY indexes, and provides code‑level and MySQL‑level solutions to prevent such deadlocks.
This article explains how InnoDB implements full-text search using inverted indexes, shows how to create and use full-text indexes with various query modes—including natural language, boolean, and query expansion—covers relevance calculation, stopwords, token size limits, and demonstrates how to delete indexes, all illustrated with SQL examples and diagrams.
This guide explains how Sentry uses Django migrations to manage database schema changes, covering commands for upgrading, targeting specific migrations, generating SQL, creating and merging migrations, and detailed best‑practice procedures for handling filters, indexes, column and table modifications, foreign keys, and renaming while avoiding downtime.
This article presents three practical SQL interview problems—finding the second‑highest salary per department, counting login intervals under five days, and analyzing daily purchase channels—complete with data setup, solution queries, and key concepts such as window functions and joins.
This article provides a comprehensive overview of MySQL's built‑in functions—including numeric, string, date/time, and aggregation utilities—explaining their usage, parameters, and output with clear examples to help developers write more efficient SQL queries.
The team built a lightweight AI‑FAAS platform that migrates C++ ad‑algorithm services to a Java‑centric, cloud‑native environment, encapsulates core operators as DolphinSQL plugins, and defines real‑time feature pipelines via standardized SQL, cutting service creation from weeks to days, enabling rapid iteration, high operator reuse, and near‑instant feature delivery.
The article explains how conventional SQL struggles with executing multiple grouping and Top‑N aggregations on massive tables, leading to repeated full scans and poor performance, and demonstrates how the SPL compute engine can perform these operations in a single pass with parallelism, improving speed and scalability.
Metabase is a fast, open‑source BI tool that can be set up in about five minutes using Docker, offering a user‑friendly interface for creating dashboards, custom SQL queries, data pivots, and multi‑table analysis without requiring deep SQL knowledge, making data visualization accessible to all.
This article explains database isolation levels, including read committed, repeatable read, serializable, and snapshot isolation, discusses dirty reads, phantom reads, and how various systems such as SQL Server, PostgreSQL, MySQL, Oracle, MongoDB, and others implement or differ in these isolation semantics.
This article explains why MySQL queries become slow, how to enable and analyze slow‑query logs, and provides practical steps for index, SQL, schema, optimizer, and architecture optimizations to keep performance strong even with millions of rows.
This article details ByteDance's practical experience with Apache Flink, covering SQL extensions, a visual SQL platform, performance tweaks such as window mini‑batching and custom windows, join and checkpoint recovery improvements, stream‑batch integration experiments, and future roadmap plans.
The article explains the true execution order of SQL queries, detailing each step from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT, and illustrates the concepts with examples and code snippets.
This article explains the principles, advantages, and pitfalls of MySQL composite indexes, covering the left‑most matching rule, field order effects, when single columns can use an index, and whether a composite index can replace single‑column indexes, with practical EXPLAIN examples.
This article explains the MySQL JSON data type, demonstrates how to store structured and semi‑structured data with JSON objects and arrays, shows practical table designs for user login and profiling, and details virtual columns, function indexes, and multi‑valued indexes for fast querying.
The article analyzes four fundamental limitations of SQL—lack of stepwise execution, incomplete set semantics, missing ordered operations, and no object reference mechanism—illustrates them with real queries, and introduces SPL as a more intuitive language that overcomes these issues.
A practical MySQL InnoDB test shows that count(1) and count(*) have almost identical performance, explains the underlying index usage, and clarifies why MyISAM can count rows faster due to its storage design.
This guide details comprehensive naming conventions and design best‑practices for MySQL database objects—including schemas, tables, indexes, views, stored procedures, functions, triggers, constraints, and users—along with recommended storage engine, character set, field types, index strategies, and SQL usage rules to ensure consistency, performance, and maintainability.
Learn how to effectively sort query results using ORDER BY, apply ASC/DESC rules, combine multiple fields, use aliases, integrate functions, and limit result sets with LIMIT for pagination, while avoiding common pitfalls such as duplicate sorting values and expression misuse in MySQL.
This article introduces MySQL indexes, explains why they are needed, compares B+Tree and hash index structures, describes how indexes improve query performance, outlines their limitations, shows how to create single‑ and multi‑column indexes, and discusses the trade‑offs of using them.
This article presents practical MySQL optimization methods—including proper column definitions, NOT NULL usage, JOIN over sub‑queries, UNION, transaction handling, foreign keys, table locking, indexing strategies, and a collection of query‑tuning tips—each illustrated with clear examples and code snippets to improve database speed and reliability.
The article explains that using the logical operator AND between column assignments in a MySQL UPDATE statement causes unexpected results, demonstrates the issue with sample data, and shows that multiple columns must be separated by commas to update them correctly.
This article provides a detailed walkthrough of DataGrip, JetBrains' database client, covering installation, connection setup, UI customization, SQL execution, data import/export, and advanced editor features, helping developers efficiently manage and query various databases.
This article explains how to design and implement a fully elastic, zero‑ops Serverless database using Alibaba Cloud Tablestore, covering its architecture, key features, step‑by‑step CLI setup, table creation, data import, querying, indexing, and cost efficiency for high‑volume order workloads.
This article explains the concept of Slowly Changing Dimensions (SCD), discusses practical design questions, compares three change‑tracking requirements, presents three implementation patterns, and provides detailed Hive/SQL examples for historical data initialization and incremental updates in large‑scale data warehouses.
Youzan upgraded its real‑time computing engine from Flink 1.10 to 1.13 to meet rising SQL and containerization demands, gaining enhanced SQL syntax, time‑function handling, Window TVF standardization, Hive integration, K8s stability, elastic scaling, richer Kafka and format support, improved metrics and debugging tools, and successfully migrated all custom connectors, UDFs, and SQL jobs to the new Kubernetes‑based platform.
This article explains Spark SQL join classifications, the mechanics of Nested Loop Join, Sort‑Merge Join, and Hash Join, and describes how the Catalyst optimizer and Tungsten project improve query execution and memory efficiency in large‑scale data environments.
This article walks through implementing a complex e‑commerce search API, starting with a monolithic SQL query, then refactoring it into multiple indexed queries, and finally accelerating it with Redis set and sorted‑set operations, including pagination and transaction optimizations.
This article explains the origins of Spark DataFrames, compares them with RDDs, describes how Spark SQL optimizes DataFrame execution, and provides detailed examples of creating DataFrames from RDDs, files, and JDBC sources along with common DataFrame operations and code snippets.
This article examines seven typical scenarios where MySQL indexes unexpectedly fail—such as OR conditions, leading wildcards in LIKE, missing quotes, functions on indexed columns, NULL checks, arithmetic operations, and composite index ordering—providing SQL examples, execution plans, and practical tips to avoid costly full‑table scans.
This article explains why SQL queries can become slow, outlines MySQL's internal architecture, demonstrates how to use the EXPLAIN statement to view execution plans, and interprets key fields such as type, key, and Extra to help developers identify and optimize performance bottlenecks.
This article introduces Presto, an open‑source MPP SQL engine, explains its coordinator‑worker architecture and connector model, and provides detailed storage, query, and join optimization strategies—including in‑memory parallelism, dynamic plan compilation, and practical SQL code examples—to achieve low‑latency, high‑performance analytics on big data.
This article introduces the open‑source pg_recovery tool for PostgreSQL, explains how to install it, and demonstrates recovering data lost through UPDATE, DELETE, ROLLBACK, and DROP COLUMN operations with practical command‑line examples.
When a table grows beyond ten million rows, sharding it across multiple databases improves performance, but the usual LIMIT offset, pagesize pagination breaks, leading to missing or incorrect records; this article examines why simple merge approaches fail and evaluates global, secondary query, and no‑skip paging strategies, highlighting their trade‑offs.
This article reviews MySQL horizontal partitioning, explains five partitioning strategies (HASH, RANGE, KEY, LIST, COMPOSITE), demonstrates how to create and test partitioned tables, compares performance with and without indexes, and covers partition management operations such as adding, dropping, and rebuilding partitions.
This tutorial explains how to use Apache Flink 1.12 to construct a unified streaming‑batch data warehouse by integrating Hive via HiveCatalog and HiveDialect, performing read/write operations, configuring upsert‑Kafka sinks, and leveraging Flink CDC connectors for change data capture from MySQL and other sources.
This article explains why MyBatis foreach batch inserts can become extremely slow when inserting thousands of rows, analyzes the underlying SQL and PreparedStatement overhead, and demonstrates how using ExecutorType.BATCH together with a limited batch size (20‑50 rows per statement) dramatically improves insertion speed.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
The article details the design, architecture, core features, scheduling logic, and performance gains of Vipshop’s self‑built Hera data service, which unifies data‑warehouse access, supports multiple engines, adapts SQL execution, and dramatically improves SLA for both B‑to‑B and B‑to‑C workloads.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
This article explains how MySQL InnoDB partition tables can dramatically boost query speed, simplify routine operations, enable parallel writes, and provide efficient data exchange and cleanup compared to non‑partitioned tables, illustrated with concrete SQL examples and performance metrics.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This article introduces chiner, an open‑source, cross‑platform database design tool built with React, Electron, and Java, compares it with PowerDesigner, walks through installation, core concepts, table and relationship management, and shows import/export capabilities for efficient schema development.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article demonstrates how to use Fluent Mybatis, native Mybatis, and Mybatis‑Plus to implement a complex student‑score aggregation query, compares their code structures, generation settings, and highlights the advantages and drawbacks of each approach for backend development.
After a colleague accidentally dropped a production database, we revoked DROP privileges for a MySQL user and systematically tested how this restriction impacts common operations such as renaming tables, using TRUNCATE, deleting rows, dropping objects, and recovering data via binlog, revealing surprising behaviors and practical workarounds.
This article explains MySQL's character encoding workflow, illustrates why queries containing Chinese characters fail without proper settings, and shows how to configure JDBC URLs, server variables, and Docker‑based MySQL instances to ensure lossless UTF‑8 handling.
