The article explains why using COUNT(*) for existence checks is inefficient, proposes using SELECT 1 with LIMIT 1 in SQL and a null‑check in Java, provides code examples, and highlights the performance improvements of this optimization.
This article explains MySQL 8’s newly supported CHECK constraints, outlines usage restrictions, and provides step‑by‑step examples—including simple age validation and complex gender‑age logic—showing how to create, test, alter, and drop constraints while highlighting practical considerations.
This article explains how to diagnose MySQL client connection failures by checking whether the mysqld process is running, verifying network communication settings such as skip‑networking and bind‑address, and examining authentication or password issues via the error log.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
The article explains how ThinkPHP’s query method works with raw SQL, distinguishes between execute for writes and query for reads, details MySQL master‑slave replication considerations, and describes the framework’s database connection initialization, including read/write separation logic and related code examples.
This article provides a comprehensive overview of the relational model and SQL, covering fundamental concepts, set‑based operations such as SELECT, WHERE, GROUP BY, UNION, and various JOIN types, along with practical code examples and explanations of integrity constraints and DML statements.
MySQL startup failures are typically caused by either insufficient system resource access (such as file permissions or security policies) or incorrect configuration parameters, and can be diagnosed by checking permissions, security policies, and the order and content of MySQL option files.
This article explains the fundamentals of single- and multi-data-source transactions, reviews common distributed-transaction solutions such as 2PC, 3PC, TCC, transaction-status tables and message-queue based eventual consistency, and then details Seata’s AT mode implementation, including its workflow, undo-log mechanism, and isolation handling.
This article provides a detailed walkthrough of MySQL protocol analysis using Wireshark, covering SSL disabling, packet capture commands, step‑by‑step examination of handshake, authentication, database selection, query execution packets, and an in‑depth look at related source‑code functions and command enums.
This article introduces HBase’s column‑oriented architecture, explains the roles of Master, RegionServer, and Zookeeper, and provides detailed environment preparation and installation commands for setting up an HBase cluster on Hadoop.
This article explains the functional and performance differences among MySQL's count(1), count(*), and count(column) functions, illustrates how NULL handling varies, and provides a concrete example demonstrating their results and execution efficiency under different table schemas.
In his 2020 talk, Prof. Zhou Xuan outlined the evolution of database systems, highlighted ECNU’s research on distributed transactions, hardware‑aware modular designs, HTAP and system decoupling, and argued that future databases will be increasingly modular, hardware‑adapted, and driven by close academia‑industry collaboration.
This article explains how to prevent duplicate order numbers in ERP systems under high concurrency by creating a dedicated table and a MySQL stored procedure that generates, validates, inserts, and returns a uniquely formatted order identifier.
This guide explains seven critical Linux operating‑system settings—memory overcommit, swappiness, Transparent Huge Pages, OOM killer, NTP synchronization, ulimit, and TCP backlog—that together ensure Redis runs reliably and at peak speed.
This article explains why GTID‑based replication is preferred for synchronizing data from four regional factories to an IDC, and provides a step‑by‑step guide—including data export, GTID configuration, master‑slave channel setup, adding a new slave, and important precautions—for successful multi‑source MySQL replication.
This article explains why a MySQL query that uses an index can still become a slow query, explores the difference between index usage and execution time, and presents practical optimization techniques such as improving selectivity, reducing back‑table lookups, using index condition push‑down, and leveraging virtual columns.
This guide walks you through installing the Go MongoDB driver, establishing connections, and performing full CRUD operations—including inserting single and multiple documents, querying, updating, and deleting records—while also showing how to retrieve server status and explaining BSON handling in Go.
This article explains how MySQL's CASE WHEN, IF, and IFNULL functions can be used to implement conditional logic in SQL queries, providing syntax, examples, and combinations with aggregate functions for common data‑analysis scenarios.
This article explains the concept of Multiversion Concurrency Control (MVCC), how it solves read‑write blocking, deadlocks and consistency issues, and details InnoDB’s implementation—including transaction IDs, hidden columns, undo logs, and the behavior of snapshot and current reads under various isolation levels.
This article provides a comprehensive guide to advanced Redis operations, covering pipeline usage, transaction handling, publish/subscribe mechanisms, key migration techniques, custom command implementation with Jedis, and efficient key scanning methods, complete with code examples and performance comparisons.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed ordering, EXISTS clauses, condition push‑down, early limiting, intermediate result push‑down, and demonstrates how rewriting queries with proper indexes, JOINs, UNION, and WITH statements can dramatically reduce execution time.
This article explains the fundamentals of index structures—including binary trees, balanced AVL trees, B trees, and B+ trees—detailing their characteristics, search complexities, and how they are applied in MySQL indexing to reduce disk I/O and improve query performance.
This article explains the structure of MySQL ROW‑mode binlogs, demonstrates how to modify binlog events with Python to recover deleted rows, locate oversized transactions, and split large transactions into smaller ones, providing practical scripts and examples for advanced database manipulation.
This article explains MySQL lock types—table, row, and page locks—their algorithms, common causes of deadlocks, and provides multiple practical examples with SQL statements and diagrams, followed by InnoDB lock‑prevention strategies and detailed analysis of concurrent delete scenarios.
This article explains MySQL master‑slave replication by covering SBR, RBR, and MBR concepts, step‑by‑step configuration of master and slave servers, binlog format settings, the underlying replication protocol, key SQL commands, and troubleshooting tips, all illustrated with real code snippets and diagrams.
This article explains how Redis introduced Lua scripting to combine multiple commands atomically, covers Lua basics, data types, control structures, script execution with EVAL/EVALSHA, script management commands, and practical tips for writing efficient, safe Redis Lua scripts.
This article explains how to use Redis Sentinel to achieve high availability, covering its core functions, configuration steps, three methods of receiving failover notifications, and a recommended overall design with diagrams and code examples.
This article evaluates two techniques for accelerating rollback of large MySQL transactions—dynamically enlarging the InnoDB buffer pool and using innodb_force_recovery=3 with instance shutdown—by presenting experimental steps, code examples, performance measurements, and practical recommendations.
A sudden P1 incident reset all user passwords, and after a thorough investigation the team discovered that a security‑scanning tool’s weak‑password check repeatedly hit login attempts, triggering a bug that caused the outage, highlighting the critical need for proper incident response and security engineering.
This article provides a comprehensive, step‑by‑step guide to using programmatic transactions in Spring, covering both PlatformTransactionManager and TransactionTemplate approaches, complete with Maven configuration, SQL setup, test code, and best‑practice usage patterns.
This article explains the three primary ways to remove data in MySQL—DELETE, TRUNCATE, and DROP—comparing their execution speed, underlying mechanisms, impact on disk space, transaction behavior, and how to reclaim space with OPTIMIZE, while providing practical SQL examples and usage warnings.
This article introduces SQLAlchemy as a Python ORM, shows how to install and configure it in a Flask project, defines Article and Article_Detail models, demonstrates CRUD routes, explains database migration with Flask‑Migrate, and covers comment handling and rollback procedures.
This article presents a comprehensive methodology for database performance optimization, covering objectives, key metrics, common pitfalls, a step‑by‑step process, instance‑level best practices, and detailed parameter‑tuning tables for Oracle, MySQL (InnoDB), PostgreSQL, and DM databases.
This article traces the evolution of openGauss from its Huawei roots and systematically evaluates how well openGauss 1.0.0 supports Oracle’s various table types, providing creation examples, compatibility results, and guidance on work‑arounds for unsupported features.
This article presents a comprehensive collection of 50 SQL interview questions, complete with sample queries that demonstrate various operations such as joins, subqueries, aggregations, updates, and deletions on student, course, teacher, and enrollment tables.
This article explains how to design and implement elegant RESTful APIs using ThinkJS, covering routing conventions, controller generation, custom router configuration, CRUD action mapping, token‑based authentication, permission validation with Logic layers, inheritance for reusable checks, and database operations with chainable queries.
This article explains the relational model, its data structures, set-oriented operations, and integrity constraints, then demonstrates core SQL clauses such as SELECT, WHERE, GROUP BY, UNION, INTERSECT, EXCEPT, and various JOIN types with clear examples and diagrams.
This article explains why using indexes does not guarantee fast queries, analyzes slow‑query causes such as full index scans and poor filter selectivity, and demonstrates advanced MySQL optimizations including virtual columns and index condition push‑down to reduce row scans and costly table lookups.
This article explains how to identify slow‑query symptoms, use system and SQL‑level indicators, apply various database tools, follow best‑practice writing tips, read execution plans, and walk through a complete MySQL optimization case to dramatically improve performance.
A concise collection of Linux command‑line snippets helps you query Oracle client IPs, kill specific processes, count connections, summarize traffic, find large files, measure copy time, and monitor CPU and memory usage, all useful for DB and system administrators.
This article explains MySQL 8.0 window functions, provides clear definitions, demonstrates basic and advanced usage with sample tables and SQL queries, and lists all available window functions, helping readers apply ranking and cumulative calculations in real‑world database scenarios.
This article explains the concept of idempotency, examines common failure scenarios such as duplicate order creation, coupon redemption, and inventory deduction, and presents practical solutions—including unique identifiers, locking, database constraints, and state‑machine approaches—accompanied by concrete SQL and pseudo‑code examples.
This article explains MySQL's table, row, and page lock levels, the mechanics of next‑key locks, common deadlock scenarios with practical examples, and offers concrete SQL techniques and InnoDB strategies to prevent and troubleshoot deadlocks.
This guide explains how to configure Redis Sentinel to monitor master‑slave instances, automatically promote a slave on master failure, and verify the high‑availability setup with detailed configuration files, startup commands, status checks, and failover testing steps.
This article explains the typical reasons for Redis latency spikes—including complex commands, large keys, expiration bursts, memory limits, fork overhead, AOF settings, swap usage, and network saturation—and provides practical steps and commands to identify and mitigate each issue.
This article presents a comprehensive guide to MySQL performance tuning, covering the use of EXPLAIN, proper handling of IN clauses, selective column queries, limit usage, index best practices, avoiding costly operations, pagination techniques, full‑text search, join optimization, and advanced directives such as FORCE INDEX and STRAIGHT_JOIN.
This article explains why using Redis's KEYS command can block other operations, illustrates the underlying single‑threaded execution model, and shows how upgrading shiro‑redis to use the SCAN command avoids performance issues in distributed session management.
This guide provides step‑by‑step instructions for exporting entire MySQL databases or individual tables, importing data, performing common DDL and DML operations such as creating, altering, renaming, and dropping databases and tables, managing fields, and configuring user permissions, all illustrated with concrete command‑line examples.
This article explains the fundamentals of distributed systems, covering the CAP and BASE theorems, caching strategies, message queues, database choices, JVM optimization, and practical architectural improvements applied to New Oriental's mobile app to enhance availability and performance.
The article explains how MySQL stores persistent data while Redis serves read‑only data, outlines read/write request handling, discusses consistency issues in low and high concurrency scenarios, and provides practical solutions such as cache invalidation, queue‑based updates, and deployment considerations.
This article explains how Redis Sentinel provides automatic failover and high availability by using master‑slave replication, multiple Sentinel nodes, consensus‑based leader election, and client notification mechanisms to ensure continuous service despite node failures.
This article explains Redis's high‑availability persistence mechanisms, detailing the two storage options—RDB snapshots and AOF append‑only logs—their configurations, advantages, drawbacks, and suitable use‑cases, helping readers choose the proper method for data safety and performance.
This article explains how MySQL implements transaction features such as atomicity, durability, and isolation by using redo logs, undo logs, lock mechanisms, and MVCC, and discusses the role of each component in ensuring data consistency and reliability.
The article narrates Redis’s journey to implement durable storage, contrasting RDB snapshotting and AOF logging, detailing configuration options, forked processes, and trade‑offs, while illustrating interactions with MySQL’s binlog to highlight persistence strategies and performance considerations.
This tutorial explains how to upgrade a SpringBoot application from a single‑node Redis setup to a Redis cluster by adjusting configuration parameters, adding the correct Maven dependencies, injecting a RedisTemplate with JedisConnectionFactory, and providing a reusable utility class for common Redis operations.
This article explains how MySQL calculates table and index statistics, distinguishes between persistent and non‑persistent statistics, details relevant InnoDB parameters, shows when and how statistics are updated, and provides example queries and commands for manual analysis.
This article explains why a high‑performance Redis instance can become slow, covering typical latency causes such as complex commands, big keys, concentrated expirations, memory limits, fork overhead, CPU binding, AOF settings, swap usage, and network saturation, and provides practical diagnosis steps and optimization recommendations for both developers and operators.
This article explains why Redis latency can suddenly increase—covering high‑complexity commands, large keys, concentrated expirations, memory limits, fork overhead, CPU binding, AOF settings, swap usage, and network saturation—and provides practical diagnostic steps and mitigation techniques.
This article explains what MySQL master‑slave replication is, outlines its benefits, details the underlying mechanisms and event types, and provides a comprehensive, command‑line tutorial for configuring both master and slave servers, complete with code snippets and illustrative diagrams.
This article explains why a Redis instance may experience sudden latency spikes, walks through diagnosing the problem with slow‑log and big‑key scans, and provides concrete configuration tweaks, command‑level best practices, and operational guidelines to keep Redis performing at peak speed.
This article provides a detailed, step‑by‑step guide to MySQL covering its purpose, installation, directory layout, common commands, data types, table creation and modification, constraints, functions, subqueries, joins, custom functions, stored procedures, transaction control, and user management, illustrated with extensive code examples and screenshots.
This article explains why phantom reads occur under MySQL's REPEATABLE READ isolation, how InnoDB uses MVCC for snapshot reads and next‑key locks for current reads, and demonstrates the mechanisms with code examples and version‑tracking diagrams.
This article explains the SQL standard restriction on SELECT columns after GROUP BY, demonstrates the error caused by ONLY_FULL_GROUP_BY, explores MySQL SQL modes that affect this behavior, and clarifies why aggregated queries cannot reference non‑grouped columns.
The article examines the evolving role of Database Administrators, tracing their origins from ops engineers to high‑paid specialists, highlighting how cloud‑based Database‑as‑a‑Service and PaaS offerings are automating traditional DBA tasks, causing job declines and prompting DBAs to adapt their skills toward cloud, performance, security, and business‑oriented data management.
This article provides a comprehensive examination of MySQL/InnoDB locking, covering MVCC, snapshot vs. current reads, cluster indexes, two‑phase locking, isolation levels, detailed lock‑behaviour for various index and isolation combinations, complex query lock analysis, and deadlock detection techniques.
This article analyzes a 60‑second MySQL query that retrieves article comments with multiple filters, explains why the optimizer chooses a small table as the driver, and presents a step‑by‑step optimization—including avoiding semi‑joins, improving index usage, refining range conditions, and moving GROUP BY into a subquery—that reduces execution time to 1.3 seconds, achieving a 60‑fold speedup.
This article examines whether MySQL's count(1) is faster than count(*), explains how InnoDB processes both expressions identically, compares them with count(column), and offers practical advice on avoiding costly full‑table counts.
This article explains the SQL standard restriction that only grouped keys, aggregate functions, or constants may appear in the SELECT clause of a GROUP BY query, demonstrates the resulting error in MySQL, and discusses how SQL modes like ONLY_FULL_GROUP_BY enforce this rule.
This article reviews six practical URL deduplication methods—including Java Set, Redis Set, database queries, unique indexes, Guava Bloom filter, and Redis Bloom filter—explaining their principles, providing complete implementation code, and recommending the most suitable approach for different system scales.
This article explains the InnoDB logical storage hierarchy of segments, extents, and pages, then details MySQL horizontal partitioning concepts, types (RANGE, LIST, HASH, KEY), example DDL and DML, and discusses how partitioning impacts performance for OLTP and OLAP workloads.
Although Docker has become popular for containerizing applications, this article outlines seven key drawbacks—ranging from data safety and performance to networking, statefulness, resource isolation, cloud platform incompatibility, and hardware demands—that make deploying databases in Docker containers generally unsuitable.
This article explains the scenarios where MySQL compressed tables are appropriate, describes how to monitor their health using InnoDB CMP tables in information_schema, and provides practical examples of creation, performance comparison, and update/delete operations to illustrate best‑practice usage.
This article explains the concept of caching, why database caching with Redis is needed for MySQL, the consistency challenges it introduces, and compares four practical solutions—TTL, write‑through, message‑queue, and binlog replication—offering guidance on selecting the appropriate approach for different latency and reliability requirements.
This article explains Redis sharding, covering its purpose, various partitioning methods such as range and hash partitioning, implementation approaches like client‑side and proxy‑assisted sharding, their drawbacks, and practical steps for migration and using Redis Cluster or Twemproxy.
This article investigates MySQL's recommendation against UUID primary keys by creating three tables with auto‑increment, UUID, and random Snowflake IDs, benchmarking insert and query speeds using Spring JDBC, analyzing index structures, and discussing the trade‑offs of each approach.
This article announces a monthly giveaway of the newly upgraded 5th edition of Ben Forta's SQL Essentials book, outlines its features, author background, and target readers, and provides a detailed tutorial on using the SELECT statement to retrieve single and multiple columns with example code.
This article explains the fundamentals of SQL Server transactions, the ACID properties, various lock types and modes, how to analyze and resolve blocking, the behavior of different isolation levels, and strategies for detecting and handling deadlocks, providing practical T‑SQL examples throughout.
This article explains how ClickHouse's MergeTree engine creates partition directories based on a partition key, details the naming convention PartitionID_MinBlockNum_MaxBlockNum_Level, and describes the automatic and manual merge processes that consolidate partitions for efficient storage.
This tutorial walks through integrating MyBatis with Spring Boot, covering Maven dependencies, datasource configuration, mapper scanning, basic CRUD XML mappings, the differences between #{ } and ${ }, auto‑generated key handling, SQL fragments, and enabling camel‑case mapping for seamless Java‑to‑database interaction.
The article explains SAP HANA’s performance advantages by combining four key technologies—high‑speed memory, columnar storage, data compression, and an insert‑only model—detailing their individual pros and cons, how they complement each other, and the trade‑offs involved in scaling and persistence.
This article introduces the SlightShift MySQL high‑availability solution, detailing cloud‑native design principles, architecture, key technologies such as Raft‑based leader election, Local PV storage, ProxySQL routing, automated failover, backup/recovery, and the operator‑driven declarative management that enable scalable, low‑cost, resilient MySQL deployments on Kubernetes.
This article explains the two persistence mechanisms of Redis—RDB snapshots and AOF append‑only logs—detailing how each works, their respective benefits such as backup compactness and durability, and their drawbacks including potential data loss windows and larger file sizes.
This article explains three practical ways to implement distributed locks—using a relational database, Redis cache, and Zookeeper—detailing their underlying mechanisms, Java code examples, performance trade‑offs, and a side‑by‑side comparison of strengths and weaknesses.
This article explains the performance differences between IN, EXISTS, NOT IN, and NOT EXISTS in SQL queries, provides guidance on when to use each construct based on table sizes and indexes, and demonstrates the concepts with practical code examples.
The article explains why SQL is essential for data scientists, introduces PostgreSQL as a powerful open‑source relational database suited for large‑scale data science, outlines its key features, advantages and disadvantages, and provides practical learning resources for beginners.
This article provides a comprehensive tutorial on using EF Core in .NET, covering DbContext setup for MySQL and SQL Server, thread‑safety considerations, connection‑pooling benefits, logging of generated SQL, various insertion techniques (single, cascade, bulk), querying methods, and efficient update patterns.
iQIYI’s custom MySQL HA solution replaces the single‑point‑failure MHA manager with a Raft‑based trio of HA Masters and HA Agents that continuously monitor instances, automatically perform same‑DC, cross‑DC, or cross‑region failovers, reconcile diff binlogs, ensure data consistency, and integrate with CMDB, tracing and chaos‑engineering platforms.
Tencent Cloud’s managed ClickHouse service now includes an automated data‑balancing feature that, after user authorization and bandwidth configuration, creates migration plans to redistribute tables across new or decommissioned nodes, eliminating manual rebalancing, reducing operational overhead, and ensuring balanced storage during elastic scaling.
MySQL 8.x adds a lightweight backup lock, LOCK INSTANCE FOR BACKUP, which permits DML during online backups while restricting only file creation, account changes, and certain table operations, requiring BACKUP_ADMIN privilege and offering a less disruptive alternative to FTWRL, used by MySQL Enterprise Backup and Percona Xtrabackup.
This article explains ClickHouse's four composite data types—Array, Tuple, Enum, and Nested—detailing their definitions, type inference rules, practical SQL examples, and common pitfalls to help developers model complex data structures efficiently.
This article explains how MySQL's EXPLAIN command reveals the execution plan of a query, detailing each column such as id, select_type, table, type, possible_keys, key, key_len, ref, rows, extra and filtered, while also covering its limitations and a practical case study.
This guide explains what MySQL temporary tables are, how they exist only for the current connection, provides step‑by‑step SQL examples for creating, inserting, querying, and dropping a temporary table, and discusses practical scenarios such as high‑concurrency writes.
This article provides a detailed, step‑by‑step tutorial on building a six‑node Redis cluster on a single Linux machine, covering directory setup, configuration files, Ruby‑based cluster tools, instance startup, cluster creation, verification, online scaling, slot rebalancing, and using Redis for session sharing.
This article reviews common primary key generation methods—including auto‑increment drawbacks and five alternatives such as UUID, step, segment, Snowflake, and Redis—explaining their mechanisms, advantages, and trade‑offs to help you select the best fit for high‑concurrency and distributed systems.
This article introduces MySQL 8.0.20 Group Replication, covering single‑master and multi‑master modes, monitoring, failover procedures, abnormal recovery, flow control, performance testing, encountered issues, and limitations, and provides a downloadable PDF with detailed documentation hosted on Baidu Cloud.
Learn how to set up Oracle Database for Python, install and configure the 64‑bit Instant Client, resolve common issues like Chinese character encoding, install the cx_Oracle driver, and perform data queries using fetchone, fetchall, and pandas DataFrames.
This article explains how to construct SQL INSERT statements for string, numeric, date, and boolean fields, demonstrates variable concatenation for dynamic values, and provides a comprehensive example that combines multiple data types into a single query.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
This article explains three practical implementations of distributed locks—using a relational database, Redis cache, and Zookeeper—detailing their mechanisms, Java code examples, advantages, disadvantages, and comparative analysis of performance and reliability.
