27MySQL体系结构与管理
MySQL C/S结构
TCP/IP mysql -uroot -poldboy123 -h 10.0.0.51 -p3306
Socket mysql -uroot -poldboy123 -S /tmp/mysql.sock
SQL语句引入
结构化的查询语言
DQL 数据查询语言
DDL 数据定义语言
DML 数据操作语言
DCL 数据控制语言
select user,host from mysql.user;
连接层
提供连接协议(Socket，TCPIP)
验证用户名(root@localhost)密码合法性，进行匹配专门的授权表
派生一个专用连接线程(接收SQL，返回结果)
show processlist;
SQL层(优化方面至关重要)
验证SQL语法和SQL_MODE
验证语义
验证权限
解析器进行语句解析，生成执行计划(解析树)
优化器(各种算法，基于执行代价)，根据算法找到代价最低的执行计划
代价 CPU，IO，MEM
执行器按照优化器选择执行计划，执行SQL语句，得出获取数据的方法
提供query cache(默认不开)，一般不开，会用redis
记录操作日志(binlog)，默认没开
存储引擎层
真正和磁盘打交道的一个层次
根据SQL层提供的取数据的方法，拿到数据，返回给SQL，结构化成表，再由连接层线程返回给用户
MySQL逻辑存储结构
库
create database wordpress charset utf8mb4;
show datanases;
use wordpress;
表(字段，记录)
表属性
列属性(元数据)
MySQL物理存储结构
库 使用FS上的目录来表示
表
MyISAM(ext2)
user.frm 存储的表结构(列，列属性)
user.MYD 存储的数据记录
user.MYI 存储索引
InnoDB(XFS)
time_zone.frm 存储的表结构(列，列属性)
time_zone.ibd 存储的数据记录和索引
ibdata1 数据字典信息
InnoDB段区页
一般情况下(非分区表)
一个表就是一个段
一个段由多个区构成
一个区在(16k)，64个连续的页，1M大小
用户和权限管理
登录MySQL，管理MySQL
用户的定义
用户名@'白名单'
wordpress@'%'
wordpress@'localhost'
wordpress@'127.0.0.1'
wordpress@'10.0.0.%'
wordpress@'10.0.0.5'
wordpress@'10.0.0.0/255.255.254.0'
wordpress@'10.0.%'
用户的操作
建用户
create user oldboy@'10.0.0.%' identified by '123';
select user,host from mysql.user;
8.0以前可以自动创建用户并授权
grant all on *.* to oldguo@'10.0.0.%' identified by '123';
查询用户
select user,host from mysql.user;
修改用户密码
alter user oldguo@'10.0.0.%' identified by '123456';
删除用户
drop user oldguo@'10.0.0.%';
权限管理
权限列表
ALL
with grant option
SELECT，INSERT，UPDATE...
授权命令
grant all on *.* to oldguo@'10.0.0.%' identified by '123' with grant option;
创建一个管理员用户root，可以通过10网段管理数据库
grant all on *.* to root@'10.0.0.%' identified by '123' with grant option;
创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT，INSERT，UPDATE，DELETE
grant SELECT, INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';
回收权限
show grants for wordpress@'10.0.0.%' ;
revoke delete on wordpress.* from 'wordpress'@'10.0.0.%';
show grants for wordpress@'10.0.0.%';
关于生产中开用户
如何沟通开用户
是否有邮件批复
对哪些库和表做操作
做什么操作
从什么地址来登录
开发人员找你要root用户密码
走流程拒绝
如果是金融类公司，原则上不允许任何非DBA人员持有或申请root，私下索要及时举报
MySQL的启动和关闭
日常启停
mysql.server start mysqld_safe mysqld
mysql.service mysqld
需要依赖于/etc/my.cnf
维护性任务
mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行，也会读取/etc/my.cnf的内容，但是如果冲突，命令行优先级最高
mysqld_safe &
mysqladmin -uroot -p123 shutdown
初始化配置
影响数据库的启动，影响到客户端的功能
初始化配置的方法
初始化配置文件(例如/etc/my.cnf)
启动命令行上进行设置(例如mysqld_safe mysqld)
预编译设置(仅限于编译安装时设置)
初始化配置文件的书写格式
[标签]
xxx=xxx
[标签]
xxx=xxx
配置文件标签的归类
服务器端
[mysqld]
[mysqld_safe]
[server]
客户端
[mysql]
[mysqladmin]
[mysqldump]
[client]
配置文件设置样本(5.7)
#服务器端配置
[mysqld]
#用户
user=mysql
#软件安装目录
base=/application/mysql
#数据路径
datadir=/data/mysql/data
socket文件位置
socket=/tmp/mysql.sock
服务器id号
server_id=6
端口号
port=3306
客户端配置
[mysql]
#socket文件位置
socket=/tmp/mysql.sock
配置文件读取顺序
mysqld --help --verbose | grep my.cnf
强制使用自定义配置文件
--defaults-file
mysqld_safe --defaults-file=/tmp/aa.txt &
MySQL的连接命令
mysql命令
提前应该将用户授权好
grant all on *.* to root@'10.0.0.%' identified by '123';
TCPIP:
mysql -uroot -p -h 10.0.0.51 -P3306
Socket
mysql -uroot -p -S /tmp/mysql.sock
客户端工具
SQLyog
navicat
多实例管理
准备多个目录
mkdir -p /data/330{7,8,9}/data
准备配置文件
cat >/data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
cat >/data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
cat >/data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
初始化三套数据
mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysql3309.service
vim mysqld3307.service 修改为
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim mysqld3308.service 修改为
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim mysqld3309.service 修改为
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
授权
chown -R mysql.mysql /data/*
启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
验证多实例
netstat -lnp | grep 330
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"
mysql内置的功能
连接数据库
-u
-p
-S
-h
-P
-e
<
例子
mysql -uroot -p -S /tmp/mysql.sock
mysql -uroot -p -h10.0.0.51 -P3306
mysql -uroot -p -e "show databases;" 免交互执行SQL语句
mysql -uroot -p123 </root/world.sql 恢复数据
内置命令
help
\c ctrl+c 结束命令运行
\q quit exit ctrl+d 退出mysql
\G 格式化输出
source 恢复备份文件
SQL基础应用
结构化查询语言，关系型数据库通用命令，遵循SQL92的标准
SQL常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
SQL引入
数据库的逻辑结构
库
库名字
库属性 字符集，排序规则
表
表名
表属性 存储引擎类型，字符集，排序规则
列名
列属性 数据类型，约束，其他属性
数据行
字符集
show charset;
utf8 3个字节
utf8mb4(建议) 4个字节，支持emoji
排序规则
show collation;
对于英文字符串的大小写敏感
utf8mb4_general_ci 大小写敏感
utf8mb4_bin 大小写敏感(存拼音，日文)
数据类型
数字类型
整数
TINYINT
INT
浮点数
字符串类型
CHAT(100)
定长字符串类型，不管字符串长度多长，都立即分配100个字符长度的存储空间，未占满的空间使用"空格"填充
VARCHAR(100)
变长字符串类型，每次存储数据之前，都要先判断一下长度，按需分配此盘空间，会单独申请一个字符长度的空间存储字符长度(少于255，如果超过255以上会占用2个存储空间)
少于255个字符串长度，定长的列值选择CHAR，多于255字符长度，变长的字符串可以选择VARCHAR
ENUM
枚举数据类型
数据类型可能会影响到索引的性能
时间类型
DATETIME 范围从1000-01-01 9999-12-31
TIMESTAMP 范围从1970-01-01 2038-01-19
二进制类型
DDL的使用
DDL语句库的定义
创建库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;
删除库
DROP DATABASE oldguo;
修改数据库字符集(目标字符集一定是原字符集的严格超级)
SHOW CREATE DATABASE oldguo;
SHOW CREATE DATABASE oldguo;
ALTER DATABASE oldguo CGARSET utf8mb4;
库的定义规范
建库使用小写字符，库名不能以数字开头，不能是数据库内部的关键字，必须设置字符集
表定义
建表(表名，列名，列属性，表属性)
列属性
PRIMARY KEY 主键约束，表中只能有一个，非空且唯一
NOT NULL 非空约束，不允许空值
UNIQUE KEY 唯一键约束，不允许重复值
DEFAULT 一般配合NOT NULL一起使用
UNSIGNED 无符号，一般是配合数字列，非负数
COMMENT 注释
AUTO_INCREMENT 自增长列
CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT "学号",
sname VARCHAR(255) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄'，
gender ENUM('m', 'f', 'n') NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE INNODB CHARSET utf8mb4;
建表规范
表名小写字母，不能数字开头，不能是保留字符，，使用和业务有关的字符
选择合适的数据类型及长度
每个列设置NOL NULL+DEFAULT，对于数据0填充，对于字符使用有效数字
每个列设置注释
表必须设置存储引擎和字符集
主键列尽量是无关数字列，最好是自增长
enum类型不要保存数字，只能是字符串类型
查询建表信息
SHOW TABLES;
SHOW CREATE TABLE stu;
CREATE TABLE test LIKE stu;
删表
DROP TABLE test;
修改表
在stu表中添加qq列
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;
在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST;
把刚才添加的列都删掉
ALTER TABLE stu DROP num;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;
修改sname数据类型的属性
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';
将gender改为sex数据类型改为CHAT类型
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
DCL
GRANT
REVOKE
DML
INSERT
DESC stu;
INSERT stu VALUES(1,'zs',18,'m',NOW());
SELECT * FROM stu;
INSERT INTO stu (id,sname,age,sex,intime)
VALUES (2,'ls',19,'f',NOW());
INSERT INTO stu(sname,age,sex)
VALUES ('w5',11,'m');
INSERT INTO stu(sname,age,sex)
VALUES ('aa',11,'m'),('bb',12,'f'),('cc',13,'m');
UPDATE
DELETE
DQL
SELECT
SELECT单独使用的情况
SELECT @@basedir;
SELECT @@port;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW variables LIKE 'innodb%';
SELECT database();
SELECT NOW();
SELECT通用语法(单表)
SELECT 列 FROM 表 WHERE 条件 GROUP BY 条件 HAVING 条件 ORDER BY 条件 LIMIT
如何熟悉数据库业务
快速和研发人员打好关系，找到领导要ER图
DESC,SHOW CREATE TABLE
SELECT * FROM city LIMIT 5;
SELECT配合FROM子句使用
查询表中所有的信息
USE world;
SELECT id,NAME,countrycode,district,population FROM city;
SELECT * FROM city;
查询表中name和population的值
SELECT NAME,population FROM city;
SELECT配合WHERE子句使用
查询中国所有的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN';
世界上小于100人的城市名和人口数
SELECT NAME,population FROM city WHERE population<100;
查询中国人口数量大于1000万的城市名称和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN' AND population>10000000;
查询中国或美国的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN' or countrycode='USA';
查询人口数量在500w到600w之间的城市名称和人口数
SELECT NAME,population FROM city WHERE population>5000000 AND population<6000000;
SELECT NAME,population FROM city WHERE population BETWEEN 5000000 AND 6000000;
查询一下countrycode中有CH开头的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
不要出现类似于%CH%，前后都有%的语句，因为不走索引；如果业务中有大量需求，我们用ES来替代
查询中国或美国的城市信息
SELECT NAME,population FROM city WHERE countrycode IN ('CHN','USA');
SELECT 配合GROUP BY+聚合函数应用
统计每个国家城市的个数
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
统计每个国家的总人口数
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;
统计每个国家省的个数
SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode;
统计中国每个省的总人口数
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
统计中国每个省城市的个数
SELECT district,COUNT(NAME) WHERE countrycode='CHN' GROUP BY district;
统计中国每个省城市的名字列表
SELECT district,GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
SELECT CONCAT(district,":",GROUP_CONCAT(NAME)) FROM city WHERE countrycode='CHN' GROUP BY district;
SELECT配合HAVING应用
统计所有国家的总人口数量，将总人口数大于1亿的过滤出来
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>100000000;
SELECT配合ORDER BY子句
统计所有国家的总人口数量，将总人口数大于5千万的过滤出来，并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC;
SELECT配合LIMIT子句
统计所有国家的总人口数量，将总人口数大于5千万的过滤出来，并且按照从大到小顺序排列，只显示前3名
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3;
统计所有国家的总人口数量，将总人口数大于5千万的过滤出来，并且按照从大到小顺序排列，只显示前4到6名
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3,3;
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 3;
统计中国每个省的总人口数，只打印总人口数小于100万的
SELECT district,SUM(population) FROM city WHERE countrycode='CN' GROUP BY district HAVING SUM(population)<1000000;
查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC;
统计中国每个省的总人口，找出总人口大于500w的，并按总人口从大到小排序，只显示前3名
SELECT district,SUM(population) FROM city WHERE countrycode='CN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;
UNION和UNION ALL
是做多个结果集合并查询的功能，UNION ALL不做去重复，UNION会做去重操作
多表连接查询(内连接)
单表数据不能满足查询需求时
查询世界上小于100人的城市，所在的国家名，国土面积，城市名，人口数
SELECT country.name,country.SurfaceArea,city.name,city.Population FROM city JOIN country ON city.CountryCode=country.code WHERE city.population<100;
统计zhang3学习了几门课
SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3';
查询zzhang3，学习的课程名称有哪些
SELECT student.sname,course.cname FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3';
SELECT student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3' GROUP BY student.sname;
查询oldguo老师教的学生名和个数
SELECT teacher.tname,GROUP_CONCAT(student.sname), COUNT(student.sname) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' GROUP BY teacher.tname;
查询oldguo所教课程的平均分数
SELECT teacher.tname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo' GROUP BY sc.cno;
每位老师所教课程的平均分，并按平均分排序
SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno GROUP BY teacher.tname,course.cname ORDER BY AVG(sc.score);
查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,student.sname,sc.score FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' AND sc.score<60;
查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score)) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teacher.tno;
别名应用
表别名是全局调用的，列别名可以被HAVING和ORDER BY调用
表别名
SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) FROM teacher AS t JOIN course AS c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student AS st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno;
列别名
SELECT t.tname AS 讲师名,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) AS 不及格的 FROM teacher AS t JOIN course AS c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student AS st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno;
元数据获取
元数据
元数据是存储在"基表"中
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询基础功能
show命令是封装好的功能，提供元数据查询基础功能
information_schema的基本应用
tables视图的应用
use infromation_schema;
desc tables;
TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度
显示所有的库和表的信息
SELECT table_schema,TABLE_NAME FROM information_schema.tables;
SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema;
查询所有innodb引擎的表
SELECT table_shcema,table_name FROM information_schema.tables WHERE ENGINE='innodb';
统计world下的city表占用空间大小(平均行长度+行数+索引长度)
SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world' AND table_name='city';
统计world库数据量总大小
SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world';
统计每个库的数据量大小，并按数据量从大到小排序
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_KB DESC;
模仿以下语句进行数据库的分库分表备份
mysqldump -uroot -p123 world city >/bak/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123", table_schema," ", table_name,">/bak/",table_schema,"_",table_name,".sql") FROM information_schema.tables;
模仿以下语句，进行批量生成对world下的所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;
SELECT CONCAT("ALTER TABLE", table_schema,".",table_name, "DISCARD TABLESPACE;") FROM information_schema.tables WHERE table_schema='world';
SHOW介绍
SHOW DATABASES; 查看数据库名
SHOW TABLES; 查看建表名
SHOW CREATE DATABASE xxx; 查看建库语句
SHOW CREATE TABLE xxx; 查看建表语句
SHOW PROCESSLIST; 查看所有用户连接情况
SHOW CHARSET; 查看支持的字符集
SHOW COLLATION; 查看所有支持的校对规则
SHOW GRANTS FOR xxx; 查看用户权限信息
SHOW VARIABLES LIKE '%xxx%'; 查看参数信息
SHOW ENGINES; 查看所有支持的存储引擎类型
SHOW INDEX FROM xxx; 查看表的索引信息
SHOW ENGINE INNODB STATUS\G 查看INNODB引擎详细状态信息
SHOW BINARY LOGS; 查看二进制日志的列表信息
SHOW BINLOG EVEBTS 'XXX'; 查看二进制日志事件信息
SHOW MASTER STATUS; 查看mysql当前使用二进制日志信息
SHOW SLAVE STATUS\G 查看从库状态信息
SHOW RELAYLOG EVENTS IN ' '; 查看中继日志的事件信息
SHOW STATUS LIKE ' '; 查看数据库整体状态信息
