目錄
1、存儲引擎簡介
1.1、查詢建表語句，默認(rèn)存儲引擎：InnoDB
1.2、查看當(dāng)前數(shù)據(jù)庫支持的存儲引擎
1.3、創(chuàng)建表，并指定存儲引擎
2、?存儲引擎-InnoDB介紹
2.1、存儲引擎特點(diǎn)
?3、MyISAM存儲引擎
?4、Memory存儲引擎
?5、InnoDB、MyISAM、Memory的區(qū)別
?6、存儲引擎的選擇
1、存儲引擎簡介
1.1、查詢建表語句，默認(rèn)存儲引擎：InnoDB
-- 查詢建表語句 show create table emp;
1.2、查看當(dāng)前數(shù)據(jù)庫支持的存儲引擎
-- 查看當(dāng)前數(shù)據(jù)庫支持的存儲引擎 show engines;
1.3、創(chuàng)建表，并指定存儲引擎
-- 創(chuàng)建表 my_myisam,并指定MYISAM存儲引擎 create table my_myisam ( id int, name varchar(10) ) engine = MyISAM; -- 創(chuàng)建表 my_memory,指定MEMORY存儲引擎 create table my_memory ( id int, name varchar(10) ) engine = MEMORY;
2、?存儲引擎-InnoDB介紹
2.1、存儲引擎特點(diǎn)
- 介紹：InnoDB是一種兼顧高可靠性和高性能的通用存儲引擎，在MySQL 5.5之后，InnoDB是默認(rèn)的MySQL存儲引擎。
- 特點(diǎn)：DML操作遵循ACID?原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）模型，支持 事務(wù) ；行級鎖，提高并發(fā)訪問性能；支持 外鍵 FOREIGN KEY約束，保證數(shù)據(jù)的完整性和正確性；
- 文件：xxx.ibd：xxx代表的是表名，InnoDB引擎的每張表都會對應(yīng)這樣一個表空間文件，存儲該表的表結(jié)構(gòu)（frm、sdi）、數(shù)據(jù)和索引。參數(shù)：innodb_file_per_table
在InnoDB存儲引擎中，
.ibd文件是表空間文件，用于存儲表的數(shù)據(jù)和索引。每個InnoDB表都有一個與之關(guān)聯(lián)的.ibd文件（除非該表被配置為使用共享表空間）。.ibd文件通常包含表的元數(shù)據(jù)、數(shù)據(jù)和索引信息。當(dāng)InnoDB存儲引擎的
innodb_file_per_table配置選項(xiàng)被啟用時，每個InnoDB表都會有自己獨(dú)立的.ibd文件。這樣做的好處是，可以更容易地管理單個表的大小和存儲位置，例如，可以通過移動或刪除.ibd文件來移動或刪除表。與
.ibd文件相對應(yīng)的是.frm文件，它存儲了表的元數(shù)據(jù)（即表結(jié)構(gòu)）。而InnoDB的系統(tǒng)表空間文件通常命名為ibdata1、ibdata2等，用于存儲undo日志、插入緩沖區(qū)、鎖信息等。在某些情況下，例如當(dāng)表損壞或需要優(yōu)化時，可能需要直接操作
.ibd文件。但是，直接操作這些文件是危險的，應(yīng)該謹(jǐn)慎進(jìn)行，并在操作前備份相關(guān)數(shù)據(jù)。show variables like 'innodb_file_per_table';
C:\ProgramData\MySQL\MySQL Server 8.0\Data
Microsoft Windows [版本 10.0.19045.3996] (c) Microsoft Corporation。保留所有權(quán)利。 C:\ProgramData\MySQL\MySQL Server 8.0\Data\mybatis@002dexample>ibd2sdi t_book.ibd ["ibd2sdi" , { "type": 1, "id": 441, "object": { "mysqld_version_id": 80026, "dd_version": 80023, "sdi_version": 80019, "dd_object_type": "Table", "dd_object": { "name": "t_book", "mysql_version_id": 80026, "created": 20231209065441, "last_altered": 20231209065441, "hidden": 1, "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;", "columns": [ { "name": "bid", "type": 16, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 1, "char_length": 80, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": true, "default_value_null": false, "srs_id_null": true, "srs_id": 0, "default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=1162;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 2, "column_type_utf8": "varchar(20)", "elements": [], "collation_id": 255, "is_explicit_collation": false }, { "name": "bname", "type": 16, "is_nullable": true, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 2, "char_length": 80, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=1162;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "varchar(20)", "elements": [], "collation_id": 255, "is_explicit_collation": false }, { "name": "stuid", "type": 16, "is_nullable": true, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 3, "char_length": 80, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=1162;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 4, "column_type_utf8": "varchar(20)", "elements": [], "collation_id": 255, "is_explicit_collation": false }, { "name": "DB_TRX_ID", "type": 10, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 2, "ordinal_position": 4, "char_length": 6, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "", "se_private_data": "table_id=1162;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false }, { "name": "DB_ROLL_PTR", "type": 9, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 2, "ordinal_position": 5, "char_length": 7, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "", "se_private_data": "table_id=1162;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false } ], "schema_ref": "mybatis-example", "se_private_id": 1162, "engine": "InnoDB", "last_checked_for_upgrade_version_id": 0, "comment": "", "se_private_data": "", "engine_attribute": "", "secondary_engine_attribute": "", "row_format": 2, "partition_type": 0, "partition_expression": "", "partition_expression_utf8": "", "default_partitioning": 0, "subpartition_type": 0, "subpartition_expression": "", "subpartition_expression_utf8": "", "default_subpartitioning": 0, "indexes": [ { "name": "PRIMARY", "hidden": false, "is_generated": false, "ordinal_position": 1, "comment": "", "options": "flags=0;", "se_private_data": "id=289;root=4;space_id=101;table_id=1162;trx_id=10390;", "type": 1, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "InnoDB", "engine_attribute": "", "secondary_engine_attribute": "", "elements": [ { "ordinal_position": 1, "length": 80, "order": 2, "hidden": false, "column_opx": 0 }, { "ordinal_position": 2, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 3 }, { "ordinal_position": 3, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 4 }, { "ordinal_position": 4, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 1 }, { "ordinal_position": 5, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 2 } ], "tablespace_ref": "mybatis-example/t_book" }, { "name": "fk_book_stuid", "hidden": false, "is_generated": true, "ordinal_position": 2, "comment": "", "options": "flags=0;", "se_private_data": "id=290;root=5;space_id=101;table_id=1162;trx_id=10390;", "type": 3, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "InnoDB", "engine_attribute": "", "secondary_engine_attribute": "", "elements": [ { "ordinal_position": 1, "length": 80, "order": 2, "hidden": false, "column_opx": 2 }, { "ordinal_position": 2, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 0 } ], "tablespace_ref": "mybatis-example/t_book" } ], "foreign_keys": [ { "name": "fk_book_stuid", "match_option": 1, "update_rule": 1, "delete_rule": 1, "unique_constraint_name": "PRIMARY", "referenced_table_catalog_name": "def", "referenced_table_schema_name": "mybatis-example", "referenced_table_name": "t_stu", "elements": [ { "column_opx": 2, "ordinal_position": 1, "referenced_column_name": "sid" } ] } ], "check_constraints": [], "partitions": [], "collation_id": 255 } } } , { "type": 2, "id": 106, "object": { "mysqld_version_id": 80026, "dd_version": 80023, "sdi_version": 80019, "dd_object_type": "Tablespace", "dd_object": { "name": "mybatis-example/t_book", "comment": "", "options": "autoextend_size=0;encryption=N;", "se_private_data": "flags=16417;id=101;server_version=80026;space_version=1;state=normal;", "engine": "InnoDB", "engine_attribute": "", "files": [ { "ordinal_position": 1, "filename": ".\\mybatis@002dexample\\t_book.ibd", "se_private_data": "id=101;" } ] } } } ] C:\ProgramData\MySQL\MySQL Server 8.0\Data\mybatis@002dexample>
?3、MyISAM存儲引擎
- 介紹：MyISAM是MySQL早期的默認(rèn)存儲引擎
- 特點(diǎn)：不支持事務(wù)，不支持外鍵；支持表鎖，不支持行鎖；訪問速度快
- 文件：xxx.sdi：存儲表結(jié)構(gòu)信息；xxx.MYD：存儲數(shù)據(jù)；xxx.MYI：存儲索引
https://www.json.cn/
?
?4、Memory存儲引擎
- 介紹：Memory引擎的表數(shù)據(jù)時存儲在內(nèi)存中的，由于受到硬件問題、或斷電問題的影響，只能將這些表作為臨時表或緩存使用。
- 特點(diǎn)：內(nèi)存存放；hash索引（默認(rèn)）
- 文件：xxx.sdi：存儲表結(jié)構(gòu)信息
?5、InnoDB、MyISAM、Memory的區(qū)別
文章來源：http://www.zghlxwxcb.cn/news/detail-847799.html
?6、存儲引擎的選擇
文章來源地址http://www.zghlxwxcb.cn/news/detail-847799.html
- InnoDB用于大多數(shù)業(yè)務(wù)場景
- MyISAM用于業(yè)務(wù)系統(tǒng)中的日志相關(guān)的數(shù)據(jù)，電商當(dāng)中足跡和評論相關(guān)的數(shù)據(jù)，已經(jīng)被?MongoDB 取代
- MEMORY用于緩存，已經(jīng)被 redis 取代
到了這里，關(guān)于MySQL高級篇（存儲引擎InnoDB、MyISAM、Memory）的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
