Mysql引擎MyIsam与InnoDB

Mysql引擎MyIsam与InnoDB

存储引擎是第一个表存储、组织数据的一个方式，不同的引擎存储数据的方式不一样。

查看表的引擎

show create table +  表名

mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
在后面可以看到engine = InnoDB

建表的时候可以指定数据表的存储引擎以及字符方式，默认引擎为InnoDB，默认的编码方式为utf8；

查看mysq支持哪些引擎

show engines \G;

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

mysql支持9大引擎，不同版本的mysql对引擎支持情况不一样，8.0只支持8个。

MYISAM存储引擎

它管理的表有以下特征：

​ 使用三个文件表示一张表：

格式文件：存储表结构的定义（mytable.frm）
数据文件：存储表行的内容(mytable.MYD)
索引文件：存储表上索引(mytable.MYI)索引是为了帮助mysql高效获取数据的排好序的数据结构,缩小扫描范围。

对于一张表，只要是主键或者有unique约束的字段，会自动加索引。

MYISAM存储的特点，一个表对应的三个文件，都可以被压缩，节省空间，且当一个表只是读表时，可以转换成只读表来节省空间。

InnoDB

mysql默认的存储引擎，也是一个重量级的引擎

它支持事务，支持数据库崩溃后自动恢复机制，特点是安全。

它管理的表具有一下特征：

每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace（一个逻辑名称）被用于存储表的内容（数据以及索引）
提供一组用来记录事务性活动的日志文件
用commit、savepoint、rollback支持事务处理
多版本的行级锁定
支持外键及引用的完整性，包括级联删除与更新。

以保证数据的安全，效率不是很高，并且文件不能压缩。

MyISAM与InnoDB

首先要知道，每个数据库的文件存储位置，是在磁盘文件上的。每一个数据库对应的是一个文件夹，表对应的是文件夹内的子文件，

如果存在索引的话，.MYD文件存储的是数据元素，MYI存储的是索引。

MyISAM叶子节点存储的是数据的磁盘地址

MyISAM不支持事务。

InnoDB

表数据文件本身就是一个B+tree组织的一个索引结构文件

innodb表叶子节点存储的是完整的数据记录

叶子节点之间，还有一个指针维护着相邻两个叶子节点的地址，也就是说在范围查询时，这个双向指针可以减少查询索引的次数。这样的查询是基于整个字段是有序的。

Hash

mysql并没有放弃hash结构，但是按照hash来排序的话，是不能很好支持范围查找的，它在判断是否相等这种条件查询是效率很高的。

B+TREE

使用自增主键建立索引，因为在写入新数据后，如果增加后的叶子节点长度达到最大长度后，叶子结点就会分裂成两个子节点，而b+树为了维护自己的有序性，会建立冗余索引，此时新建立的索引会影响之前的索引，从而导致树的平衡，影响效率。如果通过自增的方式添加元素，只会在最后一个节点处产生分裂，产生分裂的节点的最小值作为上一级冗余索引的节点，会直接添加到上一级的尾部，从而影响的范围比较小，树平衡的代价比较低，效率高！

联合索引&索引最左前缀原则

​ 当根据多个条件形成索引的时候，首先会根据第一个字段排序建立索引，如果不能根据第一个字段划分开来，就需要借助第二个字段，以此类推，建立起来的完整索引叫做联合索引。

索引最左前缀原则：当我们在查询时，如果直接跳过最左的字段，那么这条查询就不会走索引。因为当跳过最左前缀时，在索引中就找不到数据的位置。相当于我想找第14页第22行第7个字，这时我跳过了页数，说要找第22行和第7个字，这时查找的要求就不明确，无法根据索引查找。