When you combine ORDER BY with LIMIT in MySQL, the result set may appear in a different order than expected, especially if the ordered column contains duplicate values, and understanding the optimizer's behavior and adding deterministic columns can resolve this issue.
This article provides a step‑by‑step guide to preparing the operating system, installing and configuring MySQL, tuning system parameters, and using a Bash one‑click deployment script to automate the setup and initialization of a MySQL 5.7.32 instance on Linux.
This article explains the data structures behind MySQL indexes, demonstrates how B+Tree indexes are built, and shows why the optimizer may choose full‑table scans over index scans in various scenarios such as pattern matching, OR conditions, type conversion, and NULL handling.
This article examines how MySQL binlog timestamps and the SET TIMESTAMP command affect the exec_time values of statements, especially the BEGIN event, through step‑by‑step experiments that reveal timing discrepancies and their impact on log accuracy.
This article explains how to list MySQL databases, perform full, multiple‑database, single‑database, and table‑specific backups using mysqldump, and restore them correctly, highlighting the differences when using the --databases option and common pitfalls.
This article explains what MySQL's slow query log is, how to configure and read it, and provides step‑by‑step procedures for safely clearing the log in both FILE and TABLE output modes to free disk space and maintain database performance.
The article explains how incorrectly placed quotation marks in MySQL UPDATE statements can cause expressions like "str_col=\"xxx\" = \"yyy\"" to evaluate to zero, leading to unintended data overwrites and highlights the importance of testing SQL in safe environments.
This article walks through building a Java Swing supermarket management desktop application, covering database table creation, user registration and login, product category and product CRUD operations, and the main menu UI, with complete source code snippets for each feature.
This article explains several efficient methods—including shell scripts, generated series tables, JSON functions, and MySQL 8.0 recursive CTEs—to quickly locate missing auto‑increment primary key values in a MySQL table, comparing their performance and suitability.
This article introduces MyBatis-Plus, explains its non‑intrusive features, demonstrates how to add Maven dependencies, examines the built‑in batch insert API, and provides step‑by‑step code to extend BaseMapper with a custom EasyBaseMapper for efficient MySQL batch inserts.
This tutorial walks you through using Flyway in a Spring Boot application to manage MySQL schema versions, from initial table creation and CRUD implementation to incremental migrations like adding new columns, complete with Maven dependencies, SQL scripts, Java code, and automated tests.
The article introduces go-mysql-transfer, a Go-implemented MySQL binlog incremental synchronization solution that avoids extra components, supports multiple downstreams like Redis, offers Lua scripting, Prometheus monitoring, high‑availability clustering, retry mechanisms, full data initialization, and provides installation, deployment, and performance testing details.
This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.
When selecting an open‑source database, enterprises must assess their specific data needs, understand licensing differences, and weigh the strengths and weaknesses of popular options such as MySQL, PostgreSQL, MariaDB, and SQLite, as this guide details.
This article details how to dramatically speed up the import of massive Excel files in a Java backend by switching to EasyExcel, caching database lookups, using batch inserts, and applying parallel streams while eliminating excessive logging.
This guide explains MySQL GTID concepts, how to enable GTID-based replication, configure the server, create replication users, initialize a slave, and verify replication using command-line examples and configuration steps, including transaction commit and rollback testing.
This article explains why SQL performance is critical for fast‑response web services, presents database‑agnostic optimization techniques—such as preferring EXISTS over IN, using JOINs, avoiding unnecessary ORDER BY, leveraging UNION ALL, and applying indexes—to dramatically reduce query latency and resource consumption.
This article describes a real‑world requirement to import over 2 billion records into MySQL, analyzes the challenges, introduces XeLabs TokuDB with its optimizations, details the test schema and configuration, demonstrates bulk loading commands, presents performance metrics showing up to 570 k rows per second, and concludes with practical recommendations and environment details.
This article explains MySQL's built‑in replication mechanism, covering its purpose, supported replication formats, step‑by‑step master‑slave configuration, common topologies, troubleshooting techniques, and practical SQL commands for setting up and maintaining reliable data distribution and high availability.
This article summarizes ICBC's experience of deploying nearly ten thousand MySQL nodes, detailing the current status, challenges, governance strategies—including standards, containerization, automated monitoring, emergency handling, and post‑incident analysis—and outlines future improvement plans for core‑application databases.
This article outlines Industrial and Commercial Bank of China's journey from early MySQL adoption to managing nearly ten thousand production nodes, detailing the challenges of high‑capacity, high‑concurrency workloads and the comprehensive governance framework—including standards, pre‑emptive checks, automated incident response, and future self‑healing plans—used to ensure reliable core‑application data storage.
This article explains how SELinux enforces mandatory access control on Linux, describes its three modes, shows how to view and modify SELinux contexts for MySQL processes and data directories, and provides step‑by‑step commands to add custom paths, logs, PID files, and ports while preserving system security.
This article presents a comprehensive set of mandatory and recommended conventions for MySQL table creation, SQL writing, and index design, aiming to improve backend development efficiency, reduce erroneous DDL and queries, and enhance overall database performance.
This article explains the role of MySQL binary logs for replication and point‑in‑time recovery, outlines four practical cleanup techniques—including reset master and purge commands—and demonstrates how to switch logs using flush operations while showing relevant command outputs and screenshots.
This article explains why MySQL's legacy utf8 charset cannot store four‑byte characters such as emoji, demonstrates the resulting insertion error, and shows how switching the database, table, and column collations to utf8mb4 resolves the issue while also covering the historical reasons behind MySQL's limited utf8 implementation.
This article compiles 52 practical SQL performance‑tuning strategies—including index best practices, query rewrites, use of temporary tables, proper data types, and server configuration tips—to help developers dramatically speed up MySQL and other relational database queries while avoiding common pitfalls.
The article explains the concept and requirements of primary keys in MySQL, outlines best practices such as avoiding updates and reuse, and discusses how primary keys serve as clustered indexes in InnoDB, highlighting the performance advantages of using business‑agnostic auto‑increment IDs over random keys.
The article explains the reasons behind rapid growth of MySQL slow query logs, introduces four key variables that affect logging, describes the decision process for recording queries, and provides practical verification scenarios to help DBAs fine‑tune slow‑log settings.
This article examines MySQL's recommendation to use auto_increment primary keys instead of UUID or random snowflake IDs by creating three tables, running insertion benchmarks with Spring Boot and JdbcTemplate, and analyzing how index structure affects performance and scalability.
This article demonstrates how to perform a full backup of a MySQL 8.0 database using mysqldump, drop and recreate the database, then restore it and apply incremental recovery with mysqlbinlog by specifying start and stop positions to recover data inserted after the backup.
This article compiles 52 practical SQL optimization strategies—including index usage, query rewriting, avoiding full table scans, proper data types, and server configuration tips—to help developers dramatically improve query speed and overall database performance.
This article explains why MySQL queries become slow by examining write‑operation issues such as dirty‑page flushing and locking, read‑operation problems like missing indexes and buffer‑pool evictions, and provides practical steps to diagnose and prevent these performance bottlenecks.
This article explains the complete MySQL architecture, covering its connection, service, storage‑engine, and system‑file layers, the role of each component such as connection pools, query cache, parser, optimizer, executor, and the differences between InnoDB and MyISAM, providing practical insights for developers and interview preparation.
This article explains how to use MySQL's SHOW FULL PROCESSLIST command, interpret its output, kill problematic threads, and address metadata lock issues by inspecting InnoDB transactions and adjusting lock_wait_timeout, providing practical code examples for database administrators.
This article details ICBC's extensive MySQL deployment—nearly ten thousand nodes supporting core A‑level applications—and outlines the bank's governance framework, including current challenges, standardized operational procedures, automation, containerization, and future self‑healing strategies to ensure reliable, high‑performance database services.
This article investigates a MySQL InnoDB deadlock observed during a holiday period, explains how gap locks and next‑key locks on a composite index cause mutual waiting, reproduces the issue with large test data, and offers practical guidelines to avoid similar deadlocks in production environments.
This article details a step‑by‑step optimization of a Java backend Excel import pipeline, covering data validation caching, batch insertion with MySQL VALUES, switching to EasyExcel, parallel stream insertion, and logging reduction to achieve sub‑minute import times for hundreds of thousands of rows.
This guide explains MySQL's three levels of user permissions, shows how to create accounts with various host specifications, demonstrates how to delete users, list existing accounts, and display a specific user's granted privileges using SQL statements.
This tutorial walks through installing MySQL 8.0 on two servers, enabling binary logging, creating a replication user, configuring master and slave settings, handling common errors, and verifying that master‑slave synchronization works correctly.
This article introduces MySQL index basics, covering index concepts, advantages and disadvantages, various index types such as B‑Tree, B+Tree, hash, and full‑text, their storage structures, creation syntax, left‑most prefix and covering indexes, and practical design guidelines for effective indexing.
This guide explains MySQL's built‑in replication, covering statement‑, row‑, and mixed‑mode replication, step‑by‑step master‑slave configuration, user creation, binary‑log settings, topology options, cloning methods, common error scenarios, and practical troubleshooting commands.
The article introduces my2sql, a Go‑written MySQL binlog parser that supports flashback, forward SQL generation, and transaction analysis, compares it with binlog2sql and MyFlash, details its command‑line parameters, demonstrates three practical scenarios, lists limitations, and summarizes its performance and usage advantages.
This tutorial walks through extracting the MySQL 8.0.17 package, creating required directories and users, configuring my.cnf, initializing the data directory, setting up startup scripts, adding MySQL to the system PATH, starting the service, and securing the root password on a Linux host.
This article introduces several MySQL 8 enhancements—including invisible indexes for performance testing, the SET PERSIST command for durable configuration changes, default UTF8MB4 encoding, Common Table Expressions for clearer queries, and window functions for ranking and aggregation—illustrated with practical SQL examples.
This article provides a comprehensive guide to MySQL replication, covering built‑in replication types, the three‑step replication workflow, detailed master‑slave configuration commands, topology options, cloning methods, binlog formats, replication files, filtering, and common error scenarios with solutions.
This article details a practical performance and high‑availability comparison of HAProxy and Nginx as software load balancers for a MySQL 8.0 InnoDB Cluster (MGR) setup, covering environment configuration, Keepalived integration, test methodology, results, and final recommendations.
This article walks through the design, pitfalls, and deep optimizations of a Node.js + TypeScript backend that periodically syncs enterprise WeChat organization data to a MySQL store and provides a phone‑number lookup API, offering practical solutions for rate limits, process management, database conflicts, and error recovery.
This article analyzes a MySQL master‑slave setup where a delayed replica experiences growing replication lag due to deadlocks caused by REPLACE INTO statements, examines the lock behavior, reproduces the issue in a test environment, and proposes configuration and coding mitigations.
This article explains how to perform MySQL performance benchmarking with the built‑in mysqlslap tool and the third‑party sysbench utility, covering configuration changes, command‑line options, data preparation, execution steps, and interpretation of the resulting metrics.
This tutorial explains MySQL benchmarking fundamentals and walks you through installing sysbench, configuring test parameters, preparing data, running the benchmark, analyzing results, and applying best‑practice recommendations to accurately assess database performance.
This article presents a comprehensive collection of 52 practical SQL performance optimization techniques, covering indexing, query rewriting, use of EXISTS, avoiding full table scans, proper handling of NULLs, OR/IN clauses, temporary tables, storage engine choices, backup procedures, and other best practices to improve database efficiency.
This article presents 21 practical SQL habits covering safe delete/update usage, testing changes, backups, transaction handling, performance tuning with EXPLAIN and indexes, concise SELECT statements, proper data types, batch operations, and clean coding conventions to make your database work more reliable and efficient.
This article introduces an OA office automation system built with Spring Boot, MySQL, Freemarker, and Bootstrap, outlines its project structure, front‑end and back‑end components, provides step‑by‑step deployment instructions, and shares screenshots and the repository link for developers.
This article explains a real‑world MySQL GROUP_CONCAT truncation problem caused by the default length limit, demonstrates how it affected a doctor‑scheduling system, and provides two practical solutions: increasing the group_concat_max_len setting or adjusting the business logic to reduce concatenated data.
When a MySQL table grows large, performance degrades sharply, so this guide explains single‑table tuning, proper field choices, index strategies, query best practices, engine differences, system parameters, hardware upgrades, read‑write splitting, caching layers, table partitioning, vertical and horizontal sharding, and how to choose suitable sharding solutions.
This article explains five common issues caused by NULL values in MySQL columns—data loss with COUNT, DISTINCT, and inequality queries, NULL‑induced NULL results in SUM leading to NPE, increased query complexity, and index behavior—while providing practical SQL solutions and best‑practice recommendations.
This article explains MySQL transaction fundamentals, covering ACID concepts, isolation levels, MVCC implementation, transaction start methods, and practical examples that illustrate how InnoDB ensures atomicity, consistency, isolation, and durability while avoiding common pitfalls such as dirty reads and long‑running transactions.
This article walks through a MySQL 5.6 scenario where a query that originally took over 30,000 seconds is optimized using proper indexing, sub‑query to join conversion, and composite indexes, demonstrating dramatic performance gains and offering practical tips for future query tuning.
This article explains how MySQL implements hash indexes, illustrates their data distribution on memory tables, and evaluates their suitable scenarios and limitations compared with B‑tree indexes through detailed SQL examples and performance analysis.
This article provides a step‑by‑step guide on configuring MySQL binlog, installing and deploying Oracle GoldenGate, extracting changes, converting them to JSON, and streaming the data into Kafka for real‑time processing, complete with code snippets and verification procedures.
This article explains how to use Percona Toolkit's pt-table-checksum tool to detect and verify data consistency between MySQL master and replica servers, covering its principles, suitable scenarios, safety mechanisms, and step‑by‑step commands for setup, data generation, and checksum execution.
This article provides a step‑by‑step guide on configuring the MySQL CDC connector for Flink, covering Maven and SQL client dependencies, MySQL user setup, connector options, table creation via SQL and Stream API, key features, common issues, and practical troubleshooting tips.
This article explains MySQL’s essential logging mechanisms—binary log, redo log, and undo log—covering their purposes, formats, flushing strategies, usage in replication and recovery, and how they interact to ensure data durability and consistency.
This guide explains how to enable and configure MySQL binary logging, examine its parameters, understand event formats, view and filter binlog contents, and extract logs using positions, timestamps, or GTIDs for backup, replication, and troubleshooting purposes.
This tutorial explains how to create a Python‑based eBPF tool using the bcc framework to trace MySQL Group Replication's apply_data_packet function, covering environment setup, BPF program writing, attaching probes, and displaying real‑time thread and timestamp information.
This article demonstrates how to create two MySQL tables, populate them with sample data, and use various SELECT statements—including inner joins, filtered joins, and UNION operations—to retrieve combined information across the tables.
This guide walks through building a comprehensive MySQL monitoring platform using Prometheus and Grafana, covering exporter installation, configuration, key performance metrics such as replication health, query throughput, slow queries, connection limits, buffer pool usage, and provides ready‑made Grafana dashboards and alerting rules.
This guide explains how to boost MySQL performance through soft optimizations like query analysis, index usage, and table restructuring, as well as hard optimizations involving hardware upgrades, configuration tuning, sharding, read‑write splitting, and cache clustering.
This article explains how MySQL decides whether an index can be used for range scans, LIKE patterns, and ORDER BY operations, detailing key_len calculation, index key vs. filter vs. table filter distinctions, and providing concrete examples and code snippets for better query optimization.
This guide lists useful MySQL client shortcuts, explains the help command usage, provides common mysqladmin administration examples, and shows how to diagnose high CPU usage by tracing the offending SQL statements through process and performance schema queries.
This comprehensive guide explains MySQL's server and storage engine layers, SQL execution order, BinLog/RedoLog/UndoLog mechanisms, index structures and design principles, transaction isolation levels, lock types, MVCC, buffer pool behavior, table slimming, join strategies, statistics, random queries, and practical optimization techniques for high‑performance database operations.
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.
