This article explains database isolation levels, including read committed, repeatable read, serializable, and snapshot isolation, discusses dirty reads, phantom reads, and how various systems such as SQL Server, PostgreSQL, MySQL, Oracle, MongoDB, and others implement or differ in these isolation semantics.
This article explains why MySQL queries become slow, how to enable and analyze slow‑query logs, and provides practical steps for index, SQL, schema, optimizer, and architecture optimizations to keep performance strong even with millions of rows.
This article details ByteDance's practical experience with Apache Flink, covering SQL extensions, a visual SQL platform, performance tweaks such as window mini‑batching and custom windows, join and checkpoint recovery improvements, stream‑batch integration experiments, and future roadmap plans.
The article explains the true execution order of SQL queries, detailing each step from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT, and illustrates the concepts with examples and code snippets.
This article explains the principles, advantages, and pitfalls of MySQL composite indexes, covering the left‑most matching rule, field order effects, when single columns can use an index, and whether a composite index can replace single‑column indexes, with practical EXPLAIN examples.
This article explains the MySQL JSON data type, demonstrates how to store structured and semi‑structured data with JSON objects and arrays, shows practical table designs for user login and profiling, and details virtual columns, function indexes, and multi‑valued indexes for fast querying.
The article analyzes four fundamental limitations of SQL—lack of stepwise execution, incomplete set semantics, missing ordered operations, and no object reference mechanism—illustrates them with real queries, and introduces SPL as a more intuitive language that overcomes these issues.
A practical MySQL InnoDB test shows that count(1) and count(*) have almost identical performance, explains the underlying index usage, and clarifies why MyISAM can count rows faster due to its storage design.
This guide details comprehensive naming conventions and design best‑practices for MySQL database objects—including schemas, tables, indexes, views, stored procedures, functions, triggers, constraints, and users—along with recommended storage engine, character set, field types, index strategies, and SQL usage rules to ensure consistency, performance, and maintainability.
Learn how to effectively sort query results using ORDER BY, apply ASC/DESC rules, combine multiple fields, use aliases, integrate functions, and limit result sets with LIMIT for pagination, while avoiding common pitfalls such as duplicate sorting values and expression misuse in MySQL.
This article introduces MySQL indexes, explains why they are needed, compares B+Tree and hash index structures, describes how indexes improve query performance, outlines their limitations, shows how to create single‑ and multi‑column indexes, and discusses the trade‑offs of using them.
This article presents practical MySQL optimization methods—including proper column definitions, NOT NULL usage, JOIN over sub‑queries, UNION, transaction handling, foreign keys, table locking, indexing strategies, and a collection of query‑tuning tips—each illustrated with clear examples and code snippets to improve database speed and reliability.
The article explains that using the logical operator AND between column assignments in a MySQL UPDATE statement causes unexpected results, demonstrates the issue with sample data, and shows that multiple columns must be separated by commas to update them correctly.
This article provides a detailed walkthrough of DataGrip, JetBrains' database client, covering installation, connection setup, UI customization, SQL execution, data import/export, and advanced editor features, helping developers efficiently manage and query various databases.
This article explains how to design and implement a fully elastic, zero‑ops Serverless database using Alibaba Cloud Tablestore, covering its architecture, key features, step‑by‑step CLI setup, table creation, data import, querying, indexing, and cost efficiency for high‑volume order workloads.
This article explains the concept of Slowly Changing Dimensions (SCD), discusses practical design questions, compares three change‑tracking requirements, presents three implementation patterns, and provides detailed Hive/SQL examples for historical data initialization and incremental updates in large‑scale data warehouses.
Youzan upgraded its real‑time computing engine from Flink 1.10 to 1.13 to meet rising SQL and containerization demands, gaining enhanced SQL syntax, time‑function handling, Window TVF standardization, Hive integration, K8s stability, elastic scaling, richer Kafka and format support, improved metrics and debugging tools, and successfully migrated all custom connectors, UDFs, and SQL jobs to the new Kubernetes‑based platform.
This article explains Spark SQL join classifications, the mechanics of Nested Loop Join, Sort‑Merge Join, and Hash Join, and describes how the Catalyst optimizer and Tungsten project improve query execution and memory efficiency in large‑scale data environments.
This article walks through implementing a complex e‑commerce search API, starting with a monolithic SQL query, then refactoring it into multiple indexed queries, and finally accelerating it with Redis set and sorted‑set operations, including pagination and transaction optimizations.
This article explains the origins of Spark DataFrames, compares them with RDDs, describes how Spark SQL optimizes DataFrame execution, and provides detailed examples of creating DataFrames from RDDs, files, and JDBC sources along with common DataFrame operations and code snippets.
This article examines seven typical scenarios where MySQL indexes unexpectedly fail—such as OR conditions, leading wildcards in LIKE, missing quotes, functions on indexed columns, NULL checks, arithmetic operations, and composite index ordering—providing SQL examples, execution plans, and practical tips to avoid costly full‑table scans.
This article explains why SQL queries can become slow, outlines MySQL's internal architecture, demonstrates how to use the EXPLAIN statement to view execution plans, and interprets key fields such as type, key, and Extra to help developers identify and optimize performance bottlenecks.
This article introduces Presto, an open‑source MPP SQL engine, explains its coordinator‑worker architecture and connector model, and provides detailed storage, query, and join optimization strategies—including in‑memory parallelism, dynamic plan compilation, and practical SQL code examples—to achieve low‑latency, high‑performance analytics on big data.
This article introduces the open‑source pg_recovery tool for PostgreSQL, explains how to install it, and demonstrates recovering data lost through UPDATE, DELETE, ROLLBACK, and DROP COLUMN operations with practical command‑line examples.
When a table grows beyond ten million rows, sharding it across multiple databases improves performance, but the usual LIMIT offset, pagesize pagination breaks, leading to missing or incorrect records; this article examines why simple merge approaches fail and evaluates global, secondary query, and no‑skip paging strategies, highlighting their trade‑offs.
This article reviews MySQL horizontal partitioning, explains five partitioning strategies (HASH, RANGE, KEY, LIST, COMPOSITE), demonstrates how to create and test partitioned tables, compares performance with and without indexes, and covers partition management operations such as adding, dropping, and rebuilding partitions.
This tutorial explains how to use Apache Flink 1.12 to construct a unified streaming‑batch data warehouse by integrating Hive via HiveCatalog and HiveDialect, performing read/write operations, configuring upsert‑Kafka sinks, and leveraging Flink CDC connectors for change data capture from MySQL and other sources.
This article explains why MyBatis foreach batch inserts can become extremely slow when inserting thousands of rows, analyzes the underlying SQL and PreparedStatement overhead, and demonstrates how using ExecutorType.BATCH together with a limited batch size (20‑50 rows per statement) dramatically improves insertion speed.
This tutorial shows how to quickly construct a streaming ETL pipeline that captures changes from MySQL and PostgreSQL using Flink CDC, enriches order data with product and shipment information, and writes the results into Elasticsearch for real‑time visualization in Kibana.
This article explains why indexes are vital for MySQL performance, how to decide when to add ordinary, composite, prefix, or unique indexes, the pitfalls of using non‑sequential primary keys, and advanced optimizations such as change buffer, index condition pushdown, and MRR to reduce I/O.
The article details the design, architecture, core features, scheduling logic, and performance gains of Vipshop’s self‑built Hera data service, which unifies data‑warehouse access, supports multiple engines, adapts SQL execution, and dramatically improves SLA for both B‑to‑B and B‑to‑C workloads.
The SQLE 1.2111.0 release introduces online DDL support, LDAP login, custom database extensions, PostgreSQL and Oracle audit plugins, several UI optimizations, and a long list of bug fixes addressing rollback generation, configuration password changes, CI triggers, and plugin rule execution.
This article examines seven typical scenarios where MySQL indexes fail—such as OR conditions, leading wildcards, missing quotes, functions, NULL checks, arithmetic on indexed columns, and composite index order—explaining why they happen and offering practical solutions to maintain query performance.
This article explains how MySQL InnoDB partition tables can dramatically boost query speed, simplify routine operations, enable parallel writes, and provide efficient data exchange and cleanup compared to non‑partitioned tables, illustrated with concrete SQL examples and performance metrics.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This article introduces chiner, an open‑source, cross‑platform database design tool built with React, Electron, and Java, compares it with PowerDesigner, walks through installation, core concepts, table and relationship management, and shows import/export capabilities for efficient schema development.
This article explains why MySQL queries become slow, how index design and common pitfalls affect performance, introduces MDL locks and large‑table strategies, and then compares Elasticsearch and HBase as complementary solutions for fast search and massive data storage.
This article demonstrates how to use Fluent Mybatis, native Mybatis, and Mybatis‑Plus to implement a complex student‑score aggregation query, compares their code structures, generation settings, and highlights the advantages and drawbacks of each approach for backend development.
After a colleague accidentally dropped a production database, we revoked DROP privileges for a MySQL user and systematically tested how this restriction impacts common operations such as renaming tables, using TRUNCATE, deleting rows, dropping objects, and recovering data via binlog, revealing surprising behaviors and practical workarounds.
This article explains MySQL's character encoding workflow, illustrates why queries containing Chinese characters fail without proper settings, and shows how to configure JDBC URLs, server variables, and Docker‑based MySQL instances to ensure lossless UTF‑8 handling.
The November 2021 DB-Engines popularity ranking, based on October data, reveals shifts among the top 20 databases, highlights score drops for MySQL, SQL Server and MongoDB, growth for PostgreSQL and MongoDB, and explains the five metrics used to calculate these rankings.
This article explains how to remove duplicate rows in SQL by using DISTINCT, GROUP BY, and the ROW_NUMBER window function, compares their syntax and performance, and provides concrete examples with a Task table and a Test helper table.
This article explains why duplicate rows appear in MySQL tables, demonstrates how to identify them with SELECT queries, and provides step‑by‑step SQL solutions—including safe deletion of all duplicates or retaining a single record per group—using subqueries and temporary tables for efficient cleanup.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, using UNION ALL, driving large tables with small ones, batch operations, LIMIT usage, index control, proper data types, and efficient GROUP BY—to help developers improve query speed, reduce resource consumption, and avoid common performance pitfalls.
This article explains MySQL index fundamentals, classifies indexes by data structure, storage engine, field characteristics and column count, compares B+Tree with B‑Tree, hash and red‑black trees, and demonstrates practical creation, usage, and optimization techniques with SQL examples and diagrams.
This article demonstrates how to implement a student score statistics query—calculating count, minimum, maximum, and average scores per term and subject—using Fluent MyBatis, native MyBatis, and MyBatis‑Plus, comparing code complexity, configuration, and generated code across the three approaches.
This article demonstrates how to create and use a sample employees table, explains composite index structures, and provides ten concrete MySQL index best‑practice guidelines—covering full‑value matches, leftmost‑prefix rules, avoiding functions, range conditions, covering indexes, inequality, NULL checks, leading wildcards, type conversions, and OR usage—each illustrated with EXPLAIN output and code examples.
An engineer’s oversight in a PlanetScale schema—omitting a foreign‑key‑based index—led to massive row‑scan costs, prompting a $5,000 bill, while the article also critiques junior developers’ naive schema designs and offers advice on proper database indexing and mentorship.
This article provides a detailed introduction to MyBatis, covering its relationship with JDBC, core components, execution flow, configuration, design patterns, caching strategies, plugin mechanisms, logging options, dynamic SQL features, and includes complete Java and XML code examples to illustrate practical usage.
This article explains the fundamentals of MySQL indexes, focusing on B+Tree structures, differences between MyISAM and InnoDB implementations, practical indexing strategies, configuration and SQL tuning tips, and provides a detailed case study with EXPLAIN analysis to help developers design efficient indexes.
The SQLE 1.2111.0-pre1 preview release introduces plugin-based support for arbitrary databases, provides a PostgreSQL plugin example, includes API adjustments, fixes a rule‑passing bug, and offers links to the source repository, documentation, and release information for users and administrators.
This article provides a comprehensive overview of Snowflake and Delta Lake, detailing their cloud‑native architectures, storage‑compute separation, transaction support, performance optimizations, and a side‑by‑side comparison of their capabilities, openness, and real‑time data handling.
This guide walks through SQL’s language categories—DDL, DML, DQL, DCL, and TCL—then demonstrates practical database and table management commands such as creating and dropping databases, defining tables with various constraints (not‑null, default, primary/foreign keys, unique, auto‑increment), and modifying or copying fields and tables.
This article explains how MySQL's SELECT ... FOR UPDATE statement adds a pessimistic lock, detailing when it results in row-level locking versus table-level locking based on the presence of indexes or primary keys, and demonstrates the behavior with multiple practical examples.
This article demonstrates how to extend MyBatis‑Plus to support ClickHouse-specific SQL operations by creating custom enum definitions, method classes, a SQL injector, a super mapper interface, and accompanying unit tests, enabling update, delete, and logical delete functionalities that differ from standard MySQL behavior.
This article explains the end‑to‑end big data processing pipeline, outlines the diverse challenges of data interfaces, storage and performance, introduces the unified "Three Ones" approach, details a three‑layer service architecture, and shows how low‑code orchestration can streamline API creation and composition.
The pre‑release version 1.2110.0‑pre1 of the open‑source SQL audit tool SQLE is announced, detailing its project overview, repository links, new feature (Online DDL support), UI optimizations, and bug fixes, followed by promotional links and unrelated personal commentary.
This article outlines the principles, recommended practices, and detailed steps for upgrading MySQL from 5.7 to 8.0, covering version compatibility, major 8.0 feature changes, pre‑upgrade checks, in‑place and logical migration procedures, and essential configuration adjustments with example commands.
This article explains MySQL InnoDB lock types, analyzes common deadlock scenarios with detailed SQL examples, describes the underlying locking mechanisms such as next‑key and gap locks, and provides practical prevention and resolution techniques for developers working with transactional databases.
This article explains how the MySQL system variable explicit_defaults_for_timestamp controls the default and NULL handling of TIMESTAMP columns, demonstrates the differences between OFF and ON settings with practical tests, and discusses why the parameter is being deprecated for stricter time‑data management.
This article explains MySQL index fundamentals, the B+Tree structure, various index types, the concept of covering indexes to avoid row look‑ups, the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite indexes, reduce index size, and improve query performance while avoiding costly table scans and sorts.
This article demonstrates how to use Fluent MyBatis, native MyBatis, and MyBatis‑Plus to implement a statistical query on a student_score table, compares their code complexity and generation settings, and provides practical code examples and a summary of each framework’s advantages and drawbacks.
This article explains how Flink SQL implements Top‑N queries, shows the standard SQL syntax, analyzes the logical and physical execution plans generated by the optimizer, and details the internal Rank node, optimization rules, state handling, and configuration options for efficient stream processing.
This article introduces MySQL 8.0’s enhanced JSON capabilities, covering data types, storage limits, indexing, core functions like JSON_SET, JSON_EXTRACT, path expressions, merging, and the JSON_TABLE feature, while providing practical code examples for developers.
This article introduces three MyBatis batch insertion techniques—looped single inserts, MyBatis‑Plus batch inserts, and native SQL batch inserts—provides complete implementation code, performance test results for inserting 100,000 records, analyzes the causes of differences, and offers practical solutions to common pitfalls such as MySQL packet size limits.
This article explains MySQL's native horizontal sharding using MERGE tables, covering the concepts of horizontal versus vertical splitting, table‑level sharding and partitioning, practical examples, advantages, limitations, and suitable use cases for MERGE tables.
After a client mistakenly imported 60,000 useless rows and a developer’s faulty delete command erased over 100,000 critical records, this guide explains how to use MySQL binlog to locate, extract, and replay the missing SQL statements, restore the data, and prevent future disasters.
When a MySQL table is sharded into multiple databases or tables, the usual LIMIT offset, pagesize pagination breaks, and this article examines why simple merge fails, then presents three practical approaches—global limit, secondary queries, and jump‑page restriction—highlighting their trade‑offs and limitations.
This article explains the fundamentals and structures of database indexes—including binary trees, red‑black trees, B‑Tree, B+Tree, hash indexes—and details how MySQL’s InnoDB and MyISAM engines implement clustered and non‑clustered indexes, covering their characteristics, storage files, and query behavior.
This article explores the evolution of data lakes, compares major cloud providers' lake architectures, introduces the emerging lakehouse concept, and provides a step‑by‑step Flink‑Iceberg implementation—including dependencies, catalog setup, table creation, checkpointing, and Kafka ingestion—demonstrating practical big‑data streaming solutions.
This article explains how to integrate the Dataway interface‑configuration tool into a Spring Boot project by adding Hasor dependencies, configuring Dataway properties, creating required database tables, setting up the data source, wiring Hasor modules, enabling Hasor in the application, and finally creating and testing a Dataway API without writing any business code.
This article provides a comprehensive overview of Apache Flink's major releases from version 1.7 to 1.14, detailing new APIs, state management improvements, Kubernetes integration, SQL and Table API enhancements, checkpointing advances, and performance optimizations that together illustrate the platform's evolution for both streaming and batch processing workloads.
This article explains why MySQL LIMIT deep pagination becomes slow with large offsets and presents four practical optimization methods—including subqueries, delayed INNER JOIN, tag‑recording, and BETWEEN…AND—demonstrated with real‑world production examples.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering key constraints, storage functions, GeoJSON support, indexing, in‑place updates, essential JSON utilities, path expressions, extraction, modification techniques, and the powerful JSON_TABLE feature with clear code examples.
The October DB‑Engines popularity ranking shows the same top‑10 databases as September, highlights score gains for MySQL and PostgreSQL, notes a year‑over‑year decline for the three leading systems, and reveals Snowflake’s rise into the top‑20, all based on five key popularity indicators.
This article explains MySQL indexes, covering their definition, benefits, various types such as B‑tree and B+‑tree, practical techniques for creating high‑performance indexes like prefix, composite, and clustering indexes, and how to inspect and maintain index information and fragmentation.
This article recounts a real-world incident where a client mistakenly imported 60,000 rows of erroneous Excel data into a MySQL production database, leading to an accidental bulk delete, and explains how the team used MySQL binlog to recover the lost data while offering practical safeguards to prevent similar disasters.
This article compares Fluent Mybatis, native Mybatis, and Mybatis Plus by implementing a student score aggregation query, showing code examples, generation settings, and a summary of advantages and drawbacks to help developers choose the most efficient ORM for complex SQL operations.
This article explains how MySQL implements trusted IP access control via user@host definitions, demonstrates creating and updating user accounts and associated metadata tables, and provides step‑by‑step procedures—including DCL commands, metadata updates, and handling of stored objects—to safely modify trusted IPs in production environments.
StarRocks 1.18 introduces Lateral Join + Unnest for elegant row‑to‑multiple‑row conversion, Fast Decimal with up to 38‑digit precision and 4× faster arithmetic, extensive Bitmap function enhancements, and a suite of import, storage, operator, and ecosystem optimizations that dramatically boost query performance and scalability.
The article examines a MySQL optimizer bug where a query with a WHERE clause on uid, ORDER BY id ASC, and LIMIT 1 incorrectly triggers a full table scan despite a usable composite index, explains the optimizer’s cost calculations, and offers workarounds such as FORCE INDEX or a harmless arithmetic trick to force index usage.
This article compares MySQL's INSERT INTO and LOAD DATA INFILE statements, explains their syntax, shows practical examples, highlights when each should be used for bulk data loading, discusses error handling with IGNORE, and explains why LOAD DATA INFILE is typically faster.
This article explains how MySQL 8.0 introduces strict JSON schema validation, demonstrates the json_schema_valid and json_schema_validation_report functions with array and object examples, and shows how to enforce schema checks in tables using CHECK constraints.
This article examines the business storage requirements of large‑scale advertising platforms and details how BaikalDB was architected as a cloud‑native, MySQL‑compatible distributed database that combines OLTP, OLAP, KV and full‑text capabilities while ensuring high reliability, low cost and linear scalability.
This guide explains how to deploy Apache Kyuubi on a CDH 6 cluster, replace HiveServer2 with Kyuubi, integrate Spark 3, apply necessary patches, configure environment and Spark settings, and optimize engine sharing for various workloads, providing complete code snippets and step‑by‑step instructions.
This article explains how to improve database insertion performance in Java by replacing per‑iteration inserts with MyBatis‑Plus’s saveBatch method, detailing dependency setup, schema creation, entity, controller, service, and mapper code for efficient bulk operations.
This article explains MySQL index fundamentals, B+‑tree implementation, the left‑most prefix rule, back‑to‑table issues, and provides three practical optimization cases with code examples to help developers design efficient composite indexes and avoid performance pitfalls.
This tutorial walks beginners through the fundamentals of SQL, explaining what it is, how to run simple SELECT statements, create tables, insert, update, delete records, use WHERE clauses, and even generate fun graphics like stars and calendars using advanced queries.
This article explains MySQL index fundamentals, different index structures, the concept of covering indexes and the left‑most prefix rule, and presents three real‑world optimization cases that illustrate how to design effective composite and partial indexes to avoid table scans and improve query performance.
The article analyzes a MySQL optimizer bug where a query with ORDER BY id ASC LIMIT 1 triggers a full‑table scan despite an applicable idx_uid_stat index, explains the cost‑based decision process, and presents two practical work‑arounds—using FORCE INDEX or a harmless arithmetic expression—to ensure the index is used.
This article outlines a comprehensive A/B testing workflow, covering historical issues, business test process improvements, detailed implementation steps, SQL validation scripts, data verification in analytics platforms, and practical notes to ensure accurate experiment data collection and analysis.
This article shares NetEase’s experience building a user‑profile platform with ClickHouse, detailing the business background, challenges of massive data and complex queries, core table designs, data ingestion, bitmap techniques, performance gains, and future plans for scaling and optimization.
The latest DB‑Engines monthly ranking shows the top nine databases unchanged, highlights a swap between Microsoft Access and Cassandra, notes declines for Microsoft SQL Server and MySQL, and analyzes popularity trends that suggest PostgreSQL and MongoDB may soon surpass SQL Server, illustrated with detailed charts.
This guide walks through exporting business databases, re‑initializing the MySQL instance, importing data, extracting GTID information, creating replication and monitoring accounts with helper functions, resetting logs, and finally re‑joining the node to a MySQL Group Replication cluster.
This article presents a concise guide to the most frequently used MySQL commands, covering how to list databases and tables, create and drop databases and tables, and perform essential CRUD operations such as inserting, querying, updating, and deleting records.
This article explains why GUI tools like phpMyAdmin may fail to display database lists when connecting to DBLE, outlines step‑by‑step troubleshooting using packet capture or the DBLE general log, and details the configuration parameters, commands, performance impact, and implementation mechanism of the general log.
This article summarizes the most useful MySQL 8.0 JSON features—including storage limits, indexing, in‑place updates, path expressions, merging, and table functions—providing clear code examples and guidance for developers who need to work with JSON data in MySQL.
The article explains why using LIMIT with a large OFFSET in MySQL causes severe performance degradation, demonstrates the issue with a 5‑million‑row table, and shows how rewriting the query as a join dramatically reduces I/O and execution time while also discussing buffer‑pool effects.
