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.
The September 2021 DB‑Engines ranking shows Oracle, MySQL and SQL Server plummeting while MongoDB, Snowflake and ClickHouse surge, highlighting a shift toward niche and cloud‑native databases, and the article also recommends ten essential database books for professionals.
This article details NetEase Games’ evolution of its Flink SQL platform, from the early StreamflySQL v1 template‑JAR approach to the v2 SQL‑Gateway architecture, discussing design decisions, challenges such as metadata persistence, multi‑tenant security, horizontal scaling, and job state management.
This article compares Elasticsearch and ClickHouse in terms of architecture, query capabilities, and performance for log analytics, presenting test setups, Docker‑compose configurations, query examples, and benchmark results that show ClickHouse generally outperforms Elasticsearch in most basic query scenarios.
The September 2021 DB‑Engines ranking shows major declines for Oracle, MySQL and SQL Server, rapid gains for Snowflake and MongoDB, and highlights emerging time‑series and key‑value databases like InfluxDB, Redis, and ClickHouse reshaping the database market.
The article explains ClickHouse's new Projection feature, how it overcomes MergeTree's single‑sort limitation and materialized view drawbacks, provides step‑by‑step commands to create, materialize, and query projections, demonstrates massive performance gains, and outlines the rules for automatic projection selection.
This article provides a comprehensive guide to Flink Table and SQL window semantics—including group, tumbling, sliding, and session windows—covers over windows, demonstrates how to define windows in SQL, explains built‑in functions, shows how to implement scalar, table, aggregate and table‑aggregate UDFs, and details Flink's integration with Hive, complete with Maven dependencies and runnable examples.
This article explains why duplicate rows appeared in production MySQL tables, demonstrates how to identify them with SELECT queries, and provides two SQL solutions—one to delete all duplicates and another to keep a single record per duplicated key—while preserving data integrity.
This guide explains how LEFT JOIN creates a temporary result set, compares the effects of applying a WHERE filter after the join versus embedding the condition in the ON clause, and shows practical SQL examples with sample tables and result analysis.
This article provides a comprehensive guide to Apache Flink's Table API and SQL, covering required dependencies, the differences between old and Blink planners, program structure, table environment creation, catalog registration, query execution, conversion between DataStream and Table, update modes, and time attribute handling, with Scala code examples throughout.
This guide explains how multi‑index support in Table Store enables SQL push‑down of filters, relational operators, aggregation functions, and LIMIT clauses, dramatically reducing query latency on non‑primary‑key columns and providing practical examples with a bike‑rental dataset.
This article explains the impact of slow SQL on database resources and user experience, outlines priority rules for addressing slow queries, details MySQL execution steps, identifies key performance factors, and provides concrete optimization methods including enabling slow‑query logs, indexing, I/O merging, and distributed architecture.
This article examines how Oracle, PostgreSQL, and MySQL implement and use database sequences for primary keys, covering explicit calls, trigger‑based, DEFAULT, and AUTO_INCREMENT methods, version support, and the effects of INSERT, UPDATE, DELETE, and TRUNCATE operations, concluding with a comparative summary.
This comprehensive guide covers essential SQL concepts, clause usage, aggregate functions, view creation, sequences, indexes, and a collection of classic interview questions with detailed solutions, helping readers deepen their database knowledge and ace SQL interviews.
This article explains why mastering SQL and PostgreSQL is essential for data scientists, outlines the core skills of the role, describes PostgreSQL’s features, lists its advantages and drawbacks for data science, and suggests resources for getting started.
This article explores TiDB performance optimization by examining layered approaches—hardware, software, and SQL—and scenario‑specific tactics such as read/write hotspot handling, OLAP challenges, and distributed transaction tuning, offering practical insights and real‑world case studies from industry experts.
This article provides a thorough introduction to MyBatis, including a complete beginner example with database schema, Maven configuration, XML and Java mapper files, and a demo program, followed by an in‑depth explanation of MyBatis’s lifecycle, core components such as SqlSession, Executor, StatementHandler, ParameterHandler, ResultSetHandler, and their interactions.
The article presents a step‑by‑step MyBatis tutorial that creates a sample user table, adds Maven dependencies, configures MyBatis, defines mapper XML and Java interfaces, demonstrates a test program, and explains core components such as SqlSession, Executor, StatementHandler, ParameterHandler, and ResultSetHandler.
Geohash encodes latitude‑longitude pairs into short base‑32 strings by recursively bisecting coordinate ranges and interleaving bits, allowing fast proximity queries via prefix matching, with precision controlled by string length, and is supported natively in Redis and useful for location‑based services.
This article explains the purpose and interpretation of each column in MySQL's EXPLAIN output, covering identifiers, select types, table sources, partition info, access types, possible keys, actual keys, key lengths, row estimates, filtering percentages, and extra execution details, with visual examples and code snippets.
The article explains why TiDB discourages large transactions, outlines the historical transaction limits, shows how to configure size thresholds, and provides concrete scripts for splitting big updates based on primary‑key characteristics to keep transaction size within safe bounds.
This article reviews the evolution of big data technologies in the last five years, covering streaming and batch processing frameworks, column‑store NoSQL databases, programming language trends, the cloud‑native multi‑model database Lindorm, and practical Flink/Blink usage with code examples.
This article introduces the six fundamental parts of Structured Query Language (SQL)—DQL, DML, TPL, DCL, DDL, and CCL—while promoting a comprehensive Alibaba Cloud developer course that covers each component with detailed lessons and practical examples.
This article compiles a comprehensive set of MySQL interview questions covering index types, the differences between MyISAM and InnoDB, the rationale for auto‑increment primary keys, sharding strategies, ACID properties, isolation levels, storage‑engine trade‑offs, normalization, index best practices, data types, performance tuning, locking mechanisms, B+Tree characteristics, and common SQL operations.
This article presents a detailed performance benchmark comparing MariaDB 10.4.12 and MySQL 8.0.19 on an i7 Windows 10 system, covering single and batch inserts, indexed and non-indexed queries, and analyzes speed and memory usage differences between the two databases.
This guide introduces DBeaver, a free open‑source database management tool, covering its cross‑platform installation, how to create and configure database connections (including JDBC driver handling), and the steps to generate entity‑relationship diagrams from existing databases.
This article explains why MySQL uses locks, describes table‑level, row‑level, and page‑level locking, outlines lock types such as shared, exclusive, and intention locks, and provides practical usage examples and common troubleshooting tips for InnoDB and other storage engines.
This article demonstrates how Fluent Mybatis can build complex SQL without XML, compares its implementation with native Mybatis and Mybatis‑Plus for a student score statistics query, and reviews code‑generation settings and feature overviews to help developers choose the most efficient data‑access framework.
This article presents a complete MySQL interview guide, including a script to create and populate tables for students, teachers, courses, scores, and grades, followed by 33 common interview questions with detailed SQL solutions covering selection, aggregation, joins, subqueries, and ordering.
Using MySQL's ORDER BY with LIMIT can yield an unexpected row order because duplicate values in the sorting column cause nondeterministic results; adding a unique secondary column like id stabilizes the order, and the official documentation explains how LIMIT influences query planning and optimization.
This article introduces ClickHouse, a high‑performance open‑source columnar database, explains its architecture versus row‑based systems, details migration from MySQL, showcases installation, performance benchmarks, data‑sync strategies, common pitfalls, and summarizes its benefits for large‑scale analytical workloads.
This article explains several MySQL techniques—INSERT IGNORE, ON DUPLICATE KEY UPDATE, INSERT…SELECT WHERE NOT EXISTS, and REPLACE—plus a practical MyBatis batch‑insert example, to handle large‑scale data imports while preventing duplicate records.
This article introduces ClickHouse as a column‑oriented OLAP database, compares it with traditional row‑based databases, and details a real‑world migration from MySQL that reduced query time from minutes to under one second, along with performance testing, sync strategies, and common pitfalls.
The article examines a real‑world scenario where a developer tried to reuse existing stored procedures to merely check the presence of Job, Certification, and Disclosure records, discusses the pitfalls of such an approach, and explains why storing this logic in T‑SQL is often fragile and hard to maintain.
This whitepaper explains the concept of in‑memory databases, traces their historical development, outlines core attributes and typical use cases in e‑commerce, live streaming and telecom, compares leading products, and provides technical and management recommendations for hardware and product selection as well as future trends.
This article explains how to retrieve sales statistics for multiple business units and their associated products from a MySQL table, compares several implementation approaches—including loop queries, OR concatenation, mixed filtering, and row‑comparison using MyBatis dynamic SQL—evaluates index usage, and provides the final recommended solution.
During a performance test a MySQL server showed one CPU core constantly at 99% while the others were idle, prompting a detailed investigation that checks I/O thread settings, multi‑core utilization, transaction locks, slow‑query logging, and index optimization to resolve the bottleneck.
This comprehensive guide covers fundamental SQL concepts, syntax, and terminology, then walks through CRUD operations, subqueries, joins, unions, functions, ordering, grouping, data definition, transaction control, permission management, stored procedures, cursors, and triggers, providing clear examples for each.
The article explains the drawbacks of strict relational normalization, introduces the concept of anti‑normalization (controlled redundancy) to simplify queries, demonstrates it with a set of employee, department, salary tables and four typical query scenarios, and shows how denormalizing the schema can dramatically improve MySQL query performance.
