MySql存储引擎和索引

Mysql存储引擎

Mysql的存储引擎表示的是Mysql中数据库表的存储类型，不同存储类型，表的存储方式都不相同。
查看当前MySQL版本支持哪些引擎，可以输入以下命令：

mysql> show engines\G

可以发现有很多存储引擎，这里只讨论InnoDB、MyISAM、MEMORY这三种。

存储引擎类型

1. InnoDB
   InnoDB的表存储成3个文件。文件的名字与表名相同。拓展名为frm、idb，.frm存储表的结构， .idb存储表的数据和索引
2. MyISAM
   MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、myd、myi,
   .frm存储表的结构 ，.myi表的索引， .myd表的数据
3. 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树形索引。
索引的优点：提高查询速度
索引的缺点：索引需要占用物理空间 ，索引存储在文件中，索引多了，存储的数据变少了，创建和维护索引需要耗费时间 频繁修改表中数据，也会造成索引结构的频繁修改，消耗时间，性能降低。

索引的分类以及它们的创建和删除

1. 普通索引：创建普通索引时，不添加任何限制条件，对属性完整性约束没有要求
   （建立表的同时建立索引）

create table table_name（
属性名 数据类型 完整性约束,  
....
属性名 数据类型 完整性约束,
index(属性名)
）;

2. 唯一性索引：使用unique参数设置索引为唯一性索引。必须保证属性是唯一的（即属性有主键约束或者唯一性约束）。主键是特殊的唯一性索引
   （建立表的同时建立索引）

create table 表名（
属性名 数据类型 完整性约束, 
....
属性名 数据类型 完整性约束,
unique index 索引名(属性名)
）;

3. 全文索引：使用fulltext参数设置索引为全文索引。全文索引只能创建在char、varchar、text类型的字段上。查询数据量较大的字符串类型字段，使用全文索引可以提高查询效率.只有MyISAM存储引擎支持全文索引。
   （建立表的同时建立索引）

create table 表名（
属性名 数据类型 完整性约束, 
....
属性名 数据类型 完整性约束,
fulltext index 索引名(属性名)
）engine=MyISAM;

4. 单列索引：在表中单个字段建立索引。单列索引只根据该列进行查询
   （建立表的同时建立索引）

create table 表名（
属性名 数据类型 完整性约束, 
....
属性名 数据类型 完整性约束,
index 索引名(属性名)
）;

5. 多列索引：在表中多个字段建立索引。
   （建立表的同时建立索引）

create table 表名（
属性名 数据类型 完整性约束,  
....
属性名 数据类型 完整性约束,
index 索引名(属性名1,属性名2，...)
）;

6. 空间索引：使用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）删除不再使用或使用很少的索引