【MySQL存储引擎】

存储引擎

存储引擎是：MySQL特有的，其它数据库没有
存储引擎的本质 l 通过采用不同的技术将数据存储在文件或内存中；
每一种技术都有不同的存储机制，不同的存储机制提供不同的功能和能力；
通过选择不同的技术，可以获得额外的速度或功能，改善我们的应用；

查看存储引擎 show engines\G

创建存储引擎

在创建表时，可使用< ENGINES> 选项为CREATE TABLE语句显示指定存储 引擎
create table tname( no int ) engine=mylsam

如果创建表时没有指定存储引擎，则使用当前默认的存储引擎

默认的存储引擎可在 my.ini 配置文件中使用 default-storage-engin 选项指 定

修改表的存储引擎使用：ALTER TABLE 表名 ENGINE = 存储引擎名称

查看表使用的存储引擎，命令如下：
SHOW CREATE TABLE emp\G;
SHOW TABLE STATUS LIKE ‘emp’\G;

常用的存储引擎

**MyISAM存储引擎 **
MyISAM引擎是MySQL数据库最常用的；
它管理的表具有以下特性：
使用三个文件表示每个表：
a) 格式文件 — 存储表的结构（mytable.frm）
b) 数据文件 — 存储表的数据（mytable.MYD）
c) 索引文件 — 存储表的索引（mytable.MYI） 可转换为压缩、只读表来节省空间

InnoDB存储引擎
它管理的表具体有以下特征：
a) 每个InnoDB表在数据库目录中以.frm格式文件表示
b) InnoDB表空间tablespace被用于存储表的内容
c) 提供一组用来记录事务性活动的日志文件
d) 用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务 处理
e) 提供全部ACID兼容
f) 在MySQL服务器崩溃后提供自动恢复
g) 多版本（MVCC）和行级锁定
h) 支持外键及引用的完整性，包括级联更新和删除

MEMORY存储引擎
使用MEMORY存储引擎的表，因为数据存储在内存中，且行的长度固定，所以 使得MEMORY存储引擎非常快；
MEMORY存储引擎管理的表具有下列特征：
a) 在数据库目录内，每个表均以.frm格式文件表示；
b) 表数据及索引被存储在内存中；
c) 表级锁机制；
d) 字段属性不能包含TEXT或BLOB字段；
MEMORY存储引擎以前被称为HEAP引擎；

DBA命令

修改密码 进入mysql库：
use mysql;
update user set password = password(‘123456’) where user = ‘root’;
或者 ALTER USER ‘root’@‘localhost’ identified by ‘123456’;
或者 set password for ‘root’@‘localhost’=password(‘123456’);

刷新权限 flush privileges;

索引

MySQL数据库中表的检索方式有2种：
全表扫描（效率较低）
通过索引检索（提高查询效率）

索引对应的英文单词：index
索引的作用：相当于一本字典目录，提高程序的检索 / 查询效率；表中每一个字 段都可添加索引

索引的优点： 1、通过创建唯一性的索引，可以保证表中数据的唯一性; 2、加速 数据的检索速度; 3、加快表与表之间的连接; 4、在使用分组与排序数据检索时， 可以显著检索分组与排序的时间; 5、在查询的过程中提高系统性能。

索引的缺点： 1、创建索引需要时间，且随着数据量的增加而增加; 2、索引需要 占用物理空间;3、当对表中数据进行修改时，索引也要动态维护，降低了数据的 维护速度。

什么情况下适合给表中字段添加索引
该字段数据量庞大
该字段很少的DML操作（由于索引也需要维护，DML操作多的话，也影响 检索效率）
该字段经常出现在where条件中

注：实际开发中会根据项目需求以及客户需求等综合因素来做调整

创建索引

创建索引，语法结构：
a） create index 索引名 on 表名（列名）
b） create unique index 索引名 on 表名（列名）

注：添加unique表示在该表中的该列添加一个唯一性约束 l 示例：create index dept_dname_index on dept(dname);

索引的应用

查看索引，
语法结构： show index from 表名
show index from dept;

删除索引，
语法结构： drop index 索引名 on 表名;
drop index dept_dname_index on dept;

MySQL添加约束、删除约束及修改约束

MySQL添加约束
添加主键约束：alter table 表 add constraint 约束名称 primary key(主 键字段);
alter table t_student add constraint pk primary key(student_id);

添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从 表(外键字段) references 主表(主键字段);
alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);

添加唯一性约束：alter table 表 add constraint 约束名称 unique 表(字段);
alter table t_student add constraint uk unique(email)

MySQL修改约束，其实就是修改字段 alter table t_student modify student_name varchar(30) unique;

mysql对有些约束的修改不支持，所以我们可以先删除，再添加

MySQL删除约束
删除主键约束：alter table 表名 drop primary key ; alter table t_student drop primary key;

删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）; alter table t_student drop foreign key fk_classes_id;

删除其他约束：alter table 表名 drop key 约束名称 ; alter table t drop key uk;