目錄
1.介紹和安裝
1.1 資料庫分類
RDBMS(關係型資料庫):Relational Database Management System
代表產品:Oracle MySQL MSSQL PG
NoSQL(非關係型資料庫):Not Only SQL
代表作品:MongoDB Redis ES
NewSQL(對各種新的可擴充套件/高效能資料庫的簡稱)
代表作品:spanner PolarDB(X) TDSQL TiDB 高斯
1.2 MySQL分支
1.Oracle
2.Percona
3.MariaDB
4.雲廠商
1.3 MySQL獲取
www.mysql.com
1.4 MySQL安裝
# 1.解壓 [root@localhost opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz # 2.軟連線 ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql # 3.修改環境變數 vim /etc/profile 新增下面的內容 export PATH=/usr/local/mysql/bin:$PATH source /etc/profile # 4.建立使用者 目錄 配置檔案 [root@localhost mysql]# useradd mysql [root@localhost mysql]# mkdir -p /data/3306/data [root@localhost mysql]# chown -R mysql.mysql /data [root@localhost mysql]# vim /etc/my.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock # 5.初始化資料 如果沒有配置檔案: mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data 如果有配置檔案: [root@localhost data]# mysqld --initialize-insecure # 6.準備啟動指令碼 [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL... SUCCESS! [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [root@localhost ~]# /etc/init.d/mysqld status SUCCESS! MySQL running (12134)
2.基礎管理
2.1 使用者管理
2.1.1 作用
1.用來登入MySQL
2.用來管理MySQL物件[ps:什麼是MySQL物件??]
2.1.2 定義
語法:使用者名稱@'白名單'
什麼是白名單?? 答：IP地址範圍
常見形式:
root@'%'
root@'localhost'
root@'10.0.0.%'
root@'10.0.0.2'
2.1.3 管理使用者
1.查詢使用者
-- 查詢使用者 mysql> select user,host ,authentication_string ,plugin from mysql.user; +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | user | host | authentication_string | plugin | +------------------+-----------+------------------------------------------------------------------------+-----------------------+ | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | | root | localhost | | caching_sha2_password | +------------------+-----------+------------------------------------------------------------------------+-----------------------+
注意: plugin,加密外掛,在8.0之後做了升級,caching_sha2_password,安全性增高了.
會導致很多老的客戶端程式無法連線至MySQL.早期版本是mysql_native_password.
2.建立使用者/修改使用者/刪除使用者
-- 建立使用者 mysql> create user root@'10.0.0.%' identified by '123'; mysql> create user user1@'10.0.0.%' identified with mysql_native_password by '123'; -- 修改使用者 mysql> alter user root@'10.0.0.%' identified with mysql_native_password by '123'; mysql> alter user user1@'10.0.0.%' account lock; mysql> alter user user1@'10.0.0.%' account unlock; -- 刪除使用者 mysql> drop user user1@'10.0.0.%';
注意: 8.0 之後 ,只能先建使用者後授權.
2.2 許可權管理
2.2.1 許可權列表
mysql> show privileges;
最常見的許可權:
1.ALL ? 不包含 grant option
2.select ,insert ,update ,delete
2.2.2 授權和回收許可權
-- 授權 mysql> grant all on *.* to root@'10.0.0.%'; -- 查詢許可權 mysql> show grants for root@'10.0.0.%'; -- 回收許可權 mysql> revoke drop on *.* from root@'10.0.0.%' ;
關於許可權作用範圍:
*.* : 所有庫下所有表
luffy.*: 單庫下的所有表
luffy.user: 單表
2.3 連線管理
2.3.1 socket檔案連線
-- 前提: 需要提前建立localhost白名單的使用者 [root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock
2.3.2 TCP/IP方式
-- 前提 需要將登陸客戶端IP加入白名單 [root@localhost ~]# mysql -uroot -p123 -h 10.0.0.111 -P3306
2.4 配置管理
2.4.1 離線配置
-- 配置檔案應用順序 [root@localhost ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf ---> /etc/mysql/my.cnf ---> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
-- 配置檔案結構 [root@localhost ~]# cat /etc/my.cnf
[mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock
注意:修改配置檔案,重啟資料庫.
2.4.2 線上配置
-- 通過專用配置命令進行修改. mysql> set global innodb_buffer_pool_size=16777216;
-- 檢視所有可以線上配置的引數 show variables show variables like '%date%';
3.MySQL的體系結構
不多說,直接上圖.
1.連線層
連線層作用有三:
1.提供連線協議: Socket檔案,TCP/IP
2.驗證使用者身份/授權表.
3.連線層提供了一個與sql層互動的執行緒
2.SQL層
SQL層作用有六:
1.接收連線層傳過來的sql語句
2.驗證sql語句的語法
3.驗證sql語句的語義（DDL,DQL,DML,DCL）
4.解析器：解析sql語句，生成執行計劃
5.優化器：從執行計劃中選擇最優的一條
優化???? 優化(邏輯優化,物理優化(索引)
6.執行器：執行選出來的SQL計劃
1.與儲存引擎層建立互動的執行緒
2.將要執行的sql語句傳到儲存引擎層
3.Engine層
作用:負責和磁碟互動
4.SQL
4.1 什麼是SQL?
關係型資料庫通用的語言.結構化查詢語言.[ps:什麼是結構化查詢語言?]
4.2 SQL_MODE
常用SQL_MODE引數:[ps:SQL_MODE詳解]
mysql> select @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
重點: ONLY_FULL_GROUP_BY ,5.7版本新特性.
4.3 資料型別
特別注意:
char(10) 定長
varchar(10) 變長
其他詳細的資料型別:[ps:MySQL中的資料型別]
4.4 字符集
utf8 最多存三位元組字元
utf8mb4 最多存四位元組字元 [emoji表情]
4.5 SQL種類
DDL : 資料定義語言
DML : 資料操作語言
什麼是DDL(資料定義語言?),什麼是DML(資料操作語言?):[ps:DDL和DML的定義]
4.6 DDL的應用規範
4.6.1 對庫(Database)的操作
庫:
create databasedrop database
alter database
規範:
a. 生產系統禁用drop 操作b. 庫名不要使用系統預留字元,不要大寫字母,不要數字開頭.
c. 建庫是顯式設定字符集.
4.6.2 對錶(Table)的操作
create table
規範:
表名不要使用系統預留字元,不要大寫字母,不要數字開頭.ob_user;不要超過18字元.
資料型別:
合適的 簡短的 足夠的
注意點:
每個表要有主鍵.
每個列儘可能非空,或者設定預設值
每個列要加註釋.
儲存引擎使用InnoDB 字符集 utf8mb4
alter table
主要用途:
新增列
刪除列
加索引
刪索引
該型別
關於alter table還需要知道的一個點:
8.0以前:Online DDL 需要業務低估期間做. 或者使用PT-OSC.
8.0之後:新增列可以直接做.
drop table 非必要不要使用.
4.7 關於DDL/DML和MySQL執行階段的問題
prepare MDL X 阻塞所有DML寫入 DDL
exec S 降級共享鎖 不阻塞DML , 阻塞DDL
commit MDL X 阻塞所有DML寫入 DDL
5.索引和執行計劃
5.1 什麼是索引?
相當一本書中的目錄.優化查詢(select update delete)
5.2 索引型別
BTREE *****
RTREE(空間資料索引)
HASH(雜湊索引)
FTEXT(全文索引)
5.3 BTREE 結構認識
b-tree
b+tree(b*tree) -->加強版
5.4 MySQL中如何應用BTREE?
5.4.1 聚簇索引
1.什麼是聚簇索引?
InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，聚簇索引就是按照每張表的主鍵構造一顆B+樹，同時葉子節點中存放的就是整張表的行記錄資料，也將聚集索引的葉子節點稱為資料頁。這個特性決定了索引組織表中資料也是索引的一部分；
2.聚簇索引的構造條件?
Primary Key / Unique+not null / RowID
3.聚簇索引的構造細節?
葉子節點(Leaf): 在錄入資料時,會按照聚簇索引邏輯順序,儲存到物理上連續的多個資料頁. 從而生成了葉子節點. 並且儲存相鄰葉子節點的雙向指標.
枝節點(No-Leaf): 選取葉子節點的ID的範圍+指標.
根節點(ROOT) : No-leaf節點的ID範圍+指標4.聚簇索引的優化效果?
通過ID列作為查詢條件時,會起到優化效果.
5.4.2 輔助索引
...