devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL高级篇（存储引擎InnoDB、MyISAM、Memory）

作者：weixin_40725706 | 2024-04-17 18:00:26

赞

踩

MySQL高级篇（存储引擎InnoDB、MyISAM、Memory）

目录

1、存储引擎简介

1.1、查询建表语句，默认存储引擎：InnoDB

1.2、查看当前数据库支持的存储引擎

1.3、创建表，并指定存储引擎

2、 存储引擎-InnoDB介绍

2.1、存储引擎特点

 3、MyISAM存储引擎

 4、Memory存储引擎

 5、InnoDB、MyISAM、Memory的区别

 6、存储引擎的选择

1、存储引擎简介

1.1、查询建表语句，默认存储引擎：InnoDB

  1. -- 查询建表语句
  2. show create table emp;

1.2、查看当前数据库支持的存储引擎

  1. -- 查看当前数据库支持的存储引擎
  2. show engines;

1.3、创建表，并指定存储引擎

  1. -- 创建表 my_myisam,并指定MYISAM存储引擎
  2. create table my_myisam
  3. (
  4.     id   int,
  5.     name varchar(10)
  6. ) engine = MyISAM;
  7.  
  8. -- 创建表 my_memory,指定MEMORY存储引擎
  9. create table my_memory
  10. (
  11.     id   int,
  12.     name varchar(10)
  13. ) engine = MEMORY;

2、 存储引擎-InnoDB介绍

2.1、存储引擎特点

  • 介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。
  • 特点：DML操作遵循ACID 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）模型，支持 事务 ；行级锁，提高并发访问性能；支持 外键 FOREIGN KEY约束，保证数据的完整性和正确性；
  • 文件：xxx.ibd：xxx代表的是表名，InnoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数：innodb_file_per_table

  • 在InnoDB存储引擎中，.ibd文件是表空间文件，用于存储表的数据和索引。每个InnoDB表都有一个与之关联的.ibd文件（除非该表被配置为使用共享表空间）。.ibd文件通常包含表的元数据、数据和索引信息。

    当InnoDB存储引擎的innodb_file_per_table配置选项被启用时，每个InnoDB表都会有自己独立的.ibd文件。这样做的好处是，可以更容易地管理单个表的大小和存储位置，例如，可以通过移动或删除.ibd文件来移动或删除表。

    与.ibd文件相对应的是.frm文件，它存储了表的元数据（即表结构）。而InnoDB的系统表空间文件通常命名为ibdata1、ibdata2等，用于存储undo日志、插入缓冲区、锁信息等。

    在某些情况下，例如当表损坏或需要优化时，可能需要直接操作.ibd文件。但是，直接操作这些文件是危险的，应该谨慎进行，并在操作前备份相关数据。

show variables like 'innodb_file_per_table';

C:\ProgramData\MySQL\MySQL Server 8.0\Data

  1. Microsoft Windows [版本 10.0.19045.3996]
  2. (c) Microsoft Corporation。保留所有权利。
  3.  
  4. C:\ProgramData\MySQL\MySQL Server 8.0\Data\mybatis@002dexample>ibd2sdi t_book.ibd
  5. ["ibd2sdi"
  6. ,
  7. {
  8.         "type": 1,
  9.         "id": 441,
  10.         "object":
  11.                 {
  12.     "mysqld_version_id": 80026,
  13.     "dd_version": 80023,
  14.     "sdi_version": 80019,
  15.     "dd_object_type": "Table",
  16.     "dd_object": {
  17.         "name": "t_book",
  18.         "mysql_version_id": 80026,
  19.         "created": 20231209065441,
  20.         "last_altered": 20231209065441,
  21.         "hidden": 1,
  22.         "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
  23.         "columns": [
  24.             {
  25.                 "name": "bid",
  26.                 "type": 16,
  27.                 "is_nullable": false,
  28.                 "is_zerofill": false,
  29.                 "is_unsigned": false,
  30.                 "is_auto_increment": false,
  31.                 "is_virtual": false,
  32.                 "hidden": 1,
  33.                 "ordinal_position": 1,
  34.                 "char_length": 80,
  35.                 "numeric_precision": 0,
  36.                 "numeric_scale": 0,
  37.                 "numeric_scale_null": true,
  38.                 "datetime_precision": 0,
  39.                 "datetime_precision_null": 1,
  40.                 "has_no_default": true,
  41.                 "default_value_null": false,
  42.                 "srs_id_null": true,
  43.                 "srs_id": 0,
  44.                 "default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA",
  45.                 "default_value_utf8_null": true,
  46.                 "default_value_utf8": "",
  47.                 "default_option": "",
  48.                 "update_option": "",
  49.                 "comment": "",
  50.                 "generation_expression": "",
  51.                 "generation_expression_utf8": "",
  52.                 "options": "interval_count=0;",
  53.                 "se_private_data": "table_id=1162;",
  54.                 "engine_attribute": "",
  55.                 "secondary_engine_attribute": "",
  56.                 "column_key": 2,
  57.                 "column_type_utf8": "varchar(20)",
  58.                 "elements": [],
  59.                 "collation_id": 255,
  60.                 "is_explicit_collation": false
  61.             },
  62.             {
  63.                 "name": "bname",
  64.                 "type": 16,
  65.                 "is_nullable": true,
  66.                 "is_zerofill": false,
  67.                 "is_unsigned": false,
  68.                 "is_auto_increment": false,
  69.                 "is_virtual": false,
  70.                 "hidden": 1,
  71.                 "ordinal_position": 2,
  72.                 "char_length": 80,
  73.                 "numeric_precision": 0,
  74.                 "numeric_scale": 0,
  75.                 "numeric_scale_null": true,
  76.                 "datetime_precision": 0,
  77.                 "datetime_precision_null": 1,
  78.                 "has_no_default": false,
  79.                 "default_value_null": true,
  80.                 "srs_id_null": true,
  81.                 "srs_id": 0,
  82.                 "default_value": "",
  83.                 "default_value_utf8_null": true,
  84.                 "default_value_utf8": "",
  85.                 "default_option": "",
  86.                 "update_option": "",
  87.                 "comment": "",
  88.                 "generation_expression": "",
  89.                 "generation_expression_utf8": "",
  90.                 "options": "interval_count=0;",
  91.                 "se_private_data": "table_id=1162;",
  92.                 "engine_attribute": "",
  93.                 "secondary_engine_attribute": "",
  94.                 "column_key": 1,
  95.                 "column_type_utf8": "varchar(20)",
  96.                 "elements": [],
  97.                 "collation_id": 255,
  98.                 "is_explicit_collation": false
  99.             },
  100.             {
  101.                 "name": "stuid",
  102.                 "type": 16,
  103.                 "is_nullable": true,
  104.                 "is_zerofill": false,
  105.                 "is_unsigned": false,
  106.                 "is_auto_increment": false,
  107.                 "is_virtual": false,
  108.                 "hidden": 1,
  109.                 "ordinal_position": 3,
  110.                 "char_length": 80,
  111.                 "numeric_precision": 0,
  112.                 "numeric_scale": 0,
  113.                 "numeric_scale_null": true,
  114.                 "datetime_precision": 0,
  115.                 "datetime_precision_null": 1,
  116.                 "has_no_default": false,
  117.                 "default_value_null": true,
  118.                 "srs_id_null": true,
  119.                 "srs_id": 0,
  120.                 "default_value": "",
  121.                 "default_value_utf8_null": true,
  122.                 "default_value_utf8": "",
  123.                 "default_option": "",
  124.                 "update_option": "",
  125.                 "comment": "",
  126.                 "generation_expression": "",
  127.                 "generation_expression_utf8": "",
  128.                 "options": "interval_count=0;",
  129.                 "se_private_data": "table_id=1162;",
  130.                 "engine_attribute": "",
  131.                 "secondary_engine_attribute": "",
  132.                 "column_key": 4,
  133.                 "column_type_utf8": "varchar(20)",
  134.                 "elements": [],
  135.                 "collation_id": 255,
  136.                 "is_explicit_collation": false
  137.             },
  138.             {
  139.                 "name": "DB_TRX_ID",
  140.                 "type": 10,
  141.                 "is_nullable": false,
  142.                 "is_zerofill": false,
  143.                 "is_unsigned": false,
  144.                 "is_auto_increment": false,
  145.                 "is_virtual": false,
  146.                 "hidden": 2,
  147.                 "ordinal_position": 4,
  148.                 "char_length": 6,
  149.                 "numeric_precision": 0,
  150.                 "numeric_scale": 0,
  151.                 "numeric_scale_null": true,
  152.                 "datetime_precision": 0,
  153.                 "datetime_precision_null": 1,
  154.                 "has_no_default": false,
  155.                 "default_value_null": true,
  156.                 "srs_id_null": true,
  157.                 "srs_id": 0,
  158.                 "default_value": "",
  159.                 "default_value_utf8_null": true,
  160.                 "default_value_utf8": "",
  161.                 "default_option": "",
  162.                 "update_option": "",
  163.                 "comment": "",
  164.                 "generation_expression": "",
  165.                 "generation_expression_utf8": "",
  166.                 "options": "",
  167.                 "se_private_data": "table_id=1162;",
  168.                 "engine_attribute": "",
  169.                 "secondary_engine_attribute": "",
  170.                 "column_key": 1,
  171.                 "column_type_utf8": "",
  172.                 "elements": [],
  173.                 "collation_id": 63,
  174.                 "is_explicit_collation": false
  175.             },
  176.             {
  177.                 "name": "DB_ROLL_PTR",
  178.                 "type": 9,
  179.                 "is_nullable": false,
  180.                 "is_zerofill": false,
  181.                 "is_unsigned": false,
  182.                 "is_auto_increment": false,
  183.                 "is_virtual": false,
  184.                 "hidden": 2,
  185.                 "ordinal_position": 5,
  186.                 "char_length": 7,
  187.                 "numeric_precision": 0,
  188.                 "numeric_scale": 0,
  189.                 "numeric_scale_null": true,
  190.                 "datetime_precision": 0,
  191.                 "datetime_precision_null": 1,
  192.                 "has_no_default": false,
  193.                 "default_value_null": true,
  194.                 "srs_id_null": true,
  195.                 "srs_id": 0,
  196.                 "default_value": "",
  197.                 "default_value_utf8_null": true,
  198.                 "default_value_utf8": "",
  199.                 "default_option": "",
  200.                 "update_option": "",
  201.                 "comment": "",
  202.                 "generation_expression": "",
  203.                 "generation_expression_utf8": "",
  204.                 "options": "",
  205.                 "se_private_data": "table_id=1162;",
  206.                 "engine_attribute": "",
  207.                 "secondary_engine_attribute": "",
  208.                 "column_key": 1,
  209.                 "column_type_utf8": "",
  210.                 "elements": [],
  211.                 "collation_id": 63,
  212.                 "is_explicit_collation": false
  213.             }
  214.         ],
  215.         "schema_ref": "mybatis-example",
  216.         "se_private_id": 1162,
  217.         "engine": "InnoDB",
  218.         "last_checked_for_upgrade_version_id": 0,
  219.         "comment": "",
  220.         "se_private_data": "",
  221.         "engine_attribute": "",
  222.         "secondary_engine_attribute": "",
  223.         "row_format": 2,
  224.         "partition_type": 0,
  225.         "partition_expression": "",
  226.         "partition_expression_utf8": "",
  227.         "default_partitioning": 0,
  228.         "subpartition_type": 0,
  229.         "subpartition_expression": "",
  230.         "subpartition_expression_utf8": "",
  231.         "default_subpartitioning": 0,
  232.         "indexes": [
  233.             {
  234.                 "name": "PRIMARY",
  235.                 "hidden": false,
  236.                 "is_generated": false,
  237.                 "ordinal_position": 1,
  238.                 "comment": "",
  239.                 "options": "flags=0;",
  240.                 "se_private_data": "id=289;root=4;space_id=101;table_id=1162;trx_id=10390;",
  241.                 "type": 1,
  242.                 "algorithm": 2,
  243.                 "is_algorithm_explicit": false,
  244.                 "is_visible": true,
  245.                 "engine": "InnoDB",
  246.                 "engine_attribute": "",
  247.                 "secondary_engine_attribute": "",
  248.                 "elements": [
  249.                     {
  250.                         "ordinal_position": 1,
  251.                         "length": 80,
  252.                         "order": 2,
  253.                         "hidden": false,
  254.                         "column_opx": 0
  255.                     },
  256.                     {
  257.                         "ordinal_position": 2,
  258.                         "length": 4294967295,
  259.                         "order": 2,
  260.                         "hidden": true,
  261.                         "column_opx": 3
  262.                     },
  263.                     {
  264.                         "ordinal_position": 3,
  265.                         "length": 4294967295,
  266.                         "order": 2,
  267.                         "hidden": true,
  268.                         "column_opx": 4
  269.                     },
  270.                     {
  271.                         "ordinal_position": 4,
  272.                         "length": 4294967295,
  273.                         "order": 2,
  274.                         "hidden": true,
  275.                         "column_opx": 1
  276.                     },
  277.                     {
  278.                         "ordinal_position": 5,
  279.                         "length": 4294967295,
  280.                         "order": 2,
  281.                         "hidden": true,
  282.                         "column_opx": 2
  283.                     }
  284.                 ],
  285.                 "tablespace_ref": "mybatis-example/t_book"
  286.             },
  287.             {
  288.                 "name": "fk_book_stuid",
  289.                 "hidden": false,
  290.                 "is_generated": true,
  291.                 "ordinal_position": 2,
  292.                 "comment": "",
  293.                 "options": "flags=0;",
  294.                 "se_private_data": "id=290;root=5;space_id=101;table_id=1162;trx_id=10390;",
  295.                 "type": 3,
  296.                 "algorithm": 2,
  297.                 "is_algorithm_explicit": false,
  298.                 "is_visible": true,
  299.                 "engine": "InnoDB",
  300.                 "engine_attribute": "",
  301.                 "secondary_engine_attribute": "",
  302.                 "elements": [
  303.                     {
  304.                         "ordinal_position": 1,
  305.                         "length": 80,
  306.                         "order": 2,
  307.                         "hidden": false,
  308.                         "column_opx": 2
  309.                     },
  310.                     {
  311.                         "ordinal_position": 2,
  312.                         "length": 4294967295,
  313.                         "order": 2,
  314.                         "hidden": true,
  315.                         "column_opx": 0
  316.                     }
  317.                 ],
  318.                 "tablespace_ref": "mybatis-example/t_book"
  319.             }
  320.         ],
  321.         "foreign_keys": [
  322.             {
  323.                 "name": "fk_book_stuid",
  324.                 "match_option": 1,
  325.                 "update_rule": 1,
  326.                 "delete_rule": 1,
  327.                 "unique_constraint_name": "PRIMARY",
  328.                 "referenced_table_catalog_name": "def",
  329.                 "referenced_table_schema_name": "mybatis-example",
  330.                 "referenced_table_name": "t_stu",
  331.                 "elements": [
  332.                     {
  333.                         "column_opx": 2,
  334.                         "ordinal_position": 1,
  335.                         "referenced_column_name": "sid"
  336.                     }
  337.                 ]
  338.             }
  339.         ],
  340.         "check_constraints": [],
  341.         "partitions": [],
  342.         "collation_id": 255
  343.     }
  344. }
  345. }
  346. ,
  347. {
  348.         "type": 2,
  349.         "id": 106,
  350.         "object":
  351.                 {
  352.     "mysqld_version_id": 80026,
  353.     "dd_version": 80023,
  354.     "sdi_version": 80019,
  355.     "dd_object_type": "Tablespace",
  356.     "dd_object": {
  357.         "name": "mybatis-example/t_book",
  358.         "comment": "",
  359.         "options": "autoextend_size=0;encryption=N;",
  360.         "se_private_data": "flags=16417;id=101;server_version=80026;space_version=1;state=normal;",
  361.         "engine": "InnoDB",
  362.         "engine_attribute": "",
  363.         "files": [
  364.             {
  365.                 "ordinal_position": 1,
  366.                 "filename": ".\\mybatis@002dexample\\t_book.ibd",
  367.                 "se_private_data": "id=101;"
  368.             }
  369.         ]
  370.     }
  371. }
  372. }
  373. ]
  374.  
  375. C:\ProgramData\MySQL\MySQL Server 8.0\Data\mybatis@002dexample>

 3、MyISAM存储引擎

  • 介绍：MyISAM是MySQL早期的默认存储引擎
  • 特点：不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快
  • 文件：xxx.sdi：存储表结构信息；xxx.MYD：存储数据；xxx.MYI：存储索引

https://www.json.cn/

 

 4、Memory存储引擎

  • 介绍：Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
  • 特点：内存存放；hash索引（默认）
  • 文件：xxx.sdi：存储表结构信息

 5、InnoDB、MyISAM、Memory的区别

 6、存储引擎的选择

  • InnoDB用于大多数业务场景
  • MyISAM用于业务系统中的日志相关的数据，电商当中足迹和评论相关的数据，已经被 MongoDB 取代
  • MEMORY用于缓存，已经被 redis 取代
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/441499
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号