This article explains how to detect, analyze, and resolve slow SQL queries that threaten service stability, offering step‑by‑step guidance, EXPLAIN analysis, and real‑world case studies to help developers quickly pinpoint and fix performance bottlenecks.
The article explains how large‑offset (deep) pagination in MySQL and distributed databases degrades performance and increases memory and bandwidth usage, and describes VtDriver's streaming and SQL‑rewrite techniques along with practical recommendations such as range queries and sub‑queries to mitigate these issues.
The Taobao platform’s slow‑SQL governance team implemented a comprehensive detection and governance pipeline—combining internal slow‑log tools, database slow‑query logs, and JVM‑Sandbox instrumentation to capture full SQL details, scoring high‑risk queries by execution time, scans, and standards violations, then prioritizing remediation through health scores, branch‑diff checks, and issue tracking—significantly cutting DB‑related incidents and boosting system stability.
This article explains how to achieve high‑performance database search in PHP by optimizing schema, writing efficient SQL, using mysqli/PDO functions, employing caching tools like Memcached or Redis, and integrating full‑text engines such as Elasticsearch, with complete code examples.
This article explains how StarRocks 3.0 extends real‑time data‑warehouse capabilities to support data‑lake analysis, external catalog integration, Trino compatibility, extensive I/O optimizations, and powerful materialized‑view features that together enable a unified, cloud‑native Lakehouse solution with high performance and flexible resource isolation.
The SQLE 2.2309.0-pre3 release introduces a fast SQL audit feature, adds numerous MySQL audit rules, provides detailed community and enterprise version updates, and includes links to the repository, documentation, and previous versions for comprehensive usage and evaluation.
This article explains how JDBC operations evolve into the MyBatis persistence framework, covering JDBC's seven-step query process, MyBatis's design components, initialization workflow, builder patterns, and includes practical Java code examples for each stage.
This article walks through the creation and step‑by‑step optimization of a MySQL service_log table, covering proper data‑type selection, index usage, NULL handling, DATETIME vs TIMESTAMP choices, and floating‑point considerations to achieve a lean, high‑performance schema.
This article explains MyBatis dynamic SQL, its nine XML tags, execution principle, and how to use if, where, set, choose, trim, foreach, sql, include, and association mappings for flexible query building, batch operations, and one‑to‑many, many‑to‑one, and many‑to‑many relationships.
The Aikexing Open Source Community weekly newsletter shares curated database articles, technical insights on MySQL, OceanBase, and Redis, announces SQLE 2.2309.0‑pre2 release with Feishu integration, recaps recent events like QCon Beijing, and promotes upcoming meetups and open‑source projects.
This article explains how Alibaba's user growth platform leveraged RoaringBitmap in Hologres to accelerate massive user profiling, reducing analysis time from several minutes to around ten seconds by redesigning bitmap storage, optimizing data pipelines, and employing efficient SQL and scheduling techniques.
This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.
This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.
This article demonstrates how to use MySQL to create employee and performance tables, populate them with sample data, and write SQL statements that first select employees whose salaries exceed their department's average and then filter out those with non‑A performance ratings, providing a complete layoff‑target list.
This article explains how JDBC evolves into MyBatis, detailing the seven-step JDBC query process, the motivations for encapsulating JDBC, MyBatis architecture, core components, initialization mechanisms, design patterns, and code examples, while offering optimization strategies for connection handling, SQL management, and result mapping.
Manticore Search is a C++‑based open‑source search engine that builds on Sphinx, offering high‑performance full‑text search, SQL compatibility, and significant speed advantages over Elasticsearch, with detailed usage instructions and Docker deployment examples for developers.
The article explains how Hologres RoaringBitmap compresses user ID sets into efficient bitmap indexes, splits 64‑bit IDs into buckets, syncs them from MaxCompute, and enables sub‑second user portrait queries that previously took minutes, dramatically improving performance and scalability.
This article breaks down the true execution sequence of an SQL query—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—explaining each stage with examples, code snippets, and visual diagrams to help developers write more efficient queries.
This comprehensive guide consolidates 43 essential MySQL interview topics—from normalization, storage engines, and indexing to transaction handling, logging, replication, and performance tuning—providing clear explanations, code examples, and diagrams to help candidates quickly master the core concepts.
This article introduces MaxCompute’s new PIVOT and UNPIVOT syntax, explains their use cases for converting rows to columns and vice versa, provides detailed syntax rules, and demonstrates practical examples with code snippets to simplify data transformation and boost developer productivity in big-data environments.
This article examines why MySQL columns are often defined as NULL, debunks common myths, and presents several technical reasons—including storage overhead, index complications, query pitfalls, and timestamp issues—that justify using NOT NULL with meaningful default values for better performance and data integrity.
With MySQL 5.7 reaching end‑of‑life, this guide explains why you should upgrade to MySQL 8.0, outlines the new SQL capabilities and tools, and compares migration options including DBaaS and switching to PostgreSQL, helping you choose the best path for your workloads.
The SQLE 2.2309.0-pre1 release introduces downloadable scan task reports, eight new MySQL audit rules, and expands MyBatis scanning to all supported data source types, while providing comprehensive project overview, full release information, and links to documentation and demos.
The IEEE Spectrum 2023 programming language ranking shows Python topping both the Spectrum and Trend dimensions, while SQL rises to the top for job relevance, and the article compares these results with other major surveys to reveal shifting development trends.
This article introduces MaxCompute’s QUALIFY clause, explains its syntax and execution order, compares it to HAVING, provides multiple code examples—including simple and complex queries—and demonstrates how QUALIFY streamlines filtering of window function results, boosting query readability and efficiency.
This guide walks backend developers through fifteen practical best‑practice tips covering table and column naming, data types, field lengths, primary keys, storage engines, null constraints, foreign keys, indexes, time and monetary fields, character sets, collations, and handling large columns to reduce maintenance costs and avoid common pitfalls.
This article presents a detailed checklist of mandatory and recommended standards for database security, design, naming, indexing, SQL usage, and operational procedures, aiming to prevent low‑level failures in high‑concurrency, large‑scale internet applications.
This article walks through the exact sequence a database follows when executing a SQL query—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—highlighting key differences from the textual order of the statement.
This article examines whether adding the always‑true condition WHERE 1=1 in MySQL queries prevents index usage, by comparing EXPLAIN plans for queries with and without the clause and proposing a cleaner MyBatis mapping approach.
This article explains key database terminology, traces the history of database technologies, compares relational, NoSQL, NewSQL, OLTP/OLAP, columnar, time‑series and graph databases, and demonstrates practical data‑structure designs such as zipper tables, bit operations, bitmaps, bloom filters, and ring queues for software development.
This guide explains MaxCompute’s IF‑ELSE syntax, its compile‑time and runtime behaviors, and provides practical code examples showing how to conditionally route SQL logic based on data characteristics, helping developers write more flexible big‑data queries.
This article demystifies the actual execution sequence of SQL queries, detailing how FROM and JOIN establish table relationships, WHERE filters rows, GROUP BY groups data, HAVING applies aggregate conditions, SELECT retrieves fields, ORDER BY sorts results, and LIMIT restricts the final output.
The article explains that MySQL implicitly converts VARCHAR values to numbers during a comparison with an integer, truncating non‑numeric strings and causing queries like "WHERE a = 0" to return rows that should not match, unlike Oracle and SQL Server which raise errors.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result handling, and provides concrete SQL rewrites and execution‑plan analyses to dramatically improve query speed.
This article explains the true execution sequence of SQL statements—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with diagrams, examples, and code snippets to clarify how databases process queries.
The SQLE 2.2308.0 release introduces intelligent SQL file scanning, per‑instance work‑order approvers, multi‑file MyBatis review in IntelliJ, zero‑cost Java probe integration, new OceanBase‑Oracle support, expanded TDSQL distributed rules, and numerous bug fixes and optimizations across both community and enterprise versions.
This article explains how to identify and remove duplicate rows in a MySQL table—keeping a single record per duplicate group—by using a self‑join DELETE statement or a nested subquery approach, complete with sample table creation and data insertion scripts.
RisingWave is an open‑source distributed streaming SQL database that uses SQL to define tables and materialized views, offering low‑latency incremental query processing, a scalable architecture with separate compute and storage, robust consistency guarantees, and real‑time analytics demonstrated through several practical use cases.
This article explains how to manage multithreaded database insert operations in Spring Boot, demonstrates why the @Transactional annotation fails in a multithreaded context, and provides a solution using manual SqlSession transaction control with code examples and test results.
This article explains why using NULL in MySQL tables hurts storage, index size, query correctness, and update performance, and shows concrete examples, benchmark data, and official documentation that justify preferring NOT NULL with sensible default values.
This article explains how to retrieve the names of students who have selected both course 1 and course 2 in a MySQL table by using a GROUP BY clause together with a HAVING condition that counts distinct course IDs, avoiding incorrect results from a simple IN filter.
This article explains how to perform relational updates in MySQL using JOINs, subqueries, and multi‑table UPDATE statements, demonstrating table creation, sample data insertion, and the differences between INNER, LEFT, and RIGHT joins for keeping dependent tables in sync.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their definitions, associated phenomena such as dirty reads, non‑repeatable reads, and phantom reads, and demonstrates how to query and set isolation levels with practical SQL examples.
This article explains how to split a massive MySQL table into smaller tables, use the MERGE engine to create a unified view, troubleshoot common errors, and implement efficient pagination across the split tables with example SQL and Java code.
The article describes the evolution from manual to semi‑automatic and finally fully automatic solutions for verifying real‑time and offline ranking data during major sales events, detailing rule extraction, Hive‑based SQL generation, execution, and the resulting reduction in human effort.
This article describes how to identify, analyze, and fix InnoDB page corruption in MySQL instances, covering log inspection, querying metadata tables, using innodb_force_recovery, and the inno_space tool, with example commands and best‑practice recommendations to prevent data loss.
This article explains how to set up a MySQL test table and indexes, then enumerates nine typical scenarios—such as leading‑wildcard LIKE, arithmetic or functions on indexed columns, type mismatches, composite‑index misuse, character‑set differences, and OR conditions with non‑indexed fields—that cause queries to bypass indexes and perform full table scans.
This article compares SQL and NoSQL databases, outlining their advantages, limitations, and ideal use‑cases, and provides guidance on selecting the appropriate technology based on consistency, scalability, and data model requirements.
The article reviews Doris 2.0's major enhancements—including point‑query concurrency, log‑analysis capabilities, cold‑hot data separation, lakehouse integration, and various performance and usability upgrades—explaining how these changes benefit OLAP workloads and simplify data‑engineering pipelines.
The article examines a puzzling PostgreSQL self‑join query that yields surprising true/false results, walks through step‑by‑step rewrites, analyzes the resulting execution plans—including the appearance of InitPlan and One‑Time Filter nodes—and explains how correlated versus uncorrelated subqueries affect query evaluation.
This article presents a comprehensive guide to SQL performance tuning, covering real‑world interview scenarios, deep‑pagination pitfalls, and fifteen concrete optimization techniques—including index usage, limit tricks, proper joins, batch operations, and data‑type choices—to help developers write faster, more efficient MySQL queries.
The SQLE 2.2308.0-pre2 release introduces zero‑cost Java integration, TiDB abort‑release support, OceanBase for Oracle data source handling, and a comprehensive set of documentation, demo links, optimizations, and bug fixes for both community and enterprise editions.
The article introduces sql_helper, an open-source command-line and web tool for MySQL 5.7/8.0 and MariaDB that automatically analyzes SQL statements to suggest index creation, explains its six-step workflow, provides usage examples, command-line parameters, and notes its assumptions and limitations.
StarRocks 3.1 introduces a cloud‑native, lakehouse‑oriented architecture with enhanced storage‑compute separation, up to 3‑6× faster data‑lake queries than Trino/Presto, expanded Iceberg and Paimon support, richer materialized view capabilities, new random bucketing, expression partitioning, generated columns, and spill‑to‑disk stability, all backed by extensive performance optimizations and open‑source contributions.
MySQL 8.0 introduces a native document store that brings full ACID‑compliant NoSQL capabilities, schema‑less JSON handling, simple CRUD APIs, seamless SQL integration, large‑document support, built‑in security and infrastructure simplification, enabling developers and DBAs to leverage existing MySQL expertise for modern applications.
This article explains why MySQL, the world’s most popular open‑source relational database, is a fundamental skill for developers, operators and testers, and outlines a two‑book learning path covering installation, SQL mastery, InnoDB internals, indexing and performance optimization.
This guide walks developers through the SQL Bond competition timeline, prize details, registration steps, Hologres database creation, foreign table setup, sample analysis queries, and submission process, providing all essential instructions and code snippets for successful participation.
The SQLE 2.2308.0-pre1 release introduces community and enterprise enhancements such as intelligent SQL file scanning, per‑instance workflow approver matching, and the ability to abort deployments on OceanBase and TDSQL data sources, accompanied by usage examples, command‑line instructions, and full release information.
This article explains MySQL’s server and storage‑engine architecture, walks through the step‑by‑step execution of a SELECT statement, describes how indexes are organized, defines slow queries, shows how to detect and analyze them, and provides practical optimization examples and additional MySQL pitfalls.
This article presents practical MySQL performance tuning techniques, covering query, pagination, join, subquery, order‑by, group‑by, and count optimizations with example code, index strategies, and execution‑plan analysis to improve latency in high‑traffic systems.
This article compares Elasticsearch and ClickHouse across architecture, query capabilities, and performance using Docker‑compose stacks and Python SDK tests, demonstrating that ClickHouse often delivers superior speed, especially in aggregation and regex queries, while highlighting each system’s design trade‑offs.
This article provides interview‑style SQL examples that cover creating a sales table, computing year‑over‑year and month‑over‑month growth rates, and using various window‑function techniques to identify numbers that appear consecutively at least three times.
This article explains how to query sales statistics for multiple business units and their associated products in MySQL by preparing the environment, presenting several candidate solutions—including loop queries, OR concatenation, mixed filtering, and finally row‑comparison—and selecting the row‑comparison approach as the optimal implementation.
This article evaluates MyBatis-Plus (MP) for Java backend development, highlighting its convenient CRUD shortcuts, code examples, maintenance challenges, magic value pitfalls, and recommended practices such as using LambdaQueryWrapper, pagination, and avoiding overly complex SQL within MP.
This guide walks you through the essential stages of database selection—from assessing project requirements and comparing candidate systems to performance testing, long‑term impact analysis, and making the final decision—ensuring you pick a solution that fits both current and future needs.
This article explains InnoDB's three row‑lock types, when MySQL statements acquire implicit or explicit locks, and walks through concrete examples for unique and non‑unique index queries, showing how Next‑key, Record and Gap locks are determined.
This article examines MyBatis-Plus, highlighting its convenient CRUD shortcuts and pagination features while warning about maintenance challenges, magic strings, and limited support for complex SQL, and offers practical recommendations for using LambdaQueryWrapper and organizing queries in backend Java projects.
This article introduces Fluent Mybatis, a Java ORM framework that enhances Mybatis with features from Mybatis Plus and JPA, demonstrates its core concepts, compares it with native Mybatis and Mybatis Plus through a student score query example, and provides step‑by‑step setup, code generation, and CRUD usage.
This article explains how to use Python to automatically back up SQL databases by installing required packages, establishing a connection with pyodbc, executing backup commands, recording backup details with pandas, and scheduling the script for regular execution, providing code examples for each step.
This article introduces MyBatis-Flex, a lightweight high‑performance MyBatis enhancement framework, outlines its key features, compares it with MyBatis‑Plus and Fluent‑MyBatis, lists supported databases, and provides a step‑by‑step quick‑start tutorial with code examples.
This article examines the ongoing debate around MyBatis‑Plus, highlighting its convenience for simple CRUD operations, the maintenance challenges it introduces, and practical guidelines—such as using LambdaQueryWrapper and pagination plugins—to decide when the framework is appropriate for Java backend projects.
This article explains common MySQL index‑inefficiency scenarios—including left‑most prefix violations, range queries, functions, LIKE patterns, OR/IN misuse, and ORDER BY—provides concrete SQL examples, visual illustrations of B+‑tree indexing, and practical tips such as index jump scans, ICP, and query rewriting to ensure optimal index usage.
MySQL 8.1 and 8.0.34, released on July 18, 2023, introduce a mix of innovative features—such as JSON EXPLAIN INTO, enhanced replication controls, TLS certificate enforcement, and new SHOW PARSE_TREE—while also aligning many adjustments with 8.0.34, adding user‑management options, audit enhancements, binary‑log APIs, and a series of deprecations for future versions.
MySQL 8.1, the first innovation release, and the stable 8.0.34 bring a host of new capabilities—including JSON‑based EXPLAIN INTO, enhanced replication controls, expanded security variables, audit improvements, binary‑log functions, and several deprecations—while also fixing numerous bugs to solidify MySQL’s stability.
This article explains why a MySQL user granted both USAGE and GRANT OPTION on a database can see schema information but cannot query table data, demonstrates the issue with local tests, and provides proper REVOKE commands to cleanly remove the conflicting permissions.
reverse_sql is an open‑source utility that parses MySQL binary logs in ROW format, generates both original and reverse SQL statements, and helps recover data from accidental DML operations using multithreaded processing and flexible filtering options.
This article compares Elasticsearch and ClickHouse by describing their architectures, presenting Docker‑based test stacks, showing code snippets for deployment, data ingestion, and queries, and reporting performance results that demonstrate ClickHouse generally outperforms Elasticsearch in log‑analytics scenarios.
The article explains a puzzling MySQL DELETE failure caused by a missing primary key, shows how Navicat adds an ESCAPE clause to the generated SQL, clarifies the purpose of ESCAPE in LIKE patterns, and reveals that the real issue was a mistaken condition order in the query.
This article explains how MySQL 8.x window functions such as OVER, PARTITION BY, and ORDER BY can simplify complex ranking and aggregation queries, demonstrates creating a sample scores table, and provides practical examples of functions like ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, and LEAD with their results.
This article explains InnoDB's three row‑lock types, when they are applied implicitly or explicitly, the two core locking principles, and walks through concrete examples for unique and non‑unique index equality and range queries, showing exact lock ranges and lock‑type degradations.
This article explains how to design a MySQL table for product sales statistics, prepares sample data, describes the business requirement of querying sales for multiple business units and their associated products, and evaluates several query strategies—looping, OR concatenation, mixed filtering, and finally row‑row comparison—highlighting why the latter is chosen as the optimal solution.
This guide outlines a five‑level learning path for MySQL, starting with installation and basic queries, progressing through proficient use, deep understanding of locks, indexes and transactions, advanced performance tuning, and finally product‑level redesign, while recommending essential books and resources.
This guide compiles essential MySQL command‑line scripts covering database and table export, import, creation, alteration, permission granting, and common DDL/DML operations, providing clear examples for managing schemas, data, and user privileges in a MySQL environment.
This article analyzes a puzzling OceanBase data‑type implicit conversion problem where UPDATE statements intermittently fail with SQL syntax errors while SELECT statements return unexpected results, explains the underlying conversion mechanisms, shows diagnostic queries and log excerpts, and provides practical recommendations to avoid such errors.
This article presents twenty essential principles for optimizing MySQL indexes, covering topics such as the limitations of leading wildcard LIKE queries, the advantages of IN over UNION, handling negative conditions, composite index ordering, range queries, covering indexes, prefix indexes, and common pitfalls to avoid for better query performance.
This article translates and expands on HikariCP's pool‑sizing guidance, explaining why smaller database connection pools often yield better performance, presenting real‑world benchmark data for various pool sizes, and offering a simple formula to calculate an optimal pool size based on CPU cores and effective disks.
This guide walks you through creating a fully managed EMR Serverless StarRocks instance, loading historical World Cup data, and running OLAP SQL queries to analyze championship counts and host‑nation performance, all using a free trial of compute and storage resources.
To address the resource‑intensive challenges of joining billion‑row tables in data warehouses, this article examines common optimization approaches, analyzes Spark’s SortMergeJoin algorithm, and proposes a Bloom‑filter‑based solution that filters unchanged data early, dramatically improving performance and reducing cluster resource consumption.
By adding a guard that skips the costly part‑scan in MergeTreePartsMover::selectPartsForMove when disk usage is below the threshold and no MoveTTL is set, Didi reduced BgMoveProcPool thread CPU consumption from about 30 % to under 4 %, halving overall node CPU load and improving ClickHouse’s PB‑scale performance.
The article examines multiple techniques—including direct SQL updates, compare‑and‑swap, optimistic locking, Redis‑based distributed locks, and MySQL shared locks—to ensure correct and consistent account balance modifications and order status changes in high‑concurrency scenarios.
This article compares traditional databases with modern cloud‑native data warehouses, outlines a detailed performance testing methodology using the SSB1000 benchmark, presents test scripts and environment setup for Cloudwave and StarRocks, and analyzes the results to highlight strengths and optimization opportunities.
Certain MySQL queries, such as those using functions, leading wildcards in LIKE, OR operators, inequality comparisons, or NULL checks, prevent the database engine from utilizing indexes, leading to slower performance, and should be rewritten for optimal query efficiency.
This article explains five common MySQL query patterns—using functions, leading wildcards with LIKE, OR operators, inequality comparisons, and NULL checks—that prevent the database engine from utilizing indexes, and provides example SQL statements illustrating each case.
This article examines why MySQL LIMIT pagination becomes slower with deeper offsets on a 500,000‑row table, demonstrates the performance impact with concrete queries, and presents three optimization strategies—including using ordered primary keys, subqueries, and join‑based approaches—to reduce scan range and improve query speed.
The article explains a real‑world MySQL replication failure caused by a 4 GB binlog, walks through the replication architecture, binlog write mechanics, diagnostic commands, mode differences, and shows how a large row‑based transaction with a BLOB field inflated the log, concluding with a practical fix.
This article introduces Chat2DB, an open‑source AI‑enhanced multi‑database client, walks through its download, installation, configuration, demonstrates natural‑language‑to‑SQL, SQL explanation and optimization features using sample MySQL tables, and outlines future roadmap for developers.
This article outlines a comprehensive SQL governance framework that moves defense to the testing stage, introduces SQLReview and new‑SQL detection with fingerprinting, details full‑SQL analysis for deep insight, and explains capacity prediction through simulated traffic load testing in a hybrid‑cloud environment.
This article explores whether AI agents such as ChatGPT can help business owners quickly perform data analysis by linking large language models with local MySQL databases via LangChain, presenting the experimental setup, schema creation, sample queries, results, and practical insights.
This article demonstrates how to create a 10‑million‑row MySQL table, batch‑insert data via a stored procedure, measure ordinary LIMIT pagination versus offset‑optimized queries, and provides practical tips for improving query speed on massive tables.
DBeaver is a free, open‑source, cross‑platform database client that supports virtually every major database, offers plugin extensions, visual data tools, theme options, backup/restore, and a powerful SQL editor, with a step‑by‑step guide to download, configure drivers, connect, and run queries.
