mysql 数据库常用引擎及其差异，索引类型、索引使用场景

admin管理员组

文章数量:1530085

最近痴迷于研究数据库索引，发现网上好多文章都是高深的术语，经过数篇文章的细读，有一些总结和自己的理解，供大家参考。（部分文字图片转载自其他博客）

1、mysql常用数据引擎

mysql常见的数据库引擎主要Innodb和MyIASM

1.1 Innodb引擎（索引模式：聚集索引）

Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

1.2 MyIASM引擎（索引模式：非聚集索引）

MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。

2、两种引擎对比

针对上面的介绍，进行一下总计以及实际数据的对比

1.InnoDB不支持FULLTEXT类型的索引。

2.InnoDB 中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含 where条件时，两种表的操作是一样的

3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

6、MyISAM不支持事务，InnoDB是事务类型的存储引擎

7、MyISAM只支持表级锁(BDB支持页级锁和表级锁默认为页级锁），而InnoDB支持行级锁和表级锁默认为行级锁

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如 update table set num=1 where name like “%aaa%”

那么数据库表级锁、页级锁、行级锁以及常说的数据库死锁又是什么意思呢？？

页级锁:引擎 BDB，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。（不常见）

表级锁:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许

行级锁:引擎 INNODB ， 单独的一行记录加锁，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。

行级锁定的优点：

1. 当在许多线程中访问不同的行时只存在少量锁定冲突。
2. 回滚时只有少量的更改。
3. 可以长时间锁定单一的行。

行级锁定的缺点：

1. 比页级或表级锁定占用更多的内存。
2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
3. 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

总结

1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

2.1性能比较

我们平常用的基本上都是InnoDB引擎，因为大部分业务都是需要数据库事务的，MyISAM在没有事务需求，并发量没有要求的时候还是很强的
测试数据：（测试方法：连续提交10个query， 表记录总数：38万 ， 时间单位 s）

引擎类型	MyISAM	InnoDB
count	0.0008357	3.0163
查询主键	0.005708	0.1574
查询非主键	24.01	80.37
更新主键	0.008124	0.8183
更新非主键	0.004141	0.02625
插入	0.004188	0.3694

可以看出MyISAM在没有事务和并发的情况下，基本完爆InnoDB引擎

加入索引以后呢?

加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍
同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更新速度减慢为原来的1/30。
要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。

那种更占存储大小呢？

MyISAM可省很多的硬盘空间，大概比InnoDB节约20%的存储空间（200w数据的情况下）

在并发情况下，MyISAM就比较弱了，因为他是表级锁

3、两种引擎索引结构

两种引擎所使用的索引结构都是B+树!

这一块我理解的不是很深，为什么结构是B+树呢，总结一句，就是因为他很强，很快
像其他的二叉查找树或红黑树结构都比较差，所以就用了B+树。
好吧，编不下去了，自己去看别人的文章吧！

https://wwwblogs/boothsun/p/8970952.html

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

总结:如果数据库不需要事务，以及不需要支持并发的话，主要是查询业务的话，MyISAM要快很多，反之，则只能用Innodb引擎

4、索引

合理的设计自己的数据库表和索引可以大大提高数据的检索速度，如果在大表中滥用索引反而会影响你的数据库性能！

4.1索引是什么？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。上述SQL语句，在没有索引的情况下，数据库会遍历全部200万条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”，那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢？加上索引后查询数据会直接在索引中定位。（注：一般数据库默认都会为主键生成索引）。

4.2 索引类型

4.2.1、普通索引

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

创建方法：

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

4.2.2、唯一索引

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

创建方法

CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName 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`),
	UNIQUE indexName (title(length))

4.2.3、 全文索引（FULLTEXT）

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

4.2.4、 主键索引

数据记录里面不能有 null,数据内容不能重复，在一张表里面不能有多个主键索引，一般是在创建表的时候自动生成。

创建方法

CREATE FULLTEXT INDEX index_content ON article(content)
ALTER TABLE article ADD FULLTEXT index_content(content)

也可以在创建表的时候创建
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)

4.2.5、组合索引

看了很多文章，对组合索引有大致的概念，比如一个sql语句是where a=1 and b=2,这时候建立组合索引create index a_index_b on t(a, b);的效果优于单独建立索引，但如果and换成or，就必须建立单独索引，总而言之，组合索引一般适用于特定的sql语句，这里就不多说了，具体大家可以查资料。

总结:上面列举了常用的索引，但是索引不能乱用，因为索引都是写在I/o文件中的，读取速度比较慢，所以过多的索引反而会导致插入、更新、删除的速度变得很慢，因为插入、删除和更新的时候需要写入索引。

5、索引的使用场景和优化方案

5.1、使用场景

第一：肯定是表的数据量比较大，至少是五位数以上的数据量
第二：MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引，所以如果是其他场景，你建立了索引也没有用的
第三：查找符合where条件的记录
第四：进行sort或group操作时

某些时候的LIKE才会使用索引，原因是以通配符%和_开头作查询时，MySQL不会使用索引。例如
SELECT * FROM mytable WHERE username like’admin%’

5.2、优化方案

https://wwwblogs/zhaoguan_wang/p/4604025.html
这篇文章写的不错，大家参考一下这篇文章吧。

还有这篇文章，我觉得不错，主要写了mysql索引底层实现原理，感兴趣可以看看。
https://wwwblogs/boothsun/p/8970952.html

总结:花了两三天研究这玩意，说实话，理解的一般，文章仅供参考，可能有错误的地方。
后面如果有更深的感悟，会补充一下的。

本文标签： 索引场景差异常用类型