This article walks through the creation and step‑by‑step optimization of a MySQL service_log table, covering proper data‑type selection, index usage, NULL handling, DATETIME vs TIMESTAMP choices, and floating‑point considerations to achieve a lean, high‑performance schema.
This article explains MySQL's index prefix length restrictions for BLOB/TEXT columns in versions 8.0 and 5.7, demonstrates verification experiments showing the 767‑byte and 3072‑byte limits for different InnoDB row formats, and discusses the underlying design implications.
This tutorial demonstrates how to use Alibaba's Canal in cluster mode with Zookeeper to capture MySQL row‑based binlog events, publish them via RocketMQ, and consume them with a custom listener that transforms inserts, updates, and deletes into synchronized documents in an Elasticsearch index.
The article analyzes the performance problems of deep pagination on massive MySQL tables storing billions of fan records and presents three progressive solutions—simple LIMIT, tag‑record (maxId) pagination, and range‑limited pagination with async and offline minId caching—along with general indexing best‑practices for high‑throughput queries.
This article demonstrates how to insert 300,000 rows into a MySQL table efficiently by defining entity, mapper and configuration files, comparing direct batch insertion, row‑by‑row insertion, and optimized batch strategies with MyBatis and JDBC, and summarizing performance results and best‑practice tips.
MySQL 5.7 reaches its official End‑of‑Life in October 2023, exposing users to security, compliance, performance, and support risks, and the article outlines four migration paths—continuing with 5.7, upgrading to MySQL 8.0, switching to domestic open‑source forks like GreatSQL, or adopting commercial Chinese databases—while providing a detailed case study of GreatSQL’s suitability for Chinese enterprises.
This article analyzes MySQL 8.0 password‑related parameters, explains the meaning of NULL values in the mysql.user table, demonstrates how global settings interact with per‑user attributes, and clarifies the priority rules between password_history and password_reuse_interval through practical test scenarios.
This article presents a systematic performance test of inserting massive data into MySQL, comparing three strategies—MyBatis lightweight insertion, direct JDBC (with and without transactions), and JDBC batch processing—showing how transaction handling and batch execution dramatically affect insertion speed.
This article explains how to configure Nginx's stream module to proxy MySQL connections, including building Nginx with the required module, setting up stream, server, and listen directives, and using allow/deny rules to restrict access by IP, followed by a complete configuration example.
The Aikexing Open Source Community weekly newsletter shares curated database articles, technical insights on MySQL, OceanBase, and Redis, announces SQLE 2.2309.0‑pre2 release with Feishu integration, recaps recent events like QCon Beijing, and promotes upcoming meetups and open‑source projects.
This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.
This article explains MySQL's logical architecture, query execution flow, caching mechanisms, and detailed index design techniques, providing practical tips and examples so developers can understand the underlying principles and apply effective performance optimizations in real‑world applications.
This article explains MySQL InnoDB's multi-version concurrency control (MVCC), detailing how transaction isolation levels such as read‑committed and repeatable‑read create snapshots, how undo logs and hidden columns form version chains, and walks through concrete examples to show which row values are visible to concurrent transactions.
This article demonstrates how to use MySQL to create employee and performance tables, populate them with sample data, and write SQL statements that first select employees whose salaries exceed their department's average and then filter out those with non‑A performance ratings, providing a complete layoff‑target list.
This step‑by‑step tutorial shows how to configure Alibaba's Canal to capture MySQL binlog changes, route them through RocketMQ, and index the data into Elasticsearch, covering cluster mode, MySQL and Elasticsearch setup, RocketMQ topic creation, Canal properties, and consumer implementation.
This article explains nine practical MySQL optimization methods—including smarter LIMIT usage, implicit conversion pitfalls, JOIN‑based updates, mixed ordering tricks, EXISTS rewrites, predicate pushdown, early range reduction, intermediate result pushdown, and a concise summary—showing how to transform slow queries into sub‑millisecond operations.
With MySQL 5.7 reaching end‑of‑life in October 2023 and over half of installations still on that version, this guide explains the new features of MySQL 8.0, how to assess upgrade readiness, and the three main migration paths—including staying put, moving to a DBaaS, or switching to PostgreSQL.
This article explains how ordering issues in message queues like RabbitMQ and Kafka can break MySQL binlog synchronization, illustrates common pitfalls, and offers practical solutions to guarantee correct processing order in high‑throughput backend systems.
This article provides a collection of ready-to-use Bash scripts for common MySQL administration tasks on Linux, including database backup, table optimization, cleanup of old backups, process monitoring, status checks, size reporting, schema export/import, and lock/unlock operations.
This article analyzes the MySQL ERROR 1499 caused by exceeding partition limits in OceanBase, examines server parameters, recycle‑bin settings, and tenant memory usage, and provides calculations and recommendations to expand memory or limit partition counts to prevent the error.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
The article explains how MySQL 8 enhances replication observability by exposing detailed metrics through Performance Schema tables and sys schema views, providing DBAs with richer information such as per‑channel lag, worker thread states, and full replication status beyond the traditional SHOW REPLICA STATUS output.
This comprehensive guide consolidates 43 essential MySQL interview topics—from normalization, storage engines, and indexing to transaction handling, logging, replication, and performance tuning—providing clear explanations, code examples, and diagrams to help candidates quickly master the core concepts.
This article examines why MySQL columns are often defined as NULL, debunks common myths, and presents several technical reasons—including storage overhead, index complications, query pitfalls, and timestamp issues—that justify using NOT NULL with meaningful default values for better performance and data integrity.
This article walks through why MySQL queries become slow, covering how to capture slow SQL, configure the slow‑query log, analyze execution plans with EXPLAIN, dive into PROFILE and OPTIMIZER_TRACE, and examine engine and system parameters that affect performance.
This article explains how an UPDATE without indexed WHERE conditions can trigger full‑table next‑key locks in InnoDB, causing business downtime, and provides practical safeguards such as enabling sql_safe_updates and using FORCE INDEX to ensure safe execution.
With MySQL 5.7 reaching end‑of‑life, this guide explains why you should upgrade to MySQL 8.0, outlines the new SQL capabilities and tools, and compares migration options including DBaaS and switching to PostgreSQL, helping you choose the best path for your workloads.
The SQLE 2.2309.0-pre1 release introduces downloadable scan task reports, eight new MySQL audit rules, and expands MyBatis scanning to all supported data source types, while providing comprehensive project overview, full release information, and links to documentation and demos.
This guide walks through setting up a test environment, extracting binlog positions, generating rollback SQL with the my2sql tool, and safely re‑importing the data to restore rows mistakenly removed by a DELETE statement.
This article examines a MySQL 5.7 replication anomaly where a slave reports no errors despite GTID mismatches, demonstrates how to reproduce the issue, explains the underlying cause related to missing Auto_Position=1, and provides remediation steps and source‑code insights.
This guide walks backend developers through fifteen practical best‑practice tips covering table and column naming, data types, field lengths, primary keys, storage engines, null constraints, foreign keys, indexes, time and monetary fields, character sets, collations, and handling large columns to reduce maintenance costs and avoid common pitfalls.
This article examines whether adding the always‑true condition WHERE 1=1 in MySQL queries prevents index usage, by comparing EXPLAIN plans for queries with and without the clause and proposing a cleaner MyBatis mapping approach.
The article analyzes why massive tables become a bottleneck in Spring Boot systems, outlines the drawbacks of sharding, and presents a layered solution—data archiving, read‑write separation with caching, heterogeneous source synchronization via Elasticsearch and Canal, and selective sharding—to enable high‑performance real‑time search.
This article examines the challenges of scaling inventory pre‑allocation in fast‑growing logistics platforms, outlines architectural principles for a lightweight, Redis‑driven solution that replaces MySQL bottlenecks, details data structures, consistency mechanisms, and performance results, and provides a roadmap for future capacity growth.
This article explains how to insert 300,000 rows into a MySQL table efficiently by defining a User entity, configuring MyBatis mappers, and comparing three approaches—direct bulk insert, per‑row loop insert, and batch insert with configurable batch sizes—while also providing JDBC equivalents and performance tips.
The article explains that MySQL implicitly converts VARCHAR values to numbers during a comparison with an integer, truncating non‑numeric strings and causing queries like "WHERE a = 0" to return rows that should not match, unlike Oracle and SQL Server which raise errors.
This article explains the MySQL master‑slave switch process, why the internal event_scheduler thread cannot be killed, the differences between MySQL 5.7 and 8.0, and provides step‑by‑step commands for enabling, creating, viewing, modifying, and deleting scheduled events.
This article examines why containerizing MySQL with Docker can be problematic, highlighting data safety concerns, performance degradation due to I/O bottlenecks, state management limitations, and resource isolation challenges, while also presenting scenarios where containerization may be feasible and offering mitigation strategies.
This article examines frequent MySQL performance problems such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, and intermediate result handling, and provides concrete SQL rewrites and execution‑plan analyses to dramatically improve query speed.
This article demonstrates multiple ways to insert 300,000 rows into a MySQL table using MyBatis and JDBC, compares batch, loop, and single‑record approaches, analyzes execution time and resource usage, and provides practical optimization tips for high‑throughput data loading.
This article explains how MySQL InnoDB Cluster 8.1 introduces read‑only replica instances, shows step‑by‑step commands to create and configure them, describes their failover behavior, routing options with MySQL Router, health‑check isolation, replication‑lag handling, and how to hide replicas from traffic.
This guide explains how to use Nginx’s stream module as a jump‑host to securely proxy MySQL connections, covering module compilation, core directives, configuration examples for single and clustered servers, and IP‑based access restrictions to protect database traffic.
This article explains MySQL transaction fundamentals, the ACID properties, how to start and control transactions, the problems of dirty, non‑repeatable and phantom reads, isolation level settings, MVCC mechanics, read‑view structures, and the role of undo logs in ensuring data consistency.
This tutorial demonstrates how to use PHP to create a backup directory, export MySQL tables to text files, and restore them by reading the files and executing INSERT statements, providing complete code examples for both backup and recovery processes.
The SQLE 2.2308.0 release introduces intelligent SQL file scanning, per‑instance work‑order approvers, multi‑file MyBatis review in IntelliJ, zero‑cost Java probe integration, new OceanBase‑Oracle support, expanded TDSQL distributed rules, and numerous bug fixes and optimizations across both community and enterprise versions.
This article examines the high connection‑count problem of Sharding-JDBC in JD Logistics, explains sharding concepts, compares four mitigation strategies, and presents a custom table‑partitioning algorithm with configuration changes that dramatically lowers MySQL instance connections while maintaining performance.
During a traffic peak the CPU surged and thread dumps revealed many MySQL Statement Cancellation Timer threads; this article analyzes the root cause in the MySQL driver, Druid pool and MyBatis configuration, and provides concrete steps to eliminate the unwanted timer threads.
This article explains why MySQL joins and subqueries can become performance bottlenecks on large tables, recommends fetching data via indexed single‑table queries and merging in the application layer, outlines advantages, use cases, drawbacks of joins, and offers practical alternatives and tuning tips.
This article explains how to identify and remove duplicate rows in a MySQL table—keeping a single record per duplicate group—by using a self‑join DELETE statement or a nested subquery approach, complete with sample table creation and data insertion scripts.
This tutorial explains how to create a basic online quiz platform by designing a MySQL database, writing PHP code to connect, retrieve, display questions, and process user answers to calculate scores, providing a foundation for further feature expansion.
This article explains how Prometheus stores alerts only as time‑series data, why that limits historical queries, and provides a complete open‑source solution using Alertmanager, Alertsnitch, MySQL, and Grafana to persist, query, and visualize alerts in production environments.
This article explains why using NULL in MySQL tables hurts storage, index size, query correctness, and update performance, and shows concrete examples, benchmark data, and official documentation that justify preferring NOT NULL with sensible default values.
This article explains how to retrieve the names of students who have selected both course 1 and course 2 in a MySQL table by using a GROUP BY clause together with a HAVING condition that counts distinct course IDs, avoiding incorrect results from a simple IN filter.
This article provides a step‑by‑step guide for Windows users to install VSCode, set up a VMware‑hosted CentOS 7 VM, compile the MySQL 8.0.34 source with debug options, configure VSCode remote‑debugging plugins, and perform source‑level debugging of MySQL.
This article explains why using NULL columns in MySQL can hurt performance, complicate indexing and queries, and increase storage, while offering practical reasons and examples for preferring NOT NULL constraints and meaningful default values.
This article explains MySQL’s leftmost prefix principle, shows how composite indexes are built and used, demonstrates which query patterns trigger index usage or full‑table scans, and provides practical tips for SELECT *, functions, LIKE, OR, IN, ORDER BY and sub‑queries to avoid index loss.
This article presents a full set of Java backend interview questions—ranging from self‑introduction and challenging projects to abstract classes, HashMap internals, B+‑tree characteristics, MySQL vs. skip‑list indexing, Redis skip‑list usage, large‑table optimization, join queries, JVM memory model, and Survivor space—along with detailed English explanations and sample code.
This guide walks through setting up Spring Batch 4.2.7 with Spring Boot 2.4.12, covering architecture, configuration, job, step, reader, processor, writer, listeners, and deployment, demonstrating how to read CSV data, process it, and persist results to a MySQL database.
This article compares MySQL and Elasticsearch across data models, query languages, indexing, distributed architecture, performance, scalability, and typical use cases, highlighting their distinct strengths and trade‑offs to help developers decide which system—or combination—best fits specific application requirements.
This article presents practical MySQL optimization techniques—including proper index creation, avoiding index invalidation, choosing appropriate lock granularity, efficient pagination strategies, steering clear of SELECT *, and using EXPLAIN and SHOW PROFILE—to help developers write faster, more reliable SQL queries.
This article explains how to perform relational updates in MySQL using JOINs, subqueries, and multi‑table UPDATE statements, demonstrating table creation, sample data insertion, and the differences between INNER, LEFT, and RIGHT joins for keeping dependent tables in sync.
Oracle announces a new MySQL version model that separates Innovation releases, which deliver new features and frequent updates, from Long‑Term Support (LTS) releases that provide stable, minimal‑change versions with extended support, outlining upgrade paths, deprecation policies, and detailed release schedules.
This article examines the current state and historical background of MySQL and MariaDB service lifecycles and version‑management strategies, comparing their community‑edition release models, long‑term‑support policies, innovation releases, and upgrade paths.
This article explains MySQL's four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—detailing their definitions, associated phenomena such as dirty reads, non‑repeatable reads, and phantom reads, and demonstrates how to query and set isolation levels with practical SQL examples.
This article walks through a real‑world incident where a pagination‑related slow‑query caused massive CPU spikes, details the step‑by‑step investigation, and presents several MySQL optimization techniques—including ID‑based queries, sub‑queries, and cursor‑based pagination—that ultimately resolved the performance crisis.
This article thoroughly examines MySQL replication, detailing binlog formats, event types, replication workflows, semi‑synchronous and parallel replication techniques, performance benchmarks, and practical implementation steps such as fake‑slave registration and connection‑pool enhancements, while illustrating each concept with concrete examples and code snippets.
This article explains how to build a secure email‑verification login and registration system using PHP and MySQL, covering database schema creation, user registration with verification code generation and email sending, verification link handling, and login validation, with full code examples.
The article examines recent data showing PostgreSQL’s rapid rise over the past decade, its strong performance in the 2023 StackOverflow survey, growing adoption by cloud providers like Google and AWS, and the reasons developers favor its open‑source reliability and extensibility.
This article explains how to split a massive MySQL table into smaller tables, use the MERGE engine to create a unified view, troubleshoot common errors, and implement efficient pagination across the split tables with example SQL and Java code.
This article describes how to identify, analyze, and fix InnoDB page corruption in MySQL instances, covering log inspection, querying metadata tables, using innodb_force_recovery, and the inno_space tool, with example commands and best‑practice recommendations to prevent data loss.
This article demonstrates how to insert 300,000 rows into a MySQL table using MyBatis and plain JDBC, compares direct batch insertion, per‑row insertion, and staged batch processing, and provides practical performance tips such as batch size, waiting intervals, index handling, and connection‑pool configuration.
This article explains how to set up a MySQL test table and indexes, then enumerates nine typical scenarios—such as leading‑wildcard LIKE, arithmetic or functions on indexed columns, type mismatches, composite‑index misuse, character‑set differences, and OR conditions with non‑indexed fields—that cause queries to bypass indexes and perform full table scans.
This article explains how MySQL handles overflow of auto_increment, InnoDB row_id, Xid, trx_id, and thread_id, showing the resulting primary‑key errors, data overwrites, rare Xid collisions, a potential dirty‑read bug, and recommended safeguards such as using BIGINT.
This comprehensive guide explains MySQL’s architecture, index fundamentals, the EXPLAIN plan keywords, step‑by‑step index creation and deletion commands, and numerous real‑world optimization examples, helping developers dramatically improve query execution speed while avoiding common pitfalls.
The article examines the drawbacks of using NULL columns in MySQL, explaining how they increase storage space, complicate indexing and query optimization, and can cause performance issues, while providing examples and best practices for defining NOT NULL constraints and handling data correctly.
The article analyzes a MySQL 5.7 replication failure where the slave SQL thread stops with error 1590 because the master writes an INCIDENT_EVENT to the binlog when a GRANT or CREATE USER statement partially succeeds due to password‑validation plugin constraints, leading to replication breakage.
This article details the design and implementation of a highly available, high‑performance membership system serving over a billion users, covering dual‑center Elasticsearch clusters, traffic‑isolated three‑cluster ES architecture, Redis dual‑center caching, MySQL partitioned clusters, migration strategies, and refined flow‑control and degradation mechanisms.
This article compares SQL and NoSQL databases, outlining their advantages, limitations, and ideal use‑cases, and provides guidance on selecting the appropriate technology based on consistency, scalability, and data model requirements.
Learn how to safely expose MySQL on a private network by configuring Nginx as a stream proxy, including building the required modules, setting up stream, server, and listen directives, and restricting access with allow/deny rules for specific IP ranges.
MySQL 8.0.34 adds the validate_password.changed_characters_percentage variable, allowing administrators to require a minimum percentage of different characters when users change passwords, and the article demonstrates how to enable the policy, set up a test environment, and verify behavior with various password change scenarios.
This article explains how small web projects can avoid heavyweight message middleware by using CDC technology, specifically Debezium, to monitor MySQL binlog changes, outlines why Debezium outperforms alternatives like Canal, and provides step‑by‑step Spring Boot integration with configuration, code samples, and practical use‑case scenarios.
This article analyzes a production deadlock caused by MyBatis‑Plus’s saveOrUpdate method, explains how MySQL gap locks lead to the issue, demonstrates reproducible tests, and offers practical solutions such as disabling gap locks or customizing the saveOrUpdate implementation.
This article presents a comprehensive guide to SQL performance tuning, covering real‑world interview scenarios, deep‑pagination pitfalls, and fifteen concrete optimization techniques—including index usage, limit tricks, proper joins, batch operations, and data‑type choices—to help developers write faster, more efficient MySQL queries.
This article explains the purpose, classification, and implementation of MySQL locks—including global, table, page, row, intention, and gap/record/next-key locks—as well as optimistic and pessimistic locking strategies, providing code examples and usage scenarios for ensuring data consistency in concurrent transactions.
The article introduces sql_helper, an open-source command-line and web tool for MySQL 5.7/8.0 and MariaDB that automatically analyzes SQL statements to suggest index creation, explains its six-step workflow, provides usage examples, command-line parameters, and notes its assumptions and limitations.
This article explains the architecture of TDSQL-C Serverless buffer pool, details how InnoDB buffer pool resize works, analyzes performance bottlenecks such as IO and mutex contention, and presents optimization techniques like chunk pre‑allocation, delayed release, and hash‑resize improvements that make serverless scaling more stable.
This article explains how to use CDC technology, particularly Debezium, to capture MySQL binlog changes and process them in a Spring Boot application without adding heavyweight middleware, providing code examples, configuration details, and typical use cases.
The article examines various MySQL pagination strategies—including simple LIMIT clauses, sub‑query approaches, ID‑range filtering, and temporary‑table methods—provides performance measurements on a table with millions of rows, and offers practical recommendations for reducing query latency in large datasets.
MySQL 8.0 introduces a native document store that brings full ACID‑compliant NoSQL capabilities, schema‑less JSON handling, simple CRUD APIs, seamless SQL integration, large‑document support, built‑in security and infrastructure simplification, enabling developers and DBAs to leverage existing MySQL expertise for modern applications.
This article details a systematic investigation of MySQL insert slowdown caused by disk I/O anomalies, describing log analysis, blktrace data collection, RAID controller firmware bugs, and the final resolution through firmware upgrade, providing practical guidance for database performance troubleshooting.
This article investigates why inserting the foreign_key_checks variable into MySQL's my.cnf causes the server to fail to start, explains the underlying NO_CMD_LINE flag mechanism, and provides a simple command‑line method to distinguish variables that can or cannot be set via configuration files.
This article details the end‑to‑end high‑availability architecture—including dual‑center Elasticsearch clusters, Redis caching with distributed locks, and a dual‑center MySQL partitioned setup—that enables a membership platform serving billions of users to sustain massive traffic while ensuring data consistency and rapid recovery.
This article explains why MySQL, the world’s most popular open‑source relational database, is a fundamental skill for developers, operators and testers, and outlines a two‑book learning path covering installation, SQL mastery, InnoDB internals, indexing and performance optimization.
This article provides concise Python code snippets for connecting to and querying a variety of popular databases—including MySQL, PostgreSQL, SQLite, MongoDB, Oracle, SQL Server, Redis, Elasticsearch, Cassandra, Couchbase, Firebase, DynamoDB, Neo4j, and InfluxDB—illustrating the essential steps for each.
This article experimentally verifies how SELECT FOR UPDATE behaves under MySQL 5.7 and 8.0 with both REPEATABLE‑READ and READ‑COMMITTED isolation levels, covering primary keys, unique indexes, ordinary indexes, no indexes and range queries, and summarizes when row‑level, gap‑level or table‑level locks are taken.
This article explains three relatively new MySQL 8.0 features—hidden (invisible) columns, generated invisible primary keys, and invisible indexes—detailing their purpose, how to create and use them, and practical scenarios where they can help avoid schema‑change issues or test query performance.
This article details a real‑world test of loading over 20 billion records into MySQL with XeLabs TokuDB, covering the demand, configuration tweaks, table schema, bulk‑loader commands, performance metrics, comparison with InnoDB, and practical conclusions for large‑scale data ingestion.
This article explains the purpose, flushing mechanisms, and key differences of MySQL's redo log, binary log, and undo log, highlighting how they ensure durability, support recovery, and enable atomic rollback in InnoDB.
This article provides a detailed backend interview cheat‑sheet covering Redis data structures and performance issues, MySQL indexing and transaction principles, operating‑system process and thread concepts, TCP/UDP networking differences, and common Java garbage‑collection algorithms, all illustrated with examples and code snippets.
The SQLE 2.2308.0-pre1 release introduces community and enterprise enhancements such as intelligent SQL file scanning, per‑instance workflow approver matching, and the ability to abort deployments on OceanBase and TDSQL data sources, accompanied by usage examples, command‑line instructions, and full release information.
