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.
This article explains the standard SQL CORRESPONDING keyword, shows how HSQLDB implements it with UNION, INTERSECT and EXCEPT, demonstrates the optional BY clause for column selection, and discusses its limited support in other dialects such as PostgreSQL.
This article walks through the performance bottleneck of CPU in data analytics, introduces the Velox vectorized execution engine, and provides a detailed, zero‑to‑one tutorial for downloading Presto source, syncing Velox, fixing build paths, compiling both Java and C++ components, configuring CLion and IntelliJ, launching the servers, and executing SQL queries while noting stability concerns.
This article explains how to handle massive MySQL tables by splitting a single large table into multiple smaller tables, creating a MERGE (union) table to combine them, and using the merged view to implement efficient pagination, including necessary SQL statements and troubleshooting tips.
This article explains the logical execution order of SQL statements, highlights common performance pitfalls such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and demonstrates how to rewrite queries with JOINs, early range reduction, CTEs, and other optimizations to achieve dramatically faster execution times.
This article explains the limitations of ClickHouse for multi‑table joins, describes ByteHouse’s staged execution model, various join strategies (Shuffle, Broadcast, Colocate) and runtime filters, and presents performance benchmarks that show significant speed‑ups over the original ClickHouse engine.
This article introduces the ten most important programming languages for cybersecurity, explains why each language matters, and shows how mastering them can boost your ability to protect systems, detect threats, and build secure applications in an increasingly digital world.
This article explains the fundamental differences between row‑based and column‑based storage in databases, compares their write and read performance, lists pros and cons, presents an openGauss experiment with sample tables, and offers practical guidance on when to choose each storage model.
This article walks through MyBatis's complete query lifecycle—from configuration parsing, SqlSessionFactory creation, and mapper loading to the actual SELECT execution—while reproducing a pre‑3.4.5 bug caused by foreach variable leakage and presenting the official fix and upgrade recommendations.
This article analyzes why MySQL LIMIT pagination becomes slower on large tables, demonstrates the performance impact with real query timings, explains the underlying full‑table scan behavior, and presents three practical optimization strategies—including ordered primary‑key scans, sub‑queries, and join‑based approaches—to improve query speed.
This article explains how X‑Select’s Data Quality Platform (DQC) addresses common data quality problems in large‑scale data development by defining six quality dimensions, leveraging open‑source solutions such as Apache Griffin and Qualitis, and implementing rule definition, execution, alerting, and workflow interruption within a Spark‑based architecture.
This article provides a comprehensive guide on using Flink CDC with Debezium to capture MySQL changes, covering serialization, adding dependencies, configuring SQL client and Java/Scala APIs, creating source and sink tables, enabling checkpoints, and streaming data into a Hudi data lake.
This article analyzes the limitations of traditional BI multi‑dimensional analysis that relies on wide tables and complex SQL JOINs, introduces a new DQL language that redefines JOIN operations into three plus one patterns, and demonstrates how DQL simplifies data modeling, reduces errors, and enables truly self‑service analytics.
This guide presents thirty practical SQL Server optimization techniques—including proper indexing, avoiding full table scans, rewriting predicates, and minimizing temporary objects—to help developers write faster, more efficient queries and improve overall database performance.
This article explains why sharding tables improves read/write speed, then compares three pagination strategies—global query, no‑skip pagination, and two‑phase query—detailing their SQL rewrites, execution steps, performance trade‑offs, and practical examples.
This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
This guide presents 21 practical MySQL table design guidelines covering naming conventions, field types, primary key strategies, indexing, logical deletion, common columns, storage engines, character sets, time types, relationship modeling, sharding considerations, and SQL optimization tips for robust backend development.
This article introduces RobustDB, a client‑side read‑write splitting framework that replaces the problematic Atlas proxy by routing SQL statements to master or slave databases based on DML/DQL detection, using Spring, AspectJ, thread‑local context, and dynamic data‑source management.
This article provides a detailed tutorial on JetBrains DataGrip, covering installation, driver management, connection configuration, SQL editing shortcuts, data import/export, schema visualization, and various productivity features for efficiently working with relational databases.
This article examines the challenges of pagination after database sharding and presents three practical solutions—global query, jump‑page prohibition, and two‑step query—detailing their implementations, trade‑offs, and performance considerations for large‑scale order services.
This article explains why using LIMIT with large offsets slows MySQL queries, analyzes the execution flow, and presents four practical optimization techniques—including subqueries, INNER JOIN, bookmark (tag‑record) method, and BETWEEN range scans—backed by real‑world performance data and code examples.
This article breaks down the step-by-step SQL execution sequence—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, to LIMIT—illustrating each phase with examples, diagrams, and code snippets to help you understand how queries are processed and optimized.
This article explains how MyBatis‑Plus maps Mapper interface methods to executable SQL statements, detailing the auto‑configuration of SqlSessionFactory, XML parsing, mapper registration, SQL injection, and the dynamic proxy mechanism used at runtime.
This article reviews SQLite's 2018 enhancements, covering Boolean literals, window functions, the FILTER clause, UPSERT syntax, column‑renaming support, and other API changes, while comparing SQLite's capabilities to other major database systems.
This article explains why using OFFSET and LIMIT for pagination becomes inefficient as data grows, leading to costly full table scans, and demonstrates a high‑performance cursor‑based pagination alternative that leverages indexed primary keys to retrieve pages quickly and reliably.
This article explains why thoughtful database design is critical for scalable applications and outlines ten best‑practice guidelines—including stakeholder input, choosing the right DB type, consistent naming, normalization, documentation, security, scalability, stored procedures, modeling effort, and thorough testing—to help developers build robust, high‑performance data stores.
This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.
This article explains the architecture of Apache Doris, introduces Apache Hudi as a data‑lake format, compares Lambda and Kappa approaches, and details the design, implementation steps, and future roadmap for querying Hudi tables directly from Doris.
StarRocks is a high‑performance MPP database whose simplified FE/BE architecture, fully vectorized engine, and CBO optimizer enable fast multi‑table joins, while its partition‑bucket‑tablet storage model supports real‑time metric services and dashboard migrations, accompanied by practical troubleshooting guidance and upcoming enhancements.
This article explains the standard SQL query execution sequence—including FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with examples, code snippets, and diagrams to clarify how data is retrieved and processed.
The article explains the logical execution order of a SQL query—starting with FROM/JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—using diagrams, code snippets, and practical examples to illustrate each step.
This guide explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, how to delete the extra rows while keeping the earliest entry, and how to handle duplicate detection across multiple columns with correct query patterns and common pitfalls.
This article describes how AutoHome tackled the complexity of managing multiple relational, NoSQL, and Hive data stores by adopting openLooKeng for unified, cross‑source SQL queries, outlines its key features such as ANSI‑SQL support, diverse connectors, and query optimizations, and details the custom enhancements made to the Apache Kylin connector to better serve their commercial data analysis workloads.
This article demonstrates how to mask sensitive data such as phone numbers and ID cards using SQL functions, introduces Java masking libraries, and provides a complete MyBatis-Mate Sensitive Jackson example with configuration, custom strategies, entity annotations, controller endpoints, and sample responses.
This article explains how to collect comprehensive MySQL performance metrics using only native SHOW commands, covering connections, buffer pool, locks, SQL statements, throughput, server variables, and slow‑query analysis, while also offering practical tips for interpreting and optimizing the results.
This article introduces Flink Table Store v0.2, explains its four primary application scenarios—offline warehouse acceleration, partial update, pre‑aggregation rollup, and real‑time warehouse enhancement—details the core lake‑storage architecture, bucket management, append‑only mode, and outlines the project’s future roadmap and trade‑off considerations.
SQLite 3.39.3, the latest version of the widely used embedded SQL database engine, introduces RIGHT and FULL OUTER JOIN support, new binary comparison operators IS NOT DISTINCT FROM and IS DISTINCT FROM, additional APIs such as sqlite3_db_name, enhanced file‑name handling, HAVING clause for aggregates, and overall CPU optimizations reducing cycles by about 2.3%.
This article demonstrates how Spring's @Transactional annotation rolls back only for RuntimeException subclasses, explains why checked exceptions like Exception do not trigger rollback by default, and shows how to configure rollbackFor to handle custom exceptions, illustrated with SQL updates and Java code examples.
This article compares Fluent MyBatis, native MyBatis, and MyBatis Plus by implementing a student score aggregation query, showcasing code examples, generator settings, and a detailed feature matrix to help developers choose the most efficient ORM for complex SQL construction.
This article walks through installing and using the golang-migrate CLI to manage database schema changes, covering supported drivers, creating migration files, applying and rolling back migrations in PostgreSQL and MySQL, handling errors, and advanced scenarios such as multiple sequential migrations.
This comprehensive guide explains MyBatis fundamentals, compares it with Hibernate, details its lifecycle, configuration, dynamic SQL, caching, lazy loading, batch operations, pagination, and plugin development, providing code examples and best‑practice recommendations for Java backend developers.
Despite the rise of newer data tools, SQL continues to dominate the developer landscape in 2022, offering universal applicability, high demand, strong career prospects, and a rich history that makes it an essential skill for data analysts, developers, and high‑paying tech roles.
This article presents OpenMLDB, an open‑source machine learning database that unifies offline and online feature computation with millisecond‑level latency, outlines its development history, architecture, recent 0.6.0 enhancements, ecosystem integrations, and multiple real‑world deployment case studies across finance, banking, research, and marketing domains.
This article investigates why MySQL treats strings with trailing spaces as equal during "=" comparisons, explains the underlying PAD SPACE collation rule, and demonstrates how to perform exact matches using LIKE and BINARY operators with practical examples and code snippets.
The article explains why MyBatis batch inserts using a foreach-generated single INSERT statement can cause severe CPU and latency issues, and demonstrates how to achieve fast bulk insertion by limiting batch size or switching to ExecutorType.BATCH with code examples.
When loading massive amounts of data into PostgreSQL, disabling autocommit, postponing index and foreign‑key creation, adjusting memory settings, using COPY or pg_bulkload, and fine‑tuning WAL and trigger settings can dramatically improve import speed and overall performance.
The gpdb‑faiss‑vector plugin embeds Facebook’s Faiss library into the Dolphin (Greenplum‑compatible) engine, exposing SQL functions for distributed, high‑performance approximate nearest‑neighbor vector retrieval with caching, parallel search, configurable indexes, and sub‑millisecond latency, enabling scalable recommendation and advertising workloads.
This article introduces the mysqlreplicate utility, explains how to run it with common command‑line options, and provides a step‑by‑step source‑code analysis that checks server‑id and UUID uniqueness, InnoDB compatibility, storage‑engine consistency, binary‑log settings, and finally creates and starts the replication process.
This article examines unexpected bugs caused by PageHelper in a Java/MyBatis project—such as duplicate registrations, limited dropdown results, and password‑reset errors—traces them to ThreadLocal misuse, explains the library’s internal pagination flow, and offers practical fixes to avoid these pitfalls.
This article provides a comprehensive collection of SQL Server commands and techniques, covering database creation, table manipulation, queries, indexing, backup, replication, and advanced operations, offering practical examples and code snippets for developers and database administrators.
This article explains how to collect comprehensive MySQL performance metrics—including connections, buffer cache, locks, statement counts, throughput, server variables, and slow‑query analysis—using only MySQL's native SHOW commands, providing a fast, low‑overhead monitoring solution.
This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article benchmarks ClickHouse, Oracle, and the open‑source esProc SPL on the TPC‑H suite, showing ClickHouse excels at simple scans, Oracle handles many complex queries, while SPL consistently outperforms both in speed and code simplicity across a range of workloads.
This guide walks you through ten essential advanced SQL techniques—including CTEs, recursive queries, temporary functions, CASE pivots, EXCEPT vs NOT IN, self‑joins, ranking window functions, delta calculations, running totals, and date‑time manipulation—providing clear explanations and practical code examples to boost your interview performance.
This article presents five practical rules—return only needed columns, use proper indexes, avoid subqueries, replace OFFSET pagination with key‑based limits, and understand SQL's logical execution order—to help developers craft efficient SQL statements across major relational databases.
Time management in programming spans human concepts of time, language-specific handling of zones and timestamps, 32‑bit overflow risks, sync versus async processing, log timestamping, business‑level period calculations, and common pitfalls, emphasizing that mastering these nuances prevents bugs, improves performance, and enables reliable analytics.
This article explains Doris's fundamental concepts such as rows, columns, tablets, and partitions, provides guidelines for column definition, partitioning and bucketing strategies, and details table creation syntax and property settings for optimal big‑data storage and query performance.
