MYSQL进阶01

存储引擎

MYSQL默认支持多种存储引擎，可以根据用户不同需求选择合适的储存引擎。MYSQL支持的存储引擎包括但不局限于以下几种（MyISAM、InnoDB、MEMORY、MERGE…，创建时如果不指定存储引擎系统就会使用默认的存储引擎MYSQL5.5之前默认的存储引擎是MyISAM，之后则是InnoDB。

在创建表的时候也可以通过ENGINE关键字设置新建表的存储引擎。

也可以通过ALTER TABLE修改表的存储引擎

存储引擎的特性

MyISAM

MyISAM是5.5之前版本的默认存储引擎，MyISAM不支持事务，也不支持外键，优势是访问速度快，对事物完整性没有要求或者以SELECT、INSERT为主的引用基本上都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其文件名和表名都相同，但是扩展名分别为：

• .frm(存储表定义)
• .MYD(MYDATA，存储数据)
• .MYI(MYIndex，存储索引)

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。
MyISAM还支持3种不同的存储格式：

1. 静态表
2. 动态表
3. 压缩表
   其中静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障易恢复；缺点是占用空间通常比动态的多。静态表的数据会在存储时按照列的宽度定义补足空格，但是在应用访问的时候不会出现这些空格，这些空格在返回给用户之前就已经被去掉。
   这种类型的表通常包含一些不会经常更新的参考数据，例如国家列表、货币代码表、行政区划表等。
   静态表的特点是数据量通常比较小，且不会频繁地被修改。因此，可以使用一些优化技巧来提高查询性能，例如使用缓存机制、预先加载数据等。
   
   动态表则是包含变长字段，记录不是固定长度的，这样的有点事占用空间相对较小，但是频繁的更新和删除记录会产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能。
   压缩表由myisampack工具创建，占用非常小的额子盘空间，一位内每个记录时单独被压缩的，所以只有非常小的访问开支。

InnoDB

InnoDB存储引擎提供了具有提交、回滚和崩溃能力恢复的事务安全，但是对比MyISAM，InnoDB的处理效率差一些，并且会占用更多的磁盘空间保留数据和索引。

1. 自动增长列
   
   自动增长的必须是索引，如果是组合索引也必须是组合索引的第一列，但是对于MyISAM,自动增长列可以是组合索引的其他列，这样插入数据后，自动增长列是按照组合索引的前面几列进行排序后递增的

2. 外键约束
   MYSQL支持晚间的存储引擎只有InnoDB,在创建外键的时候，要求父表必须要有对应的索引，子表在创建的时候也会自动创建对应的索引。
   在关系型数据库中，外键约束是一种用于维护表之间关系的约束。外键约束通常用于确保表中的数据一致性，同时也可以帮助保护数据完整性。

外键约束是指一个表中的一个或多个列，它们的值必须在另一个表中的一个指定列的值中存在。这个指定的列通常是另一个表的主键列。这种约束可以确保在一个表中插入的数据与另一个表中的数据保持一致。

例如，假设有两个表，一个是 orders 表，另一个是 customers 表。orders 表中有一个 customer_id 列，用于表示订单所属的客户。为了确保 orders 表中的 customer_id 列的值必须存在于 customers 表的 id 列中，可以在 orders 表中创建一个外键约束：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(id);

在上面的例子中，fk_orders_customer_id 是外键约束的名称，customer_id 是 orders 表中的列，customers 是引用表的名称，id 是 customers 表中的列。

当试图在 orders 表中插入一个 customer_id 值，如果这个值在 customers 表中不存在，就会触发外键约束，从而防止插入操作执行。这可以确保在 orders 表中插入的数据与 customers 表中的数据保持一致，同时也可以防止插入无效的数据。

3. 存储方式
   InnoDB的存储方式有两种
   • 使用共享表空间存储，这种方式创建的表的结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
   • 使用多表空间，这种方式创建的表的结构仍然保存在.frm文件中，但是每个表的数据和索引结构单独保存在.ibd中。如果是个分区表，则每个分区单独对应.ibd文件，文件命名为“表名+分区名”，可以在文件创建分区的时候指定文件的位置，以此来将文件均匀地分布在多个磁盘上。
     行存储：InnoDB 存储引擎采用了行存储的方式，它将每行数据存储在一个独立的数据页中，这样可以提高数据的访问速度和并发性能。

聚簇索引：InnoDB 存储引擎使用聚簇索引来组织数据，每张表都有一个主键，主键索引是一个聚簇索引，它将数据按照主键的值来组织，这样可以提高数据的访问速度。

二次分配：InnoDB 存储引擎使用了一种称为“二次分配”的方式来管理数据页。它将数据页按照大小划分为多个块，每个块可以存储一行数据，当需要存储新的数据行时，它会先在数据页中查找空闲块，如果没有空闲块，就会重新分配一个更大的数据页。

MVCC：InnoDB 存储引擎采用了多版本并发控制（MVCC）来实现事务的隔离性。它会为每个事务维护一个独立的版本号，当多个事务同时访问同一行数据时，它会根据版本号来决定是否允许访问。

问：什么是聚簇索引？

聚簇索引是一种特殊的索引，它将数据行按照主键的值来组织，并将数据行存储在同一个页中。聚簇索引的叶子节点存储了数据行的物理位置，通过聚簇索引可以快速地定位到数据行。

在聚簇索引中，数据行的物理存储顺序和索引顺序是一致的，这意味着相邻的数据行存储在相邻的磁盘块中，可以提高数据的访问速度。因此，聚簇索引适合于经常需要进行范围查询或排序操作的表，可以提高查询的性能。

在 MySQL 中，InnoDB 存储引擎支持聚簇索引，每张表都必须有一个主键，主键索引就是一种聚簇索引。如果没有指定主键，则 InnoDB 存储引擎会选择一个唯一的非空索引作为主键，如果没有这样的索引，则会自动生成一个隐藏的主键。

需要注意的是，聚簇索引的缺点是插入数据时可能会导致数据页的分裂和合并，这会造成额外的性能开销。同时，由于聚簇索引的物理存储顺序与主键的顺序有关，因此如果主键选择不当，可能会导致数据的存储和查询性能下降。因此，在设计表结构时需要根据实际情况选择合适的主键，以达到最佳的查询性能。

MEMORY

MEMORY存储引擎使用存在于内存中的内容来创建表。每个MEMORY表知识及对应一磁盘文件，格式是.frm。MEMORY类型的表访问速度非常快，因为它的数据是存放在内存中的，并且默认使用的是HASH索引，但是服务一旦关闭，表中的数据就会丢失。

如何选择合适的引擎

在选择合适的存储引擎时，应该根据引擎的特点选择，对于复杂的系统同还可以根据实际情况进行组合。

• MyISAM：5.5之前版本默认的存储，如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求，并发性的要求不是很高就可以选择这个存储引擎。
• InnoDB：用于事务处理的应用程序，支持外键。如果对事务的完整性有比较高的要求，在并发条件下要求事务的一致性，数据的操作除了插入和查询以外还包含了分舵更新操作，那么就可以选择InnoDB。
• MEMORY：将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极其快速的访问，但是缺陷是内存大小有限制，且要注意持久化问题。