This article explains the purpose, syntax rules, and practical examples of the UNION operator in MySQL, covering distinct and ALL modes, ORDER BY restrictions, new TABLE and VALUES usages introduced in MySQL 8.0.19, and differences from MySQL 5.7.
The SQLE 1.2202.0 release introduces new user group and permission management, scoring for audit results, expanded support for MySQL, Oracle, Java application SQL capture, Db2 audit plugins, additional MySQL audit rules, UI optimizations, and several critical bug fixes.
The article explains how using IN and NOT IN in SQL can cause performance problems and subtle bugs, especially with large tables or NULL values, and demonstrates safer alternatives such as EXISTS, NOT EXISTS, and JOIN with clear code examples.
This article explains how to detect Redis latency problems, measure baseline performance, monitor slow commands, and address common causes such as network round‑trip delays, fork‑generated RDB snapshots, transparent huge pages, swap usage, AOF settings, key expiration, and big‑key handling, providing practical troubleshooting steps and solutions.
This article explains how InnoDB stores data in 16 KB pages, links pages via a double‑linked list, uses page directories and primary‑key directories for binary search, and builds B+‑tree index pages to accelerate MySQL queries, covering free space, page splits, and the overall search flow.
This article explains why MySQL may ignore an index on range queries, shows example queries with EXPLAIN output, describes the optimizer's row‑count threshold that triggers a full table scan, and offers guidance on keeping indexes effective.
This article analyzes a MySQL replication failure caused by stale GTID execution data and unsynchronized binlog persistence, explains the underlying mechanisms, demonstrates reproducible testing, and provides two practical remediation methods to skip duplicate-key errors and restore replication.
The article examines the drawbacks of running MySQL in Docker containers, highlighting data safety, performance, state management, and resource isolation concerns while also discussing scenarios where containerizing MySQL might be feasible.
This extensive guide covers Redis fundamentals, data structures, persistence mechanisms, clustering, sentinel high‑availability, performance tuning, common pitfalls, and practical use‑case implementations, providing detailed answers and code examples for interview preparation and real‑world development.
This article compares three Go transaction implementations, explains their trade‑offs, and presents a refined pattern that uses defer tx.Rollback() to guarantee rollback while still allowing successful commits, covering both simple and loop‑based use cases.
This article explores Redis persistence mechanisms, detailing the purpose of data durability, the two primary methods—RDB snapshots and AOF logs—their operation, strategies, advantages, drawbacks, and the newer hybrid persistence mode, while also addressing common questions and best‑practice recommendations for reliable deployment.
This comprehensive guide explores Redis fundamentals, internal data structures, persistence options, multi‑threading, replication, sentinel failover, clustering, common performance pitfalls, and practical use‑case patterns, providing detailed explanations and code examples for developers and architects.
Redis is an open‑source, in‑memory data‑structure store that serves as a database, cache, and message broker; this guide introduces its fundamentals, explains why NoSQL emerged, details its five primary data types, common commands, and advanced features like transactions and pipelines.
This article introduces mybatis-mate, an official MyBatis‑Plus extension that provides enterprise‑grade features such as sharding, multi‑datasource support, data auditing, field encryption, sensitive‑data masking, automatic DDL maintenance, dynamic datasource switching, distributed transaction logging, and fine‑grained data permission control, with detailed usage examples and configuration snippets for Spring Boot projects.
This article explains MySQL indexes—what they are, their advantages and disadvantages, how they improve query performance, and the scenarios where you should or shouldn't create them—plus a free PDF of interview questions for deeper study.
When installing MongoDB 5.0 on a CentOS 7.2 server, the mongo and mongod commands abort with an "Illegal instruction" error because the CPU lacks AVX support, and the article explains how to diagnose this issue, verify CPU capabilities, and resolve it by using AVX‑compatible hardware.
This article explains why traditional database connection pools rely on blocking I/O and connection pooling instead of leveraging IO multiplexing, covering JDBC limitations, protocol constraints, ecosystem factors, and the trade‑offs between performance and code complexity.
The article explains why using COUNT(*) to test for record existence is inefficient and demonstrates how replacing it with SELECT 1 … LIMIT 1 in SQL and a corresponding Java existence check can significantly improve database query performance.
This guide walks through MySQL's query execution sequence, then presents concrete optimization techniques—from selecting specific columns and avoiding OR conditions to proper indexing, using EXPLAIN, handling VARCHAR vs CHAR, limiting result sets, and improving joins, GROUP BY, and UNION operations—complete with code examples and visual explain plans.
To overcome export failures when report data exceeds ten thousand rows, this guide demonstrates how to configure MyBatis for forward-only streaming reads by adjusting JDBC settings, adding ResultHandler callbacks, and modifying mapper XML, enabling efficient memory usage during large result set processing.
This guide explains why InnoDB full‑text search is needed, how inverted indexes work, how to create and use full‑text indexes with MATCH AGAINST in natural language, boolean, and query‑expansion modes, and how to drop them safely.
The article explains why traditional Java database connection pools rely on blocking I/O instead of IO multiplexing, covering JDBC's design, session management, resource constraints, and the practical challenges of adopting non‑blocking approaches in mainstream backend systems.
The article explains why placing databases inside Docker containers is generally unsuitable, outlining seven major issues—including data safety, performance, networking, statefulness, resource isolation, cloud platform incompatibility, and environment requirements—while also offering practical mitigation strategies.
KeyDB, a multithreaded fork of Redis, offers higher throughput, multi‑master replication, vertical and horizontal scaling, TLS encryption, JavaScript modules, and ARM support, making it a compelling alternative for boosting QPS without additional hardware, while remaining fully compatible with Redis.
A comprehensive collection of MySQL interview questions and answers covering storage engines, lock types, gap locks, deadlock avoidance, isolation levels, index types, covering indexes, the left‑most prefix rule, SQL tuning, master‑slave replication, latency handling, sharding challenges, and global ID generation.
This article explains why InnoDB full‑text search is needed for keyword‑based queries, introduces inverted index structures, shows how to create, use, and delete full‑text indexes in MySQL, and demonstrates natural language, boolean, and query‑expansion search modes with practical SQL examples.
The SQLE 1.2201.0 release introduces workflow optimizations, new MySQL and SQL Server audit rules, enhanced pre‑check capabilities, several UI improvements, and numerous bug fixes, while providing links to the source repository, documentation, and plugin development guide.
This tutorial explains Redis Bitmaps, covering their underlying concept, core commands such as SETBIT, GETBIT, BITCOUNT, BITOP, BITPOS, BITFIELD, and demonstrates practical uses like online‑status tracking, daily active‑user counting, Bloom filters, marketing segmentation, collaborative drawing, and handling non‑sequential IDs.
This article explains why globally unique identifiers are essential in distributed systems, outlines the key characteristics of a good ID scheme, and compares several generation methods—including UUID, database auto‑increment, segmented DB ranges, Redis INCR, Zookeeper, Meituan Leaf, Snowflake, and Baidu uid‑generator—highlighting their advantages, drawbacks, and practical implementation details.
This article explains Redis's high‑performance RESP protocol, its simple design principles, how commands are serialized over TCP, and demonstrates Java examples for both a mock server and client, while detailing the various response types defined by the protocol.
This article explains why adding conditions after a LEFT JOIN's ON clause does not filter rows, contrasts ON and WHERE behavior, and demonstrates the differences with multiple SQL examples and visual illustrations of intermediate result tables.
This article explains the concept of distributed locks, compares three implementation methods—database unique indexes, Redis (using SETNX), and Zookeeper—provides detailed SQL table design and Java code examples for acquiring and releasing locks, and discusses reentrancy, lock release timing, and single‑point failure considerations.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures pagination query times under different offsets and limits, and presents practical optimization techniques such as sub‑queries, id‑range filtering, and column selection to improve performance on massive data sets.
This article explains why Java database connection pools typically use blocking I/O and connection pooling instead of IO multiplexing, covering JDBC's design, session management, ecosystem constraints, and the trade‑offs between performance and code complexity.
This article explains how MyBatis' ${} and #{} placeholders differ in SQL generation, demonstrates their usage with code examples, analyzes the SQL injection risks of ${} versus the safety of #{} through prepared statements, and provides practical guidelines for choosing the appropriate placeholder in various scenarios.
This article explains why distributed locks are needed beyond Java's JVM‑level locks, compares three common implementation approaches—database unique indexes, Redis SETNX, and Zookeeper ephemeral sequential nodes—and provides concrete SQL and Java code examples with analysis of re‑entrancy, release timing, and single‑point risks.
This weekly newsletter from the MySQL community presents curated technical articles, bug fixes, open‑source middleware progress, and upcoming plans, offering developers concise insights into MySQL issues, DBLE/DTLE developments, and SQL audit tool enhancements.
The article outlines a comprehensive backend architecture for handling million‑level daily active users, covering DNS routing, L4/L7 load balancing, monolithic versus microservice deployment, caching, database sharding, hybrid‑cloud deployment, elastic scaling, and multi‑level degradation strategies to ensure high availability under sudden traffic spikes.
This article provides a comprehensive overview of MySQL's built‑in functions—including numeric, string, date/time, and aggregation utilities—explaining their usage, parameters, and output with clear examples to help developers write more efficient SQL queries.
This article walks through setting up GORM in a Go project, covering dependency management, database initialization, model definition, and practical examples of select, insert, update, delete, raw SQL execution, and transaction handling with detailed code snippets.
The article presents a comprehensive performance benchmark of RedisJSON (with RediSearch) against MongoDB and ElasticSearch, showing RedisJSON’s superior write, read, and mixed‑workload throughput and latency across isolated and mixed scenarios, supported by detailed YCSB test results and analysis.
This article explains database isolation levels, including read committed, repeatable read, serializable, and snapshot isolation, discusses dirty reads, phantom reads, and how various systems such as SQL Server, PostgreSQL, MySQL, Oracle, MongoDB, and others implement or differ in these isolation semantics.
This article reproduces a prolonged MySQL 5.7 binlog replay issue, analyzes the underlying client memory‑allocation bug, and demonstrates how MySQL 8.0 resolves the problem, resulting in significantly faster binlog restoration for large delete operations.
The article explains the true execution order of SQL queries, detailing each step from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT, and illustrates the concepts with examples and code snippets.
The SQLE 1.2112.0 release introduces index optimization, scheduled deployment, enhanced rule configuration, SQL autocomplete, cloud database support, several UI improvements, and a critical bug fix for rule‑crash issues, while providing links to the repository, documentation, and related resources.
The article introduces a lightweight Go ORM for MySQL, explains native connection setup, details the SmallormEngine design with chainable methods for Table, Where, Insert, Update, Delete, and BatchInsert, and demonstrates how reflection builds SQL fragments while handling errors and supporting fluent query composition.
This guide explains how to create a custom Spring AOP annotation for detailed logging, defines the necessary annotation and aspect classes, shows how to extract method details, parameters and user info, store them in a database, and demonstrates required Spring XML configuration and usage examples.
This article explains why a MySQL UPDATE statement can appear to have no effect when using AND between assignments, shows the correct comma‑separated syntax, and provides visual verification of the fix.
The article explains how MySQL 8.0 improves deadlock logging by showing lock information for the holding transaction, discusses potential misunderstandings, and provides a step‑by‑step reproduction of a deadlock case with SQL statements and detailed lock analysis.
Recent benchmark tests show RedisJSON (with RediSearch) dramatically outperforms MongoDB and ElasticSearch, delivering up to 5.4‑times faster isolated writes, over 200‑times faster than ElasticSearch, 12.7‑times faster reads, and significantly lower latency and higher throughput across mixed workloads.
The article explains how concurrent updates can cause data loss in high‑traffic scenarios and demonstrates using optimistic and pessimistic locking in PostgreSQL with Python, including EFCore‑style token checks and a retry mechanism via the tenacity library.
This article explains the fundamentals of object‑oriented modeling, contrasting data‑model and object‑model designs, illustrating attribute‑method composition, composition versus aggregation, and providing concrete Java and SQL examples such as a simple Account domain, an eating‑process model, and an open‑source e‑commerce reference.
This article outlines a comprehensive approach to diagnosing and optimizing performance problems in production business systems, covering common causes such as concurrency spikes, data growth, and environment changes, and detailing hardware, middleware, database, JVM, code-level analyses, monitoring tools, and APM strategies.
This guide explains Redis AOF persistence settings, including default options, the three synchronization strategies (no, always, everysec), and practical recommendations such as enabling AOF on slaves for critical data while keeping masters free of persistence work.
This article summarizes the design, working principle, deployment steps, testing results, and future outlook of a TiDB cross-data-center high‑availability solution based on Binlog bidirectional replication, aiming to ensure rapid failover and continuous service between two data‑center clusters.
This tutorial walks through downloading, compiling, installing, configuring, and setting up Redis 6.2.6 as a systemd service on a Linux server, including key configuration changes and commands to start the server and enable it at boot.
This article examines the limitations of Redis master‑slave replication, explains how Redis Sentinel addresses those issues with monitoring, notification, and automatic failover, and provides detailed configuration commands, discovery mechanisms, and step‑by‑step failover procedures for building a highly available Redis deployment.
Alibaba Cloud's RDS team detailed how cloud‑native transformations, multi‑active deployments, ARM localization, and advanced kernel optimizations enabled the 2021 Double 11 shopping festival to handle massive traffic, cut costs by over 30%, and achieve greener, highly available database services.
This article presents thirteen practical Redis performance optimization rules, covering avoidance of slow commands, proper key expiration handling, data structure selection, persistence strategies, hardware considerations, pipelining, client optimization, and clustering to maximize throughput and minimize latency in high‑volume deployments.
This article explains why a single‑instance Redis can cause outages, introduces the master‑slave architecture, details the full and incremental synchronization processes, shows how to configure replication, addresses multi‑slave scaling, network interruptions, and automatic failover with Sentinel.
This article examines how MySQL Connector/J determines the character set during connection initialization, explains the transition from UTF8MB3 to UTF8MB4, analyzes source code of versions 5.1.46 and 5.1.47, and provides practical methods to enable UTF8MB4 without upgrading the driver.
When a table grows beyond ten million rows, sharding it across multiple databases improves performance, but the usual LIMIT offset, pagesize pagination breaks, leading to missing or incorrect records; this article examines why simple merge approaches fail and evaluates global, secondary query, and no‑skip paging strategies, highlighting their trade‑offs.
The article details Facebook’s multi‑year effort to migrate its heavily customized MySQL 5.6 deployment—including the MyRocks storage engine—to MySQL 8.0, describing the technical challenges, patch‑porting strategy, replication changes, automated verification, and application validation performed during the upgrade.
This article explains how to set the correct time zone for MySQL during installation, describes the relevant startup parameters and system variables, demonstrates their effect on functions like NOW() and TIMESTAMP columns, and provides practical solutions for Java applications, data migration, and dump utilities.
The article chronicles Taobao’s architectural evolution from a LAMP stack to an Oracle‑IBM mainframe setup, then to a Java‑centric distributed system, and finally to a cloud‑native solution on Alibaba Cloud, highlighting key design decisions, scalability challenges, and migration best practices across storage, services, OLTP and OLAP workloads.
This guide walks through the step‑by‑step deployment of MHA on a MySQL cluster, covering package installation on all nodes, copying and installing the MHA RPMs, creating the required MySQL user, configuring MHA, testing SSH connectivity, and reviewing the failover script.
This article explains eight typical situations—such as using non‑transactional storage engines, missing Spring bean registration, non‑public methods, self‑invocation, absent transaction manager, incorrect propagation settings, swallowed exceptions, and wrong exception types—that cause the @Transactional annotation in Spring to appear ineffective, and provides practical solutions for each.
The article presents Maxwell, a C/assembly‑based high‑performance accounting database designed for Alipay, detailing its architecture, hotspot account challenges, performance optimizations such as Raft and a custom storage engine, and real‑world results showing thousand‑fold throughput gains and robust operation during massive promotional events.
This article explains MySQL 8.0's new current‑password validation strategies, showing how to enforce old‑password checks per user or globally, with detailed command‑line examples and options to enable, disable, or customize the behavior for enhanced security.
This article details a MySQL InnoDB startup failure caused by ibdata1 lock errors, explains the underlying resource‑unavailable issue, and walks through removing stale socket lock files and restarting the server to achieve a successful launch.
This article presents a comprehensive analysis of flash‑sale (seckill) business characteristics, technical challenges, and architectural principles, offering detailed backend design solutions—including request interception, queue management, database sharding, caching strategies, optimistic locking, and anti‑cheating measures—to achieve high‑throughput, low‑latency processing for millions of concurrent users.
This article introduces MyBatis-PageHelper, a universal pagination plugin that supports a wide range of databases, explains how to configure custom dialect aliases, shows Maven and Spring integration, details numerous plugin parameters, and provides multiple code examples for various usage patterns.
This guide introduces MangoDB, an open‑source MongoDB‑compatible database built on PostgreSQL, and walks you through a three‑step Docker‑Compose setup, service startup, and connection using mongosh, enabling developers to quickly try a true open‑source MongoDB replacement.
This article introduces the RadonDB MySQL Operator, explaining its design goals, deployment topology on Kubernetes, detailed architecture—including RBAC, manager, custom resources, and services—and provides a visual overview to help engineers simplify MySQL high‑availability deployments in cloud‑native environments.
This article provides a systematic, step‑by‑step methodology for diagnosing Redis latency spikes, covering baseline performance testing, slow‑log analysis, high‑complexity commands, big‑key handling, expiration patterns, memory limits, fork overhead, huge‑page settings, AOF configurations, CPU binding, swap usage, memory fragmentation, network saturation, and practical monitoring tips.
This article explains how MySQL InnoDB implements locking, the two‑phase lock protocol, the four transaction isolation levels, the differences between pessimistic and optimistic locks, MVCC implementation, and how next‑key and gap locks prevent phantom reads in high‑concurrency environments.
This guide walks you through setting up a comprehensive logistics and warehouse management system using Spring Boot and Jeecg‑boot, covering required environment versions, technology stack, database configuration, backend and frontend setup, and deployment steps with detailed screenshots and code snippets.
This article presents fifteen practical SQL optimization techniques—including avoiding SELECT *, using UNION ALL, leveraging IN/EXISTS, batching inserts, applying LIMIT, controlling IN list size, incremental queries, efficient pagination, preferring JOINs, limiting join tables, managing indexes, choosing proper column types, improving GROUP BY, and using EXPLAIN—to help developers dramatically improve query speed and resource usage.
This article presents fifteen practical SQL optimization tips—including avoiding SELECT *, preferring UNION ALL, leveraging small‑table‑driven queries, batching inserts, applying LIMIT, handling large IN lists, incremental fetching, efficient pagination, join strategies, index management, field‑type choices, and using EXPLAIN—to dramatically improve MySQL query speed and resource usage.
This article presents a thorough analysis of performance problems in production business systems, covering root causes such as concurrency, data growth, hardware limits, database and middleware bottlenecks, JVM tuning, code inefficiencies, and offers practical diagnostic steps, monitoring tools, and optimization strategies.
This article examines Martin Kleppmann’s view of Kafka as a database, breaking down how Kafka handles atomicity, consistency, isolation, and durability through event streams and partitioning, and argues why MySQL still remains essential for many workloads.
This guide explains how to scale a Redis cluster by deploying new nodes, using redis‑trib to add them, redistributing hash slots through resharding, and setting up a four‑master four‑slave cross‑replication topology while verifying cluster health with CLI commands.
This article introduces MySQL 8.0's enhanced JSON capabilities, covering storage constraints, utility functions, path expressions, in‑place updates, indexing, merging, extraction operators, and the JSON_TABLE function, with clear code examples for each feature.
This article explains how to analyze, diagnose, and optimize performance problems in production business systems, covering root causes such as high concurrency, data growth, environment changes, hardware limits, database and middleware tuning, JVM settings, code inefficiencies, and the role of monitoring and APM tools.
This article explains why MySQL fails to start on Ubuntu due to AppArmor restrictions and provides two practical solutions: editing the AppArmor profile to grant MySQL directory access or switching AppArmor to complain mode, with full command examples and configuration snippets.
This article explains MySQL disaster‑recovery techniques, covering cold and hot backups, same‑city versus remote setups, master‑slave topologies, async, semi‑sync and full‑sync replication, the MAR strong‑sync approach, and practical recommendations for building resilient two‑city three‑center architectures.
This article introduces MyBatis‑Mate, an official MyBatis‑Plus extension that provides enterprise‑grade features such as sharding, data auditing, field encryption, sensitive data masking, dictionary binding, dynamic DDL maintenance, and flexible data‑source switching, complete with usage examples and code snippets.
This article introduces LevelDB, a Google‑built key/value store, and demonstrates how to integrate it in Java and Go projects by showing dependency setup, basic operations such as opening, putting, getting, deleting, iterating, and closing the database, and explains the typical use cases and file layout.
This article explains common reasons why Spring's @Transactional annotation can become ineffective—such as self‑invocation, non‑public methods, multiple data sources, incorrect rollback settings, and unsupported database engines—and provides concrete code examples and solutions to ensure proper transaction management.
This article explains Redis Sentinel’s role in high‑availability deployments, covering its architecture, monitoring and notification mechanisms, automatic failover process, configuration steps for master‑slave and sentinel nodes, and practical guidelines for building a reliable Redis cluster.
The article details Tencent Cloud's TDSQL‑C cloud‑native database, explaining traditional database bottlenecks, its four standout features—PB‑level storage, second‑level scaling, rapid snapshot backup, and serverless pay‑as‑you‑go—and the deep kernel optimizations that enable these capabilities while outlining future performance and cross‑region goals.
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 guide walks through setting up PostgreSQL's Point‑In‑Time Recovery (PITR) by enabling WAL archiving, performing a base backup, simulating data loss, and restoring the database to a specific timestamp, complete with command‑line examples and verification steps.
This article explains how to use the mysqldump command to back up MySQL databases, provides various command examples for full or partial backups, demonstrates a Bash script for automated daily backups with log management, and shows how to schedule these tasks using crontab.
The article provides a comprehensive overview of global unique ID generation in distributed systems, detailing the characteristics of a good ID, comparing five common strategies—database auto‑increment, UUID, Redis, Zookeeper, and Twitter Snowflake—along with their pros, cons, and practical optimization tips.
The article presents mybatis-mate, an enterprise‑grade MyBatis module that provides sharding, data auditing, sensitive‑word filtering, field encryption, dictionary write‑back, data permission, automatic DDL maintenance, dynamic multi‑datasource switching, and distributed transaction logging, offering developers a more agile and elegant way to handle data.
The article explains a common MySQL UPDATE mistake where developers use AND between column assignments, shows the unexpected result of owner_code becoming 0, analyzes the logical expression behind it, and provides the correct comma‑separated syntax to reliably update multiple fields.
This tutorial explains how MySQL implements full‑text search, covering the creation of full‑text indexes (including Chinese ngram support), the three query modes (natural language, boolean, and query expansion), relevance ranking, underlying inverted‑index structures, cache handling, and common DML operations.
This article explains how to implement a lottery feature in a Java backend, covering database schema design for prizes, probability limits and records, constructing a prize pool, the random selection algorithm, handling prize limits, and using a factory pattern to distribute various prize types.
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.
