This article explains the root causes of MySQL character‑set garbling, illustrates the encoding/decoding steps during data insertion and retrieval, and provides practical methods—including dump‑reload and binary conversion—to prevent and repair corrupted data.
This article presents the evolution, architecture, and operational metrics of Beike Zhaofang's Hermes real‑time computing platform built on Apache Flink, detailing its business scale, SQL editors, task growth, monitoring, use cases, and future development directions.
This article explains MySQL's row size restrictions at both the Server and InnoDB layers, detailing the calculation methods, relevant source code paths, error messages, and practical guidelines for ensuring total record length stays within the 65535‑byte server limit and the 8126‑byte InnoDB limit.
This article explains the fundamentals of MySQL InnoDB transaction isolation levels, describes the four standard isolation grades, shows how InnoDB implements them in source code, and provides concrete case studies with SQL scripts to illustrate locking behavior and MVCC effects.
This article explains how MySQL's InnoDB engine applies locks for DELETE and SELECT statements, covering MVCC, snapshot and current reads, clustered indexes, left‑most prefix rules, two‑phase locking, isolation levels, gap and next‑key locks, and analyzes nine different index‑and‑isolation scenarios with detailed conclusions.
This article reviews the historical evolution, market size, and technical advantages of relational databases, explains the shift from centralized to distributed architectures, and highlights OceanBase's recent breakthroughs and community events as a modern distributed relational solution.
This article presents a concise collection of twelve actionable SQL tuning techniques—including using proper comparison operators, LIMIT 1, appropriate data types, breaking large statements, UNION ALL, consistent query formatting, avoiding SELECT *, indexing WHERE/JOIN/ORDER BY columns, pagination with LIMIT, and leveraging EXPLAIN—to help developers improve database performance.
This guide walks you through the essential daily maintenance tasks for GaussDB T clusters, covering ETCD startup, cluster health checks, host resource monitoring, tablespace usage, abnormal wait events, log inspection, and common error troubleshooting with concrete commands and SQL examples.
This article presents the design of Flink's batch processing architecture, its integration with Hive through a unified Catalog API, details the enhancements in Flink 1.10, outlines future work, and reports a performance test showing roughly seven‑fold speedup over Hive on MapReduce.
This article introduces Flyway as a database migration tool, explains its working principle, directory and naming conventions, supported databases, and provides detailed step‑by‑step instructions, best‑practice guidelines, and troubleshooting tips for safely managing MySQL schema changes in production environments.
This article explains the InnoDB logical storage architecture—including segments, extents, and pages—then introduces MySQL table partitioning concepts, details the main partition types (RANGE, LIST, HASH, KEY) with SQL examples, and discusses their performance implications for OLTP and OLAP workloads.
Apache Flink 1.10 introduces major performance and stability improvements, unified memory configuration, native Kubernetes session mode, enhanced Table API/SQL with production‑ready Hive integration, expanded Python UDF support, and a host of important bug fixes and connector updates, marking the largest community‑driven release to date.
This article explains how to identify and remove various kinds of duplicate data in MySQL—including fully duplicated rows, records with duplicate non‑key columns, and unwanted whitespace inside fields—by using SQL statements, table cloning, OS utilities, and regular‑expression updates, with performance measurements for each method.
This article explains how to integrate Apache Phoenix with CDH‑based HBase, covering Phoenix overview, version selection, parcel installation, HBase configuration, command‑line usage, mapping existing tables, creating schemas and views, building secondary indexes, and comparing different index types for performance optimization.
The article explains how Citus enables sharding and MapReduce‑style parallel query execution in PostgreSQL, showing performance gains, example bucket algorithms, and how standard SQL can replace custom MapReduce code for large‑scale data analytics.
The article provides a series of advanced MyBatis-Plus query examples, including table creation, data initialization, and four detailed cases that demonstrate the use of like, nested, in, and limit clauses with QueryWrapper, accompanied by SQL statements, Java test code, and execution screenshots.
This article walks through building a MySQL user table, defining the corresponding Java entity and MyBatis‑Plus mapper, and demonstrates basic query operations such as selectList, selectById, selectBatchIds, and selectByMap with full code examples and the generated SQL statements.
This article demonstrates how to use MyBatis-Plus for complex queries—including date filtering, sub‑queries, INNER JOIN, dynamic SQL with the apply method, and the inSql helper—while highlighting potential SQL‑injection pitfalls and providing complete runnable code examples.
The article explains PostgreSQL materialized views, their evolution, how they differ from regular views, demonstrates creating and refreshing them, and compares them with incremental rollup tables using upsert to help decide which method best fits performance and scalability needs.
This tutorial demonstrates how to create a User table, define the corresponding entity and mapper classes, and perform basic MyBatis-Plus queries such as selectList, selectById, selectBatchIds, and selectByMap, including the required SQL scripts and Java code examples.
This tutorial demonstrates several advanced MyBatis-Plus query scenarios—including combined AND conditions, nested expressions, IN clauses, and limiting results—by providing complete SQL setup, Java QueryWrapper code samples, execution logs, and result screenshots for each case.
This article demonstrates how to perform sophisticated MyBatis‑Plus queries—such as date filtering, manager name prefix matching, sub‑queries, INNER JOIN, dynamic SQL with apply, and inSql—while highlighting potential SQL‑injection risks and providing complete Java test code examples.
This article presents a series of MyBatis-Plus advanced query examples, including how to filter users by name patterns, age ranges, and email presence, and demonstrates ordering and combined conditions with complete Java test code and a sample User table schema.
This tutorial demonstrates how to perform advanced MyBatis-Plus queries—including subqueries, INNER JOIN, apply, and inSql methods—while highlighting difficulty levels, code examples, and the risk of SQL injection in dynamic query construction.
This tutorial showcases MyBatis-Plus advanced query techniques through three practical case studies, providing sample data, detailed SQL conditions, and complete Java test code snippets to help developers master complex database queries.
This tutorial walks you through installing the PyMySQL library, configuring connection parameters, establishing a Python‑MySQL link, and performing full CRUD operations—including creating tables, inserting, querying, updating, and deleting records—while also covering cursor methods and pandas integration.
This tutorial walks data analysts through a side‑by‑side comparison of common data‑manipulation tasks using pandas in Python and SQL, covering everything from basic selects and filters to joins, aggregations, unions, ordering, case expressions, and data updates with clear code examples.
The article recounts Olery’s five‑year evolution from a Ruby‑centric stack using MongoDB and MySQL to a PostgreSQL‑based architecture, detailing schema‑less pitfalls, database requirements, migration strategies, code adaptations, and the measurable performance gains achieved after the transition.
The article reviews Google’s F1 Query paper, describing its architecture, three execution modes, data source handling, extensibility features such as UDF servers and TVFs, and performance optimizations that enable a unified, enterprise‑wide SQL engine for heterogeneous big‑data workloads.
This article demonstrates how to enable DBLE's trace function, view detailed execution timing with `show trace`, and analyze performance bottlenecks across client, middleware, and MySQL layers, including a real‑world case study and optimization tips.
This article explains the concept of Hash Join in MySQL 8.0, compares it with Nested Loop joins, shows how to enable or force it with server variables or hints, and presents performance benchmarks that demonstrate its speed advantages on large datasets.
This guide provides a comprehensive overview of MySQL 5.7 monitoring techniques, covering connection parameters, long‑running transactions, metadata lock tracing, lock‑wait analysis, global read locks, memory usage, partitioned tables, database size summaries, table update status, primary key and index considerations, storage engine distribution, and real‑time load metrics.
This article details{32-64 words} OPPO's evolution from an offline data warehouse to a real‑time platform, describing the business scale, data‑mid platform architecture, migration strategy using Flink SQL, extensions like AthenaX, and practical use cases such as real‑time ETL, CTR calculation, and tag import.
Learn step-by-step how to query MySQL 5.7 and 8.0 to list the top three orders by amount for each month, using variables and ranking functions, with full SQL examples, explanations, and visual results.
A chaotic incident‑response story shows how a DBA and SA scramble through VPN glitches, broken jump servers, log hunting, ad‑hoc config tweaks, unexpected bugs, security scans, and frantic firefighting to finally restore a production system, highlighting the messy reality of modern operations.
This article provides a thorough introduction to data warehousing, covering warehouse creation, the ETL process, OLAP/BI tools, data cube concepts, common OLAP operations, and the three main OLAP architectural models (MOLAP, ROLAP, HOLAP).
This article analyzes ten typical situations that cause MySQL indexes to become ineffective—such as OR conditions, missing quotes, leading wildcards in LIKE, composite‑index ordering, built‑in functions, arithmetic operations, inequality operators, NULL checks, charset mismatches, and optimizer choices—and provides concrete examples, visual explanations, and practical solutions to restore index usage.
This article provides a thorough overview of MySQL optimization, covering the underlying philosophy, risk considerations, participants, optimization scope across hardware, system, application, and database layers, practical tools, command‑line techniques, and detailed tuning recommendations for both system and database parameters.
This article explains how DBLE's hint mechanism can be used to specify database routing, classify different hint types, and demonstrate practical operations such as invoking stored procedures and executing custom SQL statements on selected nodes.
This article explains the performance drawbacks of using UUIDs as primary keys in MySQL InnoDB tables, analyzes their impact on storage, I/O and CPU, and presents several mitigation strategies such as time‑ordered UUIDs, integer mapping, and alternative identifier schemes.
This article introduces the four main MySQL transaction types—ordinary, chain, nested (simulated), and autonomous (simulated)—explaining their behavior and providing concrete SQL examples that demonstrate how each type works in practice.
This article explains the fundamentals of LEFT JOIN in SQL, illustrates one‑to‑one, one‑to‑many, and many‑to‑many scenarios, compares ON versus WHERE conditions, and provides concrete MySQL and Hive examples with code snippets and visual diagrams to avoid common mistakes.
Apache Flink, the open‑source stream‑and‑batch processing engine, has surged to become one of the most active Apache projects, with rapid community growth in China, unified SQL capabilities, AI‑focused extensions, Kubernetes integration, and benchmark results that outperform Hive by up to seven times.
This article tackles the challenge of ordering hard‑coded role levels in a MySQL table by presenting ten distinct SQL solutions—including adding a helper column, using FIELD, FIND_IN_SET, CASE, subqueries, UNION, hash‑based formulas, and custom arithmetic—so that a "vice‑manager" (level 2) can be ranked above a "manager" (level 3).
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.
This article investigates the claim from Alibaba's Java Development Manual that joining more than three tables should be avoided, by setting up a MySQL 5.7 environment, generating massive synthetic data, executing multi‑table join queries, analyzing execution times, and comparing the results with Oracle, ultimately revealing the practical limits of MySQL joins on large data sets.
This article introduces the open‑source infobin utility for parsing MySQL binary logs, explains how it detects long‑running and large transactions, measures event generation speed, aggregates per‑table DML events, and demonstrates its usage with sample commands and output, offering a faster alternative to mysqlbinlog.
This tutorial walks through loading order data into pandas and SQL, then demonstrates how to retrieve current dates, extract date components, convert between readable dates and Unix timestamps, transform between 10‑digit and 8‑digit date formats, and perform date arithmetic using pandas, MySQL, and Hive.
This tutorial explains how DBLE splits tables using different rules, demonstrates cross‑database INNER, LEFT, and RIGHT JOINs with jump‑hash sharding, and details the distributed query plan and sorting optimization that reduces join complexity in a middleware environment.
The article explains that MySQL's count(1) and count(*) perform identical full‑table scans with negligible performance difference, while count(column) only counts non‑NULL values, and it discusses execution‑plan effects and optimal usage depending on primary keys and table size.
This article explains DBLE's query workflow, covering basic DML handling, simple query routing and result merging, as well as complex query processing with JOIN, UNION, sub‑queries, and sharding strategies, illustrated with diagrams and code examples.
This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.
This article outlines the three generations of databases—SQL, NoSQL, and NewSQL—explaining the limitations of traditional relational systems, the scalability and consistency trade‑offs of NoSQL, and how NewSQL combines distributed architecture with ACID guarantees, while also listing major NewSQL projects.
This comprehensive guide covers fundamental SQL Server operations such as creating databases, tables, indexes, and views, advanced techniques like set operators, subqueries, and linked servers, plus step‑by‑step instructions for replication, backup, compression, and automated synchronization using stored procedures and SQL Agent jobs.
This article compiles a comprehensive guide to MyBatis, covering typical mistakes such as misuse of # vs $, handling of dates and null values, multi‑parameter strategies, XML escaping, dynamic SQL pitfalls, and a balanced list of its advantages and disadvantages for Java developers.
Learn how MySQL 8’s hash join algorithm works by building an in‑memory hash table from the smaller table, probing the larger table for matches, and handling oversized tables through disk‑based chunk files, offering a more efficient alternative to traditional nested‑loop joins.
This article provides a comprehensive overview of Flink SQL window functions, detailing time‑based window types, their underlying implementation in the StreamExecGroupWindowAggregate operator, the processing flow of WindowOperator, timer handling, emit/trigger strategies, and practical code examples for Tumble, Hop, and Session windows.
This article explains how to use MySQL's EXPLAIN command, describes each column in its output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and Extra—provides syntax examples, discusses join type rankings, and outlines the command's limitations for query performance analysis.
To accelerate growth on Xianyu’s 20 million‑DAU platform, the team built an event‑driven rule engine with a SQL‑like DSL that translates user‑behavior streams into real‑time Flink/Blink queries, cutting rule development from four days to half a day and achieving sub‑5‑second processing latency.
This article explains the basic implementation of the SOAR SQL Optimizer plugin, demonstrates its lint mode output, and provides step‑by‑step instructions for installing and configuring the Vim, Sublime Text, and VS Code extensions so developers can receive automated SQL quality suggestions directly in their editors.
This article introduces the open‑source SQL optimization tool Soar, explains its core features, configuration methods, heuristic rules, multiple report formats, syntax checking, SQL rewrite capabilities, EXPLAIN analysis, markdown‑to‑HTML conversion, and how to clean up test artifacts, providing concrete command‑line examples throughout.
This article introduces Xiaomi's open‑source SOAR tool, explains how to download and install the binary or source versions, walks through quick‑start commands, details key YAML configuration options and output formats, and describes four typical usage scenarios for SQL optimization and rewriting.
This article explains how MySQL 8.0.18 introduces the hash join algorithm, describes its build and probe phases, disk‑overflow handling, usage instructions, optimizer switches, performance comparisons with block nested loops, and current limitations, providing code examples and practical guidance.
This guide provides a comprehensive overview of Oracle Data Pump utilities, detailing how to create directories, use expdp and impdp commands for exporting and importing schemas, tables, and full databases, explaining key parameters, interactive mode, and common error resolutions.
This article explains why MySQL 8.0 switched its default character set to utf8mb4 and provides two detailed migration plans—including configuration changes, data export/import, table and column conversion, and post‑migration testing—to help users safely upgrade their databases.
This article explains how Groovy, combined with Gradle, can serve as a native application server to synchronize environment‑specific property files with a MySQL registry, demonstrating practical build‑script configuration, file‑system traversal, and Groovy‑SQL database operations for robust CI/CD workflows.
This article explains how InnoDB auto‑increment values behave when the highest ID is deleted, after a MySQL restart, and when IDs are manually set, including experiments and guidance on handling overflow.
This article provides a comprehensive overview of ClickHouse, covering its typical application scenarios, inherent limitations, common SQL syntax, default values, data types, materialized and expression columns, and detailed explanations of its various storage engines such as TinyLog, Log, Memory, Merge, Distributed, Null, Buffer, Set, MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, and CollapsingMergeTree, accompanied by practical code examples.
This article explains how IO and CPU bottlenecks increase active database connections, describes horizontal and vertical sharding techniques, outlines practical steps and tools for implementing sharding, and discusses common challenges such as non‑partition key queries and expansion strategies.
This article explains the different forms of MySQL's COUNT function, compares COUNT(*), COUNT(1) and COUNT(column), reveals engine‑specific optimizations for MyISAM and InnoDB, and provides guidance on which form to use in practice.
This article explains the meaning of NULL in SQL, distinguishes the two conceptual types of unknown and not‑applicable, describes three‑valued logic, shows why IS NULL must be used instead of = NULL, and warns about pitfalls such as NOT IN with NULL and improper CASE expressions.
This article explains InfluxDB's Continuous Queries feature, covering configuration settings, basic and advanced syntax, execution timing, data range handling, result storage, and the use of RESAMPLE clauses with practical examples and code snippets.
This article details more than fifty practical issues encountered when migrating financial applications from Oracle to PostgreSQL—including character sets, comment syntax, numeric precision, VARCHAR handling, sequence limits, type casting, operator overloading, and connectivity—while offering concrete solutions, code examples, and performance tips.
This guide explores advanced MySQL techniques—including ON DUPLICATE KEY UPDATE for upserts, INSERT IGNORE to bypass errors, replacing subqueries with JOINs, handling LEFT JOIN pitfalls, efficient pagination strategies, multi‑table UPDATE/DELETE via JOIN, and the nuances of COUNT(*) versus COUNT(1).
This article explains JDBC fundamentals, detailing its core interfaces such as Driver, Connection, Statement, PreparedStatement, and CallableStatement, outlines transaction isolation levels, common methods, ResultSet handling, and emphasizes the importance of mastering JDBC for effective use of Java ORM frameworks.
The article explains that SQL queries are not executed in the order written—SELECT is evaluated fifth after FROM, WHERE, GROUP BY, and HAVING—illustrates this logical sequence with diagrams, discusses alias handling, optimizer reordering, and shows comparable patterns in LINQ, pandas, and dplyr.
This article investigates Alibaba's recommendation against joining more than three tables by designing a MySQL experiment with large synthetic datasets, analyzing performance results, comparing with Oracle, and providing full SQL and data‑generation scripts to illustrate the underlying reasons.
NL2SQL converts natural language questions into executable SQL, bridging the gap between users and databases; the article reviews its value, historical roots, academic positioning, major datasets, current models, challenges, and future directions, highlighting its potential to reshape data interaction across industries.
Apache Kylin is an open‑source, Hadoop‑based OLAP engine that provides sub‑second, multi‑dimensional SQL queries on massive datasets, with features such as cube pre‑computation, real‑time analytics, and seamless BI tool integration, and its latest v2.6.4 release adds numerous fixes and improvements.
The article shares a database expert’s experience with three types of Oracle bugs—internal errors, slow execution plans, and incorrect results—explains why they occur, offers concrete fixes such as patching and session parameters, and outlines essential skills for reliable SQL development.
This article examines a MySQL performance problem where frequent requests become unresponsive, analyzes processlist, system I/O/CPU metrics, and pstack traces to identify InnoDB thread concurrency and concurrency tickets as the root cause, then explains the relevant parameters, configuration guidelines, and a simulated test to demonstrate the solution.
This article explains the concept of database transactions, the ACID properties, details MySQL’s four isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—and provides step‑by‑step command‑line experiments demonstrating phenomena such as dirty reads, non‑repeatable reads, phantom reads, and serialization effects.
This article provides a comprehensive guide to MySQL InnoDB tablespace encryption, covering its architecture, supported plugins, configuration steps, key rotation, limitations, encrypted table operations, import/export procedures, and backup/restore methods using mysqlbackup and innobackupex.
This article describes how Autohome migrated its real‑time analytics from Storm to a Flink‑SQL platform, detailing the architectural design, development and operational advantages, practical use cases such as recommendation metrics, and future plans for ecosystem expansion and open‑source release.
This article offers practical advice for data development job interviews, explaining why Java is essential, comparing Java and Python, outlining required backend framework knowledge, discussing the role of SQL and data warehousing, and addressing work‑life concerns such as overtime and company size choices.
This article introduces mycli, a Python‑based MySQL command‑line client that offers easy installation via pip, advanced auto‑completion, high‑risk operation confirmations, customizable syntax highlighting, output paging, command bookmarking, logging, multiline editing, flexible credential handling, and additional features such as SSL support and query looping.
This article explains how Apache Kylin, an open‑source distributed analytics engine built on Hadoop/Spark, achieves sub‑second OLAP query performance through pre‑computed cubes, a layered cuboid generation algorithm, bitmap‑based distinct counting, dimension optimization techniques, and tight integration with HBase for storage and fast SQL querying.
The article describes how Didi's data science team partnered with Ant Financial to open‑source SQLFlow, a tool that translates SQL into Python for AI model training and inference, enabling analysts to use familiar SQL to run deep‑learning, XGBoost, and clustering models across diverse business scenarios.
This article explains how to calculate cumulative daily user totals in MySQL using user-defined variables, demonstrates the necessary SQL queries, compares them with a Java‑style algorithm, and highlights important considerations such as variable initialization and practical pitfalls.
This article examines common disputes in designing an order status column, analyzes business actions and state transitions, proposes a concise set of core statuses, discusses dictionary representation options, and recommends using VARCHAR2 with a numeric enum for scalable, maintainable database design.
This article explains how Apache Phoenix implements global secondary indexes using separate HBase tables, demonstrates index creation and data synchronization with example SQL, and provides design guidelines to optimize query latency and avoid full‑table scans in big‑data environments.
MySQL 8.0 introduces Hash Join for dramatically faster large‑dataset queries, supports in‑memory and spill‑to‑disk processing, can replace Block Nested‑Loop joins, and adds EXPLAIN ANALYZE with detailed runtime statistics and tree‑style output, highlighting the platform’s rapid evolution.
This article examines the most popular open‑source and commercial database systems in 2019, presents DB‑Engines rankings, explores single versus hybrid strategies, highlights popular SQL‑NoSQL combos, and identifies the most time‑consuming DBA tasks and key performance metrics.
This article reviews the concepts, criteria, and characteristics of major open‑source OLAP engines—including Hive, HAWQ, Spark SQL, Presto, Kylin, Impala, Druid, Greenplum, and ClickHouse—providing guidance on selecting the most suitable solution for various big‑data analytics scenarios.
The article walks through a Java solution for extracting multilingual data from an HTML table using regular expressions, handling spacing and encoding issues, splitting fields, and constructing INSERT statements to populate a country_code database table.
This article explains the functional and performance differences among MySQL's count(*), count(1), and count(column) expressions, illustrates how NULL values affect each, and provides a practical example with query results to guide optimal usage.
This article introduces Apache Phoenix as an open‑source SQL layer for HBase, explains its core concepts and performance benefits, provides step‑by‑step installation instructions, demonstrates basic SQL operations and shows how to use the Phoenix Java API with code examples.
A busy DBA can reduce weekend firefights by automating SQL code formatting, detecting injection risks, assessing performance impact, and fostering a collaborative review culture with developers through testing frameworks and knowledge‑sharing sessions.
This article introduces Apache Beam as a unified programming model for batch and streaming data processing, explains its architecture, core components, advantages, extensibility, and demonstrates practical usage with KafkaIO, BeamSQL, and AIoT scenarios across multiple runners.
This article provides a comprehensive overview of Apache Druid, covering its real‑time OLAP design, core features, six‑component architecture, segment storage model, data ingestion pipelines (including Tranquility and Kafka), native and SQL query interfaces, and practical tuning tips with code examples.
