开发者都需要了解的mysql索引四大存储引擎
MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。
接着让我们来说一下其中比较常见的四大存储引擎吧。
InnoDB
InnoDB
是MySQL
(MySQL5.5
以后)的默认存储引擎,支持事务、行级锁和外键,被用来处理大量短期事务。如果使用到外键、需求并发程度较高、数据一致性要求较高的话,那么通常选择InnoDB
引擎,这也是互联网大厂使用InnoDB
存储引擎的原因。除非有非常特别的原因需要使用其他的存储引擎,否则建议优先考虑InnoDB
。但是对比MyISAM
,InnoDB
写的处理效率会差一些,并且会占用更多的磁盘空间以保留数据和索引。

MyISAM
MyISAM
提供了大量的特性,包含全文索引、压缩、空间行数等,支持3种不同的存储格式,分别是:静态表、动态表、压缩表。
静态表:表中的字段都是非变长字段,这样每个记录都是固定长度的。优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多(因为存储时会按照列的宽度定义补足空格),在取数据的时候,默认会把字段后面的空格去掉,如果不注意会把数据本身带的空格也会忽略。
动态表:表中的字段都是变长字段,记录不是固定长度的。这样存储的优点是占用的空间相对较少;缺点是频繁的更新、删除数据容易产生碎片,需要定期执行OPTIMIZE TABLE
或者myisamchk -r
命令来改善性能,并且出现故障的时候恢复相对比较困难。
压缩表:压缩表由myisamchk工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。
在MyISAM
中,数据文件和索引文件可以放置在不同的目录(在创建表的时候通过DATA DIRECTORY
和INDEX DIRECTORY
语句指定文件的绝对路径),平均分配IO,获取更快的访问速度。但是MyISAM
不支持事务,不支持外键,也不支持行级锁,支持表级锁,有个缺陷就是崩溃后无法恢复。如果应用程序以检索为主,只有少量的插入、更新和删除操作,并且对事物的完整性、并发程度不是很高的话,通常建议选择MyISAM
存储引擎。
Memory
Memory
存储引擎使用存在内存中的内容来创建表,所以它的访问速度非常快,并且默认使用HASH
索引。但是一旦服务器关闭或者mysqld
守护进程崩溃时,所有的Memory
数据都会丢失,但表还会继续存在,获得速度的同时也带来了一些缺陷。
它要求存储在Memory
数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOB
和TEXT
这样的长度可变的数据类型,VARCHAR
是一种长度可变的类型,但因为它在MySQL
内部当做长度固定不变的CHAR
类型,所以可以使用。
服务器需要足够的内存来维持在同一时间内使用的MEMORY
表,当不再使用MEMORY
表时,要释放MEMORY
表所占用的内存,应该执行DELETE FROM
或truncate table
或者删除整个表。每个MEMORY
表中放置的数据量的大小,受到max_heap_table_size
系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY
表时可以使用MAX_ROWS
子句来指定表中的最大行数。它通常用于更新不太频繁的小表。
Merge
Merge
存储引擎是一组MyISAM
表的组合,这些MyISAM
表结构必须完全相同,Merge
表本身没有数据,对Merge
类型的表进行查询、更新、删除的操作,实际上是对内部的MyISAM
表进行的。Merge表在磁盘上保留两个文件,一个是.frm
文件存储表定义、一个是.MRG
文件存储Merge
表的组成等。MERGE
表的优点在于可以突破对单个MyISAM
表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE
表的访问效率。
我们可以通过show create table 表名
命令来查看表使用的引擎,由以下代码可以看出test
表使用的是MyISAM
存储引擎。
CREATE TABLE `test` ( `id` int(1) NOT NULL AUTO_INCREMENT, `name` varchar(8) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8123456复制代码类型:[javascript]
相对其他数据库而言,MySQL
的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM
和MEMORY
存储引擎采用的是表级锁(table-level locking
);BDB
存储引擎(5.1之后就不直接支持了,因为BDB
被oracle
收购了)采用的是页面锁(page-level locking
),但也支持表级锁;InnoDB
存储引擎既支持行级锁(row-level locking
),也支持表级锁,但默认情况下是采用行级锁。