BestHub
Sign in
Tagged articles

MySQL

5000 articles · Page 31 of 50
Architect's Guide
Architect's Guide
Aug 8, 2022 · Backend Development

Cache Consistency Strategies: Updating Database and Cache, Deleting Cache, and Handling Inconsistencies

The article explains why caching is essential for high‑traffic read‑heavy services, analyzes the consistency problems that arise when data exists simultaneously in MySQL and Redis, compares four cache‑update strategies, and provides practical recommendations such as using expiration, delayed double‑delete, message queues, and binlog subscription to achieve eventual consistency.

Cache ConsistencyMySQLRedis
0 likes · 18 min read
Cache Consistency Strategies: Updating Database and Cache, Deleting Cache, and Handling Inconsistencies
dbaplus Community
dbaplus Community
Aug 7, 2022 · Databases

Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization

This comprehensive guide examines why slow SQL queries degrade system performance, outlines key factors such as data volume, access patterns, and processing methods, and provides concrete optimization techniques, case studies, index design principles, and advanced features like MRR and index push‑down for MySQL‑InnoDB.

InnoDBMySQLPerformance Tuning
0 likes · 14 min read
Mastering MySQL Performance: Practical Strategies for Slow‑SQL Optimization
ITPUB
ITPUB
Aug 7, 2022 · Databases

Why MySQL Unique Indexes Still Let Duplicates Slip Through and How to Prevent Them

This article examines a MySQL InnoDB pitfall where a unique index fails to block duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents practical solutions such as adjusting index columns, adding timestamps, delete status counters, hash fields, and proper bulk‑insert strategies.

Logical DeleteMySQLNULL handling
0 likes · 13 min read
Why MySQL Unique Indexes Still Let Duplicates Slip Through and How to Prevent Them
21CTO
21CTO
Aug 6, 2022 · Databases

August 2023 DB-Engines Ranking: Oracle Drops, MySQL & MongoDB Surge

The August 2023 DB‑Engines ranking shows Oracle’s steep 19.5‑point drop, while MySQL and MongoDB surge, PostgreSQL continues rising, and the top‑10 list highlights shifts across relational, key‑value, and document databases, with methodology based on search, job, and community metrics.

DB-EnginesMySQLOracle
0 likes · 4 min read
August 2023 DB-Engines Ranking: Oracle Drops, MySQL & MongoDB Surge
Java Backend Technology
Java Backend Technology
Aug 6, 2022 · Backend Development

How to Prevent Coupon Over‑Issuing in High‑Concurrency Scenarios

The article analyzes why a coupon‑distribution feature can issue more coupons than available under heavy load, explains the root cause of concurrent stock deductions, and presents four practical solutions—including Java synchronized blocks, SQL row‑level locking, Redis distributed locks, and Redisson—to reliably prevent over‑issuance.

JavaMySQLRedis
0 likes · 11 min read
How to Prevent Coupon Over‑Issuing in High‑Concurrency Scenarios
dbaplus Community
dbaplus Community
Aug 6, 2022 · Databases

Mastering MySQL JSON: From Basics to Advanced Partial Updates

This comprehensive guide explains MySQL's native JSON data type, its advantages over text storage, detailed CRUD operations, indexing strategies, migration from TEXT columns, the powerful Partial Updates feature, performance benchmarks, and a wide range of JSON functions with practical examples.

IndexingMySQLPartial Updates
0 likes · 39 min read
Mastering MySQL JSON: From Basics to Advanced Partial Updates
Java Backend Technology
Java Backend Technology
Aug 5, 2022 · Databases

Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits, Drawbacks & Java Example

This article explains why using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves query performance, outlines the trade‑offs such as readability, shows MySQL conversion functions, discusses IPv6 alternatives, and provides Java code for converting between string and numeric representations.

Database PerformanceJavaMySQL
0 likes · 5 min read
Why Store IPv4 Addresses as UNSIGNED INT in MySQL? Benefits, Drawbacks & Java Example
Top Architect
Top Architect
Aug 4, 2022 · Databases

Implementing Multi‑Business‑Unit Sales Statistics Queries with Row‑by‑Row Comparison in MySQL

The article explains how to query sales statistics for multiple business units and their associated products in MySQL, compares several implementation strategies such as looping, OR‑concatenation, mixed filtering, and finally adopts the SQL‑92 row‑by‑row comparison technique to achieve a single‑query solution that respects indexing and corporate coding standards.

Dynamic SQLMySQLPerformance Optimization
0 likes · 8 min read
Implementing Multi‑Business‑Unit Sales Statistics Queries with Row‑by‑Row Comparison in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 4, 2022 · Databases

Analysis of MySQL Router “Too Many Connections” Error in Versions 8.0.29/8.0.30 and Work‑around

The article investigates why MySQL Router 8.0.29/8.0.30 reports “ERROR 1040 (HY000): Too many connections to MySQL Router” even when the backend MySQL server has only a few connections, reproduces the issue, analyzes packet traces, and concludes that the problem exists in those versions but not in 8.0.28 or 8.0.21, likely due to a new connection‑pooling feature.

Connection PoolingMySQLMySQL Router
0 likes · 7 min read
Analysis of MySQL Router “Too Many Connections” Error in Versions 8.0.29/8.0.30 and Work‑around
Su San Talks Tech
Su San Talks Tech
Aug 4, 2022 · Databases

Why MySQL Unique Indexes Still Allow Duplicates and How to Fix Them

This article explains why a unique index on a MySQL InnoDB table may still permit duplicate rows, especially when indexed columns contain NULL values or when logical deletion is used, and presents practical solutions such as adjusting index fields, adding timestamps, hash columns, or redesigning delete logic.

Logical DeleteMySQLNULL handling
0 likes · 14 min read
Why MySQL Unique Indexes Still Allow Duplicates and How to Fix Them
Java High-Performance Architecture
Java High-Performance Architecture
Aug 2, 2022 · Databases

Why MySQL IN Subqueries Can Be So Slow and How to Fix Them

This article examines why a MySQL query that uses an IN subquery on a massive users table becomes extremely slow, analyzes the execution plan revealing materialized temporary tables and semi‑join optimization, and demonstrates how disabling the optimizer or rewriting the query restores index usage and dramatically improves performance.

Execution PlanIndexingMySQL
0 likes · 8 min read
Why MySQL IN Subqueries Can Be So Slow and How to Fix Them
Top Architect
Top Architect
Aug 2, 2022 · Databases

Understanding MySQL Execution Process: Connectors, Permissions, Cache, Parser, Optimizer, and Executor

This article explains the complete MySQL execution flow, covering the connector's duties, permission tables, caching behavior, parsing of SQL statements, optimizer decision‑making, executor interaction with storage engines, processlist states, logical query order, and practical tips for ordering WHERE‑clause conditions.

CacheExecution ProcessMySQL
0 likes · 11 min read
Understanding MySQL Execution Process: Connectors, Permissions, Cache, Parser, Optimizer, and Executor
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 2, 2022 · Databases

Using Generated Invisible Primary Keys (GIPK) in MySQL 8.0.30

This article explains the new MySQL 8.0.30 feature that automatically generates invisible primary keys for InnoDB tables without explicit primary keys, demonstrates how to enable and use it, discusses DDL and replication implications, and outlines related limitations and backup considerations.

GIPKInnoDBInvisible Primary Key
0 likes · 12 min read
Using Generated Invisible Primary Keys (GIPK) in MySQL 8.0.30
Aikesheng Open Source Community
Aikesheng Open Source Community
Aug 1, 2022 · Databases

MySQL SELECT Query Crash Analysis and Resolution

This article investigates a MySQL crash triggered by a specific SELECT statement, analyzes the stack trace and optimizer behavior that leads to an invalid memory access, and presents three practical solutions including disabling DuplicateWeedout, upgrading MySQL, and normalizing table character sets.

CrashDuplicateWeedoutMySQL
0 likes · 7 min read
MySQL SELECT Query Crash Analysis and Resolution
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 30, 2022 · Databases

Weekly Technical Newsletter: MySQL Releases, Linux I/O Optimization, Monitoring Tips, and SQLE Updates

This weekly newsletter curates top community technical shares covering MySQL 8.0.30 GA, master‑slave replication recovery, InnoDB parameters, Linux I/O optimization, two‑phase commit, Prometheus‑Grafana monitoring pitfalls, as well as the latest SQLE 1.2207.0 release, development progress, and upcoming plans.

Linux I/OMySQLOpen Source
0 likes · 4 min read
Weekly Technical Newsletter: MySQL Releases, Linux I/O Optimization, Monitoring Tips, and SQLE Updates
Architecture Digest
Architecture Digest
Jul 29, 2022 · Databases

Guide to Setting Up MySQL Master‑Slave Replication with MyBatis‑Plus, ShardingSphereJDBC and Spring Boot for Read‑Write Splitting

This tutorial explains how to configure MySQL master‑slave replication using Docker, set up read‑write splitting with ShardingSphereJDBC, integrate MyBatis‑Plus, and build a Spring Boot application that demonstrates write operations on the master and read operations on the slave.

Database ReplicationMySQLShardingSphere
0 likes · 16 min read
Guide to Setting Up MySQL Master‑Slave Replication with MyBatis‑Plus, ShardingSphereJDBC and Spring Boot for Read‑Write Splitting
Liangxu Linux
Liangxu Linux
Jul 28, 2022 · Databases

Why Your MySQL Queries Are Slow and How to Fix Common Mistakes

This article examines frequent MySQL performance pitfalls—such as misuse of LIMIT, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and condition push‑down failures—and demonstrates how rewriting queries with proper indexes, JOINs, early range reduction, and WITH statements can dramatically reduce execution time.

DatabaseMySQLQuery Rewrite
0 likes · 15 min read
Why Your MySQL Queries Are Slow and How to Fix Common Mistakes
Top Architect
Top Architect
Jul 28, 2022 · Databases

SQL Optimization Steps and Common Scenarios: Index Usage, EXPLAIN, Profiling, and Trace

This article explains how to identify and resolve performance bottlenecks in MySQL by locating slow queries, analyzing execution plans with EXPLAIN, focusing on type, rows and extra information, using profiling and trace tools, and applying practical optimization techniques illustrated through multiple real‑world cases.

IndexingMySQLPerformance Tuning
0 likes · 11 min read
SQL Optimization Steps and Common Scenarios: Index Usage, EXPLAIN, Profiling, and Trace
政采云技术
政采云技术
Jul 28, 2022 · Databases

Analyzing and Resolving MySQL Next‑Key Lock Deadlocks: A Practical Case Study

This article walks through a real MySQL deadlock scenario, explaining the four necessary deadlock conditions, illustrating how Next‑Key locks on non‑unique indexes cause gap locks, and presenting step‑by‑step analysis, experimental verification, and practical recommendations to avoid such deadlocks in production systems.

DatabaseDeadlockMySQL
0 likes · 15 min read
Analyzing and Resolving MySQL Next‑Key Lock Deadlocks: A Practical Case Study
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 28, 2022 · Databases

Understanding Dble Startup Configuration Validation and Common Failure Cases (Version 3.22.01.0)

This article explains how Dble validates its configuration files during startup, lists the main configuration file types, and walks through typical startup failure examples—such as port conflicts, syntax errors, and backend MySQL connectivity issues—providing step‑by‑step troubleshooting guidance for new users.

ConfigurationDatabase MiddlewareMySQL
0 likes · 9 min read
Understanding Dble Startup Configuration Validation and Common Failure Cases (Version 3.22.01.0)
dbaplus Community
dbaplus Community
Jul 26, 2022 · Databases

Master MySQL Data Archiving with pt-archiver: Install, Run, and Optimize

This guide explains how to use Percona Toolkit's pt-archiver for MySQL data archiving, covering installation, basic and bulk commands, underlying two‑phase‑commit logic, performance trade‑offs of various parameters, and best‑practice tips for safe, efficient archiving and deletion.

Data ArchivingMySQLReplication Lag
0 likes · 19 min read
Master MySQL Data Archiving with pt-archiver: Install, Run, and Optimize
macrozheng
macrozheng
Jul 23, 2022 · Databases

July 2023 DB-Engines Ranking: Oracle Drops, MySQL & SQL Server Rise

The July 2023 DB-Engines ranking shows Oracle, PostgreSQL and MongoDB slipping, while MySQL and Microsoft SQL Server climb, and provides detailed scores, trend charts, and the methodology behind the monthly popularity scores for the top ten database systems.

DB-EnginesMySQLOracle
0 likes · 4 min read
July 2023 DB-Engines Ranking: Oracle Drops, MySQL & SQL Server Rise
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 22, 2022 · Databases

SQLE 1.2207.0-pre2 Release Notes: New IDE Plugin, Incremental Smart Scan, and Full Feature List

The SQLE 1.2207.0-pre2 preview release introduces a JetBrains IDE audit plugin, supports incremental smart scanning for slow logs and TiDB audit logs, provides updated dashboards, optimizations, bug fixes, and includes detailed deployment links and a complete list of new features and improvements.

Database AuditingIDE pluginIncremental Scan
0 likes · 6 min read
SQLE 1.2207.0-pre2 Release Notes: New IDE Plugin, Incremental Smart Scan, and Full Feature List
Top Architect
Top Architect
Jul 22, 2022 · Databases

Why and How to Optimize Slow SQL Queries in MySQL

The article explains the impact of slow SQL on system resources, outlines priorities for addressing performance bottlenecks, describes MySQL's execution process, shows how to enable and analyze slow‑query logs, and provides practical SQL optimization techniques such as avoiding subqueries, using IN, and eliminating unnecessary ORDER BY clauses.

Database PerformanceMySQLSQL optimization
0 likes · 10 min read
Why and How to Optimize Slow SQL Queries in MySQL
Wukong Talks Architecture
Wukong Talks Architecture
Jul 21, 2022 · Databases

Understanding MySQL MVCC Mechanism

This article explains MySQL's multi-version concurrency control (MVCC), describing how version chains, undo logs, and ReadView work together to implement isolation levels, prevent dirty reads, non‑repeatable reads, and phantom reads, and how RC and RR isolation differ in practice.

Database ConcurrencyIsolation LevelsMVCC
0 likes · 8 min read
Understanding MySQL MVCC Mechanism
Efficient Ops
Efficient Ops
Jul 20, 2022 · Databases

How Indexes Can Speed Up MySQL Queries by Tens of Thousands of Times

This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.

MySQLSQLdatabase tuning
0 likes · 12 min read
How Indexes Can Speed Up MySQL Queries by Tens of Thousands of Times
Programmer DD
Programmer DD
Jul 20, 2022 · Databases

Mastering MySQL Sharding: Strategies, ID Generation, and Seamless Scaling

This article explains why and how to apply database sharding, introduces key terminology, compares global ID generation methods such as auto‑increment, UUID, COMB and Snowflake, outlines sharding algorithms, discusses challenges like distributed transactions, and presents practical expansion and implementation solutions.

Database ScalingMySQLid generation
0 likes · 12 min read
Mastering MySQL Sharding: Strategies, ID Generation, and Seamless Scaling
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 20, 2022 · Databases

Using ProxySQL Binlog Reader to Monitor MySQL GTID Replication

This article introduces the ProxySQL Binlog Reader component, explains its background in read/write splitting and GTID‑based consistency, demonstrates how to configure MySQL servers, create a dedicated user, start the reader on master and replica, and verify its operation through logs and session information.

Binlog ReaderGTIDMySQL
0 likes · 9 min read
Using ProxySQL Binlog Reader to Monitor MySQL GTID Replication
Selected Java Interview Questions
Selected Java Interview Questions
Jul 19, 2022 · Backend Development

Comprehensive Guide to Identifying and Solving Backend Interface Performance Issues

This article recounts a backend team's experience of addressing numerous slow API endpoints, detailing root causes such as MySQL slow queries, complex business logic, thread‑pool and lock misconfigurations, and offers practical solutions including pagination fixes, indexing, query refactoring, concurrency improvements, and caching strategies.

MySQLOptimizationPerformance
0 likes · 16 min read
Comprehensive Guide to Identifying and Solving Backend Interface Performance Issues
Liangxu Linux
Liangxu Linux
Jul 19, 2022 · Databases

Master MySQL Stored Procedures, Events, and Triggers for Automated Data Workflows

This guide explains how to use MySQL stored procedures, events (timers), and triggers—including their creation, variable handling, control structures, parameter passing, loops, scheduling options, and practical examples—to automate reporting, data monitoring, and business logic directly at the database layer.

Database AutomationEvent SchedulerMySQL
0 likes · 11 min read
Master MySQL Stored Procedures, Events, and Triggers for Automated Data Workflows
IT Services Circle
IT Services Circle
Jul 18, 2022 · Databases

Migrating MySQL Dual-Master High Availability to Master‑Slave Architecture: Lessons Learned and Simple Conversion Steps

After a month of testing a MySQL dual‑master high‑availability setup, the author details the numerous pitfalls encountered—including primary key collisions, sync failures, and data inconsistencies—and explains why they switched to a simpler master‑slave configuration, providing step‑by‑step instructions for the conversion.

Database ReplicationKeepalivedMaster‑Slave
0 likes · 8 min read
Migrating MySQL Dual-Master High Availability to Master‑Slave Architecture: Lessons Learned and Simple Conversion Steps
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 18, 2022 · Databases

Analysis of Commit Blocking and SHOW MASTER STATUS Contention in MySQL 5.7 Replication

The article examines how a large transaction in MySQL 5.7 can block the SHOW MASTER STATUS command by holding the lock_log during the commit's flush stage, analyzes the commit and SHOW MASTER STATUS code paths, and proposes mitigation strategies such as avoiding big transactions and using SELECT @@global.gtid_executed.

CommitMySQLPerformance
0 likes · 7 min read
Analysis of Commit Blocking and SHOW MASTER STATUS Contention in MySQL 5.7 Replication
Architecture Digest
Architecture Digest
Jul 18, 2022 · Databases

Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It

The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.

Execution PlanIndexMySQL
0 likes · 8 min read
Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It
FunTester
FunTester
Jul 18, 2022 · Databases

Why MySQL’s MysqlConnectionPoolDataSource Isn’t a Real Connection Pool (And How to Use It)

The article examines the MySQL class com.mysql.cj.jdbc.MysqlConnectionPoolDataSource, revealing that despite its name it creates new connections each request and only recycles idle ones, provides a practical Groovy example, highlights pitfalls such as duplicate setURL methods, and concludes it’s unsuitable as a true pool for testing.

DatabaseGroovyJava
0 likes · 5 min read
Why MySQL’s MysqlConnectionPoolDataSource Isn’t a Real Connection Pool (And How to Use It)
Top Architect
Top Architect
Jul 16, 2022 · Databases

Understanding MySQL Locks: Table, Row, and Gap Locks in InnoDB and MyISAM

This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.

InnoDBLocksMyISAM
0 likes · 9 min read
Understanding MySQL Locks: Table, Row, and Gap Locks in InnoDB and MyISAM
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 15, 2022 · Databases

SQLE 1.2207.0‑pre1 Release Notes: New OceanBase MySQL Support, Feature Updates, and Bug Fixes

The SQLE 1.2207.0‑pre1 pre‑release introduces OceanBase MySQL‑mode audit plugins, exclusive OceanBase rules, enhanced table‑name case checks, count(*) recommendations, adds TiDB audit‑log support, fixes several MySQL‑related bugs, and outlines future plans for more OceanBase and Oracle‑mode auditing capabilities.

Database AuditingMySQLOceanBase
0 likes · 5 min read
SQLE 1.2207.0‑pre1 Release Notes: New OceanBase MySQL Support, Feature Updates, and Bug Fixes
Programmer DD
Programmer DD
Jul 15, 2022 · Databases

Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them

This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.

MySQLPerformance TuningQuery Rewriting
0 likes · 16 min read
Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 14, 2022 · Backend Development

Introducing and Evaluating the DBLE Split Command for Accelerated Data Import

This article explains the DBLE split feature that partitions large mysqldump files according to sharding configuration, provides command syntax and usage examples, and presents a performance test showing that split‑based import can be up to 18 times faster than direct DBLE import while preserving data correctness.

DBLEMySQLdata import
0 likes · 12 min read
Introducing and Evaluating the DBLE Split Command for Accelerated Data Import
Programmer DD
Programmer DD
Jul 14, 2022 · Databases

Why MySQL Joins Lag Behind PostgreSQL and How to Optimize Multi‑Table Queries

This article examines MySQL’s limited join capabilities compared to PostgreSQL, explains why multi‑table queries over three tables can be inefficient, and explores strategies such as service‑layer joins, query decomposition, and caching to improve performance and scalability in database‑centric applications.

MySQLQuery DecompositionService Layer
0 likes · 9 min read
Why MySQL Joins Lag Behind PostgreSQL and How to Optimize Multi‑Table Queries
Programmer DD
Programmer DD
Jul 14, 2022 · Big Data

Master Fast Data Synchronization with Alibaba DataX: A Step‑by‑Step Guide

This article explains why traditional mysqldump and file‑based methods struggle with massive tables, introduces Alibaba DataX as a high‑performance offline data integration tool, details its architecture, and provides comprehensive installation and configuration steps for full and incremental MySQL‑to‑MySQL synchronization using JSON job files.

Big DataDataXETL
0 likes · 15 min read
Master Fast Data Synchronization with Alibaba DataX: A Step‑by‑Step Guide
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 13, 2022 · Databases

Bypassing GTID Restrictions on CREATE TABLE … SELECT and Temporary Tables in MySQL 5.6/5.7 and Their Removal in MySQL 8.0

The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.

DDLGTIDMySQL
0 likes · 12 min read
Bypassing GTID Restrictions on CREATE TABLE … SELECT and Temporary Tables in MySQL 5.6/5.7 and Their Removal in MySQL 8.0
dbaplus Community
dbaplus Community
Jul 11, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them

This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.

MySQLSlow Querybuffer pool
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them
Wukong Talks Architecture
Wukong Talks Architecture
Jul 11, 2022 · Databases

Migrating from MySQL Dual-Master to Master‑Slave: Lessons Learned and Simple Conversion Steps

After a month of operating a MySQL dual‑master high‑availability cluster, the author details the numerous pitfalls encountered and explains a step‑by‑step manual process for converting the setup to a simpler master‑slave architecture, including configuration changes and Keepalived adjustments.

Database MigrationDual MasterKeepalived
0 likes · 9 min read
Migrating from MySQL Dual-Master to Master‑Slave: Lessons Learned and Simple Conversion Steps
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 11, 2022 · Databases

Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist

This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.

Database PerformanceMySQLSHOW PROCESSLIST
0 likes · 6 min read
Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist
Liangxu Linux
Liangxu Linux
Jul 9, 2022 · Databases

How Indexes Transform MySQL Subquery Performance: A Deep Dive

This article walks through a MySQL 5.6 scenario where a subquery that originally took over eight hours is accelerated by orders of magnitude through strategic index creation, query rewriting, and join optimization, illustrating the impact of single‑column, composite, and covering indexes on execution plans.

JOINMySQLPerformance Tuning
0 likes · 13 min read
How Indexes Transform MySQL Subquery Performance: A Deep Dive
Programmer DD
Programmer DD
Jul 8, 2022 · Databases

Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive

This article investigates MySQL’s recommendation against UUIDs, comparing auto‑increment, UUID, and Snowflake‑style random keys through insertion and query benchmarks, analyzing their index structures, performance impacts, and trade‑offs, and concludes why sequential primary keys generally outperform non‑sequential identifiers.

DatabaseIndexMySQL
0 likes · 12 min read
Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive
Top Architect
Top Architect
Jul 7, 2022 · Databases

Understanding InnoDB Logical Storage Structure and B+Tree Indexes in MySQL

The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.

B+TreeDatabaseIndex
0 likes · 10 min read
Understanding InnoDB Logical Storage Structure and B+Tree Indexes in MySQL
IT Services Circle
IT Services Circle
Jul 6, 2022 · Databases

Understanding MySQL COUNT() Performance and Strategies for Large Tables

This article explains how MySQL COUNT() works under different storage engines, why counting rows becomes slow on large InnoDB tables, and presents practical methods such as using EXPLAIN rows, auxiliary count tables, batch processing, and transaction‑based updates to obtain approximate or exact row counts efficiently.

COUNTDatabaseInnoDB
0 likes · 12 min read
Understanding MySQL COUNT() Performance and Strategies for Large Tables
Java Captain
Java Captain
Jul 5, 2022 · Frontend Development

Music Website Project – Full‑Stack Implementation with Vue 3 and Spring Boot

This article introduces a music website built with Vue 3 on the client side and Spring Boot + MyBatis on the server side, detailing its features, technology stack, development environment, and step‑by‑step instructions for downloading, configuring, and running the full‑stack application.

Full-StackMySQLSpring Boot
0 likes · 7 min read
Music Website Project – Full‑Stack Implementation with Vue 3 and Spring Boot
dbaplus Community
dbaplus Community
Jul 4, 2022 · Databases

Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up

The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.

InnoDBLIMITMySQL
0 likes · 9 min read
Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up
Liangxu Linux
Liangxu Linux
Jul 3, 2022 · Databases

What’s the Optimal Batch Size for MySQL Inserts? A Deep Performance Test

This article investigates how many rows should be inserted per batch in MySQL by measuring the impact of packet size limits, buffer pool usage, insert buffers, transaction handling and index structures, and it provides practical recommendations based on tests with millions of rows.

Batch InsertMySQLbuffer pool
0 likes · 16 min read
What’s the Optimal Batch Size for MySQL Inserts? A Deep Performance Test
dbaplus Community
dbaplus Community
Jul 3, 2022 · Databases

Master MySQL Clone Plugin: Installation, Usage, and Optimization

This guide explains MySQL 8.0.17's Clone Plugin—how to install it, perform local and remote clones, monitor progress via performance_schema, build replicas from cloned data, understand its internal stages, configure parameters, and compare it with XtraBackup.

Clone PluginDatabase ReplicationInnoDB
0 likes · 19 min read
Master MySQL Clone Plugin: Installation, Usage, and Optimization
Programmer DD
Programmer DD
Jul 3, 2022 · Databases

What Happens When MySQL Auto‑Increment IDs Hit Their Limit?

This article explains how MySQL’s various auto‑increment identifiers—table AUTO_INCREMENT, InnoDB row_id, Xid, trx_id, and thread_id—behave when they reach their maximum values, the resulting errors or data overwrites, and the underlying mechanisms that cause these edge‑case behaviors.

InnoDBMySQLXid
0 likes · 13 min read
What Happens When MySQL Auto‑Increment IDs Hit Their Limit?
Shepherd Advanced Notes
Shepherd Advanced Notes
Jul 1, 2022 · Backend Development

How to Parse Dynamic SQL with Druid: A Practical Guide

This article explains how to use Alibaba's Druid SQL parser to extract and replace dynamic @var parameters in complex SQL statements, detailing the AST structure, visitor pattern, and a complete Java implementation with example code and results.

DruidDynamic SQLJava
0 likes · 13 min read
How to Parse Dynamic SQL with Druid: A Practical Guide
Open Source Linux
Open Source Linux
Jul 1, 2022 · Databases

Understanding MySQL Buffers, Cache, and Zero‑Copy I/O: A Deep Dive

This article explains the differences between buffers and caches, explores MySQL’s buffer pool architecture, details write‑through/write‑back strategies, and reviews key InnoDB parameters such as innodb_flush_log_at_trx_commit and innodb_flush_method for optimizing data durability and performance.

CacheInnoDBMySQL
0 likes · 12 min read
Understanding MySQL Buffers, Cache, and Zero‑Copy I/O: A Deep Dive
Top Architect
Top Architect
Jun 30, 2022 · Databases

Database Sharding and Partitioning Strategies with Practical Implementation

This article explains why high‑traffic systems need database sharding, compares table‑level and database‑level partitioning methods, presents concrete MySQL table‑creation scripts and Java service code, and discusses the advantages, drawbacks, and common middleware solutions such as Sharding‑JDBC and Mycat.

MySQLMycatSharding-JDBC
0 likes · 12 min read
Database Sharding and Partitioning Strategies with Practical Implementation
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 30, 2022 · Databases

Handling Replication Anomalies in MySQL Slave IO Thread

This article analyzes MySQL replication anomalies caused by master failures or network interruptions that lead to incomplete transaction replay on slaves, demonstrates a reproducible experiment using network delay and iptables, and provides practical guidance for both recovering and permanently handling stalled slave IO threads.

DatabaseGTIDMySQL
0 likes · 6 min read
Handling Replication Anomalies in MySQL Slave IO Thread
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 29, 2022 · Databases

Understanding Multi-Column Partition Tables in MySQL: Implementation, Performance Comparison, and Best Practices

This article explains MySQL's support for multi‑column partition tables, provides a stored‑procedure to create them, compares performance against single‑column partitions under various query conditions, discusses partition column order impact, and offers practical guidance for when and how to use multi‑column partitions.

DatabaseMulti-ColumnMySQL
0 likes · 10 min read
Understanding Multi-Column Partition Tables in MySQL: Implementation, Performance Comparison, and Best Practices
Open Source Linux
Open Source Linux
Jun 29, 2022 · Databases

Top MySQL Interview Questions & Answers Every DBA Should Know

This article compiles essential MySQL interview questions covering primary and candidate keys, date functions, trigger limits, index column counts, data copying techniques, core MySQL concepts, feature highlights, transaction ACID properties, heap versus clustered tables, numeric type differences, and objects creatable with CREATE statements.

DatabaseHeap TableInterview Questions
0 likes · 9 min read
Top MySQL Interview Questions & Answers Every DBA Should Know
MaGe Linux Operations
MaGe Linux Operations
Jun 28, 2022 · Databases

How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive

This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.

DatabaseMySQLSQL
0 likes · 11 min read
How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 28, 2022 · Databases

Quick Guide to Enabling Read‑Write Splitting in Dble (v3.20.10+)

This article explains how to configure Dble's read‑write splitting feature by preparing MySQL master‑slave instances, defining dbGroup and rwSplitUser in XML configuration files, reloading the settings, and verifying that write statements go to the primary node while reads are load‑balanced across replicas.

ConfigurationDatabase MiddlewareMySQL
0 likes · 6 min read
Quick Guide to Enabling Read‑Write Splitting in Dble (v3.20.10+)
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 27, 2022 · Databases

SQLE 1.2206.0-pre2 Release Notes: New Features, Full Release Information, and Future Plans

The preview release SQLE 1.2206.0-pre2 introduces enterprise‑level SQL drill‑down analysis, a refreshed login page, detailed release notes with new features and bug fixes, and outlines future plans to support more database types, while providing links to documentation, demos, and previous versions.

Bug FixesDatabase AuditingMySQL
0 likes · 4 min read
SQLE 1.2206.0-pre2 Release Notes: New Features, Full Release Information, and Future Plans
FunTester
FunTester
Jun 27, 2022 · Backend Development

Implementing a Custom MySQL Connection Pool with Apache Commons Pool2 in Java

The article explains how to build a reusable MySQL connection pool in Java using Apache Commons Pool2, detailing the creation of a poolable object, a factory class, the pool configuration, and a simple API wrapper for borrowing and returning connections while providing full source code examples.

JavaMySQLcommons-pool2
0 likes · 7 min read
Implementing a Custom MySQL Connection Pool with Apache Commons Pool2 in Java
dbaplus Community
dbaplus Community
Jun 26, 2022 · Information Security

How to Build a Lightweight MySQL/MariaDB Audit Log Platform with LogAnalyzer

This guide explains how to set up a lightweight database audit log platform using MariaDB's audit plugin, Rsyslog, and the LogAnalyzer web interface, covering environment preparation, plugin installation, syslog forwarding, MySQL schema creation, and LogAnalyzer configuration for real‑time monitoring and compliance reporting.

AuditDatabase SecurityLogAnalyzer
0 likes · 11 min read
How to Build a Lightweight MySQL/MariaDB Audit Log Platform with LogAnalyzer
IT Services Circle
IT Services Circle
Jun 25, 2022 · Databases

Understanding Optimistic and Pessimistic Locks in MySQL

Optimistic and pessimistic locks are conceptual concurrency control strategies used across databases and caching systems; the article explains their principles, MySQL implementations with SELECT ... FOR UPDATE and version checks, compares their trade‑offs, and advises preferring optimistic locking in high‑concurrency scenarios.

MySQLconcurrency controloptimistic lock
0 likes · 6 min read
Understanding Optimistic and Pessimistic Locks in MySQL
Architecture & Thinking
Architecture & Thinking
Jun 25, 2022 · Databases

Why and How to Split Databases: Vertical & Horizontal Sharding Explained

This article explains the reasons for database sharding, compares vertical (scale‑up) and horizontal (scale‑out) partitioning methods, illustrates vertical database/table splits and horizontal partition strategies such as HASH, RANGE, LIST, KEY and composite, and discusses the challenges like distributed transactions, cross‑database joins and increased management overhead.

Horizontal PartitioningMySQLVertical Partitioning
0 likes · 14 min read
Why and How to Split Databases: Vertical & Horizontal Sharding Explained
ITPUB
ITPUB
Jun 24, 2022 · Databases

How to Build MySQL Master‑Master HA with Keepalived: A Step‑by‑Step Guide

This article walks through the complete process of creating a highly available MySQL master‑master cluster using Docker containers, MySQL replication, and Keepalived for automatic health checks, virtual IP failover, and seamless traffic switching, while also sharing common pitfalls and their solutions.

DockerLinuxMaster-Master Replication
0 likes · 24 min read
How to Build MySQL Master‑Master HA with Keepalived: A Step‑by‑Step Guide
php Courses
php Courses
Jun 24, 2022 · Databases

Comprehensive List of MySQL Interview Questions

This article compiles 66 MySQL interview questions covering fundamentals such as joins, data types, indexes, storage engines, transaction properties, locking mechanisms, replication, sharding, performance tuning, and high‑availability concepts for interview preparation.

DatabaseIndexingMySQL
0 likes · 3 min read
Comprehensive List of MySQL Interview Questions
dbaplus Community
dbaplus Community
Jun 23, 2022 · Databases

How to Detect and Resolve Blocked DDL Operations in MySQL

Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.

DDLMetadata LockMySQL
0 likes · 9 min read
How to Detect and Resolve Blocked DDL Operations in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 23, 2022 · Information Security

SSL Overview and DBLE SSL Configuration Guide

This article introduces the SSL protocol, explains why encryption is needed for database communication, and provides step‑by‑step instructions for generating self‑signed certificates with OpenSSL, converting them for Java, and configuring both DBLE server and client to use SSL, including experimental verification of encrypted versus unencrypted connections.

DBLEEncryptionJava
0 likes · 15 min read
SSL Overview and DBLE SSL Configuration Guide