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.
This article explains the core principles of performance optimization, distinguishes active and passive approaches, and provides detailed MySQL passive tuning techniques—including index usage, data partitioning, slow‑query logging, and read/write splitting—to prevent and resolve slow SQL queries.
This article explains how MySQL's AUTO_INCREMENT generates unique identifiers, the constraints on its use, how deletions affect the sequence, the role of LAST_INSERT_ID in concurrent environments, and best practices for managing auto‑increment columns in InnoDB tables.
This article explains how ClickHouse's MergeTree engine creates partition directories, the naming convention for partitions, and the automatic and manual merging processes that consolidate parts while tracking block numbers and merge levels.
Brad Nicholson and Dave Cramer discuss their experiences with PostgreSQL, covering the project's community strengths and weaknesses, emerging pluggable storage, JSON path, Kubernetes deployment challenges, performance tuning, and their vision for the database’s evolution over the next decade.
Hibernate ORM 5.4.20, a popular Java object‑relational mapping framework, introduces several bug fixes, new tasks such as CockroachDB support, and performance improvements, aiming to simplify database persistence and reduce manual SQL/JDBC coding for developers.
In this interview, Apache CouchDB project leaders Adam Kocoloski and Jan Lehnardt discuss the database's unique replication model, strengths and weaknesses, notable real‑world deployments, upcoming 3.0 and 4.0 releases, Kubernetes deployment advice, and guidance for newcomers to Erlang and the CouchDB community.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
The article explains why a small database connection pool often yields better performance than a large one, illustrates the point with Oracle and PostgreSQL benchmark data, and provides a simple formula to calculate the optimal pool size based on CPU cores and effective disks.
This guide walks you through connecting IntelliJ IDEA to a database, using its built‑in POJO generators, adding JPA modules, and creating custom Groovy scripts to produce fully annotated JPA entity classes, complete with getters, setters, and utility methods.
This article provides a detailed comparison of eight widely used Redis visualization tools—including desktop clients, web applications, and IDE plugins—covering their features, UI quality, pricing, command‑line integration, support for Redis data types such as streams, and overall suitability for developers.
This article explains MySQL's implicit and explicit locking mechanisms—including table, row, and intention locks—covers lock modes, MVCC, transaction isolation levels, optimistic and pessimistic locking, gap locks, and deadlock causes and solutions, providing practical SQL examples for each concept.
A single‑instance Oracle 11.2.0.4 primary‑standby setup on Windows encountered ORA‑600 errors on archived logs 17620 and 17621 after manual file deletions, and the detailed troubleshooting revealed a mismatched COMPATIBLE parameter that was corrected, allowing the standby to apply the missing logs and resume synchronization.
The article provides a step‑by‑step tutorial on configuring database connections in IntelliJ IDEA, using its built‑in tools to generate simple POJOs or JPA‑annotated entities, and creating a custom Groovy script to produce fully annotated Java entity classes directly from a database schema.
This article explains how to design a high‑frequency like/unlike feature by caching operations in Redis, persisting data to a relational database at regular intervals, and orchestrating the process with Spring Boot, Spring Cloud, and Quartz scheduled jobs, complete with code examples and schema design.
The article evaluates MySQL Shell 8.0.21's new logical backup and restore method—featuring ZSTD compression, chunked parallel export, and parallel LOAD DATA import—against mysqldump, mysqlpump, and mydumper, presenting benchmark results, analysis, and practical command examples.
This article introduces Apache Phoenix, an open‑source SQL layer for HBase that enables JDBC‑based table creation, data insertion and querying, while supporting secondary indexes, transactions and various optimizations, and outlines a series covering its syntax, tools, best‑practice and real‑world use cases.
This article examines seven key reasons why deploying databases in Docker containers is problematic, covering data safety, performance bottlenecks, networking complexities, statefulness, resource isolation, cloud platform constraints, and hardware requirements, while also suggesting cautious strategies for selective containerization.
Understanding CPU usage in MySQL involves recognizing that user processes, IO waits, and system interrupts dominate consumption, and by reducing IO, optimizing queries, indexing, and minimizing costly calculations, you can lower CPU load, improve throughput, and avoid performance bottlenecks.
A Java Spring service method misused map.contains and REQUIRES_NEW transaction propagation, leading to unreleased DB connections and a CannotCreateTransactionException, and the article walks through the bug, its root causes, and concrete fixes including correct key checks and timeout settings.
This article explains MySQL index structures—including B‑Tree, B+Tree, and hash indexes—their underlying principles, performance characteristics, and practical usage guidelines such as left‑most prefix, full‑text indexing, and common pitfalls, providing a comprehensive guide for database developers.
Redis Sentinel is a crucial component of Redis clustering that monitors master and slave instances, sends alerts, performs automatic failover, and updates clients with new master addresses, while operating as a distributed system requiring a quorum of nodes to ensure robust high‑availability.
This guide explains how to restore MySQL databases when the server cannot start by generating table definitions with mysqlfrm, discarding damaged InnoDB tablespaces, copying old .ibd files, and importing them back, followed by verification using mysqlcheck.
This article explains why using SELECT 1 with LIMIT 1 is a more efficient alternative to COUNT(*) when checking for the existence of rows, shows the common and optimized SQL/Java code patterns, and discusses the performance benefits and impact on index usage.
Redis ensures high availability through two persistence mechanisms—RDB snapshots and AOF logs—each with distinct advantages, trade‑offs, and configuration nuances, and this guide explains how they work, when to choose each, and how they impact performance and data safety.
This article explains how to build distributed locks using three different technologies—database tables, Redis, and Zookeeper—detailing their implementation steps, code examples, advantages, drawbacks, and practical considerations for choosing the right solution in real‑world scenarios.
This article explains how to use MySQL's EXPLAIN statement to view execution plans, describes the meaning of each of the twelve output fields, provides practical examples with sample tables and queries, and offers optimization tips such as index creation and the left‑most prefix rule.
This article explains InnoDB's 16KB page structure, row formats, record metadata, primary key strategies, and how B+Tree indexes—including clustered, secondary, and covering indexes—organize and retrieve data, with practical SQL examples and performance considerations.
This article explores Redis 6.0’s new client‑side caching feature, detailing the tracking command, its options such as BCAST, PREFIX, OPTIN, OPTOUT, REDIRECT and NOLOOP, and provides step‑by‑step examples using telnet and redis‑cli to demonstrate invalidation messages and configuration.
Although Docker has become popular for deploying many applications, this article outlines seven technical reasons—ranging from data safety and performance to networking, statefulness, resource isolation, cloud platform limitations, and environment requirements—that make containerizing databases generally impractical, while also suggesting cautious scenarios where it might work.
This article provides a comprehensive overview of using Lua scripts with Redis, covering Lua basics, advantages of Lua scripting, detailed Redis‑Lua integration syntax, command usage, return‑type mappings, script management commands, and step‑by‑step instructions for installing Lua and executing example scripts.
This article provides an in‑depth overview of MySQL, covering storage engine concepts, selecting appropriate data types, index design and usage, view creation, stored procedure syntax, variable handling, and trigger implementation, with practical examples and SQL code snippets to illustrate each topic.
The article explains that MySQL’s legacy “utf8” character set only supports three‑byte sequences and therefore cannot store true UTF‑8 characters, describes the historical reasons behind this limitation, and advises all MySQL/MariaDB users to migrate to the proper utf8mb4 charset.
This article explains Redis's custom Simple Dynamic String (SDS) implementation, its dynamic expansion mechanism, and the performance benefits such as O(1) length retrieval, buffer overflow avoidance, and efficient memory allocation strategies for high‑throughput applications.
