Day02 MySQL 体系与用户管理

本节关键词

• Server 三层抽象
• 用户管理(创建、修改、锁定与删除)

体系

实例

mysqld + Master Thread + Worker Thead + MEM

处理流程

连接层 -> SQL层 -> Engine层

• 连接层 mysql -uroot -p123456

1. 连接的协议：两种方式
    TCP/IP
    Socket文件
2. 用户验证功能：加载授权表，进行验证
    mysql.user
    mysql.db
    mysql.tables_priv
    mysql.columns_priv
3. 提供连接线程：为客户端提供连接
    查看前台连接    show processlist;
    查后后台线程    select * from performance_schema.threads\G;

• SQL层 select * from t1;

1. 预处理：语法、语义、权限
2. 解析：生成解析树，AST抽象语法树
3. 优化：
    逻辑优化 -> 优化器算法，关系代数的等价转换
    物理优化 
        参考统计信息
        优化器算法
        Cost代价评估
4. 执行 执行计划 explain

• Engine层 与磁盘交互，组织存储数据

如 InnoDB
1. In-Memory Structures
2. On-Disk Structures

ls -al /data/3306/data
cd aaa  # 目录
ibd2sdi stu.ibd # 文件，8.0.26自带工具可以查看，元数据+数据行

8.0 表存储方式：每张表的数据都存储在ibd
5.7 表存储方式：
    数据行 ibd
    元数据 frm + ibdata1

所以 8.0 DDL原子性、Online DDL更快速

问答：一条SQL语句，昨天执行好好的（0.01s），突然遍慢了（2min）为什么呢

统计信息不准确，不走索引造成的

统计信息记录的是，表的状态，比如表名、数据行、数据分布、索引状态信息
统计信息是MySQL定期收集的，通过一些参数指标控制，不是实时收集

# 存在相关库表
mysql.innodb_index_stats
mysql.innodb_tables_stats

# 经过命令优化
mysql> analyze table world.city;
mysql> optimize table world.city;
mysql> alter table world.city engine=innodb;

用户管理

# 用户功能
登录数据库
管理数据库对象

# 用户组成 包含两部分
用户名@'白名单'

白名单组成：
    %
    10.0.0.10
    10.0.0.%
    10.0.0.5%
    10.0.0.0/255.255.255.254.0
    ni-ning.com
    127.0.0.1
    db01 --> 别名
    localhost --> socket

# 用户管理
## 8.0 密码插件 with caching_sha2_password
## 5.7 密码插件 with mysql_native_password 简单的会暴力破解
select user, host, authentication_string, plugin from mysql.user;   

# 创建用户 附带密码以及密码插件
create user nining@localhost identified by '123456';
create user admin@localhost identified with mysql_native_password by '123';

# 修改用户
alter user admin@localhost identified with mysql_native_password by '123456';

# 锁定用户
alter user user1@localhost account lock;
select user, host, account_locked from mysql.user; 
alter user user1@localhost account unlock;

# 删除用户，推荐先锁定用户
drop user user1

# 忘记root密码 原理：关闭连接层验证功能
1. 关闭数据库
2. 安全方式启动数据库 mysqld_safe --skip-grant-tables --skip-networking &
3. 登录数据库 mysql
4. 刷新授权表 flush privileges;
5. 修改密码 alter user root@localhost identified with mysql_native_password by '123456';
6. 恢复正常数据库服务

8.0 新特性

1. 密码插件，在8.0为sha2模式，可还原为 mysql_native_password
    vim /etc/my.cfg
    default_authentication_plugin=mysql_native_password
2. 在8.0需要先创建user，在grant