This tutorial demonstrates how to set up a PHP script that connects to a MySQL database, executes a SELECT query, stores the result rows in an array, and displays the data in an HTML table using loops such as while and foreach.
This comprehensive guide compiles 35 essential Linux operations interview questions covering server management, RAID configurations, load balancing with LVS/Nginx/HAProxy, proxy choices, middleware, MySQL troubleshooting, networking tools, security practices, and practical scripts, providing concise answers to help candidates ace DevOps and sysadmin roles.
This article walks through converting a single‑tenant MySQL application into a SaaS solution by adding a tenant_id column, using shared‑database table partitioning, creating tenant metadata tables, writing stored procedures to add columns and partitions, and implementing a MyBatis interceptor for automatic data‑permission enforcement.
This article examines a real‑world MySQL deadlock that occurred during a large promotion, explains the various lock types (shared, exclusive, gap, next‑key, insert‑intention), shows code and lock‑log examples, and derives best‑practice recommendations for preventing similar concurrency issues.
This tutorial explains how to use PHP and PDO to quickly insert ten million rows into a remote MySQL database by preparing the connection, employing batch inserts, managing transactions, tuning MySQL settings, and optionally using the LOAD DATA statement for optimal performance.
This article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains how the original use of EXISTS and NOT EXISTS caused performance bottlenecks, and details two successive rewrites—first using JOIN + GROUP BY, then replacing NOT EXISTS with NOT IN—bringing execution time down to 0.7 seconds while highlighting key indexing and testing lessons.
This article explains what MySQL general tablespaces are, outlines their main features and benefits, provides step‑by‑step commands for creating, managing, and assigning tables to them, and shows monitoring queries and practical usage scenarios for improved storage efficiency and performance.
This newsletter announces the SQLE 3.2401.0-pre1 release with automatic data masking, shares recent community development work, outlines upcoming plans, and curates several technical articles covering MySQL EXPLAIN enhancements, transaction fundamentals, hardware considerations for database stability, consistent backup techniques, and efficient JDBC connection testing.
This article presents a systematic, nine‑grid method for reducing MySQL storage expenses—including table compression, JSON field serialization, and hot‑cold data separation—while quantifying benefits, ensuring data safety, and validating system stability through staged testing and SRE metrics.
Learn how to connect Go applications to MySQL using the native driver and the GORM ORM, covering driver installation, basic connection code, database configuration, query execution, and best practices for model definition and handling common pitfalls.
This guide explains MySQL stored procedures, covering their definition, benefits, parameter types, creation syntax, delimiter handling, multiple practical examples, calling conventions, debugging tips, and hands‑on exercises, enabling developers to efficiently write, test, and manage reusable SQL routines.
This article walks through a MySQL 5.6 scenario where a sub‑query that scans 700,000 rows takes over 30,000 seconds, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans reduces the runtime to milliseconds, highlighting best‑practice indexing techniques.
This article explains how InnoDB in MySQL 8.0.32 manages transaction objects through a transaction pool and a single pool manager, detailing pool creation, initialization of a limited number of objects, and the on‑demand expansion strategy to improve performance under high concurrency.
This article explains the new EXPLAIN INTO clause introduced in MySQL 8.1 for storing JSON‑formatted EXPLAIN output in a user variable and the EXPLAIN FOR SCHEMA clause added in MySQL 8.2 for running EXPLAIN on other schemas, showing how to extract costs with JSON functions and automate collection with stored procedures.
GitHub migrated over 1,200 MySQL hosts to version 8.0 through a staged, zero‑downtime process, detailing infrastructure scale, tooling, and five upgrade steps, while also highlighting new Java architecture video courses for developers.
This article explains the fundamentals of B‑Tree and B+Tree data structures, their search algorithms, and how MySQL's MyISAM and InnoDB storage engines implement these indexes to achieve efficient disk‑based query performance.
This article compiles a comprehensive set of ByteDance daily internship interview questions covering Java backend fundamentals, networking protocols, operating system concepts, Linux process management, Redis data structures and persistence, MySQL indexing and query optimization, as well as essential algorithms, providing detailed explanations and code examples for each topic.
This article explains how MySQL indexes work, compares clustered and non‑clustered indexes, outlines their advantages and disadvantages, and provides practical design guidelines to improve query performance and maintainability.
This article explains why slow queries occur in high‑traffic MySQL databases, introduces the EXPLAIN statement, walks through its syntax and output columns, demonstrates how to interpret each field with concrete examples, and provides a practical workflow for optimizing indexes to eliminate performance bottlenecks.
The article recounts a legacy project that relied on MySQL 5.6, explains common pitfalls of that version such as replication lag, heavy performance_schema overhead, binlog issues, and memory limits, then details why an upgrade to 5.7 was rolled back due to syntax changes, case‑sensitivity differences, and unresolved configuration problems.
This guide presents a comprehensive set of MySQL conventions—including engine choice, character set, naming, table and column design, indexing, and SQL usage—tailored for high‑concurrency, large‑scale internet services, explaining the rationale behind each rule to boost performance and maintainability.
This article compiles common technical interview questions for bank software positions, covering Java fundamentals, MySQL storage engines and indexes, thread‑pool parameters, HTTP GET/POST differences, TCP vs UDP characteristics, session and cookie handling, Redis data types, and C++ memory management, providing concise explanations and examples for each topic.
This article outlines the background, challenges, systematic methods, benefit calculations, data‑safety and stability checks, verification steps, rollback strategies, and gray‑deployment practices for lowering MySQL storage expenses in large‑scale billing systems while maintaining system reliability.
This article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete reasons and code examples showing how to replace NULL with meaningful defaults for more reliable database design.
Vivo replaced its fragile Python‑Celery backup system on GlusterFS—characterized by two‑day MySQL backups, single‑point failures, and weak security—with a Java‑based architecture using a Redis cluster and object storage, adding automated copying, verification, point‑in‑time recovery and migration, cutting backup windows to about ten hours and achieving near‑100 % success.
This article examines how MySQL executes a three‑table JOIN using nested‑loop algorithms, demonstrates the scanning row counts and cost calculations with concrete examples, and explains why the optimizer’s estimated fan‑out can differ from actual runtime statistics.
This article explains MySQL's binary log (binlog), a binary format file that records SQL statements for database updates, its role in master-slave replication, crash recovery, and detailed implementation mechanisms including cache management and event writing processes.
This article explains the meaning, generation process, and operational impact of the new immediate_commit_timestamp and original_commit_timestamp fields in MySQL 8.0 binlog events, including how they affect replication delay calculations, warning handling, and precise monitoring of transaction commit times.
This article explains the limitations of using JDBC for true streaming reads in Spark and Flink, demonstrates failed attempts with MySQL, shows workarounds that revert to batch processing, and recommends Flink CDC as the practical solution for incremental MySQL ingestion.
This article explains how to use MySQL's EXPLAIN statement to view execution plans, demonstrates creating sample order and customer tables, walks through each column of the EXPLAIN output, and shows how indexes affect query performance, providing a practical reference for database optimization.
This article recounts a real‑world MySQL data‑migration failure caused by an unguarded INSERT INTO SELECT that triggered OOM, full‑table scans, and row‑level locking, explains why testing missed the problem, and offers indexing and transaction‑level safeguards to prevent similar incidents.
This article examines how generative AI chatbots such as Google Bard, Bing Chat, and ChatGPT respond to database downgrade queries for MongoDB and MySQL, highlighting inaccurate or fabricated answers, providing correct downgrade procedures, and stressing the need for careful verification when relying on AI assistance.
This article explains how JVM synchronized locks and MySQL row locks can interact to cause deadlocks, demonstrates the problem with sample Spring code, shows the resulting lock‑wait timeout exception, and provides a refactoring solution to avoid the deadlock.
When a system’s data grows, MySQL queries can become slow and cause latency; this article presents practical optimization strategies—including query tuning, pagination, join improvements, subquery replacement, order‑by and group‑by enhancements, and count() efficiency—illustrated with sample tables, code snippets, and performance screenshots.
This article explains MySQL index structures, covers covered and back‑table queries, details InnoDB transaction isolation levels, classifies various lock types (table, row, gap, next‑key, intention, auto‑inc, metadata), analyzes lock behavior in multiple scenarios, discusses deadlock causes, and provides DDL and online schema‑change recommendations.
The article explains how to handle the most complex part of an e‑commerce order flow—stock deduction under high concurrency—by using JVM and distributed locks, Redis atomic operations, two‑phase commit, hot‑cold routing, fault tolerance, and cache rebuilding strategies.
GitHub upgraded over 1,200 MySQL hosts from 5.7 to 8.0 across more than 50 clusters, detailing the motivation, infrastructure, extensive preparation, a five‑step rolling upgrade process, rollback strategies, challenges like Vitess integration and replication lag, and key lessons for future database migrations.
This article explains the nature of NULL in MySQL, demonstrates why comparisons with = or != fail, shows the correct IS NULL/IS NOT NULL syntax, and discusses how NULL behaves with DISTINCT, GROUP BY, ORDER BY, and aggregate functions.
This article explains the complete execution process of a MySQL UPDATE statement, covering InnoDB page structures, tablespaces, buffer pool caching, redo/undo logs, binlog, and the two‑phase commit mechanism, helping readers understand how MySQL ensures data consistency and durability.
This article compiles essential backend interview questions and answers covering MySQL storage engines and indexes, Redis persistence modes, Java collection frameworks and HashMap internals, thread‑safe ConcurrentHashMap implementations, as well as HTTP message structure and TCP reliability mechanisms, providing a thorough review for candidates preparing for backend positions.
This article explains the concept, principles, performance benefits, and trade‑offs of MySQL indexes, illustrating with a 200‑million‑row example that shows how indexing reduces query time from days to under a second compared to full table scans.
This article explains the most common MySQL table join types—including INNER, LEFT, RIGHT, FULL OUTER, and exclusive joins—by creating sample order and customer tables, inserting data, visualizing each join with diagrams, and providing the exact SQL statements and query results.
This article examines the storage challenges faced by large‑scale internet services and evaluates popular MySQL middleware solutions—ProxySQL, MaxScale, DBProxy, MyCat, and TDDL—by mapping business requirements to their core features and capabilities.
This article details how to strengthen MySQL user security by implementing comprehensive password complexity requirements, connection control policies, and password change strategies, including configuration of the validate_password component, connection_control plugin, and password expiration settings for MySQL 5.7 and 8.0, with practical examples and code snippets.
This article explains how to build a high‑performance, million‑row Excel export feature in Java using SpringBoot, EasyExcel, asynchronous job or MQ processing, pagination, multi‑sheet handling, OSS storage, and WebSocket notifications to avoid timeouts, OOM errors, and improve user experience.
GitHub detailed a year‑long, multi‑team effort to upgrade over 1,200 MySQL hosts from version 5.7 to 8.0, describing the motivations, infrastructure scale, preparation steps, a staged rollout plan, rollback strategies, challenges faced, and key lessons learned for large‑scale database migrations.
The article explains that MySQL's sysdate() returns the exact execution time of a statement, making it non‑deterministic and preventing index usage, whereas now() returns a constant timestamp at statement start, allowing the optimizer to treat it as a constant and use indexes effectively.
This article presents a collection of seemingly humorous or "wrong" MySQL interview answers, explains why they can be insightful, and provides practical guidance on slow‑query optimization, disaster‑recovery planning, replica lag, insecure configuration, and handling massive schema changes.
This comprehensive guide explores MySQL transaction fundamentals, the ACID properties, various isolation levels, concurrency issues like dirty, non‑repeatable, and phantom reads, and dives deep into MVCC implementation, hidden fields, undo logs, and read views to help developers write reliable, consistent database code.
Using MySQL's EXISTS() function for existence checks is generally more efficient and readable than COUNT(), especially in large datasets, and Laravel provides convenient query builder methods to implement EXISTS() queries, offering performance gains over COUNT() in PHP applications.
GitHub’s engineering team detailed a year‑long, multi‑team effort to upgrade over 1,200 MySQL hosts from 5.7 to 8.0, preserving high availability, SLO compliance, and rollback capability while introducing new features and performance improvements.
This article presents a comprehensive, step‑by‑step methodology for cutting MySQL storage expenses—including background analysis, challenge identification, a nine‑grid systematic framework, benefit calculation, safety and stability verification, rollout, gray‑release, and rollback—demonstrating over 50% disk space reduction while managing risk.
Real‑time monitoring of CPU, memory, disk I/O and network bandwidth during performance testing helps identify bottlenecks, prevent crashes, collect actionable data, ensure accurate results, and meet SLA requirements, and a Python script can automate data collection and storage in MySQL.
GitHub detailed its year‑long, multi‑team effort to seamlessly upgrade over 1,200 MySQL servers—supporting more than 300 TB of data and 5.5 million queries per second—from 5.7 to 8.0, outlining the infrastructure, tools, and step‑by‑step migration strategy used to maintain service reliability.
This article explains the various lock types in MySQL—including global, table, row, metadata, and intent locks—how they work under InnoDB, demonstrates lock acquisition with SQL examples, and provides practical guidance for detecting and resolving deadlocks in production environments.
This article explains how to deploy and use Alibaba Canal to capture MySQL binlog changes, configure MySQL and Canal, and write Java code that listens to row‑level events for synchronizing data to other systems such as Redis or Elasticsearch.
This article explains MySQL index types, B+Tree structures, secondary indexes, and demonstrates how enabling Index Condition Pushdown (ICP) can accelerate a query on a 6‑million‑row table by roughly ninety times, providing detailed setup, commands, and performance results.
This article examines how MySQL's JDBC driver manages ResultSet resource release, the internal close mechanisms, and the concurrency limitations of Statement objects, illustrated with code excerpts and a Groovy virtual‑thread demo that reveals runtime exceptions when ResultSets are accessed after implicit closure.
This article explains the fundamentals of MySQL master‑slave replication, identifies why replication lag occurs—including single‑threaded processing, lock contention, network issues, and outdated versions—and offers concrete strategies such as hardware upgrades, configuration tweaks, and architectural changes to minimize the delay.
This article explains how commercial banks can analyze MySQL lock‑wait events and transaction timeouts using MDL metadata lock monitoring, innodb_lock_wait_timeout, custom MyAWR collection, SQL de‑parameterization, and post‑mortem queries to pinpoint blocking SQL and its source.
This article reviews MySQL's execution process and presents a comprehensive list of practical SQL optimization strategies—including indexing, query rewriting, avoiding costly operators, using batch operations, and leveraging EXPLAIN—to improve database performance and maintainability.
This article walks through a step‑by‑step investigation of why a MySQL 8.0 instance cannot be accessed remotely after adding configuration variables, identifies the skip_networking setting as the root cause, and explains how to correct the configuration for proper TCP/IP connections.
This article explains the differences between utf8, utf8mb3 and utf8mb4 character sets in MySQL, demonstrates how utf8mb4 enables full Unicode support including emojis, and provides step‑by‑step SQL examples for creating tables, inserting data, and querying results with the proper character set.
Oracle’s MySQL 8.2 introduces built‑in read/write splitting via MySQL Router, allowing write traffic to target primary instances while reads are automatically routed to secondary replicas, with configuration examples, command‑line bootstrap, routing settings, and practical observations on transaction behavior.
GitHub detailed a year‑long, multi‑team effort to upgrade over 1,200 MySQL hosts from 5.7 to 8.0 using phased rollouts, automated testing, compatibility checks, and rollback mechanisms while maintaining strict SLOs and high‑availability requirements.
This article outlines a comprehensive strategy for rapidly loading one billion 1 KB log records stored in HDFS or S3 into MySQL, covering data partitioning, B‑tree limits, batch inserts, storage‑engine selection, sharding, concurrent write control, efficient file reading, task coordination, fault‑tolerant progress tracking with Redis, and distributed locking with Redisson.
This guide explains how to implement server‑side WebSocket message persistence using MySQL, covering requirement analysis, database schema design, Think‑ORM integration, event handling code, testing scripts, and performance considerations for high‑concurrency scenarios.
The article investigates whether the long‑standing rule of avoiding MySQL tables larger than 20 million rows still holds on modern SSD‑backed instances by creating tables of varying sizes, measuring query latency under different InnoDB buffer pool configurations, and analyzing the impact of caching and index usage.
This article explains how to implement online traffic replay for Python automated testing by parsing logs, storing request data in a local MySQL database, analyzing user information with data‑analysis libraries, and locally persisting URL, method, headers, and body details using JSON files, complete with sample code.
An in‑depth analysis shows that MySQL’s early Windows support, lower entry barrier, strong LAMP ecosystem, backing from major Chinese tech firms, and a mature tooling landscape together explain why it remains far more popular than PostgreSQL across China despite global growth trends.
Transaction isolation prevents concurrency issues such as dirty writes, dirty reads, non‑repeatable reads, and phantom reads in MySQL, and the article explains these problems, the ACID properties of transactions, and the four isolation levels—read uncommitted, read committed, repeatable read, and serializable.
This article explains MySQL's explicit and implicit locking mechanisms, illustrates how transaction isolation levels affect lock scope, provides practical SQL examples for various scenarios, and offers guidelines on when to apply pessimistic locks versus optimistic approaches to maintain database performance.
This article analyzes a slow MySQL query that scans millions of rows to find students scoring 100 in Chinese, then demonstrates how adding appropriate single‑column and composite indexes, rewriting the query as a join, and using covering indexes can reduce execution time from hours to milliseconds.
This article explains the various lock types in MySQL's InnoDB engine, how they are applied to different SQL statements, and the impact on concurrency, isolation levels, and transaction behavior.
This article introduces three free MySQL client tools—DBeaver, MySQL Workbench, and HeidiSQL—as practical alternatives to the paid Navicat, detailing their download links, installation steps, key features, and why each may suit different user needs.
The article walks through a real‑world case of a slow MySQL query that took 8 seconds, explains why simple indexing wasn't enough, and demonstrates two successive rewrites using JOIN‑GROUP BY and NOT IN that reduced execution time to 0.7 seconds while detailing the EXPLAIN analysis.
This article explains the characteristics of various MySQL join algorithms—including Simple Nested‑Loop, Index Nested‑Loop, Block Nested‑Loop, Hash Join, and Batched Key Access—demonstrates how to examine execution plans, configure optimizer settings, and apply best‑practice optimizations such as indexing, choosing the small driver table, and upgrading MySQL versions.
This article recounts three production‑level pagination bugs encountered in a payment system, explains why they occurred—ranging from naive batch processing to MySQL's nondeterministic LIMIT behavior—and shows how proper pagination, ThreadLocal cleanup, and deterministic ordering resolve the issues.
This article presents a concise tutorial on building a flash‑sale (Seckill) system with SpringBoot, MySQL, Redis, RabbitMQ and a basic HTML/JQuery front‑end, covering architecture, key features, code snippets, over‑selling prevention, rate limiting, and deployment screenshots.
This tutorial walks you through the fundamentals of MySQL binlog replication, installing and configuring Canal, setting up Elasticsearch, Kibana, and the IK analyzer, and then demonstrates both full and incremental data synchronization from MySQL to Elasticsearch.
Effective index design, including single‑table, foreign‑key, composite, and covering indexes, along with optimizer hints, can dramatically improve the performance of multi‑table queries in PHP‑MySQL applications, and the article provides practical examples and SQL statements for each technique.
This article provides an in‑depth tutorial on MySQL performance tuning, covering basic architecture, various index types, the EXPLAIN execution plan, common pitfalls, and practical optimization techniques with numerous SQL examples and code snippets.
This guide walks through installing the open‑source JumpServer bastion host on a Kubernetes cluster using Helm, configuring external MySQL and Redis databases, customizing Helm values, and exposing the web UI through an Istio gateway for secure access.
This article explains MySQL InnoDB's lock mechanisms and index structures, covering primary and secondary indexes, record, gap, and next‑key locks, and how they affect query performance and transaction isolation in high‑concurrency environments.
This article provides a comprehensive, step‑by‑step guide for performing an in‑place upgrade of MySQL 8.0 (including MGR clusters) from earlier versions, covering pre‑upgrade checks, backup procedures, configuration changes, data dictionary upgrade, server upgrade, verification, rollback, and common pitfalls.
This tutorial demonstrates how to prompt Tencent Hunyuan’s large‑model API to generate a Spring Boot project configured for MySQL, create a user table, and implement full JPA‑based CRUD layers—including entity, repository, service, and REST controller—while addressing common null‑field errors and testing the endpoints.
This guide presents a comprehensive set of SQL statements for the idc_evaluating database, illustrating how to retrieve rows by province or IP, count and delete active IPs, describe table structures, perform conditional aggregations, and execute inner‑join queries with calculated fields.
This article explains how Quora reduces database load by optimizing read patterns, improving high‑QPS queries with smarter caching, shrinking table size using MyRocks, and boosting write throughput through parallel replication settings, providing concrete steps, code snippets, and real‑world results.
This article walks through the full engineering process of importing a billion 1 KB log records into MySQL, covering data size calculations, B‑tree index limits, batch‑insert strategies, storage‑engine trade‑offs, fast file‑reading techniques, task coordination with Redis and Redisson, and distributed‑lock design using Zookeeper.
This article experimentally compares the performance of JOIN and IN approaches for retrieving order and user data in MySQL, showing that JOIN is faster on small datasets while IN may become costly with large data volumes, and discusses code implementations and practical conclusions.
This article provides a thorough overview of Java interview topics, covering core language fundamentals, collection frameworks, concurrency mechanisms, Spring Boot transaction handling, database indexing strategies, and network protocol comparisons, all presented in clear English with code examples and diagrams.
This article demonstrates, with step‑by‑step examples, how to capture MySQL changes via Flink CDC and stream them in real time into Apache Doris using the Doris Flink Connector, covering CDC concepts, connector features, environment setup, SQL client usage, and data verification.
This article debunks the common belief that MySQL VARCHAR can store up to 65535 characters, showing how the true limit depends on the chosen character set, NULLability, column count, and row‑format details, and explains how oversized values are stored or require TEXT/BLOB types.
This article explains why MySQL master‑slave replication can become delayed, outlines the differences between DDL and DML, shows how to diagnose lag with show slave status, and provides architectural, hardware, and configuration solutions—including semi‑synchronous replication and tuning parameters—to reduce latency.
To import one billion 1 KB log records into MySQL efficiently, the article examines data size constraints, B‑tree index limits, batch insertion strategies, storage engine choices, file‑reading techniques, task coordination with Redis, Redisson semaphores, and distributed lock handling to ensure ordered, reliable, high‑throughput loading.
Running ANALYZE TABLE on MySQL, Percona Server, or MariaDB can block queries in older versions, but from MySQL 8.0.24, Percona Server 5.6.38+, 5.7.20+, and MariaDB 10.5.4+, the operation is safe and no longer causes disruptive locks.
This article explains MySQL's fault‑tolerance mechanisms, covering data persistence, storage engines, the binary, redo, and undo logs, WAL, two‑phase commit, and step‑by‑step recovery procedures for accidental data loss or crashes.
This article explains why MySQL's LIMIT offset,size becomes slower as the offset grows, compares it with LIMIT size, details the internal execution using primary and secondary indexes, discusses deep pagination issues, and presents practical optimization techniques to improve query performance.
This article examines eight frequent MySQL performance problems—including misuse of LIMIT, implicit type conversion, sub‑query updates, mixed ordering, EXISTS, condition push‑down, early limiting, and intermediate result push‑down—and provides concrete rewrite techniques and example code that dramatically reduce execution time.
This article explains why database sharding is needed, the problems caused by oversized databases and tables, and how to apply vertical and horizontal splitting using MyCat, including configuration examples, splitting strategies, and testing procedures.
