Mysql存储引擎
Mysql的存储引擎表示的是Mysql中数据库表的存储类型,不同存储类型,表的存储方式都不相同。
查看当前MySQL版本支持哪些引擎,可以输入以下命令:
mysql> show engines\G
可以发现有很多存储引擎,这里只讨论InnoDB、MyISAM、MEMORY这三种。
存储引擎类型
- InnoDB
InnoDB的表存储成3个文件。文件的名字与表名相同。拓展名为frm、idb,.frm存储表的结构, .idb存储表的数据和索引 - MyISAM
MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、myd、myi,
.frm存储表的结构 ,.myi表的索引, .myd表的数据 - MEMORY
MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表,而且数据全部放在内存中。
存储引擎的区别
- InnoDB:
支持行锁,支持B-树索引,不支持哈希索引,支持外键,支持事务,支持索引缓存, 支持数据缓存。 - MyISAM:
支持表锁,支持B-树索引,不支持哈希索引,不支持外键,不支持事务,支持索引缓存,不支持数据缓存. - MyISAM:
MEMORY:支持表锁,支持B-树索引,支持哈希索引,不支持外键,不支持事务,支持索引缓存,支持数据缓存。
总结:
-
InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)。缺点是读写效率较差,占用的数据空间相对较大。
-
MyISAM的优势在于占用空间小,处理速度快。如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。缺点是不支持事务的完整性和并发性。
-
MEMOEY:所有的数据都在内存中,数据的处理速度快,但是安全性不高。如果需要很快的读写速度,对数据的安全性要求较低,可以选择MEMOEY。它对表的大小有要求,不能建立太大的表。所以,这类数据库只使用在相对较小的数据库表。
改变存储引擎
方式一:在创建表的时候指定存储引擎
CREATE TABLE table_name (
属性名 数据类型 完整性约束,
...
属性名 数据类型 完整性约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
方式二:修改已存在表的存储引擎
ALTER TABLE table_name ENGINE = InnoDB;
方式三:修改配置文件
MySQL Server启动的时候都会加载一个配置文件,windows下是my.ini文件,linux下是my.cnf文件,打开配置文件,在[mysqld]下面添加如下内容,保存,重启MySQL Server服务器,默认就采用配置项指定的存储引擎了。记得操作之后一定要保存再重新启动Mysql服务
索引
索引是创建在表上,是对数据库表中一列或多列的值进行排序的一种结构。索引和表中属性有很大关系,要通过属性排序后的结果建立结构。
索引结构存储在文件中:InnoDB引擎下:.ibd文件 MyISAM:.myi文件。
索引有两种存储类型:B树形索引 、哈希索引;InnoDB和MyISAM存储引擎支持B树形索引,MEMORY支持B树形索引、哈希索引,默认B树形索引。
索引的优点:提高查询速度
索引的缺点:索引需要占用物理空间 ,索引存储在文件中,索引多了,存储的数据变少了,创建和维护索引需要耗费时间 频繁修改表中数据,也会造成索引结构的频繁修改,消耗时间,性能降低。
索引的分类以及它们的创建和删除
- 普通索引:创建普通索引时,不添加任何限制条件,对属性完整性约束没有要求
(建立表的同时建立索引)
create table table_name(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
index(属性名)
);
- 唯一性索引:使用unique参数设置索引为唯一性索引。必须保证属性是唯一的(即属性有主键约束或者唯一性约束)。主键是特殊的唯一性索引
(建立表的同时建立索引)
create table 表名(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
unique index 索引名(属性名)
);
- 全文索引:使用fulltext参数设置索引为全文索引。全文索引只能创建在char、varchar、text类型的字段上。查询数据量较大的字符串类型字段,使用全文索引可以提高查询效率.只有MyISAM存储引擎支持全文索引。
(建立表的同时建立索引)
create table 表名(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
fulltext index 索引名(属性名)
)engine=MyISAM;
- 单列索引:在表中单个字段建立索引。单列索引只根据该列进行查询
(建立表的同时建立索引)
create table 表名(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
index 索引名(属性名)
);
- 多列索引:在表中多个字段建立索引。
(建立表的同时建立索引)
create table 表名(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
index 索引名(属性名1,属性名2,...)
);
- 空间索引:使用spatial参数设置索引为空间索引。空间索引只能建立在空间数据类上。
空间数据类型包括geometry、point、linestring、polygon等。只有MyISAM存储引擎支持空间数据检索,且索引字段不能为空值。
create table 表名(
属性名 数据类型 完整性约束,
....
属性名 数据类型 完整性约束,
spatial index 索引名(属性名)
)engine=MyISAM;
在已存在的表上建立索引
create (unique\fulltext\spatial) index 索引名 on 表名 (属性名);
alter table 表名 add (unique\fulltext\spatial) index 索引名(属性名);
删除索引:
drop index 索引名 on 表名;
索引的设计原则
(1)如果表中的数据较少,不需要建立索引
(2)选择唯一性索引。属性不重复
(3)为经常需要排序、分组和联合操作的字段建立索引
(4)为经常作为查询条件的字段建立索引
(5)限制索引的数目
(6)尽量使用数据量少的索引
(7)尽量使用前缀来索引
(8)删除不再使用或使用很少的索引