MySQL进阶【1】

2022-07-22 10:43:01

一、MySQL的企业版本选择

1.MySQL 同源分支

Oracle
Percona
MariaDB

2.MySQL 版本选择

5.6 ：5.6.40 + , GA 6-12之间，202010
5.7 ：5.7.20+
8.0 : 8.0.18+

MySQL官网下载：https://downloads.mysql.com/archives/community/

二、MySQL 二进制安装

  1. 创建相关目录

    [root@node01 ~]# mkdir -p /data/app
    [root@node01 ~]# mkdir -p /data/3306/data

  2. 创建用户

    [root@node01 data]# useradd mysql

  3. 关闭防火墙

    [root@node01 data]# iptables - L #显示出当前使用的 iptables 规则
    [root@node01 data]# systemctl stop firewalld #关闭防火墙
    [root@node01 data]# systemctl disable firewalld #设置开机禁用防火墙
    [root@node01 data]# getenforce #查看当前SELinux的运行模式
    Disabled
    [root@node01 data]# setenforce 0 #设置SELinux 成为permissive模式 临时关闭selinux的
    setenforce: SELinux is disabled
    [root@node01 data]# cat /etc/selinux/config
    SELINUX=disabled

  • Enforcing：强制模式。代表SELinux在运行中，且已经开始限制domain/type之间的验证关系
  • Permissive：宽容模式。代表SELinux在运行中，不过不会限制domain/type之间的验证关系，即使验证不正确，进程仍可以对文件进行操作。不过如果验证不正确会发出警告
  • Disabled：关闭模式。SELinux并没有实际运行

删除无关的软件包

[root@node01 data]# rpm -qa|grep mariadb
[root@node01 data]# yum remove mariadb-*
[root@node01 data]# rm -rf /etc/my.cnf* #删除残留的配置文件

4.上传软件至/data/app目录下，并解压，做软连接

[root@node01 ~]# cd /data/app/

[root@node01 ~]# rz -E

没有rz命令使用yum install lrzsz安装

tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql

cd bin/

chown -R mysql.mysql /data/*

  1. 初始化系统库和表

    vim /etc/profile
    export PATH=/data/app/mysql/bin:$PATH #添加mysql环境

source /etc/profile

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

--initialize-insecure #初始化数据库指令

--user=mysql #指定MySQL用户

--basedir=/data/app/mysql #指定软件目录

--datadir=/data/3306/data #指定数据存放目录

[root@node01 bin]# cd /data/3306/data/
[root@node01 data]# ll

++++++

初始化: 在5.6和5.7 的区别
5.6 ：
/data/app/mysql/scripts/mysql_install_db .....
5.7 :
不安全：
mysqld --initialize-insecure ...
初始化完成之后：没有密码即可登录
安全：
mysqld --initialize ...
初始化完成之后：自动生成一个12位，4种密码复杂度的临时密码（root@‘localhost‘）。不能直接使用，第一次登陆时修改掉密码。

++++++

  1. 准备启动脚本

cp /data/app/mysql/support-files/mysql.server /etc/init.d/mysqld

  1. 准备配置文件

    [root@node01 data]# vim /etc/my.cnf
    [mysqld]
    user=mysql
    basedir=/data/app/mysql
    datadir=/data/3306/data
    socket=/tmp/mysql.sock
    server_id=10
    port=3306
    [mysql]
    socket=/tmp/mysql.sock

  2. 启动数据库

    [root@node01 data]# /etc/init.d/mysqld start
    [root@node01 data]# mysql

三、 MySQL 体系结构 及 日常管理

1.MySQL C / S 结构

Client:
MySQL进阶【1】

Server：

2.MySQL 实例结构

MySQL进阶【1】

? 作用： 提供数据处理功能。
? 实例 = mysqld守护进程 + Master thread+ 干活 threads + 专用内存结构

3.服务端的体系结构

见图

MySQL进阶【1】

4.用户管理

4.1 作用

登陆数据库
管理数据库对象（库、表）

4.2 定义

用户名@白名单
root@‘localhost‘
app@‘localhost‘
app@‘%‘
app@‘10.0.0.%‘
app@‘10.0.0.5%‘
app@‘10.%‘
app@‘10.0.0.0/255.255.254.0‘

4.3 用户管理

查询用户

mysql> select user,host ,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

创建用户

mysql> create user xie@‘10.0.0.%‘;
mysql> create user xies@‘10.0.0.%‘ identified by ‘123‘;

修改用户

mysql> alter user xie@‘10.0.0.%‘ identified by ‘123‘;

删除用户

mysql> drop user xie@‘10.0.0.%‘;

++++++++++++++

说明：
在8.0之前，可以通过grant 既可以授权又可以自动创建用户。
grant all on . to admin@‘%‘ identified by ‘123‘;
8.0版本之后,必须要先建立用户，再单独授权。而且不支持identified by字句了。

8.0密码加密插件发生了变化，默认是sha2的加密方式，很多应用连接会不支持。如果需要向下兼容需要将密码插件修改为mysql_native_password插件
mysql> create user test@‘localhost‘ identified with mysql_native_password by ‘123‘;
mysql> alter user test@‘localhost‘ identified with mysql_native_password by ‘123‘;

++++++++++++++

5.权限管理

5.1 作用

约束用户的操作行为。

5.2 权限的定义

ALL ： 拥有所有权限（除了grant option），一般管理员才会有的。
mysql> show privileges;
Alter
Alter routine
Create
Create routine
Create temporary tables
Create view
Create user
Delete
Drop
Event
Execute
File
Grant option ** *******
Index
Insert
Lock tables
Process
Proxy
References
Reload
Replication client
Replication slave
Select
Show databases
Show view
Shutdown
Super
Trigger
Create tablespace
Update
Usage

5.3 权限管理
授权

grant 权限 on 权限作用范围 to 用户 identified by 密码;

权限： ALL 、 单一权限等。
作用范围：
*.*
test.*
test.t1

mysql> grant all on . to admin@‘%‘ identified by ‘123‘;
mysql> grant select,update,delete,insert on oldboy.* to oldboy@‘10.0.0.%‘ identified by ‘123‘;

回收

错误方法：
grant select,update,insert on oldboy.* to oldboy@‘10.0.0.%‘ identified by ‘123‘;
mysql> show grants for oldboy@‘10.0.0.%‘;
正确方法：
mysql> revoke delete on oldboy.* from ‘oldboy‘@‘10.0.0.%‘;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for oldboy@‘10.0.0.%‘;

5.4 授权表

mysql.user #用户和权限
mysql.db #用户被赋予哪些库权限
mysql.tables_priv #用户被赋予哪些表权限

6.启动关闭

启动：

mysqld &

关闭：

方式一：

mysql -uroot -p‘123‘

shutdown

方式二：

mysql -uroot -p‘123‘ shutdown

centos6与centos7启动关闭 MySQL数据库区别

centos6中使用sys-v方式

service mysqld start

service mysqld stop

service mysqld restart

centos7中使用systemd方式

systemctl start mysqld

systemctl stop mysqld

systemctl restart mysqld

7.配置文件应用

7.1 配置文件默认读取顺序

[root@node01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf ---》 /etc/mysql/my.cnf ---》 /usr/local/mysql/etc/my.cnf ---》 ~/.my.cnf

说明：

  1. 如果自定义配置文件位置（例如： /opt/aa.txt），需要以下命令：
    mysqld --defaults-file=/opt/aa.txt &
    mysqld_safe --defaults-file=/opt/aa.txt &

  2. 如果是多配置文件，重复选项，以最后一个配置信息为准。

7.2 配置文件结构

[标签] ：
服务器标签 ： 影响到了数据库启动和数据初始化
[mysqld] 、[mysqld_safe] 、[server]
客户端标签 ：影响客户端的连接
[mysql] 、 [mysqldump] 、 [client]

配置

[root@node01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=10
port=3306
[mysql]
socket=/tmp/mysql.sock

8.连接管理

8.1 自带客户端连接

本地Socket

要求： 提前创建好localhost相关用户
[root@node01 ~]# mysql -uroot -p123 -S /tmp/mysql.sock

远程连接

要求： 提前创建好可以远程登录的用户
[root@node01 ~]# mysql -uadmin -p123 -h 10.0.0.21 -P 3306

8.2 开发工具

要求： 提前创建好可以远程登录的用户
sqlyog navicat workbech

8.3 程序连接

pip3 install pymysql
import ....

9.多实例环境

9.1 作用：
  1. 测试环境、开发环境
  2. 分布式架构

3307、3308、3309

9.2 准备目录

mkdir -p /data/330{7..9}/data

chown -R mysql.mysql /data/*

9.3 配置文件

vim /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/tmp/mysql7.sock
server_id=17
port=3307

vim /data/3308/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3308/data
socket=/tmp/mysql8.sock
server_id=18
port=3308

vim /data/3309/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/tmp/mysql9.sock
server_id=19
port=3309

9.4 初始化数据

[root@node01 data]# mv /etc/my.cnf /tmp/mysql.cnf

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data

mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data

9.5 启动数据库

[root@node01 data]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@node01 data]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@node01 data]# mysqld_safe --defaults-file=/data/3309/my.cnf &

9.6连接并查看

mysql -S /tmp/mysql7.sock

mysql -S /tmp/mysql8.sock

mysql -S /tmp/mysql9.sock

查看端口

select @@port;

四、SQL基础规范

1.SQL 常用种类

DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言

2.DDL 开发规范

库 ：
create database
alter database
drop database

表 ：
create table
alter table
drop table
truncate table

3.DDL规范：

1. 建库需要显示添加字符集和校对规则 
2. 库名要和业务有关。
3. 库名不要大写字母，数字开头。
4. 普通用户禁用drop操作
5. 表名：和业务有关、不要大写字母和数字开头、不要太长
6. 必须显式设定存储引擎。
7. 字符集一般建议utf8mb4 
8. 表要有注释 
9. 必须要有主键列，建议是数字自增列。
10. 数据类型： 合适的、简短的、足够的。
11. 尽量每个列非空。
12. 每个列要加注释。
13. 表大做alter语句，业务不繁忙期间做。如果必须做，建议pt-osc 。

4.ML规范：

1. 大的insert事务，要拆分为小事务进行。也可以用load data infile方式进行批量录入。
2. 导入大量数据时，可以将索引先禁用，导入成功后再创建。
3. update 必须要加where条件。经常做update操作的列，不建索引。更新范围尽量小。
4. delete 必须要加where条件。有必要的话，可以使用update替代delete。

五、索引及执行计划

1.作用

一本书中的目录。起到优化查询的作用（select\update\delete）

2.支持的种类

Btree -》B树索引
Rtree -》R树索引｛空间索引｝
Fulltext -》全文索引
Hash -》hash索引

3.查找算法

遍历
二叉树
红黑树
Balance tree(平衡多叉树)【B树】

B树结构图

MySQL进阶【1】

B+树结构图

MySQL进阶【1】

4.MySQL 索引如何应用BTree

4.1 聚簇索引

前提：
如果有主键列（PK），通过主键构建聚簇索引。
没有主键,选择第一个不为空的唯一键UK（unique key）。
都没有，自动生成DB_ROW_ID（6字节）的隐藏列，生成聚簇索引
一张表只有一个聚簇（区,默认是64个连续page，默认1M）索引。
聚簇索引，采用区的结构，组织存储表中的数据行。MySQL采用的是IOT。

MySQL进阶【1】

构建过程：

  1. 表设计时，一般会设置一个ID主键。
  2. 所有数据的录入，都是按照ID顺序，有序的在磁盘的连续（同一个区）数据页上有序存储数据行。
  3. 聚簇索引的叶子节点就是原表数据。
  4. 枝节点，保存了下层叶子节点的ID的范围+指针，生成枝节点。
  5. 根节点，保存了下层枝节点的ID的范围+指针，生成根节点

作用： 通过ID作为查询的条件时，会受到聚簇索引优化，理论上只需要最少三个数据页，即可获取数据行。

4.2 辅助索引

MySQL进阶【1】

构建过程:

  1. 叶子节点： 提取辅助索引列值+ID，按照辅助索引列进行排序，存储到有序的page中。
  2. 枝节点： 保存了下层叶子节点，辅助索引列值范围+指针。
  3. 根节点： 保存了下层枝节点，辅助索引列值范围+指针。

作用：
通过辅助索引列作为条件查询时，首先扫描辅助索引树，获得ID主键值，然后再回到聚簇索引扫描【回表查询】(尽可能覆盖大部分索引减少回表查询次数)，最终获取想要的数据行。

MySQL进阶【1】

上一篇：详解MySQL的主从复制、读写分离、备份恢复


下一篇：考试系统--底层框架发布时遇到的问题解决方案（Window7 IIS6.0）（一）