This article provides a comprehensive overview of Hive as a Hadoop‑based data warehouse, explains its architecture, query‑to‑MapReduce translation, high‑availability design, and compares its batch‑oriented processing with Impala's low‑latency SQL engine for big data analytics.
This article explains MySQL's execution stages, defines SemiJoin, details how IN subqueries can be transformed into SemiJoin queries, outlines conversion constraints, provides example transformations, and describes the various execution strategies MySQL may use for SemiJoins.
This article explains how distributed middleware processes simple SQL, ER‑based joins, and cross‑database joins, analyzes their performance impacts, and provides practical recommendations for optimizing SQL statements by using shard conditions, limiting clauses, low‑cardinality columns, and table ordering.
This guide provides a concise review of fundamental database concepts—including definitions of databases, RDBMS, SQL, and tables—and walks through essential SQL query techniques, data manipulation commands, schema definition statements, and control language basics, offering practical examples for data analysts.
This article explains MySQL indexes—defining what they are, classifying them by structure, storage, and logic, illustrating InnoDB clustered and secondary index structures with examples, and covering advanced concepts such as covering indexes, the leftmost‑prefix rule, and index condition pushdown to improve query performance.
MySQL 8.0.14 introduces a read/write consistency feature for Group Replication, explaining why MGR can still be inconsistent, detailing the group_replication_consistency parameter with its five levels, their advantages, disadvantages, and appropriate application scenarios, plus example session settings.
This article explains MyBatis fundamentals including the difference between #{} and ${} placeholders, various pagination approaches, logical versus physical pagination, first‑ and second‑level caching, lazy‑loading mechanisms, executor types, pagination plugin principles, and how to create a custom MyBatis plugin with code examples.
The article examines why seemingly outdated technologies such as SQL remain valuable, critiques the overhyped promises of TypeScript and Docker, and urges developers to balance new trends with proven fundamentals for sustainable software engineering.
This article explains why Hibernate is used, defines ORM, shows how to view SQL logs, describes HQL/SQL/Criteria queries with code examples, discusses entity class design, caching layers, object states, session handling methods, and the requirement for a no‑argument constructor.
This article explains the main index structures used in MySQL—including hash tables, ordered arrays, search trees, and B+ trees—covers why InnoDB prefers B+ trees, the benefits of auto‑increment primary keys, and key concepts such as covering indexes, the left‑most prefix rule, and index‑pushdown for query optimization.
This article explains the concept of Temporal Table JOIN, its implementation in SQL Server and Apache Flink, provides DDL/DML examples, compares it with other join types, and discusses improvements to align Flink with ANSI‑SQL standards.
This article explains how Apache Flink's Interval Join solves time‑bounded join requirements more efficiently than unbounded joins, covering its syntax, semantics, state‑management considerations, and providing a complete Scala example with code and execution results.
This article explains the special JOIN LATERAL operator, compares it with INNER JOIN and correlated subqueries, shows how SQL Server implements it via CROSS APPLY, and demonstrates its support in Apache Flink using Calcite and user‑defined table functions with concrete code examples.
This weekly briefing introduces Apache Flink's continuous query mechanism, demonstrates how to integrate Kafka as a DataStream connector, provides an overview of Flink SQL features, explains the implementation and optimization of dual‑stream JOIN operators, and showcases the Table API with end‑to‑end examples.
This article provides a comprehensive tutorial on Apache Flink's Table API, explaining its concepts, core features, and a wide range of operators such as SELECT, WHERE, GROUP BY, UNION, JOIN, and various window functions, while offering complete Scala code examples, custom sources, sinks, and an end‑to‑end job that computes page‑view counts per region using event‑time tumbling windows.
This article explains the purpose and types of SQL JOIN operators, demonstrates their syntax and semantics with examples, compares traditional database joins to Apache Flink's streaming two‑stream join implementation, and discusses optimization techniques such as state management, shuffle handling, and join reordering.
This article provides an in-depth introduction to SQL, its history and ANSI standards, then details Apache Flink's SQL capabilities—including SELECT, WHERE, GROUP BY, UNION, JOIN, window functions, and user-defined functions—accompanied by extensive code examples and a complete end‑to‑end Flink job implementation.
This article reviews the shortcomings of MyCat in handling sharding, joins, inserts, and session variables, demonstrates how the DBLE middleware addresses those issues with better correctness, performance, security, and operational management, and discusses code‑quality improvements and automated testing practices.
Database indexes, akin to a dictionary’s table of contents, dramatically speed up data retrieval, and this article explains what indexes are, how composite indexes work, the left‑most prefix rule, and the difference between clustered and non‑clustered indexes, using clear analogies and examples.
This guide explains how to define duplicate rows, use GROUP BY and HAVING to locate them, and apply temporary tables and DELETE statements to remove duplicates while preserving the row with the smallest ID.
This article explains why the native streaming engine Apache Flink can offer a SQL API by showing that MySQL binlog events form a time‑stamped data stream that can be replayed as a dynamic table, establishing a lossless stream‑table duality useful for both batch and streaming queries.
This article explains the evolution of MySQL's data dictionary from pre‑8.0 scattered metadata to the unified InnoDB dictionary tables in MySQL 8.0, covering storage structures, dictionary caching, information_schema changes, serialized dictionary information (SDI), atomic DDL mechanisms, persistent autoincrement handling, upgrade considerations, and provides practical code examples.
An InnoDB deadlock occurs when concurrent UPDATEs on a table with a composite (c,d) index generate conflicting GAP and Next‑Key locks during internal delete‑plus‑insert operations, and the simplest resolution is to drop column d from the index, leaving a single‑column index to prevent the lock clash.
This article examines a MySQL 5.6 InnoDB deadlock scenario caused by concurrent batch INSERTs, detailing the table schema, test cases, lock acquisition phases, log analysis, lock compatibility matrix, and discusses why typical SQL‑level fixes are limited, suggesting index redesign or data sharding as mitigation.
When a critical Oracle 10g database on AIX spikes to 100% CPU, this detailed case study walks through emergency actions, AWR analysis, hidden bottlenecks, and practical tuning steps that reduced CPU usage by half and restored system stability.
This article introduces Apache Flink’s Table API and SQL, explains the TableEnvironment programming model, shows how to register tables and sinks, and provides two complete Java examples—WordCount and a file‑based aggregation—complete with code that can be downloaded for local testing.
This article shows step‑by‑step how to identify duplicate rows in a MySQL table—whether the duplication is based on a single column, multiple columns, or either column—and provides reliable SQL patterns, including GROUP BY with HAVING, UNION, sub‑queries, temporary tables, and a safe DELETE statement to clean the data.
A SAP month‑end CO module query suffered severe slowdown despite standard index hints, prompting a deep dive into execution plans, statistics, and a little‑known num_index_keys hint that dramatically improves IN‑list iterator access and reduces filter overhead.
The article explains seven SQL join techniques—including INNER, LEFT, RIGHT, FULL OUTER, and their exclusive variants—illustrated with diagrams and accompanied by complete SELECT statements that demonstrate how to combine rows from two tables based on matching keys.
An in‑depth MySQL case study shows how concurrent gap‑locks under REPEATABLE READ can cause a deadlock when two transactions delete non‑existent rows and then attempt inserts, includes full DDL, log excerpts, step‑by‑step analysis, and practical mitigation strategies.
This article explains the essential concepts, philosophy, participants, dimensions, tools, and step‑by‑step procedures for safely and effectively optimizing MySQL databases, covering hardware, system, application, and MySQL‑level tuning together with practical command‑line examples.
This article examines two MySQL deadlock scenarios under REPEATABLE READ isolation—one with a non‑unique index and another with a unique index—by presenting table definitions, test data, raw deadlock logs, and step‑by‑step analysis of lock acquisition and waiting, ultimately offering practical recommendations to avoid such deadlocks.
This article provides a comprehensive guide to MySQL indexes, covering fundamental concepts, various index types, creation and design principles, practical optimization techniques, and the underlying data structures, illustrated with example tables, SQL statements, EXPLAIN analyses, and visual diagrams.
An in‑depth guide to MySQL optimization covering the query execution process, risk‑aware philosophy, hardware and system tuning, essential diagnostic tools, practical SQL and index adjustments, and comprehensive configuration settings to boost performance while maintaining stability and security.
This article compiles 52 practical SQL performance optimization strategies—including indexing best practices, query rewriting, use of temporary tables, storage engine selection, data type choices, backup procedures, and diagnostic tools—to help developers write faster, more efficient database queries.
The article lists nineteen practical MySQL optimization methods—including using EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, reducing unnecessary sorting, preferring UNION ALL, avoiding ORDER BY RAND(), distinguishing IN/EXISTS, applying proper pagination, segment queries, avoiding NULL checks, using full‑text indexes, preventing expression‑based WHERE clauses, respecting left‑most prefix, forcing indexes, handling range queries, and optimizing JOINs—to improve query performance and resource usage.
Data Mill is a Spark‑Streaming‑based real‑time computation framework that abstracts tasks as DataFrames, enables SQL‑driven development, and supports DSP business requirements by reducing latency to 15‑30 minutes while providing a scalable architecture, caching strategy, and automated fault handling.
This article walks through Youzan's real‑time platform architecture, explains why Flink was chosen over Spark Structured Streaming, details practical challenges such as container over‑provisioning and monitoring overhead, shares solutions for Spring integration and async caching, and outlines future directions for SQL‑based streaming and scheduler improvements.
TDH 5.2.3 introduces a series of stability and performance upgrades—including transaction and compaction optimizations, enhanced error handling, SQL length protection, improved Oracle‑compatible UDFs, default resource pool support, Guardian caching, TxSQL monitoring, and workflow and OLAP engine fixes—aimed at delivering a more reliable big‑data platform.
This article explains how Apache Ignite, a memory‑centric data platform with strong SQL support, can replace costly MySQL join queries, reduce data‑sync time from over 20 minutes to 15 minutes, improve performance fourfold, and outlines architectural changes, SQL advantages, current limitations, and future scaling plans.
This article outlines the philosophy, risks, participants, and step‑by‑step strategies for MySQL optimization, covering query flow, hardware and system tuning, configuration parameters, monitoring tools, and SQL‑level improvements to achieve stable, high‑performance database operations.
This article explains how relational optimizers validate, analyze, and transform SQL statements into executable code, detailing the four-step optimization process, query‑analysis factors, cost‑based access‑path selection, table scans, index lookups, and the role of data prefetching in improving performance.
This article explains the three classic concurrency anomalies—dirty read, non‑repeatable read, and phantom read—describes the four standard isolation levels (read uncommitted, read committed, repeatable read, serializable), and shows how databases implement isolation using rollback logs and read‑views.
This article presents 19 practical MySQL optimization tips, ranging from using EXPLAIN and proper indexing to rewriting IN/EXISTS queries, avoiding costly ORDER BY RAND(), improving pagination, leveraging full‑text indexes, and applying join and execution‑plan strategies for faster, more efficient database performance.
This article provides a step‑by‑step tutorial on using MyBatis3 annotation‑based CRUD, mapping, and provider annotations to replace XML configuration in a Spring Boot project, covering basic annotations, result mapping, dynamic SQL, Maven dependencies, configuration files, entity classes, database scripts, controller code, and testing procedures.
This article explains the complete MyBatis initialization process, detailing how the XML configuration file is parsed into a Configuration object, how SqlSessionFactory and SqlSession are built, and highlights the Builder design pattern used throughout the setup, with code examples for manual configuration loading.
This article examines a MySQL data‑update mishap caused by misplaced quotation marks, showing how such syntax errors turn intended string assignments into zero values, and explains the underlying expression evaluation that leads to all rows being updated to 0.
A developer ran a series of UPDATE statements that appeared correct, but due to misplaced double‑quotation marks the WHERE clause was parsed incorrectly, causing every row's column to be set to 0; the article explains MySQL's expression evaluation, shows binlog analysis, and offers practical debugging tips.
This article provides practical guidance for Java backend interview preparation, emphasizing solid Java fundamentals, hands‑on framework experience, distributed system knowledge, database performance tuning, core data‑structure and concurrency concepts, Linux log troubleshooting, and how to showcase real project achievements.
This article explains why SQL auditing is essential, outlines its core principles, dimensions, scoring model, and workflow, and provides practical guidance on building a self‑service, visualized, and automated audit solution that improves database reliability and developer productivity.
This article explains how to select the right index for mixed‑type queries, quantitatively compare query performance with and without LIMIT, and safely handle long‑running DELETE statements by using InnoDB status and proper kill techniques.
This article explains how to install the IP4R PostgreSQL extension via PGXS, describes its custom IP address and range data types, demonstrates common queries, functions, operators, and GiST indexing techniques, and compares its performance against bigint‑based lookups for efficient IP range handling.
This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and sorting, detailing key_len calculation, index key vs. filter vs. table filter concepts, and practical examples with BETWEEN and ORDER BY.
This guide presents four practical MySQL performance‑boosting techniques—selecting optimal column types, replacing sub‑queries with JOINs, using UNION instead of temporary tables, and employing transactions with BEGIN/COMMIT/ROLLBACK—to make queries faster and ensure data integrity.
HybridDB for MySQL delivers millisecond‑scale, multi‑dimensional filtering on billions of rows with hundreds of metrics by combining a high‑performance columnar engine, automatic composite indexes, and a fused MPP‑DAG pipeline, turning half‑day push preparation into seconds while supporting full SQL, spatial, and JSON data.
This article walks through diagnosing unexpected Oracle parallel execution, resolving DBLINK‑induced plan anomalies after a database upgrade, and fixing ORA‑00604 errors caused by missing domain indexes, providing step‑by‑step queries, parameter checks, and corrective ALTER statements to restore normal performance.
A developer’s series of MySQL UPDATE statements unintentionally set a column to zero due to misplaced quotation marks, and the article explains how MySQL parses such syntax, the resulting data loss, and best practices to avoid similar errors.
This article explains how InnoDB stores data pages, builds page directories, and uses B+‑tree indexes—including clustered, secondary, and composite indexes—to enable fast record lookup, while also covering MyISAM indexing differences and the SQL statements for creating and dropping indexes.
This article explains how to use pessimistic (FOR UPDATE) and optimistic (version field) database locks in a Java SSM red‑packet application to prevent over‑issuance, details implementation steps, code changes, performance testing, and the trade‑offs between lock types.
This article explores various SQL engines and storage options for big‑data workloads, compares their performance and capabilities, shows practical code examples, and offers guidance on writing efficient SQL in complex data environments.
This article presents ten classic MySQL error scenarios—from connection limits and replication conflicts to permission issues and timeout settings—explaining their causes, illustrating them with screenshots, and offering concrete configuration tweaks and procedural steps to resolve each problem efficiently.
This article presents ten classic MySQL error scenarios—from connection limits and replication conflicts to permission issues and character‑set problems—explaining their root causes, diagnostic commands, and step‑by‑step solutions to help beginners and DBAs troubleshoot efficiently.
This article explains MySQL's XA distributed transaction mechanism, covering its 2‑phase commit process, the roles of resource and transaction managers, internal vs. external XA, essential SQL syntax, practical code examples, common pitfalls, performance considerations, and reference resources.
This tutorial explains why and how to use MySQL window functions, covering their concepts, syntax, various function families, practical examples such as ranking, distribution, lead/lag, first/last, and aggregations within dynamic windows, plus detailed SQL snippets and visual illustrations.
Xiaomi is open‑sourcing its Go‑based SQL Optimizer and Rewriter (SOAR) at OSCAR Days, a tool that automatically checks SQL quality, suggests index improvements, rewrites queries, provides EXPLAIN analysis, and integrates with editor plugins and a web self‑audit platform to boost database reliability and developer productivity.
This article explains how Common Table Expressions (CTEs) simplify hierarchical queries in SQL, covering both non‑recursive and recursive forms, providing step‑by‑step examples for retrieving child, parent, and root categories using MySQL 8 and PostgreSQL 10, along with test data and execution results.
This article details how Beike Zhaofang leveraged the ClickHouse columnar OLAP database for large‑scale user behavior analysis, covering its architecture, key features, performance benchmarks against other engines, data ingestion pipelines, custom UDFs for funnel and retention metrics, deployment setup, and future enhancements.
This article introduces the open‑source BI platform Redash, outlines its wide‑range data source support and visualization capabilities, and provides step‑by‑step Docker‑Compose installation instructions along with practical usage tips for building dashboards.
This tutorial walks you through logging into Navicat, creating a MySQL database, designing a table with fields and a primary key, inserting sample data, and running a simple SELECT query, all illustrated with screenshots for clear, hands‑on learning.
This article explains the concept of FUZZ testing, describes its two main techniques, presents the design and core functions of a self‑developed FUZZ testing tool, showcases real‑world test results on SQL and HTTP interfaces, and outlines future enhancements for broader applicability.
This comprehensive guide walks you through mastering MySQL by covering server configuration choices, storage engine selection, data type recommendations, index strategies, backup methods, logging options, and practical advice on triggers, views, stored procedures, and character set handling, all aimed at boosting performance and avoiding common mistakes.
This article explains fundamental MySQL concepts including primary, candidate, super and foreign keys, transaction ACID properties, view creation, differences among DROP, DELETE and TRUNCATE, index structures and types, join variations, normalization forms, optimization techniques, replication mechanisms, engine distinctions, and logging formats.
This article explores PostgreSQL’s advanced SQL capabilities—including Common Table Expressions (WITH), batch inserts, the RETURNING clause, UPSERT with ON CONFLICT, TABLESAMPLE methods, aggregation functions like string_agg and array_agg, and a range of window functions—providing practical examples, performance insights, and guidance for real‑world use.
Through detailed debugging and source code analysis, the author discovers that MyBatis's SqlSession.clearCache() does clear the first‑level cache, but the observed stale query results are caused by MySQL's default REPEATABLE‑READ isolation level, which can be resolved by setting the transaction isolation to READ COMMITTED.
This article explains how to use Bash shell scripts on Linux to automate MySQL database testing tasks such as creating tables, inserting, updating, and monitoring dynamic database states, providing step‑by‑step code examples and execution instructions for efficient test automation.
This guide explains how to safely archive massive MySQL tables by using a custom reimplementation of Percona's pt-archiver that creates a temporary copy, swaps tables via triggers, and moves old data to an archive, reducing disk usage without deleting original rows.
Apache Hive is an open‑source data‑warehouse system built on Hadoop that enables users to read, write, and manage large distributed datasets using SQL‑like queries, offering features such as ETL support, various file‑format connectors, extensible UDFs, and integration with tools like Tez, Spark, and MapReduce.
This article explains the importance of transaction isolation in ACID, describes the four InnoDB isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—covers their locking behavior, effects on replication, and performance trade‑offs, and offers guidance on choosing the appropriate level.
This article details the architecture and implementation techniques of T‑TDSQL’s dual‑temporal database system, covering full‑state data reading, history‑table creation, snapshot query syntax, transaction snapshot attributes, visibility algorithms, transaction‑state management, index design, and valid‑time creation, query, update, and delete operations.
A production Oracle database suffered widespread query failures because collected system statistics gave the optimizer incorrect I/O cost values, causing it to favor index scans over full table scans; the article details the diagnosis, root cause, and a simple fix by deleting the system stats.
This article provides a concise overview of Apache Hive, covering its definition, Hadoop background, architecture, query workflow, storage model, advantages, disadvantages, and a comparison with traditional relational databases, helping readers understand how Hive enables SQL-like queries on data stored in HDFS.
The article walks through two common Oracle performance problems—slow tablespace‑information queries and sluggish ASH data collection—explaining root causes, showing diagnostic steps, and providing exact SQL and PL/SQL scripts to gather statistics, clean up partitions, and restore fast query response.
This guide introduces Elasticsearch 6.3's native SQL support, demonstrates how to bulk‑load sample books, run SQL queries, apply ordering, limits, combine filters, translate SQL to ES DSL, and use the built‑in SQL client, providing a practical walkthrough for developers.
This article explains how T‑TDSQL leverages full‑temporal storage and snapshot‑difference techniques to perform incremental data extraction, precise account reconciliation, online flashback, and data replay, illustrating the underlying SQL commands, error‑handling tables, and practical benefits for internet finance workloads.
This article presents a comprehensive overview of Oracle partitioning, covering basic concepts, the evolution of partition features across versions, practical best‑practice scenarios, and the latest enhancements in Oracle 12c, 12.2, and 18c that improve performance, manageability, and availability.
This article provides concise MySQL command examples for creating unique and full‑text indexes, multi‑column indexes, views, triggers, inserting data, pagination, transaction handling, character set configuration, and basic service management.
This article provides a concise introduction to essential SQL concepts, covering database creation, manipulation, table design, constraints, indexes, and common DDL statements, making it a useful reference for beginners and experienced developers alike.
This article walks through choosing a web framework and database, introduces relational databases, explains design steps such as requirement analysis, table creation, primary key selection, relationship modeling (one‑to‑many, many‑to‑many, one‑to‑one) with SQL examples, and covers normalization forms, integrity rules, and indexing strategies.
This guide explains MariaDB 10.3 system-versioned tables, showing how to create them with PERIOD FOR SYSTEM_TIME, query historical rows using FOR SYSTEM_TIME clauses, restore previous data via export/import, partition history to reduce storage, and safely manage versioning in production environments.
This article recounts a May 2018 incident where a missing WHERE clause caused a full‑table update of user asset data, detailing the step‑by‑step recovery attempts, iterative testing, and the key lessons learned about backup, simulation, and cautious production operations.
This guide outlines a comprehensive, step‑by‑step strategy for preparing Java backend interview candidates—covering architecture, databases, core Java, multithreading, JVM, and practical tips to demonstrate senior‑level expertise.
This guide provides a comprehensive overview of MySQL fundamentals—including CRUD statements, table creation, index management, data manipulation, transaction control, stored procedures, and trigger creation—complete with syntax examples and practical tips for reliable database development.
The article describes how to build and use a sql-review-helper tool that automatically captures all SQL statements during CI, deduplicates them, runs EXPLAIN to generate fingerprints, and stores the results in a reviewable file so that DBA and developers can reliably review every new SQL before it reaches production.
This guide outlines best‑practice naming rules, storage engine choices, data type recommendations, and a collection of performance‑oriented MySQL techniques—including indexing strategies, query optimizations, pagination methods, and security measures—to help developers design efficient and maintainable database schemas.
This article explains the new MySQL 8.0 features for user and role management, including changes to authentication plugins, password encryption, dynamic SET PERSIST configuration, password expiration and reuse policies, and detailed SQL examples for creating users, granting privileges, and managing roles.
A mathematician‑turned‑engineer recounts his 2015‑2022 path from undocumented recommendation systems at Tencent, through high‑precision security models, reinforcement‑learning game AI, quantum‑ML studies, to large‑scale AIOps time‑series anomaly detection, offering practical lessons for anyone transitioning into machine learning.
This article presents a comprehensive set of MySQL optimization techniques—including query‑cache tuning, proper use of EXPLAIN, indexing strategies, avoiding costly functions, and leveraging prepared statements—to help developers eliminate database bottlenecks and boost web‑application performance.
This article demystifies MySQL query optimization by explaining the server's logical architecture, the end‑to‑end query processing flow, the role of the client/server protocol, query cache, parsing, optimizer, execution engine, and result delivery, and then offers concrete performance‑tuning recommendations on schema design, data types, indexing strategies, and specific query patterns such as COUNT(), JOIN, LIMIT pagination, and UNION.
This article introduces MySQL 8's Common Table Expressions (CTEs), explains their syntax and advantages over derived tables, and provides multiple practical examples—including recursive queries, table creation, and usage in INSERT, UPDATE, and DELETE statements—plus a Docker setup tip for MySQL 8.
This article presents essential MySQL command-line operations, database and table management statements, and compares equivalent SQL syntax in Oracle, covering date handling, string functions, and notable differences such as pagination and auto‑increment behavior.
