BestHub
Sign in
Tagged articles
5000 articles
Page 12 of 50
macrozheng
macrozheng
Nov 7, 2024 · Backend Development

9 Proven Techniques to Supercharge Pagination Query Performance

This article presents nine practical strategies—including adding default filters, limiting page size, reducing joins, optimizing indexes, using straight_join, archiving data, leveraging count(*), querying ClickHouse, and implementing read‑write splitting—to dramatically improve the speed and scalability of pagination APIs in MySQL‑based back‑ends.

Database PerformanceSQL Optimizationclickhouse
0 likes · 11 min read
9 Proven Techniques to Supercharge Pagination Query Performance
Tencent Cloud Developer
Tencent Cloud Developer
Nov 7, 2024 · Backend Development

Cache Consistency Strategies and Best Practices for the Cache‑Aside Pattern

The article explains cache‑aside consistency challenges and compares four update strategies—DB‑then‑cache, cache‑then‑DB, DB‑then‑delete, and delete‑then‑DB—showing that deleting the cache after a successful DB write offers the smallest inconsistency window, while recommending TTLs, message‑queue invalidation, and multi‑key coordination for robust eventual consistency.

Cache ConsistencyDistributed Systemscache-aside
0 likes · 20 min read
Cache Consistency Strategies and Best Practices for the Cache‑Aside Pattern
Architect
Architect
Nov 6, 2024 · Databases

Storing IPv4 as Unsigned Int in MySQL: Benefits, Drawbacks & Code

Using an unsigned INT to store IPv4 addresses in MySQL saves space and enables efficient range queries, while strings are larger and slower; the article explains these advantages, outlines conversion functions INET_ATON/INET_NTOA, shows equivalent handling for IPv6, and provides Java utilities for bidirectional conversion.

IPv4data storagejava
0 likes · 6 min read
Storing IPv4 as Unsigned Int in MySQL: Benefits, Drawbacks & Code
Lobster Programming
Lobster Programming
Nov 6, 2024 · Cloud Native

Master Docker: From Basics to Running MySQL in Containers

This guide introduces Docker fundamentals, explains how containers differ from virtual machines, walks through installing Docker on Linux, and demonstrates practical steps to pull and run a MySQL container, while also providing a handy reference of common Docker commands for managing containers and images.

ContainersDevOpsDocker
0 likes · 6 min read
Master Docker: From Basics to Running MySQL in Containers
Java Tech Enthusiast
Java Tech Enthusiast
Nov 6, 2024 · Backend Development

Java Interview Insights: Spring Boot Startup, Spring MVC Flow, MySQL Indexes, and Redis Performance

The article recounts a recent Hikvision interview, outlining campus salary ranges and providing a technical Q&A that explains Spring Boot’s startup sequence, Spring MVC request handling, MySQL index structures, creation rules, and performance diagnostics, as well as Redis’s in‑memory, single‑threaded architecture and optional I/O threading.

BackendSpringBootinterview
0 likes · 20 min read
Java Interview Insights: Spring Boot Startup, Spring MVC Flow, MySQL Indexes, and Redis Performance
Code Ape Tech Column
Code Ape Tech Column
Nov 6, 2024 · Backend Development

Implementing a Snapchat-like Self-Destructing Image Feature with Spring Boot and MySQL

This article presents a step‑by‑step guide on building a Snapchat‑style “burn after reading” image sharing system using Spring Boot, MySQL, Thymeleaf, and optional cloud storage, covering background analysis, architecture design, environment setup, code implementation, optimization, testing, and deployment.

Self-Destructing ImagesSpring Bootfile upload
0 likes · 19 min read
Implementing a Snapchat-like Self-Destructing Image Feature with Spring Boot and MySQL
Su San Talks Tech
Su San Talks Tech
Nov 6, 2024 · Databases

Why MySQL’s Case‑Insensitive Collation Returns Unexpected Duplicate Brands (and How to Fix It)

This article examines a puzzling MySQL case‑insensitive collation issue where a user‑defined brand "yoyo" cannot be saved because the database returns an existing uppercase "YOYO" entry, explains the underlying COLLATE settings, and proposes practical solutions to prevent duplicate brand data.

Brand ManagementUnique Indexcase-insensitive
0 likes · 8 min read
Why MySQL’s Case‑Insensitive Collation Returns Unexpected Duplicate Brands (and How to Fix It)
php Courses
php Courses
Nov 5, 2024 · Backend Development

Generating Visual Statistical Charts with PHP and ECharts

This article demonstrates how to use a PHP interface to retrieve data from a MySQL database and employ the ECharts library to create dynamic visual statistical charts, guiding developers through preparation, data extraction, and chart generation steps, including code examples for a bar chart.

Data visualizationEChartsPHP
0 likes · 7 min read
Generating Visual Statistical Charts with PHP and ECharts
macrozheng
macrozheng
Nov 5, 2024 · Backend Development

Mastering Global Unique ID Generation: 7 Proven Strategies for Distributed Systems

This article explores seven practical approaches to generating globally unique identifiers in distributed environments, covering database‑level configurations, MySQL auto‑increment tweaks, MyCat + Zookeeper integration, UUID limitations, Snowflake algorithm implementation, Meituan's Leaf service, Redis counters, and Zookeeper sequential nodes, providing code snippets and configuration guidance.

ZooKeeperdistributed-idglobal ID
0 likes · 18 min read
Mastering Global Unique ID Generation: 7 Proven Strategies for Distributed Systems
Java Tech Enthusiast
Java Tech Enthusiast
Nov 3, 2024 · Backend Development

Explore WeBlog: A Spring Boot & Vue 3.2 Full‑Stack Blog Platform

WeBlog is a Spring Boot and Vue 3.2 based full‑stack blog that combines a MySQL‑backed backend with JWT authentication, MinIO storage, and a hand‑crafted Element Plus admin UI, offering markdown publishing, analytics dashboards, and open‑source repository links for developers to explore.

Element PlusFull-Stack BlogJWT
0 likes · 6 min read
Explore WeBlog: A Spring Boot & Vue 3.2 Full‑Stack Blog Platform
ITPUB
ITPUB
Nov 3, 2024 · Databases

MySQL vs PostgreSQL: Performance, Use Cases, and Choosing the Right Database

This article compares MySQL and PostgreSQL, outlining their histories, architectural differences, benchmark results for SELECT, INSERT, UPDATE operations, and discusses suitable scenarios, advantages, disadvantages, and guidance on selecting the appropriate database for various application needs.

Use Casesdatabase comparisonmysql
0 likes · 8 min read
MySQL vs PostgreSQL: Performance, Use Cases, and Choosing the Right Database
Java Tech Enthusiast
Java Tech Enthusiast
Nov 1, 2024 · Databases

Quick MySQL Configuration and Monitoring Queries

This guide presents essential MySQL configuration and monitoring queries—covering connection limits, Binlog/GTID status, InnoDB settings—plus a one‑click script that consolidates these checks, enabling quick health assessments and more efficient routine inspections of MySQL servers.

databasemonitoringmysql
0 likes · 2 min read
Quick MySQL Configuration and Monitoring Queries
Architect's Guide
Architect's Guide
Oct 31, 2024 · Databases

Designing an Efficient Pipeline for Importing One Billion Records into MySQL

This article presents a comprehensive engineering guide for importing one billion 1 KB unstructured log records stored in HDFS or S3 into MySQL, covering data sizing, B‑tree limits, batch insertion strategies, storage‑engine choices, sharding, file‑reading techniques, concurrency control, and reliable task coordination using Redis, Redisson, and Zookeeper.

Batch InsertZooKeeperdata sharding
0 likes · 17 min read
Designing an Efficient Pipeline for Importing One Billion Records into MySQL
21CTO
21CTO
Oct 30, 2024 · Cloud Native

How to Run MySQL, PostgreSQL & MongoDB on Kubernetes: A Step‑by‑Step Guide

Learn how to deploy stateful databases such as MySQL, PostgreSQL, and MongoDB on Kubernetes by mastering StatefulSets, PersistentVolumes, PVCs, StorageClasses, and related best practices for storage, scaling, backup, monitoring, and disaster recovery.

KubernetesPersistentVolumesStatefulSets
0 likes · 9 min read
How to Run MySQL, PostgreSQL & MongoDB on Kubernetes: A Step‑by‑Step Guide
dbaplus Community
dbaplus Community
Oct 29, 2024 · Databases

Why UUID v7 Beats UUID v4 as a Clustered Index – Performance Test Results

This article compares UUID v4 and UUID v7 as clustered index keys, explains their structures, lists pros and cons, describes a Docker‑based MySQL experiment using Node.js and Go, presents single‑ and multi‑thread insertion timings, and analyses why the time‑ordered UUID v7 (and serial IDs) outperform random UUID v4.

Clustered IndexDockerGo
0 likes · 14 min read
Why UUID v7 Beats UUID v4 as a Clustered Index – Performance Test Results
Architect
Architect
Oct 29, 2024 · Databases

Four Pagination Strategies: MySQL LIMIT OFFSET, Primary‑Key Filtering, HasMore Scrolling, and Elasticsearch

This article compares four pagination techniques—MySQL LIMIT OFFSET, LIMIT with primary‑key filtering, HasMore scrolling, and Elasticsearch pagination—explaining their implementations, advantages, drawbacks such as deep‑page performance issues, and recommending appropriate scenarios for each method.

Elasticsearchmysqlpagination
0 likes · 6 min read
Four Pagination Strategies: MySQL LIMIT OFFSET, Primary‑Key Filtering, HasMore Scrolling, and Elasticsearch
Rare Earth Juejin Tech Community
Rare Earth Juejin Tech Community
Oct 29, 2024 · Backend Development

Storing Douyin and Baidu Hot Search Data with MySQL, MyBatis Generator, and Java Crawlers

This tutorial explains how to design a MySQL table for hot‑search records, generate Java entity and mapper classes using MyBatis Generator, create unique IDs for each entry, and implement scheduled Java crawlers for Douyin and Baidu hot‑search data that persist the results via Spring‑Boot services.

BackendDatabase designMyBatis
0 likes · 19 min read
Storing Douyin and Baidu Hot Search Data with MySQL, MyBatis Generator, and Java Crawlers
JavaEdge
JavaEdge
Oct 28, 2024 · Databases

Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks

This article explains how MySQL InnoDB deadlocks occur during order‑record idempotency checks, demonstrates step‑by‑step reproduction with SQL scripts, analyzes the lock types involved, and provides practical strategies to avoid and resolve such deadlocks in production systems.

InnoDBdatabasedeadlock
0 likes · 13 min read
Why Do MySQL Transactions Deadlock? Reproduce and Prevent InnoDB Deadlocks
ITPUB
ITPUB
Oct 28, 2024 · Databases

Why a MySQL Table Can Hold 100 Million Rows Without Slowing Down

This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+‑tree fan‑out, showing that a table with 100 million rows can still query quickly when the index depth remains low.

B+TreeRow Limitindex
0 likes · 14 min read
Why a MySQL Table Can Hold 100 Million Rows Without Slowing Down
Top Architect
Top Architect
Oct 28, 2024 · Databases

Understanding Database Insert Operations and Batch Insertion Strategies

This article explains how databases handle insert operations, compares single‑row and batch inserts, discusses factors such as cache‑to‑disk synchronization, transaction logs, page size, hardware limits, and provides practical MyBatis examples for optimizing bulk data loading.

batch insertionmysqlperformance
0 likes · 15 min read
Understanding Database Insert Operations and Batch Insertion Strategies
dbaplus Community
dbaplus Community
Oct 27, 2024 · Databases

Master Multi‑DB Backup: Oracle, MySQL, PostgreSQL & openGauss Shell Scripts

This guide provides step‑by‑step shell scripts, directory setups, cron jobs, and cleanup commands for reliable backup and restore of Oracle (RMAN and logical), MySQL (full, incremental, Percona XtraBackup), PostgreSQL (logical and physical with pg_rman), and openGauss databases, including retention policies and automation tips.

BackupDatabase AdministrationOracle
0 likes · 19 min read
Master Multi‑DB Backup: Oracle, MySQL, PostgreSQL & openGauss Shell Scripts
Selected Java Interview Questions
Selected Java Interview Questions
Oct 26, 2024 · Databases

Handling MySQL Auto Increment ID Exhaustion: Strategies and Solutions

This article explains why MySQL auto‑increment IDs can run out in large or special‑case tables and presents six practical solutions—including changing the column type to BIGINT, using UUIDs, segmenting IDs, composite keys, adjusting auto‑increment steps, and sharding databases—to ensure scalable and unique primary keys.

ID Exhaustionauto_incrementmysql
0 likes · 6 min read
Handling MySQL Auto Increment ID Exhaustion: Strategies and Solutions
php Courses
php Courses
Oct 24, 2024 · Backend Development

Using PHP mysqli_query to Execute MySQL Queries

This article explains how to use PHP's mysqli_query function to perform MySQL SELECT, INSERT, UPDATE, and DELETE operations, provides a complete example script with connection handling, query execution, result processing, and highlights related functions for fetching data.

PHPdatabasemysql
0 likes · 4 min read
Using PHP mysqli_query to Execute MySQL Queries
Java Architect Essentials
Java Architect Essentials
Oct 23, 2024 · Databases

Does Varchar Length Really Affect MySQL Storage and Performance?

This article investigates whether MySQL varchar column length influences storage size and query performance by creating two tables with varchar(50) and varchar(500), inserting one million rows, measuring disk usage, and benchmarking various queries, ultimately revealing that storage is identical while longer columns can degrade performance during sorted scans due to increased temporary file usage.

Database designindexmysql
0 likes · 10 min read
Does Varchar Length Really Affect MySQL Storage and Performance?
php Courses
php Courses
Oct 22, 2024 · Backend Development

Using PHP mysqli_query to Execute MySQL Queries

This article explains how to use PHP's mysqli_query function to connect to a MySQL database, execute SELECT queries, handle result sets, and perform other operations such as INSERT, UPDATE, and DELETE, including a complete example code snippet and best practices for error handling.

PHPdatabasemysql
0 likes · 5 min read
Using PHP mysqli_query to Execute MySQL Queries
Architect's Guide
Architect's Guide
Oct 22, 2024 · Backend Development

Design and Implementation of a Business Middle Platform Using SpringBoot, Kong API Gateway, and Kubernetes

This article details the end‑to‑end architecture of a high‑traffic business middle platform, covering business and technical design, Kong API Gateway, SpringBoot microservices, Dockerfile creation, MyCat‑MySQL sharding, cold/hot data strategies, DevOps pipelines, Kubernetes deployment, and operational monitoring.

DevOpsDockerKong
0 likes · 18 min read
Design and Implementation of a Business Middle Platform Using SpringBoot, Kong API Gateway, and Kubernetes
Su San Talks Tech
Su San Talks Tech
Oct 21, 2024 · Databases

How to Diagnose and Fix MySQL Master‑Slave Replication Lag

This article explains why MySQL master‑slave replication can become delayed in high‑traffic order systems, describes the underlying binlog synchronization mechanism, and provides practical steps—including network upgrades, server tuning, transaction reduction, version updates, and slave count limits—to eliminate the lag.

LatencyMaster‑SlaveReplication
0 likes · 7 min read
How to Diagnose and Fix MySQL Master‑Slave Replication Lag
IT Services Circle
IT Services Circle
Oct 20, 2024 · Backend Development

Huawei Campus Interview Experience and Technical Q&A: Java, MySQL, Redis, Design Patterns, and Networking Fundamentals

This article combines a Huawei campus recruitment overview—including interview timeline, salary grades, and interview process—with extensive technical Q&A covering Java vs C differences, Java features, OOP concepts, common design patterns, Redis and MySQL comparisons, MySQL transaction properties, B‑Tree indexes, list deduplication, stack implementations, and the low‑level workings of the ping command.

Data StructuresDesign Patternsjava
0 likes · 23 min read
Huawei Campus Interview Experience and Technical Q&A: Java, MySQL, Redis, Design Patterns, and Networking Fundamentals
Selected Java Interview Questions
Selected Java Interview Questions
Oct 20, 2024 · Backend Development

Optimizing XML‑to‑MySQL Bulk Import: Reducing Execution Time from 300 s to 4 s with JDBC Batch and Disruptor

This article details how a Java‑based XML‑to‑MySQL import of over 60,000 records was accelerated from 300 seconds to just 4 seconds by enabling JDBC batch processing, configuring rewriteBatchedStatements, and employing a multithreaded Disruptor pipeline for asynchronous writes, while also discussing further tuning options.

DisruptorJDBC BatchXML parsing
0 likes · 11 min read
Optimizing XML‑to‑MySQL Bulk Import: Reducing Execution Time from 300 s to 4 s with JDBC Batch and Disruptor
Spring Full-Stack Practical Cases
Spring Full-Stack Practical Cases
Oct 18, 2024 · Databases

Master MySQL Performance: 20 Essential SQL Optimization Techniques

This article presents a comprehensive guide for Java engineers on MySQL query optimization, covering index usage, avoiding SELECT *, proper JOINs, efficient WHERE clauses, LIMIT, EXISTS vs IN, function indexes, connection pooling, batch processing, data types, execution plan analysis, materialized views, and regular performance monitoring, all illustrated with practical code examples.

SQL Optimizationdatabaseindexes
0 likes · 19 min read
Master MySQL Performance: 20 Essential SQL Optimization Techniques
dbaplus Community
dbaplus Community
Oct 17, 2024 · Databases

What’s New in MySQL 9.1.0? A Deep Dive into Latest Features and Fixes

MySQL Innovation Edition 9.1.0, released on October 15 2024, introduces atomic DDL operations, enhanced audit and firewall handling, numerous compiler and SQL function fixes, JavaScript stored‑procedure improvements, new vector support, updated keyring security, pluggable authentication fixes, expanded GROUP REPLICATION logging, and several EXPLAIN and performance‑schema enhancements.

Database FeaturesGroup ReplicationJavaScript Stored Procedures
0 likes · 10 min read
What’s New in MySQL 9.1.0? A Deep Dive into Latest Features and Fixes
ITPUB
ITPUB
Oct 17, 2024 · Databases

What’s New in MySQL 9.1.0? A Deep Dive into Features and Fixes

MySQL 9.1.0, released on October 15, 2024, introduces DDL atomicity, improved audit handling, new JavaScript defaults, VECTOR type support, enhanced GROUP REPLICATION logging, expanded EXPLAIN output, and numerous bug fixes across SQL syntax, performance, and security, while the 8.4.3 LTS patch remains feature‑free.

DDLGroupReplicationReleaseNotes
0 likes · 10 min read
What’s New in MySQL 9.1.0? A Deep Dive into Features and Fixes
Java Tech Enthusiast
Java Tech Enthusiast
Oct 17, 2024 · Fundamentals

Technical Interview Insights: Networking, TCP, HTTP, MySQL Locks, and C++ Level‑Order Traversal

The article explains why network protocols use layered design, details TCP's reliability and congestion‑control mechanisms, outlines HTTP status‑code categories, reviews MySQL lock scopes from global to row‑level, provides a C++ breadth‑first binary‑tree traversal example, and describes a three‑step weighing puzzle to find an odd ball.

AlgorithmsC++HTTP
0 likes · 15 min read
Technical Interview Insights: Networking, TCP, HTTP, MySQL Locks, and C++ Level‑Order Traversal
dbaplus Community
dbaplus Community
Oct 15, 2024 · Databases

How to Safely Perform Full-Table Updates on Billion-Row MySQL Tables

Updating billions of rows in a MySQL table can overwhelm binlog replication and cause deep‑pagination inefficiencies, so this article explains the pitfalls of direct UPDATE, explores limit‑based and IN‑based approaches, and presents a production‑ready batch update strategy using NO_CACHE and forced primary‑key indexing.

Batch ProcessingBinlogFull Table Update
0 likes · 7 min read
How to Safely Perform Full-Table Updates on Billion-Row MySQL Tables
Architect's Tech Stack
Architect's Tech Stack
Oct 15, 2024 · Databases

MySQL Query Optimization: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Pushdown, Early Limiting, and Intermediate Result Pushdown

This article presents practical MySQL performance tuning methods, covering LIMIT pagination, implicit type conversion pitfalls, rewriting joins for updates/deletes, mixed-order sorting, replacing EXISTS with joins, predicate pushdown, early result limiting, and intermediate result pushdown, each illustrated with SQL examples and execution plan analyses.

Predicate PushdownSQL Optimizationindexes
0 likes · 13 min read
MySQL Query Optimization: LIMIT, Implicit Conversion, Join Rewrite, Mixed Sorting, EXISTS, Predicate Pushdown, Early Limiting, and Intermediate Result Pushdown
ITPUB
ITPUB
Oct 14, 2024 · Databases

How Long Can MySQL AUTO_INCREMENT IDs Last? INT vs BIGINT Explained

This article examines MySQL's AUTO_INCREMENT integer types, comparing the 4‑byte INT and 8‑byte BIGINT limits, calculating how long each can store records under realistic insertion rates, and providing step‑by‑step SQL commands and safety tips for converting INT columns to BIGINT.

AUTO_INCREMENTData Typesint
0 likes · 6 min read
How Long Can MySQL AUTO_INCREMENT IDs Last? INT vs BIGINT Explained
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 14, 2024 · Operations

Guide to Installing and Using CGDB for Debugging MySQL on CentOS

This article introduces CGDB, explains how to choose the appropriate GDB version, provides step‑by‑step installation commands for both CGDB and GDB, and demonstrates practical debugging scenarios with MySQL, including attaching to processes, inspecting threads, analyzing core dumps, and adjusting runtime variables.

CGDBLinuxcurses
0 likes · 8 min read
Guide to Installing and Using CGDB for Debugging MySQL on CentOS
Efficient Ops
Efficient Ops
Oct 13, 2024 · Databases

Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ES & HBase

This article explains why MySQL queries become slow—covering index misuse, MDL locks, flush waits, large‑table bottlenecks, and read/write splitting—then shows how ElasticSearch’s inverted index and HBase’s column‑family design can complement MySQL for faster search and scalable storage.

Database OptimizationHBaseindexing
0 likes · 20 min read
Why Your MySQL Queries Are Slow and How to Fix Them with Indexes, ES & HBase
dbaplus Community
dbaplus Community
Oct 13, 2024 · Databases

Master MySQL Data Masking: Percona Plugin & Custom Functions Guide

Learn how to protect sensitive information in MySQL by installing the open‑source Percona data_masking plugin, verifying its status, and using built‑in and custom masking functions for IDs, phone numbers, emails, names, amounts, and addresses, with step‑by‑step SQL examples.

Perconadata maskingmysql
0 likes · 7 min read
Master MySQL Data Masking: Percona Plugin & Custom Functions Guide
ITPUB
ITPUB
Oct 12, 2024 · Databases

How Do Chinese Databases Stack Up? A Deep Dive into Compatibility Across Major Products

This article examines the compatibility landscape of major Chinese database products, comparing their support for Oracle, MySQL, PostgreSQL and other standards, and outlines the available assessment and migration tools to help users choose the right solution for heterogeneous environments.

Chinese DatabasesDatabase CompatibilityMigration Tools
0 likes · 25 min read
How Do Chinese Databases Stack Up? A Deep Dive into Compatibility Across Major Products
Top Architect
Top Architect
Oct 12, 2024 · Databases

Understanding MySQL Index Structures, Execution Plans, Transaction Isolation Levels, MVCC, and Buffer Pool Mechanisms

This article provides a comprehensive technical guide on MySQL internals, covering the underlying data structures of indexes (B‑tree, B+‑tree, hash), how execution plans are generated and interpreted, the four transaction isolation levels, MVCC implementation, and the InnoDB buffer‑pool architecture.

MVCCSQL Optimizationbuffer pool
0 likes · 22 min read
Understanding MySQL Index Structures, Execution Plans, Transaction Isolation Levels, MVCC, and Buffer Pool Mechanisms
Su San Talks Tech
Su San Talks Tech
Oct 12, 2024 · Databases

Master MySQL: Essential Database Design and Performance Guidelines

This article compiles comprehensive MySQL best‑practice guidelines covering charset selection, storage engine choice, naming conventions, field design, index optimization, SQL development tips, and operational procedures to improve performance, maintainability, and reliability of database systems.

SQL Best Practicesdata modelingindexing
0 likes · 23 min read
Master MySQL: Essential Database Design and Performance Guidelines
ITPUB
ITPUB
Oct 11, 2024 · Databases

How to Efficiently Delete Massive MySQL Logs While Preserving Critical Types

This article explains how to clean up a rapidly growing MySQL log table by batching deletions, handling unindexed type columns, and using a start‑id based pagination strategy that scales from tens of millions to billions of rows without locking the database.

Large TablesSQL Optimizationdata deletion
0 likes · 8 min read
How to Efficiently Delete Massive MySQL Logs While Preserving Critical Types
MaGe Linux Operations
MaGe Linux Operations
Oct 11, 2024 · Databases

Comprehensive MySQL Optimization Guide: Hardware, OS, Config & Query Tuning

This guide details a step‑by‑step MySQL performance optimization strategy covering hardware selection, disk I/O planning, operating‑system tweaks, vertical and horizontal sharding, my.cnf parameter tuning, query cache configuration, resource limits, table and index caching, and practical query‑level best practices.

ConfigurationDatabase Optimizationmysql
0 likes · 18 min read
Comprehensive MySQL Optimization Guide: Hardware, OS, Config & Query Tuning
Java Architecture Stack
Java Architecture Stack
Oct 10, 2024 · Databases

Master MySQL Backup & Recovery: Strategies for Every Business Scenario

This guide walks through five practical MySQL backup and restoration workflows—including scheduled full backups, hourly incremental binlog backups for high‑frequency trading, development‑environment data masking, selective table dumps, and cold‑storage disaster recovery—providing exact commands, configuration tweaks, and step‑by‑step procedures.

BackupDatabase AdministrationIncremental Backup
0 likes · 7 min read
Master MySQL Backup & Recovery: Strategies for Every Business Scenario
Alibaba Cloud Developer
Alibaba Cloud Developer
Oct 10, 2024 · Databases

Inside MySQL 8.0: How Table, Page, and Row Locks Manage Concurrency

This article provides a comprehensive overview of MySQL 8.0's concurrency control mechanisms, detailing table‑level MDL locks, server‑ and engine‑layer table locks, page‑level B+Tree locking, and row‑level lock types with code examples and real‑world deadlock scenarios.

Concurrency ControlDatabase InternalsInnoDB
0 likes · 28 min read
Inside MySQL 8.0: How Table, Page, and Row Locks Manage Concurrency
Aikesheng Open Source Community
Aikesheng Open Source Community
Oct 9, 2024 · Databases

Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels

This article demonstrates how InnoDB acquires different types of row and gap locks under REPEATABLE-READ and READ-COMMITTED isolation levels by creating a test table, inserting data, setting transaction isolation, executing SELECT ... FOR SHARE statements, and inspecting lock information from performance_schema.

InnoDBRead CommittedRepeatable Read
0 likes · 9 min read
Understanding InnoDB Locking: Repeatable Read vs. Read Committed Isolation Levels
dbaplus Community
dbaplus Community
Oct 7, 2024 · Databases

How Do Chinese Databases Compare in Compatibility? A Detailed Survey

This article surveys the compatibility of major Chinese database products with Oracle, MySQL, PostgreSQL and other systems, covering compatibility targets, levels, assessment methods, migration tools, and specific feature support such as protocols, data types, character sets, SQL syntax, procedural languages, built‑in functions, system views, optimizer capabilities and security features.

Chinese DatabasesDatabase CompatibilityEvaluation Tools
0 likes · 26 min read
How Do Chinese Databases Compare in Compatibility? A Detailed Survey
Top Architect
Top Architect
Oct 4, 2024 · Databases

Design and Implementation of Table Sharding for Loan and Repayment Applications Using ShardingSphere and Spring Boot

The article describes a year‑long development experience where a senior architect designs, plans, and implements a sharding solution for loan and repayment tables using ShardingSphere, Spring Boot 3, MySQL, and custom synchronization code to migrate billions of rows while ensuring data consistency and operational continuity.

ShardingSphereSpringBootdatabase sharding
0 likes · 25 min read
Design and Implementation of Table Sharding for Loan and Repayment Applications Using ShardingSphere and Spring Boot
Architect's Guide
Architect's Guide
Oct 4, 2024 · Databases

Using MySQL Router 8.2 for Read/Write Splitting with InnoDB ReplicaSet

The article demonstrates how MySQL Router 8.2 enables transparent read/write splitting for an InnoDB ReplicaSet, showing the architecture, configuration steps, connection behavior, and the generated routing settings that improve database performance without modifying application code.

DatabasePerformanceInnoDBReadWriteSplitting
0 likes · 4 min read
Using MySQL Router 8.2 for Read/Write Splitting with InnoDB ReplicaSet
Architect
Architect
Oct 3, 2024 · Databases

How to Tame Massive MySQL Tables: Partitioning, Sharding, and Archiving Strategies

This article walks through evaluating massive MySQL tables, explains why large row counts slow queries, and compares three practical solutions—table partitioning, database sharding, and hot/cold archiving—while highlighting their trade‑offs and offering guidance on selecting the right approach for a given workload.

Hot/Cold ArchivingLarge Tablesdatabase partitioning
0 likes · 18 min read
How to Tame Massive MySQL Tables: Partitioning, Sharding, and Archiving Strategies
Java Tech Enthusiast
Java Tech Enthusiast
Oct 3, 2024 · Databases

Understanding MySQL Architecture and SQL Execution Flow

The article outlines MySQL’s layered architecture—connector, parser, optimizer, executor, and binary log in the server layer and pluggable storage engines like InnoDB—then walks through the step‑by‑step processing of SELECT and UPDATE statements, highlighting permission checks, caching, plan selection, redo and binlog handling for crash‑safe durability.

Database ArchitectureInnoDBQuery Processing
0 likes · 11 min read
Understanding MySQL Architecture and SQL Execution Flow
Java Tech Enthusiast
Java Tech Enthusiast
Oct 3, 2024 · Databases

MySQL Large Table Data Deletion Optimization Strategy

To efficiently purge old user‑operation logs while retaining the most recent three months and preserving type‑c entries, the article recommends abandoning offset pagination and MyBatis PageHelper, using primary‑key‑based pagination with a tracked startId, in‑memory filtering, intelligent startId recalculation, and optional sleep intervals to avoid database overload.

Database Optimizationdata cleanuplarge table deletion
0 likes · 6 min read
MySQL Large Table Data Deletion Optimization Strategy
Architect's Guide
Architect's Guide
Oct 1, 2024 · Databases

Efficient Full‑Table Updates in MySQL: Handling Row‑Based Binlog, Deep Pagination, and Batch Processing

The article explains why executing a full‑table UPDATE on large MySQL tables using row‑based binlog can overload replication, discusses the inefficiencies of deep pagination and IN clauses, and presents a batch‑processing strategy that leverages SQL_NO_CACHE and FORCE INDEX to safely and efficiently migrate data.

BinlogFORCE INDEXFull Table Update
0 likes · 8 min read
Efficient Full‑Table Updates in MySQL: Handling Row‑Based Binlog, Deep Pagination, and Batch Processing
IT Services Circle
IT Services Circle
Oct 1, 2024 · Databases

Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table

This article explains how adding an index to a status column in a tens‑of‑millions‑row MySQL table affects query performance, covering the basic index lookup process, extreme cases where full scans are chosen, selectivity, covering indexes, composite indexes, partitioning, and using EXPLAIN to verify execution plans.

Partitioningdatabaseexplain
0 likes · 9 min read
Effectiveness of Adding an Index on a Status Column in a Tens‑of‑Millions Row MySQL Table
Ops Development & AI Practice
Ops Development & AI Practice
Sep 26, 2024 · Operations

How to Configure MySQL 8.0 as a Systemd Notify Service on Linux

This guide walks through verifying MySQL 8.0.16+, copying and editing the mysqld.service file, setting the Service Type to notify, adding NotifyAccess, adjusting ExecStart, reloading systemd, restarting MySQL, and checking its status, while covering SELinux, firewall, and notification‑delay optimizations.

LinuxNotifymysql
0 likes · 6 min read
How to Configure MySQL 8.0 as a Systemd Notify Service on Linux
Open Source Tech Hub
Open Source Tech Hub
Sep 25, 2024 · Databases

How to Safely Delete Large Log Tables and Resolve MySQL Read‑Replica Lag

This guide explains how to regularly purge oversized log tables using time‑based DELETE statements, interprets related cloud RDS and ECS alerts, and offers troubleshooting steps and batch‑deletion techniques to address MySQL read‑replica synchronization delays caused by large transactions.

Batch DeleteDatabase MaintenanceRDS
0 likes · 6 min read
How to Safely Delete Large Log Tables and Resolve MySQL Read‑Replica Lag
Java Backend Technology
Java Backend Technology
Sep 25, 2024 · Databases

How to Safely Update Billion‑Row MySQL Tables Without Overloading Binlog

This article explains why a simple full‑table UPDATE on massive MySQL tables can cripple master‑slave replication, analyzes deep‑pagination inefficiencies, and presents a step‑by‑step batch‑update strategy using NO_CACHE and FORCE INDEX to keep binlog size and buffer‑pool impact under control.

BinlogFORCE INDEXFull Table Update
0 likes · 9 min read
How to Safely Update Billion‑Row MySQL Tables Without Overloading Binlog
Su San Talks Tech
Su San Talks Tech
Sep 25, 2024 · Backend Development

Mastering Batch Updates in MyBatis: From foreach to ON DUPLICATE KEY

This article walks through practical ways to perform batch updates in MyBatis, compares foreach, case‑when, and INSERT…ON DUPLICATE KEY techniques, explains common Druid WallFilter restrictions, and shows how to enable multi‑statement execution via JDBC and Spring configuration.

Batch UpdateDruidSpring Boot
0 likes · 13 min read
Mastering Batch Updates in MyBatis: From foreach to ON DUPLICATE KEY
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 24, 2024 · Databases

Why Does a SELECT on performance_schema.data_locks Hang MySQL? Deep Dive & Fix

A batch INSERT triggers a massive lock record buildup, and a concurrent SELECT on performance_schema.data_locks causes a memory allocation error that leaves trx_sys‑mutex unreleased, leading to a full MySQL hang; the article reproduces the bug, analyses the stack, explains the root cause, and shows the official fix in MySQL 8.0.37.

Bug AnalysisInnoDBPerformance Schema
0 likes · 9 min read
Why Does a SELECT on performance_schema.data_locks Hang MySQL? Deep Dive & Fix
Bilibili Tech
Bilibili Tech
Sep 24, 2024 · Backend Development

Technical Implementation of Bilibili's Game Live Streaming Interactive Features: 'Play Together' and 'Help Me Play'

Bilibili’s game live‑stream platform implements interactive features ‘Play Together’ and ‘Help Me Play’ by using Redis ZSET queues, MySQL persistence, real‑time streamer recommendation, ticket‑based purchase flows, state‑machine order handling, and comprehensive monitoring to ensure reliable, scalable viewer‑streamer gameplay collaboration.

Bilibilibackend-developmentgame live streaming
0 likes · 12 min read
Technical Implementation of Bilibili's Game Live Streaming Interactive Features: 'Play Together' and 'Help Me Play'
Open Source Linux
Open Source Linux
Sep 24, 2024 · Backend Development

How to Tame 900% CPU Spikes in MySQL and Java Processes

This guide explains why MySQL and Java processes can exceed 900% CPU usage in production, and provides step‑by‑step methods—using top, show processlist, jstack, index tuning, caching, and code adjustments—to diagnose, mitigate, and prevent such spikes.

CPU optimizationLinuxjava
0 likes · 10 min read
How to Tame 900% CPU Spikes in MySQL and Java Processes
Architecture Digest
Architecture Digest
Sep 23, 2024 · Databases

Evaluating and Optimizing Large MySQL Tables: Partitioning, Sharding, and Hot‑Cold Archiving Strategies

This article explains how to assess MySQL table size using table capacity, disk usage, and instance resources, describes why large tables degrade performance, and presents three practical solutions—partitioning, sharding (horizontal/vertical), and hot‑cold data archiving—along with their trade‑offs and implementation tips.

B+TreeDatabase OptimizationHotColdArchiving
0 likes · 15 min read
Evaluating and Optimizing Large MySQL Tables: Partitioning, Sharding, and Hot‑Cold Archiving Strategies
Aikesheng Open Source Community
Aikesheng Open Source Community
Sep 23, 2024 · Databases

Misuse of Wildcards in MySQL GRANT Statements Leads to Permission Errors and How to Avoid Them

This article explains how using the '_' and '%' wildcards in MySQL GRANT statements can unintentionally broaden privileges, illustrates the security risks with concrete examples and version‑specific behavior, and provides practical guidance on escaping wildcards, auditing existing grants, and preventing privilege loss.

GrantPermissionsWildcards
0 likes · 14 min read
Misuse of Wildcards in MySQL GRANT Statements Leads to Permission Errors and How to Avoid Them