MYSQL简介和存储引擎的选择

最新推荐文章于 2024-07-23 10:17:04 发布

wab5168

最新推荐文章于 2024-07-23 10:17:04 发布

阅读量1.5k

点赞数

分类专栏：数据库文章标签： mysql 存储引擎 MYISAM INNODB

本文链接：https://blog.csdn.net/wab5168/article/details/49852275

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MYSQL简介和存储引擎的选择

1.MYSQL

MYSQL本质上是一种KEY-VALUE结构的关系型数据，不过这么讲可能比较笼统，在这里用MYSQL逻辑架构图来做一下详细的说明。

(1) 客户端

MYSQL同大多数服务一样，拥有客户端和服务端，客户端在MYSQL工作的时候主要负责连接处理，授权认证，安全等等。每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询都只会在这个单独的线程中执行，并且该线程只能在某个CPU核心或者CPU中运行，因此服务端不需要为每一个客户端连接创建或者销毁线程。

(2) 中间层

这部分是MYSQL的核心，大部分核心MYSQL的核心服务包括查询解析，SQL语句的分析、优化、语句的索引和缓存以及所有的内置函数(md5,date,二目运算符等)和所有跨存储引擎的功能都在这一层实现：存储过程、触发器、MYSQL视图等。

(3)存储引擎

第三层主要是MYSQL的存储引擎，MYSQL的存储引擎主要负责在MYSQL中的数据操作，包括存储和提取。MYSQL拥有很多存储引擎，每个存储引擎都有其相应的优点缺点以及应用场景，服务器通过API与不同的存储引擎进行通讯，这些接口屏蔽了不同存储引擎的差异，存储引擎API包含了十几个底层函数，用于执行“事务”、“查询”等操作，不同的存储引擎由于其调用API的机制不会相互通讯，只是作为接口响应上层服务的请求。

2.MYSQL各存储引擎的区别

MYSQL的存储引擎有很多，不过一般情况下常用的有三种，MyISAM、InnoDB和MEMORY引擎，不过由于目前MEMCACHE、REDIS等非关系型缓存的大量使用，在我接触到的业务中MEMORY引擎已经很少在正式的生产环境中进行使用了，所以下面我主要对MyISAM和InnoDB这两种存储引擎的特点和区别进行说明。

(1) MyISAM

首先是MyISAM引擎,其有着十分悠久的历史，在MYSQL5.1之前，MyISAM是作为MYSQL的官方存储引擎而存在。MyISAM的特点是其表的存储是通过3个文件分别进行存储的，分别是.frm文件、.MYD文件和.MYI文件，其中.frm文件是描述表结构的文件,.MYD文件是表数据文件，.MYI文件存储是表数据文件中任何索引的数据树，说到这里不得不提一下，MYSQL索引的结构一般是B树，并且不同存储引擎的索引结构并非是相同的，这块我们会在后面的内容中进行详细说明，其特点如下。

1.MyISAM因为上面的文件结构是完全分离的，所以其具有很好的可迁移性，直接复制数据文件到新的数据库中即可完成迁移，并且数据以紧密格式存储，还可以使用myisampack进行压缩，压缩后能够减少百分之70的存储空间但数据只可读取，其存储大小受限于可用磁盘空间和操作系统对文件最大尺寸的限制。

2.MyISAM支持B树索引和全文索引（InnoDB在5.5版本之后才支持了全文索引），并且在查询方面的效率很高，在一些数据量不是特别巨大的查询业务中有着很高的性能。

3.锁的级别为表所，不支持行锁。

4.不支持事务，不支持外键，不过其实MyISAM是支持事务的，不过因为其无法在崩溃时安全恢复，所以在大多数人的印象中变成了这样。

5.MyISAM会单独保存表的行数，所以当查询count时不需要对表进行扫描，可以直接获取表的行数。

6.MyISAM的BUFFER通过key_buffer_size字段来设置，并且其只会将索引存放在缓冲池中，并不会像InnoDB一样将数据也存储在缓冲池里，所以在MyISAM中配置key_buffer_size时只需要计算索引的大小。

(2) InnoDB

InnoDB引擎是目前MYSQL的默认事务型引擎，也是目前最重要，使用最广泛的存储引擎，它被设计用于大量的短期事务，短期事务要求执行的完成性和准确性，并且很少会被回滚。InnoDB是一个很重要的存储引擎，非常值得我们投入特别精力去学习并且会获得更多的收益。目前我们所使用的InnoDB其实全称是InnoDB plugin，这个最开始是作为一个MYSQL的插件而存在，在MYSQL5.5之前，原生的InnoDB引擎并没有如今的这么好用，但是在之后的岁月里很多个人和公司都对其贡献代码，而不仅仅是其自己的开发团队，在性能，可扩展性，可配置化和各种新特性上都有了很大的提升。这个现代广泛使用的InnoDB 就是我们之前提到的InnoDB plugin在MYSQL5.5后终于由原有的第三方插件替换了旧版的InnoDB引擎，它支持了一些新的特性。例如利用排序创建索引，在删除和增加索引时不需要复制全表数据，新的压缩存储格式以及文件管理等。

     1.首先是最重要的一点，InnoDB支持事务和行锁，这是它同MyISAM最重要的区别。

     2.InnoDB支持外键索引，并且在MYSQL中只有它支持。

     3.InnoDB因为其行锁的特性，更适应对于插入和更新较多的业务场景。

     4.InnoDB支持四种锁的隔离级别，同时使用一种被称成next-key locking的策略来避免换读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。

     5.InnoDB的缓冲池不只缓存了索引文件，还对数据进行了缓存。但是在InnoDB的缓冲池中除了上面的两种数据外，还同时缓存了索引页、数据页、undo页、插入缓存、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。

(3) 特殊说明

除了上面说到的一些不同之处外，还有两处感觉需要详细说明一下。
1.索引和MyISAM和InnoDB索引的区别
     既然说到索引区别，那么首先就应该谈一下索引。MYSQL索引是一个很重要的东西，如果要详细说的话可能一本书也说不完，所以在这里我主要稍微的介绍一下，那么MYSQL的索引是什么呢？打个比方，我们在使用MYSQL用作查询的时候就好比查字典，索引就好比字典的查询页，如果没有索引我们可能要在茫茫多的数据中逐条查询，显而易见这种方式效率很低，如果我们对某一字段加入了索引，就可以直接在这个列上找到该条数据的位置，使查询变得更加高效。那么既然索引这么方便，我们就在所有列上都加上索引不就好了，这么想是没错，但是由于在列上加索引也是需要占据存储空间的，所以并不是说索引越多速度就越快，我们一般会将索引加在查询的条件上，例如 select xxxx from xxx  where uid = 'xxx'，在这个查询语句上我们只需要在UID列加上索引就可以了。不过这样并不是最节省空间的做法，因为索引还可以设置索引长度，这样我们还拿上面语句举例，现在有这样一个应用场景，在学校里我们的学号通常是2015xxxx,后面几位为班级学号等，假如你需要查询2015年入学同学的信息，实际使用到索引的地方只是2015这前4个数字，这样的话将索引长度设置为4是比较好的，因为判断不同时间入学的同学只需要判断前4位数字，如果说前四位的代号种类特别多并且数据库的数据量很大，那么在某些业务下，通过设置指定指定长度的索引可以节省大量的空间。
    下面再说一下MyISAM和InnoDB索引的详细区别，首先是结构区别。
    MyISAM和InnoDB都使用了B树结构作为索引，初此之外MyISAM还支持全文索引，但是InnoDB在MYSQL5.6之后也加入了对全文索引的使用，MyISAM还支持R树索引。在这里主要介绍一下B树索引，InnoDB引擎使用的B-树索引结构如下图。
                           B-Tree索引能够加快访问数据的速度，因为就同二叉树一样，使用B树索引后，它不需要通过全表扫描来获取到其需要数据的位置，仅仅需要从索引的根节点开始进行树状搜索，存储引擎通过根节点中的指针指向下一层的接到，这些指针实际上定义了子节点页中值的上线和下限，通过这种方式来尽量减少不必要的操作，提高效率。但是每个存储引擎可能会以不同的方式使用B-Tree索引，例如MyISAM使用了前缀压缩技术使得索引更小，并且MyISAM索引通过数据的物理位置引用被索引的行，但InnoDB则按照原数据格式进行存储，同时根据主键引用被索引的行。在索引的结构上，MyISAM不支持聚簇索引而InnoDB会在表的主键上默认添加聚簇索引，聚簇索引具有更好的查询性能和对比非聚簇索引更小的体积，但是由于文件结构的关系在每个表中只能存在一个聚簇索引。
2.存储引擎的缓存
    在上面的介绍中已经简略的介绍了MyISAM和InnoDB引擎缓存的区别，不过在MYSQL中缓存的应用和其运行机制是十分复杂的，笔者也只是对InnoDB的缓存机智略有了解，所以在这里主要针对InnoDB引擎缓存进行一下说明，欢迎补充和拍砖。
    在MYSQL中，我们主要使用三种缓存，日志缓存，查询缓存和缓冲池，其中InnoDB引擎还拥有其特有的插入缓存。其中缓冲池在MyISAM和InnoDB中有着不同的功能，前者只将索引进行存储，而后者除了索引外，还会存储数据页，undo页等数据。在InnoDB引擎中，缓冲池是使用内存最多的部分，在MYSQL运行的过程中，InnoDB存储引擎会将数据库文件按页(每页16K)读取到缓冲池，然后通过LRU算法来保存缓冲池中的数据，如果产生数据修改的条件，其会优先修改在缓冲池中的页，然后再将脏页刷新到文件上。在这里脏页的刷新也有着相应的策略，因为如果每条数据都刷新一次的话会产生很多不必要的操作，InnoDB采用了设置一个checkpoint的点，这个点可以通过相应的参数进行配置，这个参数会优先将最老的百分之xx的脏页进行写入。
    最后，InnoDB存储引擎设计了独有的插入缓存，对于非聚簇索引的插入和更新操作，并不是每次都直接插入，耳塞先判断插入的非聚簇索引页是否在缓冲池中，如果不在，则先补充缓冲池中的数据，然后再进行合并插入，否则直接进行插入，通过这种方法大大提高了对飞机场索引的插入和修改的性能，同时在可靠性方面，通过二次写和四种隔离级别，也尽可能的得到了保证。