This article introduces Sequel Pro, a lightweight macOS client for MySQL and MariaDB, explains how to configure standard connections, describes its main interface modules such as database selection, table structure, query editor, and provides visual guidance for common operations.
This article explains how InnoDB stores data in 16 KB pages, links pages via a double‑linked list, uses page directories and primary‑key directories for binary search, and builds B+‑tree index pages to accelerate MySQL queries, covering free space, page splits, and the overall search flow.
This article explains why MySQL may ignore an index on range queries, shows example queries with EXPLAIN output, describes the optimizer's row‑count threshold that triggers a full table scan, and offers guidance on keeping indexes effective.
This article presents a detailed benchmark comparing MySQL 8.0.19 and MariaDB 10.4.12 on a Windows 10 i7 machine, covering single‑row and batch inserts, various SELECT queries with and without indexes, and analyzes the trade‑off between speed and memory usage.
This article examines how MySQL slaves recover their replication state in GTID‑based and binary‑log‑file‑position scenarios, identifies unsafe factors that can cause crash‑unsafe behavior, and explains how enabling relay_log_recovery=on mitigates these risks while outlining remaining limitations.
This article analyzes a MySQL replication failure caused by stale GTID execution data and unsynchronized binlog persistence, explains the underlying mechanisms, demonstrates reproducible testing, and provides two practical remediation methods to skip duplicate-key errors and restore replication.
Through a series of MySQL 5.7.26 experiments the article reveals how record‑level locks are applied to primary‑key indexes when non‑indexed columns are locked, how isolation levels (READ COMMITTED vs REPEATABLE READ) change the locking order, and why both SELECT … FOR UPDATE and UPDATE statements can block other transactions.
The article examines the drawbacks of running MySQL in Docker containers, highlighting data safety, performance, state management, and resource isolation concerns while also discussing scenarios where containerizing MySQL might be feasible.
This article explains why using MySQL's --force option with default extended‑insert backups can cause missing rows during import, demonstrates the issue with a reproducible example, and shows how disabling extended‑insert with --skip-extended-insert restores all data while providing practical usage recommendations.
This article provides a comprehensive guide to Linux Virtual Server (LVS), covering its history, four operating modes, scheduling algorithms, installation steps, MySQL load‑balancing configuration, ARP suppression techniques, and high‑availability setup using keepalived, all illustrated with diagrams and code snippets.
The article examines tiered data storage and evaluates four cache‑synchronization strategies—updating the database before the cache, deleting the cache before updating the database, updating the cache before the database, and deleting the cache after a database update—highlighting their trade‑offs and practical solutions such as delayed double deletion, message‑queue retries, and binlog‑driven cache updates.
The SQLE 1.2202.0-pre1 release introduces a preview version of the open‑source SQL audit platform with new user‑group permissions, task scoring, support for MySQL schema audits, Oracle TopSQL capture, and Java application SQL capture, along with links to the repository, documentation, and plugin development guide.
This article shares hands‑on experience with MySQL 5.7 slow‑query problems, explaining common causes, proper index design, pitfalls that invalidate indexes, efficient SQL writing, deep‑pagination avoidance, and how to use EXPLAIN to verify that queries leverage the right indexes.
The article explains why MHA’s default long‑connection ping cannot detect a hung MySQL instance, introduces three configurable ping_type options (select, connect, insert), shows how to modify the configuration and test the behavior with Perl code and shell scripts, and details the internal health‑check workflow.
This article explains how InnoDB stores data on disk, the role of 16 KB pages in read/write operations, the dynamic row format’s length and NULL lists, overflow handling, and why varchar columns are limited to 16 383 characters under utf8mb4, illustrated with SQL examples.
This article extends the previous discussion on MySQL time‑based partition implementation by detailing how to write efficient SQL queries, demonstrating partition pruning with supported functions, and offering practical optimization techniques for tables that use unsupported partition functions.
This article compiles a comprehensive set of Tencent interview questions and detailed answers covering HashMap internals, thread safety, red‑black trees, Redis performance, MySQL indexing, TCP reliability, IO multiplexing, RPC, and system bottleneck analysis to help candidates prepare effectively.
This article introduces Change Data Capture (CDC), compares query‑based and log‑based approaches, explains Debezium and ClickHouse, and provides detailed Flink CDC and Flink SQL CDC examples—including Java source code, custom deserialization schema, ClickHouse sink implementation, and required Maven dependencies—to synchronize MySQL data into ClickHouse in real time.
This article explains InnoDB's storage engine mechanics, page-based I/O, the default Dynamic row format, how variable‑length fields like VARCHAR are recorded, why the maximum usable length is 16383 characters in utf8mb4, and the handling of NULL lists and overflow columns.
This article explains MySQL indexes—what they are, their advantages and disadvantages, how they improve query performance, and the scenarios where you should or shouldn't create them—plus a free PDF of interview questions for deeper study.
The article explains why using LIMIT together with ORDER BY in MySQL 5.6 can cause duplicate records on subsequent pages, analyzes the priority‑queue optimization that makes the sort unstable, and offers practical solutions such as indexing, proper pagination logic, and query adjustments.
This article explains how to prevent data loss in MySQL by enabling binlog in ROW format, performing full and incremental restores using mysqldump or XtraBackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements, and using flashback tools such as binlog2sql and MyFlash.
The article explains why data inconsistency occurs when MySQL and Redis are used together under heavy load, analyzes cache‑delete ordering issues, and presents two practical solutions—delayed double‑delete and asynchronous cache updates via MySQL binlog—along with their trade‑offs and implementation steps.
This article explains how MySQL treats NULL values, the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, and demonstrates with practical examples how NULL affects indexing, query performance, aggregation functions, and sorting, while offering recommendations for schema design.
This guide walks through MySQL's query execution sequence, then presents concrete optimization techniques—from selecting specific columns and avoiding OR conditions to proper indexing, using EXPLAIN, handling VARCHAR vs CHAR, limiting result sets, and improving joins, GROUP BY, and UNION operations—complete with code examples and visual explain plans.
This article compiles a collection of practical Bash scripts for Linux system administrators, covering automatic DDoS IP blocking, email alert setup, single‑ and multi‑database MySQL backups, Nginx log rotation and analysis, real‑time network traffic monitoring, server initialization, and bulk disk‑usage checks across many hosts.
This article analyzes why data inconsistencies arise when MySQL and Redis are used together under high load, compares cache‑delete ordering problems, and presents two practical solutions—delayed double‑delete and asynchronous binlog‑driven cache updates—along with implementation tips and trade‑offs.
This guide explains how to collect, store, clean, and aggregate daily user metrics—including new users, active users, and multi‑day retention—using shell scripts, cron jobs, MySQL partitioned tables, and stored procedures, suitable for small‑to‑medium sized products.
This article explains that the numeric value in MySQL INT definitions (e.g., int(1) or int(10)) does not limit the column's range, demonstrates inserting the maximum unsigned value, and shows how ZEROFILL combined with a display width pads numbers with leading zeros.
The article explains that the number in MySQL's INT definition (e.g., INT(1) or INT(4)) does not limit the column's numeric range, but only affects display width when combined with ZEROFILL, illustrating the concept with practical CREATE and INSERT examples.
This article explains why MySQL’s DELETE only sets a delete‑mark, how InnoDB’s MVCC and purge thread reuse freed pages, the impact of B+‑tree storage on I/O, page merge and split mechanisms, and the proper way to rebuild tables to recover space.
This article analyzes why using SET unique_checks=0 during large MySQL imports can bypass unique‑key checks when data is not cached in the InnoDB buffer pool, explains the underlying change‑buffer mechanism, demonstrates the behavior with sysbench tests, and offers a safe workaround.
This guide explains MySQL custom functions, covering their definition, advantages over stored procedures, step‑by‑step creation syntax, parameter handling, invocation, and management commands such as showing, altering, and dropping functions, with practical examples for both parameterless and parameterized functions and a concise comparison table.
This article explains MySQL master‑slave replication fundamentals, why it’s used, the replication workflow, causes of lag, and presents multiple practical solutions—including system tuning, partitioning, caching, multi‑threaded relay log replay, and read‑from‑master strategies—to improve performance and reliability.
This article investigates how the innodb_ddl_threads, innodb_ddl_buffer_size, and innodb_parallel_read_threads variables introduced in MySQL 8.0.27 affect parallel DDL execution speed and resource consumption, using a 50‑million‑row test table and presenting detailed benchmark results.
Even when scanning a 200 GB InnoDB table on a server with only 100 GB RAM, MySQL streams results using a small net buffer and an optimized InnoDB buffer‑pool LRU, so the server’s memory never blows up, though I/O load remains high.
This guide explains why InnoDB full‑text search is needed, how inverted indexes work, how to create and use full‑text indexes with MATCH AGAINST in natural language, boolean, and query‑expansion modes, and how to drop them safely.
A comprehensive collection of MySQL interview questions and answers covering storage engines, lock types, gap locks, deadlock avoidance, isolation levels, index types, covering indexes, the left‑most prefix rule, SQL tuning, master‑slave replication, latency handling, sharding challenges, and global ID generation.
This article walks through ten typical scenarios where MySQL indexes fail—covering left‑most prefix violations, SELECT *, calculations, functions, type mismatches, LIKE patterns, column comparisons, OR, NOT IN/NOT EXISTS, and ORDER BY—showing why each occurs and how to keep indexes effective.
This article explains why InnoDB full‑text search is needed for keyword‑based queries, introduces inverted index structures, shows how to create, use, and delete full‑text indexes in MySQL, and demonstrates natural language, boolean, and query‑expansion search modes with practical SQL examples.
This article evaluates how MySQL handles time data stored as datetime, timestamp, and bigint by inserting half a million records and measuring query, grouping, and sorting speeds, concluding that bigint generally offers the best performance for time‑range operations.
This article presents a collection of practical Bash scripts for Linux servers, covering DOS attack IP blocking, alert emailing, MySQL backup (single and multi‑loop), Nginx log rotation and analysis, real‑time network traffic monitoring, system initialization, and disk usage checks across multiple hosts.
This article explains how InnoDB stores data on disk using 16 KB pages, describes the dynamic row format and its handling of variable‑length columns such as VARCHAR, and clarifies why MySQL limits VARCHAR to a maximum of 16 383 characters under utf8mb4.
This article shares a detailed account of a backend engineer's multi‑stage interview process at Huanju (formerly YY), covering personal background, common Java and system design questions, database and caching strategies, distributed lock handling, and reflections on preparation and outcomes.
This guide explains the concepts, benefits, and step‑by‑step configuration of MySQL master‑slave replication and read/write splitting, including required master and slave settings, practical VM setup, and Mycat configuration files for routing reads and writes.
This article presents a curated, non‑ranked collection of Chinese database community public accounts and resources covering PostgreSQL, MongoDB, MySQL, and miscellaneous IT knowledge, thanking followers and outlining future content plans for professional development.
This guide explains MySQL’s permission system, shows how to configure remote access for the root user by setting the host field to ‘%’, granting all privileges, flushing privileges, and clarifies the role of the skip‑grant‑tables option, while detailing the underlying tables and privilege levels.
This weekly community newsletter summarizes the latest MySQL change‑buffer insights, data‑consistency checks, new password policies, releases of DBLE, DTLE, and SQLE middleware, along with bug fixes, feature improvements, community Q&A, and plans for the upcoming week.
The article examines why containerizing MySQL with Docker is problematic, highlighting risks to data safety, performance bottlenecks, state management challenges, and insufficient resource isolation, while also noting scenarios where containerized databases may be viable.
This article explains MySQL's index storage structures, the difference between clustered and secondary indexes, and demonstrates six typical scenarios—wildcard LIKE, functions on indexed columns, expression calculations, implicit type conversion, left‑most rule violations in composite indexes, and OR conditions—that cause indexes to become ineffective and lead to full‑table scans.
The SQLE 1.2201.0 release introduces workflow optimizations, new MySQL and SQL Server audit rules, enhanced pre‑check capabilities, several UI improvements, and numerous bug fixes, while providing links to the source repository, documentation, and plugin development guide.
This article shares a step‑by‑step account of a candidate's three‑round ByteDance interview, covering background, technical questions on Java, JVM, Redis, MySQL, Kafka, and algorithm challenges, along with personal reflections on pacing, preparation, and why concise answers matter.
The article explores why knowledge retention varies among engineers, proposes concrete learning practices such as systematic note‑taking, focused "nail" learning, hands‑on experimentation with tools like tcpdump and SSH, and distinguishes between knowledge efficiency and engineering efficiency to help readers build lasting, applicable expertise.
DTLE 4.22.01.0, an open‑source MySQL‑focused data transfer component, has been released, introducing Oracle‑MySQL incremental DDL/DML sync, enhanced WHERE clause support, initial Kafka schema‑change handling, and numerous bug fixes including binlog relay issues, character‑set handling, and CREATE TABLE … LIKE renaming problems.
This tutorial provides step‑by‑step instructions for installing Hadoop 3.1.1, Homebrew, Hive, and configuring MySQL as Hive's metastore on macOS, including environment variable setup, hive‑site.xml configuration, MySQL connector placement, schema initialization, and verification commands.
This article explains MySQL's client‑server architecture, detailing how the server processes connections, parses and optimizes queries, generates execution plans, and interacts with various storage engines, while also covering configuration variables, connection monitoring, and practical command examples.
This article explains MySQL 8.0's Failed‑Login Tracking and Temporary Account Locking (FLTTAL) feature, describes its two configurable options, outlines important usage notes, and provides step‑by‑step examples for both regular and proxy users along with methods to reset locked accounts.
This article introduces a custom MySQL data consistency verification tool inspired by pt-table-checksum, explains the challenges of handling complex primary keys, details the implementation logic with shell scripts and SQL, and provides installation, configuration, and usage instructions including parallelism, network monitoring, and scheduling.
This article explains MySQL GTID (Global Transaction Identifier), covering its composition, lifecycle, how GTIDs are generated during commit, the mechanisms that maintain GTID state, and practical examples that illustrate GTID behavior in replication scenarios.
This article walks through reproducing a MySQL deadlock in a test environment, explains how to read InnoDB status logs to pinpoint the conflicting locks, and offers practical tips to prevent deadlocks in production systems.
This article analyzes why MyBatis foreach‑based batch inserts become extremely slow with thousands of rows, explains the underlying SQL and PreparedStatement overhead, and demonstrates how switching to ExecutorType.BATCH or limiting foreach values to 20‑50 rows dramatically improves MySQL insertion performance.
The article explains why large‑offset LIMIT queries on a MySQL table with millions of rows are slow, demonstrates how rewriting the query to use a subquery that first selects primary‑key IDs dramatically reduces execution time, and validates the improvement by measuring buffer‑pool page usage.
This article explains why a MySQL query that scans a 100‑gigabyte table and returns millions of rows does not exhaust server memory, describing the net_buffer mechanism, socket send buffer behavior, InnoDB buffer‑pool management, and the improved LRU algorithm used to keep large scans from degrading overall performance.
This article demonstrates how to generate, insert, and query ten‑million‑row MySQL tables, measures pagination query times under different offsets and limits, and presents practical optimization techniques such as sub‑queries, id‑range filtering, and column selection to improve performance on massive data sets.
A former Oracle MySQL optimizer lead publicly denounces MySQL as a poorly designed database, praises PostgreSQL's strengths, critiques MariaDB, and highlights real‑world migration experiences, offering a balanced view of the ongoing open‑source relational database debate.
Full table scans on large InnoDB tables do not exhaust MySQL server memory because results are streamed in small net buffers, while InnoDB’s optimized LRU algorithm and buffer pool management ensure that massive scans minimally affect buffer pool hit rates and overall system performance.
This article explains the concepts behind MySQL GTID replication, compares GTID‑based and binlog‑position‑based replication, and provides step‑by‑step commands for safely enabling and disabling GTID mode online with minimal impact on production workloads.
Vivo’s self‑built database and storage platform evolved from a fragmented 2018 system into a two‑layer DaaS solution offering unified management, automated security, high availability, and cost‑effective scaling across MySQL, Redis, TiDB, object storage, and future autonomous services within a hybrid‑cloud architecture.
This article provides a comprehensive overview of Tencent's self‑developed MySQL kernel TXSQL, covering its evolution, overall architecture, columnar storage engine, instant DDL capabilities, enterprise‑grade features, high‑availability mechanisms, performance optimizations, and the rigorous development and testing processes behind the product.
During nightly peaks, a social‑e‑commerce startup experienced hour‑long service outages due to MySQL timeouts; by analyzing traffic spikes, CPU usage, and slow‑SQL logs, the team identified un‑cached ranking queries and a 20‑minute cache refresh bottleneck, then implemented targeted caching, monitoring scripts, and fallback static pages to eliminate the issue.
The article demonstrates how MySQL handles simple relationship queries efficiently but struggles with deep relational traversals, and shows how the graph database Neo4j can replace MySQL in such scenarios, providing faster query execution and better scalability for multi‑level social connections.
This article explains MySQL's three‑layer logical architecture, the roles of binary log, redo log, and undo log, how they interact during data updates, and the configuration options that affect durability and performance.
This article reviews Tencent Cloud’s self‑developed MySQL kernel TXSQL, detailing its enterprise‑grade features, evolutionary roadmap, overall and cloud‑native TDSQL‑C architecture, columnar engine for OLAP, instant DDL capabilities, high‑availability mechanisms, performance optimizations, and the rigorous development and release process that underpins its stability.
This article analyzes a production MySQL deadlock caused by two identical UPDATE statements that trigger an index‑merge optimization, explains the lock interactions between the idx_status and PRIMARY indexes, and provides code‑level and MySQL‑level solutions to prevent such deadlocks.
This article describes practical improvements to the my2sql binlog‑parsing tool—adding query support and automatic binlog range detection—and demonstrates how to integrate it into DBeaver to provide developers with easy data‑rollback and original‑SQL tracing without requiring DBA intervention.
This article explains how InnoDB implements full-text search using inverted indexes, shows how to create and use full-text indexes with various query modes—including natural language, boolean, and query expansion—covers relevance calculation, stopwords, token size limits, and demonstrates how to delete indexes, all illustrated with SQL examples and diagrams.
This article investigates MySQL's recommendation to avoid UUIDs as primary keys by benchmarking auto‑increment, UUID, and random snowflake IDs using Spring Boot and JdbcTemplate, analyzing insert speed, InnoDB index behavior, and the trade‑offs of each key strategy.
This guide compiles practical MySQL performance techniques—including proper indexing, avoiding costly operators, rewriting OR conditions, using EXISTS, limiting SELECT *, managing temporary tables, configuring threads, choosing storage engines, and optimizing data types—to dramatically reduce full‑table scans and improve query speed.
This weekly newsletter from the MySQL community presents curated technical articles, bug fixes, open‑source middleware progress, and upcoming plans, offering developers concise insights into MySQL issues, DBLE/DTLE developments, and SQL audit tool enhancements.
The article explains how using a large OFFSET in MySQL pagination forces the server to read and discard many rows, causing severe performance degradation, and demonstrates that rewriting the query to first fetch primary‑key IDs via a subquery dramatically reduces I/O and execution time, with buffer‑pool experiments confirming the improvement.
This article presents three practical SQL interview problems—finding the second‑highest salary per department, counting login intervals under five days, and analyzing daily purchase channels—complete with data setup, solution queries, and key concepts such as window functions and joins.
This guide explains the background of TPC‑C, its key concepts, and provides a detailed, container‑based procedure for setting up the environment, preparing data, running the benchmark on RadonDB MySQL in Kubernetes, and interpreting throughput and price‑performance results.
This article explains how InnoDB’s two‑phase commit mechanism keeps the redo log and binlog logically consistent, illustrating failure scenarios, the impact on data recovery, and how the prepare‑commit split prevents inconsistencies in MySQL’s storage engine.
This article provides a comprehensive overview of MySQL's built‑in functions—including numeric, string, date/time, and aggregation utilities—explaining their usage, parameters, and output with clear examples to help developers write more efficient SQL queries.
The article shares a comprehensive methodology for MySQL database inspection, classifying checks by method, time and depth, and detailing availability, reliability, and performance best‑practice items along with concrete command examples and configuration recommendations.
The article details a MySQL memory‑usage incident at Qunar where abnormal slab memory, especially dentry allocation, caused a rapid increase in used memory after a mis‑configured yum makecache cron job, and explains the investigation steps, Linux memory concepts, diagnostic commands, and the corrective actions taken.
This article explains how MySQL allocates auto‑increment IDs, the differences between MySQL versions, the three InnoDB lock modes, scenarios that break ID continuity, what to do when IDs run out, and strategies to hide sequential IDs from malicious crawlers.
This article explains how InnoDB stores data in 16 KB pages, calculates how many rows a B+Tree index can hold, shows how to determine the tree height from the page level, and answers why MySQL uses B+Tree rather than other tree structures.
This article explains MySQL's experimental EXPLAIN FORMAT=TREE feature and the later EXPLAIN ANALYZE extension, showing how they present execution plans, estimated costs, actual runtime metrics, and how to interpret the tree output for query optimization.
The article investigates a MySQL 8.0.26 crash triggered by setting the terminology_use_previous parameter to BEFORE_8_0_26, analyzes stack traces and core dumps, compares processlist access methods, references related bugs, and offers mitigation recommendations for monitoring tools.
This article details Industrial and Commercial Bank of China's comprehensive MySQL governance framework, covering risk identification, prevention strategies, a four‑step methodology, automated quality gates, production‑level monitoring, SRE management, and future visions for rapid incident detection and self‑healing.
This article evaluates MySQL insertion performance of three primary key strategies—auto‑increment, UUID, and random Snowflake‑like keys—by building identical tables, running Spring‑Boot/JdbcTemplate benchmarks, analyzing index structures, and discussing the advantages and drawbacks of each approach.
This article explains why MySQL queries become slow, how to enable and analyze slow‑query logs, and provides practical steps for index, SQL, schema, optimizer, and architecture optimizations to keep performance strong even with millions of rows.
Learn how MySQL’s powerful join operations transform simple table queries into meaningful results, covering Cartesian products, inner, left, right joins, aggregation with group functions, and practical filtering techniques, complete with sample data, SQL syntax, and execution examples.
This article introduces several MySQL performance‑diagnostic utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run them, and shows how to interpret their reports for optimizing database configuration and query performance.
This article explains ProxySQL's built‑in databases, key configuration tables such as mysql_servers, mysql_users, mysql_replication_hostgroups, mysql_group_replication_hostgroups, and mysql_query_rules, and demonstrates how to set up read/write splitting and automatic failover for MySQL primary‑replica and group replication environments.
This article explains the importance of MySQL backups, compares common logical and physical backup tools such as mysqldump, Mydumper and Percona XtraBackup, details XtraBackup's step‑by‑step workflow, lock handling options, and provides practical Q&A for reliable database backup strategies.
This article reproduces a prolonged MySQL 5.7 binlog replay issue, analyzes the underlying client memory‑allocation bug, and demonstrates how MySQL 8.0 resolves the problem, resulting in significantly faster binlog restoration for large delete operations.
This tutorial walks through creating a maintainable GraphQL application using Go, demonstrating how to set up a Docker‑based MySQL database, configure Viper and Echo, define ent schemas, run migrations, integrate gqlgen with ent, and implement queries and mutations following Clean Architecture principles.
This article examines the architecture differences between traditional MySQL and the cloud‑native TDSQL‑C database, outlines MySQL disaster‑recovery deployment models, and details TDSQL‑C’s multi‑dimensional disaster‑recovery system, including its agent‑scheduler design, cross‑AZ switching challenges, and mitigation strategies.
