This article explains why read‑heavy internet services use MySQL master‑slave replication for read/write separation, describes the asynchronous replication process, discusses latency side‑effects, and offers practical strategies such as data redundancy, caching, and direct master queries to mitigate delays.
Deep pagination in MySQL becomes a performance bottleneck when tables reach millions of rows, causing excessive row scans and costly index lookups; this article explains its root causes and presents six practical optimization techniques—including keyset pagination, delayed joins, covering indexes, data partitioning, and external search engines—to dramatically improve query speed.
This guide covers practical MySQL performance improvements, detailing SQL-level optimizations such as limit pagination, proper LIKE usage, UNION ALL, EXISTS vs IN, TRUNCATE, batch inserts, early filtering, function placement, data type minimization, CHAR/VARCHAR choices, indexing strategies, force index, as well as server parameter tuning for buffer pool and redo log settings.
This article explains where MySQL stores auto‑increment values, why gaps appear after failed inserts, duplicate‑key errors, rollbacks or batch inserts, and how different innodb_autoinc_lock_mode settings and MySQL 8.0 improvements can mitigate the issue.
MySQL, born in the 1990s as a free open‑source database, became a core component of the LAMP stack and remains widely used; the article explains why indexes are needed, compares binary trees, B‑trees and B+‑trees, and describes how modern cloud‑based MySQL services improve performance and reliability.
This guide compiles essential MySQL interview topics—including definitions, differences from Oracle and SQL Server, index types and usage, B‑tree vs B+‑tree structures, deadlock handling, and view concepts—providing a systematic overview for developers preparing for database interviews.
This tutorial explains how to connect PHP to databases using both mysqli and PDO, covering connection setup, error handling, executing queries, and properly closing the connection, with complete code examples for each method.
This article reviews the 14-part series on MySQL's transaction module, covering initialization, transaction types, two‑phase commit, binlog handling, savepoints, and full rollback, and provides links to each detailed post, including discussions on BEGIN statements, read‑only transactions, and the internal mechanisms of InnoDB.
A detailed post recounts a production outage caused by improper index handling in a massive MySQL table, walks through MySQL lock diagnostics, compares index behavior on MySQL and TiDB, and distills practical recommendations for safe DML, DDL, and index management.
This article explains the WKB binary format, its storage advantages in MySQL and PostgreSQL, and provides step‑by‑step Java code using JTS's GeometryFactory, WKBWriter, and WKBReader to write a Point to WKB, convert it to a hex string, and read it back into a geometry object.
This article details the migration of a popular social app's private‑message service from a saturated MySQL cluster to PolarDB, covering business challenges, evaluation of storage‑optimization, vertical and horizontal scaling, the chosen distributed database solution, step‑by‑step offline and online migration procedures, and the resulting performance and cost benefits.
The article investigates why MySQL batch jobs experience aborted connections, tracing the issue to TCP buffer overflow on the client side, excessive ACK traffic, and the default net_write_timeout of 60 seconds, and proposes kernel tweaks and query optimizations to resolve the problem.
This guide walks through three MySQL administration scenarios—resetting the root password using the --skip-grant-tables option, adding user accounts with proper grants, and changing passwords via UPDATE, SET PASSWORD, ALTER USER, GRANT, and mysqladmin—providing step‑by‑step commands and precautions.
This article walks through replacing MyBatis with MyBatis‑Plus in a legacy Java project, demonstrates the LocalDateTime conversion error caused by MySQL‑connector‑java version incompatibility, shows how upgrading the driver resolves the issue, and shares a cautionary tale about unintended bugs after component upgrades.
This article demonstrates how the MySQL LIKE clause can give misleading results when filtering triggers, walks through a step‑by‑step lab that creates tables and triggers, shows the pitfalls of LIKE, and presents a safer WHERE‑based method for retrieving trigger definitions.
The article explains why transaction isolation is essential in databases, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads, and phantom reads, and outlines MySQL's four isolation levels with their effects and trade‑offs.
A Java developer recounts a production MySQL outage caused by a faulty batch insert, walks through failed delete and lock‑free attempts, then details a series of table‑recreation, truncate, and index‑rebuilding steps that finally restored data integrity and performance.
This article explains how to boost MySQL insertion speed by adjusting server hardware, tuning database parameters, choosing the MyISAM engine, and especially applying three batch‑insert techniques—SQL concatenation, MyBatis‑Flex saveBatch, and manual transaction batching—while showing test results and configuration tips.
This article explains how an invisible zero‑width Unicode character embedded in a MySQL table name can make the table appear to disappear, demonstrates the problem with reproducible examples, analyzes the root cause, and provides practical steps to detect and fix such issues.
This article recounts how a volunteer registration platform was redesigned to meet extreme concurrency demands—handling up to 40k QPS and 20k TPS—by evaluating MySQL limits, adding Redis caching, integrating RocketMQ, applying fault‑recovery mechanisms, and iteratively optimizing through multiple load‑testing rounds.
This guide explains why MySQL or Java processes can consume 900% CPU, outlines step‑by‑step diagnostics using tools like top, show processlist, jstack, and provides concrete fixes such as adding indexes, disabling slow logs, using caches, and optimizing thread handling.
Facing a 50‑million‑row financial transaction table that grew 6 million rows per month, the team analyzed the problem, set clear split goals, evaluated sharding middleware, designed a custom pagination algorithm, built a hybrid data‑migration plan, and executed a three‑stage rollout to safely replace the monolithic table with multiple 10‑million‑row shards.
This article explains how to use PHP's mysqli_num_rows function to retrieve the number of rows in a MySQL result set, providing a complete example that connects to the database, executes a SELECT query, checks for errors, obtains the row count, and displays it.
The article examines why MySQL LIMIT pagination becomes slower on deeper pages of a 500,000‑row table, analyzes query execution plans, and presents three optimization strategies—ordered index filtering, subquery pagination, and join‑based pagination—to improve performance in real‑world scenarios.
This article investigates a slow‑SQL incident caused by the TDDL optimizer reordering a MySQL query, analyzes why the optimizer chose an inefficient index, demonstrates how to verify and force the correct index, and proposes better index and partition‑key designs for optimal performance.
This article explains how MySQL 8.0.32 rolls back a transaction by first attempting a binlog rollback (which does nothing for a plain ROLLBACK), then performing InnoDB undo‑log processing, committing the internal InnoDB changes, and finally clearing temporary binlog data from the transaction cache.
Learn how to assess and optimize your MySQL database using four powerful open‑source tools—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—complete with download links, command‑line usage examples, and guidance on interpreting their diagnostic reports to improve performance and security.
The article analyzes a MySQL cascade replication issue where the new slave failed to sync due to duplicate server_id values, explains the root cause stemming from automated server_id assignment, and provides step‑by‑step remediation to ensure unique server_id settings and reliable replication.
This article explains the default repeatable‑read isolation level in InnoDB, details the three main lock types—record, gap, and next‑key—and shows how SELECT … FOR UPDATE applies different locking rules depending on primary key, unique, ordinary, range, or table‑scan queries, with practical SQL examples.
This article explains what MySQL binlog is, how it enables master‑slave replication, and explores practical business applications such as data heterogeneity, cache synchronization, and task distribution, while highlighting performance considerations and the benefits of using binlog‑based middleware.
This article explains how MySQL executes ORDER BY queries, the role of sort_buffer, the difference between full-field and rowid sorting, how parameters like sort_buffer_size and max_length_for_sort_data affect performance, and how proper indexing can eliminate the need for sorting altogether.
This article presents eight practical MySQL optimization methods—including smarter LIMIT usage, avoiding implicit type conversion, rewriting joins, handling mixed ordering, replacing EXISTS with joins, pushing down conditions, pre‑filtering data, and using WITH clauses—to dramatically reduce query execution time.
This guide walks through locating the Docker volume of a removed MySQL container, creating a new container with the same image, and swapping the volumes to restore the original database, providing step‑by‑step commands and screenshots for successful data recovery.
This article provides an in‑depth overview of MySQL string functions, covering concatenation, length, position, replacement, conversion, encryption, comparison, generation, and their syntax and usage examples, helping developers manipulate textual data efficiently in database operations.
This article compares PostgreSQL and MySQL across history, ACID compliance, performance, scalability, advanced features, tooling, security, backup strategies, and Linux installation steps, helping full‑stack developers choose the most suitable open‑source relational database for their projects.
This article analyzes why MySQL recommends auto‑increment primary keys over UUIDs or random snowflake IDs by designing three tables, running insert‑select benchmarks with Spring Boot's JdbcTemplate, presenting the test results, and discussing the underlying index‑structure impacts and trade‑offs.
This article explains MySQL InnoDB full-text search, covering inverted index structures, creation and usage of full-text indexes, query modes such as natural language, boolean, and query expansion, and provides practical SQL examples and considerations for effective text searching.
This article explains MySQL foreign keys, how to define them with CREATE TABLE syntax, cascade actions, limitations, pros and cons, and provides a complete MySQL 5.7 example with metadata queries and a cascade‑delete test, helping readers understand when to use or avoid foreign keys.
This article presents a detailed investigation of a MySQL master‑slave instance suffering from nightly SLA alerts due to slow‑query latency, analyzes the root causes with pt‑query‑digest and execution plans, proposes index redesign, demonstrates backup and restore with mydumper, compares online DDL and pt‑osc, and finally shows batch‑delete techniques to reduce load on large tables.
This article examines whether SELECT COUNT(*) causes full‑table scans, explains MySQL’s optimizer cost‑based index selection (including IO and CPU costs), demonstrates with a 100k‑row table how auxiliary indexes are chosen, and shows cases where the optimizer’s estimates mislead performance.
This article explains how MySQL 8.0.32 InnoDB rolls back to a specific savepoint by locating the savepoint in the thread's m_savepoints list, discarding relevant binlog entries, applying undo logs in reverse order, and finally removing any later savepoints, with full code examples.
This article shares a detailed ByteDance backend interview experience covering three rounds, summarizing essential topics such as Java HashMap internals, Spring circular dependencies, MySQL indexing, OS process/thread concepts, networking protocols, TLS handshake, HTTP/2 features, and common design patterns, providing concise explanations and practical tips.
This guide walks through setting up Flink CDC with MySQL on SpringBoot 2.7, covering binlog configuration, Maven dependencies, Java implementation for real‑time change capture, startup options, a custom Redis sink, and a web UI for monitoring the streaming pipeline.
This article explains the new mysqld-long-query-time option added to MySQL 8.0.30's mysqldump tool, shows how to configure it, presents comparative experiments demonstrating its effect on slow‑query logs, and provides conclusions and additional notes for practical database backup scenarios.
This article presents nine practical MySQL index optimization techniques—from selecting high‑selectivity columns and avoiding functions on indexed fields to applying the leftmost‑prefix rule, handling NULLs, using proper LIKE patterns, and leveraging EXPLAIN and LIMIT 1 for faster queries.
When a user reported errors, I discovered a MySQL table flooded with millions of bad rows from a new group, causing slow queries and crashes; I detail the step‑by‑step investigation, failed attempts with DELETE and lock‑free changes, and the final successful rebuild using temporary tables, DROP, TRUNCATE, and index reconstruction.
This article introduces MySQL and PostgreSQL, compares their architectures, presents benchmark results showing PostgreSQL's superior SELECT, INSERT, and UPDATE performance, discusses suitable application scenarios, and summarizes the strengths and weaknesses of each database for informed selection.
This article explains common MySQL performance pitfalls such as large‑offset pagination, implicit type conversion, inefficient UPDATE/DELETE joins, mixed ordering, EXISTS subqueries, predicate push‑down, early data reduction, and intermediate result set push‑down, and provides concrete rewrites that turn multi‑second queries into millisecond‑level executions.
This article explains what InnoDB does, how its page‑based I/O causes the first query to be slower, the default Dynamic row format, how varchar length is stored, the practical limits of varchar(M) under utf8mb4, and the impact of metadata and internal fragmentation on row space.
This article explores eight common MySQL performance pitfalls—from inefficient LIMIT usage and implicit type conversion to subquery joins, mixed ordering, EXISTS clauses, condition pushdown, early result limiting, and intermediate result set optimization—offering concrete rewrites and execution plan analyses to dramatically speed up queries.
This guide outlines a clear, maintainable CMS architecture for technical blogs, covering logical, development, physical, process, and scenario views, recommending MVC with Go, MySQL, and React/Vue, and provides a simple Go API example.
The article examines why MySQL performance drops sharply when a single table exceeds tens of millions of rows, debunks the index‑depth myth, explains SMO concurrency limits in InnoDB, and shows how B‑Link Tree indexes and heap‑organized tables can overcome these bottlenecks.
This article presents a series of MySQL performance‑enhancing techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting UPDATE/DELETE with JOINs, mixed‑order sorting tricks, replacing EXISTS with JOINs, condition push‑down, early result limiting, and intermediate result push‑down—to dramatically reduce query execution time.
In MySQL 5.7, using REPLACE INTO on a table that has an auto‑increment primary key and an extra unique index can cause the master and slave AUTO_INCREMENT counters to diverge, leading to duplicate‑key errors after failover, a bug mitigated by upgrading to MySQL 8.0, manual counter fixes, or avoiding REPLACE INTO.
This article explains how InnoDB implements savepoints in MySQL 8.0.32, covering undo log sequencing, the savepoint structures in the server, binlog, and InnoDB layers, the process of locating and deleting duplicate savepoints, and the three-step procedure for creating a new savepoint.
The article demonstrates through a practical experiment that MySQL will use indexes for an OR condition only when both operands are indexed, otherwise it falls back to a full table scan, and it suggests using UNION as an optimization when one side lacks an index.
This article investigates a MySQL 8.0.27 replica that repeatedly hangs during show replica status, analyzes worker thread states, InnoDB status, checkpoint and I/O load, identifies an oversized Redo Log usage as the root cause, and provides configuration and upgrade suggestions to resolve the issue.
This article presents 18 practical guidelines for backend developers on naming conventions, field types, lengths, primary keys, indexes, storage engines, null constraints, foreign keys, character sets, collations, large fields, redundant fields, and comments to help create efficient, maintainable MySQL tables.
The article explains MySQL 8.0's INSTANT DDL algorithm, its default behavior, limitations such as the 64‑change cap, and provides practical recommendations for always specifying the algorithm, monitoring instant changes via INFORMATION_SCHEMA, and safely rebuilding tables when needed.
This article walks through the complete process of upgrading MySQL 5.7.43 to 8.0.34 on CentOS 7, covering lifecycle background, new 8.0 features, pre‑upgrade checks, data backup, configuration changes, installation, upgrade execution, post‑upgrade verification, and troubleshooting of typical SSL and host‑blocking issues.
This article demonstrates how to insert 300,000 rows into a MySQL table using MyBatis and JDBC, compares bulk batch insertion with per‑row looping, discusses packet size limits, transaction handling, performance metrics, and provides practical code snippets and optimization tips for efficient large‑scale data loading.
This article explains how MySQL auto‑increment primary keys work, the practical limits of various internal IDs such as auto‑increment, InnoDB row_id, Xid, trx_id, and thread_id, and compares them with external solutions like Redis‑based auto‑increment keys, providing guidance on choosing the right approach for different scenarios.
This guide explains how to install Docker, pull the official MySQL image, run a MySQL container with persistent storage, connect to it, perform basic SQL operations, and finally stop and remove the container, demonstrating a complete end‑to‑end deployment using Docker.
This article explains the fundamentals of database sharding and partitioning, why they are needed for massive data volumes, various vertical and horizontal splitting strategies, routing algorithms, client vs. proxy deployment models, and the practical challenges and solutions when using ShardingSphere 5.x.
This guide explains how to efficiently load one billion 1‑KB log entries from HDFS or S3 into MySQL by analyzing B‑tree limits, using batch inserts, choosing the right storage engine, sharding tables, optimizing file reading, and coordinating tasks with Redis, Redisson, and Zookeeper.
This article examines the detailed steps and performance impact of creating a MySQL connection from a Java web application, measures the latency of the handshake and authentication using Wireshark, and demonstrates why connection pooling is essential for high‑traffic services.
This article walks through replacing MyBatis with MyBatis‑Plus in a legacy Java project, uncovers the root cause of a LocalDateTime conversion error caused by MyBatis 3.5.1 and an outdated MySQL connector, demonstrates version upgrades and code fixes, and shares a related production bug and its remediation.
This article investigates the time overhead of establishing MySQL database connections in Java web applications, detailing the TCP handshake, authentication steps, and measured latency using Wireshark, and demonstrates why connection pooling is essential to avoid hundreds of milliseconds per request.
This article introduces MySQL and PostgreSQL, compares their architectures and benchmark results on identical hardware, highlights PostgreSQL's superior read/write performance, and discusses the strengths, weaknesses, and ideal use‑case scenarios for each database system.
This article details the motivations, technology choices, step‑by‑step deployment of Typecho on a Tencent lightweight cloud server, domain registration, site theming, custom image‑processing plugins, HTTPS setup, content migration, and a concise checklist for building a dynamic personal technical website.
This article explains the internal steps InnoDB performs when committing a transaction, covering undo segment caching, generation of the transaction commit number, state changes of insert and update undo segments, history‑list insertion, and the final reinitialisation of the transaction object.
This article explains the MySQL 8.0.22 READ ONLY option for individual databases, demonstrates how to enable and verify the read‑only state with ALTER DATABASE and SHOW CREATE DATABASE commands, shows the impact on DDL/DML operations, lists usage restrictions, and notes exceptional cases and backup considerations.
This tutorial explains how to install the MySQL Exporter, configure Prometheus to scrape MySQL metrics, set up Grafana dashboards for visualization, and define alerting rules for common MySQL performance indicators, providing a complete end‑to‑end monitoring solution.
This article introduces MySQL and PostgreSQL, compares their architectures and benchmark results, highlights performance differences in SELECT, INSERT, and UPDATE operations, and discusses suitable scenarios and trade‑offs to help developers choose the appropriate database for their applications.
This article explains common causes of extreme CPU usage in MySQL and Java applications, provides step‑by‑step troubleshooting methods using Linux tools and SQL commands, and presents real‑world case studies with concrete optimizations such as indexing, caching, and thread‑handling adjustments.
This article recounts the end‑to‑end design, bottleneck analysis, and iterative performance tuning—including MySQL, Redis, RocketMQ, compression, sharding, and connection‑pool adjustments—that enabled a volunteer registration platform to meet demanding high‑concurrency and data‑accuracy requirements under limited resources.
This article walks through a real‑world Python group question about efficiently inserting a processed pandas DataFrame into a MySQL table, explains why the original pymysql approach fails, and demonstrates a working solution with SQLAlchemy and create_engine.
This guide introduces key MySQL performance‑tuning utilities—including MySQLTuner, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run, and interpret their reports to identify configuration issues, memory overload, and slow‑query problems for a healthier database.
This article provides a detailed tutorial on the PyMySQL library, covering installation, connection setup, cursor operations, executing queries, transaction handling, advanced features like batch processing and connection pooling, as well as best practices for error handling and performance optimization in Python‑MySQL applications.
This guide explains how to identify and fix extreme CPU usage—up to 900%—in MySQL and Java processes, covering diagnostic commands, SQL indexing, caching strategies, thread analysis, and code adjustments to stabilize production systems.
This article explains MySQL 8.0.32's binlog log file structure, the 8 KB in‑memory buffer (IO_CACHE), how binlog entries are written from trx_cache to the binlog file, and the detailed steps and code involved in reading from memory or temporary files and flushing to disk.
The article explains how granting ordinary MySQL users full access to authentication tables can lead to severe privilege‑escalation risks, demonstrates the issue with concrete scenarios, and provides mitigation strategies including the use of MySQL 8.0 partial revokes and the principle of least privilege.
This article enumerates the most frequent situations that cause MySQL indexes to become ineffective—such as violating the left‑most rule, using functions or expressions on indexed columns, type conversions, improper LIKE patterns, OR/IN/NOT IN usage, and inequality operators—and demonstrates how to reproduce and verify each case with sample data and SQL scripts.
Understanding MySQL’s auto‑increment mechanism reveals why IDs can become non‑sequential due to storage differences, transaction rollbacks, unique key conflicts, and batch inserts, and shows how lock‑mode settings and MySQL 8.0 improvements can optimize performance and maintain consistency.
The article introduces MySQL and PostgreSQL, compares their performance through a benchmark on identical hardware, discusses each system’s strengths, weaknesses, and suitable application scenarios, and provides guidance on choosing the appropriate database for different workloads.
This comprehensive guide explains why MySQL needs InnoDB locking, details the seven lock types, shows how a single SQL statement acquires locks under different isolation levels, demonstrates how to view lock information, and walks through practical deadlock analysis examples.
When a MySQL master‑slave switch makes the old master read-only, Go connection pools keep reusing stale connections and cause prolonged write errors, but by extending the driver to return driver.ErrBadConn on error 1290 or using the DSN flag rejectReadOnly=true, the bad connections are discarded and the error disappears almost instantly.
This comprehensive guide explains MySQL's architecture, the importance of SQL optimization, detailed index types and creation methods, how to interpret EXPLAIN output, and practical techniques to improve query performance while avoiding common pitfalls.
This article explains why slow SQL becomes a critical risk during large‑scale promotions, outlines a three‑step method—observing the query, analyzing it with EXPLAIN, and designing fixes—then demonstrates each step with concrete MySQL examples, tips, and overall optimization principles.
This article explains how MySQL 8.0.32 processes the flush, sync, and commit subphases during a two‑phase commit, detailing the roles of leader and follower threads, log handling, system variables, and the conditions that trigger binlog rotation and persistence.
This article analyzes a MySQL replica that repeatedly ran out of memory due to large triggers and a high table_open_cache_instances setting, demonstrates how to reproduce the issue with test scripts, and provides a practical fix by reducing the parameter to mitigate OOM.
The article examines MySQL's limited join types and performance drawbacks for multi‑table queries, compares them with PostgreSQL, and explains why many applications split joins into separate service‑layer operations to reduce RPC overhead, improve caching, and handle sharding constraints.
This weekly newsletter from the Aikexing open‑source community shares the latest SQLE product releases, summaries of recent MySQL and OceanBase technical articles, a curated selection of database‑related reads, and upcoming community events and activities for developers and enthusiasts.
Google has introduced preview vector search to its Cloud SQL for MySQL service, positioning it ahead of Oracle, while industry analysts note the growing importance of vector capabilities for generative AI applications across major database platforms.
This tutorial demonstrates how to build a taxi trajectory visualization by creating a MySQL table for track data, writing a PHP script to retrieve the data as JSON, and using Baidu Maps JavaScript API in an HTML page to render the moving paths with interactive features.
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.
