This article walks through the complete implementation of an advertising statistics pipeline using Spark and Kudu, covering requirement analysis, Scala code development, SQL queries, schema definition, and data sinking, with full code snippets and execution results.
This tutorial walks through a Spark‑Kudu advertising project that computes province‑city distribution statistics using SQL, defines the necessary schema, and demonstrates how to write the aggregated results back to a Kudu table for persistent storage, complete with Scala code examples.
This tutorial explains how to obtain geographic coordinates, store them as MySQL POLYGON types following OGC standards, insert campus area data, and query with spatial functions and SPATIAL indexes to determine whether a user is within a defined check‑in region.
This article explains how to construct SQL INSERT statements for string, numeric, date, and boolean fields, demonstrates variable concatenation for dynamic values, and provides a comprehensive example that combines multiple data types into a single query.
The article describes how a news‑list page became sluggish due to large base64‑encoded images stored in the database, details the investigation using Chrome dev tools and SQL queries, and presents a step‑by‑step solution that moves images to a FastDFS file server and updates the content references.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
This article explains how MySQL's AUTO_INCREMENT generates unique identifiers, the constraints on its use, how deletions affect the sequence, the role of LAST_INSERT_ID in concurrent environments, and best practices for managing auto‑increment columns in InnoDB tables.
This article explains how ClickHouse's MergeTree engine creates partition directories, the naming convention for partitions, and the automatic and manual merging processes that consolidate parts while tracking block numbers and merge levels.
This article provides a detailed collection of SQL Server commands and techniques covering database creation, table and index management, common DML statements, advanced set operators, various join types, grouping, pagination, maintenance tasks, replication setup, linked‑server usage, and a synchronization stored procedure.
This article explains why using NULL columns in MySQL can hurt performance and increase storage, debunks common myths with official documentation, and provides practical reasons and code examples for preferring NOT NULL constraints in table design.
This article introduces Confluent KSQL, a SQL‑based real‑time stream processing engine for Kafka, covering its architecture, stream vs table concepts, query lifecycle, Docker‑based setup, DDL commands, example joins, windowed aggregations, connectors, and its advantages and limitations.
This article explains how to install, configure, and test MySQL 8.x query‑rewriter and DDL‑rewriter plugins, demonstrating environment setup, sample SQL scripts, rule creation, and the impact on query performance and CREATE TABLE statements.
This article explains the evolution of Apache Flink's SQL support, detailing the Blink Planner architecture, the end‑to‑end Flink SQL workflow, logical and physical planning, code generation, stream‑specific optimizations such as retraction and mini‑batch, and future development directions.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
This tutorial walks Python developers through using the built‑in sqlite3 library to create, query, and manage SQLite databases, demonstrates how to connect with SQL clients like DBeaver, and shows seamless integration with Pandas data frames for advanced data handling.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains how to locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
The article explains that when checking for the existence of records, using SELECT count(*) is inefficient and proposes replacing it with SELECT 1 … LIMIT 1, showing Java code examples and highlighting performance gains and reduced index usage.
This article explains the meaning of Java OOM errors, presents a real‑world case where an Oracle database lock caused a slow memory leak leading to OOM, details the lock types and modes, provides SQL to identify and kill offending sessions, and offers best‑practice tips to avoid lock‑related performance issues.
This article introduces Apache Phoenix, an open‑source SQL layer for HBase that enables JDBC‑based table creation, data insertion and querying, while supporting secondary indexes, transactions and various optimizations, and outlines a series covering its syntax, tools, best‑practice and real‑world use cases.
Discover the true execution sequence of SQL queries, why SELECT runs fifth, how window functions and GROUP BY interact, and the impact of engine optimizations, illustrated with diagrams and code examples spanning aliases, LINQ, and pandas.
This article breaks down MySQL’s architecture into its five key parts—connector, parser, optimizer, executor, and storage engine—explaining each component’s role from establishing connections and handling permissions to query parsing, execution planning, and data storage, with example commands and code snippets.
This article presents a collection of practical PostgreSQL DBA techniques—including selective updates, bulk‑load optimizations, CTE‑based deduplication, partial and BRIN indexes, correlation tuning, and transactional DDL tricks—to help developers reduce database bottlenecks, improve query speed, and avoid common performance pitfalls.
This article explains Hive's bucket sampling syntax, demonstrates how to use the TABLESAMPLE clause with various bucket parameters, provides concrete SQL examples, and clarifies the underlying hash‑based mechanism that determines which rows are returned.
This article explains the concepts of the first, second, and third normal forms in relational databases, shows concrete table examples, discusses why normalization is important for consistency and storage efficiency, and explores denormalization trade‑offs and modern JSON support in MySQL and NoSQL systems.
This article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) when checking for the existence of rows, shows the common and optimized SQL/Java code patterns, and discusses the performance benefits and impact on index usage.
This article explains the TPC‑C benchmark, covering its purpose, data model, database schema, the five transaction types, and a detailed step‑by‑step walkthrough of the New‑Order operation with accompanying SQL illustrations, while also summarizing workload characteristics and providing reference links.
This article explains the concepts of the first, second, and third normal forms, shows how to identify violations with concrete table examples, discusses why normalization is important for consistency and storage efficiency, and explores anti‑normalization techniques that trade redundancy for performance.
The article explains that using SELECT 1 with LIMIT 1 instead of COUNT(*) to test whether rows exist can dramatically improve query performance, shows the typical COUNT(*) approach in SQL and Java, and provides a streamlined alternative with code examples and practical benefits.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
The article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) for existence checks in SQL and shows how to simplify the corresponding Java code for better performance.
This article presents a thorough overview of MySQL optimization, covering the query execution process, philosophical principles, risk assessment, required participants, optimization dimensions, commonly used diagnostic tools, emergency and regular tuning procedures, hardware and system level adjustments, as well as detailed configuration parameters for both the server and the database engine.
This article provides a detailed walkthrough of JetBrains DataGrip, covering installation, connection setup, UI customization, SQL editing features, data import/export, and numerous productivity shortcuts, helping developers efficiently manage and interact with various relational databases.
This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
This article details how Alibaba’s Data Experience team builds a comprehensive stack—from a high‑performance SQL editor and low‑code BI platform to sophisticated data visualizations, intelligent analytics, digital art, Web3D rendering, and robust data‑security measures—showcasing the architecture, design principles, and future directions of their end‑to‑end data‑driven product ecosystem.
This article explains why MySQL data can become garbled due to mismatched character sets during insertion or retrieval, explores various scenarios such as client‑side encoding errors, mixed encodings within tables, and LATIN1 storage, and provides detailed solutions including setting proper client encodings, using strict SQL_MODE, and exporting/importing data to unify character sets.
This article introduces PostgreSQL, the powerful open‑source object‑relational database, highlights its rising popularity in DB‑Engines rankings, compares its advanced features and performance advantages over MySQL, and outlines many of its unique capabilities such as rich data types, sophisticated indexing, and robust replication.
This tutorial walks through the test environment and demonstrates how InnoDB Cluster's built‑in functions—such as get_cluster, describe, list_routers, set_option, options, set_instance_option, set_primary_instance, switch_to_multi_primary_mode, switch_to_single_primary_mode, status, and dissolve—simplify the deployment, monitoring, and administration of MySQL group replication, complete with code snippets and visual results.
This guide walks through using Oracle Real Application Testing (RAT) Database Replay to capture production workloads, preprocess them, and replay them on a new Oracle 19c environment, highlighting setup steps, common pitfalls, and monitoring techniques.
This guide details recommended MySQL versions for Zabbix, proper grant statements for read/write and read‑only users, essential MySQL configuration tweaks, SQL techniques for item and function updates, read‑write splitting, upgrade procedures, backup strategies, and comprehensive partition maintenance scripts to improve performance and manage data growth.
This article explains various MySQL pagination methods—including simple LIMIT queries, sub‑query optimizations, ID‑range filtering, and temporary‑table approaches—provides detailed performance measurements on a large order_history table, and concludes with practical recommendations while also promoting a comprehensive interview‑question PDF collection.
This article explains Hive's role as a Hadoop‑based data warehouse, its integration with HBase, the advantages and drawbacks of that combination, introduces Apache Phoenix as a high‑performance SQL layer on HBase, and describes the open‑source NewSQL database Lealone, providing practical usage scenarios and performance comparisons.
This article compiles frequent Spark SQL, Spark Core, PySpark, and Streaming problems—such as filesystem errors, configuration pitfalls, memory limits, shuffle failures, and version incompatibilities—along with concise explanations of their causes and step‑by‑step remediation methods for big‑data environments.
This article provides a thorough, step‑by‑step guide to MySQL, covering how to install and start the server, create databases and tables, use DDL/DML/DQL statements, understand numeric, date/time and string data types, apply arithmetic, comparison, logical and bitwise operators, and leverage common built‑in functions for efficient data manipulation.
This article provides a comprehensive overview of relational database fundamentals and SQL syntax, covering basic concepts, statement categories (DDL, DML, TCL, DCL), CRUD operations, subqueries, joins, unions, functions, ordering, grouping, constraints, transactions, permission management, stored procedures, cursors, and triggers with illustrative code examples.
This article explains PostgreSQL's auto‑increment mechanism, covering built‑in serial data types, manual sequence creation, default value assignment, step‑by‑step code examples, and how to query indexes, offering a clear comparison with MySQL's auto_increment feature.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
This article explains MySQL transaction isolation concepts, the four standard isolation levels, their effects on dirty, non‑repeatable, and phantom reads, practical configuration steps, when to use repeatable read, how InnoDB implements isolation, and tips for avoiding long transactions and planning backups.
This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
The article explains why using SELECT 1 with LIMIT 1 to test for record existence is more efficient than counting rows with SELECT count(*), and shows how to rewrite both SQL and Java code for better performance.
To streamline Oracle SQL script reviews, this guide shows how to adapt the open‑source SOAR tool—originally built for MySQL—by defining custom rules, installing Go and Git, compiling the source, and integrating a web UI, enabling automated, 90% coverage of Oracle SQL checks.
The article explains why using COUNT(*) to test record existence is inefficient and demonstrates an optimized approach that replaces COUNT(*) with SELECT 1 LIMIT 1 in SQL and a corresponding null‑check in Java, improving database performance especially when many rows match the condition.
This tutorial demonstrates how to create a VARBINARY test table in HBase using Phoenix, serialize its data with RoaringBitmap, implement a custom Spark aggregation function to merge bitmap values, and query the table via Spark SQL, showcasing a practical big-data processing workflow.
SQLFlow is an open‑source system that lets users describe and execute end‑to‑end AI tasks—including data extraction, preprocessing, model training, evaluation, prediction, and explanation—entirely with SQL, simplifying workflow construction across multiple databases and machine‑learning engines while supporting scalable execution on Kubernetes.
The article shares practical experiences on running test cases concurrently using Java multithreading and a custom thread pool, and outlines MySQL database naming, null handling, and schema design guidelines, including full code examples for the case runner and thread pool implementation.
This article examines the role of foreign keys in relational databases, comparing their consistency guarantees and performance overhead with manual simulation approaches, and provides practical guidance on choosing RESTRICT, CASCADE, or no foreign‑key strategy in high‑concurrency applications.
This article explains MySQL's storage engines, their features and selection criteria, guides on choosing appropriate data types such as CHAR, VARCHAR, TEXT, BLOB, numeric and date types, and discusses character sets and related SQL commands.
This article demonstrates how to create and register custom scalar UDFs, untyped and type‑safe aggregate functions (UDAF and Aggregator) in Spark SQL, and how to apply window functions such as ROW_NUMBER, providing complete Scala code examples and execution results.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This article explains how to specify and manage character sets and collations when creating or altering MySQL databases, tables, views, triggers, and columns, illustrating the impact on stored procedures and data retrieval with practical SQL examples.
Apache Flink 1.11.0 introduces a suite of major enhancements—including unaligned checkpoints, a unified source interface, CDC support in Table API/SQL, performance‑boosted PyFlink, a new application deployment mode, and numerous UI, Docker, and catalog improvements—aimed at increasing usability, scalability, and integration across streaming and batch workloads.
This article explains how to resolve foreign key constraint violations that occur when migrating data from multiple source databases into a target PostgreSQL/Greenplum database by disabling, dropping, or deferring constraints and then re‑enabling them to ensure full data integrity.
The article explains why a SELECT COUNT(*) on a 5‑million‑row InnoDB table was extremely slow, analyzes the execution plan, demonstrates that the query used the primary (clustered) index, and shows how creating a small secondary index reduces the runtime from dozens of seconds to under a second.
This article analyzes the various system‑level and database‑level reasons why SELECT, UPDATE, INSERT, or DELETE statements become slow, covering network issues, OS and hardware constraints, MySQL configuration, indexing, execution plans, and query design, and offers practical mitigation strategies.
This article introduces Spark Thrift Server, explains how to deploy it by copying configuration files and required JARs, details its architecture and SQL execution flow, compares it with HiveServer2, and discusses its advantages, limitations, and practical suitability.
This article explains MySQL’s internal workflow, covering the Server layer components, Storage Engine layer, connection handling, query cache behavior, lexical and syntax parsing, optimization decisions, and execution steps, including tips for long‑connection management and common pitfalls.
This article explains the background of Oracle 19c hot clone, lists the prerequisites, details the three‑phase cloning process, provides exhaustive local, remote, subset and refreshable PDB scenarios with exact SQL commands, highlights common errors and their solutions, and concludes with best‑practice recommendations.
This article introduces KSQL as a lightweight streaming SQL engine for Apache Kafka, explains its architecture and core concepts of streams and tables, and provides step‑by‑step deployment instructions, command‑line examples for creating streams/tables, querying data, and managing persistent queries.
This article explains Spark SQL's Adaptive Execution engine, covering its background, dynamic plan adjustments, shuffle partition tuning, data skew mitigation techniques, and the key configuration parameters needed to enable and fine‑tune adaptive query execution for improved performance.
This article explains how HiveCube can replace traditional development for multi‑dimensional aggregation in a data‑warehouse, covering background, theory of cube, with‑cube/rollup/grouping‑sets syntax, grouping_id handling, practical implementation tips, performance tuning, and a comparison with conventional methods.
In this technical diary I walk through three real‑world problems I hit while integrating MyBatis SQL fragments, debugging Groovy constant class compatibility, and crafting a reliable Bash startup script for Java services, sharing the root causes, error messages, and step‑by‑step fixes.
This article details Shopee Singapore Data Team’s implementation of a Flink‑based real‑time data warehouse, covering background challenges, layered architecture integrating Kafka, HBase, Druid, Hive, streaming pipelines, job management, monitoring, and future plans to expand Flink SQL support.
This article walks through the complete lifecycle of a SQL SELECT statement—using simple Citizen and City tables—to illustrate the six processing stages (FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY/LIMIT), showing how each clause transforms the data step by step.
This article presents a Python-based method for quickly profiling dozens of large database tables by generating and executing column‑wise SQL queries, avoiding full table reads, and exporting the results to an Excel file for easy analysis.
This article explains MySQL's support for XA distributed transactions, describes the two‑phase commit process, details the XA syntax, and walks through a complete hands‑on example that transfers money between two databases using XA START, END, PREPARE, RECOVER and COMMIT commands.
This article explains why using SELECT * in MySQL queries reduces performance, detailing increased parsing cost, network overhead, I/O for large columns, and loss of covering index optimization, and also expands on index concepts such as composite indexes and their impact on query efficiency.
This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.
This article explains how to explicitly specify character sets and collations in MySQL using the character introducer, CONVERT and CHARSET functions, SET NAMES/SET CHARACTER SET statements, and the COLLATE clause, with practical examples and guidance on coercibility.
This article provides a concise overview of relational database fundamentals, covering key terminology, the structure of SQL statements, essential DDL/DML/TCL/DCL commands, query operators, joins, subqueries, functions, indexing, constraints, transactions, permissions, stored procedures, cursors, and triggers, with practical code examples throughout.
This article provides a comprehensive MySQL tutorial covering schema concepts, data types, service control, database creation, table manipulation, and complete CRUD operations with practical command‑line examples and code snippets for beginners and intermediate users.
Although many developers hear the advice to avoid using SELECT * in SQL, this article delves into the underlying reasons—such as increased parsing cost, unnecessary data transfer, extra I/O, and loss of covering-index optimization—while also explaining index concepts and best practices for MySQL performance.
This guide demonstrates how to recover MySQL 5.7 data after an accidental UPDATE by extracting row‑based binlog entries, converting them into executable SQL statements, and applying those statements to restore the original table contents.
This article walks through building complex test‑case search queries using MySQL CASE WHEN, FIND_IN_SET, and MyBatis dynamic SQL, demonstrates converting asynchronous data fetches to synchronous calls with CountDownLatch in Spring, and provides complete Java bean and configuration examples.
Although most SQL statements begin with SELECT, the actual execution follows a specific order—WHERE, GROUP BY, HAVING, then SELECT—illustrated by a diagram that also clarifies common misconceptions about filtering window functions, column aliases, and how databases may reorder operations for optimization.
This guide explains how SQL analytic (window) functions work, covering the concepts of current row, logical and physical windows, PARTITION, ORDER BY, the differences between ROWS and RANGE, window clause variations, and their advantages and drawbacks.
This article demonstrates how to use MyBatis to update records with sub‑queries, batch‑insert list‑type attributes, defines the related Java bean, and implements a Spring servlet filter (with request and response wrappers) to log request parameters and response results.
This article walks through extracting service‑host mappings from MySQL using MyBatis, handling a Long‑to‑String conversion error, and demonstrates an insert‑select SQL pattern that requires column aliases to avoid duplicate‑column errors, complete with XML and Java code examples.
This article explains several reliable techniques for renaming a MySQL database—including full export/import with mysqldump, bulk table‑rename operations, and legacy binlog‑based approaches—while providing concrete command‑line examples, performance measurements, and step‑by‑step instructions for each method.
This article explains the actual logical execution order of SQL statements, clarifies common misconceptions about WHERE, GROUP BY, and SELECT, shows how column aliases affect processing, discusses optimizer reordering, and compares the order with LINQ and pandas query patterns.
This guide explains how to use MySQL's built‑in mysqlslap tool and the third‑party sysbench utility to simulate concurrent client loads, adjust server settings, run read/write and I/O tests, and interpret the resulting performance metrics for database optimization.
This article investigates the Alibaba Java Development Manual's recommendation against joining more than three tables by experimentally evaluating multi-table join performance in MySQL and Oracle using massive synthetic datasets, analyzing query execution times, indexing effects, and providing data generation scripts and detailed results.
The article details how to handle test user module data by converting ID fields to strings, validating optional parameters, and configuring complex MyBatis XML mappings alongside a Java bean, illustrating the full workflow from requirements to implementation and testing.
This article investigates Alibaba's recommendation against joining more than three MySQL tables by designing experiments with synthetic data, measuring query performance on MySQL and Oracle, analyzing index impact, and providing full DDL and data‑generation scripts to explain the rule's practical limits.
The article investigates the Alibaba Java development manual's rule that joins involving more than three tables are prohibited, by designing massive MySQL and Oracle experiments, generating billions of rows, measuring query times, analyzing indexing effects, and concluding that the rule is MySQL‑specific and driven by large‑scale performance limits.
This article demonstrates how to use SQL, including dense_rank and date arithmetic, to detect users who have recorded a specific event for at least seven consecutive days within a month, providing step‑by‑step queries and a complete combined statement.
This article describes the evolution of a real‑time computing platform from SQL 1.0 built on Spark Structured Streaming to SQL 2.0 powered by Flink‑SQL, covering dynamic tables, continuous queries, dimension‑table joins, cache optimization, DDL extensions, platformization, operational challenges and future roadmap.
This article explains what foreign keys are, how they enforce referential integrity in relational databases, the different actions (RESTRICT, CASCADE, SET NULL), their performance overhead demonstrated by benchmarks, and practical guidelines for simulating or using foreign keys in high‑concurrency applications.
