day128:MySQL进阶:

iR-Poke 2021-01-21 23:20:45
Mysql 进阶 day day128


目录

1.介绍和安装

2.基础管理

2.1 用户管理

2.2 权限管理

2.3 连接管理

2.4 配置管理

3.MySQL的体系结构

4.SQL

5.索引和执行计划

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 database

drop 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 辅助索引

...

 

版权声明
本文为[iR-Poke]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/libolun/p/14310507.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云