Tag: mysql
62 articles
MySQL Database Sharding: Vertical vs Horizontal Partitioning
Database sharding explained: vertical partitioning, horizontal partitioning, vertical sharding, vertical table splitting, horizontal table splitting. This article analyzes principles and practical ...
MySQL MHA Master-Slave Failover: High Availability Solution
MHA high availability solution: Manager, Node, fault handling, failover. This article analyzes principles and practical applications to help readers master key technical details.
MySQL Read-Write Separation: Principles and Implementation
Read-write separation principles, implementation methods, master-slave lag solutions, database middleware. This article analyzes principles and practical applications to help readers master key tec...
MySQL Transaction Control: Lost Update, Locking and MVCC
Concurrent transaction problems: lost update, dirty read, non-repeatable read, phantom read, lock mechanism: shared lock, exclusive lock, MVCC. This article provides in-depth analysis of principles...
MySQL Transaction and Lock Mechanism: ACID Properties
ACID properties: atomicity, consistency, isolation, durability, WAL mechanism. This article provides in-depth analysis of principles and practical applications.
MySQL Slow Query Analysis and Optimization Guide
Slow query log, mysqldumpslow, EXPLAIN analysis, common optimization solutions. This article provides in-depth analysis of principles and practical applications.
MySQL Index and Sorting: Filesort and Index Sort
filesort sorting, index sorting, two-pass sorting, single-pass sorting, clustered index and secondary index sorting. This article provides in-depth analysis of principles and practical applications.
MySQL Index Optimization: Table Lookup, Covering Index, L...
Table lookup queries, covering index, leftmost prefix principle, LIKE queries, NULL value handling. This article provides in-depth analysis of principles and practical applications.
MySQL EXPLAIN: Index Analysis and Query Optimization
EXPLAIN command: select_type, type, possible_keys, key, rows, key_len, Extra. This article provides in-depth analysis of principles and practical applications.
MySQL Clustered vs Secondary Index: Structure and Perform...
Clustered index (secondary index, primary key index), secondary index, table lookup query, covering index. This article provides in-depth analysis of principles and practical applications.
MySQL Index Principles: B+Tree, Hash, Binary Search
Index principles: B+Tree, Hash index, binary search, InnoDB adaptive hash index. This article provides in-depth analysis of principles and practical applications.
MySQL Index Types: BTree, Hash, FULLTEXT, RTree Explained
Index types: BTree, Hash, FULLTEXT, RTree, regular index, unique index, primary key index, composite index, clustered index. This article provides in-depth analysis of principles and practical appl...
MySQL Binlog: Recording Modes, Replication, Data Recovery
Binlog three recording modes: STATEMENT, ROW, MIXED, file format, write mechanism, master-slave replication. This article provides in-depth analysis of principles and practical applications.
MySQL Undo/Redo Log: Transaction Rollback and Persistence
Undo Log for rollback, Redo Log for redo, transaction atomicity, durability, MVCC. This article provides in-depth analysis of principles and practical applications.
MySQL InnoDB Storage Structure: Tablespace, Segment, Exte...
InnoDB storage structure: Tablespace, Segment, Extent, Page, Row. Row formats: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED
MySQL Architecture to Threads: Storage Engine and Thread ...
MySQL 5.7/8.0 storage engine optimization, InnoDB thread model: IO Thread, Purge Thread, Page Cleaner Thread, Master Thread
MySQL InnoDB Disk Structure: Tablespace, Log System, Data...
InnoDB disk structure: system tablespace, file-per-table tablespace, general tablespace, temporary tablespace, Doublewrite Buffer, Redo Log, Undo Log - complete storage architecture guide.
MySQL InnoDB Storage Structure: Buffer Pool and Log Mecha...
InnoDB memory structure: Buffer Pool, Change Buffer, Adaptive Hash Index, Log Buffer - this article provides an in-depth analysis of principles and practical applications.
MySQL Storage Engines: InnoDB vs MyISAM Deep Comparison
MySQL storage engine comparison: InnoDB, MyISAM, Memory, Archive - transactions, locking mechanisms, index structures. This article provides in-depth analysis of principles and practical applications.
MySQL Connection, Thread, Query Cache and Optimizer Deep ...
MySQL connection mechanism, half-duplex communication, query cache, query optimizer explained. This article provides in-depth analysis of principles and practical applications.
MySQL Internal Architecture: Service Layer and Storage En...
MySQL four-layer architecture: network connection layer, service layer, storage engine layer, system file layer. This article provides in-depth analysis of principles and practical applications.
MySQL Evolution: From Standalone to Cloud Database
MySQL development history, main branches, architecture evolution (standalone -> master-slave -> sharding -> cloud database). This article provides in-depth analysis of principles and practical appl...
MySQL Binlog Deep Dive: Storage Directory, Change Records...
MySQL's Binary Log (binlog) is a log file type in MySQL that records all change operations performed on the database (excluding SELECT and SHOW queries). It is...
Offline Data Warehouse Practice: E-commerce Core Transact...
Focusing on three main metrics: order count, product count, payment amount, breakdown analysis dimensions by sales region and product type (3-level category).
Offline Data Warehouse Advertising Business Hive ADS Prac...
Complete solution for exporting Hive ADS layer data to MySQL using DataX. Covers ADS loading, DataX configuration, MySQL table creation, Shell script parameterized execution, and common error diagn...
Offline Data Warehouse Member Metrics Verification, DataX...
Offline data warehouse practice based on Hadoop + Hive + HDFS + DataX + MySQL, covering member metrics testing (active/new/retention), HDFS export, DataX sync to MySQL, and advertising business ODS...
Offline Data Warehouse Hive ADS Export MySQL DataX Practi...
The landing path for exporting Hive ADS layer tables to MySQL in offline data warehouse. Gives typical DataX solution: hdfsreader -> mysqlwriter. Focuses on DataX JSON configuration and common erro...
MySQL Master-Slave Replication: Read-Write Separation and...
This article provides an in-depth explanation of MySQL master-slave replication: master-slave mode, read-write separation, semi-sync replication principles and configuration.
MySQL Design Guide: Availability, Scalability, Consistency
This article provides an in-depth explanation of MySQL database design: availability (redundancy, failover), scalability (sharding, read-write separation), consistency (strong consistency vs eventu...
MySQL Deadlock: Principles, Scenarios and Prevention
This article provides an in-depth explanation of MySQL deadlocks: deadlock definition, necessary conditions, table lock deadlocks, row-level deadlocks, shared to exclusive lock conversion, deadlock...
MySQL Lock Mechanism: From Table Locks to Row Locks
This article provides an in-depth explanation of MySQL lock mechanisms: pessimistic locking, optimistic locking, table-level locks, row-level locks (shared locks, exclusive locks), intention locks,...
MySQL Parallel Replication: From 5.6 to 8.0 Evolution
This article provides an in-depth explanation of MySQL parallel replication technology: from 5.6 database-based parallel to 5.7 group commit-based to 8.0 writeset-based evolution.
MySQL Dual-Master Architecture and MMM High Availability
Deep dive into MySQL dual-master architecture and MMM high availability solution: dual-master mode, MMM fault handling, monitoring mechanism. This article analyzes principles and practical applicat...
MySQL MHA Master-Slave Failover: Architecture and Impleme...
Deep dive into MySQL MHA high availability solution: MHA architecture, fault handling, primary-standby switch, lag issues and solutions. This article analyzes principles and practical applications.
MySQL Sharding Technologies Compared: Range, Hash, Consis...
Sharding is the core technology for horizontal scaling in distributed databases. This article provides a comprehensive analysis of range-based, hash-based, and consistent hash sharding strategies w...
MySQL Distributed Primary Key Strategies: UUID, SnowFlake...
Explore MySQL distributed primary key strategies including UUID, SnowFlake, COMB, Redis, and database ID table approaches with their principles, pros and cons, and applicable scenarios comparison.
MySQL Scaling Guide: Triggers, Migration, and Performance...
Explore MySQL database horizontal scaling solutions for capacity, performance, and concurrency bottlenecks. Covers scaling triggers, migration plans, and best practices with detailed technical anal...
MySQL Sharding-JDBC: Lightweight Sharding & Read-Write Se...
A comprehensive guide to Sharding-JDBC, a lightweight Java database middleware solving database sharding, read-write separation and distributed transactions. Learn core principles and practical app...
MySQL Sharding-JDBC: Physical Tables, Logical Tables & Bi...
In distributed databases, sharding is implemented through physical tables, logical tables, data nodes and binding tables. Binding tables ensure master and child tables use the same sharding rules, ...
MySQL ShardingSphere: SQL Parse, Route, Rewrite & Execute...
Deep dive into ShardingSphere's sharding flow including SQL parsing, query optimization, SQL routing, SQL rewriting, SQL execution and result merging six major stages with detailed technical analysis.
MySQL Read-Write Separation: Master-Slave Architecture De...
Read-write separation is a common database architecture optimization technique that separates write operations to master and read operations to slaves through master-slave replication. Learn the fu...
MySQL ShardingSphere: SQL Support, Limitations & Optimiza...
Deep dive into ShardingSphere's SQL support scope, limitations, and optimization practices in sharding scenarios. Learn about supported SQL types, pagination subquery restrictions, and best practices.
MySQL Sharding-JDBC: Physical, Logical & Binding Tables E...
In distributed databases, sharding uses physical tables, logical tables, data nodes and binding tables for efficient data storage and queries. Binding tables ensure master and child tables use same...
MySQL ShardingSphere Saga: Flexible Transaction Implement...
ShardingSphere implements flexible transactions through servicecomb-saga integration, using SPI dynamic injection and reverse SQL technology to automatically generate compensation operations for di...
MySQL Distributed Transactions: TCC, Message Queue & Saga...
Distributed transaction patterns mainly include TCC, message queue, and Saga. TCC divides traditional 2PC into three business-controllable steps, message queue achieves eventual consistency, Saga s...
Seata Framework: Four Transaction Modes & Core Architecture
Seata is Alibaba's open-source distributed transaction solution providing one-stop transaction management with high performance, low intrusion and multi-language support.
MySQL XA Distributed Transactions: ShardingSphere Integra...
Traditional approaches rely on database vendor XA drivers and application server transaction managers. Modern systems use embedded transaction managers. ShardingSphere provides cross-database XA tr...
CAP Theory & Distributed Transactions: From 2PC/3PC to XA
CAP theory reveals that distributed systems cannot simultaneously satisfy consistency, availability and partition tolerance. 2PC ensures strong consistency through coordinator-controlled two-phase ...
ShardingSphere Data Masking: Transparent Encryption & Com...
Data masking is a key data security technology that balances data utility and privacy protection by replacing, encrypting or perturbing sensitive information. ShardingSphere provides complete maski...
ShardingSphere Saga Flexible Transaction Implementation
ShardingSphere implements flexible transactions through integration with third-party servicecomb-saga component, using SPI dynamic injection and reverse SQL technology to automatically generate com...
MySQL MyCat Distributed Database Middleware: Architecture...
MyCat is an open-source distributed database middleware fully compatible with MySQL protocol. Core features include database sharding, read-write separation, and result set merging with multiple sh...
Sharding-Proxy: Zero-Code-Invasive Database Sharding Solu...
Sharding-Proxy in the ShardingSphere ecosystem uses transparent database proxy architecture to implement database sharding, read-write separation, distributed transactions and data encryption witho...
MyCat Sharding Strategies: E-R, Global Tables & Sharding ...
MyCat distributed database sharding strategies include E-R table sharding, global table mechanism, shard nodes, node hosts and sharding rules.
MyCat server.xml: Core Configuration File Guide
server.xml is MyCat's core configuration file that centrally stores key parameters needed for system operation, including system parameters, user permissions, firewall configuration and performance...
MyCat Installation & Configuration: Setup, Service Manage...
MyCAT is an open-source database middleware supporting MySQL protocol, commonly used for database sharding and data sharding management. Installation includes unpacking, configuring environment var...
MySQL Transaction Isolation Levels: From READ UNCOMMITTED...
MySQL transaction isolation levels include READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Each level solves different concurrency problems.
MySQL Transactions: ACID Properties, Isolation Levels & U...
MySQL database transaction is a set of SQL operations executed as a single logical unit of work, following ACID principles: Atomicity, Consistency, Isolation and Durability.
MyCat schema.xml: Logical Database, Tables, Data Nodes Guide
schema.xml is MyCat's core configuration file managing logical databases, logical tables, data nodes and data hosts.
MySQL Transaction Issues: Dirty Read, Non-Repeatable Read...
Common issues in database transactions include dirty read, non-repeatable read, and phantom read. These issues are closely related to database isolation levels.
MongoDB vs MySQL: B-Tree vs B+Tree Index Mechanisms
MongoDB uses B-tree indexes where nodes store both data and keys. MySQL uses B+tree indexes with all data concentrated in leaf nodes. B+tree is better for range queries, B-tree is better for random...
MySQL Transaction Isolation Levels: Read Uncommitted to S...
This article provides an in-depth explanation of MySQL transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable, analyzing dirty read, non-repeatable read, ...
Flink JDBC Sink Deep Dive: MySQL Real-time Write, Batch O...
JDBC Sink is one of the most commonly used data output components, often used to write stream and batch processing results to relational databases like MySQL,...