【MySQL】MySQL存储引擎以及索引

一、存储引擎

MySQL中的数据用各种不同的技术存储在文件中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。

我的理解是，数据存储使用不同的数据结构，使得它们各自支持不同的技术和功能，因此划分成不同的存储引擎。而存储引擎是对应表的，所以也被称为表类型。

用以下指令可以查看MySQL中支持的存储引擎，挑几个整理一下知识。

show engines;

+——————–+———+————————————————
—————-+————–+——+————+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+——————–+———+————————————————
—————-+————–+——+————+
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
+——————–+———+————————————————
—————-+————–+——+————+
9 rows in set (0.00 sec)

support一列代表是否支持该存储引擎，yes表示支持、no表示不支持、default表示默认使用、disabled代表被关闭；transactions一列代表是否支持事务处理；xa表示是否支持分布式事务，savepoints表示是否支持分布式事务。

（MySQL默认存储引擎从版本5.5.5开始）

支持事务，行级锁，以及外键，拥有高并发处理能力。但是在创建索引和加载数据时，比MyISAM慢。

不支持事务和行级锁。所以速度很快，性能优秀。可以对整张表加锁，支持并发插入，支持全文索引。

支持Hash索引，内存表，Memory引擎将数据存储在内存中，表结构不是存储在内存中的，查询时不需要执行磁盘I/O操作，所以要比MyISAM和InnoDB快很多倍，但是数据库断电或是重启后，表中的数据将会丢失，表结构不会丢失。

InnoDB跟MyISAM的区别

1.存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2.存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3.可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
4.事务支持
MyISAM：强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
5.AUTO_INCREMENT
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
6.表锁差异
MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
7.全文索引
MyISAM：支持 FULLTEXT类型的全文索引
InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
8.表主键
MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
9.表的具体行数
MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。
InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
操作
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
11.外键
MyISAM：不支持
InnoDB：支持

二、索引的数据结构

索引是一种数据结构。索引本身很大，不可能全部存储在内存中，因此索引以索引表的形式存储在磁盘中。

+树索引

并不能找到一个给定健值的具体行，B+树索引只能找到被查找数据行所在的页，然后从数据库将页读入内存，在内存中查找。
B+树索引可以分为聚集索引和辅助索引。聚簇索引是按照数据存放的逻辑地址为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

聚集索引
聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。
InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
辅助索引中，叶结点的data域存放的是对应记录的主键的key。
对于建立辅助索引的表需要先根据辅助索引找到相应的主键，再根据主键在聚集索引中找到相应的记录集。
非聚集索引
非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
主键索引中，叶节点的data域存放的是数据记录的地址，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。（MYISAM采用此种索引方式）。
区别
- 聚集索引表里数据物理存储顺序和主键索引的顺序一致，所以如果新增数据是离散的，会导致数据块趋于离散，而不是趋于顺序。而非聚集索引表数据写入的顺序是按写入时间顺序存储的。
- 聚簇索引索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
适用情景

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	Y	Y
一个或极少不同的值	N	N
返回某范围内的数据	Y	N
小数目的不同值	Y	N
大数目的不同值	N	Y
外键	Y	Y
主键	Y	Y
频繁更新的列	N	Y
频繁修改索引列	N	Y

索引

在《数据库索引探索》中已经整理过Hash索引的知识，这里不再重复。

三、MySQL中的索引使用

1.普通索引

最普通的索引，没有任何限制。

# 创建表的同时创建索引
CREATE TABLE `artical` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `subject` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
    `time` Date NULL DEFAULT NULL,
    PRIMARY KEY(`id`),
    INDEX index_subject (subject)
);

# 直接创建索引
CREATE INDEX <index_name> ON <table_name>(<column_name>);

# 修改表结构的方式添加索引
ALTER TABLE <table_name> ADD INDEX index_name (<column_name>);

实际上，最好的做法是创建表的时候创建索引，如果创建表之后再修改新建索引的话，对于聚集索引，会根据原来的表，创建一个新的表带有索引数据结构，再把原来的表删去，新创建的表改成原来的表的名字。而非聚集索引则是通过修改索引文件来完成。所以都是需要占用额外的资源来修改或新建索引的。

2.唯一索引

与普通索引的不同的是，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

# 创建表的时候直接指定
CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `tel` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY(`id`),
    UNIQUE index_subject (name)
);

# 直接创建索引
CREATE UNIQUE INDEX <index_name> ON <table_name>(<column_name>);

# 修改表结构的方式添加索引
ALTER TABLE <table_name> ADD UNIQUE index_name (<column_name>);

3.主键索引

索引值必须唯一，不能为NULL，在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位。

4.全文索引

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

# 创建表的时候添加全文索引
CREATE TABLE `artical` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `subject` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
    `time` Date NULL DEFAULT NULL,
    PRIMARY KEY(`id`),
    FULLTEXT (content)
)engine=MyISAM;

# 修改表结构添加全文索引
ALTER TABLE artical ADD FULLTEXT INDEX index_content(content);

# 直接创建索引
CREATE FULLTEXT INDEX index_content ON artical(content);

5.单列索引，多列索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

6.组合索引

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (subject,title(50),time(10))，实际上包含三个索引(subject),(subject, title), (subject, title, time)。

在使用查询的时候遵循“最左前缀”：

不按索引最左列开始查询不适用索引。例如对idnex(c1,c2,c3),使用where c2 = “aaa” and c3 = “bbb”不能使用索引
查询中某个列有范围查询，则其右边的所有列都无法使用查询。例如对idnex(c1,c2,c3)，where c1 = “xxx” and c2 like = “aa%” and c3 = “sss”查询只会使用索引的前两列，因为like是范围查询
不能跳过某个字段进行查询。

四、使用索引的优点和缺点

优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件，可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引，可以提高性能

缺点

1.创建索引和维护索引会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

秒客网

【MySQL】MySQL存储引擎以及索引

一、存储引擎

（MySQL默认存储引擎从版本5.5.5开始）

InnoDB跟MyISAM的区别

二、索引的数据结构

+树索引

索引

三、MySQL中的索引使用

1.普通索引

2.唯一索引

3.主键索引

4.全文索引

5.单列索引，多列索引

6.组合索引

四、使用索引的优点和缺点

优点

缺点

相关文章

【MySQL】MySQL存储引擎以及索引

一、存储引擎

（MySQL默认存储引擎 从版本5.5.5开始）

InnoDB跟MyISAM的区别

二、索引的数据结构

+树索引

索引

三、MySQL中的索引使用

1.普通索引

2.唯一索引

3.主键索引

4.全文索引

5.单列索引，多列索引

6.组合索引

四、使用索引的优点和缺点

优点

缺点

相关文章

（MySQL默认存储引擎从版本5.5.5开始）