Tag: Mysql
61 articles
MySQL Database Sharding: Vertical vs Horizontal Partitioning
Database sharding explained: vertical partitioning, horizontal partitioning, vertical sharding, vertical table splitting, horizontal table splitting.
MySQL MHA Master-Slave Failover: High Availability Solution
MHA high availability solution: Manager, Node, fault handling, failover.
MySQL Read-Write Separation: Principles and Implementation
Read-write separation principles, implementation methods, master-slave lag solutions, database middleware.
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.
MySQL Transaction and Lock Mechanism: ACID Properties
ACID properties: atomicity, consistency, isolation, durability, WAL mechanism.
MySQL Slow Query Analysis and Optimization Guide
Slow query log, mysqldumpslow, EXPLAIN analysis, common optimization solutions.
MySQL Index and Sorting: Filesort and Index Sort
filesort sorting, index sorting, two-pass sorting, single-pass sorting, clustered index and secondary index sorting.
MySQL Index Optimization: Table Lookup, Covering Index, Leftmost Prefix and LIKE
Table lookup queries, covering index, leftmost prefix principle, LIKE queries, NULL value handling.
MySQL EXPLAIN: Index Analysis and Query Optimization
EXPLAIN command: selecttype, type, possiblekeys, key, rows, key_len, Extra.
MySQL Clustered vs Secondary Index: Structure, Principle and Performance Deep Dive
Clustered index (secondary index, primary key index), secondary index, table lookup query, covering index.
MySQL Index Principles: B+Tree, Hash, Binary Search
Index principles: B+Tree, Hash index, binary search, InnoDB adaptive hash index.
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.
MySQL Binlog: Recording Modes, Replication, Data Recovery
Binlog three recording modes: STATEMENT, ROW, MIXED, file format, write mechanism, master-slave replication.
MySQL Undo/Redo Log: Transaction Rollback and Persistence
Undo Log for rollback, Redo Log for redo, transaction atomicity, durability, MVCC.
MySQL InnoDB Storage Structure: Tablespace, Segment, Extent, Page and Row Format Deep Dive
InnoDB storage structure: Tablespace, Segment, Extent, Page, Row. Row formats: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED
MySQL Architecture to Threads: Storage Engine and Thread Model Deep Dive
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 and Data Dictionary Deep Dive
InnoDB disk structure: system tablespace, file-per-table tablespace, general tablespace, temporary tablespace, Doublewrite Buffer, Redo Log, Undo Log - complete storage a...
MySQL InnoDB Storage Structure: Buffer Pool, Write Buffer and Log Mechanism Deep Dive
InnoDB memory structure: Buffer Pool, Change Buffer, Adaptive Hash Index, Log Buffer
MySQL Storage Engines: InnoDB vs MyISAM Deep Comparison
MySQL storage engine comparison: InnoDB, MyISAM, Memory, Archive - transactions, locking mechanisms, index structures.
MySQL Connection, Thread, Query Cache and Optimizer Deep Dive
MySQL connection mechanism, half-duplex communication, query cache, query optimizer explained.
MySQL Internal Architecture: Service Layer, Storage Engine and File System Coverage
MySQL four-layer architecture: network connection layer, service layer, storage engine layer, system file layer.
MySQL Evolution: From Standalone to Cloud Database
MySQL development history, main branches, architecture evolution (standalone -> master-slave -> sharding -> cloud database).
MySQL Binlog Deep Dive: Storage Directory, Change Records and Format
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).
Big Data 241 - Offline Data Warehouse Practice: E-commerce Core Transaction Data Model & MySQL Source Table Design
Focusing on three main metrics: order count, product count, payment amount, breakdown analysis dimensions by sales region and product type (3-level category).
Big Data 240 - Offline Data Warehouse Advertising Hive ADS Practice: DataX Export to MySQL
Complete solution for exporting Hive ADS layer data to MySQL using DataX.
Big Data 236 - Offline Data Warehouse Member Metrics Verification, DataX Export and Advertising Pipeline
This is a practical verification article for member theme and advertising business pipeline based on Hadoop + Hive + HDFS + DataX + MySQL.
Offline Data Warehouse Hive ADS Export MySQL DataX Practice
The landing path for exporting Hive ADS layer tables to MySQL in offline data warehouse. Gives typical DataX solution: hdfsreader -> mysqlwriter.
MySQL Master-Slave Replication: Read-Write Separation and Semi-Sync Replication
This article provides an in-depth explanation of MySQL master-slave replication: master-slave mode, read-write separation, semi-sync replication principles and configurat...
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.
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 exclusiv...
MySQL Lock Mechanism: From Table Locks to Row Locks
Pessimistic locking is a concurrency control mechanism based on a conservative "lock first, then modify" strategy.
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.
MySQL Dual-Master Architecture and MMM High Availability
MySQL dual-master mode is a common high-availability database architecture solution, particularly suitable for the following business scenarios:
MySQL MHA Master-Slave Failover: Architecture and Implementation
Deep dive into MySQL MHA high availability solution: MHA architecture, fault handling, primary-standby switch, lag issues and solutions. This article analyzes principles...
MySQL Sharding Technologies Compared: Range, Hash, Consistent Hash and Geohash
Sharding is the core technology for horizontal scaling in distributed databases.
MySQL Distributed Primary Key Strategies: UUID, SnowFlake, Redis and ID Tables
Explore MySQL distributed primary key strategies including UUID, SnowFlake, COMB, Redis, and database ID table approaches with their principles, pros and cons...
MySQL Scaling Guide: Triggers, Migration, and Performance Optimization
Explore MySQL database horizontal scaling solutions for capacity, performance, and concurrency bottlenecks.
MySQL Sharding-JDBC: Lightweight Sharding and Read-Write Separation Middleware
A comprehensive guide to Sharding-JDBC, a lightweight Java database middleware solving database sharding, read-write separation and distributed transactions.
MySQL Sharding-JDBC: Physical Tables, Logical Tables and Binding Tables
In distributed databases, sharding is implemented through physical tables, logical tables, data nodes and binding tables.
MySQL ShardingSphere: SQL Parse, Route, Rewrite and Execution Flow
Deep dive into ShardingSphere's sharding flow including SQL parsing, query optimization, SQL routing, SQL rewriting, SQL execution and result merging six major stages wit...
MySQL Read-Write Separation: Master-Slave Architecture Design and Routing
Read-write separation is a common database architecture optimization technique that separates write operations to master and read operations to slaves through master-slav...
MySQL ShardingSphere: SQL Support, Limitations and Optimization Practices
Deep dive into ShardingSphere's SQL support scope, limitations, and optimization practices in sharding scenarios. Learn about supported SQL types, pagination subquery res...
MySQL Sharding-JDBC: Physical, Logical and Binding Tables Explained
In distributed databases, sharding uses physical tables, logical tables, data nodes and binding tables for efficient data storage and queries.
MySQL ShardingSphere Saga: Flexible Transaction Implementation
ShardingSphere's flexible transactions are implemented through the third-party servicecomb-saga component, using SPI mechanism for dynamic injection.
MySQL Distributed Transactions: TCC, Message Queue and Saga Patterns
Distributed transaction patterns mainly include TCC, message queue, and Saga.
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 Integration and Practice
Traditional approaches rely on database vendor XA drivers and application server transaction managers. Modern systems use embedded transaction managers.
ShardingSphere Data Masking: Transparent Encryption and Compliance
Data masking is a key data security technology that balances data utility and privacy protection by replacing, encrypting or perturbing sensitive information.
ShardingSphere Saga Flexible Transaction Implementation
ShardingSphere's flexible transactions are implemented through the third-party servicecomb-saga component, using SPI mechanism for dynamic injection.
MySQL MyCat Distributed Database Middleware: Architecture and Core Principles
MyCat is an open-source distributed database middleware fully compatible with MySQL protocol.
Sharding-Proxy: Zero-Code-Invasive Database Sharding Solution
Sharding-Proxy is one of the core products in the Apache ShardingSphere ecosystem, using a transparent database proxy architecture.
MyCat Sharding Strategies: E-R, Global Tables and Sharding Rules
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, storing almost all system configuration information needed by MyCat.
MyCat Installation and Configuration: Setup, Service Management and Schema Files
MyCAT is an open-source database middleware supporting MySQL protocol, commonly used for database sharding and data sharding management.
MySQL Transaction Isolation Levels: From READ UNCOMMITTED to SERIALIZABLE
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 and Use Cases
MySQL database transaction is a set of SQL operations executed as a single logical unit of work, following ACID principles: Atomicity, Consistency, Isolation and Durabili...
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 and Phantom 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.
MySQL Transaction Isolation Levels: Read Uncommitted to Serializable
This article provides an in-depth explanation of MySQL transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable...
Flink JDBC Sink Deep Dive: MySQL Real-time Write, Batch Output and Retry
In Apache Flink, JDBC Sink is an important data output component that allows writing stream or batch processed data to relational databases through JDBC connections.