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'\Gmysql> 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