MySQL系列（一）之存储引擎，建库及账号管理

一. MySql的存储引擎

1.1 什么是存储引擎？

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

存储引擎这个概念似乎只有mysql有，存储引擎不同，mysql呈现出来的特性也不同，适用场景就不同。其他数据库，没有听说过有存储引擎这个概念。但应该也有类似的机制。比如oracle、sql server都有OLTP（联机事务处理）、OLAP（联机分析处理）两种类型，分别适用于业务系统应用，数据仓库，可在安装时进行选择。

MySQL的核心就是存储引擎

1.2 MySql的常见存储引擎

1.2.1 InnoDB(MySQL默认引擎)

InnoDB是一个健壮的事务型存储引擎

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。

InnoDB存储引擎的特点：支持自动增长列，支持外键约束

1.2.2 MyISAM

相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立与操作系统，可以在windows及linux上使用。不过和InnoDB不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提 交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。 但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用 一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

1.2.3 MEMORY（Heap）

使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。 Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

1.3 存储引擎查看

1.3.1 查看存储引擎

SHOW ENGINES

1.3.2 查看默认引擎

SHOW VARIABLES LIKE 'default_storage_engine'

1.3.3 查看某个库下所有表使用的存储引擎

SHOW TABLE STATUS FROM 库名

1.3.4 查看某个库下指定表使用的存储引擎

SHOW TABLE STATUS FROM 库名 WHERE NAME='表名'

二. MySql建库

2.1 默认数据库介绍

• mysql

这是MySQL的系统管理数据库，它存储了用户、权限和其他系统级别的元数据。在这个数据库中，包含了可以控制用户授权以及审核日志的表。例如，用户可以使用mysql.user表来添加、删除、修改数据库账户的权限以及修改密码。同时，系统的所有日志信息也在mysql数据库中进行存储。

• information_schema

这是一个元数据数据库，它包含了MySQL的全部数据库对象（表、列、约束、函数、存储过程等）的描述信息。在这个数据库中，包含了获取数据库信息的各种表。例如，用户可以通过查询information_schema.tables表获取当前数据库中所有的表信息。

• performance_schema

这是MySQL的性能分析数据库，它提供了大量有用的性能统计数据和信息，以帮助用户优化SQL查询。在这个数据库中，包含了各种用于审计和分析SQL语句性能的表。例如，用户可以使用performance_schema.events_statements_summary_by_digest表获取SQL语句执行的摘要数据。

• sys

MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图，数据都来自于performation_schema，主要是让开发者和使用者更方便地查看性能问题。

2.2 建库

在MySQL中，可以使用 CREATE DATABASE 语句创建数据库，语法格式如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

[]中的内容是可选的。语法说明如下：

• <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
• IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
• [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
• [DEFAULT] COLLATE：指定此数据库的默认排序规则。

2.3 查看数据库

在 MySQL 中，可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。查看数据库的语法格式为：

SHOW DATABASES [LIKE '数据库名'];

语法说明如下：

• LIKE 从句是可选项，用于匹配指定的数据库名称。LIKE 从句可以部分匹配，也可以完全匹配。
• 数据库名由单引号' '包围。

2.4 删除数据库

在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

语法说明如下：

• <数据库名>：指定要删除的数据库名。
• IF EXISTS：用于防止当数据库不存在时发生错误。
• DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

注意：MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

三. 账号管理

3.1 创建用户

MySQL 在安装时，会默认创建一个名为 root 的用户，该用户拥有超级权限，可以控制整个 MySQL 服务器。

3.1.1 创建用户并设置登录密码

MySQL 提供了以下 3 种方法创建用户

1. 使用 CREATE USER 语句创建用户
2. 在 mysql.user 表中添加用户
3. 使用 GRANT 语句创建用户
   使用 CREATE USER 语句创建用户
   可以使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。其基本语法格式如下：

CREATE USER <用户> [ IDENTIFIED BY [ PASSWORD ] 'password' ] [ ,用户 [ IDENTIFIED BY [ PASSWORD ] 'password' ]]

参数说明如下：

1. 用户：指定创建用户账号，格式为 user_name'@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。
   
2. IDENTIFIED BY子句：用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。
   
3. PASSWORD ‘password’：PASSWORD 表示使用哈希值设置密码，该参数可选。
   

3.1.2 修改密码

SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。使用 SET 语句修改密码的语法结构如下：

SET PASSWORD = PASSWORD ("rootpwd");

3.2 查看用户

#切换数据库
use mysql;
#查询用户信息
select host,user,authentication_string from user;

MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。

host参数值说明：

3.3 删除用户

使用 DROP USER 语句删除用户的语法格式如下：

DROP USER <用户1> [ , <用户2> ]…

注意：用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。

3.4 用户授权

在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

参数说明：

• priv_type 参数表示权限类型；
• columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
• database.table 用于指定权限的级别；
• user 参数表示用户账户，由用户名和主机名构成，格式是“‘username’@‘hostname’”；
• IDENTIFIED BY 参数用来为用户设置密码；
• password 参数是用户的新密码。

WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

• GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
• MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
• MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
• MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
• MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

3.5 用户撤权

删除用户某些特定的权限，语法格式如下：

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：

• priv_type 参数表示权限的类型；
• column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上；
• user 参数由用户名和主机名构成，格式为“username’@‘hostname’”。

3.6 查看用户权限

使用 SHOW GRANTS FOR 语句查看权限。其语法格式如下：

SHOW GRANTS FOR 'username'@'hostname';

其中，username 表示用户名，hostname 表示主机名或主机 IP。