MySql--存储引擎、索引、视图、DBA命令

存储引擎

1.存储引擎的使用

• 数据表中的各表均被（在创建表时）指定的存储引擎来处理
• 服务器可用的引擎依赖于一下因素：

mysql的版本
服务器在开发时如何被配置
启动选项

为了解决当前服务器中有哪些存储引擎可用，可使用show engines语句：

show engines\G

2.常见的存储引擎

2.1 MyISAM存储引擎

MyISAM存储引擎是mySql最常见的引擎

它管理的表具有以下特征：

(1)使用三个文件表示每个表：

• 格式文件：存储表结构的定义（mytable.frm）
• 数据文件：存储表行的内容（mytable.MYD）
• 索引文件：存储表上索引（mytable.MYI）

(2)灵活的auto_increment字段处理

(3)可被转换为压缩、只读表来节省空间

2.2InnoDB存储引擎

InnoDB存储引擎是mysql的缺省引擎。(可以在my.ini文件中修改)

它管理的表具有下列主要特性：

• 每个InnooDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容
• 提供一组用来记录事务性活动的日志文件
• 用commit、savepoint及rollback支持事务处理
• 提供全ACID兼容
• 在mysql服务器崩溃后提供自动恢复
• 多版本（mvcc）和行级锁定
• 支持外键及引用的完整性，包括级联删除和更新

2.3 memory存储引擎

使用memory存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得memory存储引擎非常块。

memory存储引擎管理的表具有下列特性：(数据不能持久化)

• 在数据库目录内，每个表以.frm格式的文件表示
• 表数据及索引被存储在内存中
• 表级锁机制
• 不能包含text或BLOB字段

memory存储引擎以前被称为heap引擎

3.选择合适的存储引擎

• 当创建表时，应根据表的应用场景选择合适的存储引擎：
• MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAMb表的另一种适用情形是使用压缩的只读表
• 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
• 可使用memory存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

索引

1.什么是索引？

index，索引相当于一本字典的目录，索引的作用是提高程序的检索【查询】效率

2.主键自动添加索引，所以能够通过主键查询尽量通过主键查询，效率较高。

3.索引和表相同，都是一个对象，表是存储在硬盘文件中的，索引是表的一部分，索引也是存储在硬盘文件中的。

4.在mysql数据库管理系统中，对表中记录进行检索的时候，通常包括几种检索方式：

• 全表扫描【效率较低】
• 通过索引进行检索【提高查询效率】

5.一张表中有多个字段，每一个字段都是可以添加索引的。

6.什么情况下适合给表中的某些字段添加索引？

• 该字段的数据量庞大
• 该字段很少使用DML操作【DML操作很多的话，索引也是需要不断的维护的，效率反而降低】
• 该字段经常出现在where条件中

7.创建和删除索引

创建：

create index dept_dname_index on dept(dname); 
create unique index dept_dname_index on dept(dname);
//添加unique表是dept表中的dname字段添加一个唯一性约束

删除：

drop index dept_dname_index on dept;

视图

1.什么是视图

• 视图是一种根据查询（也就是select表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
• 视图有时也被称为“虚拟表”
• 视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。

相对于从基表中直接获取数据，视图有一下好处：

访问数据变得简单
可被用来对不同用户显示不同的表内容

用来协助适配表的结构以适应前端现有的应用程序

2.视图的作用：

• 提高检索效率
• 隐藏表的实现细节【面向视图检索】

DBA命令

1.新建用户

create user username identified by 'password';
username:你将创建的用户名
password:该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器

例：create user p361 identified by '123';

2.授权

grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
dbname=*表示所有数据库
tbname=*表示所有表
login ip=%表示任何ip
password为空，表示不需要密码即可登录
with grant option;表示该用户还可以授权给其他用户

3.回收权限

revoke privileges on dbname[.tbname] from username;
revoke all privileges on *.* from p361;

4.导入导出

导出：

导出整个数据库（mydb）
mysqldump mydb>D:\mydb.sql -uroot -p123

导出指定库下的指定表（emp表）
mysqldump mydb emp>D:\mydb.sql -uroot -p123

导入：

source 路径
source D:\mydb.sql