- 数据库的介绍和安装
1.1 数据库产品
RDBMS: 结构化存储关系数据(二维表)
NoSQL : 无结构存储数据(json,KV...)
{
id:101,
name:zs
}
key:value
1.2 MySQL版本
5.6 : 5.6.34 36 38 40
5.7 : 5.7.18 20 ...
8.0 : 8.0.11
1.3 安装
5.6
scripts/mysql_install_db --user --basedir --datadir
5.7
bin/mysqld --initialize-insecure --user --basedir --datadir
- 体系结构和基础管理
2.1 C/S结构
client: mysql , mysqldump
mysqld:
Scoket文件: -S /tmp/mysql.sock
TCP/IP : -h -P
2.2 实例
后台守护进程mysqld+master thread + 干活 + 内存结构
2.3 mysqld 工作过程
SQL种类: DDL,DCL,DML,DQL
select 语句工作流程
select user,host from mysql.user;
2.4 逻辑结构
库 : 库名,库属性
表 : 表名,表属性,列(列名,列属性)
2.5 物理结构
表空间 : 独立表空间,一张表一个表空间
段 : 一张表就是一个段,1个或多个区构成
区 : 一个区是,连续的64个页,默认是1M
页 : 连续4个OS block,默认是16K
把控一个原则: 连续
2.6 用户权限管理
(1) 8.0 以前
grant : 建用户,授权,密码 一条龙.
grant 权限 on 范围 to 用户 identified by '密码'
reovke 回收权限
revoke 权限 on 范围 from 用户.
(2) 8.0
先建用户再授权
create user 用户名 identified by '密码';
grant 权限 on 范围 to 用户;
(3) 忘记本地管理员密码
--skip-grant-tables
--skip-networking
flush porivileges;
alter user root@'localhost' identified by '123';
2.7 连接管理
事先把用户创建好.
客户端工具
mysql :
mysql -uroot -p123 -S /tmp/mysql.sock
mysql -uroot -p123 -h 10.0.0.51 -P 3306
安装API
yum install php-mysql
pip3 install pymysql
xxxxx.jar
https://dev.mysql.com/downloads/
2.8 MySQL的启动关闭
启动 :
Sys-V
systemd
mysqld_safe
mysqld
关闭 :
Sys-V
systemd
mysqladmin
2.9 初始化配置
[mysqld]
基本配置
basedir=/usr/local/mysql/ # 程序路径
datadir=/data/mysql/data # 数据路径
socket=/tmp/mysql.sock # socket文件位置
server_id=51 # 主机编号
port=3306 # 端口号
secure-file-priv=/tmp # into outfile 安全路径
autocommit=0 # 自动提交功能(1/0)
max_allowed_packet # 控制最大数据包大小
日志有关
log_bin=/data/binlog/mysql-bin # 二进制日志位置
binlog_format=row # 二进制日志格式
sync_binlog=1 # 控制二进制日志刷新策略
gtid-mode=on # 开启GTID模式
enforce-gtid-consistency=true # 强制GTID一致性
log-slave-updates=1 # 从库中强制更新GTID事务到本地binlog (MHA必须开,双主模式)
exprie_logs_days # 二进制日志过期时间
slow_query_log=1 # 慢日志开关
slow_query_log_file # 慢日志位置
long_query_time # 慢日志记录条件(时间)
log_queries_not_using_indexes # 慢日志记录条件(不走索引的)
log_error # 错误日志
general_log # 通用日志
InnoDB存储引擎相关
innodb_flush_log_at_trx_commit=1 # redo刷新策略(0/1/2)
innodb_buffer_pool_size=2048M # 内存池大小
innodb_flush_method=O_DIRECT # 数据和redo刷写策略
innodb_log_buffer_size # redo buffer大小
innodb_file_per_table=1 # 独立表空间设置
innodb_data_file_path # 共享表空间文件配置
innodb_log_file_size # redo 文件的大小
innodb_log_files_in_group # redo 文件的个数
transaction_isolation # 隔离级别(RC,RR)
[mysql]
prompt=db01 [\d]>
SET SQL_LOG_BIN= 0;
SET GTID_PURGED='444c6d6c-0c49-11ea-b94f-000c29:1-10';
2.10 多实例
参考多实例配置文档.
-
SQL基础及元数据获取
3.1 SQL类型
3.2 SQL_MODE
ONLY_FULL_GROUP_BY
select group_concat(name),sex,avg(age) from student group by sex;
张三,李四 ,王五 男 20
3.3 属性
库属性:
字符集 : charset = utf8(3) utfmb4(4)
校对规则 :
表属性:
存储引擎 : engine = InnoDB
字符集 : charset = utf8(3) utfmb4(4)
注释 : Comment
列属性:
数据类型 : int ,tinyint , char() ,varchar(),enum(), datetime ,timestamp
int : 4 Bytes: 1Bytes=8bit 00000000 ~ 11111111 =0-255, 0-232-1,-231~2^31-1
char(10) : 最大多少字节 40; 定长
varchar(10) : 最大多少字节 41; 变长约束 :
PK : 非空切唯一,每个表只能有一个,聚簇索引,一般数字自增列.
NOT NULL : 非空 ,建议每个列非空
UNIQUE : 唯一
其他属性: auto_increment , default ,comment
3.4 DDL
create
drop
alter
规范:
库:
不能大写,不能关键字,业务有关,不能数字开头,字符集.
表:
不能大写,不能关键字,业务有关,不能数字开头,不要太多字符,字符集,主键,非空,默认值,注释,数据类型,存储引擎.
总字符长度不要超过65535.....
表的结构更改: 业务低估,采用pt-osc.
3.5 DCL
grant
revoke
3.6 DML
insert
insert into t1(id,name,age)
values(1,'zs',18),(2,'ls',19);
说明:
1. 有多行录入,最好是批量
2. 特大数据行,假如100w数据,分成多份批量.
update
要加where条件.
delete
要加where条件
全表删除要用truncate
大表删除部分行
降低 "高水位线" ---> archiver table;
3.7 DQL
select
show
3.8 元数据获取
information_schma.tables
库 , 表相关的资产统计及监控
information_schma.columns
表的数据字典统计.
- 索引
4.1 MySQL索引类型
BTree
Rtree
FullTEXT
HASH
GIS
4.2 B树演变
B-TREE :
B+TREE : 叶子节点,有双向指针
B*TREE : 叶子和枝节点都有双向指针
优化了范围查找:> ,< ,>=, <=
4.3 MySQL 怎么构建BTREE
聚簇索引 : 一般是主键(ID),只能有一个
辅助索引 :
4.5 索引树高度
前缀
alter tables t1 add index idx(name(10));
例如: city表中,name列要做前缀索引,判断多少个字符合适
总行数:
select count(*) from world.city ; ----> 4079
提取name的前5个字符,
select count(distinct (left(name,5)) from world.city ; ---> 3554
select count(distinct (left(name,6)) from world.city ; ---> 3775
表结构设计
数据类型
归档表
分布式
4.6 联合索引
理论上可以减少回表查询的次数
(a,b,c) ---> a ab abc
原则:
1. 遵循最左原则,唯一值多的放在最左侧
2. 原则上不等值查询的列(>,<,>=,<=,like)
3. where order ,按照子句顺序创建索引
4.7 索引命令
alter table t1 add index idx_xxx(xxx);
alter table t1 drop index idx_xxx;
desc city;
show index from city;
4.8 explain
show processlist; -----> explain
slow log; ----> mysqldumpslow,pt-query-digest --> explain
type : ALL , index , range , ref ,eq_ref ,const(system)
key_len : 联合索引应用长度评估
(a,b,c)
数字:
最大储值长度+[1]
not null 没有
int 4 4+1
tinyint 1 1+1
字符串
字符集: utf8(3) utf8mb4(4)
char(10) 10字符集最大+[1]
varchar(10) 10字符集最大+2+[1]
utf8(3)
not null 没有not null
char(10) 103 103+1
varchar(10) 103+2 103+2+1
utf8mb4(4)
not null 没有not null
char(10) 104 104+1
varchar(10) 104+2 104+2+1
extra
filesort =====> order by group by distinct 后的列有没有联合索引.
====================================
日常工作职责:
(1) 基础工作:
基本架构搭建
配置文件管理
MySQL故障处理
SQL语句审核(规范设计)
配合开发进行Schema设计
备份恢复
(2) 自动化工作
配合运维开发设计数据库自动化运维开发(启停数据库,用户管理,监控,SQL审核,工单,备份恢复,日志收集)
数据库资产统计
(3) 高级部分
数据库优化(安全,性能)
架构设计(高可用,读写分离,分布式架构)
(4) 专家级
MySQL 内核研究和二次开发
