This article explains several reliable techniques for renaming a MySQL database—including full export/import with mysqldump, bulk table‑rename operations, and legacy binlog‑based approaches—while providing concrete command‑line examples, performance measurements, and step‑by‑step instructions for each method.
This article explains the actual logical execution order of SQL statements, clarifies common misconceptions about WHERE, GROUP BY, and SELECT, shows how column aliases affect processing, discusses optimizer reordering, and compares the order with LINQ and pandas query patterns.
This guide explains how to use MySQL's built‑in mysqlslap tool and the third‑party sysbench utility to simulate concurrent client loads, adjust server settings, run read/write and I/O tests, and interpret the resulting performance metrics for database optimization.
This article investigates the Alibaba Java Development Manual's recommendation against joining more than three tables by experimentally evaluating multi-table join performance in MySQL and Oracle using massive synthetic datasets, analyzing query execution times, indexing effects, and providing data generation scripts and detailed results.
The article details how to handle test user module data by converting ID fields to strings, validating optional parameters, and configuring complex MyBatis XML mappings alongside a Java bean, illustrating the full workflow from requirements to implementation and testing.
This article investigates Alibaba's recommendation against joining more than three MySQL tables by designing experiments with synthetic data, measuring query performance on MySQL and Oracle, analyzing index impact, and providing full DDL and data‑generation scripts to explain the rule's practical limits.
The article investigates the Alibaba Java development manual's rule that joins involving more than three tables are prohibited, by designing massive MySQL and Oracle experiments, generating billions of rows, measuring query times, analyzing indexing effects, and concluding that the rule is MySQL‑specific and driven by large‑scale performance limits.
This article demonstrates how to use SQL, including dense_rank and date arithmetic, to detect users who have recorded a specific event for at least seven consecutive days within a month, providing step‑by‑step queries and a complete combined statement.
This article describes the evolution of a real‑time computing platform from SQL 1.0 built on Spark Structured Streaming to SQL 2.0 powered by Flink‑SQL, covering dynamic tables, continuous queries, dimension‑table joins, cache optimization, DDL extensions, platformization, operational challenges and future roadmap.
This article explains what foreign keys are, how they enforce referential integrity in relational databases, the different actions (RESTRICT, CASCADE, SET NULL), their performance overhead demonstrated by benchmarks, and practical guidelines for simulating or using foreign keys in high‑concurrency applications.
This article walks through common interview questions on Linux memory monitoring and cache clearing, then dives into SQL join concepts, types, performance pitfalls, and practical optimization techniques for MySQL, providing clear explanations and actionable commands.
ByteKV is a C++-based, strongly consistent, range-partitioned key‑value storage system built by ByteDance, featuring a multi‑Raft consensus layer, custom storage engines (RocksDB and BlockDB), automatic partition splitting/merging, load balancing, distributed transactions, and a SQL table layer for rich data models.
This guide outlines step‑by‑step methods to identify high load causes, troubleshoot replication interruptions, resolve instance hangs, and address common MySQL errors using commands like SHOW PROCESSLIST, EXPLAIN, iostat, and GTID management.
This article provides a comprehensive guide for Java backend interview preparation, covering essential framework experience, distributed technology basics, database performance tuning, core Java concepts, Linux log troubleshooting, and how to showcase practical project experience to stand out to interviewers.
This article explains how loop‑based database queries degrade performance and demonstrates three concrete optimizations—using IN clauses in MySQL, aggregation pipelines in MongoDB, and Redis pipelines—to replace repetitive queries with bulk operations, improving speed and maintainability.
This article explains the concepts of isolation levels, dirty reads, and phantom reads across various relational and NoSQL databases, describing how each system implements or lacks transaction isolation and the practical impact on data consistency and performance.
This guide walks you through downloading DataGrip, configuring its appearance, creating MySQL and MongoDB data sources, managing tables, applying powerful SQL editing shortcuts, and leveraging export and execution‑plan features to write higher‑quality database code.
This article explains how Spark converts SQL queries into RDD operations by creating a SparkSession, registering temporary views, executing SQL, and then detailing the underlying InternalRow, TreeNode, and Expression structures that power the Catalyst optimizer.
An engineer recounts a real‑world challenge of reusing existing SQL stored procedures to check a user's job, certification, and disclosure status, explores why inserting results into temporary tables can cause maintenance headaches, and concludes that moving business logic out of the database often yields cleaner, more maintainable code.
This article examines the purpose, benefits, and drawbacks of MySQL foreign keys, presents various usage scenarios with performance tests, discusses constraints, and offers guidance on when to apply or avoid foreign keys in database design.
This guide walks through Oracle SQL execution steps, optimizer selection, table access methods, shared SQL caching, join order, index usage, query rewriting techniques, hint usage, and dozens of practical tips that together dramatically improve database performance while avoiding common pitfalls.
This article explains the fundamentals of machine learning by illustrating how humans recognize cats, extracting features, training models with gradient descent, implementing linear regression, and providing practical JavaScript and SQL code examples for building a simple classifier.
The article examines the drawbacks of reusing stored procedures for business logic, illustrates maintenance challenges with example code, and argues that moving simple existence checks to the database while keeping complex logic in application code yields better readability, extensibility, and performance.
SQLite 3.32.0, the latest release of the world’s most widely used database engine, introduces approximate ANALYZE support, a bytecode virtual table, new SQL functions, expanded parameter limits, numerous CLI enhancements, and behavior changes that align the LIKE operator with PostgreSQL, offering developers richer functionality and improved compatibility.
This article investigates the Alibaba Java Development Manual's recommendation to avoid joining more than three tables by designing and executing large‑scale MySQL and Oracle experiments, analyzing query performance, index impact, and data‑generation scripts to determine the practical limits of multi‑table joins.
This article explains the MySQL group_replication_consistency parameter, describes its five possible values, demonstrates the three most common consistency modes (EVENTUAL, BEFORE, AFTER) with practical multi‑node examples, and discusses their advantages, drawbacks, and impact on query latency and data integrity.
This article walks through using Elasticsearch SQL to query data, covering installation, loading sample datasets, describing index schemas, executing simple and complex SQL queries with functions, converting SQL to Elasticsearch DSL, reindexing, alias management, and performance considerations, all illustrated with code snippets.
The article discusses a real‑world scenario where a developer tried to reuse existing stored procedures to determine the presence of Job, Certification, and Disclosure records, explores various T‑SQL techniques such as temporary tables and output parameters, and concludes that stored procedures are often unsuitable for simple boolean logic due to maintenance and reuse challenges.
This article provides a comprehensive guide to JDBC, covering its definition, core interfaces (Connection, Statement, ResultSet), the advantages of PreparedStatement, the need for connection pools, pagination techniques for Oracle and MySQL, and practical usage of DBUtils with full code examples.
This tutorial walks through integrating Dataway into a Spring Boot project, covering dependency setup, Dataway configuration, database table creation, data source integration, Hasor module registration, enabling Hasor, running the application, accessing the UI, and creating APIs using SQL or DataQL, all without writing custom code.
This article outlines a comprehensive real‑time data system built on Apache Flink, covering its application scenarios, layered architecture, data model stratification, construction methods, and a concrete Flink SQL example for calculating UV metrics from Kafka‑sourced page‑view data.
This article examines why NOT EXISTS can perform poorly in MySQL, demonstrates how rewriting the query with a LEFT JOIN dramatically reduces execution time, and explains the underlying indexing and profiling details that affect query efficiency.
This tutorial walks you through installing MySQL 5.7 on two CentOS 7.6 machines, configuring the master and slave my.cnf files, setting up replication users, starting the services, and verifying synchronization with detailed commands and troubleshooting tips.
This comprehensive MySQL FAQ covers its definition, implementation language, key features, differences from SQL, storage engines, data types, triggers, security tips, performance considerations, and practical queries such as retrieving the N‑th highest salary, providing a solid reference for developers and DBAs.
This article provides concise answers to common MySQL questions covering default ports, regular expression usage, index limits, query examples for retrieving rows, date functions, savepoints, ENUM types, storage engines, connection functions, ACLs, and other essential database concepts.
This tutorial explains several techniques for adding new columns to PySpark DataFrames—including native Spark functions, user‑defined functions, RDD transformations, Pandas UDFs, and SQL queries—while demonstrating data loading, schema handling, and code examples for each method.
This article explains the fundamentals of SQL parsing, describing the roles of lexical analysis, syntax analysis, and visitors, and demonstrates the process with a concrete SELECT statement example, while also mentioning popular parsing libraries such as Druid and ANTLR.
This article investigates why moving MySQL’s LIMIT clause outside a subquery can cause the ORDER BY sorting to be ignored in a LEFT JOIN, analyzes the optimizer’s behavior, and recommends placing both ORDER BY and LIMIT at the outermost query level for correct results.
This comprehensive guide walks you through essential Oracle database administration tasks—including starting and stopping the database, managing listeners, creating and altering tablespaces, handling users and privileges, controlling transactions, building indexes, defining views and materialized views, working with sequences, synonyms, and partitioned tables—complete with commands, examples, and best‑practice tips.
The new Java Development Manual (Taishan Edition) adds 34 rules and revises 90 descriptions, introduces a unified five‑character error‑code scheme, warns about stream‑toMap and ternary‑operator null‑pointer traps, highlights leap‑year and SQL alias bugs, and recommends readability tweaks such as blank lines after returns.
The author discovers a puzzling MySQL bug where moving the LIMIT clause outside a nested SELECT causes the final result set to be ordered incorrectly, explains how ORDER BY inside subqueries is ignored during LEFT JOIN processing, and recommends placing ORDER BY and LIMIT at the outermost query level.
An engineer’s attempt to migrate millions of orders using INSERT INTO SELECT caused massive table locking, payment failures, and system alerts, but by analyzing the locking behavior and adding an index on the timestamp column, the migration succeeded efficiently, illustrating the importance of proper indexing for large‑scale data moves.
This article provides a concise Laravel Log quick reference, detailing the seven RFC‑5424 log levels, usage examples, how to retrieve the Monolog instance, add listeners, and enable and fetch SQL query logs, followed by motivational reflections on perseverance and growth.
This guide walks through essential Oracle Database administration tasks, covering how to start and shut down the database and listener, create and manage tablespaces, users, and permissions, control transactions, build and maintain indexes, views, materialized views, sequences, synonyms, and partitioned tables, with command examples and best‑practice tips.
This article explains why a MyBatis query may return zero rows even though the identical SQL run directly in the database returns data, and shows how correcting line‑break placement in the XML mapper resolves the inconsistency.
This article introduces Spark SQL window functions, explains the difference between aggregation and window functions, and demonstrates how to use various ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE with practical Scala code examples and partitioning options.
This article explains the concept of MySQL Undo Log, traces its evolution from version 5.5 to 8.0, and provides step‑by‑step instructions with examples for creating, viewing, configuring, deleting, and moving undo tablespaces, highlighting the new parameters and defaults introduced in MySQL 8.0.
This step‑by‑step guide shows how to download the FIFA 18 player dataset from GitHub, create a MySQL database and table, read the CSV with Python’s csv.DictReader, generate INSERT statements, batch‑load the data efficiently, and query Argentine players, illustrating a complete end‑to‑end data‑import workflow.
SCADA (Supervisory Control and Data Acquisition) systems are essential industrial automation platforms that monitor and control processes across sectors such as energy, manufacturing, and water treatment, featuring sensor‑to‑PLC/RTU data flow, HMI interfaces, SQL integration, and modern web‑based capabilities exemplified by Ignition software.
This guide explains how Laravel’s query builder offers a secure, fluent interface for constructing and executing database queries, covering result retrieval, filtering, aggregation, joins, unions, inserts, updates, deletes, and advanced features like raw expressions, JSON handling, and pessimistic locking.
This guide explains how to configure Laravel's supported databases, set up SQLite, implement read/write connection splitting, use multiple connections, execute raw SQL queries, listen to query events, and manage transactions—including deadlock handling and manual control—complete with code examples.
This article explains InnoDB's logical storage hierarchy of tablespaces, segments, extents, and pages, then details MySQL's partitioning concepts, types, creation syntax, and performance considerations, providing practical examples and guidance for effective database design.
This article explains how DBLE, a PaaS middleware, implements MySQL view capabilities by first describing MySQL view concepts and the MERGE and TEMPTABLE algorithms, then detailing DBLE's push‑down and non‑push‑down approaches, the criteria for each, and providing illustrative diagrams.
This guide introduces four open‑source MySQL tuning utilities—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run each tool, and shows how to interpret their AWR‑style reports for concrete performance and security improvements.
This tutorial walks through creating the five microservice databases for the PassJava platform using PowerDesigner, detailing each service’s schema, showing screenshots, and providing the GitHub repository where the SQL files can be accessed.
This article explains how to create a JetBrains IntelliJ plugin that parses MySQL DDL statements, performs lexical analysis to extract table and column information, and automatically generates corresponding Python ORM model classes, illustrating the underlying state‑machine lexer, recursive parsing logic, and sample code.
This article explains why B+ trees are the dominant data structure for MySQL indexes, compares them with hash tables, linked lists, and skip lists, and details page splits, merges, and how index values map to row records, helping readers master high‑frequency interview questions.
This article demonstrates how to create Hive tables for student, course, teacher, and score data, generate CSV files, load them into Hive, and provides a comprehensive set of Hive SQL queries covering data retrieval, aggregation, ranking, and statistical analysis for educational datasets.
This article presents thirty actionable SQL performance recommendations—including selecting specific columns, using LIMIT, avoiding OR and functions in WHERE clauses, proper indexing, batch inserts, and query planning—each illustrated with clear examples and explanations to help developers write faster, more efficient queries.
This article provides a comprehensive overview of MySQL data types—including numeric, floating‑point, decimal, string, date/time, binary, bit, enum, and set types—explaining their storage characteristics, appropriate use cases, and performance implications through practical examples and SQL demonstrations.
This article examines various MySQL pagination methods—including simple LIMIT, offset impact, sub‑query, ID‑range, and temporary‑table strategies—provides detailed performance measurements on a table with over five million rows, and offers practical recommendations for speeding up large‑scale data retrieval.
MySQL 8.0.19 adds two novel DML statements—TABLE for convenient full‑table scans and sub‑query replacements, and VALUES for generating in‑memory row sets—each with clear syntax, practical examples, and execution‑plan insights that help developers quickly adopt these features.
This article explains how Linux memory statistics (free, top) report buffer/cache usage, how to clear it with sync and drop_caches, and then dives into SQL join concepts, types, optimization techniques, and MySQL join algorithms such as nested loop and block nested loop, highlighting their impact on performance.
A detailed reference covering MySQL service management, connection commands, database and table operations, data types, indexes, joins, subqueries, transactions, backup, user privileges, and common SQL functions, complete with practical code examples.
This article explains the concept of multi‑tenancy, compares three data‑isolation strategies, and demonstrates how to configure MyBatis‑Plus, Spring Boot, and H2 to achieve transparent tenant filtering and automatic tenant‑ID handling using a custom SQL parser and context component.
This article introduces MyBatis, a Java persistence framework that separates SQL from code, outlines its key features, explains its layered architecture, and details the core components such as SqlSessionFactory, SqlSession, MapperProxy, Executor, StatementHandler, ParameterHandler, and ResultSetHandler with code examples.
This article explains the distinctions between UNION and UNION ALL, TRUNCATE and DELETE, TIMESTAMP and DATETIME, introduces composite and covering indexes, describes InnoDB versus MyISAM storage, the left‑most prefix rule, monotonic primary keys, int display length, SHOW INDEX fields, handling LIKE patterns, and efficient pagination techniques.
This tutorial explains MySQL 8.0 role concepts, demonstrates how to create roles, assign them to multiple users, activate default roles, configure role parameters, and revoke roles, providing step‑by‑step examples with complete MySQL commands and explanations for effective privilege management.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
This article explains core MySQL concepts, comparing UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP and DATETIME, composite and covering indexes, the left‑most prefix rule, clustered vs non‑clustered indexes, prefix indexes, InnoDB vs MyISAM storage, monotonic primary keys, column length display, SHOW INDEX fields, LIKE pattern pitfalls, and efficient pagination techniques.
This tutorial explains how to install MariaDB's Spider storage engine, create single-table remote links, configure users and servers, and leverage Spider for sharding and multi‑server table aggregation, providing practical code examples and performance considerations for distributed database setups.
An analysis of Indeed's 2014‑2019 US job data reveals that SQL, Java, and Python dominate employer demand, while AWS shows explosive growth, highlighting shifting trends in programming languages, cloud services, and data‑science‑related roles across the tech job market.
This guide explains essential MySQL concepts such as the distinction between UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP versus DATETIME, the purpose and benefits of composite indexes, index storage engines, prefix indexes, covering indexes, the left‑most prefix rule, and efficient pagination techniques.
This article shares the experience of deploying TiDB on the 360 Cloud Platform, covering background challenges with massive tables, TiDB's online DDL and high‑performance features, cluster architecture, DM migration workflow, common operational issues, and detailed tuning parameters to improve latency, QPS, and resource utilization.
The article documents a puzzling MySQL 5.7.25 bug where RAND() in a derived table is unexpectedly re‑evaluated in the outer query, explains the step‑by‑step reproduction, analyses the cause, and provides official work‑arounds for both MySQL 5.7 and 8.0.
This article walks through the entire Oracle‑to‑GaussDB migration process with Huawei DataSync, covering environment setup, tool constraints, NFS mounting, JDBC driver preparation, configuration file editing, password encryption, export and import execution, and post‑migration validation.
This article explains the nature of scalar subqueries, identifies three common performance pitfalls, and provides practical rewrite techniques—including LEFT/INNER JOIN, MERGE, and WITH‑MATERIALIZE—illustrated with three real‑world Oracle SQL cases to dramatically reduce execution time.
This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.
This article explains the MySQL COUNT function, compares COUNT(*), COUNT(1) and COUNT(column) usages, discusses common interview questions, and details the specific optimizations MyISAM and InnoDB apply to COUNT(*) queries, concluding with best‑practice recommendations.
This article explains the root causes of MySQL character‑set garbling, illustrates the encoding/decoding steps during data insertion and retrieval, and provides practical methods—including dump‑reload and binary conversion—to prevent and repair corrupted data.
This article presents the evolution, architecture, and operational metrics of Beike Zhaofang's Hermes real‑time computing platform built on Apache Flink, detailing its business scale, SQL editors, task growth, monitoring, use cases, and future development directions.
This article explains MySQL's row size restrictions at both the Server and InnoDB layers, detailing the calculation methods, relevant source code paths, error messages, and practical guidelines for ensuring total record length stays within the 65535‑byte server limit and the 8126‑byte InnoDB limit.
This article explains the fundamentals of MySQL InnoDB transaction isolation levels, describes the four standard isolation grades, shows how InnoDB implements them in source code, and provides concrete case studies with SQL scripts to illustrate locking behavior and MVCC effects.
This article explains how MySQL's InnoDB engine applies locks for DELETE and SELECT statements, covering MVCC, snapshot and current reads, clustered indexes, left‑most prefix rules, two‑phase locking, isolation levels, gap and next‑key locks, and analyzes nine different index‑and‑isolation scenarios with detailed conclusions.
This article reviews the historical evolution, market size, and technical advantages of relational databases, explains the shift from centralized to distributed architectures, and highlights OceanBase's recent breakthroughs and community events as a modern distributed relational solution.
This article presents a concise collection of twelve actionable SQL tuning techniques—including using proper comparison operators, LIMIT 1, appropriate data types, breaking large statements, UNION ALL, consistent query formatting, avoiding SELECT *, indexing WHERE/JOIN/ORDER BY columns, pagination with LIMIT, and leveraging EXPLAIN—to help developers improve database performance.
This guide walks you through the essential daily maintenance tasks for GaussDB T clusters, covering ETCD startup, cluster health checks, host resource monitoring, tablespace usage, abnormal wait events, log inspection, and common error troubleshooting with concrete commands and SQL examples.
This article presents the design of Flink's batch processing architecture, its integration with Hive through a unified Catalog API, details the enhancements in Flink 1.10, outlines future work, and reports a performance test showing roughly seven‑fold speedup over Hive on MapReduce.
This article introduces Flyway as a database migration tool, explains its working principle, directory and naming conventions, supported databases, and provides detailed step‑by‑step instructions, best‑practice guidelines, and troubleshooting tips for safely managing MySQL schema changes in production environments.
This article explains the InnoDB logical storage architecture—including segments, extents, and pages—then introduces MySQL table partitioning concepts, details the main partition types (RANGE, LIST, HASH, KEY) with SQL examples, and discusses their performance implications for OLTP and OLAP workloads.
Apache Flink 1.10 introduces major performance and stability improvements, unified memory configuration, native Kubernetes session mode, enhanced Table API/SQL with production‑ready Hive integration, expanded Python UDF support, and a host of important bug fixes and connector updates, marking the largest community‑driven release to date.
This article explains how to identify and remove various kinds of duplicate data in MySQL—including fully duplicated rows, records with duplicate non‑key columns, and unwanted whitespace inside fields—by using SQL statements, table cloning, OS utilities, and regular‑expression updates, with performance measurements for each method.
This article explains how to integrate Apache Phoenix with CDH‑based HBase, covering Phoenix overview, version selection, parcel installation, HBase configuration, command‑line usage, mapping existing tables, creating schemas and views, building secondary indexes, and comparing different index types for performance optimization.
The article explains how Citus enables sharding and MapReduce‑style parallel query execution in PostgreSQL, showing performance gains, example bucket algorithms, and how standard SQL can replace custom MapReduce code for large‑scale data analytics.
The article provides a series of advanced MyBatis-Plus query examples, including table creation, data initialization, and four detailed cases that demonstrate the use of like, nested, in, and limit clauses with QueryWrapper, accompanied by SQL statements, Java test code, and execution screenshots.
This article walks through building a MySQL user table, defining the corresponding Java entity and MyBatis‑Plus mapper, and demonstrates basic query operations such as selectList, selectById, selectBatchIds, and selectByMap with full code examples and the generated SQL statements.
This article demonstrates how to use MyBatis-Plus for complex queries—including date filtering, sub‑queries, INNER JOIN, dynamic SQL with the apply method, and the inSql helper—while highlighting potential SQL‑injection pitfalls and providing complete runnable code examples.
The article explains PostgreSQL materialized views, their evolution, how they differ from regular views, demonstrates creating and refreshing them, and compares them with incremental rollup tables using upsert to help decide which method best fits performance and scalability needs.
This tutorial demonstrates how to create a User table, define the corresponding entity and mapper classes, and perform basic MyBatis-Plus queries such as selectList, selectById, selectBatchIds, and selectByMap, including the required SQL scripts and Java code examples.
This tutorial demonstrates several advanced MyBatis-Plus query scenarios—including combined AND conditions, nested expressions, IN clauses, and limiting results—by providing complete SQL setup, Java QueryWrapper code samples, execution logs, and result screenshots for each case.
