Facing a 50‑million‑row financial transaction table, the team implemented horizontal sharding with sharding‑jdbc, designed migration and pagination strategies, tackled multi‑datasource transaction issues, and outlined a phased rollout, providing practical insights for large‑scale MySQL table splitting and backend system stability.
The article explains why storing IPv4 addresses as VARCHAR is suboptimal, demonstrates that a 32‑bit unsigned INT perfectly fits an IPv4 address, compares storage size and query performance, and shows how to use MySQL's inet_aton and inet_ntoa conversion functions.
This article explains how to customize the OBClient client’s welcome output by installing dependencies, cloning the OceanBase OBClient source, editing specific source files to replace default strings, recompiling the client, installing the new package, and verifying the changed prompts.
This technical guide details a real‑world Kubernetes deployment, covering a 10‑node cluster specification, master‑worker architecture, containerized frontend and backend services, MySQL persistence, load‑balancing, step‑by‑step deployment procedures, and monitoring setup.
This tutorial explains how to build a PHP-based single sign‑on system using a main login application, a shared authentication service backed by MySQL and Redis, and multiple external applications, providing complete code examples for database setup, login handling, session sharing, and logout.
This article explains how to enable fuzzy search on encrypted sensitive fields such as phone numbers and ID numbers in a MySQL database by using Spring Boot, AOP, tokenized ciphertext mapping tables, and encrypted keyword queries to balance security and query performance.
This article explains what indexes are, when they should be applied, and provides step‑by‑step MySQL commands—including single‑column, composite, and index‑hint examples—to improve query performance on large datasets.
This article details a November 2022 database outage caused by a sudden CPU and load surge, explains how the team diagnosed the issue, outlines the emergency steps taken, and shares practical SQL performance optimization recommendations to prevent similar incidents.
This article walks through creating a 10‑million‑row MySQL table, measuring pagination performance with LIMIT, and applying practical optimizations—such as index‑based subqueries, ID‑range queries, and column selection—to dramatically reduce query latency on large data sets.
This article introduces Sequel Pro, a macOS GUI client for MySQL and MariaDB, explains how to configure standard connections with required fields, describes the main interface features such as database selection, table structure and content viewing, and highlights additional tools like Query and Table Info.
This article compares B+ trees and skip lists, explaining their structures, insertion and search complexities, and why MySQL chooses B+ trees for disk‑based indexing while Redis prefers skip lists for in‑memory sorted sets, highlighting trade‑offs in read/write performance and I/O costs.
This article provides a comprehensive guide to MySQL indexing, covering index types, data structures, clustering vs. non‑clustering, hash and B+Tree indexes, covering indexes, index push‑down, index merge strategies, cost‑based index selection, common pitfalls, and practical indexing principles with illustrative examples and SQL snippets.
The article explains why MySQL 8.x may ignore a primary‑key index and perform a full table scan for an IN subquery, describes the optimizer’s cost‑model reasoning, and shows how rewriting the query with an INNER JOIN restores index usage and dramatically improves performance.
This article explains how MySQL's lock_time metric is calculated by summing table‑level and row‑level lock wait times, details the InnoDB implementation, shows source‑code paths and functions involved, and clarifies why lock_time may appear small even when query execution is long.
The article investigates a MySQL 8.0 instance where many sessions were stuck in the executing state, analyzes InnoDB thread‑concurrency settings, stack traces and source code to reveal that exhausted InnoDB tickets caused nanosleep‑based blocking, and proposes configuration and SQL‑optimisation solutions.
This tutorial walks through setting up an Ubuntu 16.04 vulnerable environment, gathering information, uploading a webshell via MySQL into outfile or log injection, establishing a reverse shell with Metasploit, and finally exploiting CVE‑2021‑4034 for kernel privilege escalation, while also covering post‑exploitation persistence techniques.
This tutorial walks through the rapid installation of Nacos in MySQL mode, the configuration of Seata 1.5.0, the creation of required database tables, the development of three micro‑services (stock, order, account) with Spring Cloud Alibaba, and the verification of global transaction rollback using a sample purchase scenario.
This article explains how MySQL handles auto‑increment primary keys, InnoDB’s internal row_id, transaction Xid and trx_id counters when they reach their maximum values, the resulting primary‑key conflicts, data overwrites, and a theoretical dirty‑read bug caused by counter wrap‑around.
The SQLE 2.2305.0-pre3 release introduces Webhook-based work‑order status notifications, Oracle rollback statement generation for the enterprise edition, updated documentation links, a detailed feature list, bug fixes, and references to previous versions and further reading resources.
This article demonstrates multiple strategies for inserting 300,000 rows into a MySQL table using MyBatis and JDBC, comparing batch‑insert, single‑row loops, packet size limits, transaction handling, and performance tuning techniques such as batch size, waiting intervals, and index management.
This article explains MySQL’s various lock types—global, table, metadata, intention, and row‑level—and shows how SELECT statements acquire different locks under each isolation level, with examples for existing and missing rows, range scans, unique and non‑unique indexes, and full‑table scans.
This article introduces a Python‑based online movie website built with the Flask framework, detailing its project structure, front‑end using HTML5 and Bootstrap, back‑end in Python 3, MySQL database integration, deployment instructions, and includes screenshots of the UI and architecture.
This article shows how a concise 10‑line shell script, created with ChatGPT, can continuously capture MySQL QPS, TPS, and connection counts and append the metrics to a log file for performance monitoring.
This article explains how to identify when a MySQL DDL operation is blocked, locate the blocking session using the sys.schema_table_lock_waits view, and resolve the issue by enabling necessary instruments and killing the appropriate connection, with examples for MySQL 5.7 and 8.0.
This tutorial walks through the complete environment setup, including rapid installation of Nacos with MySQL mode, configuring Seata 1.5.0 for distributed transactions, creating the required MySQL tables, building micro‑services with Spring Boot, defining Feign clients, enabling global transactions, testing the workflow, and noting important version compatibility issues.
This article recounts how a finance team tackled a 50‑million‑row MySQL table by analyzing system bottlenecks, defining splitting goals, selecting sharding‑jdbc, addressing multi‑datasource transactions and pagination, designing data migration strategies, and executing a staged rollout to ensure stability and performance.
This article examines why long‑running SELECT COUNT(*) statements are not recorded in MySQL's slow query log, analyzes the relevant source‑code functions, demonstrates debugging with gdb, identifies the min_examined_row_limit setting as the root cause, and provides practical solutions and bug‑report details.
This article presents a comprehensive engineering case study of a high‑traffic membership system, detailing the dual‑center Elasticsearch high‑availability design, traffic‑isolated three‑cluster ES architecture, Redis caching strategy, dual‑center MySQL partitioning and migration plan, abnormal member relationship governance, and future fine‑grained flow‑control and downgrade policies.
Through hands‑on experiments with a 10‑million‑row MySQL 5.7 table, this article shows how standard LIMIT pagination becomes slower as offset and data volume grow, then presents practical optimizations—sub‑queries, ID‑based ranges, and column selection—to dramatically improve query performance.
This article details how a finance team tackled a 50‑million‑row MySQL transaction table by designing a horizontal sharding strategy with Sharding‑JDBC, addressing performance bottlenecks, multi‑source transaction handling, pagination across shards, and a phased data migration and rollout plan.
The article explains why adding a LIMIT clause to MySQL DELETE statements is a recommended habit, detailing performance gains, reduced locking, safer error handling, and practical execution strategies for large‑scale deletions.
The article details how a finance team tackled a 50‑million‑row MySQL transaction table by analyzing the pre‑split problems, defining split goals, choosing sharding‑jdbc, addressing multi‑datasource transaction and pagination issues, designing a phased migration and rollout plan, and summarizing lessons learned.
After upgrading from MySQL 5.7 to 8.0, a client experienced a flood of slow queries despite low system load; the root cause was a change in the sys.innodb_lock_waits view that now relies on performance_schema.data_locks, whose large lock table holds a global mutex and blocks other transactions, a problem solved by adding an index to reduce lock volume.
This guide demonstrates how to decouple data change logging from business logic in a SpringBoot application by leveraging MySQL binlog monitoring with Canal, forwarding change events through RabbitMQ, and persisting both new and old record states using Docker‑compose, configuration files, and Java client code.
An engineer troubleshoots a .NET application login freeze by capturing a dump with WinDbg, discovers the thread blocked in MySQL SSL handshake, verifies the MySQL driver version, and resolves the issue by disabling SSL via the connection string, illustrating effective debugging without source code or logs.
This article explains how to obtain distinct code entries together with their cdate and ctotal fields in MySQL, covering the use of DISTINCT, GROUP BY, handling ONLY_FULL_GROUP_BY mode, and alternative solutions such as GROUP_CONCAT with DISTINCT.
This article examines the challenges of data consistency verification during database migrations, reviews existing tools like pt-table-checksum, sync‑diff‑inspector and gt‑checksum, outlines essential capabilities, and proposes a detailed workflow and future enhancements for a versatile, low‑impact consistency checking solution.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures the latency of ordinary LIMIT pagination, analyzes the impact of offset size and result set size, and presents practical optimization techniques such as sub‑query pagination and ID‑range filtering to dramatically improve query speed.
This article walks through creating a 10‑million‑row MySQL table, measuring pagination query times under different offsets and result sizes, and presents practical optimizations such as sub‑queries, ID‑range filtering, and column selection to dramatically improve performance while explaining why using SELECT * is discouraged.
This article summarizes the key technical questions and answers from a MiHoYo spring recruitment interview, covering Java string handling, synchronized locks, exception types, operating‑system IPC mechanisms, MySQL indexing and isolation levels, as well as DNS resolution and TCP packet issues.
This article details the design and implementation of a high‑performance, highly available membership platform serving billions of users, covering dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis caching strategies, MySQL dual‑center partitioning, seamless migration, and fine‑grained flow‑control and degradation mechanisms.
This article provides a comprehensive 15‑question guide on MySQL indexes, covering their definition, various types across data‑structure, physical and logical dimensions, situations where indexes fail or are unsuitable, B+‑tree advantages, search process, covering indexes, left‑most prefix, index push‑down, adding indexes to huge tables, using EXPLAIN to verify index usage, hash versus B+‑tree differences, pros and cons, and the distinction between clustered and non‑clustered indexes.
The SQLE 2.2305.0‑pre1 release introduces a MySQL‑instance‑based smart scan for slow queries, adds configurable data source defaults, fixes several MySQL rule and workflow bugs, and provides comprehensive documentation, download links, and usage examples for both community and enterprise editions.
This article demonstrates how to achieve cache‑database consistency by updating MySQL first, deleting Redis cache asynchronously via Canal, publishing change events to RabbitMQ, and handling acknowledgments manually in a Spring Boot application, complete with configuration steps and troubleshooting tips.
This guide explains how to pinpoint the MySQL statements that use the most CPU by mapping OS thread IDs to MySQL sessions, querying performance_schema and information_schema, and reviewing the execution plan to identify optimization opportunities.
This article explains why SQL queries become slow, how to capture slow queries using MySQL's slow query log, and provides detailed step‑by‑step methods for analyzing execution plans, profiling, optimizer tracing, and I/O parameters to identify and resolve performance bottlenecks.
This comprehensive guide covers MySQL basics such as join types, normal forms, data types, indexing strategies, lock mechanisms, transaction isolation levels, replication, sharding, and practical optimization techniques for handling large tables and high‑traffic workloads.
This comprehensive guide explains what MySQL indexes are, details various index types—including B+‑tree, hash, full‑text, and spatial—covers when indexes become ineffective, shows how to design, use, and troubleshoot them, and provides practical steps for large‑scale index management.
This article explains how to export large MySQL datasets in SpringBoot by streaming data directly to CSV, avoiding full‑memory loads that cause OutOfMemoryError, and provides complete JPA and MyBatis implementations, performance testing, and practical code examples for production use.
This guide walks through a real‑world MySQL master‑slave sync failure caused by a corrupted relay log, explains the replication mechanics, shows how to analyze slave status, use GTID to skip the bad transaction, and fully restore synchronization with step‑by‑step commands and screenshots.
This article compares four common approaches for synchronizing MySQL data to Elasticsearch—synchronous dual write, asynchronous dual write via message queues, scheduled tasks, and binlog‑based data subscription—detailing their advantages, drawbacks, implementation steps, and tool choices such as Canal, Maxwell, and Python‑MySQL‑Replication.
This article explains MySQL index fundamentals, focusing on the leftmost prefix principle, common index failure scenarios, the impact of SELECT *, range queries, functions, LIKE patterns, OR, IN, ORDER BY, and provides practical examples and optimization tips.
This article explains why MySQL slave lag is a critical issue, compares the official parallel replication schemes—including Commit‑Parent‑Based, Lock‑Based, and WRITESET—details their internal mechanisms, presents benchmark results, and provides the exact configuration parameters needed to enable high‑performance parallel replication.
This tutorial shows how to quickly set up a full PHP 8.2, Nginx, MySQL 8, Redis 6, and Composer environment on Ubuntu 20.04 by configuring Alibaba Cloud apt mirrors, installing required packages, adjusting configurations, and verifying the stack, all in roughly twenty minutes.
This article explains how MySQL handles queries that involve two separate indexed columns, demonstrates the index_merge execution plan with real‑world examples, details the three index‑merge strategies (intersect, union, sort_union), and shows how to control or disable the feature for better performance.
This guide explains why traditional mysqldump and file‑based methods fail for massive cross‑database sync, introduces Alibaba’s open‑source DataX middleware, details its framework and plugin architecture, walks through installation on Linux, shows how to configure MySQL source and target, and demonstrates both full and incremental data synchronization with practical JSON job examples.
This article walks you through downloading the MySQL 5.7 Docker image, configuring IPv4 networking, launching the container with proper volume mounts, troubleshooting common startup errors, verifying the setup, and discussing production‑grade considerations such as data persistence, memory isolation, and scaling.
This article examines the most frequent reasons MySQL queries become slow—including missing or ineffective indexes, deep pagination, massive tables, excessive joins, IN‑list overload, dirty pages, ORDER BY file‑sort, lock contention, and hardware limits—while offering concrete optimization techniques and best‑practice recommendations.
This article explains how MySQL implements partition pruning, describing the conditions under which the optimizer can skip irrelevant partitions, the internal data structures such as read_partitions and lock_partitions, the role of SEL_TREE and SEL_ARG, and provides a detailed example with code to illustrate the pruning process.
This article walks through MySQL 8.0.32 source code to explain how the server builds a tree for a WHERE clause, evaluates each condition using classes such as Item_cond_and, Item_cond_or, Item_func_gt, and Item_func_eq, and ultimately decides whether a row satisfies the query.
A detailed postmortem shows how a poorly optimized SQL query, triggered by a cache‑removal update, saturated MySQL's CPU and connection pool, causing cascading failures across a sales‑critical system despite low overall traffic.
This article examines a MySQL deadlock caused by concurrent INSERT operations on a unique index, detailing the detection, reproduction, root cause analysis using InnoDB status, performance_schema data, and provides scripts and recommendations to trace and resolve such deadlocks.
This article explains what ClickHouse is, why a large MySQL table caused multi‑minute queries, and how migrating the data to ClickHouse using Docker, CREATE TABLE AS SELECT, and a lightweight sync tool reduced query time to under one second, while highlighting pitfalls and performance reasons.
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.
