This article examines common MySQL performance pitfalls—such as large OFFSET limits, implicit type conversion, inefficient UPDATE/DELETE joins, mixed ordering, EXISTS subqueries, and poor condition push‑down—and demonstrates concrete query rewrites that reduce execution times from seconds to milliseconds.
This tutorial demonstrates how to replace the traditional VIP‑based MySQL Router deployment with DNS SRV and Consul service discovery, enabling automatic failover and load balancing for MySQL InnoDB Cluster using supported MySQL Connectors and a Python client.
This article explains the essential role of MySQL REDO logs in ensuring data durability and performance, describes what would happen without logging, details various REDO log record formats, and outlines the multiple moments when InnoDB flushes logs to disk.
This article presents eight practical MySQL optimization strategies—including LIMIT redesign, implicit type conversion fixes, JOIN rewrites for updates/deletes, mixed sorting tricks, EXISTS elimination, condition push‑down, early data reduction, and intermediate result set push‑down—each illustrated with before‑and‑after queries, execution plans, and measurable speed improvements.
This article explains the purpose and management of InnoDB REDO log files, the role of Log Sequence Numbers, the structure of log pages, the MTR physical transaction mechanism, and how checkpoints ensure data integrity in MySQL databases.
This guide presents comprehensive MySQL best‑practice rules covering object naming conventions, fundamental schema design, field type selection, index creation strategies, and SQL development habits to improve performance, maintainability, and reliability of production databases.
This article explains how to use Alibaba's Canal to capture MySQL binlog changes in real time, covering its underlying protocol, component architecture, HA design with ZooKeeper, configuration steps, deployment examples, and a detailed comparison with alternative tools such as Maxwell and mysql_streamer.
This article explains how to identify and remove various kinds of duplicate data in MySQL—including fully duplicated rows, records with duplicate non‑key columns, and unwanted whitespace inside fields—by using SQL statements, table cloning, OS utilities, and regular‑expression updates, with performance measurements for each method.
This article explains the theory behind InnoDB's log management, details the architecture and dynamic sizing of the Buffer Pool, describes its internal data structures and multi‑instance implementation, and provides annotated source code snippets to illustrate how MySQL allocates and manages buffer pages.
A production MySQL query on a table with five million rows took over 30 seconds, and after reproducing the issue, examining execution plans, trying various index and query tweaks, discovering that SQLyog adds a hidden LIMIT, and finally forcing the correct index, the runtime dropped to under a second, illustrating the importance of proper index usage and tool‑specific quirks.
This article details the design principles, implementation steps, and code examples for a lightweight transactional message module in a microservices environment, leveraging Spring Boot, RabbitMQ, MySQL, and Redisson to achieve low‑intrusion, eventual‑consistency messaging with compensation and retry mechanisms.
This article explains the concept of a DataSource, compares it with core JDBC APIs, and provides a step‑by‑step guide to configuring Spring Boot 2.x’s default HikariCP connection pool, including common properties, their meanings, and links to full example projects.
A detailed performance comparison shows that redirecting a huge MySQL dump directly into the client process is roughly three times faster than piping the file through cat, due to fewer system calls and context switches.
This article presents a thorough set of MySQL best‑practice guidelines covering object naming conventions, basic table design, column definitions, index creation, index usage policies, SQL coding standards, and operational behaviors to improve performance, maintainability, and data integrity.
The article examines the 2019 DB-Engines ranking that crowned MySQL as the DBMS of the Year, discusses its historical development, compares it with Oracle and SQL Server, analyzes the rise of NoSQL systems, and explores the implications of hybrid features in modern relational databases.
This guide walks you through configuring data sources in Spring Boot, adding JDBC dependencies, using embedded databases, connecting to MySQL, defining entities and repositories with JdbcTemplate, and writing comprehensive unit tests to perform CRUD operations, illustrating a complete backend data access workflow.
This tutorial demonstrates how to use Python to read dozens of txt files, write millions of records into a MySQL database, clean duplicate entries, and export selected rows to new files, offering a fast, scriptable alternative to manual Excel processing.
This article documents a MySQL slow‑query case where a 5‑million‑row table took over 30 seconds to run a GROUP BY query, explores several ineffective optimization attempts, reveals a client‑side limit issue, and finally solves the problem by forcing the correct index, cutting the runtime to under 0.2 seconds.
The article explains how using LIMIT with a large offset on a MySQL table forces the engine to scan hundreds of thousands of index rows and cluster‑index pages, dramatically increasing I/O, and shows a sub‑query join technique that reduces the scan to just the needed rows, cutting execution time from minutes to fractions of a second while also illustrating the impact on the InnoDB buffer pool.
This tutorial walks you through installing the PyMySQL library, configuring connection parameters, establishing a Python‑MySQL link, and performing full CRUD operations—including creating tables, inserting, querying, updating, and deleting records—while also covering cursor methods and pandas integration.
This tutorial demonstrates how to enable, configure, and analyze DBLE’s slow query log—compatible with MySQL’s mysqldumpslow and Percona’s pt‑query‑digest—by generating sample queries, examining log rotation, and using the tools to inspect performance details, while also providing community resources and course information.
This article analyzes a slow MySQL LEFT JOIN query, explains why the optimizer chose the inefficient Block Nested Loop algorithm instead of Index Nested Loop, shows how character‑set and collation differences cause index loss, and demonstrates how converting to INNER JOIN or fixing indexes restores high performance.
This article demonstrates how rewriting a MySQL LIMIT OFFSET query to first fetch primary keys in a sub‑query and then join the main table can cut execution time from over 16 seconds to under one second by avoiding massive random I/O and buffer‑pool pollution.
The article explains how to overcome performance degradation in DBLE‑based pagination by using a constant LIMIT clause, filtering with id‑based conditions, and adhering to sharding‑key constraints, while outlining practical steps, limitations, and best‑practice recommendations for continuous page navigation.
This guide explains how to use Xiaomi's open‑source Gaea middleware to achieve automatic MySQL read/write separation, covering Go environment setup, Gaea compilation, configuration files, Docker deployment, testing procedures, and integration with SpringBoot.
This article explains the concept of Hash Join in MySQL 8.0, compares it with Nested Loop joins, shows how to enable or force it with server variables or hints, and presents performance benchmarks that demonstrate its speed advantages on large datasets.
The article explains MySQL InnoDB’s four transaction isolation levels, how the default Repeatable Read is realized through MVCC versioning and snapshot reads, and details the three lock types—record, gap, and next‑key—illustrating their roles in preventing dirty, non‑repeatable, and phantom reads.
This guide provides a comprehensive overview of MySQL 5.7 monitoring techniques, covering connection parameters, long‑running transactions, metadata lock tracing, lock‑wait analysis, global read locks, memory usage, partitioned tables, database size summaries, table update status, primary key and index considerations, storage engine distribution, and real‑time load metrics.
MySQL 8.0.19 introduces InnoDB ReplicaSet, Router enhancements, numerous SQL syntax additions, optimizer improvements, new Information Schema role views, security features, replication options, X Protocol compression, and a range of deprecations and bug fixes, providing a comprehensive update for database administrators and developers.
This comprehensive guide explains the motivations, methods, and trade‑offs of database sharding—including vertical and horizontal partitioning, sharding rules, handling cross‑node joins, distributed transactions, global primary key generation, and practical migration strategies—so you can scale relational databases effectively.
This article explains the principles of MySQL master‑slave replication and provides a detailed, step‑by‑step tutorial for configuring a master and a slave instance in a Docker environment, including configuration files, Docker commands, replication setup, testing, and troubleshooting.
This article explains why using an index does not guarantee a fast query in MySQL, analyzes how slow‑query detection works, demonstrates full‑index scans, discusses index selectivity, the cost of row‑lookup (back‑table) operations, and shows how virtual columns and index condition push‑down can improve performance.
Facing MySQL performance bottlenecks at massive scale, Xiaomi built the LCSBinlog service that captures MySQL binlog events, streams them through the Talos platform, and writes to Kudu for real‑time BI, detailing architecture, job scheduling, consistency guarantees, use cases, and troubleshooting lessons.
This weekly digest summarizes the latest MySQL community news, including a Microsoft Access security flaw, phpMyAdmin 4.9.4/5.0.1 releases, DBLE and TXLE middleware weekly reports, and a call for community feedback on DBLE, DTLE, and TXLE usage.
This article reveals how open‑source software like MySQL powers critical systems—from hospital patient records to massive retail operations—illustrating that a single code error or missing backup can affect tens of millions of people worldwide, underscoring the profound responsibility of open‑source contributors.
This article introduces Vitess, a cloud‑native MySQL sharding middleware, explains its key features, architecture, core concepts such as cells, keyspaces, shards and vindexes, and provides a step‑by‑step guide to performing a reshard from two to four shards while highlighting operational challenges and practical recommendations.
The 2019 DB-Engines ranking crowned MySQL as the most popular DBMS, with Oracle and Microsoft SQL Server following, and discusses how relational databases dominate while NoSQL systems remain vital, highlighting evolving features and the implications for future database technology choices.
This article explains how MySQL X API (available since MySQL 5.7) can replace NoSQL databases by enabling the X protocol, showing configuration steps, equivalent insert examples, and detailed CRUD, indexing, and transaction operations with code snippets.
This article compiles a comprehensive set of Linux operations interview questions and answers covering fundamentals such as the definition of ops, RAID levels, load‑balancing tools, middleware, MySQL troubleshooting, scripting, network diagnostics, and best‑practice optimization techniques for system administrators.
This guide introduces several Percona Toolkit command‑line tools—pt‑archiver, pt‑config‑diff, pt‑heartbeat, pt‑variable‑advisor, and pt‑pmp—explaining their purpose, providing example commands for data archiving, configuration comparison, accurate replication lag measurement, variable optimization suggestions, and stack‑trace debugging, plus a quick installation snippet.
The article explains that MySQL’s legacy “utf8” charset only supports three‑byte characters, causing errors when storing true four‑byte UTF‑8 data, and shows how switching to the proper “utf8mb4” charset resolves the issue.
Learn step-by-step how to query MySQL 5.7 and 8.0 to list the top three orders by amount for each month, using variables and ranking functions, with full SQL examples, explanations, and visual results.
This article explains why relational databases become bottlenecks at large scale, introduces vertical and horizontal sharding techniques, discusses the challenges of distributed transactions, cross‑shard queries, pagination, and global primary‑key generation, and provides practical guidelines and middleware options for implementing sharding in production systems.
This weekly newsletter summarizes the 2019 DB-Engines MySQL award, highlights community news on DBLE, DTLE, and TXLE middleware, and presents technical articles on query field count impact, count(*) vs count(column) differences, and improving RDS monitoring aesthetics.
This article walks through iQIYI's practical approach to database selection, covering key evaluation dimensions, a taxonomy of SQL/NoSQL and OLTP/OLAP workloads, detailed optimizations for MySQL, Redis, Couchbase and the internally built HiKV, as well as multi‑stage operational management and concrete selection recommendations.
This article explores common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, subquery handling, and mixed sorting, and demonstrates eight practical rewrite strategies—including index‑friendly pagination, JOIN replacements, condition pushdown, and WITH clauses—to dramatically reduce query execution times from seconds to milliseconds.
This article explains how MySQL executes COUNT(*) and COUNT(column) queries on InnoDB tables, showing example results, detailing each layer's processing steps, the handling of NULL values, and the subtle performance differences between the two forms.
This article analyzes ten typical situations that cause MySQL indexes to become ineffective—such as OR conditions, missing quotes, leading wildcards in LIKE, composite‑index ordering, built‑in functions, arithmetic operations, inequality operators, NULL checks, charset mismatches, and optimizer choices—and provides concrete examples, visual explanations, and practical solutions to restore index usage.
This article presents eight practical MySQL optimization techniques—including smarter LIMIT usage, implicit type conversion fixes, join rewrites, mixed sorting, EXISTS elimination, condition pushdown, early row limiting, and intermediate result pushdown—each illustrated with SQL examples and performance comparisons that reduce query times from seconds to milliseconds.
This article explains how MySQL processes a full‑table‑scan query, detailing the construction of read‑sets, template creation, cursor positioning, row conversion to MySQL format, where‑clause filtering, and subsequent row iteration, while highlighting the performance impact of the number of selected fields.
This article explains the three InnoDB Buffer Pool page types—Free, Clean, and Dirty—their associated LRU, Flush, and Free linked lists, and how MySQL uses configurable parameters to balance memory usage, query speed, and data safety during flushing and checkpoint operations.
This article provides a thorough overview of MySQL optimization, covering the underlying philosophy, risk considerations, participants, optimization scope across hardware, system, application, and database layers, practical tools, command‑line techniques, and detailed tuning recommendations for both system and database parameters.
This article explains what database indexes are, why they are needed, the various index types and their characteristics, guidelines for choosing columns to index, and compares the performance of hash and B‑tree indexes in MySQL.
This article analyzes how MySQL/InnoDB applies row-level locks for DELETE and SELECT statements under various index configurations and isolation levels, explaining MVCC, current vs. snapshot reads, two‑phase locking, gap and next‑key locks, and the impact of primary, unique, non‑unique, and missing indexes.
This article examines the MySQL C API option MYSQL_OPT_READ_TIMEOUT, comparing official documentation that claims a three‑times timeout with empirical tests and source‑code analysis across MySQL 5.7.20, 5.0.96, and later versions, revealing outdated documentation and a historic bug.
This article presents concrete MySQL optimization techniques—including smarter LIMIT usage, avoiding implicit type conversion, rewriting updates and deletes with JOINs, handling mixed ordering, replacing EXISTS with joins, pushing down conditions, and leveraging WITH clauses—to dramatically reduce query execution times from seconds to milliseconds.
This article explains a second method for implementing ER tables in DBLE that avoids configuring child_table, demonstrates how tables with identical split columns, distribution nodes, and algorithms can act as ER tables, and shows practical JOIN and EXPLAIN query examples.
This article provides a comprehensive overview of the MySQL Test Run (MTR) framework, detailing its two-part architecture consisting of Perl scripts for control flow and C++ binaries for test execution, along with the complete execution process and framework design.
This article explains the performance drawbacks of using UUIDs as primary keys in MySQL InnoDB tables, analyzes their impact on storage, I/O and CPU, and presents several mitigation strategies such as time‑ordered UUIDs, integer mapping, and alternative identifier schemes.
This article introduces the four main MySQL transaction types—ordinary, chain, nested (simulated), and autonomous (simulated)—explaining their behavior and providing concrete SQL examples that demonstrate how each type works in practice.
This tutorial walks you through using Python to crawl Tmall lingerie product and comment data, store it in MySQL, clean and analyze cup‑size distribution and popular colors, and finally visualize the results with pyecharts.
This article explains the fundamentals of LEFT JOIN in SQL, illustrates one‑to‑one, one‑to‑many, and many‑to‑many scenarios, compares ON versus WHERE conditions, and provides concrete MySQL and Hive examples with code snippets and visual diagrams to avoid common mistakes.
This article chronicles Dada's rapid growth from a single‑database backend to a multi‑layer architecture that employs MySQL read/write separation, vertical partitioning, and horizontal sharding with a custom ID generator to sustain millions of daily orders while maintaining performance and stability.
This article compares key new features of MySQL 8.0 and MariaDB 10.4—including authentication plugin changes, Unix‑socket auth, atomic DDL, instant column adds, clone plugin usage, optimizer enhancements, resource groups, query‑rewrite, and backup tools—providing step‑by‑step commands, code snippets, and practical caveats for database upgrades.
This article discusses iQIYI’s approach to database selection, covering evaluation criteria, the variety of databases used—including MySQL, TiDB, Redis, Couchbase, and the in‑house HiKV—along with detailed optimization, high‑availability, auditing, and operational management techniques for each system.
This article tackles the challenge of ordering hard‑coded role levels in a MySQL table by presenting ten distinct SQL solutions—including adding a helper column, using FIELD, FIND_IN_SET, CASE, subqueries, UNION, hash‑based formulas, and custom arithmetic—so that a "vice‑manager" (level 2) can be ranked above a "manager" (level 3).
This article explains MySQL's three‑layer logical architecture, the end‑to‑end query execution flow—including client/server protocol, query cache, parsing, cost‑based optimization, execution engine, and result delivery—followed by practical performance‑tuning advice on schema design, data types, index creation, and specific query optimizations such as COUNT(), JOINs, LIMIT pagination, and UNION handling.
This tutorial walks through setting up a Kunpeng development environment, building a simple Hello World program, creating a cross‑platform CMake project, and compiling, installing, and packaging MySQL 5.7 as RPM/DEB packages for CentOS 7 and Ubuntu 18.04.
This article investigates the claim from Alibaba's Java Development Manual that joining more than three tables should be avoided, by setting up a MySQL 5.7 environment, generating massive synthetic data, executing multi‑table join queries, analyzing execution times, and comparing the results with Oracle, ultimately revealing the practical limits of MySQL joins on large data sets.
This article introduces the open‑source infobin utility for parsing MySQL binary logs, explains how it detects long‑running and large transactions, measures event generation speed, aggregates per‑table DML events, and demonstrates its usage with sample commands and output, offering a faster alternative to mysqlbinlog.
In this interview, Ming Xiyuan, Technical Solutions Director at iKangSheng, shares practical guidance on selecting and deploying high‑availability MySQL solutions for the financial sector, covering planning pitfalls, hardware considerations, key configuration parameters, and the company’s product roadmap.
This article explains the reasons for splitting databases, describes vertical and horizontal sharding techniques, compares their benefits and drawbacks, and advises performing horizontal sharding before vertical partitioning to improve scalability and performance in large‑scale applications.
This article examines a MySQL instance that hit 100% CPU at 9 am on 2019‑01‑11, identifies the offending ORDER BY query through slow‑log and EXPLAIN analysis, and demonstrates how adding a composite index and correcting implicit type conversions reduced execution time from over 10 seconds to milliseconds.
This article explains how MySQL composite (multi‑column) indexes work, the left‑most prefix rule, demonstrates queries where the index is used or ignored, and lists common situations that cause index failure along with practical optimization tips.
This tutorial walks through loading order data into pandas and SQL, then demonstrates how to retrieve current dates, extract date components, convert between readable dates and Unix timestamps, transform between 10‑digit and 8‑digit date formats, and perform date arithmetic using pandas, MySQL, and Hive.
The article explains that MySQL's count(1) and count(*) perform identical full‑table scans with negligible performance difference, while count(column) only counts non‑NULL values, and it discusses execution‑plan effects and optimal usage depending on primary keys and table size.
This article explains how MySQL InnoDB implements indexes with B+ trees, describes primary and secondary (clustered and non‑clustered) indexes, the concepts of row lookup, covering indexes, composite indexes, the left‑most prefix rule, index push‑down, and provides practical guidelines for creating and maintaining efficient indexes.
This guide walks you through setting up the open‑source MySQL Monitor tool, covering environment preparation, installation, database schema import, host configuration, alert channel setup (email and WeChat), cron scheduling, and accessing the monitoring pages, all with a clean Chinese interface.
This article explains iQIYI's multi‑dimensional approach to database selection, details the practical use and optimization of MySQL, TiDB, Redis, Couchbase, and the internally built HiKV, and offers concrete recommendations for choosing the right database in different scenarios.
This article explains the internal calculation of the Time column shown by MySQL's SHOW PROCESSLIST command, covering the source functions, overloads of THD::set_time, various scenarios that affect the result—including replication lag, manual timestamp settings, and idle sessions—and why Percona and official builds may display different values.
This article explains MySQL's logical architecture, query execution steps, client‑server protocol, query cache behavior, cost‑based optimizer mechanics, and practical index design strategies—including B+Tree fundamentals, covering indexes, and common pitfalls—to help developers achieve measurable performance improvements.
This article explains how using a large OFFSET in a MySQL LIMIT clause forces the server to scan hundreds of thousands of index and data pages, causing massive random I/O, and demonstrates a faster rewrite with an inner join that dramatically reduces buffer‑pool usage and execution time.
This weekly report summarizes the latest DBLE, DTLE, and TXLE middleware releases, bug fixes, community Q&A, documentation updates, and previews of upcoming features, providing developers with concise information on new versions, resolved issues, and future development plans.
This guide explains step‑by‑step how to configure Zabbix database high availability using MariaDB master‑master replication, GTID‑based binlog synchronization, Keepalived virtual IP failover, and associated backup and monitoring scripts for reliable production deployment.
This article presents three common MySQL interview questions—whether query fields are case‑sensitive, how InnoDB implements transactions and logs, and the different binlog formats—along with detailed explanations, configuration examples, and SQL code snippets for each scenario.
The DTLE 2.19.11.0 release notes detail the project's purpose as a MySQL‑focused data transfer component, outline new features such as binlog relay and MySQL 8.0 support, list numerous bug fixes, and provide links to documentation and download resources for developers.
Learn how MySQL 8’s hash join algorithm works by building an in‑memory hash table from the smaller table, probing the larger table for matches, and handling oversized tables through disk‑based chunk files, offering a more efficient alternative to traditional nested‑loop joins.
This guide introduces the dbdeployer tool, walks through its installation on macOS/Linux, shows how to configure sandbox paths, download MySQL binaries, and deploy various MySQL topologies such as single nodes, master‑slave replication, and group replication with one‑click commands.
The DBLE 2.19.09.0 release notes introduce eight new refactoring features, 34 bug fixes, external MySQL‑HA interface commands, a dump‑file split utility, detailed translation of all changes, and compatibility updates for the open‑source distributed database middleware.
The article demonstrates how to enrich Flink streaming ETL jobs with slowly changing dimension data by periodically loading MySQL tables into an in‑memory cache and performing a simple map‑side join within a custom RichMapFunction implementation.
This tutorial demonstrates how to install DBLE using a Docker‑based MySQL environment, configure its XML templates to connect to two MySQL instances, start the DBLE service, and manage databases and tables through the DBLE console, including practical code examples.
This article explains how to use MySQL's EXPLAIN command, describes each column in its output—including id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, and Extra—provides syntax examples, discusses join type rankings, and outlines the command's limitations for query performance analysis.
A MySQL 5.6 database with three large tables suffered a 30,000‑second query when searching for students scoring 100 in a specific subject; by analyzing the execution plan, adding indexes on filter columns, and rewriting the query as a join, execution time dropped to under a second.
This article explains how a growing company designed, optimized, and deployed a comprehensive MySQL monitoring and analysis pipeline—moving from Flume‑HDFS‑Hive to ClickTail‑ClickHouse, enriching SQL parsing, and applying practical methods for state statistics, trend analysis, permission management, and data‑skew detection.
This article walks through a real‑world MySQL performance case where a nested sub‑query on millions of rows took over eight hours, explains why the plan used full table scans, and shows step‑by‑step index creation and query rewrites that cut the execution time to a millisecond.
This article explains how to quickly set up MySQL (as well as MariaDB and Percona) inside Docker containers, covering official images, running the latest version, connecting to the server, managing container lifecycle, passing server options, and handling multiple or specific MySQL versions.
The article explains MySQL logical and physical backup methods, compares online and offline backup approaches, details the advantages and disadvantages of each, and provides practical mysqldump commands and master‑slave backup techniques for reliable database protection.
This article examines common MySQL performance pitfalls such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed sorting, and unpushed predicates, and demonstrates eight practical rewriting techniques—including using max values, JOINs, WITH clauses, and early range reduction—to dramatically accelerate query execution.
The article explains why the Alibaba Java Development Manual restricts joins to three tables, discusses MySQL’s join algorithm limitations, and offers practical alternatives such as query decomposition, denormalization, and using IN or hash joins to improve performance.
This article explains how MySQL 8.0.18 introduces the hash join algorithm, describes its build and probe phases, disk‑overflow handling, usage instructions, optimizer switches, performance comparisons with block nested loops, and current limitations, providing code examples and practical guidance.
