This guide explains what MySQL temporary tables are, how they exist only for the current connection, provides step‑by‑step SQL examples for creating, inserting, querying, and dropping a temporary table, and discusses practical scenarios such as high‑concurrency writes.
This article presents a systematic performance test of Hibernate and MyBatis covering bulk inserts, single‑table queries, and various association queries on MySQL, analyzes the impact of lazy versus eager loading, caching, and data volume, and provides practical recommendations for choosing the appropriate ORM framework.
This article investigates the practical limits of joining more than three tables in MySQL by designing experiments with up to 1.5 billion rows, comparing indexed and non‑indexed queries, and contrasting the results with Oracle's performance, while providing full SQL scripts for data generation and analysis.
This document explains the differences between OLTP and OLAP workloads, introduces the CSTORE columnar storage engine architecture, its core technologies, performance advantages, typical use cases, benchmark results, and future development plans for MySQL 8.0.
Tencent Cloud MySQL 8.0 builds on official MySQL improvements with eight enterprise‑grade enhancements—TDE encryption, kernel‑level audit, multi‑queue thread pool, strong consistency, AEP SSD support, lightweight AP mode, hotspot updates, and SQL rate‑limiting—delivering high‑performance, secure, and scalable solutions for e‑commerce, gaming, finance, and new‑retail workloads while paving the way for cloud‑native, AI‑driven database evolution.
The article demonstrates how moving the LIMIT‑OFFSET condition into a subquery that selects only primary‑key IDs and then joining back to the main table can cut execution time from over 16 seconds to a few hundred milliseconds by avoiding massive index and data‑page scans.
This article explains how MySQL parses SQL statements using Yacc, detailing the grammar structure, definition and rule sections, relevant source files, parse‑tree node declarations, common error messages, and the call stacks involved in both parsing and pre‑parsing phases.
This article introduces MySQL 8.0.20 Group Replication, covering single‑master and multi‑master modes, monitoring, failover procedures, abnormal recovery, flow control, performance testing, encountered issues, and limitations, and provides a downloadable PDF with detailed documentation hosted on Baidu Cloud.
This article examines how MySQL Group Replication's message cache fills under load, the impact of the group_replication_message_cache_size and group_replication_member_expel_timeout parameters, and provides experimental observations and practical recommendations for balancing reliability, memory usage, and data consistency.
The article explains why using NULL columns in MySQL can lead to extra storage, index complications, query errors, and performance degradation, and provides concrete examples and best‑practice recommendations to prefer NOT NULL constraints whenever possible.
A production MySQL query that should have returned instantly took 44 seconds because the optimizer selected the primary key index instead of a suitable composite index, and the article explains the root cause, the optimizer's decision process, and multiple practical solutions to prevent such slow‑query incidents.
This tutorial explains how to obtain geographic coordinates, store them as MySQL POLYGON types following OGC standards, insert campus area data, and query with spatial functions and SPATIAL indexes to determine whether a user is within a defined check‑in region.
The article details the architecture of 58.com’s SSP advertising platform, identifies three key reliability challenges—data consistency, interface regression, and storage synchronization—and presents a three‑layer testing quality system comprising web‑layer validation, service‑layer automated testing, and data‑layer monitoring with concrete tools and future improvement plans.
This article explains the concept of MySQL compressed tables, highlights strong compression products, discusses why and when to use them, outlines their advantages and drawbacks, details supported compression algorithms, and provides step‑by‑step examples for both MyISAM and InnoDB engines along with their impact on B‑tree pages and the InnoDB buffer pool.
This article explains how to recover MySQL data by preparing backups with binlog enabled, performing full and point‑in‑time restores using mysqldump, xtrabackup, or binlog, restoring individual tables, skipping unwanted SQL statements, and using flashback tools such as binlog2sql and MyFlash.
This article explains the different lock mechanisms used by MySQL storage engines—including MyISAM table‑level locks and InnoDB row‑level locks—covers lock compatibility, scheduling, transaction properties, optimistic locking, and practical strategies to avoid lock conflicts and deadlocks.
This article explains why SQL optimization is a cost‑effective way to boost system performance, outlines fundamental principles, common pitfalls such as leading wildcards, IN/OR/NULL misuse, and provides practical tips on indexing, execution order, hints, DML batching, pagination, and table design for MySQL databases.
The article repeats a performance test on a 140 GB MySQL 8 database (innodb_buffer_pool_size=140G) using various innodb_buffer_pool_instances values, showing that increasing the instances improves throughput up to 64 instances, after which no further gains are observed.
This article explains the SQL standard limits on SELECT columns after GROUP BY, demonstrates how MySQL's ONLY_FULL_GROUP_BY mode enforces those limits, explores various SQL modes, and clarifies why aggregated queries cannot reference non‑grouped columns directly.
This article explains how MySQL's AUTO_INCREMENT generates unique identifiers, the constraints on its use, how deletions affect the sequence, the role of LAST_INSERT_ID in concurrent environments, and best practices for managing auto‑increment columns in InnoDB tables.
Using sysbench oltp_read_write benchmarks on a 100 GB database with a 25 GB buffer pool, this article evaluates the impact of varying innodb_buffer_pool_instances (1, 2, 4, 8, 16, 32, 64) on throughput and variability, concluding that 64 instances yields the highest performance.
This article examines common requirements for like, comment, and collection features in apps, compares MySQL partition‑and‑cache designs with Redis storage and caching solutions, provides schema examples, query patterns, and discusses trade‑offs, scalability, and data consistency considerations.
This article explains why using NULL columns in MySQL can hurt performance and increase storage, debunks common myths with official documentation, and provides practical reasons and code examples for preferring NOT NULL constraints in table design.
This article explores practical database designs for app features like likes, comments, and collections, comparing MySQL partition‑plus‑cache approaches with Redis storage and caching solutions, detailing schema definitions, query examples, Redis commands for various scenarios, and discussing scalability and consistency trade‑offs.
The article presents a detailed benchmark of ScaleFlux CSD 2000 storage against Intel SSDs using Percona Server for MySQL 8.0.19 and sysbench, showing how built‑in compression and atomic‑write features give ScaleFlux a clear advantage in large, compressible data sets and high‑concurrency scenarios.
This article presents a comprehensive guide for transferring massive MySQL datasets to HBase, covering environment setup on Ubuntu, three synchronization methods—MySQL LOAD DATA, a Kafka‑Thrift pipeline using Maxwell, and real‑time Flink processing—along with performance comparisons and practical tips for Hadoop, HBase, Kafka, Zookeeper, Phoenix, and related tools.
This guide introduces several handy Linux command-line tricks, including the 'z' shortcut for rapid directory navigation, a feature‑rich Vim configuration, customizing MySQL prompts, reliable methods to display system version details, and concise commands for checking disk usage, helping users streamline their workflow.
When a business platform’s settings page took 36 seconds to load, we used Chrome’s Network timing, Python profiling, and MySQL query analysis to identify backend bottlenecks, then applied redesign, thread‑pooling, batch queries, and ORM optimizations, reducing response time from 37.6 s to 1.47 s.
This article explains why using indexes does not guarantee fast queries in MySQL, analyzes full index scans, index selectivity, the cost of row look‑ups (back‑to‑table), and presents optimization techniques such as index condition pushdown and virtual columns to improve performance.
This comprehensive guide details three practical methods for syncing massive MySQL datasets to HBase—including Sqoop, Kafka‑Thrift, and Flink pipelines—covering environment setup, configuration, code examples, performance comparisons, and optimization tips for large‑scale data ingestion and querying.
This article explains how to install, configure, and test MySQL 8.x query‑rewriter and DDL‑rewriter plugins, demonstrating environment setup, sample SQL scripts, rule creation, and the impact on query performance and CREATE TABLE statements.
This article analyzes MySQL data‑update loss caused by writes on master‑slave pairs, explains BEFORE and AFTER IMAGE handling in row‑based binlogs, examines the binlog_row_image and slave_rows_search_algorithms parameters, and details the implementation of hash, index, and table scans during slave replay.
This article explains MySQL 8.0's new GROUPING() function, demonstrates how it works with GROUP BY ... WITH ROLLUP to differentiate regular NULL values from roll‑up summary rows, and provides multiple SQL examples and usage tips.
This article explains database concurrency control mechanisms, detailing transaction fundamentals, isolation levels, associated anomalies, and how DB2, MySQL, and Oracle implement locks and isolation to ensure data consistency while balancing performance.
This article explains the principles of MySQL performance optimization, distinguishes active and passive approaches, and provides concrete solutions for slow single queries, partially slow queries, and overall slow queries through proper indexing, data partitioning, slow‑query‑log configuration, and read‑write splitting.
This article explains why MySQL tables with massive row counts suffer performance degradation and provides practical guidance on single‑table optimization, field and index design, query tuning, engine selection, system parameters, hardware upgrades, read‑write splitting, caching layers, partitioning, vertical and horizontal sharding, as well as client‑side and proxy‑side sharding solutions.
This article explains MySQL's implicit and explicit locking mechanisms—including table, row, and intention locks—covers lock modes, MVCC, transaction isolation levels, optimistic and pessimistic locking, gap locks, and deadlock causes and solutions, providing practical SQL examples for each concept.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains what MySQL binlog is, how to enable row‑based logging, and provides step‑by‑step Linux commands using mysqlbinlog and grep to extract historical SQL statements such as DELETE operations for troubleshooting and debugging purposes.
This comprehensive guide covers MySQL index creation syntax, various index types—including primary, unique, full‑text, and composite—explains their advantages and drawbacks, details underlying B‑Tree and B+Tree structures, and provides practical strategies and optimization tips for effective index usage.
This article explains MySQL's B‑Tree index structure, describes various index types such as primary, unique, normal, full‑text, and composite indexes, and provides practical techniques for creating efficient indexes, choosing column order, using prefix and covering indexes, and optimizing query sorting.
This article explains how to locate a MySQL statement that holds a lock but does not appear in SHOW PROCESSLIST, by enabling performance_schema, examining INNODB_LOCK_WAITS, INNODB_LOCKS, INNODB_TRX, and using performance_schema.threads and events_statements_current to retrieve the blocking SQL.
This article explains the three types of MySQL InnoDB tablespaces—system, single, and general—detailing their structures, configuration options, advantages, disadvantages, and practical procedures for creation, migration, optimization, and removal, with concrete command‑line examples.
Learn step‑by‑step how to set up a binlog‑based MySQL master‑slave replication using Docker‑Compose and Dockerfiles, covering directory structure, configuration files, container creation, verification, and troubleshooting, so you can quickly deploy a reliable MySQL replication environment.
This article details a comprehensive investigation of MySQL out‑of‑memory (OOM) alerts on a Tencent Cloud CDB instance during a high‑traffic marketing event, covering background, observed symptoms, step‑by‑step debugging, analysis of server and engine memory usage, relevant MySQL bugs, fetch_cache behavior, and the final configuration recommendations to prevent OOM.
This article details a step-by-step optimization of a Java-based Excel import process, covering migration from POI to EasyExcel, caching database lookups, using batch inserts with VALUES, and leveraging parallel streams to dramatically reduce import time for massive datasets.
This article explains how Transparent Data Encryption (TDE) protects sensitive data in databases by encrypting pages with keys stored in a secure vault, outlines its advantages and drawbacks, and compares page-level encryption with column-level approaches.
This article explains the MySQL binary log (binlog), its structure and contents, the three main uses—point‑in‑time recovery, master‑slave replication, and audit logging—followed by a detailed walkthrough of the replication process, an overview of Alibaba's Canal mechanism, and extensive code examples illustrating binlog parsing and event handling.
This article analyzes a real‑world MySQL incident where using INSERT INTO SELECT to migrate billions of rows caused table‑wide locking and payment failures, explains the underlying locking behavior, and presents a solution using proper indexing and FORCE INDEX to avoid full‑table scans.
The article evaluates MySQL Shell 8.0.21's new logical backup and restore method—featuring ZSTD compression, chunked parallel export, and parallel LOAD DATA import—against mysqldump, mysqlpump, and mydumper, presenting benchmark results, analysis, and practical command examples.
This document outlines comprehensive MySQL database design standards and SQL writing guidelines, covering naming conventions, character sets, table and column specifications, index design, partitioning, data types, transaction handling, and prohibited statements to ensure stable, performant, and maintainable production databases.
This article breaks down MySQL’s architecture into its five key parts—connector, parser, optimizer, executor, and storage engine—explaining each component’s role from establishing connections and handling permissions to query parsing, execution planning, and data storage, with example commands and code snippets.
The article explains that MySQL’s legacy "utf8" charset only supports three‑byte sequences, causing errors with true four‑byte Unicode characters, and shows how the later "utf8mb4" charset provides proper UTF‑8 support along with historical context and migration guidance.
This article walks through a practical MySQL troubleshooting experiment that shows how to identify a CPU‑intensive query using the processlist, locate the offending thread with top‑H, interpret performance_schema IDs, and safely terminate the problematic SQL statement.
This article provides a comprehensive guide to gh-ost, the GitHub‑released online MySQL schema migration tool, covering its design principles, three migration modes, download and RPM installation, common command‑line options, interactive socket commands, and a step‑by‑step example of altering a column type while monitoring progress and performing a safe cut‑over.
Understanding CPU usage in MySQL involves recognizing that user processes, IO waits, and system interrupts dominate consumption, and by reducing IO, optimizing queries, indexing, and minimizing costly calculations, you can lower CPU load, improve throughput, and avoid performance bottlenecks.
This article provides a comprehensive guide to using Sqoop for importing data from relational databases into HDFS, Hive, and HBase, as well as exporting data back to databases, covering command syntax, options, and practical examples for big‑data workflows.
This article explains MySQL index structures—including B‑Tree, B+Tree, and hash indexes—their underlying principles, performance characteristics, and practical usage guidelines such as left‑most prefix, full‑text indexing, and common pitfalls, providing a comprehensive guide for database developers.
This article explains MySQL Binlog’s role in event‑driven order processing, analyzes hidden pitfalls such as premature Binlog reads and two‑phase commit issues, and offers practical solutions like retry strategies and direct Binlog consumption to ensure data consistency.
This guide explains how to restore MySQL databases when the server cannot start by generating table definitions with mysqlfrm, discarding damaged InnoDB tablespaces, copying old .ibd files, and importing them back, followed by verification using mysqlcheck.
This article explores the requirements and database design patterns for implementing likes, comments, and favorites in apps, comparing traditional MySQL schemas with Redis‑based storage and caching solutions, and discusses their trade‑offs, scalability, and data consistency considerations.
This article explains how to use MySQL's EXPLAIN statement to view execution plans, describes the meaning of each of the twelve output fields, provides practical examples with sample tables and queries, and offers optimization tips such as index creation and the left‑most prefix rule.
The article evaluates MySQL 8.0.21's new ability to disable the InnoDB redo log, comparing load‑data performance with redo log enabled and disabled, and discusses practical implications, limitations, and safety considerations for bulk data import scenarios.
This article presents practical MySQL performance tricks, covering bulk data insertion with DISABLE/ENABLE KEYS, loading data in primary‑key order, multi‑value INSERTs, INSERT DELAYED, separating index and data files, GROUP BY sorting elimination, index‑driven ORDER BY, OR‑condition indexing, replacing sub‑queries with JOINs, and using SQL hints such as USE, IGNORE, and FORCE INDEX.
The article provides a comprehensive analysis of major relational databases—Oracle, DB2, SAP HANA, MySQL, PostgreSQL—and emerging NoSQL solutions, examining their underlying technologies, recent developments, market adoption, and the overall industry landscape shaping the future of data storage.
This article explains why and how to create prefix indexes for string fields in MySQL, compares full‑column and prefix indexes with concrete examples, shows how to pick an optimal prefix length using cardinality statistics, and discusses alternative solutions such as reverse storage and hash fields.
This article introduces several MySQL binlog-based change data capture solutions—including Canal, Maxwell, Databus, and Alibaba Cloud's Data Transmission Service—explaining their principles, architecture, features, and usage considerations for incremental data subscription and processing.
This article presents a thorough overview of MySQL optimization, covering the query execution process, philosophical principles, risk assessment, required participants, optimization dimensions, commonly used diagnostic tools, emergency and regular tuning procedures, hardware and system level adjustments, as well as detailed configuration parameters for both the server and the database engine.
This article explains the differences between normal and unique indexes in MySQL, examines how they affect query and update performance, details InnoDB's change‑buffer mechanism, and provides practical guidance on when to prefer ordinary indexes for better write efficiency.
This article explains InnoDB's 16KB page structure, row formats, record metadata, primary key strategies, and how B+Tree indexes—including clustered, secondary, and covering indexes—organize and retrieve data, with practical SQL examples and performance considerations.
This article demonstrates how to enable MySQL's performance_schema, generate test data, run an ALTER TABLE operation, and continuously query a custom SQL statement to estimate the operation's progress and remaining time, providing practical insight into DDL monitoring.
This article explains how to achieve high‑performance bulk inserts in .NET Core applications by converting entities to CSV or DataTable formats and using MySQL's LOAD DATA and SQL Server's SqlBulkCopy, providing reusable extension methods, interface abstractions, and performance benchmarks.
This article explains how a friend loaded over 2 billion rows into MySQL using XeLabs TokuDB, detailing configuration, bulk‑load commands, benchmark results of roughly 574 k rows per second, and comparisons with InnoDB performance on a cloud server.
This article details a real‑world test that loads 200 million rows into a MySQL TokuDB table in under six minutes, achieving roughly 570 k writes per second, and compares the results with InnoDB while providing configuration tips and bulk‑loader considerations.
This guide walks through downloading, installing, and configuring MySQL Enterprise Monitor, explains the agent and agentless client architectures, and demonstrates how to use its overview, event, metric, and query‑analysis features for comprehensive MySQL performance monitoring.
This article explains MySQL's two‑layer architecture, the step‑by‑step execution flow of an SQL statement—including connection, query cache, parser, optimizer, executor, and storage engine—and the roles of redo log and binlog in ensuring data consistency.
This article provides an in‑depth overview of MySQL, covering storage engine concepts, selecting appropriate data types, index design and usage, view creation, stored procedure syntax, variable handling, and trigger implementation, with practical examples and SQL code snippets to illustrate each topic.
This article explains how to load over 20 billion rows into MySQL using the XeLabs‑compiled TokuDB engine, detailing configuration tweaks, table schema, bulk‑loader commands, benchmark results that reach 574 k rows per second, and practical tips for handling auto‑increment keys.
This article walks through common Linux memory inspection commands, explains the meaning of the buff/cache field, and then dives into MySQL join types, join algorithms, buffer settings, and performance‑tuning tips, all presented as a simulated interview with promotional side notes.
This article explains why MySQL data can become garbled due to mismatched character sets during insertion or retrieval, explores various scenarios such as client‑side encoding errors, mixed encodings within tables, and LATIN1 storage, and provides detailed solutions including setting proper client encodings, using strict SQL_MODE, and exporting/importing data to unify character sets.
This comprehensive guide walks you through setting up a pseudo‑distributed Hadoop environment, loading massive MySQL data with LOAD DATA, Python scripts, and multithreading, and then synchronizing the data to HBase using three approaches—Sqoop, a Kafka‑Thrift pipeline, and a real‑time Kafka‑Flink pipeline—while also comparing query performance of HBase and Phoenix.
This article explains the three most important MySQL log types—redo log, undo log, and binary log—detailing their purposes, contents, creation and release timing, related configuration parameters, physical files, and how they interact with transaction processing and replication.
This article explains how MySQL implements row‑level locking, the two‑phase locking protocol, common deadlock scenarios with online ticket sales, and practical strategies—including lock ordering, deadlock detection settings and sharding tricks—to improve concurrency and reduce CPU overhead.
The article explains that MySQL’s legacy “utf8” character set only supports three‑byte sequences and therefore cannot store true UTF‑8 characters, describes the historical reasons behind this limitation, and advises all MySQL/MariaDB users to migrate to the proper utf8mb4 charset.
This article demonstrates how to audit MySQL user actions by configuring init_connect, creating an audit log table, enabling binlog, and analyzing binlog entries to identify the user and IP responsible for accidental table deletions.
This article examines why full‑table scans on massive MySQL tables are slow, explains standard LIMIT‑based pagination, and presents several optimization strategies—including sub‑queries, ID‑range filtering, and temporary tables—backed by concrete performance measurements.
This tutorial walks through the test environment and demonstrates how InnoDB Cluster's built‑in functions—such as get_cluster, describe, list_routers, set_option, options, set_instance_option, set_primary_instance, switch_to_multi_primary_mode, switch_to_single_primary_mode, status, and dissolve—simplify the deployment, monitoring, and administration of MySQL group replication, complete with code snippets and visual results.
This article explains MySQL master‑slave replication, covering its concepts, asynchronous replication mechanisms, various synchronization strategies, step‑by‑step installation and configuration on CentOS 7, and includes common interview questions with answers; it also provides practical commands, troubleshooting tips, and performance considerations for read‑write separation in production environments.
This article explains common MySQL performance problems such as missing indexes and lack of pagination, and provides practical guidance on data size limits, concurrency settings, query time targets, table design, index types, and a variety of SQL optimizations with concrete code examples.
This guide details recommended MySQL versions for Zabbix, proper grant statements for read/write and read‑only users, essential MySQL configuration tweaks, SQL techniques for item and function updates, read‑write splitting, upgrade procedures, backup strategies, and comprehensive partition maintenance scripts to improve performance and manage data growth.
This article explains how MySQL implements transaction ACID properties by using redo logs for durability, undo logs for atomicity, lock mechanisms and MVCC for isolation, and discusses isolation levels, consistency, and practical examples with SQL code and diagrams.
This article explains various MySQL pagination methods—including simple LIMIT queries, sub‑query optimizations, ID‑range filtering, and temporary‑table approaches—provides detailed performance measurements on a large order_history table, and concludes with practical recommendations while also promoting a comprehensive interview‑question PDF collection.
The article describes a bug where a user's VIP expiration date cannot exceed a specific day in 2038 because the MySQL INT column storing the timestamp reaches its maximum signed value, explaining the cause, impact, and the pragmatic decision to ignore the issue.
After upgrading a MySQL 5.6 master to 5.7, inserts on several InnoDB tables began failing with duplicate‑key errors because the auto_increment value became inconsistent, and the article walks through kernel‑level investigation, reproduction steps, bug analysis, and practical fixes.
This article investigates whether MySQL re‑executes UPDATE statements that do not change any data, comparing the behavior under binlog_format = ROW and binlog_format = STATEMENT with a full row image setting, and presents test results from two sessions.
This article provides a thorough, step‑by‑step guide to MySQL, covering how to install and start the server, create databases and tables, use DDL/DML/DQL statements, understand numeric, date/time and string data types, apply arithmetic, comparison, logical and bitwise operators, and leverage common built‑in functions for efficient data manipulation.
This article demonstrates how to use MySQL's DBUG debugging tool to observe the creation of a table, track changes in internal statistics tables such as innodb_index_stats, and reveal the underlying operations and stored procedures that update these tables.
This article explains how to modify systemd unit files or use systemctl commands to set resource isolation parameters such as CPUQuota and MemoryLimit, demonstrates the changes with a MySQL CPU‑usage experiment, and describes the underlying cgroup and systemd mechanisms on Linux 7.
This article provides a thorough overview of MySQL performance tuning, covering proper table design according to the three normal forms, various index types and their creation, partitioning strategies, read/write separation, storage engine selection, and practical SQL tips for improving query efficiency.
This article explains MySQL transaction isolation concepts, the four standard isolation levels, their effects on dirty, non‑repeatable, and phantom reads, practical configuration steps, when to use repeatable read, how InnoDB implements isolation, and tips for avoiding long transactions and planning backups.
