The difference between storage engines in MySQL: Taking Innodb and myisam as an example, the former supports transactions but the latter does not; the former emphasizes versatility and supports more extended functions, while the latter mainly focuses on performance; the former does not support full-text indexing , and the latter supports full-text indexing, etc.
mysql supports several storage engines. Here we mainly discuss several commonly used storage engines. Innodb, myisam
INNODB
INNODB index implementation
The same thing as MyISAM is that InnoDB also uses B Tree. A data structure to implement B-Tree index. The big difference is that the InnoDB storage engine uses the "clustered index" data storage method to implement the B-Tree index. The so-called "aggregation" means that the data rows and adjacent key values are compactly stored together. Note that InnoDB can only The records of a leaf page (16K) are aggregated (that is, the clustered index satisfies a certain range of records), so records containing adjacent key values may be far apart.
In InnoDB, the table is called an index organized table. InnoDB constructs a B Tree according to the primary key (if there is no primary key, a unique and non-empty index will be selected instead. If there is no primary key, a unique and non-empty index will be selected instead. For such an index, InnoDB will implicitly define a primary key as a clustered index), and at the same time, the leaf pages store the row record data of the entire table. The leaf nodes of the clustered index can also be called data pages. Non-leaf pages can be viewed Do a sparse index of leaf pages.
The following figure illustrates the implementation of InnoDB clustered index, and also reflects the structure of an innoDB table. It can be seen that in InnoDB, the primary key index and data are integrated and not separated.
#This implementation method gives InnoDB ultra-high performance in retrieval by primary key. You can choose a clustered index purposefully, for example, in a mail table, you can choose user ID to aggregate data. In this way, you only need to read a small number of consecutive data pages from the disk to obtain all the mails of a user with a certain ID, avoiding the need for Random I/O spent reading scattered pages.
InnoDB is an I/O operation. Innodb reads and writes using MVCC to support high concurrency.
Full table scan
When InnoDB does a full table scan, it is not efficient because InnoDB does not actually read sequentially. In most cases, it reads randomly. Pick. When doing a full table scan, InnoDB scans pages and rows in primary key order. This applies to all InnoDB tables, including fragmented tables. If the primary key page table (the page table that stores primary keys and rows) is not fragmented, a full table scan is quite fast because the read order is close to the physical storage order. But when the primary key page is fragmented, the scan will become very slow
Row-level lock
Provides row locking (locking on row level), provided with Oracle Type-consistent non-locking read in SELECTs. In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table. Tables, such as
update table set num=1 where name like “%aaa%”
MYISAM
MyISAM index implementation
Each MyISAM is stored as three files on disk . The name of the first file begins with the name of the table, and the extension indicates the file type. The MyISAM index file [.MYI (MYIndex)] and the data file [.MYD (MYData)] are separated. The index file only saves the pointer (physical location) of the page where the record is located. The page is read through these addresses, and then the page is read. The indexed row. Let’s take a look at the structure diagram first
#The above picture illustrates well that the leaves in the tree save the physical location of the corresponding row. Through this value, the storage engine can smoothly query the table and obtain a complete row of records. At the same time, each leaf page also saves a pointer to the next leaf page. This facilitates range traversal of leaf nodes. As for the secondary index, it is implemented in the MyISAM storage engine in the same way as the above figure. This also shows that MyISAM's indexing method is "non-clustered", which is in contrast to Innodb's "clustered index"
MyISAM will read the index into memory by default and operate directly in memory;
Table-level lock
Summary: Innodb emphasizes versatility and compares the supported expansion functions Many, myisam mainly focuses on performance
Difference
1. InnoDB supports transactions, MyISAM does not support it. For InnoDB, every SQL language is encapsulated into a transaction by default and automatically submitted. , this will affect the speed, so it is best to put multiple SQL statements between begin and commit to form a transaction;
2. InnoDB is a clustered index, and the data file is tied to the index and must be There must be a primary key, and indexing through the primary key is very efficient. However, the auxiliary index requires two queries, first to query the primary key, and then to query the data through the primary key. Therefore, the primary key should not be too large, because if the primary key is too large, other indexes will also be large. MyISAM is a non-clustered index, the data files are separated, and the index saves the pointer of the data file. Primary key indexes and secondary indexes are independent.
3. InnoDB does not save the specific number of rows in the table. When executing select count(*) from table, a full table scan is required. MyISAM uses a variable to save the number of rows in the entire table. When executing the above statement, you only need to read the variable, which is very fast;
4. Innodb does not support full-text index, while MyISAM supports full-text index. In terms of query efficiency, MyISAM is higher;
The above is the detailed content of What are the differences between mysql storage engines?. For more information, please follow other related articles on the PHP Chinese website!

InnoDB是一个将表中的数据存储到磁盘上的存储引擎,所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的,所以需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么?InnoDB采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为16

InnoDB是MySQL的数据库引擎之一,现为MySQL的默认存储引擎,为MySQL AB发布binary的标准之一;InnoDB采用双轨制授权,一个是GPL授权,另一个是专有软件授权。InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID);InnoDB支持行级锁,行级锁可以最大程度的支持并发,行级锁是由存储引擎层实现的。

一、回退重新装mysql为避免再从其他地方导入这个数据的麻烦,先对当前库的数据库文件做了个备份(/var/lib/mysql/位置)。接下来将Perconaserver5.7包进行了卸载,重新安装原先老的5.1.71的包,启动mysql服务,提示Unknown/unsupportedtabletype:innodb,无法正常启动。11050912:04:27InnoDB:Initializingbufferpool,size=384.0M11050912:04:27InnoDB:Complete

1.Mysql的事务隔离级别这四种隔离级别,当存在多个事务并发冲突的时候,可能会出现脏读,不可重复读,幻读的一些问题,而innoDB在可重复读隔离级别模式下解决了幻读的一个问题,2.什么是幻读幻读是指在同一个事务中,前后两次查询相同范围的时候得到的结果不一致如图,第一个事务里面,我们执行一个范围查询,这个时候满足条件的数据只有一条,而在第二个事务里面,它插入一行数据并且进行了提交,接着第一个事务再去查询的时候,得到的结果比第一次查询的结果多出来一条数据,注意第一个事务的第一次和第二次查询,都在同

MySQL储存引擎选型对比:InnoDB、MyISAM与Memory性能指标评估引言:在MySQL数据库中,储存引擎的选择对于系统性能和数据完整性起着至关重要的作用。MySQL提供了多种储存引擎,其中最常用的引擎包括InnoDB、MyISAM和Memory。本文将就这三种储存引擎进行性能指标评估,并通过代码示例进行比较。一、InnoDB引擎InnoDB是My

MySQL是一款广泛使用的数据库管理系统,不同的存储引擎对数据库性能有不同的影响。MyISAM和InnoDB是MySQL中最常用的两种存储引擎,它们的特点各有不同,使用不当可能会影响数据库的性能。本文将介绍如何使用这两种存储引擎来优化MySQL性能。一、MyISAM存储引擎MyISAM是MySQL最常用的存储引擎,它的优点是速度快,存储占用空间小。MyISA

提高MySQL存储引擎读取性能的技巧和策略:MyISAM与InnoDB对比分析引言:MySQL是最常用的开源关系型数据库管理系统之一,主要用于存储和管理大量结构化数据。在应用中,对于数据库的读取性能往往是非常重要的,因为读取操作是大部分应用的主要操作类型。本文将重点讨论如何提高MySQL存储引擎的读取性能,重点对比分析MyISAM和InnoDB这两个常用的存

支持GIS数据的MySQL存储引擎:InnoDB中的空间索引优化摘要:在现代的数据库应用中,地理信息系统(GIS)数据扮演着越来越重要的角色。GIS数据处理是复杂和动态的,传统的关系型数据库并不擅长处理这种类型的数据。然而,MySQL提供了一种存储引擎,即InnoDB,可以优化GIS数据的处理。本文将介绍如何在InnoDB存储引擎上使用空间索引来优化GIS数


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 Linux new version
SublimeText3 Linux latest version

SublimeText3 Chinese version
Chinese version, very easy to use

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Mac version
God-level code editing software (SublimeText3)
