This article explains how PHP can connect to various databases, execute queries, fetch results, insert or update records, close connections, handle errors, and address security concerns, providing code examples for both MySQLi and PDO extensions.
This article explains the principles and practical designs of high‑performance database clusters, covering read/write separation, master‑slave replication lag, distribution mechanisms, sharding (both vertical and horizontal), business‑level partitioning, and the role of NoSQL technologies such as key‑value stores, document databases, columnar databases, and full‑text search engines.
This article demonstrates why @Transactional fails in multithreaded Spring services, presents a thread‑pool based solution using manual SqlSession control to achieve full rollback across child threads, and provides complete code examples and test results illustrating both failure and successful transaction handling.
This article examines why MySQL permits creating multiple identical indexes on the same column, demonstrates the behavior through several tests, compares it with Oracle's stricter restrictions, and explains how the optimizer selects indexes, highlighting practical implications for database design.
This article explains MySQL's locking mechanisms, covering when locks are applied, how they are acquired—including explicit and implicit locks—and guidelines on when to use or avoid them, with examples of lock behavior under different isolation levels and query scenarios.
An urgent overnight incident revealed a MySQL server’s CPU soaring to 400% due to poorly written queries, prompting a detailed analysis of execution plans, identification of costly operations like filesort and temporary tables, and concrete recommendations for query and team improvements.
This article explains why MySQL backups are essential, defines RTO and RPO, compares physical and logical backup methods, describes snapshot, binary‑log, incremental and differential strategies, and provides best‑practice recommendations and example commands for reliable backup and recovery.
The AIKOS community weekly newsletter shares the SQLE 3.0 release, recent development work, upcoming plans, curated MySQL and OceanBase technical articles, industry highlights, and announcements for future events, providing valuable insights for database practitioners and open‑source contributors.
This article introduces NoSQL databases, explains their core concepts, typical use cases, architectural components, and contrasts them with relational databases, helping readers understand when and why to choose NoSQL solutions for large‑scale, unstructured data workloads.
The article analyzes the importance of system stability during major sales promotions, presents data‑driven insights on traffic and revenue, identifies key challenges such as massive traffic, data volume, and complex workflows, and offers comprehensive operational, application, storage, and monitoring measures to guarantee reliable performance under extreme load.
This article explains what a MySQL deadlock is, shows a concrete example using two conflicting transactions on a simple test table, demonstrates how to detect the deadlock with InnoDB status output, and provides practical tips for preventing deadlocks in production systems.
This article explains how to parse OceanBase logs to trace the tenant freeze dump workflow, detailing the roles and log sequences of the freeze check thread, LSFreeze, Flush, DagScheduler, and MiniMerge threads, and illustrating each step with actual log excerpts and code snippets.
This article examines MySQL's comprehensive lock taxonomy, starting from common row and table locks, then delving into lesser‑known mechanisms such as global locks, intention locks, auto‑increment locks, and Latch, while explaining their commands, use‑cases, and performance implications for developers and DBAs.
The article walks through diagnosing a 10‑second backend loading delay, explains connection‑pool concepts, introduces Alibaba's Druid pool with its monitoring UI, shows how to identify and optimize slow SQL queries using DISTINCT and indexes, and provides step‑by‑step SpringBoot integration instructions.
This article explains why full‑text search is needed, how InnoDB implements it with inverted indexes, the syntax for creating and using full‑text indexes, the different query modes (natural language, boolean, query expansion), and how to delete full‑text indexes in MySQL.
Improving web page load speed is crucial for user experience, and PHP developers can employ methods such as caching, script and stylesheet compression, database query optimization, reducing HTTP requests, using CDNs, output compression, cache plugins, asynchronous loading, and image optimization to significantly enhance performance.
This article presents a collection of SQL statements—including SELECT, DELETE, DESCRIBE, and JOIN queries—targeting various tables in the idc_evaluating database, accompanied by detailed English explanations of their purpose, syntax, and result set structure.
This article explains MySQL's various lock mechanisms—including shared (S) and exclusive (X) locks, intention locks, record, gap, next‑key, and insert‑intention locks—illustrates their behavior with SQL examples, analyzes a concurrent insert deadlock scenario, and offers practical mitigation strategies.
This article explains how Redis implements geospatial indexing with GEOADD and GEORADIUS commands, walks through their source‑code implementations, describes the underlying geohash calculations and nine‑cell grid search strategy, and derives the O(N + log M) time complexity for nearby‑people queries.
A Java ToB system struggled to meet a 500 requests‑per‑second target, revealing hidden bottlenecks such as slow SQL, excessive logging, thread‑pool misconfiguration, high CPU usage from Spring bean creation, and improper Redis usage, which were systematically identified and mitigated.
This article explains essential techniques for managing concurrent access and race conditions in PHP, covering mutex locks, semaphores, atomic operations, queue-based processing, database access optimization, and transaction management to improve system reliability and performance.
This article explains the principles of disaster recovery for MySQL, covering RTO/RPO metrics, national backup level standards, common master‑slave topologies, a comparative analysis of high‑availability solutions, and a detailed three‑center multi‑region replication design with code patches to avoid replication loops.
The newsletter announces the SQLE 3.0 release with new UI and demo environments, highlights the SQLE 2.2310.0‑pre3 update, reviews technical articles on MySQL cloning, OceanBase indexing, data replication, charset issues, and connection‑pool design, recaps recent conferences, and previews upcoming OB Cloud open class events.
This article explains ten data‑analysis techniques for enhancing user experience—such as behavior tracking, A/B testing, sentiment analysis, and personalization—and provides concrete SQL code snippets that illustrate how to import, query, filter, sort, aggregate, join, update, delete, and back up data in relational databases.
This article explains how to build a personal center for a WeChat public account using PHP, covering database table creation, retrieving user information via WeChat authorization, storing data, and rendering a user profile page with sample code snippets.
This announcement details a book giveaway sponsored by Tsinghua University Press and presents a comprehensive SQL learning curriculum covering SQLite, MySQL, query fundamentals, subqueries, joins, and advanced database features, complete with micro‑lecture videos and hands‑on exercises.
This article explains Redis' client‑server request model, introduces the pipeline technique, compares normal and pipelined requests, and provides Spring Boot code examples that demonstrate a three‑fold speed improvement when writing 100,000 keys.
This article provides a comprehensive overview of Redis memory management, detailing how maxmemory limits trigger various eviction policies, explaining the internal freeMemoryIfNeeded algorithm, and describing expiration mechanisms—including active and lazy deletion—and offering guidance on selecting the appropriate eviction strategy for different workloads.
Redis is exploring a disk‑backed tiered storage architecture, allowing infrequently used data to move from memory to flash and back, a move aimed at reducing costs, improving scalability, and expanding its popularity among developers and gaming applications.
This article explains the concepts and advantages of NoSQL databases, categorizes the four main types, introduces MongoDB’s data model and components, and provides step‑by‑step instructions for installing MongoDB 7.0 on CentOS 7 using both yum repository and manual RPM packages.
This article presents practical Spring performance optimization techniques—including AOP‑based logging, second‑level caching with @Cacheable, reducing database queries via fetch strategies, employing thread‑pool executors, and general database query tuning—to improve application speed and resource efficiency.
This article explains how LEFT JOIN works in SQL, clarifying the difference between ON‑clause conditions that affect the join process and WHERE‑clause filters that operate on the resulting temporary table, using multiple example queries and visual illustrations.
This article presents a typical requirement to calculate count, minimum, maximum, and average scores for three subjects in a student_score table, demonstrates how to fulfill it using raw SQL, then shows step‑by‑step implementations with Fluent MyBatis, native MyBatis XML, and MyBatis‑Plus code generation, and finally compares the three approaches highlighting their pros and cons.
This article explains how Linux page size, especially using 2 MB or 1 GB huge pages, dramatically improves database throughput on Kubernetes nodes—showing up to an eight‑fold increase for 4 KB pages—by reducing TLB misses and optimizing memory access, and provides practical guidance for configuring huge pages in various environments.
This article presents practical methods—including indexing, query consolidation, field selection, PHP code refinements, and caching strategies with Redis and page caching—to significantly improve the performance of PHP‑MySQL applications, accompanied by concrete code examples.
This article provides a detailed walkthrough of a Tencent backend interview covering Java thread models, HashMap internals, garbage collection, operating‑system concepts, process scheduling, IPC mechanisms, I/O multiplexing, virtual memory, networking protocols, Redis data types, MySQL B+‑tree characteristics, and a classic logic puzzle, offering both theory and practical insights.
This article explains how to configure transaction timeout in Spring 5.3.23 using annotations and programmatic approaches, demonstrates four test scenarios with a large table, analyzes the resulting timeout exceptions, and delves into the underlying mechanisms in DataSourceTransactionManager, JdbcTemplate, and related utility classes.
This technical report details the background, hardware configuration, database setup, tuning results, and step‑by‑step optimization procedures—including BIOS, OS, network, container NUMA binding, MySQL CRC32 patching, and Go PGO tuning—performed to improve JED performance on ARM compared with Intel.
This article demonstrates two practical approaches for handling MySQL auto‑increment columns during migration to OB Oracle—creating custom sequences with DBCAT and using the GENERATED BY DEFAULT AS IDENTITY attribute—provides step‑by‑step commands, scripts, and validation results to help DBA engineers achieve seamless primary‑key migration.
This article explains how to debug and optimize PHP database connections using PDO and mysqli, demonstrates error handling and logging techniques, and presents performance improvements such as persistent connections, connection pooling, and query optimization with practical code examples.
This article outlines key PHP performance optimization strategies—including caching with Memcache or Redis, using efficient database access via PDO, selecting optimal loop constructs like foreach and array_map, and avoiding redundant function calls—to improve execution speed and user experience.
This article walks through how a Java application interacts with MySQL, covering the driver, connection pooling, thread handling, query parsing, the optimizer, storage engine, buffer pool, undo/redo logs, binlog, and transaction commit, providing a complete end‑to‑end view of MySQL’s internal processing.
Learn how MySQL's index merge optimization works, explore its three algorithms—intersect, union, and sort_union—understand when each applies, see practical setup and sample queries, and discover configuration flags and best‑practice tips to improve query efficiency.
This article explains MySQL's binlog as a binary log for recording data changes and master‑slave replication, then explores how parsing binlog can enable data heterogeneity, cache consistency, and task distribution, highlighting its role in building middleware solutions for backend systems.
This article explains how to design and implement a basic online recruitment platform using PHP, covering requirement analysis, system architecture, database schema, front‑end layout, back‑end logic, and provides a complete code example for user registration, illustrating the core functionalities of such a system.
This article explains how MySQL permission modifications behave at table, database, and global levels, demonstrates verification steps with Uproxy's connection pool, and clarifies why query cache settings and USE statements affect the visibility of privilege changes.
This guide outlines a comprehensive MySQL backup solution, covering backup strategy planning, choosing physical versus logical backups, using tools like mysqldump and Percona XtraBackup, automating backups with scripts and cron, storage and retention considerations, and regular recovery testing to ensure data integrity.
This guide walks through installing MySQL in Docker, configuring master‑slave replication, verifying synchronization, deploying a Canal server, and integrating it with a Spring Boot application to automatically push database changes into Redis.
This article explains the principle behind MySQL's index‑merge optimization, demonstrates how to set up test data, walks through the three merge algorithms (intersect, union, sort‑union), provides practical SQL examples, and shares tips for tuning and avoiding common pitfalls.
This article explains how MySQL 8 improves the auto‑increment counter by persisting its maximum value across server restarts, contrasting the behavior with MySQL 5.7 through detailed examples and SQL commands that demonstrate the differences in primary‑key generation.
The article explains Huawei's VLDB 2023 paper on a cloud‑native multi‑master database, detailing its architecture, novel VS‑clock and hybrid lock techniques, and presents extensive performance experiments that show near‑linear scalability and superior throughput and latency compared with Aurora and CockroachDB.
This article describes a real‑world OceanBase failure scenario where a core dump fills the data disk, and provides a step‑by‑step guide to shrink datafiles to 80% of disk capacity, adjust parameters, reduce tenant replicas, offline and reinstall OBServer instances, and finally restart the cluster.
The article explains PHP's stripslashes() function, its syntax, how to use it for removing backslash escapes in strings and database results, precautions such as escape order and scope, and best practices for safe string handling.
The Taobao platform’s slow‑SQL governance team implemented a comprehensive detection and governance pipeline—combining internal slow‑log tools, database slow‑query logs, and JVM‑Sandbox instrumentation to capture full SQL details, scoring high‑risk queries by execution time, scans, and standards violations, then prioritizing remediation through health scores, branch‑diff checks, and issue tracking—significantly cutting DB‑related incidents and boosting system stability.
PostgreSQL 16, released in September 2023, brings major enhancements in permission management, logical replication, performance, and monitoring, offering finer‑grained security, faster data handling, and comprehensive observability for modern database workloads.
BigKey and HotKey issues in Redis clusters arise when oversized keys block processing or a single key receives excessive requests, causing timeouts, memory overflow, shard collapse, and performance degradation, but can be detected during testing using Redis commands, visual clients, and open‑source analysis tools.
The article explains that, although MySQL’s theoretical limits are far higher, practical B+‑tree page and index calculations suggest keeping a single table under about twenty million one‑kilobyte rows to avoid performance degradation, illustrated with interview examples and estimation formulas.
This article explores comprehensive strategies for handling hot‑key bottlenecks in high‑traffic flash‑sale scenarios, covering DB, Redis, sharding, local cache, failure handling, and scaling techniques across various QPS ranges to ensure reliable inventory control and user experience.
The Aikexing Open Source Community weekly newsletter shares curated database articles, technical insights on MySQL, OceanBase, and Redis, announces SQLE 2.2309.0‑pre2 release with Feishu integration, recaps recent events like QCon Beijing, and promotes upcoming meetups and open‑source projects.
This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.
This article demonstrates how to use MySQL to create employee and performance tables, populate them with sample data, and write SQL statements that first select employees whose salaries exceed their department's average and then filter out those with non‑A performance ratings, providing a complete layoff‑target list.
This article introduces the open‑source web platform Mayfly-go, explains its key features for Linux system, MySQL, PostgreSQL, Redis, and MongoDB management, provides step‑by‑step installation and configuration instructions, and demonstrates how to use its project, machine, database, and system administration capabilities.
This article breaks down the true execution sequence of an SQL query—from FROM and JOIN through WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—explaining each stage with examples, code snippets, and visual diagrams to help developers write more efficient queries.
This guide walks through setting up a test environment, extracting binlog positions, generating rollback SQL with the my2sql tool, and safely re‑importing the data to restore rows mistakenly removed by a DELETE statement.
This article examines a MySQL 5.7 replication anomaly where a slave reports no errors despite GTID mismatches, demonstrates how to reproduce the issue, explains the underlying cause related to missing Auto_Position=1, and provides remediation steps and source‑code insights.
This article walks through the exact sequence a database follows when executing a SQL query—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT—highlighting key differences from the textual order of the statement.
This article examines whether adding the always‑true condition WHERE 1=1 in MySQL queries prevents index usage, by comparing EXPLAIN plans for queries with and without the clause and proposing a cleaner MyBatis mapping approach.
This article demystifies the actual execution sequence of SQL queries, detailing how FROM and JOIN establish table relationships, WHERE filters rows, GROUP BY groups data, HAVING applies aggregate conditions, SELECT retrieves fields, ORDER BY sorts results, and LIMIT restricts the final output.
This article examines why containerizing MySQL with Docker can be problematic, highlighting data safety concerns, performance degradation due to I/O bottlenecks, state management limitations, and resource isolation challenges, while also presenting scenarios where containerization may be feasible and offering mitigation strategies.
This article explains how MySQL InnoDB Cluster 8.1 introduces read‑only replica instances, shows step‑by‑step commands to create and configure them, describes their failover behavior, routing options with MySQL Router, health‑check isolation, replication‑lag handling, and how to hide replicas from traffic.
This article explains the true execution sequence of SQL statements—starting with FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT—illustrating each step with diagrams, examples, and code snippets to clarify how databases process queries.
This article explains MySQL transaction fundamentals, the ACID properties, how to start and control transactions, the problems of dirty, non‑repeatable and phantom reads, isolation level settings, MVCC mechanics, read‑view structures, and the role of undo logs in ensuring data consistency.
This article details the evolution and design of Vivo’s e‑commerce inventory platform, covering its layered architecture, multiple inventory types, deduction workflows, idempotent APIs, anti‑oversell mechanisms, high‑concurrency strategies, hotspot mitigation, and synchronization techniques, while explaining the reasoning behind each technical choice.
This article explains how to identify and remove duplicate rows in a MySQL table—keeping a single record per duplicate group—by using a self‑join DELETE statement or a nested subquery approach, complete with sample table creation and data insertion scripts.
This article explains MySQL’s leftmost prefix principle, shows how composite indexes are built and used, demonstrates which query patterns trigger index usage or full‑table scans, and provides practical tips for SELECT *, functions, LIKE, OR, IN, ORDER BY and sub‑queries to avoid index loss.
This article explains how to perform relational updates in MySQL using JOINs, subqueries, and multi‑table UPDATE statements, demonstrating table creation, sample data insertion, and the differences between INNER, LEFT, and RIGHT joins for keeping dependent tables in sync.
Oracle announces a new MySQL version model that separates Innovation releases, which deliver new features and frequent updates, from Long‑Term Support (LTS) releases that provide stable, minimal‑change versions with extended support, outlining upgrade paths, deprecation policies, and detailed release schedules.
Redis 7.2, the first Unified Redis Release, introduces extensive AI support, vector database capabilities, scalable search, server‑side Triggers and Functions, enhanced geospatial queries, performance‑boosted sorted sets, and the Redis Data Integration tool, while expanding client and protocol compatibility.
Redis 7.2, the first Unified Redis Release, introduces AI‑ready vector indexing, hybrid semantic search, scalable RAG support, server‑side Triggers and Functions, enhanced geospatial queries, and a preview of high‑performance searchable indexes, while expanding client library support and integrating Redis Data Integration for seamless enterprise data pipelines.
This article explains how to set up a MySQL test table and indexes, then enumerates nine typical scenarios—such as leading‑wildcard LIKE, arithmetic or functions on indexed columns, type mismatches, composite‑index misuse, character‑set differences, and OR conditions with non‑indexed fields—that cause queries to bypass indexes and perform full table scans.
The article analyzes a MySQL 5.7 replication failure where the slave SQL thread stops with error 1590 because the master writes an INCIDENT_EVENT to the binlog when a GRANT or CREATE USER statement partially succeeds due to password‑validation plugin constraints, leading to replication breakage.
This article explains various approaches to implementing an audit feature in a SpringBoot application, compares their pros and cons, provides detailed SQL schema, Java backend controller code, and Vue frontend integration, and includes complete code snippets for database tables, file handling, and UI components.
This article explains the common reasons why Redis performance degrades—such as network latency, high‑complexity commands, big keys, concentrated expirations, memory limits, fork overhead, huge pages, AOF settings, CPU binding, swap usage, and memory fragmentation—and provides detailed optimization and troubleshooting steps to restore low latency.
This article explains the purpose, classification, and implementation of MySQL locks—including global, table, page, row, intention, and gap/record/next-key locks—as well as optimistic and pessimistic locking strategies, providing code examples and usage scenarios for ensuring data consistency in concurrent transactions.
This article explains the architecture of TDSQL-C Serverless buffer pool, details how InnoDB buffer pool resize works, analyzes performance bottlenecks such as IO and mutex contention, and presents optimization techniques like chunk pre‑allocation, delayed release, and hash‑resize improvements that make serverless scaling more stable.
The article explains why using the KEYS command on a large Redis dataset can block the server, introduces the SCAN command as a non‑blocking alternative with cursor‑based iteration, and provides usage examples and best‑practice tips for safely listing prefixed keys.
The article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query approaches, ID‑range filtering, and temporary‑table methods—provides performance measurements on a table with millions of rows, and offers practical recommendations for reducing query latency in large datasets.
This article explains why MySQL, the world’s most popular open‑source relational database, is a fundamental skill for developers, operators and testers, and outlines a two‑book learning path covering installation, SQL mastery, InnoDB internals, indexing and performance optimization.
This article provides concise Python code snippets for connecting to and querying a variety of popular databases—including MySQL, PostgreSQL, SQLite, MongoDB, Oracle, SQL Server, Redis, Elasticsearch, Cassandra, Couchbase, Firebase, DynamoDB, Neo4j, and InfluxDB—illustrating the essential steps for each.
This article presents practical MySQL performance tuning techniques, covering query, pagination, join, subquery, order‑by, group‑by, and count optimizations with example code, index strategies, and execution‑plan analysis to improve latency in high‑traffic systems.
This article explains the three primary ways to delete data in MySQL—using DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, storage‑engine differences, and best‑practice considerations such as space reclamation and auto‑increment handling.
This article introduces MyBatis‑Flex, a lightweight yet high‑performance MyBatis enhancement framework, outlines its key features, compares it with similar tools, lists supported databases, and provides a step‑by‑step Spring Boot quick‑start tutorial with complete code examples.
This article presents several practical PHP performance optimization methods—including caching, avoiding duplicate database queries, merging and compressing assets, using MySQL query cache, and efficient database operations—accompanied by clear code examples to help developers improve web application speed.
Oracle introduces a new MySQL versioning strategy that separates Innovation releases, featuring frequent new features and fixes, from Long‑Term Support (LTS) releases with stable behavior, outlining transition timelines, upgrade/downgrade paths, HeatWave service options, and support lifecycles for developers and DBAs.
This article introduces MyBatis‑Flex, a lightweight yet powerful MyBatis enhancement framework, outlines its key features and advantages, compares it with similar tools, presents performance benchmarks, lists supported databases, and provides a step‑by‑step quick‑start tutorial with code examples for Spring Boot integration.
This article explains the Redis Sorted Set data type, its underlying storage mechanisms (listpack and skiplist + dict), the internal structures and algorithms, and demonstrates how to build a real‑time game leaderboard using ZADD, ZRANGE and ZREVRANK commands.
Oracle announces a new MySQL version model that separates Innovation releases, which deliver new features and frequent updates, from Long‑Term Support (LTS) releases that provide stable, minimal‑change versions with extended support, outlining transition paths, upgrade‑downgrade strategies, and product portfolio impacts.
This article investigates why OceanBase's memory freeze threshold changes during load testing by analyzing monitoring data, inspecting the relevant source code, and demonstrating debugging steps, ultimately revealing that the threshold is computed dynamically based on real‑time memory usage and can be lowered when other memory modules consume space.
This article describes a MySQL replication outage caused by an incorrectly set max_binlog_cache_size parameter, explains the underlying mechanism of binlog caching, and provides a step‑by‑step solution to adjust the setting and restore normal replication.
This article provides Python code examples for using Redis as a database—including connection, basic CRUD, list and sorted set operations, key expiration, publishing/subscribing, distributed locking, and caching—followed by a detailed comparison of Redis and MySQL across data model, persistence, query capabilities, performance, scalability, and transaction support.
