服务端持久化层MySQL零基础入门实战1-阿里云开发者社区

1.MySQL入门

1.1.源码安装MySQL5.7

1、关闭防火墙、selinux

systemctl stop firewalld.service
vi /etc/selinux/config 
更改 SELINUX=disabled

2、安装cmake工具

yum -y install cmake

3、解压boost压缩包（mysql5.1.17必须依赖）

tar -xf boost_1_59_0.tar.gz

4、yum安装其他组件

yum -y install gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel ncurses ncurses-devel openssl-devel

5、创建mysql程序用户

useradd -M -s /sbin/nologin mysql

6、创建mysql安装路径，数据文件路径，并授权

安装路径：mkdir -p /usr/local/mysql
数据文件路径：mkdir -p /usr/local/mysql/data
授权：chown -R mysql:mysql /usr/local/mysql /usr/local/mysql/data

7、使用cmake工具对mysql5.7.17进行环境收集检验于配置相关模块

#解压mysql源码包，并进入解压后的路径 (注意：cmake之前一定要解压boost_1_59_0.tar.gz)
tar -xf mysql-5.7.17.tar.gz
cd mysql-5.7.17

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DMITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEFAULT_CHARSET=all \
-DWITH_BIG_TABLES=1 \
-DWITH_BOOST=../boost_1_59_0 \
-DWITH_DEBUG=0

参数详细信息解释：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定mysql数据库安装目录
-DMYSQL_DATADIR=/data/mydata \        #指定数据库文件路径
-DSYSCONFDIR=/etc \             #指定配置文件目录
-DWITH_INNOBASE_STORAGE_ENGINE=1 \      #安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \       #安装ARCHIVE存储引擎 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \     #安装BLACKHOLE存储引擎 
-DWITH_READLINE=1 \             #使用readline功能 
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \     #连接文件位置
-DWITH_SSL=system \             #表示使用系统上的自带的SSL库 
-DWITH_ZLIB=system \            #表示使用系统上的自带的ZLIB库 
-DDEFAULT_CHARSET=utf8 \          #指定默认使用的字符集编码 
-DDEFAULT_COLLATION=utf8_general_ci \   #指定默认使用的字符集校对规则 
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=../boost_1_59_0 \        #指定Boost库的位置，mysql5.7必须添加该参数 
-DENABLE_DOWNLOADS=1            #支持下载可选文件

编译完成警告忽略，如果出现报错删除mysql安装包下的CMakeCache.txt文件

8、编译并安装（此步时间过长，可以来把王者压压惊）

make && make install

9、编译并安装

/usr/local/mysql/bin/mysqld \
--initialize \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
-socket=/tmp/mysql.sock

10、修改my.cnf配置文件

vi /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/usr/local/mysql/log/mysql.errlog
pid-file=/usr/local/mysql/data/$hostname.pid

11、创建错误日志文件，并将文件归属到mysql程序用户下

touch /usr/local/mysql/log/mysql.errlog
chown -R mysql:mysql /usr/local/mysql/log/mysql.errlog

注意：如果配置文件里面配置了，某个文件一定要在启动前提前创建好，否则会报错

Starting MySQL.2019-08-03T14:19:37.028727Z mysqld_safe error: log-error set to '/usr/local/mysql/log/mysql.errlog', however file don't exists. Create writable for user 'mysql'. ERROR! The server quit without updating PID file (/data/mydata/localhost.localdomain.pid).

12、初始化数据库

mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

13、创建软链接

ln -s /usr/local/mysql/bin/* /usr/bin/

14、启动mysql服务

/usr/local/mysql/support-files/mysql.server start

15、停止mysql服务

/usr/local/mysql/support-files/mysql.server stop

16、登入mysql服务

mysql -uroot -p
设置密码：set password=password('123456');

1.2.Docker安装MySQL5.7

1、查看centos内核版本

uname -a

安装条件：docker官方至少要求3.8,建议3.10以上

2、安装Docker的前期准备

（1）关闭防火墙

systemctl stop firewalld

（2）关闭selinux

vi /etc/selinux/config   --->将SELINUX=disabled

（3）安装wget命令

yum -y install wget

（4）下载阿里云docker社区版yum源

cd /etc/yum.repos.d/  进入到yum源的路径
wget  http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

（5）下载网络源安装contain-SElinux包

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

yum clean all
yum -y update #这一块时间有点长，耐心等待

注意：一定要有这一步，否则会报找不到selinux包

（6）安装docker ce社区版

yum -y install docker-ce.x86_64

（7）设置开机自启

systemctl enable docker

（8）更新xfsprogs

yum -y update xfsprogs

3、启动docker容器

systemctl start docker

4、查看docker版本和详细信息

（1）查看docker版本

docker version

（2）查看docker详细信息

docker info

5、配置阿里云镜像加速

新建文件：vi /etc/docker/daemon.json
编辑文件

{
  "registry-mirrors": ["https://6j96rs65.mirror.aliyuncs.com"]
}

重启docker
systemctl daemon-reload
systemctl restart docker
查看容器独有的IP地址
docker inspect --format=‘{{.NetworkSettings.IPAddress}}’ 容器名|容器ID

6、拉取mysql:5.7镜像

docker pull mysql:5.7

7、启动MySQL5.7容器

docker run -p 3306:3306 --name mysql \ 
-v /usr/local/docker/mysql/conf:/etc/mysql \ 
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7

docker run #启动一个容器 
--name mysql #设置容器名称为mysql 
-p 3306:3306 #宿主机端口:容器内部端口， 将容器的mysql服务端口映射给宿主机的一个端口，对外提供 访问
-v /usr/local/docker/mysql/conf:/etc/mysql #挂载映射，将容器内部的/etc/mysql文件挂载载 宿主机的/usr/local/docker/mysql/conf目录下，其他同理 
-e MYSQL_ROOT_PASSWORD=123456 #-e设置额外的参数，设置root用户的密码 
-d #后台运行 
mysql:5.7 #镜像名称

8、docker ps查看MySQL容器运行状态

docker ps

1.3.忘记MySQL超户密码

1、源码服务mysql忘记超户密码

（1）暂停 mysq服务

systemctl stop mysql  #或者/etc/init.d/mysqld start

（2）跳过 grant表授权，进入安全模式，并在后台运行

mysqld_safe --skip-grant-tables &

（3）进入安全模式修改密码

[root@mysql ~]# mysql
mysql> use mysql;
Database changed
mysql> update user set Password=password('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4    Changed: 3     Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

（4）重启mysql服务

systemctl start mysql #或者/etc/init.d/mysqld restart
mysql -uroot -p123456

2、容器服务mysql忘记超户密码

（1）进入容器内部

docker exec -it mysql bash

（2）设置跳过权限表的加载,注意这块表示任何用户都能登进来，非常不安全

echo "skip-grant-tables" >> /etc/mysql/conf.d/docker.cnf

（3）退出容器，重启容器

1.exit
2.docker restart mysql

（4）再次进入容器

docker exec -it mysql bash

（5）登录mysql(无需密码)

mysql -uroot

（6）更新权限，修改密码，退出，替换掉刚才加的跳过权限表的加载参数

1.flush privileges;
2.alter user 'root'@'localhost' identified by '123456';
(或者：update user set password=password('123456') where user ='root')
3.exit
4.sed -i "s/skip-grant-tables/ /" /etc/mysql/conf.d/docker.cnf

（7）退出容器，重启容器

1.exit
2.docker restart mysql

1.4.MySQL支持简体中文

1、临时开启mysql支持简体中文

2、永久开启mysql支持简体中文

（1）源码mysql服务

修改/etc/my.cnf配置文件

vi /etc/my.cnf
加如下内容：
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

（2）容器mysql服务

进入容器内部，修改配置文件

1.docker exec -it mysql（容器名） bash
2.vim /etc/mysql/conf.d/docker.cnf
加如下内容：
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

2.MySQL数据库操作

2.1.SQL语句概述

SQL语句是（Structured Query Language）的缩写，是关系型数据库的标准语言，用于维护管理数据库，如数据查询、数据更新、访问控制、对象管理等功能。
SQL分类：

DDL：数据定义语言，用于增删改数据库的对象。
DML：数据操作语言，用于对表数据的增删改。
DQL：数据查询语句，用于数据查询。
DCL：数据控制语句。

2.2.数据库表的管理操作

1、查看数据库结构

查看数据库结构：
show databases;

查看数据库中的数据表信息：
use 数据库名;  #使用数据库
show tables;  #查看当前库下的所有表

显示数据表的结构：
describe 数据库.表名
describe mysql.user; （或者desc user）

2、DDL语句操作

数据定义语言，用于增删改数据库对象。

（1）create创建新库、创建新表

创建test库：
create database test;

创建test表：
create table test (id int(10) not null);

（2）drop删除库、删除表

删除test库：
drop database test;

删除test表：
drop table test;

3、DML语句表数据操作

数据操作语言，用于对表中的数据进行增删改。

（1）insert插入新数据

insert into 表名(字段1,字段2,....) values ('字段1的值','字段2的值',.....);

示例：
INSERT INTO sys.sys_config
(variable, value, set_time, set_by)
VALUES('diagnostics.include_raw', 'OFF', '2021-11-01 14:43:22.0', NULL);

（2）update更改原有的数据

update 表名 set 字段1 = 值1,.... where 表达式条件;

示例：
UPDATE sys.sys_config
SET value='OFF', set_time='2021-11-01 14:43:22.0', set_by=NULL
WHERE variable='diagnostics.include_raw';

（3）delete删除不需要的数据

delete from 表名 where 表达式条件;

示例：
DELETE FROM sys.sys_config
WHERE variable='diagnostics.include_raw';

4、DQL语句操作

数据查询语句，用于数据库的查询。

select查询语句

select 字段名1,字段名2,.... from 表名;
select 字段名1,字段名2,.... from 表名 where 表达式条件;

示例：
SELECT variable, value, set_time, set_by
FROM sys.sys_config
WHERE variable='diagnostics.include_raw';

2.3.用户权限设置

1、设置用户权限（用户不存在则新建用户）

grant 权限 on 库名.表名 to 'root'@'localhost' IDENTIFIED BY '123456';

grant all on *.* to 'root'@'localhost' IDENTIFIED BY '123456';
all #代表所有权限
on  #作用在那个库那些表中
*.* #第一个*代表所有的库第二个*代表所有的表
to 'root'@'localhost' #作用在本地的root用户上
IDENTIFIED BY '123456' #设置密码
grant select on mysql.* to 'lixiang'@'localhost' IDENTIFIED BY '123456';
#将mysql库的所有表的查询的权限付给本地lixiang这个用户，初始密码为123456

2、查看用户权限

show grants;#查看当前用户的权限
show grants for 'lixiang'@'localhost';#查看本地用户lixiang的权限
show grants for 'lixiang'@'192.168.10.4';#查看远程用户lixiang的权限
#注意：执行完成记得flush privileges;刷新一下

3、撤销用户权限

revoke select on mysql.* from 'lixiang'@'localhost';
#删除lixiang用户对mysql下的所有表的查询权限，注意：撤销完记得flush privileges;刷新一下

2.4.其他的常用的命令

1、用于显示广泛的服务器状态信息

show status;

2、显示服务器错误或警告信息

show errors;
show warnings;

3、显示当前连接用户

select user();

4、显示当前时间

select now();

5、显示当前数据库

select database();

6、显示服务器状态

status;

3.MySQL索引

3.1.数据库索引概述

1、数据库索引

在数据库中，索引使数据库程序无需对整个表进行扫描，就可以在其中找到对应的数据。

数据库的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单。

2、索引的作用

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率，特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成千倍。

可以降低数据库的 IO成本，并且索引还可以降低数据库的排序成本。
通过创建唯一性索引保证数据表数据的唯一性，可以加快表与表之间的连接。
在使用分组和排序时，可大大减少分组和排序时间。

3、索引的分类

（1）普通索引

这是最基本的索引类型，而且没有唯一性之类的限制。

（2）唯一性索引

与普通索引基本相同，区别在于：索引列的所有值都只能出现一次，即必须唯一，但可为空。

（3）主键

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”，具有唯一性的同时不能为空。

（4）全文索引

MySQL从 3.23.23版开始支持全文索引和全文检索。在 MySQL中，全文索引的类型为FULLTEXT，全文索引可以在 VARCHAR或者 TEXT类型的列上创建。贴吧的文本内容，和一些小型的网站网页内容，存放在数据库中即为全文索引模式。

（5）单列索引与多列索引

索引可以是单列上创建的索引，也可以是多列上创建的索引。

4、索引高性能保证

把查询过程中的随机事件变成顺序事件
数据保存在磁盘上，为了提高性能，每次又可以把部分的数据读到内存中来计算，访问磁盘的成本大概是访问内存的十万倍左右

磁盘IO与预读

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据读到内存缓冲区，而是把相邻的数据也读到内存缓冲区内，因为局部预读性原理，当计算机访问一个地址的数据的时候，

与其它相邻的数据也会很快被访问到。每一次IO读取的数据我们称为一页（page）。具体一页有多大数据跟操作系统有关系，一般是4K或者8K。

3.2.常见的检索方案

顺序检索：最基本的查询算法-复杂度O （n），大数据量此算法效率糟糕。
二叉树查找（binary tree search）：O（log2n），单层节点所能存储数据量较少，需要遍历多层才能定位到数据。
hash索引：无法满足范围查找，优点：等值检索快，hash值==物理地址，范围检索

B-TREE：每个节点都是一个二元数组[key,data]，所有节点都可以存储数据。key为索引key，data为除key之外的数据。

3.3.B+Tree数据结构解析

B-Tree的缺点：插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。造成IO操作频繁。区间查找可能需要返回上层节点重复遍历，IO操作繁琐。
B+Tree的改进：非叶子节点不存储data，只存储索引key，只有叶子节点才存储data。

高性能保证：

三层的B+Tree可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共就是需要百万次的IO，显然成本非常高。
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。
B+Tree只在叶子节点存储索引数据，所有叶子节点包含一个链指针，其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围，先定位索引在通过索引高效快速定位数据。

3.4.创建、查看、删除索引

根据企业需求选择了何时的索引之后，可以使用 CREATE INDEX创建索引，CREATE INDEX加上各个索引关键字便可创建各个类型的索引。

1、创建普通索引

格式：create index <索引的名字> on 表名(字段);

示例：create index my_index on student(id); 
查看表的索引：show index from student;

2、创建唯一性索引

格式：create unique index <索引名称> on 表名(字段);

示例：create unique index my_index on student(id);
查看表的索引：show keys from student;

3、创建主键索引

格式：alter table 表名 add primary key(字段);

示例：alter table student add primary key(员工ID);

4、删除索引

语法：drop index <索引的名称> on 表名;
   alter table 表名 drop index <索引的名称>;
   alter table 表名 drop primary key;

示例：
drop index my_index on student;
alter table student drop primary key;

5、不适合用索引的场景

不适合键值较少的列（重复数据较多的列）
索引无法存储null值

6、索引失效的场景

如果条件中有or，即使其中有条件带索引也不会使用（这也是为什么尽量少使用or的原因），想要使用or，又想让索引生效，只能将or条件中的每个列都加上索引
对于多列索引，不是使用的第一部分，则不会使索引生效
like查询以%开头
类型强转情况下不走索引，如varchar类型用long传参来查
hash索引不支持范围检索

服务端持久化层MySQL零基础入门实战1

1.MySQL入门

1.1.源码安装MySQL5.7

1.2.Docker安装MySQL5.7

1.3.忘记MySQL超户密码

1.4.MySQL支持简体中文

2.MySQL数据库操作

2.1.SQL语句概述

2.2.数据库表的管理操作

2.3.用户权限设置

2.4.其他的常用的命令

3.MySQL索引

3.1.数据库索引概述

3.2.常见的检索方案

3.3.B+Tree数据结构解析

3.4.创建、查看、删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

服务端持久化层MySQL零基础入门实战1

1.MySQL入门

1.1.源码安装MySQL5.7

1.2.Docker安装MySQL5.7

1.3.忘记MySQL超户密码

1.4.MySQL支持简体中文

2.MySQL数据库操作

2.1.SQL语句概述

2.2.数据库表的管理操作

2.3.用户权限设置

2.4.其他的常用的命令

3.MySQL索引

3.1.数据库索引概述

3.2.常见的检索方案

3.3.B+Tree数据结构解析

3.4.创建、查看、删除索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像