This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article explains Flink's window table-valued functions (TVF), shows how they replace the old grouped‑window syntax with concrete SQL examples, describes the physical planning rules, introduces sliced windows for state efficiency, and presents a small incremental‑output improvement for cumulative windows.
The article asks how many tables can be created in a single MySQL database using the InnoDB storage engine, prompting readers to provide the correct limit without consulting external sources and discuss the theoretical maximum based on MySQL specifications.
Facing server‑hardware constraints, Bilibili’s data platform replaced wasteful full‑partition tables with a zipper‑table approach—preserving change history while cutting storage from petabytes to terabytes—and complemented it with Hudi + Flink CDC for near‑real‑time updates, dramatically lowering I/O, compute usage and latency.
This article explains two techniques for creating an org.apache.ibatis.jdbc.SQL object in MyBatis—using an anonymous inner class with an initializer block and using method‑chaining builder style—provides code examples, explains the underlying Java syntax, and summarizes their differences.
This article outlines several frequent MySQL query pitfalls—including missing table aliases in multi‑table operations, unsupported FULL JOIN syntax, unexpected REPLACE INTO side effects, auto‑increment edge cases, data‑type mismatches, nondeterministic functions, and warning‑driven query behavior—providing examples and best‑practice recommendations to prevent data loss and performance issues.
This article presents eight common MySQL error scenarios—including forgotten passwords, password policy violations, case‑sensitivity issues, service startup failures, export/import restrictions, connection limits, binary log overflow, and primary‑key replication errors—along with detailed troubleshooting steps and configuration commands to resolve each problem.
This article explains how to retrieve sales statistics for multiple business units and their associated products in MySQL by comparing rows, discusses alternative approaches such as loop queries, OR concatenation, and mixed filtering, and demonstrates why row‑by‑row comparison is the most suitable solution.
This article examines frequent SQL anti‑patterns—such as inefficient LIMIT usage, implicit type conversion, suboptimal UPDATE/DELETE joins, mixed ordering, unnecessary EXISTS clauses, condition push‑down failures, early limiting, and intermediate result handling—and provides rewritten queries that dramatically improve performance.
The article explains Propensity Score Matching as a causal inference method, detailing treatment effect concepts, required assumptions, score estimation, various matching algorithms, SQL implementation, quality assessment metrics, and how to estimate ATT using Difference-in-Differences, while outlining workflow steps, trade-offs, and alternatives.
This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.
This guide explains how to set up the Flink SQL client to work with Apache Iceberg, covering Scala version requirements, downloading and deploying Iceberg jars, configuring Hive and HDFS catalogs, creating databases and tables, performing insert and overwrite operations, and querying data in both batch and streaming modes.
This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.
The article explains Presto’s join processing by detailing the business need to limit multi‑table joins, then describing nested‑loop, sort‑merge, and hash join algorithms with Java examples, and finally showing how the Volcano model, columnar pages, and planner integration enable scalable, efficient OLAP join execution.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This article explains three common SQL deduplication techniques—using DISTINCT, GROUP BY, and the ROW_NUMBER window function—illustrating their syntax, performance considerations, and example queries for counting unique task identifiers in relational databases.
This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.
This article presents a collection of intermediate to advanced SQL interview problems covering grouping, window functions, sessionization, ranking, and join techniques, each accompanied by detailed solution queries and explanations to help data engineers prepare for technical interviews.
The article explains why using IN and NOT IN in SQL can lead to poor performance, incorrect results with NULLs, and index misuse, and demonstrates how EXISTS, NOT EXISTS, and JOIN provide safer and faster alternatives for database queries.
This article details Bilibili's end‑to‑end Presto‑based SQL‑on‑Hadoop architecture, covering overall system components, query routing, Presto feature set, extensive stability and availability enhancements, performance boosts through caching and multi‑datacenter deployment, and outlines future development plans.
This article explains how to replace parent‑id based tree storage with the nested set model, showing SQL queries to retrieve descendants, count them, detect leaf nodes, and perform insert, delete, and other operations, plus a JavaScript example for rebuilding the hierarchy.
This article explains how using IN and NOT IN in SQL queries can lead to poor performance, index misuse, and incorrect results—especially with NULLs—and shows how EXISTS, NOT EXISTS, and JOINs provide more reliable and efficient solutions.
This article provides a thorough guide to SQL fundamentals—including SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, aggregate functions, joins, set operations, and Oracle-specific objects—followed by a collection of common interview questions with detailed solutions, top‑N queries, and row‑to‑column transformations, while also containing promotional notes for related resources.
This article explains how to use the nested set (left‑right) model to efficiently query, count, and manipulate hierarchical department data in MySQL, providing SQL examples for retrieving descendants, ancestors, leaf detection, as well as insert, delete, and JavaScript tree‑building techniques.
Bilibili’s Presto on Hadoop combines a multi‑engine offline platform with Kubernetes‑managed YARN scheduling, Ranger security, and a custom dispatcher, scaling to over 400 nodes handling 160 k daily queries on 10 PB, while adding coordinator HA, resource‑group punishment, query limits, Alluxio caching, dynamic filtering, and numerous SQL‑level enhancements, with future auto‑scaling and materialized‑view automation.
This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.
This article recounts a series of real‑world challenges—including Excel‑to‑SQL imports, temporary table handling, Canal service failures, jar conflicts, and deployment mistakes—and explains the step‑by‑step solutions that restored system stability and delivered the required data to operations.
This comprehensive guide covers MySQL fundamentals including the three normal forms, permission tables, ACID properties, index design principles, SQL statement categories, sharding, deadlock resolution, isolation anomalies, view characteristics, SQL lifecycle, primary key choices, performance troubleshooting, replication, GTID, backup tools, and backup planning.
This article introduces Apache Kyuubi—a multi‑tenant Thrift JDBC/ODBC service built on Spark—detailing its architecture, advantages over Spark Thrift Server, real‑world use cases, open‑source community progress, and practical deployment strategies on mobile cloud, Kubernetes, and with Trino.
This article explains how to use the nested set model to efficiently query, count, and manage hierarchical department data in MySQL, covering recursive retrieval, leaf detection, insertion, deletion, and traversal techniques with practical SQL examples and JavaScript code for building tree structures.
This article presents a JD.com case study on applying Flink SQL for real‑time dimension modeling, detailing two complex streaming scenarios—full‑join of multiple streams and full‑group aggregation—along with the associated challenges of historical data handling, state management, and performance optimization, and proposes component‑based architectural solutions.
This article explains how to perform MySQL pagination manually in a Java backend using MyBatis by calculating offset and limit, adding total‑count queries, and exposing a REST endpoint that returns paged data and total records without relying on the PageHelper plugin.
This article introduces DBeaver, a free open‑source database management tool, walks through downloading, installing, connecting to various databases, managing tables, using the SQL editor, customizing the UI, generating ER diagrams, and concludes with a summary of its strengths for developers and DBAs.
This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.
Sequel Pro is a user‑friendly MySQL client that lets you connect via standard, socket or SSH methods, configure connection details, test and save them, and then explore databases through panels for selecting schemas, viewing table structures, contents, relationships, triggers, table info, and running queries.
This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.
This article explains how ClickHouse's AggregatingMergeTree engine uses the special AggregateFunction data type to pre‑aggregate data, demonstrates table creation, data insertion, and querying with state and merge functions, and shows how to combine it with materialized views for efficient analytics.
This article analyzes 2 million Taobao app user‑behavior records using AARRR funnel metrics and RFM segmentation, revealing daily and hourly usage patterns, conversion bottlenecks, product‑search mismatches, and offering data‑driven marketing recommendations to boost retention and sales.
This article explains the purpose and mechanics of PostgreSQL's VACUUM and AUTOVACUUM processes, demonstrates practical examples of table bloat, transaction ID handling, and provides step‑by‑step guidance for precise triggering and performance optimization of vacuum operations.
This article explains why a naïve Python‑based row‑deletion approach is slow for large MySQL tables and provides step‑by‑step SQL techniques—including identifying duplicate names, handling MySQL’s update‑from‑same‑table limitation, and deleting duplicates while preserving a single record per group—complete with executable code examples.
This article presents JD's real-time dimension modeling case using Flink SQL, detailing two complex streaming scenarios, the difficulties of handling historical data and state management, and a component‑based solution that leverages external KV stores and optimized Flink operators to improve performance and scalability.
This article explains how Sohu Smart Media built a high‑performance tracing system for microservices by integrating Zipkin for data collection with StarRocks for storage and analytics, covering architecture, data models, SQL queries, Flink processing, and real‑world results that boost observability and engineering efficiency.
This article compiles essential MySQL interview topics, covering normal forms, privilege tables, ACID properties, SQL statement categories, sharding, deadlocks, isolation anomalies, view characteristics, statement lifecycle, primary key choices, CPU troubleshooting, replication, GTID, backup tools, and backup planning strategies.
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 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 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.
