This article provides a curated collection of over 250 interview question links covering Java fundamentals, Spring framework, databases, Redis, messaging, concurrency, and system design, organized by issue numbers to help developers prepare for technical interviews.
This article explains the difference between MySQL multi-value indexes and composite indexes, demonstrates how to create and query multi-value indexes using JSON columns, and shows performance benefits and practical scenarios when dealing with tables that have a large number of columns.
This article details how the government talent housing system adopted the open‑source Seata distributed‑transaction framework, identified severe performance bottlenecks during large‑scale batch operations, and applied a series of optimizations—including query‑lock rewriting, rollback‑info compression, JDBC batch tuning, and multithreaded processing—to reduce a 30000‑record transaction from 280 seconds to under 7 seconds, achieving production‑grade reliability.
This article evaluates the insertion speed and index impact of three MySQL primary‑key strategies—auto‑increment, UUID, and Snowflake‑generated long IDs—by building identical tables, running a Spring Boot/JdbcTemplate benchmark, and analyzing the resulting performance and structural differences.
This article shares an interview experience that compares MongoDB and MySQL, explains the advantages and disadvantages of document‑oriented and relational databases, introduces other NoSQL solutions such as Elasticsearch, and offers guidance on choosing the right storage technology based on workload characteristics.
This article explains why a MySQL join query may ignore an index because of implicit character‑set conversion, demonstrates how to diagnose the issue with EXPLAIN and schema queries, and provides a fix by converting the column to UTF8 in the SQL statement.
This article explains the principles, architecture models, and concrete implementations of high‑availability for MySQL and PostgreSQL, covering data‑sync modules, HA management, unified access, and cloud‑native considerations with detailed examples and best‑practice recommendations.
This article explains the purpose, usage, and return codes of the MHA masterha_secondary_check Perl script, demonstrates how to simulate network failures with iptables, and presents a series of tests that verify the script’s ability to detect master reachability and handle various error conditions.
This guide walks through setting up a Spring Boot 2.2.11 project with Activiti 7.1, configuring Maven dependencies, explaining Activiti table prefixes, defining core services, creating a BPMN leave request process, configuring security and datasource, and exposing REST APIs for deployment, execution, and task handling.
This article explains why growing MySQL workloads require moving from a single instance to a clustered architecture, details the mechanics of master‑slave replication, asynchronous, semi‑synchronous and group replication, and evaluates various high‑availability solutions and read‑write splitting techniques.
This article compiles a comprehensive set of MySQL interview questions covering index structures, B‑tree vs. hash trade‑offs, primary‑key and clustered‑index design, ACID properties, isolation levels, lock types, storage‑engine choices, data‑type considerations, binlog formats, pagination strategies, sharding, stored procedures, normalization, and MyBatis parameter syntax.
This tutorial shows how to create a multithreaded Python web scraper that fetches news from Sina and NetEase, stores the results in MySQL, generates a live word‑cloud, and serves the data through a simple Flask web application with an HTML front‑end.
This article walks through a systematic SQL optimization workflow—identifying slow queries, interpreting EXPLAIN output, using SHOW PROFILE and TRACE, applying concrete tuning measures, and dissecting ten representative MySQL cases ranging from index ordering to large‑page handling and complex query pitfalls.
This comprehensive guide walks through MySQL's advanced concepts—including lock types, transaction control, SQL security, mode settings, regular expressions, common tricks, case sensitivity, foreign‑key support, and a wide range of built‑in functions—providing clear examples and practical commands for each topic.
This article explains MySQL's lock levels (table, page, row), the next‑key, gap, and record lock algorithms, illustrates common deadlock scenarios with detailed session examples, and discusses InnoDB's deadlock prevention strategies and practical solutions for avoiding lock conflicts.
The article experimentally evaluates three ways of creating replication users—only on the master, separately on master and slave with CREATE statements, and separately with GRANT statements—to show how each method affects MySQL 5.7 master‑slave replication, error handling, and compatibility with GTID and MySQL 8.0.
The article explains two technical methods for keeping MySQL and Redis in sync—using MySQL triggers with a custom UDF to write directly to Redis, and parsing MySQL binlog streams (or using Alibaba's Canal) to propagate changes, while discussing their suitable scenarios, challenges, and implementation details.
This article explains two practical methods for keeping MySQL and Redis cache in sync—using MySQL triggers with UDF functions or parsing binlog events—and introduces the open‑source Canal project for incremental log parsing, while also discussing the pitfalls of reverse synchronization.
This tutorial walks through MySQL's transaction control, lock statements, autocommit settings, manual commit and rollback, savepoints, differences between transactional and non‑transactional tables, SQL mode configurations, common SQL tricks, regular expressions, and a comprehensive catalog of built‑in functions, all illustrated with step‑by‑step examples and code snippets.
This article explains how MySQL unique indexes influence auto‑increment behavior, compares unique constraints with primary keys, and demonstrates three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to avoid duplicate‑key errors, including their effects on auto‑increment values and potential deadlocks.
This article analyzes how adjusting the MHA ping_interval and enabling secondary_check_script can increase MySQL high‑availability cluster tolerance to network packet loss and corruption, presenting test setups, parameter configurations, log observations, and conclusions on their impact on failover behavior.
This article describes how to improve the performance of a massive MySQL 5.6 user‑log table by redesigning schema, applying proper indexes, using partitioning, considering table sharding, and evaluating upgrade paths to compatible cloud or big‑data databases, with concrete SQL examples and cost analysis.
This article explains how MySQL UNIQUE indexes affect data insertion and auto‑increment behavior, compares them with primary keys, and presents three techniques—INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—to prevent duplicate‑key failures, including deadlock considerations.
A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
This article explains how to identify which MySQL tables in an Alibaba Cloud RDS environment should be synchronized across clouds and which can be excluded, covering key concepts, design and operational practices, a real‑world failure case, and recommended mitigation and improvement steps for application‑level disaster recovery.
This article explains what a prefix (partial) index is in MySQL, why it can reduce index size and improve query speed, outlines suitable scenarios and limitations, and demonstrates performance differences through a series of tests with varying prefix lengths on a large test table.
This article provides a detailed explanation of MySQL InnoDB's lock system, covering lock granularity, shared and exclusive locks, intention and auto‑increment locks, various row‑lock types, deadlock scenarios with examples, and the internal representation of locks in the engine.
This article explains InnoDB's multi‑granularity locking system—including table, row, intention, auto‑increment, and gap locks—detailing their modes, compatibility, internal representation, usage guidelines, deadlock scenarios, and best practices for avoiding concurrency issues in MySQL databases.
This article explains how a growing e‑commerce platform replaced a heavyweight MySQL intermediate table with a binlog‑driven pipeline that streams changes to Elasticsearch via Kafka, detailing the architecture, modules, customizations, monitoring, and performance results.
This article summarizes a real‑world MySQL database migration project, detailing pre‑migration preparation, backup and import commands, replication setup—including multi‑source replication for merging databases—and post‑migration verification steps, while sharing practical tips and pitfalls.
This article analyzes the intermittent “Public Key Retrieval is not allowed” error in MySQL 8.0 when using JDBC, C3P0, or Druid connections, explains the authentication plugins involved, and provides step‑by‑step configuration fixes and best‑practice recommendations.
This article explains XtraBackup's backup workflow, introduces the --parallel and --compress-threads options, and presents performance test results showing how these parameters can significantly reduce backup time for large MySQL databases.
This guide walks through setting up an Oracle‑MySQL transparent ODBC gateway on RHEL, covering driver installation, odbc.ini and listener configuration, DBLINK creation, a common WHERE‑clause loss problem, and the solution of switching to the ANSI ODBC driver.
This article reviews MySQL composite index syntax, explains the mandatory condition that the leftmost column must appear in the query filter, analyzes seven representative SQL statements that cannot benefit from a composite index, and provides practical refactoring suggestions such as adding single‑column indexes or creating more appropriate composite indexes.
This tutorial walks through upgrading a Spring Boot 2.x project to Java 11, updating dependencies such as MyBatis and MySQL connector, configuring the Baidu UID generator database schema, adjusting MySQL settings, and building a uid‑provider microservice with full Maven and code examples.
The article compares three stock‑deduction approaches—single‑record MySQL, sharded MySQL rows, and Redis INCRBY with Lua scripts—analyzes their concurrency drawbacks, and presents a complete Java implementation using Redis, distributed locks, and callback‑based stock initialization.
This guide explains how to enable the PDO extension, configure php.ini, write connection code with proper error handling, and debug SQL statement errors to resolve common MySQL connection problems in PHP on Windows environments.
This article explains MySQL AUTO_INCREMENT behavior, demonstrates creating tables with unsigned int primary keys, shows how to set and inspect the AUTO_INCREMENT value, discusses its maximum limits, and recommends using bigint or explicit primary keys to avoid overflow and duplicate‑key errors.
This article explains how ordered data indexes improve query efficiency, compares B+Tree and hash indexes, discusses table partitioning versus sharding, outlines MVCC read types, examines row‑level lock pros and cons, and provides practical MySQL optimization tips including key vs index differences and engine choices.
This article explains why database passwords should be encrypted, introduces Alibaba's Druid as a simple solution for Java Spring Boot projects, and provides step‑by‑step instructions—including generating ciphertext with ConfigTools, configuring application.yml, and securely handling public keys—to protect MySQL credentials.
This article provides a comprehensive overview of MySQL InnoDB's lock subsystem, describing its data structures, lock compatibility matrices, lock/unlock workflows, lock splitting, inheritance and migration, and presents several optimization techniques such as avoiding lock_sys mutex, asynchronous deadlock detection, and lock_sys mutex sharding to alleviate contention in MySQL 5.7 and later versions.
This article explains why Java applications occasionally encounter the "Public Key Retrieval is not allowed" error when connecting to MySQL 8.0, analyzes the role of caching_sha2_password and user authentication cache, and provides practical solutions such as specifying the RSA public key, enabling AllowPublicKeyRetrieval=True, or switching to mysql_native_password.
This article recounts a costly MySQL data‑migration failure caused by an unchecked INSERT INTO SELECT that triggered full‑table scans, lock contention and out‑of‑memory errors, and explains how proper indexing and awareness of transaction isolation can prevent such incidents.
This article explains how MySQL versions prior to 8.0 automatically sorted GROUP BY results, why the optimizer added this hidden ordering, how the behavior was removed in MySQL 8.0, and what developers must watch for when migrating, illustrated with concrete SQL examples and EXPLAIN output.
This article recounts a production server data loss caused by an erroneous rm -rf command, describes how ext3grep and extundelete were used to attempt file restoration, and explains how MySQL binlog replay finally recovered critical data while sharing lessons learned for future incident handling.
The DB‑Engines March 2021 ranking evaluates 364 databases, highlighting shifts among the top ten—including Microsoft SQL Server’s steep decline, MySQL’s growth surge, PostgreSQL’s continued rise, and Snowflake’s dramatic climb—while offering trend graphs and insights for professionals choosing the right database technology.
This tutorial explains how to perform asynchronous MySQL operations in Python using aiomysql, covering basic connections, CRUD actions, safe parameter handling to prevent SQL injection, connection pooling, transaction management, integration with SQLAlchemy for ORM queries, and a custom reconnection wrapper for resilient database access.
This article experimentally demonstrates how increasing MySQL's innodb_log_buffer_size reduces redo‑log write I/O, showing fewer write operations but similar total data volume, and explains why performance gains may be subtle in typical virtual‑machine storage environments.
This article explains how to diagnose and resolve MySQL slave replication failures that occur when XtraBackup’s --kill-long-queries-timeout and --kill-long-query-type options interrupt long‑running queries, detailing steps such as checking performance_schema, reviewing error logs, restarting replication, and adjusting backup parameters.
This article reviews the history of MySQL, introduces MariaDB as its open‑source fork, and presents a series of benchmark tests—including single‑row inserts, batch inserts, and various query scenarios—showing that MariaDB generally outperforms MySQL in speed while using more memory.
This article walks through a step‑by‑step optimization of a Java‑based Excel import pipeline, covering the transition from raw POI to EasyPOI and EasyExcel, caching database lookups, batch inserts, optimal batch sizes, parallel streams, and logging reductions to achieve sub‑minute import times for hundreds of thousands of rows.
This article provides a detailed shell-script tutorial for installing MySQL 5.7 on a Linux node, initializing the database, setting the root password, and automatically creating multiple user accounts with random passwords, suitable for operations and database administrators. The guide includes full script listings, explains repository configuration, service management, and error handling, making it a practical reference for DevOps engineers.
An engineer’s costly mistake using MySQL’s INSERT INTO SELECT for nightly data migration led to out‑of‑memory crashes, full‑table scans, and payment record loss, prompting a deep dive into locking behavior, transaction isolation, and how proper indexing can safely rescue large‑scale inserts.
This article explains why caching MySQL with Redis is essential, outlines the consistency challenges that arise, and presents four practical solutions—expiration, delete‑then‑repopulate, message‑queue updates, and binlog subscription—along with their pros, cons, and guidance for choosing the right approach in interviews.
This article explains the technical details of data cleaning, dirty‑data handling, and three methods—database read‑only, application termination, and network ACL isolation—to ensure data consistency during the data‑regulation and cutover phases of cross‑cloud migration, illustrated with real‑world case studies.
This article explains how to use MySQL's EXPLAIN FORMAT=JSON to view detailed execution plan costs, illustrates table structures, walks through two query examples with cost breakdowns, and clarifies the calculation of read_cost and eval_cost for both simple and join queries.
This article explains how enterprises can evaluate their needs and compare four popular open‑source relational databases—MySQL, PostgreSQL, MariaDB, and SQLite—covering licensing, performance, language support, strengths, and limitations to guide an informed selection.
This article outlines practical techniques for multi‑cloud data migration, covering challenges, resource categories, migration phases, and detailed synchronization and verification methods for MySQL databases, file systems, and object storage using tools such as UDTS, pt‑table‑checksum, sync_diff_inspector and US3SYNC.
During a holiday‑time alert, a seemingly harmless pair of UPDATE statements on the same InnoDB table caused a deadlock, and the article walks through log analysis, reproduction steps, lock mechanics, and practical ways to avoid such deadlocks in MySQL.
This guide outlines a step‑by‑step learning roadmap for PHP beginners, covering HTML/CSS/JS fundamentals, core PHP syntax, database integration with MySQL, object‑oriented programming, and popular frameworks, while emphasizing practice, project building, and continuous skill refinement.
This article explains practical strategies for adding a new column to a MySQL table with tens of millions of rows, including creating a temporary table, copying data, renaming tables, and alternative replica‑based or third‑party methods to avoid long locks and potential crashes.
This article introduces Python's DB-API for interacting with various databases, explains how to use PyMySQL and MySQLdb for MySQL operations, demonstrates basic CRUD examples, shows techniques to prevent SQL injection, and presents connection pooling solutions with DBUtils for multi‑threaded applications.
This article explains how MySQL composite indexes work, the syntax for creating them, the conditions required for their use, and how different query patterns affect index utilization, illustrated with multiple SQL examples and execution‑plan analyses.
This article explains practical techniques for handling dirty data, guaranteeing data consistency, and performing validation during the data cleaning and cut‑over phases of a cross‑cloud migration, including read‑only database accounts, process termination, and network ACL isolation.
This article explains how incorrectly placed double‑quotation marks in MySQL UPDATE and SELECT statements cause implicit type conversion, turning intended string assignments into zero values and making WHERE clauses always true, leading to massive data corruption and unexpected query results.
This article examines several MySQL pagination techniques—including basic LIMIT, subquery‑based, ID‑range, and temporary‑table methods—provides detailed performance measurements on a multi‑million‑row table, and offers practical recommendations for fast and scalable data retrieval.
This tutorial walks through creating a Spring Boot project, configuring Maven dependencies, setting up MySQL with MyBatis, defining entity, mapper, service, and controller layers, and building simple Thymeleaf login and registration pages, culminating in a runnable dynamic authentication demo.
This article explains how to replace shell‑script based lock‑wait monitoring with MySQL Event and Stored Procedure mechanisms, showing step‑by‑step creation of a monitoring database, procedure, scheduled event, and how to interpret the generated lock‑wait log table for troubleshooting.
This article explains MySQL transaction fundamentals, the ACID properties, common concurrency problems, isolation level differences, and how InnoDB implements MVCC using version chains and consistency views, illustrated with practical examples and diagrams.
This guide explains how to use MySQL’s built‑in mysqlslap tool and the third‑party sysbench utility to perform comprehensive database stress testing, covering configuration changes, command examples, result interpretation, and additional CPU and I/O benchmarks for accurate performance evaluation.
This article explains how MySQL treats NULL values, why columns defined with NULL can invalidate indexes, demonstrates the behavior of IS NULL, IS NOT NULL, the spaceship operator, IFNULL, COUNT, DISTINCT, GROUP BY and ORDER BY with NULLs, and offers practical recommendations for schema design.
This article analyzes common causes of slow MySQL queries—especially index misuse—offers practical indexing techniques, explains MDL locks and large‑table bottlenecks, and then compares ElasticSearch and HBase as complementary solutions for high‑performance search and storage.
This article reviews MySQL's evolution of high‑availability solutions—from classic master‑slave replication to InnoDB Cluster and InnoDB Replica Set—explaining each component, their features, deployment steps, and how to select the appropriate architecture based on business requirements such as RTO, RPO, and regional scope.
This tutorial demonstrates how to create a MySQL master‑slave replication cluster on Kubernetes using a StatefulSet, local persistent volumes, ConfigMaps, Secrets, and Services, covering storage class configuration, pod initialization, data cloning, replication setup, verification, and horizontal scaling.
This article compiles 52 practical SQL tuning strategies—including index best practices, query rewriting, use of temporary tables, proper data types, and server configuration tips—to dramatically improve MySQL query speed and overall database efficiency.
This article demonstrates how to create a test table, build indexes, and analyze their effectiveness across multiple query scenarios using MySQL's EXPLAIN output, highlighting the best‑left‑prefix rule, range condition impacts, ORDER BY considerations, and common pitfalls such as Using filesort and temporary tables.
This article traces MySQL’s evolution, introduces MariaDB’s origins, and presents a comprehensive benchmark comparing insert and query performance of MySQL 8.0.19 and MariaDB 10.4.12 on identical hardware, highlighting speed differences with and without indexes.
This article presents a systematic benchmark of MySQL 5.7.22 and MySQL 8.0.15 using sysbench to measure TPS and QPS across read‑write, read‑only, and write‑only modes under two persistence configurations, analyzes the results, and provides a Bash script for reproducing the tests.
This case study explains how a client with a 2‑billion‑row MySQL table avoided disk exhaustion and poor query performance by streaming data through Kafka to Google BigQuery, partitioning the data, and cleaning it to reclaim storage—all without downtime.
This article analyzes how many TCP connections MySQL will open when writing data at 100 rows per second for five seconds, examining MySQL processing capacity and connection‑pool configuration to illustrate the key factors that affect connection count.
This article explains the challenges of cross‑cloud migration, outlines three resource categories, details MySQL, file and object storage synchronization methods, presents practical tools and validation techniques, and shares real‑world case studies to ensure data integrity and efficiency.
This article explains the meaning of the Extra column in MySQL EXPLAIN output, covering how "Using index", "Using index condition", and "Using where" appear, when index coverage and index condition push‑down occur, and provides multiple practical examples with code.
This article investigates why the Alibaba Java Development Manual advises against joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, indexing effects, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article traces MySQL’s evolution, introduces its fork MariaDB, and presents a comprehensive performance benchmark—covering single and batch inserts, indexed and non‑indexed queries—on identical hardware, revealing that MariaDB generally delivers faster execution times albeit with higher memory usage.
This article explains MySQL's Partial Revokes feature introduced in version 8.0.16, showing how to enable the partial_revokes system variable, grant and selectively revoke SELECT privileges at both coarse and fine granularity, and verify the resulting restrictions using SHOW GRANTS and the mysql.user table.
This article explains why using ORDER BY with LIMIT for pagination in MySQL can yield inconsistent results when the ordering column contains duplicate values, describes MySQL's limit optimization behavior, and provides a reliable solution by adding a secondary unique sort key.
The article investigates the discrepancy between MySQL binlog execution time and slow query log duration for an INSERT statement, conducts an experiment with artificial I/O latency, and explains why the slow log provides a more complete measurement of query execution.
This article explains why, in MySQL row‑based replication, a master table having an extra column does not disrupt replication to a slave table lacking that column, provided column order, placement, and default values meet specific conditions, and demonstrates the behavior with a MySQL 5.7.25 test.
This article explains the fundamentals of MySQL table lookup (回表操作), compares low‑version and high‑version handling, and demonstrates how index pushdown reduces unnecessary lookups, using clear diagrams and a practical user‑table example.
This article explains the difference between logical and physical pagination, demonstrates how MySQL's LIMIT clause works, compares two query methods on a large MEMORY table, analyzes their performance with indexes and EXPLAIN, and provides practical pagination examples and Java code for efficient data retrieval.
The article analyzes a production incident where dozens of MySQL UPDATE statements unintentionally set a column to zero because misplaced quotation marks caused MySQL to interpret the expression as a boolean comparison with implicit type conversion, and it explains how to detect and prevent such errors.
This article explores why MySQL queries become slow, delves into index pitfalls and optimization techniques, then compares ElasticSearch and HBase architectures, offering practical guidance on when to use each technology and how to combine them for high‑performance data retrieval.
This guide explains how to configure MySQL for high‑concurrency testing, use the built‑in mysqlslap tool and the third‑party sysbench utility to benchmark read/write performance, CPU, I/O, and memory, and interpret the resulting metrics for database optimization.
This article explains the meaning of each column in MySQL's EXPLAIN output, including id, select_type, type, possible_keys, key, key_len, ref, rows, filtered, and Extra, with examples of various query patterns such as simple selects, joins, subqueries, UNIONs, and derived tables.
The article investigates the Alibaba Java Development Manual's rule against joining more than three tables by designing and executing extensive MySQL and Oracle experiments, generating massive test data, measuring query performance, and concluding that the restriction stems from join scalability limits on large datasets.
This article breaks down nine core MySQL index concepts—including index types, B+Tree advantages, search steps, covering indexes, index invalidation, left‑most prefix rules, index push‑down, and safe index creation on large tables—to help candidates answer interview questions about tree‑search operations and performance trade‑offs.
The article analyzes a real‑world incident where a series of MySQL UPDATE statements with incorrectly placed quotation marks caused the entire source_name column to be set to zero, explains MySQL's implicit type conversion behavior, and shows how to recover the data using binlog analysis and flashback SQL.
This article provides a comprehensive overview of MySQL InnoDB's Buffer Pool, explaining its memory layout, data and cache pages, free, flush, and LRU lists, hot‑cold separation, dynamic resizing with chunk mechanisms, and practical configuration tips for production environments.
This article explains the root causes of MySQL master‑slave replication lag—including random replay, high master concurrency, and lock waits—and presents practical solutions such as parallel replication, reducing master load, and reading from the master to ensure data consistency.
This article demonstrates a step‑by‑step method for diagnosing MySQL hangs by collecting callstack information, identifying mutex conflicts, and mapping function calls to code locations to pinpoint the root cause of deadlocks.
