MySQL存储引擎

最新推荐文章于 2024-10-31 23:41:03 发布

想个名字糊糊

最新推荐文章于 2024-10-31 23:41:03 发布

阅读量781

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_82109773/article/details/140064663

版权

一、存储引擎：

（1）定义：

存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。

（2）常用的存储引擎：

InnoDB MyISAM

（3）MyISAM 和 InnoDB 的区别？

	MyISAM：	InnoDB：
	不支持事务、外键约束；支持全文索引	支持事务、外键约束；也支持全文索引；
	锁定类型只支持表级锁定；适合单独的查询和插入的操作；	锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；
	读写会相互阻塞；	读写并发能力较好；
	硬件资源占用较小	缓存能力较好可以减少磁盘IO的压力；
	数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI	数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
使用场景：	适用于不需要事务支持，单独的查询或插入数据的业务场景	适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景

（4）InnoDB行锁与索引的关系：

InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。

（5）MySQL 查询数据的执行过程：

客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据执行计划，调用存储引擎来执行查询。
将结果返回给客户端，同时缓存查询结果。

（6）查看存储引擎：

show create table 表名;
show table status [from 库名] where name = '表名';

1. 查看系统支持的存储引擎

show engines;

2. 查看表使用的存储引擎

方法一：

show table status from 库名 where name='表名'\G

mysql> show table status from xy where name='xy108';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| 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 |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| xy108 | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |        32768 |         0 |           NULL | 2024-06-28 16:41:48 | 2024-06-29 11:33:47 | NULL       | utf8_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql>

mysql> show table status from xy where name='xy108'\G
*************************** 1. row ***************************
           Name: xy108
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-06-28 16:41:48
    Update_time: 2024-06-29 11:33:47
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql>

方法二：

use 库名;

show create table 表名;

mysql> use xy;
Database changed
mysql> show create table xy108;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xy108 | CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show create table xy108\G;
*************************** 1. row ***************************
       Table: xy108
Create Table: CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

（7）存储引擎管理操作：

alter table 表名 engine=innodb/myisam; #针对已存在的表修改存储引擎

create table 表名 (....) engine=innodb/myisam; #新建表时指定存储引擎

set global/session default_storage_engine=innodb/myisam; #设置默认存储引擎

vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM

修改存储引擎

通过 alter table 修改

use 库名;

alter table 表名 engine=引擎名

例：将xy108表的引擎修改为MyISAM

use xy;
	##使用xy库
	
alter table xy108 engine=MyISAM;
	##修改account表的引擎为MyISAM

mysql> use xy;
Database changed
mysql> alter table xy108 engine=MyISAM;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table xy108;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| xy108 | CREATE TABLE "xy108" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" char(22) DEFAULT NULL,
  KEY "tan_index" ("sex","age","name"),
  FULLTEXT KEY "108_name" ("name")
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务

vim /etc/my.cnf
	......
	[mysqld]
	......
	default-storage-engine=引擎名

systemctl restart mysqld
##注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。

例：将默认的引擎修改为MyISAM

## MySQL内
show engines;
##
##系统命令
vim /etc/my.cnf
	......
	[mysqld]
	......
	default-storage-engine=MyISAM
		##默认的引擎为MyISAM

systemctl restart mysqld
##
## MySQL内
show engines;
##

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.00 sec)

[root@l5 ~]# systemctl restart mysqld.service
[root@l5 ~]# mysql -uroot -pabc123
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | YES     | 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             | DEFAULT | 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.00 sec)

mysql>

通过 create table 创建表时指定存储引擎

USER 库名;

CREATE TABLE 表名(字段1 数据类型,...) ENGINE=引擎名;

例：在xy库中创建存储引擎为MyISAM的home表

use xy
	##使用xy库
create table home(id int, name varchar(50)) engine=MyISAM;
	##创建home表字段1为id，字段2为name

show create table home;
	##查看home表属性

mysql> use xy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table home(id int, name varchar(50)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table home;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| home  | CREATE TABLE "home" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

三、死锁：

（1）定义：

死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。

（2）如何避免死锁？

1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多血少的场景下采用乐观锁机制