Even when scanning a 200 GB InnoDB table on a server with only 100 GB of RAM, MySQL does not consume all memory because it streams results using a limited net_buffer, employs socket send buffers, and InnoDB’s optimized LRU algorithm manages the buffer pool to prevent memory blow‑up.
This article explains how MySQL's EXPLAIN and EXPLAIN ANALYZE statements work, compares their outputs, and demonstrates through three practical examples how to interpret actual execution costs, row counts, and loop information to identify indexing and query‑plan inefficiencies.
This guide walks you through setting up a MySQL instance on AlmaLinux using Docker Compose and explains the most impactful my.cnf parameters—innodb_buffer_pool_size, max_connections, innodb_flush_log_at_trx_commit, and sync_binlog—so you can safely boost database performance.
Explore the comprehensive structure of MySQL, from its layered architecture and storage engines to detailed SQL language components, while learning essential DDL, DML, DQL, and DCL commands, performance tuning techniques, security best practices, and advanced features such as window functions, CTEs, and role-based access control.
Learn how MySQL’s three essential logs—Redo Log for durability, Undo Log for transaction rollback, and Binlog for replication—work together, understand their configurations, performance trade‑offs, and practical monitoring and recovery techniques to ensure data integrity in production environments.
This article explores the origins of the "no more than three‑table JOIN" rule, explains MySQL's join algorithm limitations, shows real‑world performance pain points in distributed architectures, and presents practical solutions such as step‑wise queries, denormalization, and materialized views to safely break the restriction.
This comprehensive guide explains MySQL backup strategies, compares full, incremental, and differential backups, details built‑in tools like mysqldump, mysqlpump, and third‑party solutions such as Percona XtraBackup, and provides scripts, scheduling tips, verification methods, and best‑practice recommendations for secure data protection and recovery.
This article walks through switching a SpringBoot + MybatisPlus project from MySQL to PostgreSQL, detailing driver addition, JDBC changes, numerous SQL syntax pitfalls, and provides helper scripts for batch column type conversion and default timestamp settings.
The article explains why using DELETE to remove rows in MySQL is discouraged, covering storage architecture, engine behavior, transaction handling, space reclamation, and compares DELETE with TRUNCATE and DROP in terms of speed, safety, and side effects.
This guide explains how to use PHP’s mysqli_query function to connect to MySQL, run SELECT queries, handle results, and perform INSERT, UPDATE, or DELETE operations, complete with a full code example and best‑practice notes.
This article provides a deep dive into MySQL’s architecture, covering the InnoDB storage engine’s layered structure, index design and usage, query execution plans, deep pagination challenges, slow‑query diagnostics, transaction processing, MVCC isolation, and the roles of undo, redo and binary logs.
This article explains how MySQL’s three essential logs—redo, undo, and binlog—work together to guarantee data durability, enable crash recovery, support transaction rollback, and power master‑slave replication, while providing practical configuration tips, monitoring queries, and recovery procedures for production environments.
This article explores the performance bottlenecks of a 700‑million‑row MySQL orders table and presents a comprehensive sharding solution—including vertical and horizontal partitioning, gene‑based shard keys, routing logic, global secondary indexes, data migration strategies, and common pitfalls—demonstrating how query latency can drop from seconds to milliseconds while supporting billions of records.
The article reveals REDstar algorithm and developer salary packages, explains why Xiaohongshu’s work schedule changed, and then provides detailed Java interview questions covering MySQL indexes, composite indexes, Java primitive types, boxing/unboxing, ReentrantLock vs synchronized, Redis usage, common commands, distributed locking, and the benefits of RocketMQ, all illustrated with code snippets and diagrams.
This comprehensive tutorial walks you through installing MySQL on AlmaLinux, covering prerequisite cleanup, official repository setup, secure configuration, service management, and an optional Docker‑Compose deployment, plus tips for connecting with DataGrip.
This article explains MySQL's InnoDB full‑text search, covering the theory of inverted indexes, how to create and use full‑text indexes with various MATCH…AGAINST modes, Boolean operators, query expansion, relevance calculation, and index removal, illustrated with practical SQL examples and diagrams.
This article explains how to handle MySQL order tables that have grown to billions of rows by applying vertical and horizontal sharding, gene‑based partitioning, cross‑shard query strategies, data migration techniques, and practical pitfalls to achieve massive performance improvements.
This article explains MySQL’s index structures, SQL execution plan analysis, query execution flow, transaction isolation levels, MVCC mechanisms, and InnoDB buffer‑pool caching, providing detailed diagrams and practical examples to help developers optimize database performance.
This article compares five common database extension strategies—from simple MySQL column additions to a hybrid MySQL‑HBase solution—detailing their implementation, advantages, drawbacks, and ideal scenarios, helping architects select the most scalable and maintainable design for evolving business data requirements.
The article first marvels at the record‑breaking sales of Xiaomi's YU7 electric SUV, then provides a comprehensive set of Java and Spring interview questions—including B‑tree differences, MySQL hash indexes, ReentrantLock internals, Spring's circular‑dependency resolution, AOP principles, and Spring Cloud components—to help candidates prepare for Xiaomi's upcoming campus recruitment.
This comprehensive guide walks DBAs through enterprise‑level MySQL architecture, master‑slave replication, high‑availability clustering, performance tuning, index optimization, backup and recovery strategies, monitoring, security hardening, fault diagnosis, and best‑practice checklists to build a stable, efficient, and secure database environment.
This article walks through optimizing a 60,000‑row XML‑to‑MySQL import by profiling the environment, measuring baseline performance, and applying JDBC batch rewriting, write aggregation, and asynchronous writes with LMAX Disruptor, ultimately reducing execution time from 300 seconds to about four seconds while keeping memory usage reasonable.
This article explains five frequent scenarios where Spring @Transactional fails—such as internal method calls, non‑public methods, swallowed exceptions, final/static methods, and unsupported MySQL engines—and provides concrete solutions and best‑practice rules for reliable transaction management.
This article breaks down MySQL's three core log types—undo log, redo log, and binlog—explaining how they support transaction atomicity, durability, MVCC, crash‑safe recovery, write‑ahead logging, and replication, while also covering buffer pool behavior and flush policies.
This article explains the JOIN order optimization technique known as the ‘small table drives large table’ principle, demonstrates its impact with a real e‑commerce query case, and reviews various MySQL join algorithms, their trade‑offs, and practical tuning tips.
This article examines the performance pitfalls of MySQL deep pagination and presents six practical optimization methods—including cursor‑based pagination, deferred joins, covering indexes, table partitioning, precomputed pages, and Elasticsearch integration—to dramatically speed up large‑scale order queries.
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.
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.
