什么是数据库存储引擎?
数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能
如何查看引擎?
1.如何查看数据库支持的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.03 sec)
2.查看当前数据的引擎:
show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
查看当前库所有表的引擎:
mysql> show table status;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| cut | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2022-05-23 09:34:33 | 2022-05-23 09:49:58 | NULL | gbk_chinese_ci | NULL | | |
| dept | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2022-05-21 10:36:20 | NULL | NULL | gbk_chinese_ci | NULL | | |
| employ | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VIEW |
| employee | MyISAM | 10 | Dynamic | 14 | 42 | 592 | 281474976710655 | 2048 | 0 | NULL | 2022-05-21 10:36:27 | 2022-05-23 09:49:58 | NULL | utf8_general_ci | NULL | | |
| kk | InnoDB | 10 | Dynamic | 8 | 2048 | 16384 | 0 | 0 | 0 | NULL | 2022-05-21 16:12:49 | NULL | NULL | gbk_chinese_ci | NULL | | |
| salgrade | InnoDB | 10 | Dynamic | 4 | 4096 | 16384 | 0 | 0 | 0 | NULL | 2022-05-21 10:36:35 | NULL | NULL | gbk_chinese_ci | NULL | | |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
6 rows in set (0.04 sec)
建表时指定引擎
mysql> create table yinqing(id int,name varchar(20)) engine='innodb';
Query OK, 0 rows affected (0.02 sec)
mysql> show create table yinqing;
+---------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------+
| yinqing | CREATE TABLE `yinqing` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
修改表的引擎
alter table 表名 engine=‘引擎名’;
mysql> alter table employee engine='innodb';
Query OK, 14 rows affected (0.04 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
MyISAM与InnoDB的区别
MyISAM:支持全文索引(full text);不支持事务;表级锁;保存表的具体行数;奔溃恢复不好
Innodb:支持事务;以前的版本是不支持全文索引,但在5.6之后的版本就开始支持这个功能了;行级锁(并非绝对,当执行sql语句时不能确定范围时,也会进行锁全表例如: update table set id=3 where name like ‘a%’;);不保存表的具体行数;奔溃恢复好
什么时候选择什么引擎比较好
MyISAM:
• 一般来说MyISAM不需要用到事务的时候
• 做很多count计算
InnoDB:
• 可靠性要求高的,或者要求支持事务
• 想要用到外键约束的时候(讲外键的时候会讲)
推荐:
• 推荐用InnoDB