day128:MySQL进阶:
目录
1.介绍和安装
1.1 数据库分类
RDBMS(关系型数据库):Relational Database Management System
代表产品:Oracle MySQL MSSQL PG
NoSQL(非关系型数据库):Not Only SQL
代表作品:MongoDB Redis ES
NewSQL(对各种新的可扩展/高性能数据库的简称)
代表作品:spanner PolarDB(X) TDSQL TiDB 高斯
1.2 MySQL分支
1.Oracle
2.Percona
3.MariaDB
4.云厂商
1.3 MySQL获取
www.mysql.com
1.4 MySQL安装
# 1.解压 [root@localhost opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz # 2.软连接 ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql # 3.修改环境变量 vim /etc/profile 添加下面的内容 export PATH=/usr/local/mysql/bin:$PATH source /etc/profile # 4.创建用户 目录 配置文件 [root@localhost mysql]# useradd mysql [root@localhost mysql]# mkdir -p /data/3306/data [root@localhost mysql]# chown -R mysql.mysql /data [root@localhost mysql]# vim /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock # 5.初始化数据 如果没有配置文件: mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data 如果有配置文件: [root@localhost data]# mysqld --initialize-insecure # 6.准备启动脚本 [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL... SUCCESS! [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld status SUCCESS! MySQL running (12134)
2.基础管理
2.1 用户管理
2.1.1 作用
1.用来登录MySQL
2.用来管理MySQL对象[ps:什么是MySQL对象??]
2.1.2 定义
语法:用户名@’白名单’
什么是白名单?? 答：IP地址范围
常见形式:
root@’%’
root@’localhost’
root@’10.0.0.%’
root@’10.0.0.2′
2.1.3 管理用户
1.查询用户
-- 查询用户 mysql> select user,host ,authentication_string ,plugin from mysql.user; +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | root | localhost | | caching_sha2_password | +------------------+-----------+------------------------------------------------------------------------+-----------------------+
注意: plugin,加密插件,在8.0之后做了升级,caching_sha2_password,安全性增高了.
会导致很多老的客户端程序无法连接至MySQL.早期版本是mysql_native_password.
2.创建用户/修改用户/删除用户
-- 创建用户 mysql> create user root@'10.0.0.%' identified by '123'; mysql> create user user1@'10.0.0.%' identified with mysql_native_password by '123'; -- 修改用户 mysql> alter user root@'10.0.0.%' identified with mysql_native_password by '123'; mysql> alter user user1@'10.0.0.%' account lock; mysql> alter user user1@'10.0.0.%' account unlock; -- 删除用户 mysql> drop user user1@'10.0.0.%';
注意: 8.0 之后 ,只能先建用户后授权.
2.2 权限管理
2.2.1 权限列表
mysql> show privileges;
最常见的权限:
1.ALL ? 不包含 grant option
2.select ,insert ,update ,delete
2.2.2 授权和回收权限
-- 授权 mysql> grant all on *.* to root@'10.0.0.%'; -- 查询权限 mysql> show grants for root@'10.0.0.%'; -- 回收权限 mysql> revoke drop on *.* from root@'10.0.0.%' ;
关于权限作用范围:
*.* : 所有库下所有表
luffy.*: 单库下的所有表
luffy.user: 单表
2.3 连接管理
2.3.1 socket文件连接
-- 前提: 需要提前创建localhost白名单的用户 [root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock
2.3.2 TCP/IP方式
-- 前提 需要将登陆客户端IP加入白名单 [root@localhost ~]# mysql -uroot -p123 -h 10.0.0.111 -P3306
2.4 配置管理
2.4.1 离线配置
-- 配置文件应用顺序 [root@localhost ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf ---> /etc/mysql/my.cnf ---> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
-- 配置文件结构 [root@localhost ~]# cat /etc/my.cnf
[mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock
注意:修改配置文件,重启数据库.
2.4.2 在线配置
-- 通过专用配置命令进行修改. mysql> set global innodb_buffer_pool_size=16777216;
-- 查看所有可以在线配置的参数 show variables show variables like '%date%';
3.MySQL的体系结构
不多说,直接上图.
1.连接层
连接层作用有三:
1.提供连接协议: Socket文件,TCP/IP
2.验证用户身份/授权表.
3.连接层提供了一个与sql层交互的线程
2.SQL层
SQL层作用有六:
1.接收连接层传过来的sql语句
2.验证sql语句的语法
3.验证sql语句的语义（DDL,DQL,DML,DCL）
4.解析器：解析sql语句，生成执行计划
5.优化器：从执行计划中选择最优的一条
优化???? 优化(逻辑优化,物理优化(索引)
6.执行器：执行选出来的SQL计划
1.与存储引擎层建立交互的线程
2.将要执行的sql语句传到存储引擎层
3.Engine层
作用:负责和磁盘交互
4.SQL
4.1 什么是SQL?
关系型数据库通用的语言.结构化查询语言.[ps:什么是结构化查询语言?]
4.2 SQL_MODE
常用SQL_MODE参数:[ps:SQL_MODE详解]
mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
重点: ONLY_FULL_GROUP_BY ,5.7版本新特性.
4.3 数据类型
特别注意:
char(10) 定长
varchar(10) 变长
其他详细的数据类型:[ps:MySQL中的数据类型]
4.4 字符集
utf8 最多存三字节字符
utf8mb4 最多存四字节字符 [emoji表情]
4.5 SQL种类
DDL : 数据定义语言
DML : 数据操作语言
什么是DDL(数据定义语言?),什么是DML(数据操作语言?):[ps:DDL和DML的定义]
4.6 DDL的应用规范
4.6.1 对库(Database)的操作
库:
create databasedrop database
alter database
规范:
a. 生产系统禁用drop 操作b. 库名不要使用系统预留字符,不要大写字母,不要数字开头.
c. 建库是显式设置字符集.
4.6.2 对表(Table)的操作
create table
规范:
表名不要使用系统预留字符,不要大写字母,不要数字开头.ob_user;不要超过18字符.
数据类型:
合适的 简短的 足够的
注意点:
每个表要有主键.
每个列尽可能非空,或者设置默认值
每个列要加注释.
存储引擎使用InnoDB 字符集 utf8mb4
alter table
主要用途:
添加列
删除列
加索引
删索引
该类型
关于alter table还需要知道的一个点:
8.0以前:Online DDL 需要业务低估期间做. 或者使用PT-OSC.
8.0之后:添加列可以直接做.
drop table 非必要不要使用.
4.7 关于DDL/DML和MySQL执行阶段的问题
prepare MDL X 阻塞所有DML写入 DDL
exec S 降级共享锁 不阻塞DML , 阻塞DDL
commit MDL X 阻塞所有DML写入 DDL
5.索引和执行计划
5.1 什么是索引?
相当一本书中的目录.优化查询(select update delete)
5.2 索引类型
BTREE *****
RTREE(空间数据索引)
HASH(哈希索引)
FTEXT(全文索引)
5.3 BTREE 结构认识
b-tree
b+tree(b*tree) –>加强版
5.4 MySQL中如何应用BTREE?
5.4.1 聚簇索引
1.什么是聚簇索引?
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；
2.聚簇索引的构造条件?
Primary Key / Unique+not null / RowID
3.聚簇索引的构造细节?
叶子节点(Leaf): 在录入数据时,会按照聚簇索引逻辑顺序,存储到物理上连续的多个数据页. 从而生成了叶子节点. 并且存储相邻叶子节点的双向指针.
枝节点(No-Leaf): 选取叶子节点的ID的范围+指针.
根节点(ROOT) : No-leaf节点的ID范围+指针4.聚簇索引的优化效果?
通过ID列作为查询条件时,会起到优化效果.
5.4.2 辅助索引
…