MySQL存储引擎与索引总结

Bobo_
190 阅读8分钟

存储引擎

概述

顾名思义，存储引擎主要便是用于存储数据，存储引擎决定了表的存储方式，不同的存储引擎会有不同的索引方式、存储机制、锁级别等。

InnoDB存储引擎

简介

MySQL从3.23.34a开始包含InnoDB存储引擎，在大于等于5.5版本后默认采用InnoDB引擎：InnoDB是MySQL的默认事务型引擎，被设计来处理大量的短期事务，可确保事务的完整性和回滚，除非有特殊需求，否则一般选用InnoDB引擎（特别是更新、删除操作）

特点

支持事务

崩奔恢复

若服务器由于一些原因崩溃，重启后InnoDB会自动将之前提交的内容定型，撤销没有提交的进程；重启之后继续崩溃点开始执行。

具备外键功能

对比MyISAM

缺陷：InnoDB写处理的效率比MyISAM低，会占用更多的磁盘空间用来保存数据和索引，并且对内存的要求高，内存大小直接影响性能；

优点：MyISAM只缓存索引，InnoDB不仅缓存索引还缓存真实数据；

MyISAM存储引擎

简介

MyISAM是5.5之前版本的默认存储引擎，提供了全文索引、压缩、空间函数（GIS）等特效，但MyISAM不支持事务、行级锁、外键，并且相对InnoDB来说，有个重大的缺陷：崩溃后无法安全恢复数据。

优点

访问速度快：主要应用于对事物完整性没要求 或者以 SELECT、INSERT 为主的应用；

数据统计有额外常数存储：count(*) 查询效率快；

应用的场景

节省资源、消耗少、业务简单

Archive引擎

archive（即归档），仅支持插入（插入速度很快）和查询（查询支持较差）操作（插入后无法修改），使用了zlib压缩库，在记录请求时进行压缩，同样的数据量下，Archive表比MyIASM表要小越75%,比InnoDB表小越83%，经常用来作为仓库使用。【适合用于 日志和数据采集（档案）、存储大量的独立历史记录的数据】

在MySQL5.5以后支持索引功能。

存储数据的文件：以表名开头，扩展名为 .ARZ；

Archive 存储引擎采用行级锁，支持AUTO_INCREMENT 列属性，并且AUTO_INCREMENT列可以具有唯一索引或非唯一索引，在其他列上创建索引会导致错误。

Blackhole引擎

Blackhole引擎没有任何存储机制，对于插入的数据会直接丢弃，但服务器会记录其日志，可用于复制数据到备库或者简单的记录日志（不推荐使用）

CVS引擎

CVS引擎可作为一种数据交换机制：将普通的CVS文件作为MySQL表来处理（不支持索引），存储的数据可直接在操作系统中用文本编辑器、excel读取。

优点：数据的快速导入、导出。

存储文件：以其对应的表名开头，扩展名：.CSV 的纯文本文件；存储的数据会以逗号分隔。

Memory引擎：置于内存的表

概述

Memory引擎的数据存储在内存中，响应速度快，但若是数据库崩溃会导致数据丢失，并且要求存储的数据为长度不变的格式（Blob、Text等类型的数据不能用：长度不固定）

特征

同时支持 Hash索引（默认使用，速度比使用B型树索引快）、B+树索引，若想使用B+树索引，可在创建索引时选用；

Memory表大小受max_rows（创建表时指定） 和 max_heap_table_size（默认16MB，可扩大） 限制；

数据文件与索引文件分开存储：在磁盘中有类型为 frm的文件存储表结构，数据文件存储在内存中，利于数据的快速处理。

使用场景

  1. 目标数据小，需要频繁访问：数据若太大则不适合在内存中存储（内存溢出风险），可通过max_heap_table_size限制表存储极限；

  2. 临时数据、必须立即可用；

3.数据丢失的影响不大

Federated引擎：访问远程表

Federated引擎是访问其他MySQL服务的一个代理，提供了一种跨服务的灵活性，但默认是禁用的（不建议使用）

除此之外，还有 Merger、NDB等引擎.......

索引

概述

索引是存储引擎用于快速查找的一种帮助SQL高效获取数据的数据结构（类似于指针，如书本的目录一样，用于快速寻找对应的内容），避免了全表扫描：一条条数据查找，直到找到目标。

索引是在存储引擎中实现的，存储引擎可以定义每个表的最大索引数和最大索引长度，所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

索引的最主要目的是为了减少磁盘I/O次数，提高查询效率。

分类

MySQL的索引可分为：普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

  1. 普通索引

创建索引时不附加任何限制条件，用于提高查询效率，可创建在任何数据类型中，由字段本身要求决定 是否唯一、非空。建立索引后，可通过索引进行查询。

  1. 唯一性索引

使用UNIQUE参数可以设置为唯一索引，在创建唯一索引时，需限制该索引的值必须是唯一的（允许有空值）。在一张数据表里可以有多个唯一索引。

  1. 主键索引

在唯一索引上加了不为空约束（NOT NULL + UNIQUE）,一张表中只能有一个主键索引。

  1. 单列索引

在表中的单个字段创建索引，只根据该字段进行索引，一张表可以有多个单列索引。 （单列索引可以是普通索引、唯一索引、全文索引）

  1. 多列索引（组合索引、联合索引）

多列索引是在多个字段组合上创建一个索引，可以通过这几个字段进行查询，但查询条件至少要有首个字段（组合字段是有序的），比如在字段id、name、gender上创建一个多列索引idx_id_name_gender，只有在查询时使用了字段id，该索引才会被启动，使用组合索引时遵循最左前缀集合。

  1. 全文索引（适用于大型数据集）

全文索引也称为全文检索，是目前搜索引擎使用的一种关键技术，能够利用 分词技术等多种算法智能分析出文本中的关键词的频率和重要性，然后按一定的算法规则筛选出我们想要的结果。

  • 使用FULLTEX可设置为全文索引，允许索引字段使用重复值/空值，全文索引只能创建在CHAR、VARCHAR、TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型时，全文索引可以提高查询速率。
  1. 空间索引

空间索引只能建立在空间数据类型上，用来提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等，目前只有MyIASM存储引擎支持空间索引且索引的字段不能为空值。

索引的不足

  1. 创建索引和维护索引需要消耗时间，并且随数据的数量增加，消耗的时间也会增加；
  2. 索引需要占用磁盘空间；
  3. 虽然索引提高了查询速度，但同时会降低更新表的速率，当对表中数据进行增、删、改时索引也要动态维护，这样就降低了数据的维护速度。（在插入数据时，最好是先删除表中索引，插入完后再创建索引，以此来提高效率）

不同存储引擎支持的索引类型

  1. InnoDB存储引擎
  • B+Tree索引
  • 全文索引（5.6.4及以后版本）
  • hash索引 （容易发生hash碰撞，不能人为干涉，只能系统使用）
  1. MyISAM存储引擎

(1) MyIASM中常用的索引是B+Tree索引，并且都是 非聚簇类型的（索引和数据分开存储）：

  • 将表中的数据单独存储到一个文件夹中，称之为数据文件，在插入数据时没刻意按照主键大小排序，固不能在这些数据上使用二分法进行查找；
  • MyISAM会单独为表的主键创建一个索引，把索引信息存放到另一个文件中（索引文件），用于索引的叶子节点只存放了 主键+数据记录地址，用于回表操作。

(2)除此之外还支持：全文索引（Full-text）等；

  1. Memory存储引擎：B+Tree，Hash索引等（不支持全文索引）

4.NDB存储引擎：支持Hash索引，不支持B+Tree、Full-text等索引 5. Archive：许多常用索引都不支持。

avatar
文章
阅读
粉丝