This article explains how database indexes improve query speed by leveraging storage principles, binary search, and B‑tree structures, discusses clustered versus non‑clustered indexes, outlines the trade‑offs of indexing, and provides practical SQL optimization tips to avoid full table scans and index misuse.
This article explains the step‑by‑step execution order of a standard SQL query, covering FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and LIMIT, and provides illustrative code snippets and diagrams to clarify each stage.
The preview release of SQLE version 2.2302.0-pre5 introduces optimized CloudBeaver integration, UI improvements, bug fixes, and provides detailed project information, release notes, and resources for database users and administrators.
This guide walks you through Oracle's storage architecture, explains when and why to use partitioned tables, details each partition type, and provides step‑by‑step SQL examples for creating, managing, and modifying range, list, hash, and composite partitions.
This article walks through a hands‑on experience of deploying Bytebase via Docker‑Compose, configuring test and production environments, adding database instances, linking them to projects, performing a schema change with SQL, verifying the update, and completing the associated work‑order, offering practical insights for database DevOps practitioners.
This article introduces Screw, a tool that automatically generates database documentation in HTML, Word, or Markdown for many popular databases, and shows how to integrate it with Maven and Java in just two simple steps.
This article explains the fundamentals of MySQL indexes, compares them to a library catalog, details various index types and their physical implementations, demonstrates how indexes dramatically speed up queries on large tables, and provides practical SQL syntax for creating and managing indexes.
The article examines Go’s database/sql connection pool implementation, showing how reusing connections cuts latency, explains idle/in‑use/closed state transitions, details configuration parameters such as MaxIdleConns and MaxOpenConns, demonstrates metric collection via Gorm DBStats for monitoring, and provides a stress‑test illustrating the impact of proper tuning.
The SQLE 2.2302.0-pre4 pre‑release introduces enterprise‑level operation‑record viewing, outlines the tool’s project background, provides download links, and lists new features, optimizations, and bug fixes in a detailed release‑notes format for database administrators and developers.
This article explains multi‑table SQL queries, detailing Cartesian products, various join types (inner, left/right/full outer, self‑join, natural join, USING), UNION operations, and provides practical examples and exercises to illustrate correct join syntax and performance considerations.
This article presents a comprehensive H5 (HTML5) tracking solution that details the planning of event‑collection points, the full data‑warehouse modeling process—including schema design, retention calculations, and SQL implementations—and the automatic data‑capture mechanisms needed to improve user‑behavior analysis efficiency across the product lifecycle.
This article explains how to configure Flink SQL to emit early‑fire results for tumbling windows, allowing real‑time aggregation of metrics like PV and UV, and provides complete example code, execution output, and a discussion of current limitations.
This article presents PDMan, a lightweight open‑source database modeling tool that simplifies schema design, supports automatic code generation for MySQL, Oracle and Java, offers version management, and provides step‑by‑step instructions for installation and usage to boost development efficiency.
This guide walks through preparing the environment, building and launching the OceanBase Config Server, registering it with an OB server cluster, installing and configuring OBproxy, and includes troubleshooting tips for common connection issues.
This article presents a collection of practical MySQL functions and SQL commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST, and mysqldump—explaining their purposes, usage patterns, and providing concrete example queries with results.
The article reviews the cooling of 2022 database funding, highlights Velox as a shared execution engine, examines ReadySet's transparent caching, profiles Neon’s serverless PostgreSQL, surveys other notable databases, and outlines emerging trends and predictions for 2023, offering a comprehensive technical and market analysis for developers and DB professionals.
This article examines eight common SQL pitfalls—from inefficient LIMIT usage and implicit type conversion to suboptimal joins and condition push‑down—offering concrete rewrite examples and performance‑boosting techniques that can shrink query times from seconds to milliseconds.
The article explains why auto‑increment primary keys are unsuitable for sharded databases, recommends globally unique ordered identifiers, and details index and global‑table strategies—including redundant tables, index tables, and embedding shard information in keys—to achieve efficient single‑shard queries.
This article explains the evolution of data storage, the fundamentals of computer storage devices, how database indexes function like a book's table of contents, the role of binary search, the benefits and drawbacks of indexes, clustered versus non‑clustered indexes, and common SQL optimization techniques.
This article demonstrates how to deploy OceanBase 4.X in a minimal single‑node configuration, explains the key resource parameters, provides the necessary YAML configuration and command‑line steps to start the server, create a MySQL‑compatible tenant, and verify resource usage and basic database operations.
This weekly community bulletin curates high‑quality technical shares and release notes from the MySQL and DBLE ecosystems, highlighting new features, bug fixes, and upcoming development plans while inviting reader feedback and questions about the middleware tools.
The article introduces the SQLE project, details the new preview version 2.2302.0-pre2 with features such as external data source import and DMP integration, and provides the complete release notes, optimization items, links to previous versions, and additional reading resources.
This article explains MySQL's transaction concepts, the four isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their effects on dirty reads, non‑repeatable reads and phantom reads, how they are implemented with locks and MVCC, and provides practical SQL examples and diagrams.
Dolphin Streaming provides Alibaba’s advertising merchants with a DB‑like, SQL‑driven real‑time data platform built on Flink that abstracts storage and compute, enabling non‑engineers to develop, debug, and deploy streaming feature jobs quickly, boosting query volume, QPS, and revenue.
This guide compiles a comprehensive set of MySQL command‑line scripts for exporting whole databases, individual tables or schemas, importing data, and performing common database and table operations such as creation, selection, alteration, deletion, granting privileges, and viewing structure, all illustrated with ready‑to‑use code examples.
This article provides a comprehensive guide to MyBatis dynamic SQL tags—including foreach, if, choose, trim, selectKey, and related constructs—explaining their attributes, usage scenarios, and offering detailed Java and XML code examples for building flexible and error‑free database queries.
This comprehensive guide walks you through 13 practical MySQL techniques—including group_concat, char_length, locate, replace, now, insert‑select variations, on duplicate key update, show create table, explain, show processlist, and mysqldump—complete with clear examples, SQL snippets, and result screenshots to help you manage and optimize your data effectively.
The preview release of SQLE 2.2302.0-pre1 introduces manual deployment support, UI enhancements, and several bug fixes, providing a detailed overview of the project's purpose, new functionalities, and complete release information for database users and administrators.
This article walks through practical MySQL index usage, illustrating space and time costs, back‑table overhead, covering indexes and index condition pushdown, common scenarios where indexes fail, and concrete best‑practice guidelines, all demonstrated with a 5‑million‑row InnoDB table and real SQL examples.
This article demonstrates how to build multi‑dimensional data cubes on JD's big‑data platform using Hive, comparing UNION ALL with the more concise WITH CUBE, GROUPING SETS, and WITH ROLLUP functions, and discusses practical pitfalls and optimization tips.
This guide walks through a collection of practical MySQL functions and commands—including GROUP_CONCAT, CHAR_LENGTH, LOCATE, REPLACE, NOW, INSERT…SELECT, INSERT…IGNORE, SELECT FOR UPDATE, ON DUPLICATE KEY UPDATE, SHOW CREATE TABLE, CREATE TABLE … SELECT, EXPLAIN, SHOW PROCESSLIST and mysqldump—providing clear syntax, example queries, result screenshots and key usage notes to help developers write more efficient and reliable SQL.
This article presents the background and challenges of large‑scale keyword hit statistics, explains why traditional MySQL solutions struggle, and details how ClickHouse’s columnar storage, vectorized execution, and distributed architecture provide fast, scalable analytics, including cluster setup, table schema, queries, and migration lessons.
This guide walks you through 13 practical MySQL techniques—from aggregating values with GROUP_CONCAT and measuring string length with CHAR_LENGTH, to locating substrings, replacing text, retrieving timestamps, bulk inserting data, handling duplicate keys, inspecting table structures, and using diagnostic commands like EXPLAIN and SHOW PROCESSLIST—complete with ready‑to‑run SQL examples and visual results.
The SQLE 2.2212.0-pre3 pre‑release introduces DingTalk‑integrated workflow approvals, adds phone‑number based user binding, and includes several feature enhancements, optimizations, and bug fixes, with full release details, download links, and references to prior versions and related resources.
This article explains Spark SQL's CacheManager, how it stores cached query results using InMemoryRelation, the ways to trigger and release caches, the internal data structures like IndexedSeq and CachedData, and the role of canonicalization in determining cache reuse.
This article explains why real‑time data warehouses are becoming essential, outlines their goals, compares them with traditional offline warehouses, and presents detailed design patterns, naming conventions, and case studies from Didi, Kuaishou, Tencent, Youzan and other enterprises, highlighting challenges and solutions for streaming, storage, and query layers.
This article explains why MySQL's traditional utf8 charset cannot store 4‑byte characters such as emojis, demonstrates the resulting insert errors, and shows how switching the database, system, and column collations to utf8mb4 resolves the issue while also providing a brief history of MySQL's charset implementation.
This article explains Presto’s origin as Facebook’s open‑source OLAP engine, outlines its key characteristics, advantages and drawbacks, describes its overall architecture and query flow, and provides a step‑by‑step guide for downloading, configuring, and launching a Presto cluster for fast interactive analytics.
This presentation details the design, implementation, and performance evaluation of parallel query in Tencent's cloud‑native database TDSQL‑C, covering product overview, workload challenges, query‑level concurrency techniques, underlying execution models, scheduling framework, and future enhancements.
This article explains how MySQL stores AUTO_INCREMENT values for InnoDB and MyISAM tables, describes the algorithm that updates these values during inserts, and analyzes why generated IDs may become non‑sequential due to unique‑key conflicts, transaction rollbacks, and batch insert strategies.
This article walks through a collection of practical MySQL techniques—including group_concat, char_length, locate, replace, now, insert‑select variations, pessimistic locking, on duplicate key update, show create table, explain, processlist, and mysqldump—illustrated with SQL examples and screenshots to help developers efficiently query, manipulate, and maintain their databases.
Learn how to design and implement a real‑time data warehouse for an app’s AB‑test monitoring using EasyData, covering data flow layers, CDC task creation, stream table registration, Flink SQL processing, and BI reporting, with detailed steps, code snippets, and practical tips.
The SQLE 2.2212.0-pre2 preview release introduces rule template import/export for cross‑platform reuse, adds a new role permission for SQL release, and provides detailed release notes including features, UI improvements, and bug fixes for this open‑source database auditing tool.
This article explains the functional differences between DISTINCT and GROUP BY in MySQL, analyzes their performance with and without indexes, describes how implicit sorting affects GROUP BY before MySQL 8.0, and provides practical recommendations for choosing the appropriate clause.
This article analyzes a MySQL 8.0 deadlock caused by concurrent update‑then‑insert logic that creates X GAP locks and conflicting insert‑intention locks, explains the lock trace, and proposes using INSERT ON DUPLICATE KEY to avoid the deadlock.
This article demonstrates how to build a complete end-to-end machine-learning workflow for taxi trip duration prediction by integrating OpenMLDB with Alibaba Cloud MaxCompute’s serverless services, covering environment setup, offline data ingestion, feature extraction, model training, deployment, and real-time online inference within 20 ms.
This article provides a comprehensive guide to MyBatis, covering its basic concepts, Maven dependency configuration, XML setup, entity and mapper definitions, execution flow using dynamic proxies, and a custom lightweight framework implementation with full Java code examples.
The article introduces the open‑source SQLE tool, outlines its project background, details the new IDE plugin support for MyBatis XML and SQL file auditing, and provides a comprehensive list of features, optimizations, and bug fixes included in the 2.2212.0-pre1 preview release.
This article explains how to overcome export failures caused by large data volumes in a legacy Java system by switching from default full-result JDBC reads to a forward‑only streaming approach using MyBatis, detailing environment setup, configuration changes, and complete code examples for controller, service, DAO, and mapper layers.
This guide details how to configure Alibaba Cloud Log Service alarms—setting one‑day tokens, handling 1024‑byte truncation, removing record limits with analysis statements, adding a 10‑second query offset for timeliness—and shows how to visualize the data in Grafana using SQL queries for multi‑line and pie charts with timestamp conversion and time‑series filling.
This article walks through a step‑by‑step troubleshooting of MySQL LOAD DATA LOCAL INFILE failures caused by date truncation, quoted CSV fields, and incorrect line terminators, showing how to enable local_infile, use enclosed‑by, @‑variables, and str_to_date to successfully import the data.
The article explains why transaction isolation is required, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads and phantom reads, and outlines MySQL's four isolation levels with their effects on data consistency.
This article demonstrates how to create a score table in MySQL, insert sample data, and implement ranking of students using both user‑defined variables for pre‑8.0 versions and modern window functions like RANK(), DENSE_RANK() and ROW_NUMBER() to handle ties and gaps.
This article explains how to use the MySQL innodb_metrics table to transform the unreadable output of the show engine innodb status command into clear, queryable metrics, including buffer pool statistics and CPU counters, and shows how to enable or disable specific counters as needed.
This article explains how to collect extensive MySQL performance metrics—including connections, buffer cache, locks, SQL status, statement counts, throughput, server configuration, and slow‑query logs—using only MySQL's native SHOW commands and the performance_schema, providing practical code snippets and optimization tips.
This article describes how to identify and fix Greenplum standby segment failures by inspecting segment status, comparing missing files, generating a recovery configuration, executing gprecoverseg commands, and verifying that all segments return to normal operation without service interruption.
This article introduces Apache Doris, an open‑source OLAP database, outlines its development history, typical application scenarios, key architectural components and performance optimizations, and then discusses the commercial Palo product and the planned future enhancements for both platforms.
This article provides a detailed, step‑by‑step tutorial on installing and configuring an OceanBase 4.0 cluster on Ubuntu 22.04 using the OBD (ob‑deploy) tool, covering prerequisite hardware, required commands, configuration file creation, deployment, startup, and basic verification.
This article presents 100 typical SQL interview questions covering basic queries, advanced techniques, and database design, illustrated with three sample tables (employee, department, job) and detailed answers, code examples, and analysis for both MySQL and Oracle implementations.
This article explains why many MySQL queries cause index inefficiency, details the leftmost prefix rule for composite indexes, illustrates common index‑breaking patterns such as SELECT *, functions, range queries, LIKE, OR, IN, ORDER BY, and introduces Index Condition Pushdown (ICP) as an optimization to reduce row lookups.
The article explains how Blink, Alibaba’s optimized Flink variant, uses dynamic tables and streaming‑SQL to ingest mobile telemetry via source tables, compute per‑minute metrics such as API success rates with tumbling windows, and write results to Alibaba Cloud Log Service, enabling real‑time dashboards and extensible use cases like fraud detection.
This article explains how to collect a wide range of MySQL performance metrics—including connections, buffer pool usage, locks, SQL statistics, statement counts, throughput, server variables, and slow‑query logs—using only MySQL's native SHOW commands and performance_schema tables, while also offering practical tuning tips and analysis tools.
This article walks through a MySQL performance case study, showing how nested subqueries on large tables can take hours, and demonstrates step‑by‑step how creating single‑column, composite, and covering indexes, as well as rewriting queries as joins, reduces execution time from tens of thousands of seconds to milliseconds.
This article describes Liulishuo's migration to a Flink‑based real‑time data warehouse, covering background, benefits, technology selection (storage, Flink platform, dimension table connectors), overall architecture, concrete Hudi and Elasticsearch ingestion examples, processing SQL, and future outlook for unified batch‑streaming storage.
The article introduces DuckDB, a high‑performance embedded analytical database, outlines its new release’s storage, performance, and memory improvements, describes its C/C++ integration and build process, and lists key feature changes such as parallel execution, novel compression methods, and enhanced SQL capabilities.
This article explains MySQL’s internal execution flow, covering the connector’s role, permission verification, caching, parsing, optimization, execution stages, processlist states, SQL execution order, and best practices for arranging WHERE‑clause conditions to improve query performance.
This article introduces Sharding-JDBC and ShardingSphere, explains their history, core architecture, vertical and horizontal sharding concepts, sharding rule configuration, SQL parsing and rewriting, routing and result merging, and provides a quick‑start guide with Maven dependencies and Spring Boot configuration for Java applications.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters such as emojis, demonstrates the resulting insertion error, and shows how switching to the utf8mb4 charset resolves the issue while also covering the historical reasons behind MySQL's limited utf8 implementation.
This article explains why MySQL's default utf8 charset cannot store four‑byte characters like emojis, demonstrates the resulting insert errors, and shows how switching to the utf8mb4 charset resolves the issue while providing a brief history of MySQL's encoding decisions.
This article explains why MySQL’s legacy utf8 charset only supports three‑byte characters, causing emoji insertion failures, and shows how switching tables, columns, and the database to utf8mb4 resolves the issue while detailing the historical reasons behind MySQL’s limited utf8 implementation.
This weekly roundup curates top database‑related technical articles from the MySQL community, shares the latest SQLE audit tool release and development progress, and invites readers to contribute feedback and questions about DBLE, DTLE, SQLE, and TXLE middleware.
This article presents a systematic approach to transform MySQL slow‑query handling from reactive troubleshooting to proactive risk management by defining a scoring model, selecting key metrics, calculating weighted risk indices, and testing the model to prioritize and reduce harmful queries.
This article details YiPay's big data BI analysis platform construction, covering its financial data use cases, platform architecture, OLAP engine implementations with ClickHouse, Presto, and Kylin, as well as identified challenges and future development directions.
This article explains user path analysis as a method to visualize and optimize user flow, describes its productization in the Hunyi analytics platform, details the underlying computation logic, presents a complex StarRocks SQL solution, discusses performance challenges, and suggests future improvements and recruitment opportunities.
Database testing ensures data integrity, security, and quality by validating data mapping, ACID properties, schema, triggers, stored procedures, and field constraints through both manual and automated methods, using SQL queries, CRUD operations, and specialized tools to verify that applications interact correctly with underlying databases.
Managing 20,000+ concurrent SQL queries requires moving beyond a single‑instance design; this guide explains why simple architectures fail, how to shard databases into multiple instances, route traffic using geographic hashing, and implement read‑write separation to sustain high read loads while keeping latency low.
This article presents a collection of practical T‑SQL techniques—including row‑to‑column pivots, column‑to‑row unpivots, string replacement, date/time conversion, division with percentage formatting, NULL handling, counting methods, UNION ALL inserts, cache inspection, plan cache clearing, line‑break handling, TRUNCATE vs DELETE, system diagnostics, and execution‑time measurement—each illustrated with complete SQL code examples.
This article explains the concept of distributed transactions, presents typical micro‑service scenarios that generate them, and demonstrates a complete solution using RocketMQ transaction messages together with SQL table definitions and Java code for producers, transaction listeners, and consumers to achieve eventual consistency.
This tutorial explains how to use PyCharm's built‑in database tool to connect to a MySQL server, configure connection settings, download missing drivers, test the connection, display all schemas, execute SQL statements, and remove the data source when needed.
Data warehouse modeling organizes data into layered structures—ODS, DWD, DWS, and ADS—to improve performance, reduce costs, ensure data quality, enable traceability, simplify maintenance, and support both batch and real‑time analytics, while outlining best practices for ETL processes and schema design.
This article explains how MySQL performs implicit type conversion during queries, demonstrates the unexpected results caused by joining mismatched column types, outlines the conversion rules, and provides practical tips to avoid performance degradation and incorrect data retrieval.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
This article explains MySQL's native data encryption capabilities, detailing the AES_ENCRYPT/AES_DECRYPT functions, their parameters, storage considerations, example usage, and best‑practice recommendations for secure and efficient encryption of sensitive fields.
This article thoroughly explains how MyBatis binds mapper interfaces to XML files, details the step‑by‑step SQL execution flow—including mapper retrieval, statement preparation, parameter handling, and result mapping—and demonstrates how to create custom typeHandlers for both input parameters and result sets.
This article explains how MySQL uses indexes during JOIN operations, compares indexed and non‑indexed join execution, introduces Index Nested‑Loop Join and Block Nested‑Loop Join algorithms, discusses the role of the join buffer, and provides practical guidance on choosing the optimal driving table for efficient query performance.
This article explains MyBatis's first-level (SqlSession-scoped) and second-level (Mapper-scoped) caching mechanisms, their default behaviors, key generation, configuration options, and the impact of Spring integration, and provides recommendations for disabling caches in distributed environments to avoid data inconsistency.
This article explains MyBatis caching fundamentals, detailing first- and second-level cache concepts, configuration, cache invalidation scenarios, and custom cache integration with EHCache, supplemented by practical code snippets and quiz questions to illustrate cache behavior across SqlSessions.
This article walks through building a low‑code platform that automatically creates Spring Boot REST endpoints from plain SQL definitions, covering datasource configuration, dynamic request mapping, SQL execution, and optional Swagger documentation generation.
This article introduces xlite, a Rust‑based SQLite extension that lets you import Excel files as virtual tables and run full SQL queries for data lookup and analysis, outlining a simple three‑step workflow and noting current read‑only limitations.
The article explains the importance of clear SQL naming conventions, table and index creation rules, and general syntax guidelines in database DevOps, highlighting developer resistance to audits, the rationale behind each rule, and how tools like Bytebase with VCS integration improve the review experience.
This article explains MySQL’s three‑tier logical architecture—from client services to core query processing and storage engines—and details the four ANSI SQL transaction isolation levels, highlighting their behavior, advantages, and trade‑offs in MySQL.
The SQLE 1.2210.0 release introduces integrated CloudBeaver support for multiple database types, new MySQL audit rules, enhanced smart‑scan filtering, independent menus for scan tasks, along with numerous optimizations and bug fixes, and provides full release information and acknowledgments.
This guide explains why a single massive DELETE on a 500‑million‑row table creates long‑running transactions and locks, and shows step‑by‑step techniques—date‑range batching, primary‑key range slicing, and insert‑instead‑of‑delete—to break the operation into manageable chunks and improve performance.
The article explains cardinality counting in Presto, comparing exact set‑based methods with memory‑efficient bitmap, Linear Count, and HyperLogLog approximations, detailing their algorithms, implementation in Presto’s query engine, and offering best‑practice recommendations for choosing the appropriate technique in business workloads.
This article explains the functional differences between the run_sql and sql commands in MySQL Shell, demonstrates how to execute DDL/DML statements, bind variables, and retrieve results using both commands, and provides guidance on selecting the appropriate function for various use cases.
This guide walks you through integrating MyBatis with Spring Boot, covering basic CRUD operations, dynamic SQL constructs like if, choose, where, set, foreach, as well as advanced mapping techniques, pagination plugins, and practical code examples to help you master MyBatis in real-world projects.
Bilibili adopted the open‑source Kyuubi proxy to replace its unstable STS layer, enabling multi‑tenant, multi‑engine (Spark, Presto, Flink) SQL/Scala processing with Hive Thrift compatibility, fine‑grained queue isolation, UI monitoring, stability safeguards, and Kubernetes/YARN deployment, while planning further cloud‑native extensions.
This guide explains ClickHouse’s core concepts, application scenarios, table engine families, detailed engine configurations, SQL development standards, cluster architecture, ZooKeeper’s role, chproxy usage, client tool options, availability considerations, and performance‑tuning parameters for high‑throughput OLAP workloads.
This article explores the inner workings of SQL engines, detailing the end‑to‑end process from query submission through lexical analysis, parsing, abstract syntax tree generation with ANTLR4, and subsequent logical and physical plan optimization, while also demonstrating practical Java examples for SQL parsing and rewriting.
This article provides an in-depth overview of SQL window (OLAP) functions, covering their classification, syntax, window definition methods, differences from aggregate functions, and includes interview-style queries for user behavior and student score analysis.
