The article recounts a real‑world incident where a complex SQL query took over 20 seconds, explains how to pinpoint whether the slowdown originates from joins or nested subqueries, and shares practical steps and insights for diagnosing and fixing such performance issues.
A detailed case study describes how a rapidly growing edit‑log feature caused query times to soar to 30 seconds, and walks through the step‑by‑step investigation, identification of a custom function bottleneck, data‑volume analysis, and the eventual implementation of partitioning, mandatory time filters, and composite indexing to restore acceptable performance.
This article compiles the most discussed DBA questions from multiple WeChat groups in November, covering DB2 query latency, Oracle import/export challenges, MySQL monitoring, SQL performance nuances, partition table design, and practical solutions shared by experts.
This tutorial demonstrates how MySQL inner, left, and right joins combine related tables, using student and score examples to show query syntax, result differences, and how missing data is handled with NULL values.
This article provides a detailed, hands‑on walkthrough of Oracle Data Guard switchover in normal operation and the subsequent steps to convert the original primary to a standby, including all necessary SQL commands, instance restarts, and verification queries.
This guide explains the most commonly used Oracle permission‑view tables, shows how to list role assignments, object privileges, system privileges, session privileges, and role memberships, and demonstrates the impact of the WITH ADMIN OPTION through practical SQL examples.
This article explains Oracle deadlock fundamentals, classifies row‑level and block‑level (ITL) deadlocks, shows how to read trace files in both single‑instance and RAC environments, and provides step‑by‑step simulations that reveal root causes and practical mitigation techniques.
This guide walks through MySQL fundamentals—including viewing, creating, and using databases, managing users and privileges, creating and altering tables, inserting, updating, deleting, and querying data, as well as index types, best‑practice tips, and execution‑plan analysis—providing concrete commands and examples for each step.
Phoenix adds a SQL layer to HBase, enabling easy table creation, data import, and complex queries via JDBC, with features like secondary indexes and integration with Spark, Hive, and more, illustrated through step‑by‑step examples and sample code.
This comprehensive guide presents the most common MongoDB interview questions, covering NoSQL fundamentals, replication, sharding, transactions, profiling, storage nuances, and operational details, providing concise yet thorough answers to help candidates confidently tackle technical interviews.
This guide explains MySQL's Multi‑Source Replication feature, its use cases, and provides a detailed GTID‑based configuration process—including master and slave setup, unique server IDs, replication user creation, and verification of successful replication across multiple masters.
A lottery system that relied on MySQL’s SELECT without ORDER BY unintentionally gave a single player multiple top‑prize wins because InnoDB reordered results by primary key, revealing a subtle database engine rule that can corrupt random selection logic.
This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.
This article explains Redis's five core data types, their internal representations and typical commands, details memory‑saving parameters for each type, compares the four persistence methods with their trade‑offs, and offers practical recommendations for optimal performance and stability.
This guide walks you through installing MySQL on Windows, setting up the Python pymysql driver, using MySQL Workbench for visual database management, and writing basic Python code to create, read, update, and delete records in a MySQL database.
This article compares traditional relational table design with HBase's column‑family model, showing how tables, rows, column families, and versioned cells are defined and how data is inserted using HBase's put commands.
This guide compiles 34 practical Oracle SQL optimization techniques—from selecting the optimal table order and rewriting WHERE clauses to leveraging indexes, avoiding costly operators, and fine‑tuning execution plans—helping developers dramatically reduce query execution time and resource consumption.
This guide explains why backup security is critical for enterprise databases, introduces Oracle RMAN's three encryption strategies, and provides step‑by‑step commands to configure password‑based encryption, perform backups, handle decryption errors, and finally disable encryption safely.
A developer recounts how a seemingly trivial MySQL UPDATE statement—‘UPDATE table_name SET sum = sum + 5 WHERE id = 1’—proved thread‑safe for aggregating file counts across a multi‑threaded module, highlighting the importance of knowledge reserves and cautious evaluation before implementing complex synchronization solutions.
This guide introduces MySQL 8.0’s new JSON aggregation functions, demonstrates how to create tables, insert data, and use JSON_ARRAYAGG and JSON_OBJECTAGG to combine rows into JSON arrays or objects, and provides a real‑world product‑attribute example that returns complete JSON structures.
This article explains core database concepts such as sharding, replication, routing strategies, and high‑availability designs, detailing how to achieve read/write separation, cache integration, and consistency handling in a master‑slave architecture for large‑scale systems.
This guide walks through exporting Oracle PL/SQL user objects and table data with PL/SQL Developer, then restoring them by dropping the existing user, recreating it with necessary privileges, executing the exported SQL script, and importing table data while handling constraints and triggers.
This guide explains what Oracle bad blocks are, how they affect the database, common causes, methods to detect them, and step‑by‑step recovery techniques—including full datafile restore, RMAN block recovery, ROWID range scans, EVENT 10231, and DBMS_REPAIR—plus pre‑emptive detection tools.
The article introduces Ctrip DAL, an open‑source Java/C# database access framework that supports sharding across MySQL and SQL Server, provides a code generator and client library, and helps large enterprises manage multi‑team, multi‑database environments while ensuring scalability, consistency, and security.
This guide walks through installing MySQL Shell and Router, deploying three local MySQL instances, creating an InnoDB cluster, adding the instances, configuring MySQL Router, and testing failover to demonstrate a simplified high‑availability setup.
This guide shows how to restore a MySQL database that was accidentally dropped by combining a nightly full mysqldump backup (with master‑log info) and the subsequent binlog files, extracting only the needed statements and replaying them in order.
This article explains how to define Java enum classes, demonstrates their usage with code snippets, and shows a practical technique for converting numeric database values into readable Chinese labels for display in user interfaces.
Learn how to build and manage a Redis cluster on Linux, covering the rationale for clustering, hash slot mechanics, node configuration, adding/removing nodes, resharding, and integrating master‑slave replicas, with step‑by‑step commands and practical examples.
This article provides a comprehensive guide to Oracle hierarchical queries, covering fundamental concepts, key clauses and operators, practical examples with the s_emp table, advanced features like SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF, NOCYCLE, and real‑world use cases such as reporting, pruning branches, and calculating aggregates in tree structures.
This article examines the limitations of traditional database provisioning, explains why a platform‑based RDS solution is needed, and details LeTV's evolution of its cloud‑native RDS service—including Docker containerization, architecture upgrades, elastic scaling, monitoring, and lessons learned—providing a comprehensive guide for building efficient, automated database platforms.
When writing integration tests that involve database operations, developers must ensure that test execution does not pollute the original data, and the article explores strategies such as using transaction rollbacks, recreating databases, and mocking database interfaces to maintain data integrity.
The article explains how MySQL’s ease of use, open‑source nature, early replication support, flexible storage‑engine architecture, and strong community contributions have driven its widespread adoption and sustained popularity in modern high‑concurrency, high‑availability applications.
This tutorial shows how to create a three‑node MongoDB replica set using Docker, configure automatic primary election, verify data synchronization across secondaries, and simulate a primary failure to observe failover, providing a practical guide for high‑availability database deployments.
This guide walks through practical MySQL optimization steps, including status checks, variable inspection, slow‑query and general‑log configuration, essential management commands, detailed mysqldump usage, mysqldumpslow analysis, and the mysqlsla utility for performance diagnostics.
This guide explains how to back up a MySQL Galera Cluster by leveraging the State Snapshot Transfer (SST) mechanism, detailing the default and custom scripts, required parameters, execution flow, and the key benefits of using SST for point‑in‑time, non‑intrusive backups.
This article explains how traditional ACID transactions fall short for modern large‑scale systems and presents lock‑free transaction methods, atomic and partial cache‑switch techniques, and a PostgreSQL‑style MVCC model for key‑value databases, including practical rules and trade‑offs.
This guide compiles a comprehensive set of MySQL learning materials—including introductory and advanced books, online courses, podcasts, and a curated toolbox of performance, monitoring, client, and migration utilities—helping aspiring DBAs build solid SQL skills and master MySQL operations.
This article provides a comprehensive overview of SQL Server transaction concepts, ACID properties, lock types and escalation, blocking analysis using system DMVs, isolation level configurations, and deadlock detection and resolution, illustrated with practical code examples and diagrams.
This article explains the theoretical foundations of relational databases, outlines the essential storage, engine, and UI layers, and walks through a concrete minimal implementation using fixed‑length tables, B+‑tree indexes, simple SQL parsing with regular expressions, and a TCP‑based client interface.
This article provides a detailed walkthrough of MHA's core configuration parameters—including server scopes, connection settings, candidate master rules, failover scripts, and monitoring options—explaining where each setting belongs and how to fine‑tune MySQL high‑availability behavior.
This article examines MySQL's network communication stack—from the high‑level MySQL packet protocol down to the socket layer—detailing session creation, NET/VIO handling, result‑set transmission, metadata overhead, and performance measurements using MySQL 5.7.
This article details a step‑by‑step investigation of a two‑node Oracle RAC environment where node 2 repeatedly rebooted, covering time‑synchronization checks, database and network heartbeat analysis, hardware diagnostics, and the final resolution through firmware and memory replacement.
This article compares MySQL query optimization and caching for a greenfield project, outlining their respective advantages and disadvantages, and concludes with guidance on when to prioritize database tuning over cache implementation.
This guide demonstrates how MySQL 5.7.12’s new JavaScript‑based shell lets you query, insert, update, and delete both traditional tables and JSON document collections using intuitive method‑chaining syntax, eliminating the need for SQL knowledge and simplifying migration from MongoDB‑style workflows.
This guide demonstrates how to create MySQL tables with JSON columns, insert JSON data, perform queries using JSON functions, and update JSON fields, covering essential functions like JSON_CONTAINS, JSON_SEARCH, JSON_EXTRACT, JSON_MERGE, and JSON_SET.
This article walks through a real‑world project's performance evolution, detailing server specs, a litany of scalability and reliability problems, and concrete code‑level solutions such as database deadlock mitigation, transaction shortening, thread‑pool redesign, and logging improvements.
This guide explains how to locate rows with identical values across one or several columns using self‑joins, GROUP BY, DISTINCT, and rowid, and provides step‑by‑step SQL statements to safely delete the extra duplicate entries from a table.
MySQL 5.7 introduces major enhancements such as native JSON support, an expanded Performance Schema, a new SYS Schema for health monitoring, improved security, advanced GIS capabilities, three‑fold query throughput gains, InnoDB optimizations, multi‑source replication, and a smarter optimizer with dynamic costing.
This article provides a curated collection of online MongoDB learning resources, including the official website, Chinese community, issue tracker, blogs, and various tutorial sites such as Shiyanlou, imooc, Yiibai, Runoob, and others for developers seeking comprehensive database education.
MySQL 5.7 adds native JSON document storage, merging relational database strengths with NoSQL flexibility, enabling developers to use a single system for both structured and document‑oriented data while preserving ACID transactions and indexing capabilities.
A MySQL master crashed while its sync_binlog was set to 0, causing the binlog cache to lose data and making the slave’s binary log longer than the master’s, highlighting the importance of configuring sync_binlog for durability versus performance.
Redis 3.2 introduces a concise GEO API that lets you store geographic points, perform radius queries, retrieve distances, and manage location data using commands like GEOADD, GEORADIUS, GEORADIUSBYMEMBER, GEODIST, and ZRANGE, while highlighting its underlying sorted‑set storage model.
The article proposes a cross‑IDC disaster‑recovery architecture for key‑value stores that uses a storage proxy layer to record write logs in the primary data center, forward them to a read‑only backup center for replay, and route reads via load‑balancing, while noting current limitations such as complex configuration, low synchronization concurrency, and lack of multi‑write support.
Redis replication offers high‑availability through master‑slave syncing, supporting multiple slaves, cascading replication, non‑blocking data transfer, read‑write separation, and optional disk‑less sync, with detailed steps for configuration, safety considerations, partial resynchronization, and practical deployment examples.
This article explains Oracle 12.2 sharding concepts—including table families, sharded versus duplicated tables, chunk definition and movement, shardspace usage, sharding methods, and step‑by‑step deployment commands—providing detailed guidance for DBA practitioners.
This article analyzes flash‑sale (抢购) business scenarios, outlines a layered architecture separating business and data layers, explains decoupling front‑end pressure, uses Redis‑based queues and caching to manage high‑frequency inventory checks, and describes multi‑party reconciliation mechanisms to ensure reliable stock consistency under extreme load.
This article outlines ten practical guidelines for operations engineers—ranging from mandatory rollback testing and cautious handling of destructive commands to robust backup verification, vigilant monitoring, and disciplined handover procedures—to dramatically reduce system outages and improve overall reliability.
A high‑load MySQL server was investigated, revealing that a seemingly simple subquery caused full table scans, but rewriting it as a JOIN or using an equality subquery enabled index usage, and newer MySQL versions have fixed the optimizer bug.
This article dissects the technical challenges of high‑traffic flash‑sale (seckill) systems, outlining typical e‑commerce flow, unique seckill characteristics, and detailed solutions for isolation, load handling, bandwidth, request throttling, order processing, database design, caching, concurrency control, and anti‑cheat mechanisms.
This article reviews the main MySQL high‑availability architectures—including shared‑storage SAN, DRBD disk replication, keepalived/heartbeat, MHA, ZooKeeper‑based HA, Galera/PXC clustering, and middleware proxy solutions—detailing their principles, advantages, limitations, and suitability for different business scenarios.
Oracle’s block access mechanism involves hashing block addresses to locate hash buckets, using CBC latches and buffer pins to coordinate logical and physical reads, and following distinct step‑by‑step procedures for non‑unique index reads, unique index reads, and write operations, ensuring concurrency safety.
This guide explains why data backup is essential, outlines backup types (full, incremental, differential), compares MySQL backup methods (hot, warm, cold), evaluates physical vs logical approaches, reviews common tools like mysqldump, cp, tar, LVM snapshots, and xtrabackup, and offers strategies for choosing the right solution.
This comprehensive guide enumerates over forty common causes of slow SQL Server queries and provides detailed optimization techniques—including indexing strategies, hardware upgrades, partitioning, cursor handling, and configuration tweaks—to dramatically improve query performance and reduce resource bottlenecks.
NeDB is a pure‑JavaScript, MongoDB‑compatible lightweight database that can be embedded in Node services or desktop applications without requiring a separate installation, offering in‑memory or persistent storage, rich query features, decent write/read performance, and easy npm installation.
This article explains Oracle trigger fundamentals, syntax, and a series of practical examples—including preventing weekend updates, auto‑incrementing IDs, logging DML actions, aggregating department salaries, capturing deletions, view‑based inserts, salary change alerts, and tracking CREATE/DROP operations—complete with full SQL code snippets.
This article compares NoSQL and SQL databases, highlighting NoSQL’s strengths in scalability, flexible schemas, and performance, while also discussing its lack of unified standards, operational complexity, and why SQL remains essential for strong consistency and transactional integrity in critical systems.
This article explains how LeTV's payment platform handled a hundred‑fold surge in traffic by redesigning its architecture with database sharding, a custom order‑ID scheme, asynchronous consistency, high‑availability clustering, data tiering, and a coarse‑fine request pipeline to sustain up to 100,000 orders per second.
This article summarizes Gai Guoqiang's presentation on Oracle Database DevOps practices, covering Oracle's cloud strategy, the shifting competitive landscape, the impact of cloud on DBA roles, and how DevOps principles are reshaping traditional database operations and performance management.
This article explores the major enhancements in Oracle 12c's optimizer, including session‑level statistics for global temporary tables, new DBMS_STATS reporting procedures, adaptive features, advanced join elimination methods, incremental and concurrent statistics collection, and automatic column‑group detection, providing practical guidance for DBAs and developers.
This guide explains step‑by‑step how to offline Oracle datafiles or tablespaces, copy them to a new directory, rename them, recover if needed, bring them back online, and safely shut down and restart the database to complete the migration.
Oracle profiles let administrators enforce password policies and resource limits such as CPU time, I/O, session count, and idle periods, with detailed parameters for both kernel resources and password rules, and the article provides a complete example of creating a custom profile using SQL.
In his Alibaba Technology Forum speech at Tsinghua, Wang Jian outlines how Alibaba’s cloud, database, and operating‑system initiatives—along with quantum‑computing research—aim to turn computing into a public utility, positioning China to lead global technological infrastructure.
This article presents a series of MySQL backend improvements—including Group Commit redesign, DDL fast‑fail handling, GTID allocation refinements, persistent InnoDB auto‑increment values, replication parallelism, and key‑partition hash algorithm changes—detailing motivations, implementation steps, and performance results.
This article explains the multiple encoding/decoding steps MySQL performs when storing and retrieving data, identifies why mismatched character sets cause mojibake, and provides practical methods to avoid and repair corrupted text in MySQL tables.
This guide explains the different Oracle database installation methods, focuses on silent installation using response files, shows the required file templates, demonstrates the runInstaller command, and walks through typical permission and syntax errors with practical troubleshooting steps.
This article examines the various scenarios in which MySQL can lose data, covering InnoDB and MyISAM engine behaviors, flush policies, master‑slave replication pitfalls, XA transaction handling, and practical solutions to improve consistency and availability.
This guide shows how NULL values break numeric calculations in PostgreSQL and demonstrates using the COALESCE function to substitute default numbers, with step‑by‑step SQL examples that turn unexpected NULL results into accurate sums.
This guide shows how to split a delimited string into a one‑dimensional array in Oracle SQL using a custom hierarchical query, avoiding the restrictions of DBMS_UTILITY.UNCL_ARRAY and allowing any delimiter for flexible data processing.
This article explains the legacy java.util.Date class, its limited useful constructors and methods, the more flexible java.util.Calendar API, how to format and parse dates with DateFormat and SimpleDateFormat, and best practices for storing timestamps in databases using long values.
This article explains how WordPress uses the wp_postmeta table for flexible metadata storage and the hook (action) system for dynamic code insertion, illustrating both the benefits and trade‑offs of these extensibility techniques for plugin development.
This article explains how Oracle 12c's In-Memory feature enables hybrid OLTP/OLAP workloads by storing columnar data in a dedicated memory area, covering its architecture, data loading, consistency mechanisms, query acceleration techniques, and integration with RAC for high‑availability deployments.
The article details Stack Overflow’s 2016 infrastructure upgrades—including hardware, networking, load balancing, caching, database, and service layers—that enabled the site to process over 61 million daily requests while reducing processing time by hundreds of hours.
This article explains MySQL InnoDB full-text indexing, covering supported modes, auxiliary tables, creation, DML handling, query processing, transaction management, cache synchronization, optimization, background threads, monitoring, stopwords, plugins, and the built‑in n‑gram parser, with code references from MySQL 5.7.
This article provides a comprehensive overview of Oracle Database architecture, detailing the server components, instance memory structures such as SGA, essential background processes, and the physical and logical storage layers, helping readers understand how Oracle organizes data and manages operations.
This article reports a hands‑on experiment comparing single‑column and composite covered indexes in MySQL, measuring their storage overhead and insert latency on a million‑row table, and reflects on the importance of data‑driven reasoning versus intuition.
This article details Dazhong Dianping's practical approach to sharding its massive order database, covering vertical and horizontal partitioning strategies, specific sharding algorithms, unique ID design, data migration phases, and lessons learned for long‑term scalability.
This article reveals why Oracle’s CREATE TABLE documentation spans dozens of pages and walks readers through essential Oracle database concepts such as tables, indexes, sequences, views, clusters, tablespaces, database links, and synonyms to help master the platform efficiently.
A benchmark comparing Redis performance with and without the MONITOR command shows a substantial drop in throughput—up to 53% for SET operations—highlighting that MONITOR should be used only for short‑term debugging, as illustrated by a real‑world Meituan case study.
This article explains how Redis Lua scripting can make operations faster, cut network overhead, guarantee atomic execution, and enable script reuse, and it provides practical examples including a simple hello‑world script and a URL‑shortening implementation.
The article explains the concepts of vertical and horizontal database splitting, compares their technical difficulties, discusses the impact on joins and transactions, and proposes practical approaches such as careful SQL refactoring, ORM decoupling, and selective use of distributed‑transaction techniques to maintain system stability and scalability.
Redis 3 now officially supports clustering, introducing hash slots and dynamic node management to simplify key distribution, scaling, and fault tolerance, allowing seamless read/write operations across multiple nodes without manual hashing, and offering features like online node addition, automatic slave monitoring, and performance‑aware key allocation.
By leveraging MySQL's asynchronous query capabilities via mysqlnd and PHP's generator-based coroutines, developers can execute multiple SELECT statements concurrently, reducing total query time from around two seconds to roughly one second, though this approach must consider server load and thread overhead.
This article presents the full specifications and solution approaches for a two‑part Oracle SQL challenge that requires inserting every possible terminal Tic‑Tac‑Toe game state into a table and writing a query to determine forced wins from any valid board configuration.
The article details the ITPUB "Shengtou Media Cup" SQL contest, presenting two Oracle‑based Tic‑Tac‑Toe problems—one to list every possible terminal game sequence and insert it into a table, and another to compute forced wins from any valid board—along with solution approaches and extra challenges.
This technical session covers Oracle RAC high‑availability best practices, installation steps, daily operational commands, detailed case studies of auto‑start checks, version‑mix issues, addNode failures, network heartbeat problems, and client connection errors, plus a concise Q&A on uninstall, SCAN vs VIP, and split‑brain detection.
This article explains character sets and encodings, shows why MySQL data can become garbled, and provides step‑by‑step methods—including proper configuration, common pitfalls, and reliable repair techniques—to prevent and correct encoding issues such as emoji storage failures.
An overview of HBase’s 2015 milestones—including the stable 1.0 release, clearer API with BufferedMutator, multi‑region replicas for high availability, family‑level flush improvements, RPC call‑queue separation, online configuration changes, and various Q&A on performance, replication, and best‑practice design considerations.
This guide outlines a structured MySQL DBA interview process, covering self‑introduction, resume deep‑dive, career‑development questions, fundamental knowledge such as index types, core technical skills like backup, replication and performance troubleshooting, and broader potential areas including Linux and programming language proficiency.
This article explains MySQL's indexing mechanisms—including BTREE and hash structures, page layout, index types, and practical query patterns—while showing how to interpret EXPLAIN output and avoid common pitfalls for efficient database performance.
This article reviews real‑world Redis Cluster deployments from Youdao, Qihoo 360 and Mango TV, analyzes its architectural drawbacks, client challenges, implementation limitations, performance impact, and summarizes the overall advantages and disadvantages of using Redis Cluster as a distributed database solution.
