MySQL 笔记

MySQL 的数据目录

Windows 和 Linux 规则区别

# 查看当前大小写敏感参数
show variables like '%lower_case_table_names%';

# 默认为 0 ，大小写敏感
# 设置为 1 ，大小写不敏感，创建表都是以小写形式存储在磁盘上，对于 sql 语句也都是转换为小写对表和数据库进行查找
# 设置为 2 ，创建表和数据库依据语句格式存放，凡是查找都是转化为小写进行。

• 平台大小写区别：
  • Linux下 数据库名、表名、列名、别名大小写规则如下：
    • 数据库名、表名、别名、变量名都是严格区分大小写的
    • 关键字、函数名在 SQL 中不区分大小写
    • 列名与列的别名在所有情况下均忽略大小写

在 Linux 下的目录结构

数据库文件存放路径：/var/lib/mysql/
相关命令目录：/usr/bin/  和  /usr/sbin/
配置文件目录：/usr/share/mysql-8.0（命令及配置文件）  和  /etc/mysql

MySQL 的默认数据库

• mysql

  存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，时区信息等。

• information_schema

  维护所有其他数据库信息，例如有哪些表、视图、触发器、列、索引等。这些信息并不包含真实用户数据，而是些描述信息，有时候也称之为元数据。其中 innodb_sys 开头的表用于表示内部系统表。

• performance_schema

  存储MySQL服务器运行过程中的状态信息，可以用来监控MySQL服务器各类性能指标。包括统计最近执行的语句，执行过程中每个阶段花费了多长事件，内存使用情况等。

• sys

  主要是通过视图的形式将 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术

  性能。

数据库在文件中的表示

存储视图时，对应文件表示仅有 .frm 而无数据存储文件，这也侧面说明了视图不存储数据，仅供显示。

MySQL 5.7

1. InnoDB 存储引擎

   • db.opt：存放对应数据库的信息，例如数据库的字符集、使用的比较规则。

   • *.frm：存放的是表结构，例如字段、字段类型、字段约束信息等。

   • *.ibd：存放表中数据。5.6.6 之后默认存放此后缀文件（称独立表空间），而 5.5.7 ~ 5.6.6 版本存放的是 ibdata1 自扩展文件中（默认大小为 12M，称之系统表空间）

     • 可以通过 SHOW VARIABLES LIKE 'innodb_file_per_table'; 来查询当前存放规则（ON 表示使用独立表空间）

     • 可以通过配置文件设置：

       [server]
       innodb_file_per_table=0 # 0 表示使用系统表空间； 1 表示使用独立表空间

2. MyISAM 存储引擎

   • *.frm：存储表结构
   • *.MYD：存储数据
   • *.MYI：存储索引

MySQL 8.0

1. InnoDB 存储引擎

   • *.ibd：表结构和表数据信息合一，即 5.7 时的 *.frm 和 *.ibd 到 8.0 时两者合并为 *.ibd

2. MyISAM 存储引擎

   • *.sdi：相当于老版本中的 *.frm 文件，存储表结构
   • *.MYD、*.MYI：分别存储数据、索引信息

用户与权限管理

用户管理

USE mysql;
# 创建用户
CREATE USER ['username']@['host'] IDENTIFIED BY 'abc123'; # 默认 host 为 %


# 修改用户名（需要刷新 FLUSH PRIVILEGES)
UPDATE mysql.user SET user='li4' WHERE user='wang5' and host='localhost';

# 刷新权限
FLUSH PRIVILEGES;


# 删除用户名
# 1. DROP 方式删除（推荐，不需要 FLUSH PRIVILEGES）
DROP USER  user[, user, ...];

# 2. DELETE 方式删除（不推荐，系统可能有残留，需要 FLUSH PRIVILEGES）
DELETE FROM mysql.user WHERE host='hostname' AND user='username';


# 修改当前用户密码（8.0 将 PASSWORD() 函数移除了，所以不推荐使用此函数进行修改密码）
# 1.使用 ALTER USER 进行修改
ALTER USER USER() IDENTIFIED BY 'new_password';

# 2.使用 SET 进行修改
SET PASSWORD='new_password';

# 修改其它用户密码
# 1.使用 ALTER
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

# 2.使用 SET
SET PASSWORD FOR 'username'@'hostname'='new_password';

# 3.使用 UPDATE（不推荐，就不举例了）


# 设置密码过期策略
# 1.手动设置密码过期（可以登入，但无法进行查询，重新设置密码后反可）
ALTER USER 'username'@'hostname' PASSWORD EXPIRE;


# 2.手动指定过期时间
# default_password_lifetime 默认值为 0，表示禁用自动密码过期，允许正整数 N，表示每隔 N 天过期
# 全局策略，每 180 天过期
SET PERSIST default_password_lifetime = 180;

# 单独策略(INTERVAL 90 DAY 表示 90 天后过期， NEVER 表示永不过期，DEFAULT 延用全局策略)
# 创建时指定
CREATE USER 'username'@'hostname' PASSWORD EXPIRE INTERVAL 90 DAY;
# 单独修改某个用户
ALTER USER 'username'@'hostname' PASSWORD EXPIRE DEFAULT;


# 设置密码重用策略
# 全局策略
# 不能使用最近使用过的 6 个密码
SET PERSIST password_history = 6；
# 不能选择最近一年内的密码
SET PERSIST password_reuse_interval = 365;

# 单独设置
CREATE USER 'username'@'hostname' PASSWORD HISTORY 5;
ALTER USER 'username'@'hostname' PASSWORD REUSE INERVAL 365 DAY;

权限管理

# 查看 MySQL 中的所有权限
SHOW PRIVILEGES;

# 查看当前用户权限
SHOW GRANTS;

# 查看某用户全局权限
SHOW GRANTS FOR 'username'@'hostname';

# 授权命令（若发现无此用户，则会创建拥有相应权限的用户）
GRANT 权限1,... ON 数据库名.表名 TO 'username'@'hostname' IDENTIFIED BY '密码口令';

# 回收权限
REVOKE 权限1,... ON 数据库名.表名 FROM 'username'@'hostname';

角色管理

MySQL 8.0 新增，角色就是权限的集合，可以为角色添加或删除权限。

# 创建角色(hostname 默认值为 %)
CREATE ROLE 'role_name'[@'hostname'] [,'role_name'[@'hostname']] ...;


# 赋予角色权限
GRANT 权限1[,...] ON 表名 TO 'role_name'[@'hostname'];


# 查看角色权限
SHOW GRANTS FOR 'role_name'@'hostname';

# 回收角色权限
REVOKE 权限1[,...] ON 库名.表名 FROM 'role_name';

# 删除角色
DROP ROLE 'role_name'[, ...];


# 给用户赋予角色（可同时将多用户，赋予多角色）
GRANT 'role_name'[, ...] TO 'username'[, ...];


# 查看当前用户的已激活的角色(PS：需要重登才能看到赋予激活后的角色)
SELECT CURRENT_ROLE();

# 激活角色（为用户激活所有已拥有的角色）
SET DEFAULT 'role_name' ALL TO 'username'@'hostname';


# 设置全局角色永久激活（只要赋予，就默认激活）
SET GLOBAL activate_all_roles_on_login = ON;


# 撤销用户角色（重登后有效）
REVOKE 'role_name' FROM 'username';

MySQL 逻辑架构

架构刨析

第一层：连接层

主要负责获取服务器与 MySQL 连接的，经过三次握手建立连接后，MySQL 对 TCP 传输过来的账号密码进行身份认证、权限获取。

• 用户或密码错误，会抛出 Access denied for user 的错误，客户端结束执行
• 验证通过，会从权限表中查询出该账号拥有的权限与连接关联，之后权限判断逻辑都将依赖此处读取到的权限信息

MySQL 服务器中有专门的 TCP 连接池来管理连接数量（长连接），有线程池来管理数据库操作时的线程。

第二层：服务层

主要负责完成大多数核心服务功能，例如 SQL接口，并完成缓存查询，SQL分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在此层实现，如过程、函数等。

• SQL Interface：SQL接口

  • 接收用户 SQL 命令，并返回用户需要的查询结构。例如 SELECT … FROM … 就是调用 SQL Interface 来完成的。

• Parser：解析器

  • 对 SQL 语法、语义进行分析。将 SQL 语句分解成数据结构，并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于此结构的。若在分解构成中遇到错误，那么说明此 SQL 语句是不合理的。

  • 在 SQL 命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL 还会对 SQL 查询进行语法上的优化，进行查询重写。

• Optimizer：查询优化器

  • SQL 语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。

  • 这个执行计划表明应该使用哪些索引进行查询，表之间连接顺序如何，最后会按照执行计划中的步骤调用存储引擎的方法来真正的执行查询，并将查询结构返回给用户。

  • 它使用“选取-投影-连接”的策略来进行查询。例如：

    SELECT id, name FROM student WHERE gender = '女';

    此 SELECT 查询先根据 WHERE 进行选取，而不是将表全部查询出来以后再进行 gender 过滤。

    此 SELECT 查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤。

    将以上两个查询条件连接起来，生成最终查询结果。

• Caches & Buffers：查询缓存组件

  • MySQL 内部维持着一些 Cache 和 Buffer，比如 Query Cache 用来缓存一条 SELECT 语句的执行结果，如果能够在其中找到对应查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
  • 整个缓存机制是由一系列小缓存组成的。比如表缓存、记录缓存、Key缓存、权限缓存等。
  • 这个查询缓存可以再不同客户端之间共享。
  • 从 MySQL 5.7.20 开始，不推荐使用查询缓存，并在 MySQL 8.0 中移除。

第三层：引擎层

主要负责与数据库文件的存储和读取。插件式的存储引擎架构将查询处理和其它系统任务以及数据存储提取相分离。这种架构可以根据业务需求和实际需要选择合适的存储引擎，同时开源的 MySQL 还支持开发人员设置自己的存储引擎。

MySQL 8.0 中默认支持的存储引擎可以通过 SHOW ENGINES; 命令查看。

底层执行流程

MySQL的查询流程

1. 查询缓存：（8.0中被移除） Server 若在查询缓存中发现了此条 SQL 语句，就会直接将结果返回给客户端，若没有，就进入解析器阶段。因为两次查询要完全一致才能命中缓存，而且查询请求中不能包含系统函数、用户自定义变量和函数、系统表，否则就不会被缓存。另外 MySQL 缓存系统会检测涉及到的每张表，只要检测到有表发生改变，那么就会使得该表所有高速缓存查询变为无效的缓存查询并删除。
   所以此缓存可用性极低，往往弊大于利，移除合情合理。

   默认情况下也需要手动进行开启，设置 query_cache_type 参数值（0 表示关闭，1 表示开启，2表示按需使用）。

2. 解析器： SQL 语句分析分为词法分析和语法分析，首先分析器会先做词法分析，结束后再做语法分析。

   • 词法分析：MySQL 需要识别出里面的字符串分别是什么，代表什么，例如识别 SELECT 关键字、表名、列名等。
   • 语法分析：根据词法分析的结果，语法分析器会根据语法规则判断你输入的 SQL 语句是否满足 MySQL 语法。
     • 若语法有误，就会抛出”You have an error in your SQL syntax“错误提醒。
     • 若SQL语句正确，则会生成一颗语法树

3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。一条查询语句可以有多种执行方式，但返回的结果是相同的。优化器的作用就是找出这其中最好的执行计划。

   • 查询优化器中可分为逻辑查询和物理查询优化阶段

     • 逻辑查询优化阶段

       通过改变 SQL 语句来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选计划。通常采用的方式是对 SQL 语句进行等价变换，对查询进行重写。

     • 物理查询优化阶段

       基于关系代数进行查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各物理路径的代价，从中选择代价最小的作为执行计划。

     优化器仅仅生成了执行计划，还未真正进行执行。

4. 执行器： 在执行之前判断该用户是否具备权限，若没有则返回权限错误。若具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0 以下版本，如果设置了查询缓存，此时会将查询结果进行缓存。

存储引擎

InnoDB 引擎

优点：事务、并发写

• 支持外键和事务的存储引擎，支持行锁，适合高并发的操作。
• 若除了增加和查询，还需要删除、修改操作时优先选择 InnoDB 存储引擎。
• 缓存对内存要求较高，需要缓存索引已经真实数据。

MyISAM 引擎

优点：节省资源、消耗少、业务简单

• 不支持外键、事务，以及使用的是表锁。
• 缓存要求较低，只缓存索引。