- 數(shù)據(jù)庫的介紹和安裝
1.1 數(shù)據(jù)庫產(chǎn)品
RDBMS: 結(jié)構(gòu)化存儲關(guān)系數(shù)據(jù)(二維表)
NoSQL : 無結(jié)構(gòu)存儲數(shù)據(jù)(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
- 體系結(jié)構(gòu)和基礎(chǔ)管理
2.1 C/S結(jié)構(gòu)
client: mysql , mysqldump
mysqld:
Scoket文件: -S /tmp/mysql.sock
TCP/IP : -h -P
2.2 實例
后臺守護進程mysqld+master thread + 干活 + 內(nèi)存結(jié)構(gòu)
2.3 mysqld 工作過程
SQL種類: DDL,DCL,DML,DQL
select 語句工作流程
select user,host from mysql.user;
2.4 邏輯結(jié)構(gòu)
庫 : 庫名,庫屬性
表 : 表名,表屬性,列(列名,列屬性)
2.5 物理結(jié)構(gòu)
表空間 : 獨立表空間,一張表一個表空間
段 : 一張表就是一個段,1個或多個區(qū)構(gòu)成
區(qū) : 一個區(qū)是,連續(xù)的64個頁,默認是1M
頁 : 連續(xù)4個OS block,默認是16K
把控一個原則: 連續(xù)
2.6 用戶權(quán)限管理
(1) 8.0 以前
grant : 建用戶,授權(quán),密碼 一條龍.
grant 權(quán)限 on 范圍 to 用戶 identified by '密碼'
reovke 回收權(quán)限
revoke 權(quán)限 on 范圍 from 用戶.
(2) 8.0
先建用戶再授權(quán)
create user 用戶名 identified by '密碼';
grant 權(quán)限 on 范圍 to 用戶;
(3) 忘記本地管理員密碼
--skip-grant-tables
--skip-networking
flush porivileges;
alter user root@'localhost' identified by '123';
2.7 連接管理
事先把用戶創(chuàng)建好.
客戶端工具
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的啟動關(guān)閉
啟動 :
Sys-V
systemd
mysqld_safe
mysqld
關(guān)閉 :
Sys-V
systemd
mysqladmin
2.9 初始化配置
[mysqld]
基本配置
basedir=/usr/local/mysql/ # 程序路徑
datadir=/data/mysql/data # 數(shù)據(jù)路徑
socket=/tmp/mysql.sock # socket文件位置
server_id=51 # 主機編號
port=3306 # 端口號
secure-file-priv=/tmp # into outfile 安全路徑
autocommit=0 # 自動提交功能(1/0)
max_allowed_packet # 控制最大數(shù)據(jù)包大小
日志有關(guān)
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事務(wù)到本地binlog (MHA必須開,雙主模式)
exprie_logs_days # 二進制日志過期時間
slow_query_log=1 # 慢日志開關(guān)
slow_query_log_file # 慢日志位置
long_query_time # 慢日志記錄條件(時間)
log_queries_not_using_indexes # 慢日志記錄條件(不走索引的)
log_error # 錯誤日志
general_log # 通用日志
InnoDB存儲引擎相關(guān)
innodb_flush_log_at_trx_commit=1 # redo刷新策略(0/1/2)
innodb_buffer_pool_size=2048M # 內(nèi)存池大小
innodb_flush_method=O_DIRECT # 數(shù)據(jù)和redo刷寫策略
innodb_log_buffer_size # redo buffer大小
innodb_file_per_table=1 # 獨立表空間設(shè)置
innodb_data_file_path # 共享表空間文件配置
innodb_log_file_size # redo 文件的大小
innodb_log_files_in_group # redo 文件的個數(shù)
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基礎(chǔ)及元數(shù)據(jù)獲取
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)
校對規(guī)則 :
表屬性:
存儲引擎 : engine = InnoDB
字符集 : charset = utf8(3) utfmb4(4)
注釋 : Comment
列屬性:
數(shù)據(jù)類型 : 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) : 最大多少字節(jié) 40; 定長
varchar(10) : 最大多少字節(jié) 41; 變長約束 :
PK : 非空切唯一,每個表只能有一個,聚簇索引,一般數(shù)字自增列.
NOT NULL : 非空 ,建議每個列非空
UNIQUE : 唯一
其他屬性: auto_increment , default ,comment
3.4 DDL
create
drop
alter
規(guī)范:
庫:
不能大寫,不能關(guān)鍵字,業(yè)務(wù)有關(guān),不能數(shù)字開頭,字符集.
表:
不能大寫,不能關(guān)鍵字,業(yè)務(wù)有關(guān),不能數(shù)字開頭,不要太多字符,字符集,主鍵,非空,默認值,注釋,數(shù)據(jù)類型,存儲引擎.
總字符長度不要超過65535.....
表的結(jié)構(gòu)更改: 業(yè)務(wù)低估,采用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. 特大數(shù)據(jù)行,假如100w數(shù)據(jù),分成多份批量.
update
要加where條件.
delete
要加where條件
全表刪除要用truncate
大表刪除部分行
降低 "高水位線" ---> archiver table;
3.7 DQL
select
show
3.8 元數(shù)據(jù)獲取
information_schma.tables
庫 , 表相關(guān)的資產(chǎn)統(tǒng)計及監(jiān)控
information_schma.columns
表的數(shù)據(jù)字典統(tǒng)計.
- 索引
4.1 MySQL索引類型
BTree
Rtree
FullTEXT
HASH
GIS
4.2 B樹演變
B-TREE :
B+TREE : 葉子節(jié)點,有雙向指針
B*TREE : 葉子和枝節(jié)點都有雙向指針
優(yōu)化了范圍查找:> ,< ,>=, <=
4.3 MySQL 怎么構(gòu)建BTREE
聚簇索引 : 一般是主鍵(ID),只能有一個
輔助索引 :
4.5 索引樹高度
前綴
alter tables t1 add index idx(name(10));
例如: city表中,name列要做前綴索引,判斷多少個字符合適
總行數(shù):
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
表結(jié)構(gòu)設(shè)計
數(shù)據(jù)類型
歸檔表
分布式
4.6 聯(lián)合索引
理論上可以減少回表查詢的次數(shù)
(a,b,c) ---> a ab abc
原則:
1. 遵循最左原則,唯一值多的放在最左側(cè)
2. 原則上不等值查詢的列(>,<,>=,<=,like)
3. where order ,按照子句順序創(chuàng)建索引
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 : 聯(lián)合索引應(yīng)用長度評估
(a,b,c)
數(shù)字:
最大儲值長度+[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 后的列有沒有聯(lián)合索引.
====================================
日常工作職責(zé):
(1) 基礎(chǔ)工作:
基本架構(gòu)搭建
配置文件管理
MySQL故障處理
SQL語句審核(規(guī)范設(shè)計)
配合開發(fā)進行Schema設(shè)計
備份恢復(fù)
(2) 自動化工作
配合運維開發(fā)設(shè)計數(shù)據(jù)庫自動化運維開發(fā)(啟停數(shù)據(jù)庫,用戶管理,監(jiān)控,SQL審核,工單,備份恢復(fù),日志收集)
數(shù)據(jù)庫資產(chǎn)統(tǒng)計
(3) 高級部分
數(shù)據(jù)庫優(yōu)化(安全,性能)
架構(gòu)設(shè)計(高可用,讀寫分離,分布式架構(gòu))
(4) 專家級
MySQL 內(nèi)核研究和二次開發(fā)
