学习参考https://www.bilibili.com/video/BV1Kr4y1i7ru?spm_id_from=333.999.0.0
目录
体系结构
一.存储引擎简介
(1)查询建表
show create table 表
'account', 'CREATE TABLE `account`
(\n `id` int NOT NULL AUTO_INCREMENT COMMENT \'主键\',
\n `name` varchar(10) NOT NULL COMMENT \'姓名\',
\n `money` int unsigned DEFAULT NULL COMMENT \'余额\',
\n PRIMARY KEY (`id`)
\n)
ENGINE=InnoDB
AUTO_INCREMENT=3
DEFAULT
CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT=\'账户表\''
表默认引擎为innodb。
(2)查询当前数据库所支持的引擎
show engines;
(3)创建表时指定引擎
create table 表名(
id int
)engine = 引擎;
二.存储引擎特点
(1)InnoDB
show variables like 'innodb_file_per_table';
说明我的每张表都对应着一个表结构。
每一个.ibd文件存放着
1.一个表结构,
2.该表所存储的数据,
3.索引 。
展示ibd文件内容
ibd2sid 表名.ibd
innodb的逻辑存储结构
(2)MyIASAM
创建一个使用myisam引擎的表
create table myisammmm(
id int
)engine = myisam;
可以看出来多出了
打开sdi文件查看里面的表结构
{"mysqld_version_id":80029,"dd_version":80023,"sdi_version":80019,"dd_object_type":"Table","dd_object":{"name":"myisammmm","mysql_version_id":80029,"created":20220802020145,"last_altered":20220802020145,"hidden":1,"options":"avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;","columns":[
{
"name":"id",
"type":4,"
is_nullable":true,"
is_zerofill":false,"
is_unsigned":false,"
is_auto_increment":false,
"is_virtual":false,
"hidden":1,
"ordinal_position":1,
"char_length":11,
"numeric_precision":10,
"numeric_scale":0,
"numeric_scale_null":false,
"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":"",
"engine_attribute":"",
"secondary_engine_attribute":"",
"column_key":1,
"column_type_utf8":"int",
"elements[],
"collation_id":255,
"is_explicit_collation":false
}],"schema_ref":"text","se_private_id":18446744073709551615,"engine":"MyISAM","last_checke
(3)Memory
数据存放在内存中,读取操作肯定是极快的,
但既然只是存放在内存中,缺点也十分明显
数据无法长久保存。