This article presents a thorough overview of MySQL performance tuning, covering index usage, query rewriting, SELECT statement ordering, DML batch operations, hint directives, and table design recommendations to help developers write efficient SQL for large‑scale systems.
This article details how Vivo transformed its monolithic e‑commerce order service into a scalable, service‑oriented system by applying data archiving, sharding‑JDBC based sharding, MySQL‑to‑Elasticsearch sync, zero‑downtime migration, and distributed‑transaction techniques, while sharing practical pitfalls and performance results.
This article explains how proper index creation boosts MySQL query performance, detailing the mechanics of indexes, why B+Tree is chosen over binary and balanced trees, the storage differences between MyISAM and InnoDB, and practical guidelines for designing effective single‑column, composite, and covering indexes.
This article explains the importance of MySQL backup and recovery, outlines common failure scenarios, recommends backup architectures and verification processes, reviews popular logical and physical backup tools such as mysqldump, mydumper, and xtrabackup, and shares practical case studies and best‑practice guidelines.
This article provides a detailed source‑code walkthrough of DBLE’s network module, explaining how it processes MySQL packets, implements selector‑based multiplexing, and performs asynchronous task handling to achieve high‑performance database communication for client connections.
This article explains how to leverage Flinkx and Flink Stream API to create a unified data‑sync task that extracts data from sharded MySQL tables, splits the workload, and pushes it to an MQ cluster, while detailing the underlying InputFormat and Reader architecture.
This article explains the MySQL client‑server communication protocol, detailing the connection phase—including handshake, authentication, and packet structure—as well as the replication process, covering registration as a slave, binlog streaming, and the various binlog event types and their formats.
This article explains why MySQL uses B+ trees for indexing, describes the underlying principles of various index types, compares MyISAM and InnoDB implementations, and provides practical optimization guidelines such as using auto‑increment primary keys, left‑most prefix rules, and configuration tuning.
This article explains MySQL locking mechanisms, covering lock concepts, isolation level interactions, lock granularity (row, table, page), lock types (shared, exclusive, record, gap, next-key), InnoDB row‑lock implementation, deadlock causes and remedies, and the differences between optimistic and pessimistic locking.
This article analyzes common database performance bottlenecks, explains when to apply IO‑ or CPU‑focused optimizations, and provides a detailed guide to horizontal and vertical sharding, partitioning strategies, tooling choices, implementation steps, and practical pitfalls for scalable systems.
The article investigates why MySQL replication reports the error “heartbeat is not compatible with local info” when large transactions exceed the 4 GB limit of the binlog's next_position field, reproduces the issue with a controlled experiment, analyzes packet structures, and proposes practical mitigation strategies.
This article explains why using indexes in MySQL does not always prevent slow queries, analyzes full‑index scans, index selectivity, back‑table lookups, virtual columns, and provides practical optimization techniques to reduce scanned rows and improve performance.
This article explains the purpose and usage of MySQL character set system variables, illustrates how they affect client‑server communication and file handling, and provides practical examples and diagrams to help developers correctly configure character sets for optimal performance.
The article investigates why a MySQL instance experiences frequent "Opening tables" states, analyzing stack traces, identifying a data dictionary lock held by the dict_stats_thread, and concluding that slow disk writes trigger InnoDB's synchronous dirty‑page flush, blocking many operations.
This guide walks through downloading the RuoYi source, importing its SQL schema into MySQL, configuring the datasource in IntelliJ IDEA, running the SpringBoot application, and accessing the permission‑management UI at http://localhost:8000/, with links to advanced versions.
This article explains the different index mechanisms used by MySQL (MyISAM and InnoDB) and Lucene, compares them with Elasticsearch's inverted index, and discusses how these structures affect storage, memory usage, and query performance.
This article provides a comprehensive guide to MySQL best practices, covering storage engine characteristics, optimal data type selection, index design strategies, and query‑performance tuning techniques for teams without dedicated DBAs.
This article explains MySQL's hash index by introducing fundamental data structures such as arrays, dictionaries, linked lists, and hash tables, discussing their advantages and disadvantages, and showing how they are used in MySQL with practical code examples.
The article explains how ransomware gangs steal MySQL databases, automate ransom‑payment portals on the dark web, auction unsold data, and accept Bitcoin, revealing the scale of over 85,000 databases for sale at roughly $500‑$550 each.
This article introduces the fundamentals of network I/O for the DBLE database system, covering the TCP/IP protocol stack, the MySQL application‑layer protocol, and the performance differences between BIO and NIO approaches, laying the groundwork for deeper source‑code analysis.
This comprehensive guide explores MySQL’s internal architecture, covering the SQL execution process, server and storage engine layers, binlog, redo and undo logs, index structures, transaction isolation levels, lock mechanisms, MVCC implementation, buffer pool management, table optimization, query techniques, and advanced performance tuning strategies.
This article analyzes common database migration strategies, compares two approaches—master‑slave cut‑over and proxy‑based migration—describes Qunar's internal MMM and Percona XtraDB Cluster (PXC) architectures, and details a step‑by‑step, zero‑downtime migration process from MMM to PXC with practical lessons and best practices.
Even when a MySQL query uses an index, it can still become a slow query; this article explains index structures, explains why full‑index scans and poor selectivity cause performance issues, and presents step‑by‑step optimization techniques such as index condition pushdown, virtual columns, and better filtering.
This article explains MySQL’s logical architecture, query processing steps, caching mechanisms, index structures, and provides practical performance‑tuning advice—including schema design, index creation, query rewriting, and pagination—helping readers grasp the underlying principles and apply effective optimizations in real‑world workloads.
This article introduces MyFlash, an open‑source MySQL flashback utility, explains its limitations, provides step‑by‑step installation commands, details the available command‑line options for specifying databases, tables, time ranges and SQL types, and demonstrates a complete rollback test case with binlog parsing and execution.
This article explains how to identify slow‑query symptoms, gather problematic SQL statements, apply common writing and indexing tips, read execution plans, and walk through a complete MySQL optimization case, enabling readers to become proficient database performance engineers.
Over 85,000 MySQL databases are being sold on the dark web for around $500 each, with attackers automating ransom notices via portals on sqldb.to and dbrestore.to, demanding Bitcoin payments, auctioning unpaid data after nine days, and targeting a range of database platforms since 2017.
Over 85,000 MySQL databases are being sold on the dark web for around $550 each, with attackers automating ransom portals, demanding Bitcoin payments, and auctioning unpaid data after nine days, while victims share ransom notes across forums and Bitcoin abuse trackers monitor the growing threat.
This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.
This comprehensive guide answers the most frequent MySQL interview questions, covering index structures, transaction isolation levels, table design best practices, storage engine differences, query optimization techniques, and miscellaneous topics such as data types, binlog formats, and sharding strategies.
The article explains how to improve query performance by flattening multi‑table MySQL data and synchronizing it to Elasticsearch—using DataX for one‑time bulk loading and Canal (with Canal‑Adapter) for real‑time binlog‑driven incremental updates—while detailing configuration steps, job examples, and common pitfalls.
This article provides a comprehensive guide to improving MySQL performance by optimizing single‑table schemas, indexes, queries, engine settings, system parameters, and then discusses read/write separation, caching layers, table partitioning, vertical and horizontal sharding, compatible scalable databases and when to consider NoSQL alternatives.
This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.
This comprehensive guide covers the factors that impact MySQL server performance, strategies for handling large tables and transactions, hardware and OS tuning, storage‑engine selection, replication modes, high‑availability architectures, indexing techniques, and practical SQL query‑optimization methods.
This article explains how MySQL's InnoDB engine applies row‑level locks for DELETE and SELECT statements under various index configurations and isolation levels, covering MVCC, snapshot vs current reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
The article explains how MySQL's Online DDL handles VARCHAR column expansions, demonstrates performance differences across character sets using sysbench, and clarifies why certain size changes trigger in‑place alterations while others require full table copies, providing practical guidance for DBAs.
This tutorial explains how to create a Spring Boot project that implements QQ email‑based user registration and login, covering POP3/SMTP setup, Maven dependencies, MySQL schema, core Java classes, configuration files, and step‑by‑step Postman testing procedures.
This article documents a step‑by‑step MySQL data migration case involving 140 million rows, covering export via SELECT INTO OUTFILE, import with LOAD DATA INFILE, and a series of performance tweaks such as index removal, session variable tuning, engine selection, and monitoring to achieve sub‑10‑minute load times.
This article presents a comprehensive, step‑by‑step case study of a large‑scale MySQL sharding project, covering business refactoring, storage architecture design, data migration, synchronization strategies, capacity planning, validation, rollout procedures, stability safeguards, and cross‑team collaboration.
This guide explains how to set up and run several independent MySQL server instances on one Linux host by creating separate data directories, configuring individual my.cnf files, initializing each instance, managing them with systemd services, and verifying that each listens on its own port.
This article explains how to install MySQL's Query Rewrite plugin, define and manage rewrite rules, test its impact on large‑scale SELECT queries with sysbench, and demonstrates that enabling the plugin can reduce count(*) execution time from seconds to milliseconds with minimal overall database overhead.
This article examines the evolution of database architectures, introduces the hot‑data and cold‑data concepts for a weather‑data service, and details a practical implementation using MySQL, TiDB, and PolarDB with synchronization, expiration procedures, and suggestions for future improvements.
This article provides a detailed comparison of MySQL and PostgreSQL, covering licensing, ACID compliance, SQL standard support, replication models, concurrency control, performance characteristics, high‑availability solutions, external data integration, storage engines, and overall strengths and weaknesses to help readers choose the most suitable open‑source database for their scenarios.
This article shares a comprehensive overview of TiDB Data Migration (DM), covering its architecture, configuration, online DDL support, common pitfalls such as duplicate‑key errors, large‑scale import tuning, version limits, and cleanup recommendations to help DBAs efficiently migrate MySQL/MariaDB workloads to TiDB.
This comprehensive guide explains MySQL's binary log, undo log, redo log, and ChangeBuffer, covering their concepts, purposes, recording formats, flush timing, recovery processes, and how they interact during transactions and crash‑safe recovery.
The article describes an experiment that shows when a floating IP (SIP) moves between MySQL servers, existing client connections experience long delays until TCP keepalive detects the failure, highlighting the need to configure shorter keepalive intervals for high‑availability database deployments.
This article explains the fundamentals of MySQL InnoDB page size, how it interacts with operating system pages, and the performance implications for rows, indexes, and tablespaces, providing practical guidance for choosing optimal page sizes in production environments.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, pre‑filtering, and intermediate result handling, and provides rewritten SQL examples that dramatically reduce execution time.
This guide outlines comprehensive MySQL best‑practice standards covering naming conventions, storage engine selection, charset usage, table and column comments, size limits, partitioning, hot‑cold data separation, index design, SQL coding rules, and operational safeguards to ensure performant, maintainable, and secure database systems.
This article explains how using MySQL's INSERT INTO … SELECT without proper indexing can cause full‑table scans and locks, illustrates the problem with a real‑world order‑migration incident, and provides a concrete solution by adding an index to the source column.
This article explains MySQL primary key design principles, compares auto‑increment, UUID and uuid_short approaches, provides SQL examples and performance benchmarks, and offers guidance on choosing business‑related versus unrelated key columns for optimal data integrity and query efficiency.
This article explains the hidden costs of using SELECT * in MySQL queries, explores how unnecessary columns, large data types, and loss of covering‑index opportunities degrade performance, and shows how composite indexes and proper indexing strategies can dramatically speed up data retrieval.
When MySQL reports 100% disk usage with only a few megabytes left, typical fixes like dropping tables or restarting may not release space, so this article explains how to diagnose data_free bloat, apply proper fragmentation cleanup, and restore the database safely.
This guide explains how to manually back up a MySQL database using mysqldump, import the backup file, and set up a Bash script with crontab for daily automated backups, including handling of MySQL event tables and path configuration.
This article outlines five practical methods for synchronizing MySQL data to external systems such as Elasticsearch, Redis, and message queues, covering business‑layer hooks, middleware integration, scheduled tasks using updated_at, binlog parsing with ROW format, and handling mixed or statement binlog formats, plus open‑source tools.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, subquery updates, mixed ordering, EXISTS clauses, predicate push‑down, early row reduction, and intermediate result set handling, providing concrete rewrites and execution‑plan comparisons that dramatically improve query speed.
This article provides a comprehensive technical analysis of ShardingSphere's XA distributed transaction capabilities, covering the CAP theory, X/Open DTP model, MySQL XA support, JTA mapping, Atomikos initialization, transaction lifecycle (begin, getConnection, commit, rollback), and the recovery mechanism with detailed code snippets and diagrams.
This guide explains how to install MySQL 8.0.16 on two CentOS7 servers, configure master‑slave replication with binlog, set up read/write splitting, and integrate the setup into a Spring Boot application using dynamic‑datasource, covering all necessary commands, configuration files, and code examples.
This article outlines MySQL Group Replication’s key features, fault-detection mechanism, limitations, required environment, and step-by-step deployment scripts, providing a practical guide for setting up a high-availability MySQL cluster on CentOS using the provided shell scripts and configuration files.
This guide explains how to filter data in MySQL using various query conditions such as WHERE, comparison operators, BETWEEN, IN, NULL checks, logical operators, short‑circuit evaluation, and operator precedence, with clear examples and visual illustrations.
This article examines a slow MySQL 5.7 query, uses the optimizer trace tool to reveal why the optimizer chose an inefficient plan, explains the role of fan‑out estimation on information_schema tables, and shows how hints can be applied to correct the plan.
This article examines MySQL pagination strategies for large tables, detailing preparation, general LIMIT‑based pagination, subquery and ID‑range optimizations, performance test results, and practical recommendations for improving query speed on millions of rows.
This article explains common misconceptions about index selectivity and filtering, demonstrates how composite indexes can avoid slow queries, and shows why order‑by may still trigger filesort, providing practical tips and SQL examples for better MySQL index design.
This article explains how Vivo uses Alibaba's open‑source Canal to capture MySQL binlog changes, achieve non‑stop sharding and cross‑region data migrations, ensure high availability with Zookeeper, and share practical lessons on serialization, consistency, and monitoring in large‑scale backend systems.
The article explains why short URLs are popular, outlines their basic workflow, and details a backend service design that uses an incremental ID generator, Redis caching, MySQL storage, batch allocation, and distributed strategies to efficiently create and manage short links.
This article examines how differences between Galera GTID and MySQL GTID caused replication interruptions during a Percona XtraDB Cluster (PXC) split at Qunar, details the migration procedure, reproduces the problem with step‑by‑step commands, explains GTID concepts, and proposes operational improvements to avoid data inconsistency.
This article provides an extensive overview of MySQL, covering database creation, renaming, and deletion, table creation and alteration, column and index management, data queries, inserts, updates, deletes, operator usage, view handling, functions, stored procedures, triggers, sequence generation, user management, and permission configuration, with practical examples and code snippets.
This article explains the fundamental concepts and practical design of MySQL indexes, covering implementation-level index types, application-level index categories, the differences between clustered and non‑clustered indexes, B‑Tree and hash structures, covering indexes, and best‑practice strategies for optimal query performance.
The article describes a puzzling MySQL 5.7 situation where any password works, investigates the cause as the auth_socket authentication plugin, provides exact SQL commands to fix the issue, and explains the plugin's security characteristics and appropriate deployment scenarios.
This article explains Change Data Capture (CDC) and how Flink CDC can be used for incremental data synchronization, real‑time materialized views, audit logging, and CDC‑based joins, providing code examples, Maven dependencies, and SQL/Java snippets for MySQL and Kafka integrations.
The article details a MySQL server startup failure caused by insufficient memory, shows command outputs and error logs, and explains how reducing the innodb_buffer_pool_size to 64 MB in the my.cnf configuration resolves the issue on a 2 GB Alibaba Cloud instance.
This article explains why indexes dramatically improve MySQL query speed on large tables, outlines common reasons for slow SQL, shows how to use EXPLAIN and SHOW PROFILE to diagnose problems, and provides step‑by‑step examples of creating and evaluating indexes for optimal performance.
This article examines why a MySQL 5.7 query that finds tables lacking primary or unique keys runs extremely slowly, demonstrates an experiment reproducing the issue, analyzes the optimizer's subquery rewrite, and shows how adding hints and materialization reduces execution time from over 16 seconds to under one second.
This article explains four methods to determine MySQL server startup time, such as checking service status, querying uptime, using ps, and inspecting logs, and also outlines common crash causes like bugs and resource allocation failures, providing troubleshooting steps and diagnostic commands.
This article presents a comprehensive set of mandatory and recommended MySQL database standards—including table creation rules, SQL writing conventions, and index design practices—aimed at preventing improper schemas, reducing erroneous queries, and improving overall performance for backend development teams.
The article evaluates how turning on binlog transaction compression in MySQL 8.0 (both 8.0.19 and 8.0.20) affects throughput and resource usage under a sysbench workload of 120 tables with 240 concurrent threads, showing roughly a 1% performance drop and a similar increase in CPU consumption.
This guide explains how to build a private npm registry on Ubuntu using cnpmjs, covering MySQL installation, server configuration, repository setup, package publishing, and synchronization options, providing step‑by‑step commands and troubleshooting tips for a stable internal package management solution.
This article explains MySQL 8's persisted system variables feature, showing how to use SET PERSIST and SET PERSIST_ONLY to make variable changes survive restarts, how to view and clear persisted settings, how to disable persistence, required privileges, and how to monitor persisted variables via performance_schema.
The article examines MySQL pagination performance on a large order_history table, presenting various pagination methods—including simple LIMIT, subquery, ID range, and temporary table techniques—along with detailed test results that show how query speed varies with record count and offset.
This article explains the MySQL EXPLAIN command, its extended forms, the meaning of each output column, common performance pitfalls such as full table scans, using temporary tables or filesorts, and provides practical optimization recommendations with illustrative examples.
This article details the design and implementation of a Java chat system that uses Netty for server‑side networking, Swing for the client UI, and MySQL with C3P0 for data persistence, covering features such as user authentication, friend management, single‑chat messaging, and online status detection.
This article explains MySQL 8’s newly supported CHECK constraints, outlines usage restrictions, and provides step‑by‑step examples—including simple age validation and complex gender‑age logic—showing how to create, test, alter, and drop constraints while highlighting practical considerations.
This article explains how to diagnose MySQL client connection failures by checking whether the mysqld process is running, verifying network communication settings such as skip‑networking and bind‑address, and examining authentication or password issues via the error log.
This tutorial walks through adding Dataway to a Spring Boot project by importing Hasor and Dataway dependencies, configuring Maven, setting Dataway and datasource properties, creating a Hasor module to inject the Spring DataSource, enabling Hasor annotations, launching the application, and finally using the Dataway UI to define and publish SQL or DataQL‑based APIs.
Using sysbench, this study benchmarks MySQL 5.7.22 and MySQL 8.0.15 under read‑write, read‑only, and write‑only workloads with various sync_binlog and innodb_flush_log_at_trx_commit settings, revealing performance trends across different concurrency levels and highlighting that MySQL 5.7 often outperforms 8.0 in these tests.
This article explains MySQL's three lock levels—table, row, and page—describes next‑key, gap, and record locks, analyzes why deadlocks occur, and provides multiple real‑world examples and prevention strategies, including code snippets for reproducible scenarios.
This article explains the distinct purposes of MySQL's InnoDB redo log and server‑level binlog, how they work together during large update operations, the concepts of write position, checkpoint, and two‑phase commit, and why both logs are essential for durability and replication.
This article explains MyBatis-Plus's key features, shows how to add Maven dependencies, demonstrates the built‑in batch‑insert API, and provides step‑by‑step code to extend MyBatis‑Plus with a custom mapper and service for fast MySQL batch inserts.
The article explains MySQL 8.0.18's new hash join capability, shows how to enable and view it with EXPLAIN FORMAT=TREE, provides sample SQL and table definitions, compares its performance against block nested‑loop joins under various conditions, and discusses configuration limits and tuning tips.
This page continuously gathers and organizes over 200 Java backend interview Q&A articles, covering topics such as Spring, Redis, MySQL, concurrency, design patterns, and system design, providing a valuable resource for candidates to review before interviews.
This guide explains why MySQL queries become slow, how to diagnose issues with slow‑query logs, Explain and Show Profile, details the structure and types of indexes, interprets each column of the Explain output, and demonstrates practical optimization steps with real SQL examples.
This article investigates why MySQL 5.7 crash recovery can take hours on a development instance with thousands of tables, detailing stack‑frame analysis, GDB debugging, InnoDB source inspection of validation logic, performance testing, and methods to bypass the table‑space verification step.
This guide explains why MySQL reports 100% disk usage despite minimal data, shows how to identify excessive data_free, details step‑by‑step commands to defragment tables, free space, and safely backup and restore the database.
This article details how Vivo's cloud service tackled explosive data growth by applying horizontal and vertical sharding, routing‑table based dynamic expansion, and MySQL InnoDB compression, providing step‑by‑step guidance, performance results, and practical recommendations for large‑scale database deployments.
The article investigates the MySQL 5.7 sys schema ERROR 1356 caused by mysqldump --all-databases dropping mysql.proc, explains why the bug occurs, cites official documentation and bug reports, and provides four practical solutions including mysql_upgrade, separate sys backups, using --databases, and rebuilding from mysql‑sys.
This article provides a comprehensive overview of common MySQL attack techniques—including client‑side arbitrary file reads, SSRF‑based data extraction, server‑side file read/write, remote code execution vulnerabilities (CVE‑2016‑6662), and authentication bypass (CVE‑2012‑2122)—and supplies practical command examples and mitigation insights.
This article explains how MySQL stores indexes for the MyISAM and InnoDB engines, comparing clustered (primary) indexes and secondary indexes, illustrating their B+‑tree structures, showing SQL examples, and discussing the advantages and disadvantages of each storage method for database optimization.
This article compares MySQL and Oracle databases, covering their fundamental nature, security mechanisms, permission models, schema migration considerations, storage concepts, syntax variations, and practical differences such as primary keys, quoting rules, pagination, and data type handling.
This article explains how to determine whether a PHP string contains emoji characters, provides functions to detect and strip emojis using multibyte string functions, and discusses storing emoji‑containing strings in MySQL with utf8mb4 or base64 encoding.
The article explains how MySQL 8.0.22 enhances the util.import_table function with the decodeColumns option, allowing users to preprocess each row during import, replace load data statements, and customize column values directly within MySQL Shell.
