MySQL存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不多的数据库管理系统都支持多种不同的数据引擎。

MySQL8.0之后默认支持的存储引擎有九种。

可以使用SHOW ENGINES语句查看系统支持的引擎类型，结果如下：

常见的几种存储引擎

下面是常用的三种存储引擎对比：

功能	InnoDB	MyISAM	Memory
存储限制	64TB	256TB	RAM
支持事务	Yes	No	No
支持全文索引	No	Yes	No
支持树索引	Yes	Yes	Yes
支持哈希索引	No	No	Yes
支持数据缓存	Yes	No	N/A
支持外键	Yes	No	No

1、InnoDB存储引擎

InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5.5.5之后，InnoDB 作为默认的存储引擎，InnoDB 主要特性有：

支持事务
灾难恢复性好
为处理巨大数据量的最大性能设计
实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
支持外键完整性约束。存储表中的数据时，每张表的存储都按逐渐顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID,并以此作为主键。
被用在众多需要高性能的大型数据库站点上

2、MyISAM存储引擎

MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在 MySQL5.5.5 之前的版本中，MyISAM 是默认的存储引擎。MyISAM 主要特性有：

不支持事务
使用表级锁，并发性差
主机宕机后，MyISAM表易损坏，灾难恢复性不佳
可以配合锁，实现操作系统下的复制备份、迁移
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
可以把数据文件和索引文件放在不同目录

使用 MyISAM 引擎创建数据库，将产生3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm 文件存储表的定义数据，存放表具体记录的数据文件的扩展名为 .MYD（MYData），存储索引文件的扩展名是 .MYI（MYIndex）。

3、Memory存储引擎

MEMORY 存储引擎将表中的数据存储在内存中，为查询和引用其他表数据提供快速访问。MEMORY 主要特性有：

使用表级锁，虽然内存访问快，但如果频繁的读写，表级锁会成为瓶颈
只支持固定大小的行。Varchar类型的字段会存储为固定长度的Char类型，浪费空间
不支持TEXT、BLOB字段。当有些查询需要使用到临时表（使用的也是MEMORY存储引擎）时，如果表中有TEXT、BLOB字段，那么会转换为基于磁盘的MyISAM表，严重降低性能
由于内存资源成本昂贵，一般不建议设置过大的内存表，如果内存表满了，可通过清除数据或调整内存表参数来避免报错
服务器重启后数据会丢失，复制维护时需要小心

MySQL事务隔离级别和实现原理

什么是MySQL事务

MySQL 事务都是指在 InnoDB 引擎下，MyISAM 引擎是不支持事务的。

数据库事务指的是一组数据操作，事务内的操作要么就是全部成功，要么就是全部失败，什么都不做，其实不是没做，是可能做了一部分但是只要有一步失败，就要回滚所有操作，有点一不做二不休的意思。

事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）四个特性，简称 ACID，缺一不可。

概念说明

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读：幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

四种隔离级别：

读未提交（READ UNCOMMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

读提交解决了脏读问题，行锁解决了并发更新的问题。并且 MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

MySQL索引

什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

模糊查询不会使用索引，嵌套查询效率最高。

从物理存储角度分为聚簇索引、非聚簇索引、二级索引（辅助索引）

聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快，以MyISAM为存储引擎的表不存在聚簇索引。

二级索引的叶子节点并不存储一行完整的表数据，而是存储了聚簇索引所在列的值。

回表查询：由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到聚簇索引列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据。

通过二级索引查询时，回表不是必须的过程，当SELECT的所有字段在单个二级索引中都能够找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。可以用Explain命令查看SQL语句的执行计划，执行计划的Extra字段中若出现Using index，表示查询触发了索引覆盖。

从数据结构角度分为Hash索引、B+Tree索引、Full-Text索引

（1）hash索引：hash索引顾名思义基于hash表实现，hash索引中存储的就是hash码，存储引擎会为hash索引中的每一列都计算一个hash码

（2）B+Tree索引：InnoDB和MyISAM存储引擎默认使用的是B+Tree索引，Mermory搜索引擎默认使用的hash索引

按字段特性分类可分为：主键索引、普通索引、前缀索引

按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

索引的类型

1.普通索引（Normal Index）

最基本的索引，它没有任何限制。

2.唯一索引（Unique Index）

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3.主键索引（Primary Key Index）

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引。

4.组合索引（Composite Index）

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

5.全文索引（Fulltext Index）

在文本列上创建全文索引，能够对文本进行高效、全面的搜索。

6.空间索引（Spatial Index）

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。

创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。空间索引一般是用不到了，了解即可。

7.自定义函数索引（Function-Based Index）

使用用户自定义函数对列进行处理后，创建的索引。

创建索引的三种方式

第一种方式：在执行CREATE时添加索引

CREATE TABLE tableName(  
  id INT NOT NULL,   
  columnName  columnType,
  INDEX [indexName] (columnName(length))  
);

第二种方式：使用ALTER TABLE命令去增加索印

1	ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER
TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

1	CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可对表增加普通索引或 UNIQUE 索引。（但是，不能创建PRIMARY KEY索引)。

Mysql