1 数据库存储引擎
1.1 MySQL存储引擎简介
MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据操作。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。
1.2 简单相关知识点
1.2.1 并发处理
并发控制:当多个连接对记录进行修改时保证数据的一致性和完整性。
假如有两个用户正在操作一个数据表,A用户读取id=22的记录,B用户删除id=22的记录,那么有可能A用户操作报错或者读取的记录不正确。
在解决并发读或写操作时,系统会使用锁系统来解决。锁分为两种:
①共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
②排它锁(写锁):在任何时候只能只有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒:
①表锁,是一种开销最小的锁策略。
②行锁,是一种开销最大的锁策略。
如果存在一张表,把整张表锁定起来,所有用户将无法操作,也就是说这张表只能有一个锁;行锁,是因为数据表中有多少条记录就要多少个锁,所以开销大。
1.2.2 事务处理
事务:事务用于保证数据库的完整性。
例如:A用户转账200元到B用户,则有两个步骤,第一,从当前账户减掉200元(账户余额大于等于200元);第二,在对方账户增加200元。
两个步骤应该作为一个整体出现,无论两个过程的哪一个环节出现问题,都应该恢复到数据的原始状态,这就是一个事务。
事务的特性:ACID,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
1.2.3 外键和索引
外键:保证数据一致性的策略。
索引:对数据表中一列或多列的值进行排序的一种结构。
1.3 存储引擎的对比
(1)查看系统所支持的引擎类型:
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set
(2)存储引擎的选择
功能 | MyISAM | Memory | InnoDB | Archive |
---|---|---|---|---|
存储限制 | 256TB | RAM | 64TB | None |
支持事务 | NO | NO | YES | NO |
锁颗粒 | 表锁 | 表锁 | 行锁 | 行锁 |
支持全文索引 | YES | NO | NO | NO |
支持数索引 | YES | YES | YES | NO |
支持哈希索引 | NO | YES | NO | NO |
支持数据缓存 | NO | N/A | YES | NO |
支持外键 | NO | NO | YES | NO |
如果要提供提交,回滚和崩溃恢复能力的事务安全(ACID兼容)能力,并要求是想并发控制,InnoDB是个很好的选择;
如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率;
如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果;
如果只有INSERT和SELECT操作,可以选择Archive引擎,Archive引擎支持高并发的插入操作,但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据,如记录日志信息可以使用Archive引擎。
1.4 修改默认存储引擎
(1)查看默认存储引擎:
SHOW ENGINES;
显示的存储引擎中,有default字段即是。
SHOW VARIABLES LIKE 'storage_engine';
直接显示当前默认存储引擎。
(2)Windows设置默认引擎
方法一:
修改配置文件my.ini:首先打开my.ini,将[mysqld]字段下面的default-storage-engine参数后面的值改为相应存储引擎名,保存文件,重新启动MySQL即可。
方法二:
通过创建数据表命令实现:
CREATE TABLE table_name(
...
...
)ENGINE=engine_name;
例如:
mysql> CREATE TABLE tp1(
-> id INT
-> )ENGINE = MyISAM;
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE tp1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| tp1 | CREATE TABLE `tp1` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set
方法三:
通过修改数据表命令实现:
ALTER TABLE table_name ENGINE[=] engine_name;
例如:
mysql> ALTER TABLE tp1 ENGINE = InnoDB;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE tp1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| tp1 | CREATE TABLE `tp1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set