This article explains why B+ trees are the dominant data structure for MySQL indexes, compares them with hash tables, linked lists, and skip lists, and details page splits, merges, and how index values map to row records, helping readers master high‑frequency interview questions.
This article provides an in‑depth analysis of MySQL's filesort mechanism, explaining the original and modified algorithms, how sort and addon fields are calculated, memory allocation strategies, the role of temporary files, and practical tips to reduce excessive disk usage during sorting operations.
This article presents a detailed MySQL performance benchmark of Alibaba Cloud RDS, Tencent Cloud RDS, and AWS RDS using sysbench, analyzes configuration differences, shows results across mixed, read‑only, and no‑index update workloads, and offers practical recommendations for cloud database tuning.
This article presents thirty actionable SQL performance recommendations—including selecting specific columns, using LIMIT, avoiding OR and functions in WHERE clauses, proper indexing, batch inserts, and query planning—each illustrated with clear examples and explanations to help developers write faster, more efficient queries.
This article provides a comprehensive overview of MySQL data types—including numeric, floating‑point, decimal, string, date/time, binary, bit, enum, and set types—explaining their storage characteristics, appropriate use cases, and performance implications through practical examples and SQL demonstrations.
This article explains the principles of pessimistic concurrency control in relational databases, details the conditions under which MySQL's SELECT … FOR UPDATE works, compares it with optimistic locking, provides concrete SQL examples, and demonstrates lock behavior through multiple transaction test scenarios.
This guide explains how to install Percona XtraBackup 2.4, perform hot full backups of MySQL 5.7 databases, compress and transfer them, and configure master‑slave replication using innobackupex and standard MySQL commands.
This guide explains what the MySQL Test framework is, how to set up the required environment on Ubuntu, install dependencies and MySQL source, configure and compile the test suite, and demonstrates creating and running a simple test case with detailed command‑line examples.
When a MySQL table grows to millions of rows, query, insert, update and delete performance degrades sharply, but by applying proper field design, index strategies, SQL best practices, engine selection, system parameters, caching, partitioning, vertical and horizontal sharding, and choosing suitable client‑side or proxy‑side architectures, the database can efficiently handle tens of millions of records.
Yearning is an open‑source MySQL audit platform that offers query auditing, SQL review, and workflow automation, providing a web‑based interface for managing tickets, permissions, and notifications, with detailed installation and configuration instructions for quick deployment.
This guide introduces dtle, an open‑source MySQL data‑transfer middleware, covering download, installation on three nodes, detailed dtle.conf configuration, startup commands, creating source and target users, preparing job.json, using HTTP API to create and monitor migration jobs, and verifying data migration results.
This article examines why a MySQL query with a large OFFSET is extremely slow, demonstrates the performance gap with a join‑based rewrite, and validates the findings by comparing InnoDB buffer‑pool page usage, highlighting how unnecessary random I/O pollutes the cache.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.
This guide introduces the open‑source macOS MySQL client Sequel Pro, explains its interface and SSH security options, and provides two practical solutions—including a test‑build download—to resolve the frequent crashes when connecting to MySQL 8, enabling stable database management.
Yearning is an open‑source MySQL SQL audit platform that offers query auditing, workflow‑driven review, execution, rollback, fine‑grained permission control and notification integrations, with detailed installation steps and a public Gitee repository for easy deployment.
This weekly community roundup presents the latest DBLE and TXLE open‑source developments, highlights new MySQL features and diagnostic tools, shares bug‑fixes and upcoming plans, and invites readers to contribute feedback and questions about the distributed database middleware ecosystem.
Through a series of experiments using MySQL's performance_schema and explicit MEMORY tables, this article demonstrates how internal temporary tables consume memory, reveals that they allocate significantly more space than raw data size, and discusses the implications for accurate memory estimation.
This article explains how MySQL computes replication delay using show_slave_status, analyzes the relationship between binlog write timestamps and event generation, enumerates common causes of lag, and provides a detailed examination of the system‑lock state in the slave SQL thread with code examples.
This article examines various MySQL pagination methods—including simple LIMIT, offset impact, sub‑query, ID‑range, and temporary‑table strategies—provides detailed performance measurements on a table with over five million rows, and offers practical recommendations for speeding up large‑scale data retrieval.
This article explores the design of a high‑performance short‑link system, covering its benefits, redirection mechanics, hash‑based and auto‑increment generation methods, database schema, Bloom filter optimization, and a scalable OpenResty‑based architecture for handling massive traffic.
This article explains InnoDB's logical storage hierarchy—tablespace, segment, extent, page—then details MySQL partitioning concepts, types, example DDL, performance impacts, and best‑practice tips for OLTP and OLAP workloads.
MySQL 8.0.19 adds two novel DML statements—TABLE for convenient full‑table scans and sub‑query replacements, and VALUES for generating in‑memory row sets—each with clear syntax, practical examples, and execution‑plan insights that help developers quickly adopt these features.
This article introduces BPF/eBPF fundamentals, explains the bcc toolkit, provides installation steps for Ubuntu and CentOS, and demonstrates how to use bcc's dbstat and dbslower utilities to trace MySQL query latency and slow queries on Linux systems.
This guide introduces four open‑source MySQL diagnostic utilities—MySQLTuner, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run them against a live server, interpret the generated AWR‑style reports, and apply the recommended configuration changes for better stability and speed.
Learn step‑by‑step how to set up multiple MySQL data sources in Spring Boot 2.2.2, configure corresponding JdbcTemplate beans, define entity and service layers, and run unit tests, with full Maven dependencies, property settings, Java configuration, and SQL DDL examples.
This article explains how Linux memory statistics (free, top) report buffer/cache usage, how to clear it with sync and drop_caches, and then dives into SQL join concepts, types, optimization techniques, and MySQL join algorithms such as nested loop and block nested loop, highlighting their impact on performance.
A detailed reference covering MySQL service management, connection commands, database and table operations, data types, indexes, joins, subqueries, transactions, backup, user privileges, and common SQL functions, complete with practical code examples.
This article explains key Linux operations concepts such as gray release deployment, DNS resolution steps, RabbitMQ messaging, Keepalived high‑availability mechanisms, LVS load‑balancing modes, MySQL lock troubleshooting, replication lag mitigation, root password reset, backup tools, and compares Nginx, LVS, and HAProxy.
When deploying a Spring Boot application on CentOS, MySQL connections may throw CommunicationsException or SSLHandshakeException due to JDK mismatches, socket path errors, SSL settings, timeout defaults, or configuration issues, and this guide explains each cause and provides concrete fixes.
This guide introduces four key MySQL optimization utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—explains how to download, install, and run each tool, and shows how to interpret their health‑check reports to identify performance bottlenecks, configuration issues, and security risks.
This weekly DBLE community newsletter, authored by reporter Yongzheng, compiles curated MySQL and DBLE resources, outlines recent open‑source developments, bug fixes, community Q&A, and upcoming plans for both DBLE and TXLE middleware projects.
This comprehensive guide covers essential Linux fundamentals, common interview questions, and practical server administration techniques, including boot sequences, runlevels, file system differences, process management, packaging tools, Nginx optimization, MySQL replication, Docker usage, and useful shell scripts for real‑world operations.
This guide shows how to generate hourly slow‑query reports from MySQL slow‑log files by using pt‑query‑digest’s timeline feature, filtering with sed, importing the data into SQLite with termsql, and then querying the results to identify hotspot periods and recurring patterns.
The article examines why a 100 GB MySQL 5.6 transaction causes high CPU usage during lock_number_of_rows_locked calculation, compares the lock‑counting implementations of MySQL 5.6 and 5.7, and offers diagnostic steps and version‑upgrade recommendations to mitigate the bottleneck.
This article explains the distinctions between UNION and UNION ALL, TRUNCATE and DELETE, TIMESTAMP and DATETIME, introduces composite and covering indexes, describes InnoDB versus MyISAM storage, the left‑most prefix rule, monotonic primary keys, int display length, SHOW INDEX fields, handling LIKE patterns, and efficient pagination techniques.
This tutorial explains MySQL 8.0 role concepts, demonstrates how to create roles, assign them to multiple users, activate default roles, configure role parameters, and revoke roles, providing step‑by‑step examples with complete MySQL commands and explanations for effective privilege management.
This article explains MySQL storage engines, compares MyISAM and InnoDB, introduces the main index types—B‑Tree, Hash, Full‑text, R‑Tree—details the structures and characteristics of B‑Tree, B‑Tree and B+Tree, and outlines key principles for creating effective indexes.
This article walks through the end‑to‑end setup of a small‑scale data platform using Alibaba's Canal for MySQL binlog capture, covering the installation and configuration of MySQL, Zookeeper, Kafka, and Canal itself, and demonstrates real‑time change capture with sample DML operations.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
This article analyzes a MySQL InnoDB deadlock caused by concurrent batch inserts into a table with a composite unique index, explains the lock types involved, and offers practical strategies such as shortening transactions and lowering isolation levels to avoid similar deadlocks.
This article explains core MySQL concepts, comparing UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP and DATETIME, composite and covering indexes, the left‑most prefix rule, clustered vs non‑clustered indexes, prefix indexes, InnoDB vs MyISAM storage, monotonic primary keys, column length display, SHOW INDEX fields, LIKE pattern pitfalls, and efficient pagination techniques.
This article explains a MySQL replication experiment where a master is restored from an outdated backup, leading to missing GTID transactions due to server_id filtering, and provides analysis and solutions such as changing server_id or enabling replicate-same-server-id to prevent data loss.
This article explains the differences between MySQL table locks and row locks, describes how MyISAM and InnoDB handle locking, provides explicit lock syntax with examples, discusses gap locks, and offers practical recommendations for avoiding lock contention in concurrent applications.
The article reviews a Tencent Cloud TVP closed‑door forum where experts compared MySQL 8.0, PostgreSQL, Redis and the cloud‑native CynosDB, highlighting each product’s technical strengths, ecosystem support, and the broader industry trend toward cloud‑native, multi‑database solutions as the future of data management.
This guide explains essential MySQL concepts such as the distinction between UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP versus DATETIME, the purpose and benefits of composite indexes, index storage engines, prefix indexes, covering indexes, the left‑most prefix rule, and efficient pagination techniques.
This article provides a step‑by‑step tutorial on building a small‑scale data platform by installing MySQL, Zookeeper, Kafka and the open‑source Canal middleware, configuring Canal to capture MySQL binlog events, and forwarding the structured data to Kafka for downstream processing.
This article presents a comprehensive guide for synchronizing massive MySQL datasets to HBase, covering environment preparation, fast MySQL data loading techniques, and three practical pipelines—Sqoop, Kafka‑Thrift, and Kafka‑Flink—along with performance comparisons and optimization tips for large‑scale data processing.
This article demonstrates how to reproduce disk‑IO saturation in MySQL, enable performance_schema instruments and consumers, apply load, and use sys.x$latest_file_io together with events_waits_history_long to pinpoint whether binlog, redo log, or specific tables are causing the slowdown.
This article explains MySQL’s transaction isolation levels, clarifies the differences between dirty reads, non‑repeatable reads and phantom reads, and demonstrates through experiments why the REPEATABLE READ level can prevent phantom reads, while also discussing its limitations, MVCC, and locking mechanisms.
This article explains what MySQL indexes are, the different categories such as ordinary, unique, composite, clustered and non‑clustered, how B‑Tree, B+Tree and hash indexes are implemented in InnoDB and MyISAM, and why auto‑increment primary keys are recommended for optimal performance.
This tutorial explains MySQL fundamentals, the advantages of using MySQL, detailed concepts of indexes, how they work, step‑by‑step SQL examples for creating, viewing, and dropping indexes, best‑practice principles, and query‑optimization techniques to improve performance.
This tutorial walks through preparing the development environment, creating a Django project and app in PyCharm, configuring MySQL, adjusting Django's MySQL backend, setting up URL routing, and establishing a folder for file uploads, all illustrated with step‑by‑step screenshots.
The article documents a puzzling MySQL 5.7.25 bug where RAND() in a derived table is unexpectedly re‑evaluated in the outer query, explains the step‑by‑step reproduction, analyses the cause, and provides official work‑arounds for both MySQL 5.7 and 8.0.
This article compares two common approaches for encrypting user passwords in MySQL—using the built‑in AES_ENCRYPT/AES_DECRYPT functions and implementing encryption in the application layer—detailing their advantages, drawbacks, and providing concrete code examples for each method.
This article presents essential MySQL interview topics, comparing MyISAM and InnoDB storage engines, explaining INT‑Char implicit conversion nuances, demonstrating efficient methods to fetch random rows, and outlining various index types with their appropriate use cases.
This article analyses why the GTID information recorded by Xtrabackup’s xtrabackup_binlog_info file can differ from the GTID shown by SHOW MASTER STATUS after a restore, comparing the behavior of Xtrabackup 2.4 on MySQL 5.7 and Xtrabackup 8.0 on MySQL 8.0, and explains the underlying backup processes and MySQL internals that cause the discrepancy.
This article explains how MySQL’s character_set parameters such as character_set_client and character_set_results control the encoding and decoding of client commands and query results, illustrates common pitfalls with UTF‑8, GBK and Latin‑1, and provides practical commands to avoid garbled text.
This weekly newsletter summarizes the March DB‑Engines database ranking, explains its methodology, shares curated MySQL articles and tools, and reports the latest development, bug‑fix, and roadmap updates for the open‑source middleware projects DBLE, DTLE, and TXLE.
This article outlines essential principles for selecting and managing database indexes, covering unique indexes, indexing frequently sorted or filtered columns, limiting index count, using short or prefix indexes, removing unused indexes, left‑most prefix matching, column cardinality, avoiding functions on indexed columns, and extending existing indexes.
This guide explains how to create a delayed‑disk device on Linux, place MySQL binlog files on it, and use tools like mysqlslap and pt‑ioprofile to observe the impact of artificially slow I/O on MySQL performance.
This article provides a comprehensive overview of MySQL InnoDB's internal architecture, covering its memory and disk structures, buffer pool, change buffer, adaptive hash index, log buffer, various tablespace types, page layout, undo and redo logs, double‑write buffer, transaction isolation levels, and how the engine satisfies ACID properties.
This comprehensive guide explains MySQL fundamentals, transaction handling, ACID properties, isolation levels, indexing strategies, storage engine differences, lock mechanisms, optimistic vs pessimistic locking, and techniques for optimizing large tables in production environments.
The article explains the shortcomings of traditional MySQL replication lag measurement, introduces the community heartbeat‑table approach and its limitations, and then details MySQL 8 enhancements (WL#7319, WL#7374) that embed transaction timestamps in binlog events and expose new performance_schema tables, providing precise SQL queries to monitor lag at various stages of the replication pipeline.
This tutorial explains how to integrate JDBC into a Spring Boot project, covering Maven dependencies, MySQL driver setup, application.yml datasource configuration, Spring Boot's auto‑configuration mechanism, JdbcTemplate usage, Swagger documentation, CRUD endpoint implementation, and solutions to typical connection errors.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download, install, run them, and interpret their reports to identify configuration issues and optimize database performance.
This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.
This tutorial explains how to create and operate a MySQL InnoDB ReplicaSet—deploying sandbox instances, configuring a replica set with primary and secondary roles, adding nodes, performing manual primary switches and failure recovery, and using MySQL Router to provide an automatic read/write endpoint, while highlighting current limitations.
This guide explains how to integrate JDBC into a Spring Boot project, covering Maven dependencies, datasource configuration, Spring Boot's auto‑configuration mechanism, JdbcTemplate usage, Swagger setup for API testing, CRUD examples on a MySQL department table, and solutions to typical connection errors.
This guide walks through integrating MyBatis into a Spring Boot project, covering dependency setup, MySQL configuration, entity and mapper creation, CRUD annotations, and unit testing to verify database operations, including transaction management and result mapping.
This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
A detailed performance comparison shows that using input redirection to load a huge MySQL dump is roughly three times faster than piping the file through cat, because the redirection reads the data only once while the pipe incurs extra reads and a context switch, as demonstrated with SystemTap tracing.
This article explains the root causes of MySQL character‑set garbling, illustrates the encoding/decoding steps during data insertion and retrieval, and provides practical methods—including dump‑reload and binary conversion—to prevent and repair corrupted data.
An experiment using pt‑tools shows that a single MySQL INSERT triggers three fsync operations on the redo log and one fdatasync on the binlog, while highlighting that flush behavior can vary due to multiple internal mechanisms.
This article explains how a large e‑commerce platform replaced a MySQL‑centric intermediate table with a binlog‑driven pipeline that streams changes through Kafka into Elasticsearch, ensuring ordered, complete, and low‑latency data synchronization while addressing schema evolution and operational monitoring.
This tutorial walks you through installing the EasyCode IntelliJ plugin, setting up a MySQL database, configuring a Spring Boot project, adding necessary Maven dependencies and YAML settings, and using EasyCode to automatically generate entity, DAO, service, controller, and mapper code without manual coding.
This article explains MySQL's row size restrictions at both the Server and InnoDB layers, detailing the calculation methods, relevant source code paths, error messages, and practical guidelines for ensuring total record length stays within the 65535‑byte server limit and the 8126‑byte InnoDB limit.
This article compares two common MySQL import techniques—piping a large dump through cat versus using input redirection—by measuring execution time and tracing system calls with SystemTap, revealing that redirection can be roughly three times faster due to fewer reads and context switches.
This article explains MySQL's row size restrictions, the differences between server and InnoDB limits, how VARCHAR and TEXT columns affect those limits, and demonstrates through calculations and tests the maximum count of TEXT fields that can be stored under various strict‑mode settings.
This article explains the fundamentals of MySQL InnoDB transaction isolation levels, describes the four standard isolation grades, shows how InnoDB implements them in source code, and provides concrete case studies with SQL scripts to illustrate locking behavior and MVCC effects.
This article explains how MySQL's InnoDB engine applies locks for DELETE and SELECT statements, covering MVCC, snapshot and current reads, clustered indexes, left‑most prefix rules, two‑phase locking, isolation levels, gap and next‑key locks, and analyzes nine different index‑and‑isolation scenarios with detailed conclusions.
This guide explains how to create a Bash script that daily backs up multiple MySQL databases, compresses them with gzip, transfers them via rsync to a remote server, retains backups for two months, and schedules the process with cron, including necessary commands and permissions.
This guide walks you through installing the EasyCode plugin in IntelliJ IDEA, setting up a MySQL database, configuring Spring Boot dependencies, and using EasyCode to automatically generate entity, controller, service, DAO, and mapper classes, complete with Maven and application.yml settings.
This weekly roundup from the DBLE open‑source community presents the latest MySQL‑related articles, bug‑fixes, open‑source middleware progress, upcoming release plans for DBLE, DTLE, and TXLE, and invites community feedback on development and usage.
The article investigates a MySQL crash caused by binlog errors due to a full '/' partition, explains why the error leads to server abort, demonstrates reproducing the issue with large transactions, traces the problem to the my_write function in the source code, and offers mitigation strategies such as reducing transaction size or expanding the temporary directory space.
This article explains how MySQL's Multi‑Threaded Slave (MTS) processes events, manages checkpoints, and persists state using GAQ queues, bitmaps, and system tables, providing detailed code references and configuration parameters for reliable parallel replication.
This article explains MySQL index fundamentals, compares B‑Tree and B+Tree structures, details how MyISAM and InnoDB store indexes, and provides practical interview questions and usage rules such as left‑most prefix matching, composite indexes, and common pitfalls.
This article explains the internal workflow of MySQL's multi‑threaded slave (MTS) parallel replication, covering the coordination thread, logical‑clock sub‑mode, worker allocation, waiting states, key configuration parameters, and illustrative code snippets and log analysis.
This guide explains MySQL fundamentals—including what a database is, transaction concepts and ACID properties, isolation levels and their effects, index structures and the left‑most prefix rule, storage engine differences, lock algorithms, and practical techniques for optimizing large tables such as partitioning and sharding.
This article examines a MySQL outage triggered by TCP wrappers, detailing how high thread counts, DNS latency, TIME_WAIT socket exhaustion, and frequent reads of /etc/hosts.allow and /etc/hosts.deny can block connections, and provides troubleshooting steps and mitigation recommendations.
This article explains the fundamentals of characters, character sets and encodings such as ASCII, GB2312, Unicode, UTF‑8/16/32, BOM, and demonstrates how Java, browsers, Tomcat and MySQL handle encoding conversion, endianness and string length issues with practical code examples.
The article explains how MySQL 8.0's caching_sha2_password authentication plugin works, its caching mechanism, advantages, limitations, and how to generate, view, and use RSA key pairs for secure password exchange in encrypted or RSA‑based non‑encrypted connections.
Optimizing MySQL tables with tens of millions of rows requires a systematic approach that considers data volume, table type, and performance goals, and involves design standards, business‑layer tactics, architectural strategies, and database‑specific tweaks such as proper indexing, configuration, and management practices to maintain efficiency.
This article details a step‑by‑step guide for deploying Canal to capture MySQL binlog events, configure HA with ZooKeeper, design a client that parses binlog into JSON, asynchronously acknowledges messages, archive data to local files for batch upload to HDFS, and monitor latency for alerts.
This tutorial explains how to install the EasyCode plugin, set up a MySQL database, configure IDEA, generate entity, controller, service, DAO, and mapper code for a Spring Boot project, and adjust Maven and YAML settings to run the application successfully.
This weekly community briefing summarizes the February DB‑Engines ranking trends, announces MySQL 5.6's upcoming end‑of‑life, and highlights recent development, bug‑fix, and release activities for the open‑source middleware projects DBLE, DTLE, and TXLE, along with several technical deep‑dive links.
This article introduces Flyway as a database migration tool, explains its working principle, directory and naming conventions, supported databases, and provides detailed step‑by‑step instructions, best‑practice guidelines, and troubleshooting tips for safely managing MySQL schema changes in production environments.
This article explains how MySQL's Adaptive Hash Index (AHI) works, why it is needed to reduce B‑tree lookup costs, the three stages of building an AHI, practical examples of its construction, performance benefits, and operational recommendations for managing AHI in production environments.
This article explains the internal structure of InnoDB rollback segments, UNDO log storage formats, and the recovery process in MySQL, detailing how transaction IDs, segment headers, page headers, and log records are organized and used to safely roll back or purge data.
This article explains the InnoDB logical storage architecture—including segments, extents, and pages—then introduces MySQL table partitioning concepts, details the main partition types (RANGE, LIST, HASH, KEY) with SQL examples, and discusses their performance implications for OLTP and OLAP workloads.
This article explains how InnoDB uses REDO logs to recover a crashed MySQL database, detailing the steps of locating checkpoints, scanning log segments, parsing mini‑transactions, storing records in hash tables, and applying them to restore data consistency.
