This article provides a comprehensive overview of MySQL data types—including numeric, floating‑point, decimal, string, date/time, binary, bit, enum, and set types—explaining their storage characteristics, appropriate use cases, and performance implications through practical examples and SQL demonstrations.
This article examines various MySQL pagination methods—including simple LIMIT, offset impact, sub‑query, ID‑range, and temporary‑table strategies—provides detailed performance measurements on a table with over five million rows, and offers practical recommendations for speeding up large‑scale data retrieval.
MySQL 8.0.19 adds two novel DML statements—TABLE for convenient full‑table scans and sub‑query replacements, and VALUES for generating in‑memory row sets—each with clear syntax, practical examples, and execution‑plan insights that help developers quickly adopt these features.
This article explains how Linux memory statistics (free, top) report buffer/cache usage, how to clear it with sync and drop_caches, and then dives into SQL join concepts, types, optimization techniques, and MySQL join algorithms such as nested loop and block nested loop, highlighting their impact on performance.
A detailed reference covering MySQL service management, connection commands, database and table operations, data types, indexes, joins, subqueries, transactions, backup, user privileges, and common SQL functions, complete with practical code examples.
This article explains the concept of multi‑tenancy, compares three data‑isolation strategies, and demonstrates how to configure MyBatis‑Plus, Spring Boot, and H2 to achieve transparent tenant filtering and automatic tenant‑ID handling using a custom SQL parser and context component.
This article introduces MyBatis, a Java persistence framework that separates SQL from code, outlines its key features, explains its layered architecture, and details the core components such as SqlSessionFactory, SqlSession, MapperProxy, Executor, StatementHandler, ParameterHandler, and ResultSetHandler with code examples.
This article explains the distinctions between UNION and UNION ALL, TRUNCATE and DELETE, TIMESTAMP and DATETIME, introduces composite and covering indexes, describes InnoDB versus MyISAM storage, the left‑most prefix rule, monotonic primary keys, int display length, SHOW INDEX fields, handling LIKE patterns, and efficient pagination techniques.
This tutorial explains MySQL 8.0 role concepts, demonstrates how to create roles, assign them to multiple users, activate default roles, configure role parameters, and revoke roles, providing step‑by‑step examples with complete MySQL commands and explanations for effective privilege management.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
This article explains core MySQL concepts, comparing UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP and DATETIME, composite and covering indexes, the left‑most prefix rule, clustered vs non‑clustered indexes, prefix indexes, InnoDB vs MyISAM storage, monotonic primary keys, column length display, SHOW INDEX fields, LIKE pattern pitfalls, and efficient pagination techniques.
This tutorial explains how to install MariaDB's Spider storage engine, create single-table remote links, configure users and servers, and leverage Spider for sharding and multi‑server table aggregation, providing practical code examples and performance considerations for distributed database setups.
An analysis of Indeed's 2014‑2019 US job data reveals that SQL, Java, and Python dominate employer demand, while AWS shows explosive growth, highlighting shifting trends in programming languages, cloud services, and data‑science‑related roles across the tech job market.
This guide explains essential MySQL concepts such as the distinction between UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP versus DATETIME, the purpose and benefits of composite indexes, index storage engines, prefix indexes, covering indexes, the left‑most prefix rule, and efficient pagination techniques.
This article shares the experience of deploying TiDB on the 360 Cloud Platform, covering background challenges with massive tables, TiDB's online DDL and high‑performance features, cluster architecture, DM migration workflow, common operational issues, and detailed tuning parameters to improve latency, QPS, and resource utilization.
The article documents a puzzling MySQL 5.7.25 bug where RAND() in a derived table is unexpectedly re‑evaluated in the outer query, explains the step‑by‑step reproduction, analyses the cause, and provides official work‑arounds for both MySQL 5.7 and 8.0.
This article walks through the entire Oracle‑to‑GaussDB migration process with Huawei DataSync, covering environment setup, tool constraints, NFS mounting, JDBC driver preparation, configuration file editing, password encryption, export and import execution, and post‑migration validation.
This article explains the nature of scalar subqueries, identifies three common performance pitfalls, and provides practical rewrite techniques—including LEFT/INNER JOIN, MERGE, and WITH‑MATERIALIZE—illustrated with three real‑world Oracle SQL cases to dramatically reduce execution time.
This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.
This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
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.
