B-tree R-tree B+-tree indexes 索引顺序存取方法 ISAM MySQL实现拓展ISAM为MyISAM

High Performance MySQL, Third Edition
by Baron Schwartz, Peter Zaitsev, and Vadim Tkachenko

http://dev.mysql.com/doc/refman/5.7/en/

https://zh.wikipedia.org/wiki/ISAM

https://en.wikipedia.org/wiki/ISAM

 

ISAM (an acronym for Indexed Sequential Access Method) is a method for indexing data for fast retrieval.

索引顺序存取方法（ISAM, Indexed Sequential Access Method）最初是IBM公司发展起来的一个文件系统，可以连续地（按照他们进入的顺序）或者任意地（根据索引）记录任何访问。每个索引定义了一次不同排列的记录。现在这个概念用在许多场合：

  • 特指IBM公司的ISAM产品
  • 数据库系统中提供用户接口从数据文件中检索数据。
  • 通常指，数据库的索引，这种索引被大多数数据库所采用，包括关系数据库或其它。

在ISAM系统，数据组织成有固定长度的记录，按顺序存储的。

 

 

In an ISAM system, data is organized into records which are composed of fixed length fields. Records are stored sequentially, originally to speed access on a tape system. A secondary set of hash tables known as indexes contain "pointers" into the tables, allowing individual records to be retrieved without having to search the entire data set. This is a departure from the contemporaneous navigational databases, in which the pointers to other data were stored inside the records themselves. The key improvement in ISAM is that the indexes are small and can be searched quickly, thereby allowing the database to access only the records it needs. Additionally modifications to the data do not require changes to other data, only the table and indexes in question.

When an ISAM file is created, index nodes are fixed, and their pointers do not change during inserts and deletes that occur later (only content of leaf nodes change afterwards). As a consequence of this, if inserts to some leaf node exceed the node's capacity, new records are stored in overflow chains. If there are many more inserts than deletions from a table, these overflow chains can gradually become very large, and this affects the time required for retrieval of a record.[4]

 

 https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

Table 16.2 MyISAM Storage Engine Features

FeatureSupport
B-tree indexes Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) Yes
Cluster database support No
Clustered indexes No
Compressed data Yes (Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.)
Data caches No
Encrypted data Yes (Implemented in the server via encryption functions.)
Foreign key support No
Full-text search indexes Yes
Geospatial data type support Yes
Geospatial indexing support Yes
Hash indexes No
Index caches Yes
Locking granularity Table
MVCC No
Replication support (Implemented in the server, rather than in the storage engine.) Yes
Storage limits 256TB
T-tree indexes No
Transactions No
Update statistics for data dictionary Yes
 

 

Each MyISAM table is stored on disk in two files. The files have names that begin with the table name and have an extension to indicate the file type. The data file has an .MYD (MYData) extension. The index file has an .MYI(MYIndex) extension. The table definition is stored in the MySQL data dictionary.

 

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

Table 15.1 InnoDB Storage Engine Features

FeatureSupport
B-tree indexes Yes
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.) Yes
Cluster database support No
Clustered indexes Yes
Compressed data Yes
Data caches Yes
Encrypted data Yes (Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.)
Foreign key support Yes
Full-text search indexes Yes (InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.)
Geospatial data type support Yes
Geospatial indexing support Yes (InnoDB support for geospatial indexing is available in MySQL 5.7 and later.)
Hash indexes No (InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.)
Index caches Yes
Locking granularity Row
MVCC Yes
Replication support (Implemented in the server, rather than in the storage engine.) Yes
Storage limits 64TB
T-tree indexes No
Transactions Yes
Update statistics for data dictionary Yes
 
 

mysql_High.Performance.MySQL.3rd.Edition.Mar.2012

A B-Tree index speeds up data access because the storage engine doesn’t have to scan
the whole table to find the desired data. Instead, it starts at the root node (not shown
in this figure). The slots in the root node hold pointers to child nodes, and the storage
engine follows these pointers. It finds the right pointer by looking at the values in the
node pages, which define the upper and lower bounds of the values in the child nodes.
Eventually, the storage engine either determines that the desired value doesn’t exist or
successfully reaches a leaf page.
 
 
 
https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXTindexes.
 
 
 
 
posted @ 2016-08-23 00:24  papering  阅读(329)  评论(0)  编辑  收藏  举报