This guide compiles essential MySQL optimization techniques—including index cardinality, function indexes, EXPLAIN usage, covering indexes, ICP, slow‑query log analysis, online schema changes, and common SQL pitfalls—to help developers efficiently tune database performance.
This article explains how to implement a host cache within the JVM and a MySQL‑based distributed lock in a Java backend service, including detailed code examples for the service layer, a static cache class, lock acquisition, and release logic.
This guide explains how to upgrade MySQL 5.6 instances to 5.7 on Windows by using the ZIP distribution and an in‑place upgrade, covering preparation, installation, required Visual C++ libraries, common pitfalls, troubleshooting steps, and a complete checklist of post‑upgrade actions.
An in‑depth investigation shows that counting rows with COUNT(*) on a MySQL table using the PRIMARY key index can be slower than a full table scan, revealing differences in execution plans, optimizer behavior, logical reads, and profiling data that explain the 0.02‑second performance gap.
This guide walks through installing MySQL nodes, Java, and MyCat 1.6.5, configuring schemas, data nodes, sharding rules, adjusting ports, adding users, and verifying the MyCat service, providing complete code snippets and troubleshooting tips for a functional distributed database middleware setup.
MySQL 8.0.21 introduces numerous enhancements such as dynamic redo log control, optional tablespace path validation, refined lock system, JSON_VALUE function, atomic CREATE TABLE…AS SELECT, optimizer improvements, reduced group replication cache, multi‑address X Plugin support, and various security and deprecation updates.
This guide walks through preparing the environment, configuring the master and slave MySQL servers, and using Python's Paramiko library to automate SSH commands, edit configuration files, restart services, and verify replication, all while handling common pitfalls.
This article explains why MySQL connections can fail even when the concurrent client count is below max_connections, by analyzing the max_connect_errors parameter, its interaction with the host cache, and how to resolve the issue using flush hosts and proper configuration.
The article explains why adding @Transactional in Spring may fail to roll back when MySQL uses the MyISAM engine, shows the required Hibernate dialect configuration for InnoDB, and offers tips for diagnosing similar transaction issues.
This tutorial introduces Deno.js, explains its security‑first runtime features, compares it with Node.js, and walks through building a complete MySQL‑backed CRUD REST API using TypeScript, covering installation, code structure, and execution steps.
This article tests whether MySQL re‑executes an UPDATE statement that does not change any column values, comparing the behavior under binlog_format=ROW and binlog_format=STATEMENT on MySQL 5.7.25 running on CentOS 7.4.
After upgrading a critical MySQL client from 5.6 to 5.7, duplicate‑key errors appeared during inserts on master, replica and read‑only instances, prompting a deep dive into InnoDB auto‑increment mechanics, related kernel bugs, on‑site analysis, and both application‑side and engine‑side remediation strategies.
This article explains how to identify and measure large MySQL transactions by extracting GTID events from binlog files using Linux command-line tools such as grep, awk, and sort, and demonstrates the process with example commands and visual results.
This article explains the database design for likes, comments, and favorites in apps, comparing MySQL table structures and Redis storage solutions, discussing query patterns, scalability challenges, and data consistency measures for moderate to massive traffic scenarios.
This article walks through diagnosing severe SLA alerts caused by slow queries on a massive MySQL table, analyzes query patterns with pt‑query‑digest, redesigns indexes, applies online DDL and pt‑osc for index changes, and demonstrates batch‑delete techniques that dramatically reduce execution time and replication lag.
This tutorial explains two approaches to achieve MySQL read/write splitting—first by manually implementing routing logic in Spring Boot with MyBatis interceptors and dynamic data sources, and second by configuring the MyCat middleware for automatic load balancing, fault‑tolerance, and multi‑node support, complete with code samples and configuration files.
The article shares practical experiences on running test cases concurrently using Java multithreading and a custom thread pool, and outlines MySQL database naming, null handling, and schema design guidelines, including full code examples for the case runner and thread pool implementation.
After upgrading a critical MySQL 5.6 database to 5.7, inserts on the master began failing with duplicate‑key errors due to unexpected auto‑increment values, prompting a deep dive into InnoDB internals, related bugs, and practical mitigation steps.
This article explains how MySQL 8.0 introduces atomic DDL by moving the data dictionary into InnoDB system tables, describes the pre‑8.0 non‑atomic behavior, outlines the DDL execution phases, shows practical comparison results, and details how to view and configure DDL logs.
This article explains MySQL's storage engines, their features and selection criteria, guides on choosing appropriate data types such as CHAR, VARCHAR, TEXT, BLOB, numeric and date types, and discusses character sets and related SQL commands.
This article walks through the evolution of MySQL index implementations—from hash tables and binary search trees to AVL, red‑black, B‑trees and finally B+ trees—explaining their performance trade‑offs, disk‑I/O considerations, and how InnoDB and MyISAM store data and indexes differently.
This guide explains how to use MySQL binary logs and GTID to restore accidentally deleted data, covering configuration changes, command‑line operations, extracting relevant log segments, and verifying the restored tables step by step.
This article explains how to specify and manage character sets and collations when creating or altering MySQL databases, tables, views, triggers, and columns, illustrating the impact on stored procedures and data retrieval with practical SQL examples.
This article explains Linux CPU states, identifies typical MySQL queries and configurations that cause high CPU usage, and provides practical optimization techniques—including indexing, buffer tuning, and proper use of system tools—to diagnose and reduce MySQL-related CPU bottlenecks.
This article introduces the Screw plugin, a lightweight tool that automates database table structure documentation across multiple databases, supports various output formats, and can be configured via custom Java code or a Maven plugin to streamline enterprise development workflows.
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.
