This article explains how applying machine learning to database operations—specifically AIOps for MySQL—can automate index recommendation by parsing SQL, extracting semantic and statistical features, generating candidate index combinations, and training an XGBoost model to predict optimal indexes, reducing reliance on manual DBA work.
Learn how to construct near‑real‑time Elasticsearch indexes for petabyte‑scale datasets by comparing MySQL limitations, leveraging inverted indexes, using Hive and wide tables, and employing binlog‑based pipelines with Canal and Otter to achieve second‑level index updates.
This article details the migration from MyBatis to MyBatis-Plus in a Java project, explains the LocalDateTime conversion exception caused by MyBatis 3.5.1 and an outdated mysql‑connector‑java driver, and outlines the step‑by‑step fixes including driver upgrades and lessons learned from a separate file‑generation bug.
This article explains why MySQL uses locks, categorizes lock types from global to row level, details their implementation and commands, and shows how intention, metadata, and auto‑increment locks work while offering strategies to prevent deadlocks and lock contention.
This article explains the technical reasons behind limiting MySQL single‑table rows to around 20 million, covering primary‑key limits, InnoDB data‑page structure, B+‑tree storage math, and practical estimation methods for large‑scale databases.
This article recounts a real‑world incident where MySQL 5.7 chose a sub‑optimal index, causing a three‑second slow query, and explains how the team identified the root cause, used EXPLAIN and optimizer trace, and applied fixes such as forced index hints and data cleanup.
This article explains how to design a SaaS system that isolates each subsidiary's data by adding a tenant_id column to a shared MySQL table, converting the table to LIST‑partitioned storage, and enforcing tenant‑level access through stored procedures and a MyBatis interceptor, complete with code examples and testing steps.
This course introduces MySQL 8's latest features, deep dives into its network communication mechanisms, and teaches advanced performance optimization techniques such as connection pooling, compression, and SSL encryption through hands‑on projects to enhance database efficiency and security.
This article explains MySQL 8.0.32 InnoDB's two‑phase commit process, detailing the flush, sync, and commit sub‑stages, the concept of group commit, and how queues and mutexes coordinate transaction logging to reduce redundant disk writes.
This article explains how to identify and extract large MySQL transactions from binary log files by monitoring binlog size, parsing GTID information, and using shell scripts to retrieve transaction details, timestamps, positions, and DML statistics, with optional use of the my2sql tool.
This article examines how database middleware tackles the demanding needs of large‑scale internet services by providing centralized connection‑pool management, transparent read‑write splitting, diverse load‑balancing algorithms, sharding support, automatic failover, security controls, comprehensive monitoring, and flexible backup‑recovery mechanisms.
The article traces MySQL’s origins from Monty Widenius’s early Unireg project through its rapid growth, acquisition by Sun and Oracle, and the emergence of MariaDB, highlighting how an open‑source, user‑focused ecosystem and the LAMP stack propelled MySQL to become the world’s most popular database.
To ensure the massive, multi‑platform membership service remains fast and reliable, this article details a multi‑center architecture using Elasticsearch for unified member data, Redis caching, and MySQL partitioning, along with traffic isolation, fault‑tolerant syncing, and fine‑grained flow‑control and degradation strategies.
The article demonstrates that adding an explicit primary key to a MySQL InnoDB table can dramatically reduce its on‑disk size by eliminating the hidden clustered index, and explains how invisible primary keys can be used when schema changes are not possible.
This article explains MySQL master‑slave replication, covering its three modes (asynchronous, semi‑synchronous, and fully synchronous), the underlying binary‑log mechanism, key replication threads, configuration steps, and includes a promotional notice for an upcoming live career‑development session.
This article walks through a MySQL 5.6 scenario with course, student, and score tables, demonstrates why a naïve sub‑query is extremely slow, and shows step‑by‑step how adding single‑column, composite, and covering indexes, as well as rewriting the query to joins, can reduce execution time from hours to milliseconds.
This article explains how MySQL InnoDB implements full‑text search, covering the underlying inverted index structures, how to create and drop full‑text indexes, the MATCH…AGAINST syntax, and detailed examples of natural language, boolean, and query‑expansion search modes with practical SQL demos.
This article chronicles the development of ZzoNlineDDL's schema‑change platform from its early 1.0 version with limited capabilities to the robust 2.0 release, detailing architectural diagrams, intelligent alteration logic, partitioning, scheduling, monitoring, user interaction, problem analysis, and practical recommendations for MySQL online DDL operations.
This article explains MySQL 8.3’s new ability to tag GTIDs, describes the original and extended GTID formats, demonstrates how to enable and use tagged GTIDs with mysql-shell, and shows how tagged and non-tagged transactions appear in replication logs.
This article examines the technical reasons why MySQL single tables struggle with massive data volumes, debunks the index‑depth myth, explains InnoDB's SMO concurrency bottlenecks, and shows how B‑Link Tree indexes and heap‑organized tables in GaussDB provide superior performance for large‑scale workloads.
An extensive benchmark compares MySQL 8.2 Community and Enterprise editions using sysbench and TPC‑C on identical EC2 instances, revealing that, under default configurations, the Enterprise edition offers no consistent performance advantage over the Community edition, with only occasional gains from optional plugins.
An in‑depth comparison of MySQL and PostgreSQL covers their histories, architectural differences, benchmark results showing PostgreSQL’s superior SELECT, INSERT, and UPDATE throughput, and guidance on selecting the appropriate database based on application complexity, scale, and performance requirements.
This article explains how InnoDB stores data pages and builds B+‑tree indexes, illustrates the structure of clustered, secondary and composite indexes, and enumerates typical scenarios—such as left‑most prefix violations, LIKE patterns, and function use—that cause index loss and full‑table scans.
This article provides a comprehensive guide to using PyMySQL in Python, covering installation, connection setup, CRUD operations, transaction handling, advanced features like parameterized queries and connection pooling, as well as best practices for security, performance, and monitoring.
This guide explains MySQL's native JSON data type, its storage format, advantages over text columns, and provides step‑by‑step examples for creating tables, inserting JSON, querying with path operators, using built‑in functions, and handling conditional searches.
This article explains the classic cache consistency problem between MySQL and Redis, analyzes why inconsistencies occur, and details four common design patterns—delete‑then‑update, update‑then‑invalidate (Cache‑Aside), Read/Write‑Through, and Write‑Behind—along with their advantages, drawbacks, and typical execution flows.
This article walks readers through building a complete vaccine management desktop application using Python's Tkinter for the graphical interface and MySQL for data storage, covering UI design, database connection, user registration, login, CRUD operations for vaccine records, and query functionalities, all illustrated with full source code examples.
This article compares MySQL and PostgreSQL, covering their histories, architecture differences, benchmark results, suitable application scenarios, and a concise summary of PostgreSQL's advantages and disadvantages relative to MySQL.
This comprehensive guide explains why MySQL sharding (分库分表) is essential for handling massive data growth, outlines various horizontal and vertical partitioning strategies, discusses common challenges such as hot‑spot, cross‑database queries, pagination, and transaction handling, and compares popular middleware solutions.
This article explains how MySQL 8.0.32 InnoDB generates binlog events, the role of binlog cache (stmt_cache and trx_cache), the two‑level cache mechanism, and the detailed process of writing events to the cache and flushing them to disk.
This article explains the space requirements of MySQL Online DDL operations, analyzes temporary log, sort, and intermediate files, provides step‑by‑step test preparation, demonstrates failure and recovery scenarios, and summarizes best practices for avoiding space‑related errors.
This article examines common MySQL performance pitfalls—such as large OFFSET limits, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down—and demonstrates concise rewrites, including JOINs, UNION ALL, early range reduction, and WITH clauses that can reduce query execution from seconds to milliseconds.
Learn how to secure MySQL 8 connections by enabling SSL, covering the protocol’s encryption and authentication principles, generating certificates, configuring server and client settings, and testing the encrypted connection with detailed commands and practical examples.
Facing rapid data growth, our team struggled with MySQL scaling, costly distributed refactoring, and inflexible storage; by adopting OceanBase’s native distributed HTAP platform we achieved elastic scaling, strong consistency, over 60% storage savings, and multi‑tenant performance gains for both OLTP and OLAP workloads.
This article explains how to use PHP's mysqli_query function to perform MySQL operations such as SELECT, INSERT, UPDATE, and DELETE, providing a step‑by‑step example that creates a connection, runs a SELECT query, processes results, and handles errors and cleanup.
This article explains MySQL deadlocks, describing how they occur in InnoDB due to resource contention, outlines common causes such as race conditions, poor indexing, long transactions, lock upgrades, and provides practical prevention strategies including deadlock detection, short transactions, query optimization, lock timeouts, monitoring tools, lock ordering, and appropriate lock levels.
This article explains why setting NULL as a column's default in MySQL can lead to index inefficiencies, unpredictable query results, and performance issues, and demonstrates proper handling of NULL with examples, operators, functions, and indexing considerations.
This article explains why defining columns with a default NULL in MySQL is misleading, demonstrates how NULL interacts with indexes, operators, aggregate functions, and sorting, and provides practical examples and recommendations to avoid performance and correctness issues.
This article explains why read‑write separation is essential for high‑concurrency systems and details three MySQL master‑slave synchronization methods—half‑synchronous replication, database‑middleware synchronization, and cache‑key write tracking—along with their advantages, drawbacks, and consistency considerations.
This article summarizes essential MySQL concepts—including basic terminology, data types, SQL commands, indexes, views, storage engines, backup methods, and performance tuning techniques—to help developers understand and effectively apply this powerful open‑source relational database in real‑world projects.
This article explains the principles of high-availability systems using Keepalived, covering VRRP heartbeat, virtual IP handling, health-check scripts for MySQL and RabbitMQ, and the step-by-step failover process, helping developers design resilient services.
This guide explains what MySQL deadlocks are, shows how to use InnoDB status and performance‑schema queries to locate them, demonstrates a reproducible deadlock scenario, and provides practical prevention techniques such as reducing transaction size, quick commits, proper indexing, and consistent access order.
This article explains the purpose of locks in concurrent environments, compares optimistic (CAS) and pessimistic locking mechanisms, outlines their advantages, disadvantages, suitable scenarios, and provides PHP/PDO code examples demonstrating implementation and performance testing under high concurrency.
The article explains why traditional MySQL sharding is problematic, introduces the open‑source distributed database TiDB, details its architecture, core features, use cases, performance benchmarks against MySQL, and provides guidance on when to adopt TiDB for large‑scale workloads.
This article explains how to use MySQL's built‑in mysqlslap tool and the third‑party sysbench utility to benchmark MySQL performance, covering configuration changes, command‑line options, data preparation, execution of read/write and I/O tests, and interpretation of the resulting metrics.
This article explains how DBA engineers can speed up MySQL data recovery using Xtrabackup by covering three scenarios—full backup with binlog, single-database restore, and single-table restore—detailing steps such as SQL thread replay, parallel replication, and transportable tablespace techniques with practical command examples.
Storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of VARCHAR saves storage space, reduces index size, enables faster range queries, and leverages MySQL’s built‑in INET_ATON/INET_NTOA functions, while the trade‑offs include reduced readability and the need for conversion code, as demonstrated with benchmarks and Java examples.
This article explains the relationship between MySQL and Redis, the classic cache‑consistency problem, and compares four common cache‑update patterns—delete‑then‑update, update‑then‑invalidate, read/write‑through, and write‑behind—detailing their workflows, advantages, and drawbacks.
This article explains MySQL's InnoDB storage engine, covering where data files are kept, the different row formats (compact, redundant, dynamic, compressed), the internal 16 KB page layout, record header fields, overflow handling, and how B‑Tree indexes (clustered and secondary) are built and accessed.
This article introduces Python's DB-API for database interaction, lists supported databases, demonstrates basic MySQL operations with PyMySQL and SQLAlchemy, explains preventing SQL injection, and shows how to implement connection pooling using DBUtils with examples of threaded usage and utility functions.
The article demonstrates, through a concrete MySQL example, how adding a seemingly small secondary index can unexpectedly inflate a table’s on‑disk size by up to 80%, explains the underlying InnoDB storage mechanics, and offers practical schema‑design recommendations to mitigate such growth.
This article explains the concept of MySQL connection pools in PHP, demonstrates how to create, use, and correctly close both individual connections and pooled connections using mysqli functions and a custom pool class, and highlights the importance of releasing resources to maintain application performance.
This article details how to dramatically speed up the import of massive Excel files (over 100,000 rows) into a Java backend by switching to EasyExcel, caching database lookups, using batch VALUES inserts, and parallelizing the write operations with functional utilities.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, covers the creation and usage of FULLTEXT indexes, demonstrates various MATCH…AGAINST query modes such as natural language, boolean, and query expansion, and shows how to manage and delete full‑text indexes with practical SQL examples.
This article compiles practical MySQL best‑practice rules covering character set selection, InnoDB usage, naming conventions, field design, index strategies, SQL coding standards, and operational safeguards to help developers build robust, high‑performance databases.
This article explains how to assess MySQL table size through table capacity, disk usage, and instance resources, illustrates the impact of B+‑tree index depth on query speed, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—to improve performance.
This article details the design and implementation of a highly available membership system that handles billions of users and peak traffic of over 20,000 TPS, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster architecture, Redis caching with distributed locks, and dual‑center MySQL partitioning.
This article outlines ten essential MySQL table design best practices, covering requirement analysis, data type selection, normalization, indexing, keys, character sets, storage engines, partitioning, backup, and performance monitoring to improve scalability, maintainability, and efficiency.
Oracle announced preview support for JavaScript‑based stored procedures in MySQL, showing how to write, call, and debug them using GraalVM, while industry reactions highlight both excitement and concerns about mixing languages in database logic.
This article compiles essential Linux operations interview questions covering server management for hundreds of machines, RAID 0/1/5 principles, differences among LVS, Nginx and HAProxy, proxy servers, middleware, Tomcat ports, CDN, gray releases, DNS resolution, RabbitMQ, LVS modes, MySQL lock diagnostics, replication delay mitigation, root password reset, backup tools, keepalived health checks, common command‑line utilities, TCP/IP model, Nginx modules, web load‑balancing architectures, and practical shell scripts for monitoring and maintenance.
This guide walks through installing the MySQL exporter, deploying the Prometheus MySQL exporter via Helm on a Kubernetes cluster, creating comprehensive Prometheus alert rules for MySQL health, and testing the alerts by scaling the MySQL deployment, providing a complete monitoring solution.
This guide shows how to use Zabbix to monitor database SQL results, binlog health, master‑slave replication, and how to clean up old Zabbix history with practical Bash scripts for MySQL/GreatDB and ClickHouse.
This article provides a step‑by‑step guide on designing and implementing an audit feature using SpringBoot for the backend, MySQL for data storage, and Vue.js for the front‑end, covering multiple implementation strategies, database schema creation, RESTful APIs, file upload handling, and UI interaction details.
This article explains how MySQL InnoDB handles update, delete, and select statements within read and read‑only transactions, detailing when a read transaction becomes read‑write, the allocation of transaction IDs, rollback segments, and the role of lock types for ordinary and temporary tables.
This article explains why cache inconsistency occurs between MySQL and Redis, then walks through four concrete design patterns—delete‑then‑update, update‑then‑invalidate, read/write‑through, and write‑behind—detailing each step, trade‑offs, and failure scenarios to help engineers choose the most suitable approach.
This tutorial explains how to use PyCharm’s integrated database tool to create, configure, test, and manage a MySQL connection, including driver installation, schema selection, SQL execution, and removal of connections, with detailed visual instructions.
This article analyzes why ALTER TABLE operations that extend VARCHAR columns sometimes fail in MySQL, explains the underlying Online DDL constraints, presents several real‑world problems such as default‑value handling, indexed columns, and hidden table‑rebuild quirks, and offers practical solutions for each case.
This article provides a step‑by‑step guide on designing and implementing an audit workflow in a SpringBoot application, covering database schema creation, multiple backend implementation strategies, RESTful controller code, Vue.js front‑end integration, and file upload handling with deduplication.
This article examines how changing the InnoDB parameters innodb_flush_method and innodb_use_fdatasync—especially using O_DIRECT—affects MySQL's interaction with Linux syscalls, demonstrates verification with strace and /proc, and shows performance implications of these low‑level I/O choices.
This article experimentally compares the efficiency of using IN clauses versus JOIN operations in MySQL by testing small, medium, and large datasets, measuring query costs, execution times, and highlighting the scalability limits of IN statements.
This guide explains MySQL’s most useful date and time functions—including CURDATE(), NOW(), DATE_FORMAT(), DATEDIFF(), DATE_ADD(), DATE_SUB(), and DATE()—with clear syntax examples and sample results, helping developers manipulate temporal data efficiently in their databases.
An in‑depth guide explains MySQL replication fundamentals, compares asynchronous, semi‑synchronous, and fully synchronous modes, and details the binlog‑based architecture with its I/O, log dump, and SQL threads, helping readers understand how replication improves scalability and data safety.
This guide walks you through the most commonly used MySQL date functions—CURDATE, NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, DATE_SUB, and DATE—explaining their purpose, syntax, and example results so you can handle date and time data efficiently in your databases.
This article consolidates theory and real‑world examples to show how MySQL’s B+‑tree structure, page layout, and row format determine the maximum number of records a single table can store, providing step‑by‑step calculations for both int and bigint primary keys.
This tutorial explains how to create a MySQL members table, configure a PHP database connection, and build backend scripts and a web form that allow e‑commerce users to exchange points for membership level upgrades, complete with full code examples.
This guide provides complete shell scripts and step‑by‑step instructions for performing remote backups and restores of MySQL, Oracle, and PostgreSQL databases on Linux, including directory creation, compression, secure copy, cron scheduling, and cleanup of old backup files.
MySQL 8.3.0, released on January 16, 2024, adds enterprise masking refresh, OpenTelemetry telemetry, GTID extended format, automated Windows upgrades, and a new EXPLAIN JSON version while removing several legacy server options and replication variables.
This article explains the complete lifecycle of a MySQL SELECT statement, covering the server and storage‑engine layers, the roles of the connector, query cache, parser, optimizer and executor, and provides concrete command‑line and SQL examples.
This article explains how InnoDB determines transaction types based on the first SQL statement, describes the internal state changes when a transaction starts, and details the identification, ID allocation, and handling of read, read‑only, read‑write, and internal transactions in MySQL 8.0.32.
This article explains how MySQL InnoDB implements full‑text search using inverted indexes, shows how to create and drop FULLTEXT indexes, and demonstrates natural language, boolean, and query‑expansion search modes with concrete SQL examples and operator details.
This article explains why TDSQL for MySQL refuses to drop a primary key when the sql_require_primary_key variable is ON, analyzes the underlying parameter behavior, and provides two practical solutions—temporarily disabling the variable or combining drop and add statements—to safely convert a single‑column primary key into a composite key.
This guide provides practical Linux‑based MySQL administration scripts covering connection, backup and restore, table optimization, log cleanup, performance monitoring, automated cron jobs, security best practices, replication management, and user permission handling.
This article explains why database indexes often become ineffective—covering fuzzy searches, type mismatches, function use, NULL values, arithmetic operations, leftmost column rules, and full‑scan decisions—and provides practical guidelines for designing useful indexes.
Version 8.3 of MySQL introduces tagged GTID replication, a selectable JSON EXPLAIN format version, enhanced thread‑pool plugin information, changes to binlog transaction dependency tracking and new CMake options, while removing several C API functions, the FLUSH HOSTS statement, and outdated replication flags.
This article explores the true maximum length of MySQL VARCHAR columns, showing how charset, NULLability, column count, and row format affect the limit, and explains the underlying storage mechanics, row overflow, and alternatives for data larger than 64 KB.
This article outlines a comprehensive, data‑driven methodology for reducing MySQL storage expenses—including background analysis, challenge identification, a nine‑grid systematic framework, benefit estimation, safety and stability verification, gray‑scale rollout, and rollback strategies—demonstrating over 50% disk space savings in a large‑scale billing system.
This article explains why slow SQL queries occur, how MySQL indexes dramatically improve query speed, and details the underlying data structures—binary tree, hash, B‑Tree, B+Tree—as well as storage engine differences and composite index rules for optimal performance.
This article examines a performance comparison of Spring Boot applications using physical threads, Java virtual threads, and WebFlux for JWT verification with MySQL, detailing test setup, code examples, benchmark results across various concurrency levels, and concluding that virtual threads perform worst due to MySQL driver incompatibility.
This article details Qunar's DBA team's systematic analysis of shortcomings in their original database inspection and alarm systems, the design and implementation of comprehensive metric enhancements, risk‑level classification, automated reporting, and alarm noise reduction, and reports the significant improvements in stability, efficiency, and fault‑free operation achieved through these optimizations.
This article explains how InnoDB in MySQL 8.0.32 distinguishes user and internal transactions, details the allocation of transaction objects from transaction pools, describes initialization steps, and shows how transaction objects are linked into appropriate transaction lists.
This article explains the concept of database read‑write separation, why it improves performance in high‑concurrency scenarios, when to apply it, the underlying master‑slave replication mechanism, and provides a MySQL example illustrating the steps of replication and its benefits for scalability and availability.
This article explains how to leverage MySQL 5.7's built‑in ngram full‑text parser to implement lightweight Chinese full‑text search, covering configuration, index creation, query modes, operator usage, and performance considerations.
This article presents a detailed performance comparison of cloud RDS MySQL services across major providers, examining x86 versus ARM architectures, various storage options, and cost‑performance ratios, and concludes with practical recommendations for choosing the most suitable configuration.
This article analyzes a MySQL 5.7 OOM incident triggered by a heavy full‑text query, examines memory usage patterns with Tcmalloc, demonstrates how switching to Jemalloc releases memory, and provides step‑by‑step commands and observations to prevent similar outages.
Oracle’s MySQL 8.2 preview introduces JavaScript stored procedures powered by GraalVM, allowing developers to write JavaScript or Java code inside the database, but the feature remains a technical preview with performance benefits, debugging support, and cautions about production use and global variable behavior.
This article explains the concept of read‑write separation, why it improves performance, when it should be applied, the underlying principles of master‑slave replication, and provides a detailed MySQL example with diagrams to illustrate the architecture and data synchronization process.
This tutorial walks through importing a CentOS7 virtual machine into VMware, configuring static or dynamic IP settings, installing JDK 1.8, setting up MySQL 5.7 with remote access, and deploying Tomcat 8.5 and a Spring Boot service for automatic startup, including common pitfalls and fixes.
The article explains why counting rows in large MySQL tables—especially with InnoDB—can become slow, compares COUNT() performance across storage engines and query forms, and offers optimization tactics such as using metadata counts, EXPLAIN estimates, a dedicated counting table, or periodic batch processing for accurate or approximate results.
This guide explains MySQL stored procedure flow‑control constructs—including IF, CASE, LOOP, WHILE, REPEAT, LEAVE and ITERATE—detailing their syntax, usage patterns, and practical examples such as salary adjustments and conditional logic, helping developers implement complex database operations efficiently.
This article explains MySQL event scheduler fundamentals, including enabling/disabling the scheduler, creating, altering, and dropping events, scheduling syntax, and practical examples such as generating real-time sales data and periodic statistics, providing code snippets and best‑practice guidance for database automation.
