首頁 資料庫 mysql教程 mysql存储引擎memory,ndb,innodb之选择

mysql存储引擎memory,ndb,innodb之选择

Jun 07, 2016 pm 03:46 PM
innodb memory mysql 儲存 引擎

1 mysql的innodb和cluster的NDB引擎都支持事务,在有共同的特性外,也有不同之处: 以mysql cluster NDB 7.3和MySQL 5.6之InnoDB为例: ndb7.3基于mysql5.6,包括支持innodb1.1,因此可以在cluster里使用innodb表,但这些表不是集群的。 MySQL Cluster NDB存

1

mysql的innodb和cluster的NDB引擎都支持事务,在有共同的特性外,也有不同之处:
以mysql cluster NDB 7.3和MySQL 5.6之InnoDB为例:
ndb7.3基于mysql5.6,包括支持innodb1.1,因此可以在cluster里使用innodb表,但这些表不是集群的。
MySQL Cluster NDB存储引擎用分布式, shared-nothing的架构实现,这使其和innodb有不少不同之处。比如事务、外键、表限制等,具体见下表:

Theseare shown in the following table:

Feature

InnoDB1.1

MySQLClusterNDB 7.3, MySQL Cluster NDB7.4

MySQLServer Version

5.6

5.6

InnoDBVersion

InnoDB5.6.23

InnoDB5.6.23

MySQLCluster Version

N/A

NDB7.3.9

StorageLimits

64TB

3TB

(Practicalupper limit based on 48 data nodes with 64GB RAM each; can beincreased with disk-based data and BLOBs)

ForeignKeys

Yes

Priorto MySQL Cluster NDB 7.3: No. (Ignored, as withMyISAM)

Availablein MySQL Cluster NDB 7.3.

Transactions

Allstandard types

READCOMMITTED

MVCC

Yes

No

DataCompression

Yes

No

(MySQLCluster checkpoint and backup files can be compressed)

LargeRow Support (> 14K)

SupportedforVARBINARY,VARCHAR,BLOB,andTEXTcolumns

SupportedforBLOBandTEXTcolumns only

(Usingthese types to store very large amounts of data can lower MySQLCluster performance)

ReplicationSupport

Asynchronousand semisynchronous replication using MySQL Replication

Automaticsynchronous replication within a MySQL Cluster.

Asynchronousreplication between MySQL Clusters, using MySQL Replication

Scaleoutfor Read Operations

Yes(MySQL Replication)

Yes(Automatic partitioning in MySQL Cluster; MySQL Replication)

Scaleoutfor Write Operations

Requiresapplication-level partitioning (sharding)

Yes(Automatic partitioning in MySQL Cluster is transparent toapplications)

HighAvailability (HA)

Requiresadditional software

Yes(Designed for 99.999% uptime)

NodeFailure Recovery and Failover

Requiresadditional software

Automatic

(Keyelement in MySQL Cluster architecture)

Timefor Node Failure Recovery

30seconds or longer

Typically

Real-TimePerformance

No

Yes

In-MemoryTables

No

Yes

(Somedata can optionally be stored on disk; both in-memory and diskdata storage are durable)

NoSQLAccess to Storage Engine

Nativememcached interface in development (see the MySQL Dev ZonearticleMySQLCluster 7.2 (DMR2): NoSQL, Key/Value, Memcached)

Yes

MultipleAPIs, including Memcached, Node.js/JavaScript, Java, JPA, C++,and HTTP/REST

Concurrentand Parallel Writes

Notsupported

Upto 48 writers, optimized for concurrent writes

ConflictDetection and Resolution (Multiple Replication Masters)

No

Yes

HashIndexes

No

Yes

OnlineAddition of Nodes

Read-onlyreplicas using MySQL Replication

Yes(all node types)

OnlineUpgrades

No

Yes

OnlineSchema Modifications

Yes,as part of MySQL 5.6.

Yes.



数据驱动型应用负载于innodb和NDB存储引擎之主要不同:


Workload

InnoDB

MySQLCluster (NDB)

High-VolumeOLTP Applications

Yes

Yes

DSSApplications (data marts, analytics)

Yes

Limited(Join operations across OLTP datasets not exceeding 3TB in size)

CustomApplications

Yes

Yes

PackagedApplications

Yes

Limited(should be mostly primary key access).

MySQLCluster NDB 7.3 supports foreign keys.

In-NetworkTelecoms Applications (HLR, HSS, SDP)

No

Yes

SessionManagement and Caching

Yes

Yes

E-CommerceApplications

Yes

Yes

UserProfile Management, AAA Protocol

Yes

Yes


这两种存储引擎适合的应用场景


Preferredapplication requirements forInnoDB

Preferredapplication requirements forNDB

  • Foreignkeys

    Note

    MySQLCluster NDB 7.3 supports foreign keys.

  • Fulltable scans

  • Verylarge databases, rows, or transactions

  • Transactionsother thanREADCOMMITTED

  • Writescaling

  • 99.999%uptime

  • Onlineaddition of nodes and online schema operations

  • MultipleSQL and NoSQL APIs (seeMySQLCluster APIs: Overview and Concepts)

  • Real-timeperformance

  • Limiteduse ofBLOBcolumns

  • Foreignkeys are supported, although their use may have an impact onperformance at high throughput



2

如何选择memory存储引擎或mysql cluster:
When to Use MEMORY or MySQL Cluster.
Developers looking to deploy applications that use the MEMORY storage engine for important, highly available, or frequently updated data should consider whether MySQL Cluster is a better choice. A typical use case for the MEMORY engine involves these
characteristics:
? Operations involving transient, non-critical data such as session management or caching. When the MySQL server halts or restarts, the data in MEMORY tables is lost.
? In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
? A read-only or read-mostly data access pattern (limited updates).

MySQL Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:
? Row-level locking and multiple-thread operation for low contention between clients.
? Scalability even with statement mixes that include writes.
? Optional disk-backed operation for data durability.
? Shared-nothing architecture and multiple-host operation with no single point of failure, enabling 99.999% availability.
? Automatic data distribution across nodes; application developers need not craft custom sharding or partitioning solutions.
? Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY.

MEMORY存储引擎和MySQL Cluster的更多细节对比参见白皮书《Scaling Web Services with MySQL Cluster: An Alternative to the MySQL Memory Storage Engine》

Table 15.4 <strong>MEMORY</strong>Storage Engine Features

Storagelimits

RAM

Transactions

No

Lockinggranularity

Table

MVCC

No

Geospatialdata type support

No

Geospatialindexing support

No

B-treeindexes

Yes

T-treeindexes

No

Hashindexes

Yes

Full-textsearch indexes

No

Clusteredindexes

No

Datacaches

N/A

Indexcaches

N/A

Compresseddata

No

Encrypteddata[a]

Yes

Clusterdatabase support

No

Replicationsupport[b]

Yes

Foreignkey support

No

Backup/ point-in-time recovery[c]

Yes

Querycache support

Yes

Updatestatistics for data dictionary

Yes

[a]Implemented in the server (via encryption functions), ratherthan in the storage engine.

[b]Implemented in the server, rather than in the storage engine.

[c]Implemented in the server, rather than in the storage engine.



3

myisam, memory, ndb, archive, innodb存储引擎功能汇总:

Table 15.1 StorageEngines Feature Summary

Feature

MyISAM

Memory

InnoDB

Archive

NDB

Storagelimits

256TB

RAM

64TB

None

384EB

Transactions

No

No

Yes

No

Yes

Lockinggranularity

Table

Table

Row

Table

Row

MVCC

No

No

Yes

No

No

Geospatialdata type support

Yes

No

Yes

Yes

Yes

Geospatialindexing support

Yes

No

Yes[a]

No

No

B-treeindexes

Yes

Yes

Yes

No

No

T-treeindexes

No

No

No

No

Yes

Hashindexes

No

Yes

No[b]

No

Yes

Full-textsearch indexes

Yes

No

Yes[c]

No

No

Clusteredindexes

No

No

Yes

No

No

Datacaches

No

N/A

Yes

No

Yes

Indexcaches

Yes

N/A

Yes

No

Yes

Compresseddata

Yes[d]

No

Yes[e]

Yes

No

Encrypteddata[f]

Yes

Yes

Yes

Yes

Yes

Clusterdatabase support

No

No

No

No

Yes

Replicationsupport[g]

Yes

Yes

Yes

Yes

Yes

Foreignkey support

No

No

Yes

No

No

Backup/ point-in-time recovery[h]

Yes

Yes

Yes

Yes

Yes

Querycache support

Yes

Yes

Yes

Yes

Yes

Updatestatistics for data dictionary

Yes

Yes

Yes

Yes

Yes

[a]InnoDB support for geospatial indexing is available in MySQL5.7.5 and higher.

[b]InnoDB utilizes hash indexes internally for its AdaptiveHash Index feature.

[c]InnoDB support for FULLTEXT indexes is available in MySQL5.6.4 and higher.

[d]Compressed MyISAM tables are supported only when using thecompressed row format. Tables using the compressed row formatwith MyISAM are read only.

[e]Compressed InnoDB tables require the InnoDB Barracuda fileformat.

[f]Implemented in the server (via encryption functions), ratherthan in the storage engine.

[g]Implemented in the server, rather than in the storageengine.

[h]Implemented in the server, rather than in the storageengine.


要选mysql cluster要根据ndb存储引擎的特征和应用场景做详细测试,安装简测见我的博文《centos65安装简测mysql cluster 7.3.7》http://blog.csdn.net/beiigang/article/details/43485585


参考

http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html




-----------------


blog.csdn.net/beiigang



本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前 By 尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

PHP 的大數據結構處理技巧 PHP 的大數據結構處理技巧 May 08, 2024 am 10:24 AM

PHP 的大數據結構處理技巧

如何優化 PHP 中的 MySQL 查詢效能? 如何優化 PHP 中的 MySQL 查詢效能? Jun 03, 2024 pm 08:11 PM

如何優化 PHP 中的 MySQL 查詢效能?

如何在 PHP 中使用 MySQL 備份和還原? 如何在 PHP 中使用 MySQL 備份和還原? Jun 03, 2024 pm 12:19 PM

如何在 PHP 中使用 MySQL 備份和還原?

適用於桌上型電腦的 CAMM2:MSI 解釋了遊戲塔新 RAM 標準的優勢 適用於桌上型電腦的 CAMM2:MSI 解釋了遊戲塔新 RAM 標準的優勢 Aug 17, 2024 pm 06:47 PM

適用於桌上型電腦的 CAMM2:MSI 解釋了遊戲塔新 RAM 標準的優勢

如何使用 PHP 插入資料到 MySQL 表? 如何使用 PHP 插入資料到 MySQL 表? Jun 02, 2024 pm 02:26 PM

如何使用 PHP 插入資料到 MySQL 表?

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 Dec 09, 2024 am 11:42 AM

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤

如何在 PHP 中使用 MySQL 預存程序? 如何在 PHP 中使用 MySQL 預存程序? Jun 02, 2024 pm 02:13 PM

如何在 PHP 中使用 MySQL 預存程序?

如何使用 PHP 建立 MySQL 表? 如何使用 PHP 建立 MySQL 表? Jun 04, 2024 pm 01:57 PM

如何使用 PHP 建立 MySQL 表?

See all articles