mysql数据库引擎

King

引擎简介

InnoDB

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这篇文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MyISAM

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

HEAP

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

index索引介绍

索引也分为不同的种类，而且也有不同的分类方法，比较常用的是普通索引和聚集索引。

普通索引

其实对某字段建立了索引就相当于是对该字段新建立了一个表，这个表里的元素是安照这个字段有序排列。这样有什么好处呢？好处就在于如果我们select的时候要搜索该字段，那就会在这个索引表中先查找，因为索引表是有序的，所以在检索该字段的时候就是二分搜索，速度自然会比在原表上快，然后如果我只需要这一个字段的话，查询就可以结束了，但如果还需要除索引字段的其他字段的话，那么就会根据这个索引表的字段对应到主表中，然后再获取。
看了上面讲的，是不是感觉有点迷茫？下面看一下图就会清晰很多。

（图片来源于百度）
大家可以看到这里我们以Col2建立索引之后右边有一颗二叉树，可能大家会问不是说好了是一张表吗，怎么又是二叉树了，好吧表本身就是一种树形的数据结构存储，虽然实际上很少会选取二叉树，但此处方便讲解。可以看到Col2单独的一棵树，然后每一个节点对过来是一条记录，如果我们执行 select Col2 from tablename where Col2=34;那么直接在右边的树中二叉搜索，找到了就可以返回了。如果我们执行 select * from tablename where Col2=34；那么可以看到需要的不仅仅是Col2这一个字段，那么还是先在二叉树中查找，然后找到了之后对应到主表中，然后返回整条记录。

索引的数据结构

通过上面的图我们可以看到，索引的本质其实就是新建了一张表，而表本质上的数据结构就是树形结构，所以索引也是树形结构。但实际运用中并没有谁用红黑树，avl树这种数据结构，一般是b+树，接下来给大家大致介绍一下b+树的构成。

（图片来源于百度）
b+树在构建时和我们之前提到的二三树很像，只是有一些改进，b+树的非叶子节点不包含value的信息，也就是说非叶子结点只起到一个导航的作用，所有的value放在了叶子结点里，这样由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。通常会将b+树进行优化，增加顺序访问指针。

（图片来源于百度0）
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
可以看到b+树对于表的存储是一种很方便的数据结构。那么为什么不用红黑树呢，因为数据量大的时候，会导致这种二叉树深度太深，io次数会很多，层数很少的b+树可以有效降低io次数。

聚集索引

聚集索引和普通索引是不一样的，聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况。意思就是说上面的普通索引我们可以看到是另建了一个表，然后当查询到了索引没有覆盖到的字段的时候是将这个字段映射到了主表中然后进行查询的。而聚集索引建立后主表本身就会按照这个索引的结构来存储，意思就是说主表直接就按这个来存了。这也是为什么聚集索引一定是唯一的原因，因为一张表中只能有一种存储方式。

聚集索引与普通索引

两种索引谁更快呢？这当然是没有悬念的，聚集索引更快咯，因为普通索引查到没有覆盖的字段的时候需要向主表中映射过去，然后再获取，而聚集索引因为其本身就包含了所有数据，所以一次就好~

主键与聚集索引

在我们新建一个表时，如果没有定义主键，那么表格的数据是顺序线性存储的，在定义的主键之后，因为主键默认有索引，并且在很多平台上默认是聚集索引，所以在主键定义的时候就会把整个表变为一个树形结构（如果主键是聚集索引），但要知道的是主键不一定是聚集索引，也可以是普通索引，只是很多平台默认为聚集，不要盲目划等号。

索引的利弊

那么索引既然这么快是不是越多越好呢？不存在的，因为索引本身是一个数据表，那么在插入或删除的时候就涉及到了索引表的改变，b+树的插入删除涉及到很多节点操作，或许会消耗很多时间。所以我们对于常改变的字段不宜建索引，而对于改动较少的字段就很合适，在设计表的时候我们要灵活选取，才能高效。

mysql索引

索引类型

1. 普通索引

这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

知识兔td>

–直接创建索引
CREATE INDEX index_name ON table(column(length))
–修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
–创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
–删除索引
DROP INDEX index_name ON table

知识兔td>

2. 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

3. 全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

知识兔td>

–创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
–修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
–直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

知识兔td>

4. 单列索引、多列索引

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

5. 组合索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time

–title

为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

知识兔td>

–使用到上面的索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;

知识兔td>

MySQL索引的优化

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

1. 何时使用聚集索引或非聚集索引？

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

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。其实这个具体用法我还不是很理解，只能等待后期的项目开发中慢慢学学了。

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6. 不要在列上进行运算

例如：select from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

建立索引的优缺点比较

为什么要创建索引呢？

这是因为，创建索引可以大大提高系统的性能。 
 第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
 第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。 
 第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
 第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
 第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

 也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点， 但是，为表中的每一个列都增加索引，是非常不明智的。

这是因为，增加索引也有许多不利的一个方面:

 第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

 第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚集索引，那么需要的空间就会更大。 

 第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

知识兔strong>什么样的字段适合创建索引:

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引，例如：

第一、在经常需要搜索的列上，可以加快搜索的速度； 

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 

第六、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。





建立索引，一般按照select的where条件来建立，比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上简历索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。

知识兔strong>什么样的字段不适合创建索引:

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

　　第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，

并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，

在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。

增加索引，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

因此，当修改性能远远大于检索性能时，不应该创建索引。

参考：

《mysql从入门到精通》

https://www.cnblogs.com/owenma/p/8575646.html

https://www.cnblogs.com/liwei2222/p/8053940.html

https://www.2cto.com/database/201503/385669.html

原文:大专栏 mysql数据库引擎

计算机

2022年1月31日

mysql数据库引擎

引擎简介

InnoDB

ISAM

MyISAM

HEAP

index索引介绍

普通索引

索引的数据结构

聚集索引

聚集索引与普通索引

主键与聚集索引

索引的利弊

mysql索引

索引类型

MySQL索引的优化

建立索引的优缺点比较

热门标签

最新文章