MySQL存储引擎

最新推荐文章于 2024-07-30 16:52:36 发布

垃圾继承拖拉机

最新推荐文章于 2024-07-30 16:52:36 发布

阅读量168

点赞数 1

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/a219219219219/article/details/107846867

版权

数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

DBMS使用数据引擎进行select、update和delete等操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，mysql 5之后，支持的存储引擎有十几个，但是常用的有两种，MyISAM和InnoDB。

功能	MyISAM	InnoDB
存储限制	256TB	64TB
事务安全	否	是
全文类型索引	是	否
支持外键	否	是
是否聚簇索引	非聚簇索引	聚簇索引

如果要拥有提交、回滚和恢复的事务安全能力，并要求实现并发控制，选InnoDB。
如果数据表主要用来插入和查询记录，此时MyISAM 引擎有较高的处理效率。
两种引擎都不支持哈希索引，而两者都支持树索引。

注：两种引擎都不支持哈希索引，虽然InnoDB引擎有自适应哈希索引（adaptive hash index），但这不是创建索引时可指定的。

下面开始细讲一下事务安全，全文索引和聚集索引三个概念在存储引擎里的区别

事务安全

事务安全与否指的是该引擎是否支持事务的操作。innodb支持事务操作，myisam不支持事务操作。

全文索引

mysql5.6之前，只有MyISAM引擎支持全文索引，5.6版本之后innodb开始支持全文索引。

myisam全文索引的对象是一个“全文集合”，这也许是数据表中的某一列或多列数据，具体就是将一条数据被索引的所有列拼接成一个字符串，然后进行索引。

myisam的全文索引分为两层，第一层是关键字，第二层是每个关键字对应的“文档指针”，全文索引并不会使用文档对象的所有词语，它会根据规则过滤掉部分词语，过滤规则有：

1、停用词列表的词不会被索引，默认根据通用英语设置，也可以使用参数ft_stopword_file指定一个外部文件来定义停用词。

2、会过滤掉单词长度大于ft_min_word_len和小于ft_max_word_len的词

创建全文索引

1.创建表的同时创建全文索引

CREATE TABLE article (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title, body)
) TYPE=MYISAM;

2.通过 alter table 的方式来添加

ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name (`name`) #ft_stu_name是索引名，可以随便起

或者：ALTER TABLE `student` ADD FULLTEXT ft_stu_name (`name`)

3. 直接通过create index的方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

也可以在创建索引的时候指定索引的长度：

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20))

是否聚集索引

聚集索引和非聚集索引更准确来说，是一种数据存储方式。

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB使用的是聚簇索引，聚簇索引存储，行数据和主键B+树存储在一起，辅助键B+树只存储辅助键和主键。将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键（id为主键），则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引相对于非聚簇索引的优势：

1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。

注：聚集索引和聚簇索引是同一个东西，都是clustered index。

注：我看到一篇博客说到，不管是聚集索引还是非聚集索引，最终都要通过主键来查到真实的数据。而这里摘录的内容，和那篇博客摘录的内容看似矛盾，因为那里说的是

在innodb存储引擎下的聚集索引和非聚集索引，而这里说的是innodb下的聚集索引和myism下的非聚集索引，所以会不一样。

另外，那里说的在innodb存储引擎下的非聚集索引，是通过CREATE INDEX index_name
ON table_name (column_name)创建了普通索引（谈到innodb下的非聚集索引和聚集索引时，提到的非聚集索引其实也是普通索引）。（那篇文章所的链接https://www.cnblogs.com/aspwebchh/p/6652855.html）

部分内容引用至： https://blog.csdn.net/voidccc/article/details/40077329