MySQL存储

目录

1. MySQL存储引擎概述

2. 存储引擎的作用

3.存储引擎类型

4. 查看支持的存储引擎

6. InnoDB存储引擎

7. MyISAM与InnoDB的区别

8. 存储引擎的选择

9. 修改默认存储引擎

---

1. MySQL存储引擎概述

在MySQL中，数据通过不同的技术存储在文件（或内存）中，每种技术都有其独特的存储机制、索引技巧、锁定水平以及提供的功能和能力。选择合适的存储引擎可以显著改善应用的整体性能。

2. 存储引擎的作用

• 存储和检索数据：不同的存储引擎对数据的存储和检索方式有所不同，适用于不同的应用场景。
• 提供额外功能：如事务处理、外键约束等。

3.存储引擎类型

存储引擎	InnoDB	MyISAM	Memory	Archive
事务支持	是	否	否	否
外键支持	是	否	否	否
行级锁定	是	表级锁定	表级锁定（但通常很快）	无锁定（插入后无法更新或删除）
默认存储格式	动态行格式	静态/动态/压缩	内存中的哈希表	压缩的行格式
适用场景	高并发更新、事务处理、外键约束	SELECT、INSERT密集型应用，不支持事务	临时表、缓存数据	日志、审计等非关键数据
自动灾难恢复	是	否	依赖于MySQL服务器的稳定性	否
数据持久化	是（磁盘）	是（磁盘）	重启后丢失（内存）	是（磁盘，但不可更新）
索引类型	B+树	B+树	HASH（默认）或BTREE	插入时压缩，不支持索引搜索
文件结构	表空间文件、日志文件	.frm（定义）、.MYD（数据）、.MYI（索引）	内存中的数据结构	.arc（归档文件）
数据修改后的恢复	支持事务回滚	不支持事务，但可以检查和修复表	重启后数据丢失	不支持修改操作
性能特点	高并发、ACID兼容	高查询性能、较低写入性能	极快的数据访问速度，但受限于内存大小	高效存储大量数据，但不支持数据修改

4. 查看支持的存储引擎

在MySQL客户端中，可以使用以下命令查看支持的存储引擎：
5. MyISAM存储引擎

• 特点：不支持事务和外键，访问速度快，适用于以SELECT、INSERT为主的应用。
• 文件结构：每个MyISAM表在磁盘上存储为三个文件，扩展名分别为frm（存储表定义）、MYD（存储数据）、MYI（存储索引）。
• 存储格式：
  • 静态表：固定长度记录，存储迅速，容易缓存，但占用空间多。
  • 动态表：记录长度可变，占用空间少，但更新、删除频繁时会产生碎片。
  • 压缩表：由myisamchk工具创建，占用空间非常小。
• 检查与修复：可以使用CHECKTABLE和REPAIR TABLE语句检查和修复损坏的MyISAM表。

6. InnoDB存储引擎

• 特点：
  • 健壮的事务型存储引擎，支持行级锁定和外键约束。
  • MySQL 5.5.5版本开始，InnoDB成为默认存储引擎。
• 适用场景：
  • 更新密集的表。
  • 需要事务处理的应用。
  • 支持自动灾难恢复和外键约束。
• 性能特点：
  • 扫描全表计算行数时效率较低（如SELECT COUNT(*) FROM table）。
  • 清空表时效率较低（逐行删除）。

7. MyISAM与InnoDB的区别

• 事务支持：InnoDB支持事务，MyISAM不支持。
• 锁机制：InnoDB支持行级锁（特定条件下可能锁全表），MyISAM主要使用表级锁。
• 外键约束：InnoDB支持，MyISAM不支持。
• 性能与空间：MyISAM在查询和插入方面性能更优，但InnoDB在更新频繁和需要事务的场景下表现更佳。

8. 存储引擎的选择

• 应用程序需求：如果需要事务处理，则选择InnoDB；如果查询性能要求高，则选择MyISAM。
• 数据量大小：MyISAM并非只能用于小型应用，大数据量时可通过分表分库等架构升级解决。

9. 修改默认存储引擎

• 通过ALTER TABLE：

先创建一个表

mysql> create database auth;
mysql> use auth  
mysql> CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));mysql> show table status from auth where name='t1'\G

mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G

• 修改my.cnf配置文件：

  [root@localhost ~]# vim /etc/my.cnf
  [mysqld]
  #添加下面语句
  default-storage-engine=MyISAM[root@localhost ~]# systemctl restart mysqld
  [root@localhost ~]# mysql -uroot -ppwd123
  mysql> use authmysql> CREATE TABLE t2 (user_name CHAR(16), user_passwd CHAR(48));
  mysql> show table status from auth where name='t2'\G

  
  并重启MySQL服务。

• 创建表时指定：

  mysql> use auth
  mysql> create table t3 (id int(10),name char(20)) engine=INNODB;
  mysql> show table status from auth where name='t3'\G