浅谈mysql知识点(一)

1、mysql的引擎

----------》参考文档《----------

说到mysql，我们不得不谈一谈mysql的引擎，这个是绕不开的话题。

我的mysql数据库版本是5.7版本的，目前大多数企业使用的并不是最新版的8.X，而是之前很流行的5.7版本，因为数据库的数据迁移是一件很麻烦的事情，动辄伤筋动骨，所以现在虽然mysql出了8版本的，但是很多企业还采用5.7版本，我们就按照这个版本学习。

首先，先来看一下mysql的引擎：

show engines;

是不是惊了一下，怎么这么多？哈哈，没关系，其实我们平时常用的顶多也就是四种，其他的引擎还是看需求再了解吧，接下来我们学一下最常用的四种引擎。

1.1、memory

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型，例如t_memory.frm。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。对MEMORY引擎表做DELETE删除数据的操作，并不是真正删除，而是标记为删除状态，只有新记录INSERT且写入同一张表才可重用，另外重建、重置、删除表、mysqld服务重启，才会释放掉被删除数据所占的内存。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

1.2、archive

每个archive表在磁盘上存在两个文件.frm(存储表定义)和.arz(存储数据)，这个存储引擎基本上用于数据归档。它的压缩比非常的高，存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合，由于它不支持索引同时也不能缓存索引和数据，所以它不适合作为并发访问表的存储引擎。Archivec存储引擎使用行锁来实现高并发插入操作，但是它不支持事务，其设计目标只是提供高速的插入和压缩功能。

所以，一般来说，我们经常用来存储插入远多于读的业务，比如说超市的收银历史记录，留着无太大用，丢掉又可惜，可以这样压缩存储；还有操作日志；等等。

1.3、myisam

首先来讲讲myisam的前身——ISAM.

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

MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。

MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。

1.4、InnoDB

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

而且，这个引擎也是目前mysql默认的引擎，如果我们建立一个数据库而没有指定引擎时，默认创建的为innoDB引擎的数据库，应用广泛。

1.5、引擎的选择

对于每个引擎，各有各的长处，所以我们要根据自己的需求，选择应用的引擎。

2、mysql的索引

2.1、索引的类型

----------》参考文档《----------

说起来索引，我们就需要谈一下索引的种类，根据数据存储的顺序和索引顺序将索引分为两类，一种叫聚簇索引，一种叫非聚簇索引。

2.1.1、聚簇索引

是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致，且一个表只能有一个聚簇索引，因为物理存储只能有一个顺序。主键索引一般都是聚簇索引。

因为innoDB和MyISAM的数据本来就是按照b+树结构进行按照主键存储，而索引一般是b+树结构的，所以按照主键构建的一棵索引树就和原来数据存储的结构一样，一般我们就可以称主键索引就是聚簇索引，而且因为数据存储结构只有一种，所以一个数据库表只有一个聚簇索引。

2.1.2、非聚簇索引

表数据存储顺序与索引顺序无关。对于非聚簇索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

一般来说，我们创建的普通索引都是非聚簇索引。

2.2、mysql的索引种类

2.2.1、主键索引

根据数据的主键创建的索引，因为一张表只能有一个主键（组合主键也只是一个主键，组合主键的成员单独只是普通的列，组合起来才能算是主键），所以一张表只能有一个主键索引，一般来说，创建表的时候默认创建主键索引。

2.2.2、普通索引

这就是一个最为普通的索引，没有任何条件上的限制，是我们大多数情况下用到的索引，一般来说，我们对于普通的列创建的索引都是普通索引。