欢迎来访~

MySQL存储引擎介绍

本文主要介绍MySQL主要存储引擎MyISAM和InnoDB的区别,特性,优缺点,应用等内容。
同时举例介绍了MyISAM和InnoDB引擎的修改切换方法。

MySQL存储引擎种类

MySQL存储引擎有MyISAM、InnoDB、MERGE、MEMORY、FEDERATED、ARCHIVE、CSV、BLACKHOLE等。
在此主要介绍今天我们常用的MyISAMInnoDB

 MyISAM

MyISAM管理非事务表,它提供高速存储和检索,以及全文搜索能力,MySQL 5.5版本以前,它是默认引擎。
MyISAM主要特性:
  • 不支持事务,宕机时会破坏表;
  • 使用较小的内存和磁盘空间,执行速度比 InnoDB类型更快;
  • 基于表的锁,表级锁;
  • mysql 只缓存index索引, 数据由OS缓存。
 
MyISAM适用于:
  • 日志系统
  • 大部分都是读的操作。 比如:门户网站,企业站点,个人博客,www.hello2099.com等
  • 没有事务 ,低并发的应用。 

InnoDB

MySQL发展到今天,InnoDB引擎已经作为绝对的主力,除了像大数据量分析等比较特殊领域需求外,它适用于众多场景。InnoDB是MySQL下使用最广泛的引擎,它是基于MySQL的高可扩展性和高性能存储引擎,从5.5版本开始,它已经成为了默认引擎。
InnoDB主要特性:
  • 支持ACID,简单地说就是支持事务完整性、一致性;
  • 支持行锁,以及类似ORACLE的一致性读,多用户并发;
  • 独有的聚集索引主键设计方式,可大幅提升并发读写性能;
  • 支持外键;
  • 支持崩溃数据自修复;
 
InnoDB适用于: 
  • 需要事务的应用
  • 高并发的应用
  • 需要自动灾难恢复的应用
  • 较快速的基于主键的操作的应用
 
注意问题
  1. 所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键,对保证性能很有帮助;
  2. 杜绝使用text/blob,确实需要使用的,尽可能拆分出去成一个独立的表;
  3. 时间建议使用 TIMESTAMP 类型存储;
  4. IPV4 地址建议用 INT UNSIGNED 类型存储;
  5. 性别等非是即非的逻辑,建议采用 TINYINT 存储,而不是 CHAR(1),bool;
  6. 存储较长文本内容时,建议采用JSON/BSON格式存储; 
 

MyISAM与InnoDB的区别

  • MyISAM不支持事务,InnoDB是事务类型的存储引擎当我们的表需要用到事务支持的时候,那肯定是不能选择MyISAM了。
  • MyISAM只支持表级锁, InnoDB支持行级锁和表级锁默认为行级锁。

表级锁:直接锁定整张表,在锁定期间,其他进程无法对该表进行写操作,如果设置的是写锁,那么其他进程读也不允许。

  • MyISAM引擎不支持外键,InnoDB支持外键。
  • InnoDB是为处理巨大数据量时的最大性能设计,它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
  • MyISAM支持全文索引(FULLTEXT),InnoDB不支持。
  • MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高。 
 

修改MySQL存储引擎

1.查看MySQL默认引擎
mysql> show engines;    我的MySQL版本是5.1.73,从第7行可以看到默认引擎是MyISAM。
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
 
 
2.修改MySQL引擎
编辑 /etc/my.cnf 文件,在 [mysqld] 后添加默认引擎配置命令 default-storage-engine=innodb,见第9行。
[root@hello2099 ~]#  vim /etc/my.cnf
 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine=innodb
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
~                                       
 
 
3.重启MySQL                                                                     
[root@hello2099 ~]# service mysqld restart
 
4.再次查看MySQL默认引擎
mysql> show engines;    从第8行可以看到,已成功将InnoDB修改成默认引擎。
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
 
 
5.创建表测试:
mysql> create table enginetest (id int,name char(4));   从第9行可以看到,修改已生效。
Query OK, 0 rows affected (0.01 sec)
 
mysql> show create table enginetest \G
*************************** 1. row ***************************
       Table: enginetest
Create Table: CREATE TABLE `enginetest` (
  `id` int(11) DEFAULT NULL,
  `name` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
 
 
6.对于已经建成的表引擎的修改
使用SQL语句:alter table tableName type=InnoDB;
举例:将 user 表的 MyISAM 引擎修改为 InnoDB,从第10行,26行可以看到,修改已生效。
mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `email` char(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
mysql>  alter table user type=InnoDB;
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> show create table user \G
*************************** 1. row ***************************
       Table: userhttps://www.hello2099.com/wp-admin/post.php?post=647&action=edit#
Create Table: CREATE TABLE `user` (
  `id` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `email` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
 

-The End-

赞(0)
可附来源转载:姜维驿站 » MySQL存储引擎介绍

富强、民主、文明、和谐、自由、平等、公正、法治、爱国、敬业、诚信、友善