This article explores how MySQL determines the maximum length of VARCHAR columns, showing that the limit depends on the chosen character set, nullability, and column count, and explains the underlying row storage format and how to handle oversized strings.
This weekly roundup curates the latest high‑quality technical shares on databases, featuring articles about PolarDB HTAP acceleration, MySQL datetime precision, new MySQL releases, ChatGPT as a database client, and the SQLE platform's recent enhancements and upcoming plans.
This article explains MySQL replication, covering its background, binlog fundamentals, replication formats, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world implementation details for high availability and data synchronization.
This guide explains what pt‑kill is, how it works within Percona Toolkit to kill MySQL connections based on execution time and various filters, demonstrates command‑line usage, victim‑selection modes, filtering options, source‑code logic, and common patterns such as logging and daemonizing.
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, providing a complete end‑to‑end solution for monitoring MySQL databases in production environments.
This article describes a step‑by‑step procedure for safely dropping a massive MySQL history_str table—including creating a new table, renaming, using hard links, staged drops, and a truncate script—to avoid instance hangs, replication lag, and I/O pressure while freeing disk space.
This article explains how InnoDB’s REPEATABLE READ isolation level uses MVCC and gap locks to prevent most phantom reads, illustrates the mechanisms with SQL examples and diagrams, and outlines the remaining edge cases and best practices for fully avoiding phantom reads.
This article explores how MySQL can serve as a JanusGraph storage backend, detailing the design choices, multi‑tenant architecture, key‑column‑value model implementation, encountered issues such as connection timeouts and deadlocks, and performance testing results that demonstrate its suitability for medium‑scale Data Catalog services.
This article explains how MySQL 8.0.32 expands the '*' in a SELECT statement into all table columns, detailing the lexical parsing, query preparation, and the functions Item_asterisk::itemize, Query_block::prepare, Query_block::setup_wild, and insert_fields through source‑code excerpts.
This article introduces ProxySQL as a high‑performance MySQL proxy, explains its request flow, multi‑layer configuration system, key tables, and demonstrates practical scenarios such as read‑write splitting, hostgroup weight tuning, replication‑lag handling, blacklist/whitelist rules, and firewall protection.
This article explains how MySQL estimates the I/O and CPU costs of different indexes when executing COUNT(*) and other queries, demonstrates the calculations with a concrete example on a 100k‑row table, and shows why the optimizer sometimes picks a full‑table scan over a seemingly better index.
This article investigates the true maximum length of MySQL VARCHAR columns, showing how charset byte size, NULLability, column count, and the InnoDB Dynamic row format together determine the effective limit, and explains how overflow is handled for very large strings.
This article explains MySQL's index‑merge optimization, detailing how the EXPLAIN output indicates its use, the three underlying algorithms (intersect, union, sort‑union), practical query examples, configuration flags, and step‑by‑step guidance for testing with sample tables and indexes.
This article details a step‑by‑step strategy for expanding a VARCHAR column from 50 to 500 characters on a 4‑hundred‑million‑row MySQL 8.0 table, evaluating online DDL, gh‑ost, pt‑osc, and ultimately implementing a cascade replication workflow to meet a four‑hour downtime window.
This article shows how to generate tens of millions of rows in MySQL, measure insertion and pagination performance, and apply practical optimizations such as sub‑queries for large offsets and column selection to dramatically speed up queries on huge data sets.
This article explains the different types of auto‑increment identifiers used by MySQL—including table primary keys, InnoDB row_id, Xid, trx_id, and thread_id—covers their limits and behaviors when they overflow, and introduces Redis as an external auto‑increment key solution.
This article explains MySQL index fundamentals, compares B+Tree and B-Tree structures, shows how page size and tree height affect capacity, outlines which query patterns benefit from indexes, and discusses practical limits and the adaptive hash index feature.
The DTLE 4.23.04.0 release introduces numerous MySQL‑focused enhancements and bug fixes—including timestamp handling, UUID processing, transaction retry logic, UTF‑8 support, and API improvements—while providing download links, repository information, and recommended reading for users of this open‑source data transfer component.
The article introduces the SQLE 2.2304.0-pre2 preview release, outlines the tool’s purpose for database users and administrators, details new enterprise‑level features such as affected‑row statistics and enhanced rule severity display, lists added MySQL audit rules, and provides complete release information and resource links.
This article reviews five free or open‑source MySQL client applications—MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, and DBeaver—detailing their main features, supported platforms, and how they help developers and DBAs design, query, and manage MySQL databases.
This article details the design and implementation of a high‑availability, billion‑scale membership system, covering Elasticsearch dual‑center clusters, traffic‑isolated architectures, deep ES optimizations, Redis caching strategies, MySQL migration with dual‑center partitioning, abnormal member relationship handling, and future fine‑grained flow‑control and degradation plans.
This article presents practical MySQL query optimization techniques—including pagination, index‑based join improvements, subquery replacement with joins, order‑by and group‑by indexing, and count() efficiency—illustrated with real‑world examples, execution‑plan analysis, and ready‑to‑run SQL code.
This article explains a MySQL master‑slave synchronization failure caused by a corrupted relay log, walks through the replication principles, analyzes slave status and GTID differences, and provides step‑by‑step commands to skip the problematic GTID and restore replication.
This article explains how to activate MySQL 8.0.17+ REDO log archiving, configure the required directory and permissions, resolve common errors when using backup tools like mysqlbackup, and demonstrates manual archiving via the MySQL client with proper user privileges.
This article explains how MySQL 8.0.32 (InnoDB) handles a situation where the client sends a DML/DDL statement, the server executes it, and the client silently drops the connection before the server can detect the disconnect, detailing transaction commit, rollback, and send() behavior.
The article analyzes an unusual MySQL replication lag pattern where Seconds_Behind_Master jumps abruptly between 0 and a fixed maximum due to slave clock adjustments, explains the underlying cause, and provides practical steps for time synchronization and IO‑thread handling to resolve the issue.
This article explains MySQL replication’s background, binlog formats, event types, positioning methods, asynchronous and semi‑synchronous workflows, parallel replication techniques, and real‑world deployment strategies such as HA components, middleware, remote binlog copying, and data‑transfer services, providing a comprehensive guide for building highly available and scalable MySQL infrastructures.
This article explains MySQL's query execution flow and provides extensive best‑practice guidelines—including naming conventions, storage engine choices, primary‑key design, character set settings, query writing tips, indexing strategies, batch operations, and performance‑enhancing techniques—to help developers write efficient and maintainable SQL code.
This article describes a PHP‑based web crawler that extracts links and images using regular expressions, stores URLs in MySQL, handles duplicate detection via MD5, discusses performance limitations, and provides the full source code and usage instructions.
This article provides a detailed tutorial on setting up MyBatis Generator in a Spring Boot application, covering configuration file placement, XML adjustments, Maven dependencies, execution steps, and the resulting model, mapper, and XML files with full code examples.
This comprehensive guide walks you through practical MySQL optimization strategies—including slow‑query analysis, deep‑pagination fixes, explain plan interpretation, index tuning, large‑table design, and common pitfalls—so you can dramatically improve query speed and overall database performance.
The SQLE 2.2304.0-pre1 release introduces a preview version of the SQL audit and quality control tool with detailed project information, enhanced suggestion features for audit results, and a concise list of new functionalities and release details for database users and administrators.
This article provides a comprehensive guide to MySQL performance optimization, covering CPU and I/O bottlenecks, index types and tuning, slow query capture and analysis, and best practices for table design and partitioning to improve query efficiency.
This article presents a senior architect’s comprehensive MySQL optimization guide, covering execution process, naming conventions, storage engine choices, indexing strategies, query writing best practices, transaction handling, partitioning, and other performance‑tuning techniques to improve database efficiency and reliability.
This article details a joint research effort between Meituan and East China Normal University that combines cost‑based methods with AI‑driven, data‑centric models to automatically generate and evaluate missing indexes for billions of daily slow queries, improving recommendation accuracy and query performance.
MySQL replication copies data changes from a primary server to one or more replicas using binlog events—supporting statement, row, or mixed formats and GTID positioning—to provide real‑time backup, read‑write separation, high‑availability failover, and integration pipelines via asynchronous, semi‑synchronous, or centralized binlog services.
This article compares two methods for synchronizing MySQL data with Redis cache—using MySQL triggers combined with a UDF function, and parsing MySQL binlog via tools like Canal—detailing their workflows, advantages, limitations, and implementation considerations.
This article explains MySQL master‑slave replication fundamentals, the binlog‑based data flow, common consistency and latency problems, practical solutions such as row or mixed binlog formats, caching and query routing, and the trade‑offs of one‑master‑one‑slave versus one‑master‑many‑slaves architectures.
To reduce database load from high‑frequency like and favorite actions, the article outlines a backend solution that stores per‑article like data in Redis hashes, periodically synchronizes the cached data to MySQL using Quartz scheduled jobs, and shows how to indicate whether a user has liked each article.
This article walks through nine common MySQL performance pitfalls—from inefficient LIMIT clauses and implicit type conversions to sub‑query updates, mixed sorting, EXISTS usage, condition pushdown, early range reduction, and intermediate result pushdown—showing how to rewrite each pattern for dramatically faster execution.
This article explains MySQL's date and time manipulation functions—including addtime, adddate, date_add, subtime, subdate, and date_sub—showing their syntax, usage with numeric and INTERVAL arguments, and providing example queries for adding or subtracting seconds, minutes, days, weeks, months, and years.
The article investigates why a MySQL‑slave machine shows low free memory despite modest MySQL and Redis usage, revealing that massive hidden files under /run/systemd/users and tmpfs‑based shared memory consume several gigabytes, and proposes practical remediation steps.
This article demonstrates how to generate millions of test rows in MySQL, analyzes the performance impact of deep pagination using LIMIT, explains why non‑clustered index lookups cause costly table scans, and presents two optimization approaches—sub‑query ID filtering and key‑set pagination—to dramatically reduce query latency.
This article explores MySQL single‑table row limits, demonstrates how to generate massive test data, explains InnoDB page and index structures, calculates theoretical maximum rows based on B+‑tree parameters, and provides practical tuning tips for handling large tables.
This article explains MySQL master‑slave replication, covering its architecture, binlog‑based replication process, causes and mitigation of replication lag, and strategies for master‑slave failover, helping readers grasp why and how to use replication for read/write separation, high availability, and backup.
The article explains how to efficiently retrieve and process massive MySQL result sets in Java using MyBatis, comparing regular pagination, streaming queries via Cursor, and cursor-based fetchSize techniques, and provides practical code examples and best‑practice tips to avoid OOM and improve performance.
This article explains the challenges of slow DDL operations in MySQL, introduces TXSQL’s Parallel DDL framework—including Copy, Inplace, and Instant algorithms—details their design, implementation, code examples, and performance evaluations, and compares them with other database solutions.
This article compiles a technical interview recap covering MySQL MVCC, atomicity and persistence mechanisms, operating‑system page cache and deadlock concepts, TCP reliability and flow‑control, Redis persistence and clustering, as well as distributed transaction and consensus fundamentals.
This article shares a concise 10‑line Bash script, generated by ChatGPT, that captures MySQL QPS, TPS, and connection counts, timestamps each record, and appends the metrics to a log file for easy performance monitoring.
This article demonstrates MySQL query performance issues with large tables, shows how a subquery without indexes can take hours, and walks through step‑by‑step optimizations including adding single‑column indexes, converting to EXISTS and JOIN queries, creating composite indexes, covering indexes, and sorting improvements, achieving speedups of thousands of times.
This article explains MySQL index covering and how avoiding table lookups (back‑to‑table) can reduce retrieval steps, improve query performance, and provides practical examples, execution‑plan analysis, and optimization tips for large tables in production.
This article explains why MySQL uses MVCC, how undo‑log version chains and read views implement multi‑version concurrency control, and details the various lock types—including global, table, metadata, intention, row, gap, and next‑key locks—under different isolation levels with practical code examples.
This article demonstrates how to generate two million MySQL rows, examines the performance impact of large OFFSET values in LIMIT clauses, explains why non‑clustered indexes cause back‑table lookups, and presents two optimization strategies—using sub‑queries to fetch IDs and employing key‑based pagination—to dramatically speed up deep page queries.
The article investigates why MySQL 8.0.27 repeatedly logs a deprecation warning for the sha256_password plugin, tracing the issue to authentication attempts with non‑existent users, analyzing source code, and proposing configuration and code‑level fixes.
The article explains the fundamentals of tree data structures, compares B, B+, B*, and red‑black trees, and shows how B+ trees reduce disk I/O and improve range query performance, which is why MySQL adopts B+ trees for its indexes.
A detailed post‑mortem of a MySQL dual‑master cluster failure caused by a missing mysql-bin.index file, showing step‑by‑step diagnostics, container logs, replication position fixes, and preventive measures for high‑availability deployments.
This article introduces Alibaba's open‑source Canal middleware, explains its architecture and high‑availability design, walks through MySQL binlog configuration, Canal setup, and provides a complete Java client example for real‑time data synchronization, cache refresh, and task dispatch scenarios.
Echo is a full‑stack open‑source Java community system built with Spring Boot, MyBatis, MySQL, Redis, Kafka and Elasticsearch, offering modules like posts, comments and notifications, and the article provides its core tech stack, development environment, local setup steps, deployment architecture, demo screenshots and source code access.
This article explains the MySQL Buffer Pool architecture, describing data pages, cache pages, the three page states, the various linked lists (Free, Flush, LRU), pre‑read mechanisms, hot‑cold separation, concurrency handling, and dynamic resizing using the chunk mechanism.
This article details a comprehensive performance testing workflow—including requirement analysis, script recording with BlazeMeter, data construction, iterative optimization, and final results—targeting a web application that must handle over 5,000 TPS with 2 million database records, highlighting bottlenecks in Redis, MySQL, and code logic.
This article explains how MySQL InnoDB achieves the ACID properties—Atomicity, Consistency, Isolation, Durability—by detailing the roles of undo logs, redo logs, buffer pool, lock mechanisms, and MVCC, and outlines transaction lifecycle commands, isolation levels, and consistency guarantees.
The article examines how to maintain data consistency between caches and databases during dual-write operations, categorizes data into three levels, evaluates four update strategies, and proposes solutions such as delayed double deletion, message‑queue compensation, and binlog‑driven cache synchronization.
The article details how a rapidly growing airline ticket order system was re‑architected by identifying performance bottlenecks, applying vertical and horizontal sharding, optimizing cache layers, implementing dual‑write mechanisms, and planning a phased migration to achieve ten‑fold QPS growth while reducing resource usage and operational risk.
This article reveals frequent SQL mistakes—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, premature filtering, and intermediate result push‑down—and provides concrete rewrite techniques that dramatically improve MySQL query performance.
The article explains what OceanBase BUFFER (Queuing) tables are, why frequent bulk updates cause performance degradation, and provides three optimization methods—including binding execution plans, manual data transfer, and setting table_mode='queuing'—with concrete MySQL and Oracle examples and query scripts to monitor merges.
This article provides a comprehensive deep‑dive into MySQL indexing, covering the definition, pros and cons, differences between clustered and non‑clustered indexes, InnoDB vs MyISAM implementations, B+Tree structure, capacity calculations, adaptive hash indexes, index pushdown optimization, and how to interpret EXPLAIN output to verify index usage.
This article explains how to design and implement a real‑time, multi‑tenant site search system by migrating data from MySQL to Elasticsearch using Kafka and MySQL binlog, covering architecture decisions, data schema, Docker deployment, and core SpringBoot/Kotlin code for event collection and indexing.
This article explains the MySQL sql_mode setting, details each of its ten modes—including STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and ONLY_FULL_GROUP_BY—provides a command to view the current mode, and also includes a brief promotion for a PHP training class.
This article provides a comprehensive overview of MySQL 8.0 enhancements, covering performance improvements such as contention‑aware scheduling and hash joins, security upgrades including authentication plugins and password policies, optimizer refinements, and various other features like persistent variables, GIS support, and binlog expiration handling.
This article explains two methods for keeping MySQL data in sync with a Redis cache—using MySQL triggers with a UDF function and parsing MySQL binlog streams—detailing their processes, advantages, limitations, and related open‑source tools like Canal.
This article compiles a detailed interview guide covering TCP/UDP differences, three‑way handshake and four‑way termination, flow and congestion control, select/poll/epoll mechanisms, zero‑copy techniques, Redis advantages and persistence, MySQL MVCC and repeatable‑read semantics, as well as core C++ topics and common algorithm questions.
MySQL 8.0 introduces numerous enhancements including InnoDB engine defaults, atomic DDL, instant column addition, CTE support, UTF8MB4 default charset, Clone plugin, resource groups, role management, multi-valued and functional indexes, invisible indexes, descending indexes, SET_VAR syntax, persistent parameters, window functions, online DDL, and improved backup and binlog capabilities.
This article explains common scenarios that cause MySQL (and related services) to consume 900% CPU, outlines step‑by‑step diagnosis using tools like top and show processlist, and provides practical remediation such as killing offending threads, adding missing indexes, disabling slow‑log, and leveraging Redis caching to bring CPU usage down to acceptable levels.
While containers and Docker dominate modern infrastructure, this article critically examines whether MySQL should be containerized, highlighting data safety, performance, statefulness, and resource isolation concerns, and offering practical strategies such as separating data from the program, using lightweight or distributed databases, and appropriate deployment choices.
Upgrading MySQL 5.6/5.7 databases to 8.0 can introduce charset mismatches between existing utf8 (utf8mb3) tables and newly created utf8mb4 tables, causing index loss and performance degradation that can be resolved by unifying the character set across all tables.
This guide walks through the background of MySQL read/write splitting, records troubleshooting steps for an unexpected slave crash, explains the two splitting models, and provides detailed commands and configuration examples for using Keepalived to ensure high availability of the slave node.
The article explains how to process massive MySQL data sets—covering data migration, export, and batch handling—by comparing regular pagination, streaming queries using server‑side cursors, and cursor‑based fetchSize techniques, and provides concrete MyBatis code examples for each approach.
This article examines how MySQL decides whether to use an index or perform a full table scan for COUNT(*) and other queries, explains the IO and CPU cost model, demonstrates the calculations with a sample table, and shows how optimizer trace can reveal mismatches between estimated and actual performance.
This article explains the need for database sharding as applications scale, describes horizontal and vertical partitioning techniques, compares popular sharding middleware, and offers practical guidance on choosing and deploying sharding solutions for different workloads.
This article explains how to assess MySQL table size, identify performance issues caused by massive data volumes, and presents three practical solutions—table partitioning, database sharding, and hot‑cold data archiving—along with detailed SQL examples and considerations for implementation.
This article explains the concepts of database sharding, compares vertical and horizontal partitioning, discusses ID‑range and modulo sharding methods, analyzes the read‑amplification problem caused by non‑sharding keys, and presents practical solutions such as auxiliary index tables, Elasticsearch integration, and TiDB adoption.
This article explains two technical solutions for keeping MySQL data in sync with Redis cache—using a MySQL trigger with a UDF function and parsing MySQL binlog events—while also reviewing the Canal open‑source tool and discussing their trade‑offs and implementation details.
The article examines why running MySQL in Docker containers often leads to data‑security, performance, state, and resource‑isolation problems, outlines practical mitigation strategies, and identifies scenarios where containerizing MySQL can still be viable.
The article explains how a MySQL backup using xtrabackup failed due to two concurrently running cron daemons launching the same backup script, resulting in corrupted compressed files, and provides practical solutions such as using flock, regular restore drills, and monitoring crond processes.
This article explains common scenarios that cause CPU usage to soar above 200% in production, outlines step‑by‑step diagnosis and remediation for MySQL and Java processes, and shares real‑world case studies with command‑line tools, indexing, caching, and code adjustments to bring CPU load back to normal levels.
This article analyses how MySQL 5.7 InnoDB processes COUNT(*) versus COUNT(1), COUNT(id) and COUNT on indexed or non‑indexed columns, showing execution‑plan differences across various index configurations, explaining why count(*) can be unexpectedly slower and offering practical guidance for query optimization.
When upgrading MySQL 5.7 tables that use utf8 or utf8mb3 to MySQL 8.0, mismatched character sets and collations can cause index loss and inefficient joins, but converting all tables to utf8mb4 and adjusting the driver table can restore optimal query performance, as demonstrated with detailed examples and profiling results.
Database sharding—splitting data across multiple databases and tables—is essential as applications scale, and this article explains the motivations, differences between sharding and partitioning, horizontal and vertical splits, common middleware options like Cobar, TDDL, Atlas, ShardingSphere, and Mycat, and how to choose the right solution.
This article explains what a database transaction is, details the ACID properties, compares MySQL's four isolation levels, clarifies why Repeatable Read is the default due to binlog formats and consistency, examines concurrency anomalies of each level, and provides practical guidance on handling large transactions, optimizing performance, and understanding InnoDB's MVCC implementation.
This article walks through a practical SQL optimization workflow, showing how to detect problematic queries via system metrics and execution plans, analyze table structures and indexes, apply concrete ALTER statements and index changes, and iteratively verify performance improvements, illustrated with a detailed MySQL case study that achieved a twenty‑fold speed increase.
This article analyzes why MyBatis‑Plus's default saveBatch method does not achieve true batch insertion, explains how to enable JDBC batch mode with rewriteBatchedStatements, and demonstrates creating a custom SQL injector (InsertBatchSomeColumn) to perform real batch inserts, including performance test results showing significant speed improvements.
This article explains how pressing Ctrl+C in the MySQL client triggers a KILL QUERY command, details the server's multi‑step handling—including thread coordination, flag setting, and transaction rollback—and clarifies differences when autocommit is on or off.
In this interview, ICBC senior manager Wei Yadong shares the bank's challenges with massive data, the five‑point criteria for database selection, the DevOps‑driven MySQL governance framework, evolving security demands, future database trends for finance, and practical advice for database professionals.
This article presents nine useful Bash scripts that automate common Linux system administration tasks such as DOS attack mitigation, email alerts, MySQL backups, Nginx log management, network traffic monitoring, server initialization, and multi‑host disk usage checks.
This article compares MySQL's DISTINCT and GROUP BY clauses, explaining how index availability, implicit sorting, and MySQL version differences affect their execution speed, and provides practical guidance on choosing the right clause for optimal query performance.
This article explains MySQL’s internal architecture, walks through how a SELECT query and an UPDATE statement are processed—including the connector, parser, optimizer, executor, redo log, binlog, and two‑phase commit—and details transaction properties, isolation levels, MVCC, and related configuration parameters.
This article explores web pagination across database, backend, and frontend layers, detailing MySQL LIMIT syntax, Java pagination implementations with PageHelper and Spring Data JDBC, and UI approaches using Thymeleaf and Element UI, providing code examples and comparative insights for developers.
This article explains OceanBase's three tenant deletion approaches—normal, delayed, and immediate—detailing the DROP TENANT syntax, recycle‑bin behavior, and providing step‑by‑step SQL examples for creating, dropping, restoring, and permanently removing tenants.
The article explains the classic cache consistency problem between MySQL and Redis, analyzes how inconsistencies arise, and details four cache‑update design patterns—delete‑then‑update, cache‑aside, read/write‑through, and write‑behind—highlighting their workflows, advantages, and drawbacks.
The article explains how to prevent OutOfMemoryError when exporting massive MySQL datasets by streaming records with JPA or MyBatis, writing them directly to CSV files, and demonstrates significant memory savings compared to traditional batch export methods.
