Mysql存储引擎和索引

最新推荐文章于 2023-04-23 18:43:15 发布

lxxxxxt

最新推荐文章于 2023-04-23 18:43:15 发布

阅读量156

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/qq_36281031/article/details/104762127

版权

Mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1 存储引擎

MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，即MySQL数据库提供了多种存储引擎。
MySQL数据库在实际的工作中分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
在 MySQL 中，不需要在整个服务器中使用同一种存储引擎。一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求，使用合适的存储引擎将会提高整个数据库的性能。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。（可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎）
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。
修改默认存储引擎：SET default_storage_engine=< 存储引擎名 >，但是再次重启客户端时，默认存储引擎仍然是 InnoDB。

InnoDB

存储数据和索引有共享表空间存储和独占表空间存储两种方式。
- 共享表空间：每一个数据库的所有表的数据、索引都保存在一个文件中；
- 独立表空间：每一张表都有自己独立的表空间，表的结构依然在.frm文件中，还有一个后缀为.ibd的文件，保存了这张表的数据和索引。
支持事务，默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
支持外键。
锁粒度为行级锁，支持更高的并发。相对MyISAM也更易发生死锁。
存在缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度。
InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上。
灾难恢复性好。
配合一些热备工具可以支持在线热备份。

MyISAM

.frm 存储表定义；.MYD 存储数据；.MYI 存储索引（但仅保存记录所在页的指针，索引的结构是B+树结构）。
不支持事务和外键。
使用表级锁，并发度低。
缓存索引，不缓存真实对象。
支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复。
二者对比：
- 由于锁粒度的不同，InnoDB比MyISAM支持更高的并发；但是InnoDB相对于MyISAM来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大。
- 查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据。
- SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要逐行去统计行数。所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；

MEMORY

为了提高数据的访问速度，将数据存在内存中，和市场上的Redis，memcached等思想类似。默认使用HASH索引。
支持的数据类型有限制，比如：不支持TEXT和BLOB类型；对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；
如果查询时用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低。

CSV

其数据格式为.csv格式的文本，可以直接编辑保存，所有列的字段都不能为null，不支持索引，不能有主键。
导入导出比较方便，可以将某个表中的数据直接导出为csv，适用Excel办公软件打开。

MERGE

Merge表是一组MyISAM表的组合，这些myisam表的结构必须完全相同，MERGE表本身并没有数据，对它的操作实际上是对内部MYISAM表的操作。
MERGE表在磁盘上保留两个文件，.frm 存储表定义，.mrg 存储组合表的信息。
适用于将一系列MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。

Feferated

本地只保存数据库结构和连接信息，数据保存在远程的服务器中。
默认不是开启的引擎。

存储引擎选择原则

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 insert 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

2 索引

目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。
MySQL中InnoDB和MyISAM引擎都使用B+Tree作为索引结构，但实现方式不同。

B+树

一种多路平衡查找树。
对于M阶的B+树：

根节点至少有两个子树(即该节点至少有2个关键字);
除根之外的所有非终端节点的子树范围 [m/2向上取整，m]
所有叶子节点在同一层，且包含所有关键字信息以及指向这些关键字记录的指针，叶子结点本身按关键字从小到大顺序链接。
所有非终端节点可以看成是索引部分，节点中仅含其子树中最大/最小的关键字。
B+树只有叶子节点才存数据，因此内部节点不需要指向关键字具体信息的指针，节省空间，使得容纳更多节点元素。因此数据量相同时，B+树比B树更加矮胖，IO次数减少。
B+树比B树查找性能更稳定，B+树最终一定会查找到叶子节点，而B树最好情况是只查根节点，最坏情况是查到叶子节点。
对于范围查询（遍历），比如查找1~5的数据，B+树只需在叶节点的链表中遍历即可，而B树需要中序遍历。
为什么使用B+树：一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
B+树中一次检索最多需要树的高度h-1次I/O（根节点常驻内存），所以渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

InnoDB

“聚集索引”，InnoDB 的数据文件本身就是索引文件（MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址）。树的叶点data域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。
InnoDB 要求表必须有主键(MyISAM 可以没有)，如果没有显式指定，则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，类型为长整形。

尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
辅助索引： data域存储的是相应记录的主键的值。所以辅助索引搜索需要检索两遍索引（回表）：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
覆盖索引：一个索引包含所有要查询的字段的值，即指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也是不需要回表的。不是所有的非主键索引都要回表。

例如，select 后面恰好就是主键，那么索引的data字段存的主键就可以直接用。
MySQL加载索引是以磁盘块（页）为单位的，并不是一次性全部加载到内存中，每次只需要加载需要的那一块。一般来说高度为3的B+树就可以存储千万级别的数据，所以一般只需要3次IO就可以得到数据。

MyISAM

也叫做“非聚集索引”，B+树中叶结点的data域存放的是数据记录的地址（指向数据记录的指针）。
还可以按其他关键字建立辅助索引（主索引要求key唯一，辅助索引key可以重复）。
MyISAM 中索引检索的算法为：首先按照 B+Tree 搜索算法搜索索引,如果指定的 key 存在,则取出其data域的值,然后按照data域中的地址,读取相应数据记录。