目录

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

...

day128:MySQL进阶:的更多相关文章

  1. mysql进阶(二十九)常用函数

    mysql进阶(二十九)常用函数 一.数学函数 ABS(x) 返回x的绝对值 BIN(x) 返回x的二进制(OCT返回八进制,HEX返回十六进制) CEILING(x) 返回大于x的最小整数值 EXP ...

  2. mysql进阶(二十八)MySQL GRANT REVOKE用法

    mysql进阶(二十八)MySQL GRANT REVOKE用法 MySQL的权限系统围绕着两个概念: 认证->确定用户是否允许连接数据库服务器: 授权->确定用户是否拥有足够的权限执 ...

  3. mysql进阶(二十七)数据库索引原理

    mysql进阶(二十七)数据库索引原理 前言 本文主要是阐述MySQL索引机制,主要是说明存储引擎Innodb. 第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础. ...

  4. mysql进阶(二十六)MySQL 索引类型(初学者必看)

    mysql进阶(二十六)MySQL 索引类型(初学者必看) 索引是快速搜索的关键.MySQL 索引的建立对于 MySQL 的高效运行是很重要的.下面介绍几种常见的 MySQL 索引类型. 在数 ...

  5. mysql进阶(十六)常见问题汇总

    mysql进阶(十六)常见问题汇总 MySQL视图学习: http://www.itokit.com/2011/0908/67848.html 执行删除操作时,出现如下错误提示: 出现以上问题的原因是 ...

  6. 【转】MySQL— 进阶

    [转]MySQL— 进阶 目录 一.视图 二.触发器 三.函数 四.存储过程 五.事务 一.视图 视图是一个虚拟表(非真实存在),其本质是[根据SQL语句获取动态的数据集,并为其命名],用户使用时只需 ...

  7. MySQL进阶(视图)---py全栈

    目录 mysql进阶(视图)---py全栈 一.什么是视图? 二.视图的特性 三.视图的优点 四.使用场合 五.视图基本操作 六.案例 mysql进阶(视图)---py全栈 一.什么是视图? 视图是从 ...

  8. mysql进阶练习

    一 .  MySQL进阶练习 /*==========================创建班级表=============================*/ CREATE TABLE class ( ...

  9. MySQL进阶19--函数的创建(举例)/设置mysql的创建函数的权限/查看(show)/删除(drop) / 举4个栗子

    /*MySQL进阶19 函数 存储过程和函数:都类似于java中的方法; 存储过程和函数通用好处: 1.提高代码的重用性 2.简化操作 好处: 减少操作次数,减少了编译次数,减少了和服务器的连接次数, ...

  10. MySQL进阶13--常见六大约束: 非空/默认/主键/唯一约束/检查约束/外键约束--表级约束 / 列级约束

    /* MySQL进阶13 常见六大约束: 1.not null 非空 2.default :默认值,用于保证该字段的默认值 ; 比如年龄:1900-10-10 3.primary key : 主键,用 ...

随机推荐

  1. 收集一些有用的docker镜像

    https://hub.docker.com/explore/  是star排名靠前的image =================================================== ...

  2. framebuff 显示子系统

     #include <unistd.h> #include <stdio.h> #include <fcntl.h> #include <linux/fb.h ...

  3. c# 集合ArrayList;特殊集合Stack、Queue

    一)  ArrayList 1.foreach遍历数组中各个元素,执行内部语句 2.  3. 4.  myarry.Clear();//将集合清空 bool b = myarry.Contains(3 ...

  4. 学习pthreads,给线程传递多个參数

    上篇博文中.boss线程给其它线程传递的仅仅有一个參数,那么假如是多个參数呢?怎么传递呢?也许你会有这种疑问,带着这个疑问,我们进入本文的世界,这里传递多个參数,採用结构体,为什么呢?由于结构体里能够 ...

  5. Mac电脑上一款非常时尚高清的动态壁纸Living Wallpaper HD

    很多朋友Mac电脑上都喜欢用动态壁纸,Living Wallpaper HD是本人尝试的一款非常不错的高清动态壁纸.有时钟.天气等各种组建,非常时尚美观. Living Wallpaper HD下载地 ...

  6. pythonのdjango连接MYSQL

    在py3.*中利用django使用mysql时,会出现一些问题.由于django默认的是 MySQLdb,但MySQLdb目前不支持py3.*所以我们要改用pymysql,首先要安装pymysql,命 ...

  7. 【css】正确的让文本换行

    通常文本换行我们会使用word-break属性,有两个值供我们选择 word-break: break-all; word-break: break-world; 那么如何区分这两个值呢,我们看图说话 ...

  8. linux/centos6.5下编译安装python2.7和python3.5.2

    centos6.5环境下安装python2.7 解压python源码包 tar -xf Python-2.7.6.tar.xz 进入软件包目录 cd Python-2.7.6 添加配置项 ./conf ...

  9. 【Android自动化】在使用uiautomator框架自动化时,往往有时再运行脚本时发现xxx实例属性不被允许

    例如: # -*- coding:utf-8 -*- from uiautomator import device as d d(classname="android.widget.List ...

  10. linux下maven项目clean失败

    今天在linux下创建了一个项目自动化发布的脚本,在执行到 mvn clean package -Dmaven.test.skip=true 的时候,项目clean失败 查下下度娘,windows下导 ...