This article consolidates theory and practical calculations to reveal how MySQL's B+‑tree storage, page structure, and row size determine the realistic maximum number of records a single table can store, ranging from millions to billions depending on schema choices.
The article shares a detailed SF interview experience, covering typical Java interview questions such as self‑introduction, challenging projects, learning paths, abstract class vs interface, HashMap vs Hashtable, insertion steps, red‑black and B+ trees, MySQL and Redis indexing choices, large‑table optimization, JVM memory model, and the purpose of the Survivor space.
This article explains why MySQL 8.0 replication to a 5.7 slave fails because of the new utf8mb4_0900_ai_ci collation, analyzes the root causes, and provides step‑by‑step configuration and SQL‑based solutions to ensure a smooth upgrade without data loss.
This article introduces a non‑intrusive asynchronous processing SDK that leverages Spring transaction events, Kafka queues, and MySQL tables to ensure reliable execution, eventual consistency, and automatic retry without impacting the main business flow.
This article explores the pain points of a 700‑million‑row MySQL order table, presents vertical and horizontal sharding strategies, introduces gene‑based Snowflake IDs, details routing logic, migration steps, common pitfalls, and shows performance gains after applying the final architecture.
Learn how covering indexes eliminate table lookups, understand the mechanics of SQL back‑table operations in InnoDB, and apply the leftmost prefix rule for composite indexes, with clear examples and practical tips to ace MySQL interview questions and improve query performance.
This article explains why MySQL GROUP BY queries may run slowly, analyzes the execution plan showing temporary tables and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, adjusting buffer settings, and considering application‑level or materialized view solutions.
This article analyzes a slow SQL query by examining driver tables, execution plans, and table row counts, then demonstrates three optimization approaches—setting a small table as driver, setting a large table as driver, and switching to hash joins—showing their impact on execution time and providing practical guidelines for selecting the best strategy.
This tutorial walks you through installing the EasyCode IntelliJ plugin, creating a MySQL database, configuring IDEA’s database connection, setting up Maven dependencies and application.yml, generating entity, DAO, service, and controller code automatically, and finally running the Spring Boot project.
This article explains what MySQL indexes are, how they work, their advantages and drawbacks, the different index types, the inner workings of B+Tree structures, and the differences between clustered and non‑clustered indexes, providing practical insights for database optimization.
This article explains the fundamentals of database indexes, covering their purpose, underlying data structures such as B‑Tree, B+‑Tree, and hash tables, the advantages and drawbacks, different index categories in MySQL, and practical SQL examples for primary, secondary, and unique indexes.
This guide explains how to use mysqldump to back up individual databases, multiple databases, all databases, specific tables, and how to compress backups, include drop statements, export only schema, and restore data using both interactive and non‑interactive MySQL commands, plus an automated backup script.
This article explains why MySQL 5.7 community edition may block online DDL operations when leftover temporary table files remain, analyzes the root cause, and provides step‑by‑step commands and alternative solutions to safely remove the orphaned tables.
A two‑decade‑old MySQL bug (bug 11472) that prevents triggers from firing on cascading foreign‑key updates has remained unfixed, sparking community frustration, debates over ACID compliance, and a shift toward alternatives like PostgreSQL in modern applications.
Yearning is an open‑source Go + Vue MySQL audit platform that streamlines SQL review, approval, execution, and automatic rollback; this guide walks you through installing it on aimalinux or via Docker Compose, configuring DataGrip, and best practices for secure, traceable DBA workflows.
This guide explains how integrating large language models with MySQL can transform traditional database management by enabling intelligent fault diagnosis, automatic SQL optimization, knowledge‑base updates, and a complete monitoring‑optimization feedback loop, complete with implementation steps, code samples, and best‑practice recommendations.
This article analyzes 58.com’s recent business struggles and layoffs, then presents a comprehensive set of Java interview questions covering synchronized vs. Lock, JVM memory layout, garbage collection, TCP connection teardown, MySQL indexing pitfalls, and common sorting algorithms, providing detailed explanations and code examples.
This article presents ten practical rules for designing database indexes—covering business‑scenario analysis, the left‑most prefix principle, avoiding over‑indexing, using covering indexes, data‑type choices, function traps, prefix indexes, NULL handling, maintenance scripts, and monitoring—each illustrated with SQL examples, diagrams, and real‑world performance gains.
This article explains why setting appropriate Redis expiration times for user information improves performance, describes a Java test case that simulates realistic access patterns, and outlines common cache‑expiration scenarios with practical testing guidelines to balance freshness, memory usage, and database load.
VIDEX is an open‑source MySQL virtual‑index framework that enables cost‑based what‑if analysis without building real indexes, integrates with AI‑driven NDV and cardinality estimators, supports modular deployment, and has been validated on TPC‑H benchmarks to closely match InnoDB query plans.
This tutorial walks you through preparing your environment, configuring DataGrip, and executing SQL scripts to connect and manage both MySQL and PostgreSQL databases, complete with hardware requirements, connection parameters, sample code, troubleshooting tips, and performance optimization advice.
This article analyzes the poor performance of MyBatis‑Plus saveBatch, compares it with single inserts, manual SQL batching, and JDBC executeBatch, and shows how enabling the rewriteBatchedStatements flag can dramatically improve batch insert speed.
This article explains why MySQL‑Elasticsearch synchronization is needed in large‑scale projects, compares several sync methods—including synchronous and asynchronous double‑write, Logstash, Binlog, Canal, and Alibaba Cloud DTS—detailing their implementation steps, advantages, disadvantages, and typical application scenarios.
MySQL 8.0 (especially versions 8.0.20+) suffers a performance regression in table‑rebuilding operations like ALTER TABLE … FORCE, running up to three times slower than 5.7 due to the default innodb_doublewrite_pages setting, but the issue can be mitigated by adjusting the parameter or upgrading to 8.4+.
This guide walks through downloading MySQL, extracting it, creating a dedicated mysql user, setting up separate data directories, initializing each instance, configuring my.cnf, creating systemd services, starting and stopping the instances, setting passwords, and troubleshooting common errors, all on a single server.
This comprehensive guide dives into MySQL’s architecture, detailing storage engine choices, index structures like B‑Tree and hash, best practices for index design, and a range of SQL performance tuning techniques including batch inserts, query profiling, and optimization of ORDER BY, GROUP BY, and LIMIT clauses.
This tutorial demonstrates how to use variables and loop constructs in MySQL and PostgreSQL stored procedures to iterate over a users table, filter records by age, and simulate sending a system welcome message, while comparing syntax differences between the two databases.
This tutorial walks you through creating a student attendance management system that combines a Flask backend with a WeChat MiniProgram frontend, covering project structure, database setup, virtual environment creation, API configuration, and deployment for both server and mobile client.
Learn essential MySQL commands—from connecting and managing databases, tables, and users to performing CRUD operations, advanced queries, indexing, and backup/restore—along with practical code examples and optimization tips, enabling you to efficiently handle relational data in real‑world applications.
This article explains why MySQL GROUP BY queries may run slowly due to missing indexes, temporary tables, and filesort, and provides practical optimization techniques such as removing unnecessary sorting, adding indexes, tuning buffers, and using application‑level or materialized view solutions.
This tutorial explains why stored procedure parameters are essential, details the differences between IN, OUT, and INOUT parameters, and provides step‑by‑step MySQL examples—including environment setup, code snippets, execution results, common FAQs, and an advanced age‑checking exercise—to help developers write flexible, reusable procedures.
Learn how to create, execute, and troubleshoot a simple MySQL stored procedure using MySQL 8.0, covering delimiter changes, basic SQL syntax, result verification, common beginner mistakes, and extending the concept with parameterized examples.
This article walks through the challenges of synchronizing 50 million rows between disparate MySQL databases, explains why traditional mysqldump or file‑based methods fail, and then details how the open‑source DataX tool—its 3.0 framework, installation steps, job architecture, and JSON‑based configurations—enables fast full and incremental data transfers with concrete performance metrics.
The article explains how the lower_case_table_names setting controls case sensitivity of MySQL identifiers, why MySQL 8.0 makes this setting immutable after initialization, and provides practical configuration advice to avoid server crashes.
This guide walks you through identifying MySQL bottlenecks, using EXPLAIN, optimizing indexes, rewriting queries, tuning configuration, applying architectural patterns, and learning from real‑world cases to dramatically speed up your database.
This article explains MySQL's storage architecture, why using DELETE to remove rows is discouraged, compares DELETE, DROP, and TRUNCATE in terms of speed, space reclamation, transaction behavior, and auto‑increment handling, and provides practical commands and optimization tips.
This tutorial walks through configuring an NFS server and client on CentOS 7, installing the LAMP stack, setting up a virtual host, deploying WordPress, configuring the MySQL database, and verifying the site works, providing complete command‑line instructions and code snippets.
This guide explains what the MySQL slow query log is, why it matters for performance, how to check its status, enable it temporarily or permanently in Docker‑based deployments, troubleshoot command‑not‑found errors, read the log file, and filter entries to focus on the truly slow queries.
This article explains MySQL InnoDB's dual memory and on‑disk architecture, detailing the various tablespace types (system, file‑per‑table, general, undo, temporary), their internal structures (segments, extents, pages), configuration tips, and how tables, row formats, primary keys, auto‑increment lock modes, and B+Tree indexes are organized and managed.
An experienced DBA explains why adding a new column in MySQL can unexpectedly raise duplicate entry errors during online DDL, detailing the role of primary and unique keys, the innodb_online_alter_log_max_size setting, and how concurrent DML operations cause the conflict.
Learn step‑by‑step how to troubleshoot MySQL master‑slave replication problems, from verifying component roles and executing the correct SHOW SLAVE STATUS command inside the MySQL client, to fixing missing command errors, checking IO/SQL threads, and restarting replication in Docker‑based environments.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—through step‑by‑step examples, demonstrating their effects on dirty reads, non‑repeatable reads, phantom reads, and performance considerations, helping developers choose the appropriate level.
This article explains how to locate and analyze MySQL slow queries, understand index structures and types, apply best practices for index creation, handle large pagination, manage transaction isolation and MVCC, and implement master‑slave replication and sharding strategies for high‑performance databases.
This article explains what MySQL indexes are, how they work, their types—including primary, ordinary, composite, and full‑text indexes—covers B‑Tree and B+Tree structures, page organization, clustering versus non‑clustering indexes, and practical considerations for index design and performance.
This article explains MySQL's storage architecture, compares the DELETE, TRUNCATE, and DROP commands, shows their performance and side‑effects, and provides practical guidance on choosing the safest and most efficient way to remove data from a MySQL table.
This article explains why excessive MySQL joins can cause performance problems and provides a comprehensive set of techniques—including query splitting, temporary tables, denormalization, index tuning, query rewriting, result‑set reduction, configuration tweaks, big‑data tools, and summary tables—to effectively optimize multi‑table join queries.
This article explains MySQL’s locking mechanisms, categorizing locks by performance, operation type, data granularity, and finer‑grained levels such as gap and next‑key locks, and offers guidance on selecting the appropriate lock strategy for reliable concurrent database operations.
This article analyzes a typical Alibaba interview question that asks for the best index on a table with 27 columns for the query SELECT a, b, c FROM table WHERE d = X ORDER BY e, covering index design, field ordering, comparisons with alternative indexes, index condition pushdown, and the detailed MySQL execution process.
This article explains how MySQL's InnoDB engine implements full‑text search using inverted indexes, shows how to create and use full‑text indexes with various query modes, demonstrates Boolean operators and query expansion, and covers index removal techniques.
An in‑depth comparison of three popular MySQL high‑availability architectures—MHA, Percona XtraDB Cluster (PXC), and Galera Cluster—covers their principles, architectures, strengths, limitations, deployment scenarios, and best‑practice recommendations to help you select the optimal solution for your production environment.
This article details how the activity middle‑platform team identified the root causes of slow SQL queries—such as missing indexes, large data volumes, and inefficient joins—and applied data cleaning, partitioning, indexing, and query refactoring to slash daily slow‑query counts from thousands to just dozens.
This article explains the MySQL Connection Control Plugins introduced in 5.7.17, how improper configuration can lead to connection backlog and database hangs, and provides step‑by‑step installation, parameter tuning, testing procedures, and mitigation strategies to resolve the issue.
Learn how to quickly pinpoint MySQL performance bottlenecks on AlmaLinux by using built‑in tools like top, iotop, ss, and netstat, and automate snapshot collection with a simple Bash script that records system and container metrics for later analysis.
This article explains MaShang Cloud's RDS high‑availability design, detailing the distributed sentinel monitoring system, proxy layer, multi‑AZ disaster‑recovery strategies, and real‑world case studies that demonstrate how MySQL and Redis services maintain continuous, consistent access with minimal RTO and RPO.
This article presents a comprehensive guide on applying Linux Cgroups to isolate MySQL resources, covering CPU, memory, I/O, and network bandwidth controls, detailed configuration steps, performance testing with sysbench, and practical observations to ensure stable and predictable database behavior.
This article provides a step‑by‑step tutorial for DBAs to design a reliable MySQL backup strategy, write a Bash automation script, schedule it with cron, and perform restoration drills using Docker containers, while covering directory planning, command checks, cleanup, and common troubleshooting.
This article presents a series of MySQL interview questions and answers that illustrate typical situations where indexes become ineffective—such as leading wildcards in LIKE, NOT IN, NOT EXISTS, functions, inequality operators, OR conditions, mismatched ORDER BY, large result sets, and explains how to analyze execution plans and use FORCE INDEX to improve performance.
The article details a MySQL 5.7 bug where rollback SQL generated for a JSON NOT NULL column produces an empty string instead of NULL, explains the underlying binlog behavior, reproduces the issue with sample tables, and provides a practical workaround by converting the JSON column to TEXT before re‑importing the data, while recommending strict SQL_MODE for production.
The article explains how to retrieve coupon templates linked to a specific product ID in MySQL, compares a simple LIKE‑based fuzzy search with a more accurate multi‑value JSON index approach, and provides step‑by‑step SQL code and performance considerations.
This article introduces an open‑source novel content management system featuring a front‑end portal, writer and platform back‑ends, and a crawler module, detailing its functional modules, Springboot‑based architecture, installation steps, database setup, crawling methods, and access URLs.
This article explains how MySQL InnoDB transforms a simple INSERT statement into a complex on‑disk record by detailing the COMPACT row format, record header, variable‑length field list, NULL bitmap, and overflow handling, helping developers optimize storage and performance.
This article explains MySQL InnoDB locking mechanisms—including row, record, gap, next‑key, and intention locks—illustrates each type with SQL statements and performance_schema data, and shows how they interact during concurrent transactions.
This article explains why storing dates as strings is inefficient, compares MySQL DATETIME, TIMESTAMP, and numeric Unix timestamps—including storage size, range, timezone handling, and performance—and adds PostgreSQL equivalents and practical recommendations for selecting the appropriate type.
To avoid lock, page split, and index degradation when adding fields to billion‑row core tables, this article presents a practical solution that uses dynamic field extension via configurable management, JSON‑based extension fields, and an extension‑table architecture integrated with Elasticsearch for storage and retrieval.
This guide explains how to mitigate several MySQL-related security flaws—such as CVE-2022-32221, CVE-2023-21912, and CVE-2022-37434—by configuring iptables rules to restrict traffic, illustrating command usage, rule ordering, and the differences between DROP and REJECT actions.
This article explains MySQL dual‑master architecture, covering dual‑master replication, master‑slave replication, master‑master with backup nodes, and ring replication, while discussing their advantages, common pitfalls such as loop replication and data inconsistency, and practical solutions to ensure high availability and data integrity.
The article analyzes a Meituan interview question about caching 10 million MySQL rows when Redis can store only 200 thousand rows, presenting a five‑step three‑tier cache architecture that separates cold and hot data, selects appropriate eviction policies, uses proactive hotspot detection, adds multi‑level defense, and employs both scheduled and real‑time pre‑heating, with performance numbers showing an 85% hit rate and 100 ms latency.
This guide explains the fundamentals of Linux host file systems, LVM logical volumes, and Docker storage drivers, showing step‑by‑step how to format, mount, and persist MySQL data using Docker volumes and bind mounts on AlmaLinux servers.
This article examines a MySQL case where importing a 4 GB tablespace takes 13 minutes, explains why SHOW PROFILE reports most of the time in the System lock stage, and dives into the internal implementation of SHOW PROFILE, data collection, calculation, and the tablespace import code path.
This article demonstrates a practical MySQL performance test using a simulated e‑commerce scenario, detailing the user table schema, multithreaded Java code for inserting random user records, parameterized inputs, success counting, and resource management to evaluate and improve registration throughput.
This guide outlines a step‑by‑step method to diagnose and release MySQL table locks by checking open tables, inspecting running processes, querying InnoDB transaction and lock tables, and generating KILL statements to terminate blocking sessions.
This article provides a comprehensive walkthrough of JDBC for MySQL, covering driver loading, connection creation, statement and prepared statement objects, SQL execution with examples, batch processing, and performance considerations for test engineers using Java.
Facing a 50‑million‑row project with inaccurate reports and cross‑database operations, this guide explains why mysqldump and simple storage methods fail, introduces Alibaba’s open‑source DataX middleware, details its architecture, installation, and step‑by‑step configurations for full and incremental MySQL data synchronization.
This article explains how to assess MySQL table size from table capacity, disk usage, and instance limits, describes why large tables degrade performance due to B+‑tree depth, and presents three practical solutions—partitioning, sharding, and hot‑cold archiving—to improve query speed and manage growth.
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 with connection handling, query execution, result processing, and best‑practice notes.
This guide introduces the workbunny/mysql-protocol PHP library, outlines its PHP ≥ 8.1 and optional Workerman dependencies, shows how to install it via Composer, and demonstrates using its Binary and Packet utilities for reading, writing, and manipulating MySQL protocol binary streams.
This article introduces MySQL's tree‑style execution plan output using EXPLAIN FORMAT=TREE, explains its advantages over traditional and JSON formats, and demonstrates its practical use through eight detailed SQL examples with full plan analysis and performance insights.
This guide explains how to set up an automated MySQL backup solution on AlmaLinux using Docker containers, a Bash script, and crontab, covering environment preparation, script creation, command checks, logging, error handling, and optional enhancements such as email notifications and security best practices.
Learn how MySQL’s optimizer decides between indexes, why the same query can suddenly use a different index with dramatically slower performance, and practical steps—including analyzing execution plans, updating statistics, checking index coverage, and rebuilding fragmented indexes—to diagnose and resolve index selection issues.
This article demonstrates how to create a lightweight, offline MySQL CVE scanning and reporting tool by cloning the CVEProject JSON repository, leveraging DuckDB for fast JSON parsing and SQL queries, and providing simple shell commands for data updates, version scanning, and vulnerability matching.
To replace long UUIDs with short, sequential numeric account IDs, the article explores the limitations of Snowflake’s 64‑bit IDs, evaluates MySQL auto‑increment and REPLACE INTO approaches, identifies deadlock issues, and ultimately proposes a segmented free‑ID table with batch allocation to achieve compact, ordered IDs.
An in‑depth guide compares three MySQL high‑availability solutions—MHA, Percona XtraDB Cluster, and Galera Cluster—detailing their architectures, pros and cons, deployment scenarios, and best‑practice recommendations to help engineers choose the optimal HA strategy for their workloads.
This article provides a comprehensive overview of database transactions, explaining ACID properties, transaction states, explicit and implicit transaction control, isolation levels, and how to configure and use them in MySQL with practical SQL examples and code snippets.
A detailed benchmark comparing MySQL 9.0 and PostgreSQL 17.0 evaluates ingestion latency, throughput, saturation, and connection‑pool behavior, revealing that PostgreSQL consistently delivers lower write latency, higher QPS, better CPU and memory efficiency, and superior disk storage efficiency across both write and read workloads.
This article walks through using MySQL to pivot rows into columns, covering storage concepts, practical CASE‑WHEN aggregation techniques, and detailed SQL examples for sum, count, average and max calculations, while also discussing performance considerations for medium‑scale and large‑scale data workloads.
This article explains how to combine Elasticsearch and MySQL in a Spring Boot backend by selecting the technology stack, creating a shared Elasticsearch package, defining ES and MySQL entity classes, implementing repository and mapper interfaces, writing service logic, and demonstrating query testing.
This article discusses the motivations, comparative analysis, and practical steps for refactoring a Java backend project by replacing MongoDB with MySQL, covering pain points, selection criteria, schema redesign, entity mapping, and sample query code.
This article explains MySQL's index limits for InnoDB and MyISAM engines, detailing the maximum number of indexes per table, the maximum columns per index, and provides practical recommendations on how many indexes a table should have to balance performance and maintenance.
This article combines a Meituan salary update with detailed explanations of MySQL indexing strategies, B‑tree variations, ACID transaction properties, isolation levels, caching, handling high‑traffic queries, the Raft consensus algorithm, and an overview of gRPC, providing comprehensive backend development knowledge for interview preparation.
This article provides a comprehensive, step‑by‑step tutorial on deploying a MySQL master‑slave replication architecture using Docker containers, covering prerequisite checks, container setup, configuration files, replication user creation, master‑slave linking, testing data sync, and troubleshooting common issues.
This article examines a real‑world MySQL master‑slave replication lag issue caused by massive multi‑table transactions during a sharding operation, details the investigative steps using system tables, binlog parsing, and my2sql analysis, and proposes three practical mitigation strategies.
This guide walks you through locating MySQL deadlock logs, analyzing their contents to pinpoint transaction timing, order, and lock details, and identifying the root cause of the deadlock, including special locking scenarios that can trigger deadlocks.
The article examines the challenges of generating short, user‑friendly numeric account IDs, evaluates Snowflake and MySQL auto‑increment approaches, discusses deadlock issues with REPLACE INTO, and presents a final segment‑based solution that allocates ID blocks per login server while avoiding waste and concurrency problems.
This article analyzes a puzzling case where data consistency checks between a MySQL source and an OceanBase target report matching results despite visible record differences, explains the OMS ordinary and Inmode validation modes, and outlines scenarios that trigger Inmode verification.
This guide explains how to use Alibaba's open‑source DataX tool to perform high‑performance offline synchronization between heterogeneous MySQL databases, covering installation, framework design, job configuration, full‑ and incremental sync, and practical command‑line examples.
Index Condition Pushdown (ICP) is a MySQL 5.6 feature that pushes part of WHERE filtering to the storage engine, reducing row reads and I/O; this article explains its principles, activation, usage examples, performance testing, and conditions under which ICP can be applied.
This article explores the problem of generating short, sequential numeric account IDs, evaluates the classic Snowflake algorithm, proposes a MySQL auto‑increment based solution with a dedicated ID‑generation table, analyzes deadlock issues, and finally presents a segment‑based ID allocation scheme that reduces waste and supports scaling.
This article walks through the background, reproduction steps, investigation methods, and concrete solution for MySQL metadata lock (MDL) issues that cause DDL statements to hang, and provides practical tips to prevent similar lock problems in production environments.
This article explains what mysqldump is, describes its backup mechanism, lists command‑line options with detailed meanings, provides example commands for exporting databases or tables, and shows how to restore data using both system and source commands.
This guide explains why three servers are needed for high availability, walks through hardware and software preparation, network configuration, MySQL master‑slave replication setup, HAProxy load‑balancing, and firewall/SELinux adjustments, providing complete command‑line examples for each step.
