mysql存儲設計

① 在mysql中怎樣設計一個存儲過程，根據"學號"返回成績表中平均成績在60分以上的學生的學分

drop procere if exists type;
delimiter $
create procere type(
in shuruxuehao VARCHAR(20),
out shuchuxuefen int(11)
)
BEGIN
set shuchuxuefen = 0;
set @num = 0;
set @xuefen = 0;
select sum(chengji1 +chengji2 + ... + chengjiN )/ n as num,xuefen into
@num, @xuefen from table where xuehao = shuruxuehao;/*chengji 是各科成績的欄位*/
if num >=60 then
set shuchuxuefen = @xuefen;
else
set shuchuxuefen = 0;
end if ;
END
$
delimiter ;

② k8s中的Mysql資料庫持久化存儲

 

一、配置：

環境：

CentOS7 

VMware

筆者配置了四台虛擬機：

K8S-Master節點: 3GB內存   2核CPU   20GB硬碟空間

K8S-node1節點:  2GB內存   2核CPU   30GB硬碟空間

K8S-node2節點:  2GB內存   2核CPU   30GB硬碟空間

鏡像倉庫節點:      2GB內存   2核CPU   50GB硬碟空間

二、節點規劃：

使用三台虛擬機搭建K8S集群，使用一台虛擬機搭建鏡像倉庫。

每台虛擬機配置兩塊網卡，其中一塊為「NAT模式」，用於拉取鏡像等功能。

另外一塊網卡為「僅主機模式」，用於集群節點間的通信。歸劃如下：

K8s-master節點:

僅主機模式：10.10.10.200

NAT模式：  192.168.200.130

K8S-node1節點:

僅主機模式：10.10.10.201

NAT模式：  192.168.200.131

K8S-node2節點:

僅主機模式：10.10.10.202

NAT模式：  192.168.200.132

鏡像倉庫節點:

僅主機模式：10.10.10.101

NAT模式：  192.168.200.150

三、版本信息

Linux內核版本：

Linux version 3.10.0-862.el7.x86_64 ([email protected])

(gcc version 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC) )

 #1 SMP Fri Apr 20 16:44:24 UTC 2018

K8s集群版本為1.15.0版本：

四、基於StatefulSet與PV/PVC的MySql持久化存儲實驗

1. 在每個節點安裝nfs服務

在「鏡像倉庫」節點，執行以下命令：

yum install -y nfs-common nfs-utils rpcbind

在k8s集群，執行以下命令：

yum install -y nfs-utils rpcbind

2. 在「鏡像倉庫」節點下，配置nfs伺服器

mkdir /nfs_mysql

Chmod 777 /nfs_mysql/

(在測試環境中，為了不考慮用戶屬性，暫時賦予777許可權，但在生產環境不推薦這樣做)

Chown nfsnobody /nfs_mysql/

echo 「/nfs_mysql *(rw,no_root_squash,no_all_squash,sync)」 >> /etc/exports

cat /etc/exports

/nfs_mysql *(rw,no_root_squash,no_all_squash,sync)

systemctl start rpcbind

systemctl start nfs

3. 測試nfs服務是否可用

mkdir /test

showmount -e 10.10.10.101

可見/nfs_mysql *已暴露於共享目錄，接下來測試掛載是否可用：

在master節點下執行：

mount -t nfs 10.10.10.101:/nfs_mysql /test/

echo "hello-world">>/test/1.txt

在鏡像倉庫節點下查看1.txt是否存在，若存在則掛載成功：

可見nfs服務可以正常使用，接下來刪除test目錄和1.txt

在鏡像倉庫下：

[root@hub nfs_mysql]# rm -f 1.txt

在Master節點下:

[root@k8s-master ~]# umount /test/

[root@k8s-master ~]# rm -rf /test/

同理,依照以上步驟同時創建：（提供多個mysql副本進行掛載）

nfs_mysql1

nfs_mysql2

完成後需要重啟nfs服務

systemctl restart rpcbind

systemctl restart nfs

最終效果：

4. 將nfs封裝成pv

創建mysql_test文件夾，將yaml文件統一保存在此目錄下

mkdir mysql_test

cd mysql_test

vim mysql-pv.yml

mysql-pv.yml配置如下：

apiVersion: v1

kind: PersistentVolume

metadata:

  name: mysql-pv

spec:

  capacity:

    storage: 5Gi

  accessModes:

    -  ReadWriteOnce

  persistentVolumeReclaimPolicy: Retain

  storageClassName: nfs

  nfs:

    path: /nfs_mysql

    server: 10.10.10.101

---

apiVersion: v1

kind: PersistentVolume

metadata:

  name: mysql-pv1

spec:

  capacity:

    storage: 5Gi

  accessModes:

    -  ReadWriteOnce

  persistentVolumeReclaimPolicy: Retain

  storageClassName: nfs

  nfs:

    path: /nfs_mysql1

    server: 10.10.10.101

---

apiVersion: v1

kind: PersistentVolume

metadata:

  name: mysql-pv2

spec:

  capacity:

    storage: 5Gi

  accessModes:

    -  ReadWriteOnce

  persistentVolumeReclaimPolicy: Retain

  storageClassName: nfs

  nfs:

    path: /nfs_mysql2

    server: 10.10.10.101

注意：

在k8s集群15版本中recycle回收策略已被刪除，只能用retain策略或者Delete策略。這里我們使用 persistentVolumeReclaimPolicy: Retain

 

執行命令：

kubectl create -f mysql-pv.yml

kubectl get pv

如圖所示，即為Pv創建成功。

5. 部署MySQL，在mysql_test目錄下編寫mysql.yml，配置文件如下

apiVersion: v1

kind: Service

metadata:

  name: mysql

  labels:

    app: mysql

spec:

  ports:

  - port: 3306

    name: mysql

  clusterIP: None

  selector:

    app: mysql

---

apiVersion: apps/v1

kind: StatefulSet

metadata:

  name: mysql

spec:

  selector:

    matchLabels:

      app: mysql

  serviceName: "mysql"

  replicas: 3

  template:

    metadata:

      labels:

        app: mysql

    spec:

      containers:

      - name: mysql

        image: mysql:5.6

        env:

        - name: MYSQL_ROOT_PASSWORD

          value: password

        ports:

        - containerPort: 3306

          name: mysql

        volumeMounts:

        - name: mysql-persistent-storage

          mountPath: /var/lib/mysql

  volumeClaimTemplates:

  - metadata:

      name: mysql-persistent-storage

    spec:

      accessModes: ["ReadWriteOnce"]

      storageClassName: "nfs"

      resources:

        requests:

          storage: 1Gi  

執行以下命令，部署mysql服務：

kubectl create -f mysql.yml

如圖可知，mysql按StatefulSet依次創建了mysql-0 mysql-1 mysql-2

查看各個Pod部在哪個節點：

6. 通過創建臨時容器，使用MySQL客戶端發送測試請求給MySQL master節點

注意：

主機名為mysql-0.mysql；跨命名空間的話，主機名請使用mysql-0.mysql. [NAMESPACE_NAME].如果沒有指定命名空間，默認為default，即 mysql-0.mysql. default。

   

這里筆者打算關閉node2節點來模擬node2宕機，來測試是否實現數據的持久化存儲，

所以我們向node2上的mysql1寫入數據。

 

執行以下命令，訪問mysql1：

kubectl run mysql-client --image=mysql:5.6 -it --rm --restart=Never -- mysql -h mysql-1.mysql.default -p password

創建資料庫demo,並向messages表中寫入hello-world

CREATE DATABASE demo; 

CREATE TABLE demo.messages (message VARCHAR(250)); 

INSERT INTO demo.messages VALUES ('hello-world');

如圖所示

接下來我們來關閉k8s-node2虛擬機，模擬宕機

查看nodes的運行狀態，可知node2的狀態已轉變為NotReady

一段時間後，k8s將Pod MySql -1遷移到節點k8s-node1

由於時間過長，筆者把三個Pod都刪除重啟後，驗證數據：

MySQL服務恢復，數據完好無損！

③ mysql存儲地圖經緯度的表怎麼設計

可以設計兩張關聯表 一個用來保存信息 一個用來保存X.Y點的信息 或者Mysql也支持空間數據，Geometry類型即可。
若有問題，請您及時追問我
若滿意，請您及時採納
謝謝您的關照~

④ mysql存儲方式MyISAM 和 InnoDB的區別

MYISAM 表是典型的數據與索引分離存儲，主鍵和二級索引沒有本質區別。比如在 MYISAM 表裡主鍵、唯一索引是一樣的，沒有本質區別。
INNODB 表本身是索引組織表，也就是說索引就是數據。下圖表T1的數據行以聚簇索引的方式展示，非葉子節點保存了主鍵的值，葉子節點保存了主鍵的值以及對應的數據行，並且每個頁有分別指向前後兩頁的指針。
INNODB 表不同於 MYISAM，INNODB 表有自己的數據頁管理，默認 16KB。MYISAM 表數據的管理依賴文件系統，比如文件系統一般默認 4KB，MYISAM的塊大小也是 4KB，MYISAM 表的沒有自己的一套崩潰恢復機制，全部依賴於文件系統。
INNODB 表這樣設計的優點有兩個：
1. 數據按照主鍵順序存儲。主鍵的順序也就是記錄行的物理順序，相比指向數據行指針的存放方式，避免了再次排序。我們知道，排序消耗最大。
2. 兩個葉子節點分別含有指向前後兩個節點的指針，這樣在插入新行或者進行頁分裂時，只需要移動對應的指針即可。
INNODB 二級索引的非葉子節點保存索引的欄位值，上圖索引為表 t1 的欄位 age。葉子節點含有索引欄位值和對應的主鍵值。
這樣做的優點是當出現數據行移動或者數據頁分裂時，避免二級索引不必要的維護工作。當數據需要更新的時候，二級索引不需要重建，只需要修改聚簇索引即可。
但是也有缺點：
1. 二級索引由於同時保存了主鍵值，體積會變大。特別是主鍵設計不合理的時候，比如用 UUID 做主鍵。
2. 對二級索引的檢索需要檢索兩次索引樹。第一次通過檢索二級索引葉子節點，找到過濾行對應的主鍵值；第二次通過這個主鍵的值去聚簇索引中查找對應的行。

⑤ Mysql或者sql，小說資料庫，儲存設計

文本文件不推薦存儲在資料庫中，應該單獨放在某個磁碟目錄下面，然後再資料庫中，記錄文件的存放目錄。

⑥ mysql存儲金額類型，用什麼數據類型比較可靠，一般企業數據用什麼數據類型

對於游戲幣等代幣，一般存儲為int類型是可行的。問題在於越界，int類型長度為11位。

在存儲人民幣相關的金額的時候，則只能存儲到9長度的人民幣，也就是說，最大隻能存儲999999999，不到10億的數值，如果業務增長很快的話，就會給自己留下隱患。

Decimal：Decimal為專門為財務相關問題設計的數據類型。

DECIMAL從MySQL5.1引入，列的聲明語法是DECIMAL(M,D)。在MySQL5.1中，參量的取值范圍如下：M是數字的最大數（精度）。其范圍為1～65（在較舊的MySQL版本中，允許的范圍是1～254），M的默認值是10。

D是小數點右側數字的數目（標度）。其范圍是0～30，但不得超過M。說明：float佔4個位元組，double佔8個位元組，decimail(M,D)佔M+2個位元組。

如DECIMAL(5,2)的最大值為9999.99，因為有7個位元組可用。能夠解決數據的范圍和精度的問題。

(6)mysql存儲設計擴展閱讀

MySQL數據類型DECIMAL用法：

MySQLDECIMAL數據類型用於在資料庫中存儲精確的數值。我們經常將DECIMAL數據類型用於保留准確精確度的列，例如會計系統中的貨幣數據。

要定義數據類型為DECIMAL的列，請使用以下語法：column_nameDECIMAL(P,D);

在上面的語法中：

P是表示有效數字數的精度。P范圍為1〜65。

D是表示小數點後的位數。D的范圍是0~30。MySQL要求D小於或等於(<=)P。

DECIMAL(P，D)表示列可以存儲D位小數的P位數。十進制列的實際范圍取決於精度和刻度。

與INT數據類型一樣，DECIMAL類型也具有UNSIGNED和ZEROFILL屬性。如果使用UNSIGNED屬性，則DECIMALUNSIGNED的列將不接受負值。

如果使用ZEROFILL，MySQL將把顯示值填充到0以顯示由列定義指定的寬度。另外，如果我們對DECIMAL列使用ZEROFILL，MySQL將自動將UNSIGNED屬性添加到列。

⑦ MYSQL資料庫的物理設計都包括哪些內容，怎麼設計

Log File物理結構

log block結構分為日誌頭段、日誌記錄、日誌尾部

Block Header

這個部分是每個Block的頭部，主要記錄的塊的信息

⑧ MYSQL存儲引擎InnoDB（三十五）：臨時表空間

InnoDB使用會話臨時表空間和全局臨時表空間。

在InnoDB配置為磁碟內部臨時表的存儲引擎時，會話臨時表空間存儲用戶創建的臨時表和優化器創建的內部臨時表。從 MySQL 8.0.16 開始，用於磁碟內部臨時表的存儲引擎固定為InnoDB。（之前，存儲引擎由internal_tmp_disk_storage_engine的值決定 ）

在第一次請求創建磁碟臨時表時會話臨時表空間從臨時表空間池中被分配給會話。一個會話最多分配兩個表空間，一個用於用戶創建的臨時表，另一個用於優化器創建的內部臨時表。分配給會話的臨時表空間用於會話創建的所有磁碟臨時表。當會話斷開連接時，其臨時表空間將被截斷並釋放回池中。伺服器啟動時會創建一個包含 10 個臨時表空間的池。池的大小永遠不會縮小，並且表空間會根據需要自動添加到池中。臨時表空間池在正常關閉或中止初始化時被刪除。會話臨時表空間文件在創建時大小為 5 頁，並且具有.ibt文件擴展名。

InnoDB為會話臨時表空間保留了40 萬個空間 ID。因為每次啟動伺服器時都會重新創建會話臨時表空間池，所以會話臨時表空間的空間 ID 在伺服器關閉時不會保留，並且可以重復使用。

innodb_temp_tablespaces_dir 變數定義了創建會話臨時表空間的位置。默認位置是 #innodb_temp數據目錄中的目錄。如果無法創建臨時表空間池，則會拒絕啟動。

在基於語句的復制 (SBR) 模式下，在副本上創建的臨時表駐留在單個會話臨時表空間中，該臨時表空間僅在 MySQL 伺服器關閉時被截斷。

INNODB_SESSION_TEMP_TABLESPACES 表提供有關會話臨時表空間的元數據。

該INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO表提供有關在InnoDB實例中處於活動狀態的用戶創建的臨時表的元數據。

全局臨時表空間 ( ibtmp1) 存儲對用戶創建的臨時表所做的更改的回滾段。

innodb_temp_data_file_path 變數定義了全局臨時表空間數據文件的相對路徑、名稱、大小和屬性。如果沒有為innodb_temp_data_file_path指定值 ，則默認行為是創建innodb_data_home_dir目錄中命名為ibtmp1的單個自動擴展數據文件。初始文件大小略大於 12MB。

全局臨時表空間在正常關閉或中止初始化時被刪除，並在每次伺服器啟動時重新創建。全局臨時表空間在創建時會收到一個動態生成的空間 ID。如果無法創建全局臨時表空間，則拒絕啟動。如果伺服器意外停止，則不會刪除全局臨時表空間。在這種情況下，資料庫管理員可以手動刪除全局臨時表空間或重新啟動 MySQL 伺服器。重新啟動 MySQL 伺服器會自動刪除並重新創建全局臨時表空間。

全局臨時表空間不能駐留在原始設備上。

INFORMATION_SCHEMA.FILES提供有關全局臨時表空間的元數據。發出與此類似的查詢以查看全局臨時表空間元數據：

默認情況下，全局臨時表空間數據文件會自動擴展並根據需要增加大小。

要確定全局臨時表空間數據文件是否正在自動擴展，請檢查以下 innodb_temp_data_file_path 設置：

要檢查全局臨時表空間數據文件的大小，請使用與此類似的查詢來查詢INFORMATION_SCHEMA.FILES表：

TotalSizeBytes顯示全局臨時表空間數據文件的當前大小。

或者，檢查操作系統上的全局臨時表空間數據文件大小。全局臨時表空間數據文件位於 innodb_temp_data_file_path 變數定義的目錄中。

要回收全局臨時表空間數據文件佔用的磁碟空間，請重新啟動 MySQL 伺服器。重新啟動伺服器會根據innodb_temp_data_file_path定義的屬性刪除並重新創建全局臨時表空間數據文件 。

要限制全局臨時表空間數據文件的大小，請配置 innodb_temp_data_file_path以指定最大文件大小。例如：

配置 innodb_temp_data_file_path 需要重新啟動伺服器。

⑨ MySQL分表實現上百萬上千萬記錄分布存儲的批量查詢設計模式詳解

我們知道可以將一個海量記錄的
MySQL
大表根據主鍵、時間欄位，條件欄位等分成若干個表甚至保存在若干伺服器中。
唯一的問題就是跨伺服器批量查詢麻煩，只能通過應用程序來解決。談談在Java中的解決思路。其他語言原理類似。
這里說的分表不是
MySQL
5.1
的
partition，而是人為把一個表分開存在若干表或不同的伺服器。
1.
應用程序級別實現
見示意圖
electThreadManager
分表數據查詢管理器
它為分表的每個database
or
server
建立一個
thread
pool
addTask()
-
添加任務
stopTask()
-
停止任務
getResult()
-
獲取執行結果
最快的執行時間
=
最慢的
MySQL
節點查詢消耗時間
最慢的執行時間
=
超時時間
某個
ThreadPool
忙時候處理流程
1.
假如
ThreadPoolN
非常忙，(也意味
DB
N
非常忙)；
2.
新的查詢任務到來，addTask(),
新的任務的一個thread加到ThreadPoolN任務排隊中
3.
外層應用已經獲得其他
thread
返回結果，繼續等待
4.
外層應用等待超時的時間到，調用
stopTask()
設置該任務全部
thread
中的停止標志,
外層應用返回。
5.
若干時間後，ThreadPoolN取到該排隊
Thread,
因為設置了停止位，線程直接運行完成。
2.
JDBC
層實現
做一個
JDBC
Driver
的包裝，攔截
PreparedStatement,
Statement
的
executeQuery()
然後調用
SelectThreadManager
完成
3.
MySQL
partition
MySQL
5.1
的
partition
功能由於單張表的數據跨文件，批量查詢時候同樣存在上述問題，不過它是在
MySQL
內部實現的，不需要外部調用者關心。其查詢實現的原理應該大致類似。
但
partition
只解決了
IO
的瓶頸，並不能解決
CPU
計算的瓶頸，因此無法代替傳統的手工分表方式。