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.
This article presents a step‑by‑step analysis of several real‑world MySQL slow‑query problems, demonstrates how to reproduce the issues, examines execution plans, applies concrete optimizations such as removing function calls, adjusting date comparisons, adding bigint indexes, and explains the impact of different EXPLAIN types on performance.
This comprehensive guide explains MySQL optimization philosophy, required participants, key dimensions, essential tools, step‑by‑step tuning procedures, hardware and system tuning, and detailed MySQL and InnoDB parameter settings to improve performance and reliability.
This comprehensive guide introduces Apache Phoenix as an HBase SQL layer, covering quick‑start steps, supported data types, DML syntax, salted tables to prevent hotspots, various secondary index types, bulk‑load methods, auto‑increment IDs, dynamic columns, pagination, query plan analysis, and data migration techniques.
This article walks through PostgreSQL’s query processing pipeline—from parsing SQL into relational algebra, through semantic analysis, query rewriting, optimization techniques like sub‑query elimination and join ordering, to the executor’s sequential scan algorithm—illustrating each step with code snippets and diagrams.
This article presents nineteen practical MySQL optimization methods—including using EXPLAIN, limiting IN clause size, specifying columns in SELECT, proper use of LIMIT, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, leveraging full‑text indexes, and applying join and index best practices—to improve query performance and reduce resource consumption.
This guide compiles nineteen practical MySQL optimization tips—from using EXPLAIN and avoiding costly IN clauses to leveraging full‑text indexes, proper pagination, and join strategies—providing concrete SQL examples and actionable advice to improve query performance and reduce resource consumption.
Quicksql is an open‑source, cross‑data‑source SQL engine built on Apache Calcite that provides a unified, safe, and fast SQL interface, enabling users to query heterogeneous storage systems such as Hive, MySQL, Elasticsearch, and Druid through command‑line, API, or JDBC connections.
The article presents 360’s challenges with heterogeneous, high‑volume data sources, explains the design of a unified federated SQL engine called QuickSQL that leverages Apache Calcite, Spark, Flink and other back‑ends, and evaluates its performance and future development directions.
This article explains PostgreSQL's jsonb type, its performance advantages over plain json, outlines its pros and cons, and demonstrates common operations such as inserting, querying, filtering, array expansion, containment checks, and indexing with practical book‑entry examples.
This article details the database table structures used for managing various homepage recommendation features—including brand, new product, popular product, subject, and advertisement tables—providing full MySQL DDL statements, field descriptions, and visual examples of both admin and mobile interfaces.
This comprehensive guide explains how SQL's declarative nature, the mismatch between syntax and execution order, table references, various join types, derived tables, GROUP BY behavior, and key keywords like DISTINCT, UNION, ORDER BY, and OFFSET work together to help readers master SELECT statements.
This article provides a comprehensive, ordered tutorial for preparing the environment, configuring the root and oracle users, adjusting kernel and system limits, and finally installing and initializing Oracle 12c on a Linux server using detailed command‑line instructions.
This article explains how to troubleshoot and tune Spark SQL configuration parameters—covering exception‑related settings such as spark.sql.hive.convertMetastoreParquet, file‑ignore options, and partition verification, as well as performance‑focused tweaks like broadcast join thresholds, adaptive execution, and parquet schema merging—while providing a comprehensive parameter reference table.
This article provides a comprehensive walkthrough of the coupon feature's database tables—including definitions for coupons, coupon history, product relations, and category relations—along with screenshots of both admin and mobile interfaces that illustrate how these tables are used in an e‑commerce system.
This article provides a beginner‑friendly, step‑by‑step tutorial on common SQL operations—including GROUP BY aggregation, JOIN types, CASE WHEN expressions, and useful built‑in functions—illustrated with clear examples, typical pitfalls, and ready‑to‑use code snippets.
This article explores the resurgence of SQL, tracing its historical roots, the rise and limitations of NoSQL, and how modern cloud and NewSQL solutions are re‑establishing SQL as the universal interface for data storage, processing, and analysis.
This guide walks you through MySQL's query process, the philosophy and risks of optimization, the key participants, core dimensions such as hardware, system, schema and SQL, essential diagnostic tools, emergency and regular tuning steps, system‑level monitoring, kernel and user limits, as well as detailed MySQL and InnoDB configuration parameters for achieving high performance and reliability.
This article explains how MySQL decides whether an index can be used for range queries, LIKE patterns, and ORDER BY operations, detailing key length calculation, index key vs. filter vs. table filter, and practical examples for BETWEEN and sorting scenarios.
This article explains the inner workings of relational databases, covering their architecture, indexing, time‑complexity analysis, query optimization, merge sort, client and query managers, caching strategies, transaction handling, ACID properties, concurrency control, and lock management.
The Ziru IM project uses Apache Flink to monitor real-time conversation metrics such as timely reply rates, average session duration, and message counts, employing two dialogue models and session definitions to enhance service quality and operational insight within an in‑app communication platform.
This article shares practical techniques for diagnosing and optimizing PHP interface performance, covering timing and memory measurement with ThinkPHP's G() function, profiling with Xdebug/Xhprof, code-level best practices, SQL query tuning using EXPLAIN, and considerations for database sharding and hardware upgrades.
