The article explains why a SELECT COUNT(*) on a 5‑million‑row InnoDB table was extremely slow, analyzes the execution plan, demonstrates that the query used the primary (clustered) index, and shows how creating a small secondary index reduces the runtime from dozens of seconds to under a second.
This article analyzes the various system‑level and database‑level reasons why SELECT, UPDATE, INSERT, or DELETE statements become slow, covering network issues, OS and hardware constraints, MySQL configuration, indexing, execution plans, and query design, and offers practical mitigation strategies.
This article walks through preparing a four‑node MySQL environment, configuring password‑less SSH, setting up master‑slave replication with GTID, installing and configuring MHA, testing connectivity, running the manager, verifying automatic failover, and highlighting the key advantages of using MHA for high availability.
This article explains the MySQL Memcached API, its evolution from MySQL 5.6 to 8.0, demonstrates how to install the plugin, configure tables, and perform batch and range key retrievals with code examples, highlighting suitable use cases and performance benefits.
This article examines why MySQL auto‑increment primary keys can jump unexpectedly when using REPLACE INTO or INSERT … ON DUPLICATE KEY UPDATE, explains the role of innodb_autoinc_lock_mode, demonstrates examples, and offers practical solutions to prevent uncontrolled ID growth.
This article explains MySQL’s internal workflow, covering the Server layer components, Storage Engine layer, connection handling, query cache behavior, lexical and syntax parsing, optimization decisions, and execution steps, including tips for long‑connection management and common pitfalls.
This article presents a complete case study of diagnosing and optimizing a high‑traffic flash‑sale module in a Java SSM monolithic e‑commerce system, covering problem identification, root‑cause analysis, JVM/Tomcat/Redis/JDBC tuning, code refactoring, and the resulting stability improvements.
This article explains how MySQL stores data using pages, page directories, and multi‑page structures, demonstrates why MySQL silently sorts rows on insert, and shows how these mechanisms combine into the B+‑tree index that optimizes query performance and supports clustering, non‑clustering, and composite indexes.
This article documents an experiment using three virtual machines running MySQL 5.7.20 MGR to capture and analyze network traffic with a custom Wireshark dissector, revealing how the primary node updates views, removes a failed node, and re‑establishes cluster state after a crash.
This guide introduces MySQL 8's Explain Analyze tool, demonstrates how to prepend EXPLAIN ANALYZE to a query, interprets its detailed output—including cost, actual time, rows, and loops—using a sample query on the Sakila database, and provides steps to set up the test environment.
This article explains MySQL benchmark testing, the role and metrics of benchmarking, introduces sysbench, provides step‑by‑step installation and usage instructions, and offers practical tips for interpreting results and optimizing database performance in production environments.
DRC (Data Replicate Center) is Ctrip’s database middleware that provides real‑time bidirectional MySQL replication, achieving low‑latency, multi‑active data access across data centers while ensuring consistency through GTID, writeset, conflict resolution, DDL handling, and comprehensive monitoring.
This guide outlines comprehensive MySQL best practices covering naming conventions, table engine and charset choices, field type selection, index design principles, common index column recommendations, ordering of composite indexes, avoidance of redundant and foreign‑key constraints, SQL development rules, and operational guidelines for large‑scale data handling.
The article explains why MySQL may fail to start when the number of required AIO event slots exceeds the system's fs.aio-max-nr setting, demonstrates how to observe the slots with strace, and provides formulas and recommendations for configuring fs.aio-max-nr in single‑ and multi‑instance deployments.
This article analyzes a slow MySQL view query caused by mismatched character sets, demonstrates how the automatic charset conversion prevents index usage, and shows step‑by‑step how altering the table charset and removing the conversion restores index usage and reduces execution time from seconds to fractions of a second.
This article demonstrates how to set up Sharding-JDBC in a Spring Boot project, covering project structure, Maven configuration, entity, controller, service, mapper code, database DDL for multiple shards, sharding rules in application.properties, and API testing with Postman.
The article examines a severe database performance slowdown caused by excessive library cache locks, traces it to improper JDBC ResultSetType settings that introduce unwanted ROWID columns during SQL parsing, and presents systematic experiments across various queries, ResultSetType values, JVM configurations, and database versions to recommend optimal parameter choices.
This article explains why a DELETE statement with a sub‑query can cause a full‑table scan in MySQL, describes the optimizer's semijoin, materialization and exists strategies for different sub‑query types, and shows how to rewrite the statement using a JOIN for better performance.
This article explains the hidden costs of using SELECT * in MySQL, detailing how unnecessary columns increase parsing, network, and I/O overhead, and shows how proper column selection and composite indexes enable covering indexes for significantly faster query execution.
This article explains MySQL's support for XA distributed transactions, describes the two‑phase commit process, details the XA syntax, and walks through a complete hands‑on example that transfers money between two databases using XA START, END, PREPARE, RECOVER and COMMIT commands.
This article explains the fundamentals of MySQL indexes, covering their conceptual purpose, logical classifications, underlying data structures such as hash, B‑Tree, and B+Tree, physical storage differences between clustered and non‑clustered indexes, and practical tips for effective index optimization.
This article explains why using SELECT * in MySQL queries reduces performance, detailing increased parsing cost, network overhead, I/O for large columns, and loss of covering index optimization, and also expands on index concepts such as composite indexes and their impact on query efficiency.
This article explains MySQL's locking mechanisms, detailing row‑level, table‑level, and page‑level locks, their characteristics and usage, and compares optimistic and pessimistic concurrency control, including implementation methods, storage‑engine specifics, deadlock causes, and strategies to avoid them.
This article provides a comprehensive, step‑by‑step guide to MySQL's locking mechanisms—including table, row, gap, and deadlock handling—covering both MyISAM and InnoDB engines, complete with SQL examples, visual demonstrations, and practical tips for high‑concurrency scenarios.
This article explains a lightweight transactional message solution for microservices that leverages RabbitMQ, MySQL, and Spring Boot to achieve eventual consistency, detailing design principles, compensation mechanisms, table schemas, and deployment considerations for high‑throughput asynchronous processing.
This article explains MySQL's lock mechanisms—including row‑level, page‑level and table‑level locks, their shared and exclusive modes, the differences between optimistic and pessimistic concurrency control, and practical tips for avoiding deadlocks in InnoDB.
This article explains how MySQL processes a SELECT statement—from client request, cache check, parsing, optimization, and execution—to return results, using a sample User table, a detailed query example, and a step‑by‑step walkthrough of the parser, optimizer, and executor components.
This article explains a lightweight, low‑intrusive transactional message solution for microservices using Spring Boot, RabbitMQ, and MySQL, covering design principles, table schemas, transaction synchronization, compensation mechanisms, code implementation, and deployment considerations to achieve eventual consistency without sacrificing performance.
This article explains MySQL replication (master‑slave and master‑master), data sharding principles and implementations, and various distributed database deployment architectures, covering replication mechanisms, advantages, failure recovery, sharding strategies, middleware like Mycat, and scaling solutions for high‑availability systems.
This article explains how to explicitly specify character sets and collations in MySQL using the character introducer, CONVERT and CHARSET functions, SET NAMES/SET CHARACTER SET statements, and the COLLATE clause, with practical examples and guidance on coercibility.
This article provides a detailed overview of MySQL's locking mechanisms—including table, row, gap, and deadlock handling—across MyISAM and InnoDB storage engines, with practical code examples, performance considerations, and solutions for high‑concurrency scenarios.
This article explains the concept of half‑consistent reads in MySQL InnoDB, describes the conditions under which they occur, presents two test cases with detailed lock‑waiting behavior, analyzes why certain sessions are blocked or not, and summarizes the optimization benefits for RC isolation level.
In a June 10 live session, ICBC's software development center manager Wei Yadong presented the financial industry's distributed database needs and selection criteria, answering 31 detailed questions covering architecture, middleware, sharding, high availability, migration, and operational practices.
The article explains why MySQL’s legacy “utf8” character set only supports three‑byte sequences, causing errors with genuine four‑byte UTF‑8 characters, and shows how the proper “utf8mb4” charset resolves the issue, including historical background, practical examples, and migration guidance.
This article provides a comprehensive MySQL tutorial covering schema concepts, data types, service control, database creation, table manipulation, and complete CRUD operations with practical command‑line examples and code snippets for beginners and intermediate users.
This article presents a comprehensive collection of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting explicit columns, using LIMIT 1, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, preferring UNION ALL, applying full‑text indexes, and optimizing joins, pagination, and index usage—to help developers write faster, more efficient queries.
The article explains Meituan's MySQL inspection framework, covering its design principles, three‑layer architecture, inspection items, automation workflow, and operational results that reduced hidden risks and improved database stability.
In a June 10 live broadcast, ICBC software development manager Wei Yadong answered 31 audience questions covering the motivations, architecture, storage, high‑availability, migration challenges, and operational practices behind the bank’s distributed database selection and design decisions.
This article explains what database indexes are, their advantages and disadvantages, when to create or avoid them, various index types, and practical SQL optimization techniques, including code examples for efficient pagination in MySQL.
Although many developers hear the advice to avoid using SELECT * in SQL, this article delves into the underlying reasons—such as increased parsing cost, unnecessary data transfer, extra I/O, and loss of covering-index optimization—while also explaining index concepts and best practices for MySQL performance.
This article explains how to analyze and tune MySQL join queries with EXPLAIN, addressing issues like using temporary tables and filesort, and provides practical techniques—including STRAIGHT_JOIN and index strategies—to achieve sub‑second response times on tables with millions of rows.
This guide demonstrates how to recover MySQL 5.7 data after an accidental UPDATE by extracting row‑based binlog entries, converting them into executable SQL statements, and applying those statements to restore the original table contents.
This article walks through building complex test‑case search queries using MySQL CASE WHEN, FIND_IN_SET, and MyBatis dynamic SQL, demonstrates converting asynchronous data fetches to synchronous calls with CountDownLatch in Spring, and provides complete Java bean and configuration examples.
This article explains how MySQL variables are stored in memory and configuration files, the challenges of modifying them before MySQL 8, and how the new SET PERSIST and SET PERSIST_ONLY statements simplify persistent variable management with proper permissions and examples.
This article describes how Bilibili's Membership Purchase team built a Java‑based traffic replay platform using JVM‑Sandbox AOP, Kafka, and MySQL to capture real‑world request/response data, serialize it with JSON, and replay it for comprehensive regression testing of complex backend services.
This article explains the storage units of InnoDB, how B+‑tree indexes are organized on pages, calculates the maximum number of rows a B+ tree can hold based on page size and pointer count, and shows how to determine the tree height from the tablespace file.
This article explains MySQL's metadata lock mechanism and demonstrates how to use the ext.check.get_locks() function from the MySQL Shell plugin—along with a supporting SQL query—to list lock owners, statements, and lock types, including setup steps for MySQL 5.7.
This article analyzes a financial system's duplicate income‑splitting bug caused by concurrent transactions, explains how MySQL isolation levels and Spring transaction propagation contributed to dirty reads, and presents two corrective approaches—adjusting transaction propagation and repositioning Redis locks—to ensure reliable data consistency.
This article explains how to use EXPLAIN to analyze MySQL join queries, identifies causes of temporary tables and filesorts, and provides practical techniques—including driver‑table selection, STRAIGHT_JOIN usage, and index strategies—to optimize two‑ and multi‑table joins so that million‑row results return within two seconds.
This article walks through extracting service‑host mappings from MySQL using MyBatis, handling a Long‑to‑String conversion error, and demonstrates an insert‑select SQL pattern that requires column aliases to avoid duplicate‑column errors, complete with XML and Java code examples.
This article explains several reliable techniques for renaming a MySQL database—including full export/import with mysqldump, bulk table‑rename operations, and legacy binlog‑based approaches—while providing concrete command‑line examples, performance measurements, and step‑by‑step instructions for each method.
This guide explains how to use MySQL's built‑in mysqlslap tool and the third‑party sysbench utility to simulate concurrent client loads, adjust server settings, run read/write and I/O tests, and interpret the resulting performance metrics for database optimization.
This article explains MySQL's Multi-Range Read (MRR) optimization, demonstrates its performance impact through experiments, shows how to enable or disable MRR via optimizer_switch, and provides guidance on tuning related parameters such as mrr_cost_based and read_rnd_buffer_size for optimal query execution.
This article investigates the Alibaba Java Development Manual's recommendation against joining more than three tables by experimentally evaluating multi-table join performance in MySQL and Oracle using massive synthetic datasets, analyzing query execution times, indexing effects, and providing data generation scripts and detailed results.
This article explains MySQL Group Replication (MGR), covering its evolution from traditional replication, the full transaction lifecycle within an MGR cluster, and the detailed conflict‑detection mechanism that ensures data consistency and automatic failover.
This article investigates Alibaba's recommendation against joining more than three MySQL tables by designing experiments with synthetic data, measuring query performance on MySQL and Oracle, analyzing index impact, and providing full DDL and data‑generation scripts to explain the rule's practical limits.
An investigation of a DBLE performance issue revealed that identical sharding keys with the same stringhash rule failed to improve query speed because differing hashSlice configurations prevented proper partitioning, and after correcting the rule.xml function settings, the SQL executed efficiently with sub‑second response times.
This article presents comprehensive techniques for improving MySQL performance on large single tables, covering column choices, index design, query writing, engine selection, system parameters, hardware upgrades, read‑write splitting, caching, table partitioning, vertical and horizontal sharding, and guidance on selecting mature sharding solutions.
This step‑by‑step guide shows how to use Docker on CentOS 7 to pull MySQL 5.7 images, configure master and slave my.cnf files, run containers, set up replication, and verify that a test database created on the master is automatically synchronized to the slave.
The 2020 Stack Overflow Developer Survey, involving nearly 65,000 developers, reveals MySQL as the most popular database, highlights Redis, PostgreSQL and Elasticsearch as the most loved, lists DB2 and Oracle as the most feared, and presents salary and career trends for database‑related roles worldwide.
This article experimentally investigates whether MySQL re‑executes UPDATE statements that set a column to its current value, comparing behavior under ROW and STATEMENT binlog formats and revealing how InnoDB handles redundant updates.
The article investigates the Alibaba Java development manual's rule that joins involving more than three tables are prohibited, by designing massive MySQL and Oracle experiments, generating billions of rows, measuring query times, analyzing indexing effects, and concluding that the rule is MySQL‑specific and driven by large‑scale performance limits.
This article investigates how MySQL handles UPDATE statements that set columns to their existing values, comparing the behavior under ROW and STATEMENT binlog formats with binlog_row_image set to FULL, and shows that duplicate updates are skipped in ROW mode but executed in STATEMENT mode.
This article surveys MySQL audit capabilities, explains the limitations of general and binary logs, introduces several open‑source audit plugins with installation and configuration details, and compares MariaDB, Percona, and McAfee audit plugins in terms of granularity, log format, and performance impact.
This article explains how to retrieve and interpret key MySQL performance metrics—including TPS, QPS, thread status, InnoDB I/O, buffer pool health, MyISAM statistics, temporary tables, response time, and replication lag—using mysqladmin, SHOW GLOBAL STATUS, and Percona tools, with practical calculation formulas and command examples.
This article presents a hands‑on experiment that measures the performance impact of using pt‑table‑checksum on a MySQL master‑slave setup, explains the tool’s design choices such as reduced lock‑wait time and RR isolation, and concludes that its effect on production traffic is minimal when configured properly.
This article analyzes a production incident where concurrent MySQL transactions caused an over‑deduction of account balance, explains the underlying MVCC mechanism, consistency view rules, and how different isolation levels (RR vs RC) affect data visibility and lead to negative balances.
This article provides a comprehensive, ordered procedure for upgrading a Zabbix 4.0 monitoring system to version 5.0 using blue‑green deployment, backing up configurations, updating repositories, applying MySQL patches, and then migrating the associated Elasticsearch cluster from 6.1 to 7.x, including all necessary command‑line steps, configuration edits, and post‑upgrade validation.
This article presents a comprehensive guide to MySQL performance tuning, covering bulk‑insert optimizations, efficient INSERT statements, GROUP BY and ORDER BY improvements, OR‑condition handling, nested query replacement, and the use of SQL hints such as USE INDEX, IGNORE INDEX, and FORCE INDEX, with practical code examples and benchmark results.
This article explains the evolution of MySQL parallel replay from schema‑based MTS to logical‑clock and write‑set mechanisms, shows how to configure and tune parallel replication, and introduces a Jain‑index based fairness metric for evaluating the optimal number of slave_parallel_workers.
This article surveys open‑source MySQL audit plugins, explains their installation and configuration, compares their features, performance impact, and logging formats, and provides practical examples to help you choose the most suitable audit solution for your database environment.
This article explains MySQL character sets and collations, describes common character sets such as latin1, utf8mb3, utf8mb4 and gb18030, shows how to query available sets and collations, compares PAD SPACE and NO PAD behavior, and provides step‑by‑step migration examples between utf8, utf8mb3 and utf8mb4.
This article presents a thorough guide to improving MySQL performance for large single tables, covering field choices, indexing best practices, query tuning, engine selection, system parameters, hardware upgrades, read/write separation, caching layers, partitioning, vertical and horizontal sharding, and compatible scalable database alternatives.
This article walks through common interview questions on Linux memory monitoring and cache clearing, then dives into SQL join concepts, types, performance pitfalls, and practical optimization techniques for MySQL, providing clear explanations and actionable commands.
This article explains the MySQL client‑server handshake, details the sequence of packets exchanged during a TCP connection, and provides a complete Python implementation that builds a raw TCP socket to connect, authenticate, and communicate with a MySQL server without using a high‑level driver.
This article analyzes a real‑world MySQL incident where concurrent transactions caused an account balance to become negative, explains the underlying MVCC mechanism, consistency view rules, and how different isolation levels (RR vs RC) affect data visibility and locking behavior.
This article provides an extensive overview of MySQL fundamentals for interview preparation, covering primary key concepts, index types and usage, transaction properties and isolation levels, storage engine differences, query execution order, performance tuning with EXPLAIN, lock mechanisms, replication strategies, and high‑concurrency solutions.
This article examines how MySQL estimates execution costs for queries, explains why COUNT(*) can be optimal, demonstrates index selection with real‑world examples, and shows how optimizer_trace can reveal mismatches between estimated and actual performance.
The article compares MySQL and PostgreSQL across open‑source licensing, ACID compliance, SQL standard support, replication methods, concurrency control, performance, high‑availability solutions, external data sources, and storage models, highlighting each system’s strengths and weaknesses to help readers choose the most suitable database.
This article presents a comprehensive guide to MySQL performance improvement, covering maximum data volume, connection limits, query latency targets, practical implementation principles, optimal table design, index classification and tuning, as well as detailed SQL optimizations such as batch processing, operator rewriting, and pagination techniques.
This weekly newsletter presents the June DB-Engines database popularity ranking, explains its methodology, shares community resources such as Mycat diagnostics and MySQL tips, outlines DBLE and DTLE development updates, bug fixes, upcoming plans, and invites community feedback.
This guide outlines step‑by‑step methods to identify high load causes, troubleshoot replication interruptions, resolve instance hangs, and address common MySQL errors using commands like SHOW PROCESSLIST, EXPLAIN, iostat, and GTID management.
This article examines the functional requirements for likes, comments, and favorites in apps and compares MySQL and Redis based database designs, discussing schemas, queries, scalability challenges, and consistency considerations for handling moderate to massive data volumes.
This article explains MySQL execution order, highlights performance problems with large OFFSET LIMIT queries, demonstrates how implicit type conversion, sub‑query rewriting, EXISTS removal, condition push‑down, and intermediate result push‑down can dramatically improve query speed, and provides practical SQL rewrite examples.
The article defines MySQL response time as total request‑to‑reply latency, illustrates it with a travel analogy, and then details two monitoring solutions—Percona’s tcprstat utility that captures TCP‑level timings and the Query Response Time audit plugin that records latency distribution in INFORMATION_SCHEMA tables for analysis and Grafana visualization.
Meituan’s MySQL database inspection system uses a three‑layer architecture—execution agents managed by Crane, a metadata‑rich inspection database, and an integrated application UI—to run 64 automated checks, resolve over 8,000 hazards with sub‑four‑day remediation, and continuously improve automation and analytics.
This article describes the design and implementation of a data warehouse, covering storage engine choices, dimensional modeling techniques, ETL processes using Python scripts, and workflow management with Apache Airflow to address data integration, scalability, and maintenance challenges.
This article compares MySQL semi‑synchronous and asynchronous replication, presenting performance test results that show only a small difference in TPS and QPS, explains why the gap is minimal, and provides step‑by‑step instructions and sysbench scripts for setting up and benchmarking both replication modes.
A MySQL join between a large and a small table ran for seconds because differing character sets forced implicit conversions that disabled the index, and the article shows how to diagnose the issue with EXPLAIN, fix it by aligning charsets, and avoid similar pitfalls.
This article explains the internal B+Tree layout of InnoDB indexes, detailing page types, record organization, and how different primary‑key data types affect page density and overall index tree height, illustrated with concrete examples and visual diagrams.
This tutorial explains how to create a custom MySQL Docker image that automatically runs .sql, .sh, or .sql.gz initialization scripts on first container start, covering the underlying mechanism, Dockerfile steps, image build, container run, and verification tips.
This article explains the purpose of the MySQL EXPLAIN command, details each column in its output (id, select_type, table, type, possible_keys, etc.), provides real‑world SQL examples, and shows how to interpret the plan to identify indexing, join, and sorting issues for query optimization.
This comprehensive guide explains MySQL's layered architecture, storage engine plugins, index structures, transaction isolation levels, locking mechanisms, performance tuning techniques, partitioning, sharding, and replication, providing practical insights for developers and DBAs to optimize and scale MySQL deployments.
This tutorial demonstrates how to build a simple CRUD application for a WeChat mini‑program by integrating a Spring Boot backend with MyBatis‑Plus, MySQL, and the mini‑program front‑end, covering environment setup, Maven configuration, database schema, API implementation, and client‑side pages.
This article examines why MySQL is deployed on Kubernetes, outlines the technical challenges such as service routing, monitoring isolation, storage and high‑availability, highlights the benefits of cloud‑native networking and scheduling, and reviews popular MySQL operators and their implementation details.
This guide walks you through downloading DataGrip, configuring its appearance, creating MySQL and MongoDB data sources, managing tables, applying powerful SQL editing shortcuts, and leveraging export and execution‑plan features to write higher‑quality database code.
This article provides a comprehensive step‑by‑step guide for DBAs to safely upgrade MySQL from version 5.7 to 8.0, covering motivations, compatibility checks, new features, preparation tasks, upgrade commands, post‑upgrade verification, and rollback considerations.
This article examines how the increased latency of Intel's PAUSE instruction on Skylake CPUs creates a performance bottleneck for MySQL, details the profiling steps that identified ut_delay as the hotspot, and presents a series of optimizations—including spin‑wait parameter tuning and a MySQL 8.0 patch—that restore throughput on affected hardware.
This page compiles a chronological list of 100 WeChat articles covering Java interview topics, including core language concepts, data structures, concurrency, Spring, MySQL, Redis, and system design, providing readers with direct links to each issue for comprehensive preparation.
This article experimentally demonstrates how MySQL's table cache works, showing that it reduces file‑system reads of table definition files, operates per thread, and explains the recommended table_open_cache sizing formula based on concurrency and join complexity.
