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.
This article walks through MySQL 8.0's key relational‑database enhancements, including invisible indexes for performance testing, the SET PERSIST command for persistent configuration, default utf8mb4 encoding, Common Table Expressions for clearer queries, and window functions for advanced ranking and aggregation.
This article shows how to identify and remove duplicate rows in a MySQL table by defining duplication, using GROUP BY with HAVING, creating temporary tables with MIN, and applying various techniques—including UNION, nested subqueries, and joins—to handle single‑column and multi‑column duplicate detection.
This guide explains why an Oracle DBMS_JOB can end up with a next execution date of 4000‑01‑01, how to interpret its status fields, diagnose the underlying errors, and restore the job using manual execution and log‑based troubleshooting steps.
This guide shows how to use SRVCTL commands and SQL queries to view the status of Oracle RAC databases, instances, services, node applications, ASM instances, and disk groups, as well as how to start and stop the entire RAC cluster safely.
This article explains how MySQL indexes work, why queries can become slow, how to interpret the EXPLAIN output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, and extra columns—and provides practical examples and optimization cases to improve query performance while balancing the cost of maintaining indexes.
This guide walks through configuring Oracle multi‑master data replication, covering site creation, group objects, propagator and receiver setup, purge scheduling, database link creation, replication group definition, table registration, conflict handling, and finally resuming replication, all illustrated with complete SQL code examples.
This tutorial demonstrates how to install PyMySQL, establish a MySQL connection in Python, and perform table creation, data insertion, and query operations using example code, while outlining required prerequisites such as credentials and database details.
This article presents thirty practical SQL tuning guidelines—ranging from avoiding certain WHERE‑clause operators and functions to proper index usage and set‑based alternatives—that help prevent full table scans and improve overall database performance.
This article compiles essential MySQL interview topics, covering ACID transaction properties, isolation levels and their issues, storage engine differences, index types, query execution order, lock mechanisms, temporary tables, normalization, read‑write splitting, performance tuning, and recovery logs, providing concise explanations and examples for each concept.
This article investigates the classic MySQL pagination “optimization” technique, reproduces tests on a low‑end server, compares plain LIMIT queries with the sub‑query rewrite on both clustered and non‑clustered indexes, and explains why the method sometimes improves performance and sometimes adds overhead.
The article explains that Oracle Enterprise Manager requires listeners with dynamic registration, shows how a static listener on a non‑default port causes a "database is down" error, and provides step‑by‑step commands to modify the local_listener parameter so both listeners register dynamically and EM works correctly.
This article explains the challenges of MySQL SQL auditing, compares semi‑automated and manual review methods, and introduces Inception—a MySQL‑compatible automated auditing, execution, and rollback system that addresses many of the pain points faced by DBAs.
Both MySQL 8 and MariaDB 10.2 introduce robust window functions and Common Table Expressions (CTEs), enabling developers to write more efficient, maintainable queries; this article explains their syntax, demonstrates practical examples, compares implementations, and highlights performance and readability benefits across popular database systems.
This article examines a high‑volume Oracle query scenario, explains why using multiple IN clauses on millions of rows leads to costly full‑table scans, and presents three alternative strategies—including a single‑SQL join, UNION ALL view, and temporary‑table approaches—along with practical examples and performance considerations.
This article outlines the most frequent SQL performance mistakes—such as misusing UPDATE, over‑selecting columns, unnecessary double queries, and improper use of GUIDs—and provides concrete, actionable techniques like CASE expressions, temporary tables, batch operations, and table‑valued functions to dramatically improve query speed and concurrency.
This guide explains Oracle's TX row lock mechanism, shows how to identify blocking sessions, locate the exact locked rows using system views and ROWID functions, and provides practical steps to prevent and mitigate TX lock waiting issues.
By switching to the InnoDB storage engine, allocating about 80% of system memory to its buffer pool, and configuring multiple buffer pool instances, you can significantly improve MySQL performance, with practical commands and guidelines for various memory sizes.
This comprehensive guide introduces MySQL fundamentals, walks through installation on Windows and Linux, explains core commands for connecting, creating databases, managing users and privileges, performing table CRUD operations, handling data types, and provides a curated list of analysis, backup, performance, HA, and GUI tools for MySQL.
This article traces the evolution of database systems—from early navigational models and Codd's relational theory through the rise of NoSQL and sharding, to the resurgence of NewSQL and the emerging HTAP paradigm—highlighting technical motivations, design trade‑offs, and future directions.
This article explains how Alibaba built a massive real‑time computing platform using Flink and its Blink extensions, detailing the challenges of ultra‑low latency, exactly‑once guarantees, and high throughput, and showing how these technologies powered the record‑breaking Double 11 shopping festival.
