console|
收藏闪存小组博问

Tod4

园龄：2年11个月粉丝：21关注：0

MySQL（六）存储引擎

合集 - MySQL高级(30)
1.MySQL（一）Linux下MySQL的安装2.MySQL（二）字符集、比较规则与规范3.MySQL（三）数据目录4.MySQL（四）用户与权限管理5.MySQL（五）配置文件、系统变量与MySQL架构
6.MySQL（六）存储引擎
7.MySQL（七）索引8.MySQL（八）哈希索引、AVL树、B树与B+树的比较9.MySQL（九）InnoDB行格式10.MySQL（九）InnoDB数据结构11.MySQL（十）表空间结构：区、段与碎片区12.MySQL（十一）索引的分类和创建原则13.MySQL（十二）索引使用的情况分析14.MySQL（十三）MySQL性能分析工具：慢查询日志与PROFILE查询成本15.MySQL（十四）分析查询语句Explain 七千字总结16.MySQL（十五）分析优化器的查询计划：Trace17.MySQL（十六）索引优化：索引失效的情况分析18.MySQL（十七）查询优化（一）19.MySQL（十七）查询优化（二）与数据库主键设计20.MySQL（十八）MySQL事务(一)：事务的概述与使用21.MySQL（十八）MySQL事务(二)：事务的隔离级别22.MySQL（十九）MySQL事务日志（一）RedoLog23.MySQL（十九）MySQL事务日志（二）UndoLog24.MySQL（二十）锁（一）锁的概述与S、X锁25.MySQL（二十）锁（二）表锁、行锁与页级锁26.MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁27.MySQL（二十一）MVCC多版本并发控制28.MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志29.MySQL（二十二）其他数据库日志（二）bin log二进制日志与中继日志30.【完结撒花】MySQL（二十三）主从复制
收起

存储引擎

​ 连接管理、查询缓存、解析器和执行器被归为MySQL service，而把真实存储数据的功能划分为存储引擎的功能。所以MySQL service经过查询优化后，只需按照生成的执行计划调用存储引擎的API获取数据，然后返回给客户端即可。存储引擎本质就是表的类型，功能是接收上层传来的指令，然后对表中的数据进行提取或者写入操作。

1 查看存储引擎

show engines查看支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | NO      | Clustered, fault-tolerant tables                               | NULL         | NULL | NULL       |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | NO      | MySQL Cluster system information storage engine                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
查看默认表存储引擎
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
 
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

2 修改存储引擎

SET DEFAULT_STORAGE_ENGINE
mysql> SET DEFAULT_STORAGE_ENGINE = MYISAM
    -> ;
Query OK, 0 rows affected (0.01 sec)
mysql> create table emp1(id int);
Query OK, 0 rows affected (0.00 sec)
 
mysql> show create table emp1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| emp1  | CREATE TABLE `emp1` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

​ 数据库存储结构为：

[root@hadoop101 mysql]# cd dbtest1/
[root@hadoop101 dbtest1]# ls
db.opt  emp1.frm  emp1.MYD  emp1.MYI
alter 修改表的存储引擎
mysql> alter table emp2 engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show create table emp2;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| emp2  | CREATE TABLE `emp2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3 显示指明表的存储引擎

mysql> create table emp2(id int) ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show create table emp2;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| emp2  | CREATE TABLE `emp2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4 存储引擎介绍

1 InnoDB引擎：具备外键功能的事务存储引擎

  • 5.5之后默认使用InnoDB存储引擎

  • InnoDB用来处理大量的短期事务，可以确保事务的完整提交和回滚

  • 除了查询和增加之外，还需要更新和删除操作，应该优先选择InnoDB存储引擎

  • 数据文件结构：

    • 表名.frm：存储表结构（8.0后合并在了ibd文件里面）
    • 表名.ibd：存储表数据和索引

  • InnoDB是为了处理巨大数据量的最大性能设计

  • 对比MyIsam，InnoDB写的效率会差一些，并且会占用更多的磁盘空间保存数据和索引

  • MyISAM只缓存索引，而不缓存真实的数据；InnoDB不仅需要缓存索引还要缓存真实数据，对内存要求较高，而内存大小对性能有决定性影响。

    对内存要求较高是由于底层存储数据的结构的原因，相较于MyISAM而言

2 MyISAM引擎：主要的非事务存储引擎
  • MyISAM提供了大量特性，如全文检索、压缩、空间函数等，但MyISAM不支持事务、行级锁、外键，而且有一个很严重的缺陷：崩溃后无法安全恢复
  • 5.5之前的默认存储引擎
  • 优势是访问速度快，对事务没有要求或者以select、insert为主的应用
  • 针对数据统计有额外的常数存储，因此count(*)的查询效率很高
  • 数据结构文件：
    • 表名.frm：存储表结构
    • 表名.MYD：存储数据
    • 表名.MYI：存储索引
  • 应用场景：只读业务或者以读为主的业务
3 InnoDB与MyISAM的区别
image-20230311165555738
posted @   Tod4  阅读(67)  评论(0)  编辑  收藏  举报
   
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛
评论
收藏
关注
推荐
深色
回顶
收起