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.
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.
This article explains MySQL’s server and storage‑engine architecture, walks through the step‑by‑step execution of a SELECT statement, describes how indexes are organized, defines slow queries, shows how to detect and analyze them, and provides practical optimization examples and additional MySQL pitfalls.
This article presents practical MySQL performance tuning techniques, covering query, pagination, join, subquery, order‑by, group‑by, and count optimizations with example code, index strategies, and execution‑plan analysis to improve latency in high‑traffic systems.
This article explains the three primary ways to delete data in MySQL—using DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, storage‑engine differences, and best‑practice considerations such as space reclamation and auto‑increment handling.
This article reviews six theoretical approaches for maintaining MySQL‑Redis data consistency, evaluates their pros and cons, and presents a practical implementation using Java Spring, transactional updates, cache deletion, and asynchronous queue handling to achieve both real‑time and eventual consistency in high‑concurrency systems.
This article explains four design patterns for implementing an audit workflow, compares their advantages and disadvantages, and provides complete SpringBoot backend, MySQL table definitions, and Vue front‑end code to build a functional audit system with file upload support.
This article explains how to query sales statistics for multiple business units and their associated products in MySQL by preparing the environment, presenting several candidate solutions—including loop queries, OR concatenation, mixed filtering, and finally row‑comparison—and selecting the row‑comparison approach as the optimal implementation.
This article explains why MySQL's Adaptive Hash Index (AHI) can cause slow TRUNCATE operations, describes its benefits, and provides a detailed step‑by‑step walkthrough of the AHI construction process—including index counting, construction‑info counting, data‑page counting, and the final hash‑index building—using MySQL 8.0.32 source code examples.
This article explains how MySQL 8.0 updates the AUTO_INCREMENT column in INFORMATION_SCHEMA.TABLES, describes the underlying statistics caching mechanism, shows how the information_schema_stats_expiry parameter controls refresh frequency, and provides step‑by‑step tests demonstrating real‑time behavior with code examples.
This article examines the differences between MySQL's stop slave and reset slave all commands and OceanBase's replication controls, presenting experimental results that show how clog synchronization affects cluster availability, data consistency, and the procedures for decoupling standby clusters.
This tutorial guides you through creating a simple PHP message board, covering MySQL database setup, table design, and step‑by‑step PHP scripts for displaying a form, handling submissions, and listing comments, while also noting security and extension considerations.
Oracle introduces a new MySQL versioning strategy that separates Innovation releases, featuring frequent new features and fixes, from Long‑Term Support (LTS) releases with stable behavior, outlining transition timelines, upgrade/downgrade paths, HeatWave service options, and support lifecycles for developers and DBAs.
The article examines the drawbacks of running MySQL in Docker containers, highlighting data safety risks, I/O performance bottlenecks, state management challenges, and resource isolation limits, while also outlining scenarios where containerizing MySQL can be feasible.
This article explains how Percona XtraBackup 8.0.33-28 introduces a new design that reduces the time required for the backup‑prepare phase, lowers memory and I/O usage, and speeds up SST when adding new nodes to a Percona XtraDB Cluster, with detailed performance benchmarks and code examples.
This article explains the myth of a 2 million‑row limit for MySQL tables, shows how primary‑key size and InnoDB page structure determine theoretical row limits, details B+‑tree indexing mechanics, calculates practical capacities, and compares B+‑tree with B‑tree performance.
This article compares SQLite and MySQL, outlines SQLite’s advantages and disadvantages, describes scenarios where SQLite is appropriate, and provides a step‑by‑step PHP tutorial with complete code for connecting, creating tables, inserting, querying, updating, deleting, and closing an SQLite database.
This article introduces a Java‑based private cinema reservation system built on Spring Boot and MyBatis, outlines its core technologies, provides step‑by‑step deployment instructions, and details all functional URLs and the source‑code download method.
The article compares four MySQL‑to‑Elasticsearch synchronization approaches—synchronous dual‑write, asynchronous dual‑write via MQ, timer‑based SQL extraction, and binlog‑based real‑time sync—evaluates their trade‑offs, and recommends practical tools such as Canal, DTS, and Databus for implementation.
This article explains InnoDB's three row‑lock types, when MySQL statements acquire implicit or explicit locks, and walks through concrete examples for unique and non‑unique index queries, showing how Next‑key, Record and Gap locks are determined.
This article explains why a single MySQL instance is insufficient for production, introduces binlog concepts and formats, details the master‑slave replication threads and workflow, explores parallel replication mechanisms, analyzes causes of replication lag, and provides practical strategies to minimize that delay.
This article investigates a MySQL 8.0 TDSQL client experiencing connection interruptions during batch streaming, analyzes error 1161 (ER_NET_WRITE_INTERRUPTED) root causes in the MySQL source code, presents debugging steps with log and packet captures, and offers practical mitigation recommendations.
This article examines how JD Logistics tackled MySQL connection limits by redesigning its sharding strategy using ShardingSphere's Sharding-JDBC, detailing the background, concepts of database and table sharding, analysis of bottlenecks, and step‑by‑step refactoring to reduce connections and improve scalability.
MySQL introduces Innovation and Long‑Term Support (LTS) release tracks, offering faster feature delivery for Innovation builds and stable, minimal‑change updates for LTS, while outlining new version cadence, HeatWave service options, deprecation policies, and detailed upgrade‑downgrade paths for developers and DBAs.
This guide explains how to perform regular MySQL backups, automate them with crontab, compress and rotate old backups, and keep multiple servers in sync using rsync and inotify, providing complete command examples and configuration files for both server and client sides.
This article analyzes why the MariaDB audit plugin crashes on MySQL 5.7, presents compatibility tables before and after source modification, details the verification steps, and provides a step‑by‑step solution including source code changes and recompilation to make the plugin work reliably.
