Home Database Mysql Tutorial MySQL存储引擎简介_MySQL

MySQL存储引擎简介_MySQL

Jun 01, 2016 pm 01:13 PM
memory

MySQL使用的是插件式存储引擎。

主要包括存储引擎有:MyISAM,Innodb,NDB Cluster,Maria,Falcon,Memory,Archive,Merge,Federated。

其中最为广泛的是MyISAM 和Innodb两种存储引擎,所以接下来对它们做简单介绍。

MyISAM 存储引擎简介

MyISAM 存储引擎的表存储在数据库中,每一个表都被存放为三个以表名命名的物理文件。

1、(.frm文件)任何存储引擎都不可缺少的存放表结构定义信息的文件

2、(.MYD文件)存放表数据的文件

3、(.MYI文件)存放该表所有索引数据的文件

注:有且仅有这样三个文件做为MyISAM 存储类型的表的存储

MyISAM 的表以不同的格式存放在一个相同后缀名的.MYD 文件中。

其中格式分为:静态(FIXED)固定长度、动态(DYNAMIC)可变长度以及压缩(COMPRESSED)三种格式。

压缩:可以创建表的时候通过ROW_FORMAT 来指定{COMPRESSED | DEFAULT}(默认不压缩)

也可以通过myisampack 工具来进行压缩

非压缩:

动态:只要表中有可变长度类型的字段存在,那么该表就肯定是DYNAMIC 格式的

静态:如果没有任何可变长度的字段,则为FIXED 格式

MyISAM 支持以下三种类型的索引:

1、B-Tree 索引

B-Tree 索引,所有的索引节点都按照balance tree 的数据结构来存储,所有的索引数据节点都在叶节点。

2、R-Tree 索引

R-Tree 索引的存储方式和b-tree 索引有一些区别,主要设计用于为存储空间和多维数据的字段做索引,支持geometry 类型的字段作索引。

3、Full-text 索引

Full-text 索引就是我们长说的全文索引,他的存储结构也是b-tree。主要是为了解决在我们需要用like 查询的低效问题。

注:参与一个索引的所有字段的长度之和不能超过1000 字节

Innodb 存储引擎简介
1、支持事务安装

实现了SQL92 标准所定义的所有四个级别(READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ 和SERIALIZABLE)

2、数据多版本读取

Innodb 在事务支持的同时,为了保证数据的一致性已经并发时候的性能,通过对undo信息,实现了数据的多版本读取。

3、锁定机制的改进

Innodb 改变了MyISAM 的锁机制,实现了行锁。行锁机制的实现是通过索引来完成的。

4、实现外键

建议不要使用外键,数据库系统调优专家都建议不要使用

5、Innodb 的物理结构

跟MyISAM一样:(.frm 文件)来存放表结构定义相关的元数据。

跟MyISAM不一样:表数据和索引数据是存放在一起的

Innodb 的物理结构分为两大部分:

1、数据文件(表数据和索引数据)

存放数据表中的数据和所有的索引数据,包括主键和其他普通索引。

Innodb 的表空间分为两种形式:

一种是共享表空间,也就是所有表和索引数据被存放在同一个表空间(一个或多个数据文件)中,通过innodb_data_file_path 来指定,增加数据文件需要停机重启。

另外一种是独享表空间,也就是每个表的数据和索引被存放在一个单独的.ibd 文件中。

注:共享表空间是必须存在的,因为Innodb 的undo 信息和其他一些元数据信息都是存放在共享表空间里面的。

共享表空间的数据文件是可以设置为固定大小和可自动扩展大小两种形式,自动扩展形式的文件可以设置文件的最大大小和每次扩展量。

2、日志文件

Innodb 的日志文件和Oracle 的redo 日志比较类似:

可以设置多个日志组(最少2个),采用轮循策略来顺序的写入,甚至在老版本中还有和Oracle 一样的日志归档特性。

注:千万别全部删除innodb 的日志文件,因为很可能就会让你的数据库crash,无法启动,或者是丢失数据。

Innodb 完全可以通过redo 日志将数据库Crash 时刻已经完成但还没有来得及将数据写入磁盘的事务恢复,也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。

Innodb 的所有参数基本上都带有前缀“innodb_”,可以仅仅通过参数(skip-innodb)来屏蔽MySQL 中的Innodb存储引擎,这样即使我们在安装编译的时候将Innodb 存储引擎安装进去了,使用者也无法创建Innodb 的表。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

CAMM2 for desktop PCs: MSI explains the benefits of the new RAM standard for gaming towers CAMM2 for desktop PCs: MSI explains the benefits of the new RAM standard for gaming towers Aug 17, 2024 pm 06:47 PM

The first LPCAMM2 modules for laptops are already being delivered, and desktop mainboards are also expected to be equipped with CAMM2 in future. CAMM2 and LPCAMM2 are not compatible with each other, and even on desktop PCs, customers need to be caref

MySQL storage engine selection comparison: InnoDB, MyISAM and Memory performance index evaluation MySQL storage engine selection comparison: InnoDB, MyISAM and Memory performance index evaluation Jul 26, 2023 am 11:25 AM

MySQL storage engine selection comparison: InnoDB, MyISAM and Memory performance index evaluation Introduction: In the MySQL database, the choice of storage engine plays a vital role in system performance and data integrity. MySQL provides a variety of storage engines, the most commonly used engines include InnoDB, MyISAM and Memory. This article will evaluate the performance indicators of these three storage engines and compare them through code examples. 1. InnoDB engine InnoDB is My

Blue screen problem caused by win10 memory management Blue screen problem caused by win10 memory management Dec 30, 2023 pm 07:11 PM

The win10 system is an excellent system that is worth using. Its strong compatibility and high intelligence can ensure that there will be basically no problems in the use of the win10 system. However, recently, many friends have reported that their computers frequently have blue disks. And it always prompts the error code memorymanagement. What's going on? Today, the editor will bring you the solution to the frequent blue screen of win10 and the memory management termination code. If you need a game, come and take a look. Solution to win10memorymanagement blue screen: Solution 1: 1. Use "Win key + R" + enter "control + enter" to enter the control surface

Solution to PHP Fatal error: Allowed memory size of bytes exhausted Solution to PHP Fatal error: Allowed memory size of bytes exhausted Jun 22, 2023 am 08:13 AM

PHP is a widely used server-side scripting language, but when running PHP applications, you may encounter the error message 'PHPFatalerror: Allowedmemorysizeofbytesexhausted', which usually means memory allocation problems. In this article, we will explore some common causes of ‘PHPFatalerror:Allowedmemorysizeofbytese

What are the characteristics of the Memory storage engine in mysql? What are the characteristics of the Memory storage engine in mysql? Jun 02, 2023 pm 08:48 PM

1. Each table of the Memory table can have up to 32 indexes. 16 columns per index, and a key length of 500 bytes. 2. The storage engine performs HASH and BTREE micronization. 3. There can be non-unique key values ​​in the table. 4. The table adopts a fixed record length format. 5. BLOB or TEXT columns are not supported. Example mysql>CREATETABLElookup(idINT,INDEXUSINGHASH(id))ENGINE=MEMORY;mysql>CREATETABLElookup(idINT,INDEXUSINGBTREE(id))ENGINE=MEMORY;

SK hynix announces \'industry's best\' GDDR7 DRAM, claiming 60% higher speeds than predecessor at 32Gbps SK hynix announces \'industry's best\' GDDR7 DRAM, claiming 60% higher speeds than predecessor at 32Gbps Jul 30, 2024 pm 06:38 PM

South Korean memory maker SK hynix Inc. has announced its latest graphics memory, GDDR7, which claims to be industry-leading. This news comes almost two weeks after Samsung announced the industry's first GDDR7 DRAM solution on July 19th with 1.5 TBps

Raspberry Pi: Users can now get a big performance boost with just 100 lines of code Raspberry Pi: Users can now get a big performance boost with just 100 lines of code Jun 28, 2024 pm 04:16 PM

The Raspberry Pi 5 is a very versatile single-board computer, mainly because of its extensive connectivity, and the significantly increased computing capacity compared to previous generations also plays a role. In the field of IT, it is not unusual f

Samsung\'s 8-layer HBM3E chips overcome heat and power hurdles to secure Nvidia\'s approval Samsung\'s 8-layer HBM3E chips overcome heat and power hurdles to secure Nvidia\'s approval Aug 07, 2024 pm 07:20 PM

Samsung Electronics has successfully passed Nvidia's rigorous testing for its 8-layer HBM3E memory chips, positioning itself as a key supplier for the rapidly growing AI chip industry. HBM, or High Bandwidth Memory, is a specialized type of DRAM desi

See all articles