This article explains what a prefix (partial) index is in MySQL, why it can reduce index size and improve query speed, outlines suitable scenarios and limitations, and demonstrates performance differences through a series of tests with varying prefix lengths on a large test table.
This article provides a detailed explanation of MySQL InnoDB's lock system, covering lock granularity, shared and exclusive locks, intention and auto‑increment locks, various row‑lock types, deadlock scenarios with examples, and the internal representation of locks in the engine.
This article explains InnoDB's multi‑granularity locking system—including table, row, intention, auto‑increment, and gap locks—detailing their modes, compatibility, internal representation, usage guidelines, deadlock scenarios, and best practices for avoiding concurrency issues in MySQL databases.
This article explains how a growing e‑commerce platform replaced a heavyweight MySQL intermediate table with a binlog‑driven pipeline that streams changes to Elasticsearch via Kafka, detailing the architecture, modules, customizations, monitoring, and performance results.
This article summarizes a real‑world MySQL database migration project, detailing pre‑migration preparation, backup and import commands, replication setup—including multi‑source replication for merging databases—and post‑migration verification steps, while sharing practical tips and pitfalls.
This article analyzes the intermittent “Public Key Retrieval is not allowed” error in MySQL 8.0 when using JDBC, C3P0, or Druid connections, explains the authentication plugins involved, and provides step‑by‑step configuration fixes and best‑practice recommendations.
This article explains XtraBackup's backup workflow, introduces the --parallel and --compress-threads options, and presents performance test results showing how these parameters can significantly reduce backup time for large MySQL databases.
This guide walks through setting up an Oracle‑MySQL transparent ODBC gateway on RHEL, covering driver installation, odbc.ini and listener configuration, DBLINK creation, a common WHERE‑clause loss problem, and the solution of switching to the ANSI ODBC driver.
This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.
This tutorial walks through upgrading a Spring Boot 2.x project to Java 11, updating dependencies such as MyBatis and MySQL connector, configuring the Baidu UID generator database schema, adjusting MySQL settings, and building a uid‑provider microservice with full Maven and code examples.
The article compares three stock‑deduction approaches—single‑record MySQL, sharded MySQL rows, and Redis INCRBY with Lua scripts—analyzes their concurrency drawbacks, and presents a complete Java implementation using Redis, distributed locks, and callback‑based stock initialization.
This guide explains how to enable the PDO extension, configure php.ini, write connection code with proper error handling, and debug SQL statement errors to resolve common MySQL connection problems in PHP on Windows environments.
This article explains MySQL AUTO_INCREMENT behavior, demonstrates creating tables with unsigned int primary keys, shows how to set and inspect the AUTO_INCREMENT value, discusses its maximum limits, and recommends using bigint or explicit primary keys to avoid overflow and duplicate‑key errors.
This article explains how ordered data indexes improve query efficiency, compares B+Tree and hash indexes, discusses table partitioning versus sharding, outlines MVCC read types, examines row‑level lock pros and cons, and provides practical MySQL optimization tips including key vs index differences and engine choices.
This article explains why database passwords should be encrypted, introduces Alibaba's Druid as a simple solution for Java Spring Boot projects, and provides step‑by‑step instructions—including generating ciphertext with ConfigTools, configuring application.yml, and securely handling public keys—to protect MySQL credentials.
This article provides a comprehensive overview of MySQL InnoDB's lock subsystem, describing its data structures, lock compatibility matrices, lock/unlock workflows, lock splitting, inheritance and migration, and presents several optimization techniques such as avoiding lock_sys mutex, asynchronous deadlock detection, and lock_sys mutex sharding to alleviate contention in MySQL 5.7 and later versions.
This article explains why Java applications occasionally encounter the "Public Key Retrieval is not allowed" error when connecting to MySQL 8.0, analyzes the role of caching_sha2_password and user authentication cache, and provides practical solutions such as specifying the RSA public key, enabling AllowPublicKeyRetrieval=True, or switching to mysql_native_password.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT that triggered full‑table scans, lock contention and out‑of‑memory errors, and explains how proper indexing and awareness of transaction isolation can prevent such incidents.
This article explains how MySQL versions prior to 8.0 automatically sorted GROUP BY results, why the optimizer added this hidden ordering, how the behavior was removed in MySQL 8.0, and what developers must watch for when migrating, illustrated with concrete SQL examples and EXPLAIN output.
This article recounts a production server data loss caused by an erroneous rm -rf command, describes how ext3grep and extundelete were used to attempt file restoration, and explains how MySQL binlog replay finally recovered critical data while sharing lessons learned for future incident handling.
The DB‑Engines March 2021 ranking evaluates 364 databases, highlighting shifts among the top ten—including Microsoft SQL Server’s steep decline, MySQL’s growth surge, PostgreSQL’s continued rise, and Snowflake’s dramatic climb—while offering trend graphs and insights for professionals choosing the right database technology.
This tutorial explains how to perform asynchronous MySQL operations in Python using aiomysql, covering basic connections, CRUD actions, safe parameter handling to prevent SQL injection, connection pooling, transaction management, integration with SQLAlchemy for ORM queries, and a custom reconnection wrapper for resilient database access.
This article experimentally demonstrates how increasing MySQL's innodb_log_buffer_size reduces redo‑log write I/O, showing fewer write operations but similar total data volume, and explains why performance gains may be subtle in typical virtual‑machine storage environments.
This article explains how to diagnose and resolve MySQL slave replication failures that occur when XtraBackup’s --kill-long-queries-timeout and --kill-long-query-type options interrupt long‑running queries, detailing steps such as checking performance_schema, reviewing error logs, restarting replication, and adjusting backup parameters.
This article reviews the history of MySQL, introduces MariaDB as its open‑source fork, and presents a series of benchmark tests—including single‑row inserts, batch inserts, and various query scenarios—showing that MariaDB generally outperforms MySQL in speed while using more memory.
This article walks through a step‑by‑step optimization of a Java‑based Excel import pipeline, covering the transition from raw POI to EasyPOI and EasyExcel, caching database lookups, batch inserts, optimal batch sizes, parallel streams, and logging reductions to achieve sub‑minute import times for hundreds of thousands of rows.
This article provides a detailed shell-script tutorial for installing MySQL 5.7 on a Linux node, initializing the database, setting the root password, and automatically creating multiple user accounts with random passwords, suitable for operations and database administrators. The guide includes full script listings, explains repository configuration, service management, and error handling, making it a practical reference for DevOps engineers.
An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.
This article explains why caching MySQL with Redis is essential, outlines the consistency challenges that arise, and presents four practical solutions—expiration, delete‑then‑repopulate, message‑queue updates, and binlog subscription—along with their pros, cons, and guidance for choosing the right approach in interviews.
This article explains the technical details of data cleaning, dirty‑data handling, and three methods—database read‑only, application termination, and network ACL isolation—to ensure data consistency during the data‑regulation and cutover phases of cross‑cloud migration, illustrated with real‑world case studies.
This article explains how to use MySQL's EXPLAIN FORMAT=JSON to view detailed execution plan costs, illustrates table structures, walks through two query examples with cost breakdowns, and clarifies the calculation of read_cost and eval_cost for both simple and join queries.
This article explains how enterprises can evaluate their needs and compare four popular open‑source relational databases—MySQL, PostgreSQL, MariaDB, and SQLite—covering licensing, performance, language support, strengths, and limitations to guide an informed selection.
This article outlines practical techniques for multi‑cloud data migration, covering challenges, resource categories, migration phases, and detailed synchronization and verification methods for MySQL databases, file systems, and object storage using tools such as UDTS, pt‑table‑checksum, sync_diff_inspector and US3SYNC.
During a holiday‑time alert, a seemingly harmless pair of UPDATE statements on the same InnoDB table caused a deadlock, and the article walks through log analysis, reproduction steps, lock mechanics, and practical ways to avoid such deadlocks in MySQL.
This guide outlines a step‑by‑step learning roadmap for PHP beginners, covering HTML/CSS/JS fundamentals, core PHP syntax, database integration with MySQL, object‑oriented programming, and popular frameworks, while emphasizing practice, project building, and continuous skill refinement.
This article explains practical strategies for adding a new column to a MySQL table with tens of millions of rows, including creating a temporary table, copying data, renaming tables, and alternative replica‑based or third‑party methods to avoid long locks and potential crashes.
This article introduces Python's DB-API for interacting with various databases, explains how to use PyMySQL and MySQLdb for MySQL operations, demonstrates basic CRUD examples, shows techniques to prevent SQL injection, and presents connection pooling solutions with DBUtils for multi‑threaded applications.
This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.
This article explains practical techniques for handling dirty data, guaranteeing data consistency, and performing validation during the data cleaning and cut‑over phases of a cross‑cloud migration, including read‑only database accounts, process termination, and network ACL isolation.
This article explains how incorrectly placed double‑quotation marks in MySQL UPDATE and SELECT statements cause implicit type conversion, turning intended string assignments into zero values and making WHERE clauses always true, leading to massive data corruption and unexpected query results.
This article examines several MySQL pagination techniques—including basic LIMIT, subquery‑based, ID‑range, and temporary‑table methods—provides detailed performance measurements on a multi‑million‑row table, and offers practical recommendations for fast and scalable data retrieval.
This tutorial walks through creating a Spring Boot project, configuring Maven dependencies, setting up MySQL with MyBatis, defining entity, mapper, service, and controller layers, and building simple Thymeleaf login and registration pages, culminating in a runnable dynamic authentication demo.
This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.
This article explains MySQL transaction fundamentals, the ACID properties, common concurrency problems, isolation level differences, and how InnoDB implements MVCC using version chains and consistency views, illustrated with practical examples and diagrams.
This guide explains how to use MySQL’s built‑in mysqlslap tool and the third‑party sysbench utility to perform comprehensive database stress testing, covering configuration changes, command examples, result interpretation, and additional CPU and I/O benchmarks for accurate performance evaluation.
This article explains how MySQL treats NULL values, why columns defined with NULL can invalidate indexes, demonstrates the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, COUNT, DISTINCT, GROUP BY and ORDER BY with NULLs, and offers practical recommendations for schema design.
This article analyzes common causes of slow MySQL queries—especially index misuse—offers practical indexing techniques, explains MDL locks and large‑table bottlenecks, and then compares ElasticSearch and HBase as complementary solutions for high‑performance search and storage.
This article reviews MySQL's evolution of high‑availability solutions—from classic master‑slave replication to InnoDB Cluster and InnoDB Replica Set—explaining each component, their features, deployment steps, and how to select the appropriate architecture based on business requirements such as RTO, RPO, and regional scope.
This tutorial demonstrates how to create a MySQL master‑slave replication cluster on Kubernetes using a StatefulSet, local persistent volumes, ConfigMaps, Secrets, and Services, covering storage class configuration, pod initialization, data cloning, replication setup, verification, and horizontal scaling.
This article compiles 52 practical SQL tuning strategies—including index best practices, query rewriting, use of temporary tables, proper data types, and server configuration tips—to dramatically improve MySQL query speed and overall database efficiency.
This article demonstrates how to create a test table, build indexes, and analyze their effectiveness across multiple query scenarios using MySQL's EXPLAIN output, highlighting the best‑left‑prefix rule, range condition impacts, ORDER BY considerations, and common pitfalls such as Using filesort and temporary tables.
This article traces MySQL’s evolution, introduces MariaDB’s origins, and presents a comprehensive benchmark comparing insert and query performance of MySQL 8.0.19 and MariaDB 10.4.12 on identical hardware, highlighting speed differences with and without indexes.
This article presents a systematic benchmark of MySQL 5.7.22 and MySQL 8.0.15 using sysbench to measure TPS and QPS across read‑write, read‑only, and write‑only modes under two persistence configurations, analyzes the results, and provides a Bash script for reproducing the tests.
This case study explains how a client with a 2‑billion‑row MySQL table avoided disk exhaustion and poor query performance by streaming data through Kafka to Google BigQuery, partitioning the data, and cleaning it to reclaim storage—all without downtime.
This article analyzes how many TCP connections MySQL will open when writing data at 100 rows per second for five seconds, examining MySQL processing capacity and connection‑pool configuration to illustrate the key factors that affect connection count.
This article explains the challenges of cross‑cloud migration, outlines three resource categories, details MySQL, file and object storage synchronization methods, presents practical tools and validation techniques, and shares real‑world case studies to ensure data integrity and efficiency.
This article explains the meaning of the Extra column in MySQL EXPLAIN output, covering how "Using index", "Using index condition", and "Using where" appear, when index coverage and index condition push‑down occur, and provides multiple practical examples with code.
This article investigates why the Alibaba Java Development Manual advises against joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, indexing effects, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article traces MySQL’s evolution, introduces its fork MariaDB, and presents a comprehensive performance benchmark—covering single and batch inserts, indexed and non‑indexed queries—on identical hardware, revealing that MariaDB generally delivers faster execution times albeit with higher memory usage.
This article explains MySQL's Partial Revokes feature introduced in version 8.0.16, showing how to enable the partial_revokes system variable, grant and selectively revoke SELECT privileges at both coarse and fine granularity, and verify the resulting restrictions using SHOW GRANTS and the mysql.user table.
This article explains why using ORDER BY with LIMIT for pagination in MySQL can yield inconsistent results when the ordering column contains duplicate values, describes MySQL's limit optimization behavior, and provides a reliable solution by adding a secondary unique sort key.
The article investigates the discrepancy between MySQL binlog execution time and slow query log duration for an INSERT statement, conducts an experiment with artificial I/O latency, and explains why the slow log provides a more complete measurement of query execution.
This article explains why, in MySQL row‑based replication, a master table having an extra column does not disrupt replication to a slave table lacking that column, provided column order, placement, and default values meet specific conditions, and demonstrates the behavior with a MySQL 5.7.25 test.
This article explains the fundamentals of MySQL table lookup (回表操作), compares low‑version and high‑version handling, and demonstrates how index pushdown reduces unnecessary lookups, using clear diagrams and a practical user‑table example.
This article explains the difference between logical and physical pagination, demonstrates how MySQL's LIMIT clause works, compares two query methods on a large MEMORY table, analyzes their performance with indexes and EXPLAIN, and provides practical pagination examples and Java code for efficient data retrieval.
The article analyzes a production incident where dozens of MySQL UPDATE statements unintentionally set a column to zero because misplaced quotation marks caused MySQL to interpret the expression as a boolean comparison with implicit type conversion, and it explains how to detect and prevent such errors.
This article explores why MySQL queries become slow, delves into index pitfalls and optimization techniques, then compares ElasticSearch and HBase architectures, offering practical guidance on when to use each technology and how to combine them for high‑performance data retrieval.
This guide explains how to configure MySQL for high‑concurrency testing, use the built‑in mysqlslap tool and the third‑party sysbench utility to benchmark read/write performance, CPU, I/O, and memory, and interpret the resulting metrics for database optimization.
This article explains the meaning of each column in MySQL's EXPLAIN output, including id, select_type, type, possible_keys, key, key_len, ref, rows, filtered, and Extra, with examples of various query patterns such as simple selects, joins, subqueries, UNIONs, and derived tables.
The article investigates the Alibaba Java Development Manual's rule against joining more than three tables by designing and executing extensive MySQL and Oracle experiments, generating massive test data, measuring query performance, and concluding that the restriction stems from join scalability limits on large datasets.
This article breaks down nine core MySQL index concepts—including index types, B+Tree advantages, search steps, covering indexes, index invalidation, left‑most prefix rules, index push‑down, and safe index creation on large tables—to help candidates answer interview questions about tree‑search operations and performance trade‑offs.
The article analyzes a real‑world incident where a series of MySQL UPDATE statements with incorrectly placed quotation marks caused the entire source_name column to be set to zero, explains MySQL's implicit type conversion behavior, and shows how to recover the data using binlog analysis and flashback SQL.
This article provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
This article explains the root causes of MySQL master‑slave replication lag—including random replay, high master concurrency, and lock waits—and presents practical solutions such as parallel replication, reducing master load, and reading from the master to ensure data consistency.
This article demonstrates a step‑by‑step method for diagnosing MySQL hangs by collecting callstack information, identifying mutex conflicts, and mapping function calls to code locations to pinpoint the root cause of deadlocks.
This article explains how MySQL's index‑merge optimization can cause row‑level deadlocks during inventory updates, analyzes the lock sequence and deadlock logs, and presents practical solutions such as forcing a specific index, disabling index‑merge, and creating a composite index.
The article explains MySQL transaction ACID properties, details the four isolation levels and their associated anomalies, describes InnoDB's record, gap, and next‑key locks, and provides concrete SQL examples illustrating how these mechanisms prevent phantom reads and cause lock‑wait timeouts.
The DTLE 3.21.01.0 release introduces Kafka batch‑send parameters, support for rename‑table DDL, empty transactions for skipped DDL/DCL, and several bug fixes, while providing repository links, documentation, and installation tips for this MySQL‑focused data‑transfer component.
This tutorial explains how to set up MySQL master‑slave replication, configure MyCat as a database middleware, install and configure MyCat, and integrate it with a SpringBoot application using dynamic data sources and AOP to achieve transparent read‑write separation.
This article explains the three essential log files involved in MySQL write operations—redo log, binary log, and undo log—detailing the two‑phase commit process, their roles, write timing, storage mechanisms, and how they enable data durability, recovery, and replication.
This guide walks through MySQL's advanced features, covering lock types, transaction control commands, autocommit handling, savepoints, SQL mode configuration, regular expression usage, common SQL tricks, and a comprehensive overview of built‑in functions with practical code examples.
This article provides a comprehensive guide to advanced MySQL concepts, covering transaction control, various lock levels, lock and unlock statements, autocommit settings, savepoints, SQL security issues, SQL mode configurations, regular expression usage, and a collection of useful string, numeric, date‑time, and flow functions with practical examples.
The article explains why appending LIMIT to MySQL DELETE statements improves performance, reduces lock contention, and safeguards data by limiting rows affected, provides syntax details, compares deletion strategies for removing large numbers of rows, and offers practical recommendations for safe and efficient data removal.
This article walks through a systematic performance investigation of a Python‑Flask backend, using Chrome Network, flame‑graph profiling, and MySQL query refactoring to shrink a 37‑second endpoint down to 1.5 seconds, while sharing practical code snippets and optimization lessons.
This article explains the core concepts of MySQL transactions, the ACID properties, transaction states, isolation levels, multi‑version concurrency control (MVCC), and the various lock types and granularity used to ensure data consistency and concurrency control.
This article explains MySQL 8.0 window function FRAME clause syntax, differentiates ROWS and RANGE units, and demonstrates various frame boundaries such as CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, and expression‑based preceding/following with concrete query examples.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to execute, explores several ineffective optimization attempts, uncovers a client‑side LIMIT issue, and finally resolves the performance problem by forcing the use of the idx_end_time index, cutting the runtime to under a second.
This article explains InnoDB's record storage, primary and secondary index mechanics, composite index rules, and provides practical e‑commerce sharding and table‑design guidelines with real‑world case studies to help developers optimize MySQL performance and storage efficiency.
A production MySQL database suffered CPU overload due to a high‑volume query that bypassed an index because a leftmost column was missing, leading to full‑table scans, and the issue was resolved by adding a new composite index and correcting validation annotations in the Java code.
This weekly community newsletter summarizes recent MySQL issues, a recommended MySQL book, DBA team insights, MySQL 8.0.23 updates, index design tips, data recovery guidance, as well as DBLE and DTLE open‑source middleware progress, new features, bug fixes, community Q&A, and upcoming plans.
This article details a real‑world MySQL production data loss incident, explains how to identify the relevant binlog range, use binlog2sql and MyFlash to generate reverse SQL or binary logs, and outlines the step‑by‑step recovery process and post‑mortem reflections for operations teams.
This article examines the root causes of MySQL deadlocks in online services, explains transaction and lock mechanisms such as UndoLog and RedoLog, analyzes real‑world cases, and presents practical strategies—including isolation level adjustments and pre‑delete checks—to avoid and resolve deadlock issues.
This article explains how to achieve database read‑write separation in high‑concurrency scenarios using Spring Boot, Druid connection pool, and MySQL, covering master‑slave data source configuration, routing via AbstractRoutingDataSource, context holder management, custom annotations, AOP switching, and usage examples with code snippets.
This article explains how MySQL InnoDB generates AUTO_INCREMENT values, compares the full‑table locking mechanism before version 5.1 with the lightweight mutex introduced in 5.1, and describes the further improvements in 8.0 that rely on row‑based replication and lock‑mode settings.
The article documents a MySQL 5.6.35 deadlock occurring during inventory deduction, explains how Index Merge caused inconsistent lock ordering across multiple indexes, and offers solutions such as forcing specific indexes, disabling Index Merge, adding composite indexes, or restructuring the SQL to prevent the deadlock.
This article explains the concept, creation, and performance benefits of MySQL prefix indexes, demonstrates how to choose an optimal prefix length using selectivity calculations, and presents practical techniques for handling both prefix and suffix search patterns.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
This article explains why MySQL queries that use indexes can still become slow, examines full‑index scans, index selectivity, back‑table costs, and shows how features like index condition pushdown and virtual columns can dramatically improve performance.
The article reviews MySQL 8.0.23's maintenance release, detailing new features such as invisible columns, query attributes, security enhancements, InnoDB auto‑extend size, replication terminology changes, X protocol improvements, and various deprecations, providing code examples and links to official documentation.
The article explains why SELECT * slows down MySQL queries by increasing parsing cost, network traffic, and preventing index covering, and demonstrates how explicit column selection and well‑designed composite indexes can dramatically reduce I/O and improve query speed.
