需求來源

今天甲方這邊要查看一個機(jī)車的周時數(shù)據(jù)（就是一個機(jī)車從到我的管轄范圍內(nèi)，到出我的管轄內(nèi)所用的時間），那這個它會跑很多次，我們要查詢這一天的周時數(shù)據(jù)，錨定一個點比如出管轄區(qū)的時間，那么根據(jù)查詢到今天所有這個時間范圍內(nèi)出去的車信息，然后去數(shù)據(jù)表里找這個機(jī)車進(jìn)來的數(shù)據(jù)且時最新的一條就行了。

實現(xiàn)思路

分兩次查詢的第一次查詢出來所有的當(dāng)天出管轄區(qū)的機(jī)車信息，第二個查詢是根據(jù)第一個查詢小小的改動，把時間范圍去掉就好，然后根據(jù)機(jī)車信息進(jìn)行組取時間每個機(jī)車時間最新的數(shù)據(jù)就好。直接使用group by，但是這個并不能取出其它的信息所以就pass掉了。GPTl了一下給的方案是使用partition這個功能。

實施

就不看項目數(shù)據(jù)了就看一下我寫的小demo的結(jié)果吧。我有一個student表，這個表里有10個班的學(xué)生，每個班的學(xué)生有20個，我現(xiàn)在要取出每個班的學(xué)生id最大的這個記錄，就可以使用這個partition了。

select * from (SELECT *, ROW_NUMBER() over (partition by classes_id order by id desc) as rn FROM `student`) a where rn=1

解釋一下這個啊。

這條 SQL 語句使用了窗口函數(shù) ROW_NUMBER() 來為每個 classes_id 組中的行編號，并在外部查詢中只選擇每個 classes_id 組中的最新一行（根據(jù) id 倒序排序）。以下是對這條 SQL 語句的詳細(xì)解釋：

SQL 語句結(jié)構(gòu)

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

內(nèi)部查詢（子查詢）

SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
FROM `student`

SELECT *：

選擇 student 表中的所有列。

ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn：

• ROW_NUMBER() 是一個窗口函數(shù)，它為結(jié)果集中的每一行分配唯一的行號。
  • OVER 子句定義了窗口的分區(qū)和排序規(guī)則： PARTITION BY classes_id：將結(jié)果集按 classes_id 列進(jìn)行分組。對于每個 classes_id，將重新開始編號。
  • ORDER BY id DESC：在每個 classes_id 分區(qū)中，按照 id 列的降序排序。
• AS rn：將生成的行號列命名為 rn。

這部分查詢?yōu)槊總€ classes_id 組中的行編號，編號從1開始，按照 id 倒序排列。因此，rn 為1的行是每個 classes_id 組中 id 最大的行。

外部查詢

SELECT * 
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY classes_id ORDER BY id DESC) AS rn 
    FROM `student`
) a 
WHERE rn = 1;

FROM (...) a：

將內(nèi)部查詢的結(jié)果作為一個臨時表 a。

WHERE rn = 1：

篩選出臨時表 a 中 rn 等于 1 的行，即每個 classes_id 組中 id 最大的行。 結(jié)果

整個查詢的作用是：

• 對 student 表進(jìn)行分組（按 classes_id）。
• 在每個 classes_id 組中，按 id 倒序排列，并為每行分配一個行號 rn。
• 選擇每個 classes_id 組中 rn 等于 1 的行（即每個 classes_id 組中 id 最大的行）。

partition的升級使用

partition不僅僅可以在日常查詢中使用，還可以在表的數(shù)據(jù)結(jié)構(gòu)上進(jìn)行優(yōu)化，比如在建表的時候創(chuàng)建分區(qū)或者后期添加分區(qū)，這個分區(qū)操作是在物理上的操作，可以看我下面這張表的結(jié)構(gòu)，有一部分注釋說明就是分區(qū)的設(shè)置，

對表進(jìn)行分區(qū)可以提升查詢性能和數(shù)據(jù)管理的效率。由于 ENGINE=MyISAM 不支持分區(qū)，我們需要將表的存儲引擎更改為 InnoDB，因為 InnoDB 支持分區(qū)。

假設(shè)我們要根據(jù) id 列進(jìn)行范圍分區(qū)，將數(shù)據(jù)劃分為四個分區(qū)：

• p0：包含 id 小于 10000的數(shù)據(jù)。
• p1：包含 id 小于 20000的數(shù)據(jù)。
• p2：包含 id 小于 50000的數(shù)據(jù)。
• p3：包含其余的數(shù)據(jù)。

PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN (20000),
    PARTITION p2 VALUES LESS THAN (50000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

解釋

• PARTITION BY RANGE (id): 根據(jù) id 列進(jìn)行范圍分區(qū)。
• PARTITION p0 VALUES LESS THAN (10000): 第一個分區(qū)，包含 id 小于 10000的數(shù)據(jù)。
• PARTITION p1 VALUES LESS THAN (20000): 第二個分區(qū)，包含 id 小于 20000的數(shù)據(jù)。
• PARTITION p2 VALUES LESS THAN (50000): 第三個分區(qū)，包含 id 小于 50000的數(shù)據(jù)。
• PARTITION p3 VALUES LESS THAN MAXVALUE: 第四個分區(qū)，包含 id 大于等于 50000的數(shù)據(jù)。

這樣，表 products 就被劃分為四個分區(qū)，每個分區(qū)包含一定范圍的 id 值的數(shù)據(jù)。

驗證一下看看分區(qū)

上面說了創(chuàng)建分區(qū)了，但是怎么才能確定我們的查詢sql使用到了分區(qū)呢？使用explain來查看執(zhí)行的sql有沒有在分區(qū)的范圍呢，
下面是使用了explain查看執(zhí)行的sql有沒有用到分區(qū)，partition的值為p0對應(yīng)了上面設(shè)置的分區(qū)。

分區(qū)的一些操作

創(chuàng)建分區(qū)后，數(shù)據(jù)庫管理系統(tǒng)會自動處理分區(qū)的數(shù)據(jù)存儲和檢索，用戶在日常操作中并不需要特殊處理分區(qū)。不過，你可以通過一些特定的查詢和操作來利用分區(qū)的優(yōu)勢。以下是一些常見的用法示例：

1. 普通查詢

普通的查詢不需要特別處理分區(qū)，數(shù)據(jù)庫管理系統(tǒng)會自動根據(jù)分區(qū)優(yōu)化查詢：

SELECT * FROM student WHERE id < 50;

2. 分區(qū)表上的查詢優(yōu)化

當(dāng)你的查詢條件包含分區(qū)鍵時，數(shù)據(jù)庫會自動選擇相關(guān)的分區(qū)進(jìn)行查詢，從而提高查詢性能。例如：

SELECT * FROM student WHERE id BETWEEN 50 AND 100;

3. 插入數(shù)據(jù)

插入數(shù)據(jù)時，數(shù)據(jù)庫會根據(jù)分區(qū)鍵自動將數(shù)據(jù)插入到相應(yīng)的分區(qū)：

INSERT INTO student (name, classes_id) VALUES ('Alice', 1);

4. 刪除分區(qū)中的數(shù)據(jù)

可以通過分區(qū)鍵刪除特定分區(qū)中的數(shù)據(jù)：

DELETE FROM student WHERE id < 50;

5. 分區(qū)維護(hù)操作

你可以進(jìn)行一些特定的分區(qū)維護(hù)操作，例如合并分區(qū)、拆分分區(qū)、刪除分區(qū)等：

添加新的分區(qū)

ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);

刪除分區(qū)

ALTER TABLE student DROP PARTITION p0;

重組分區(qū)

可以將多個分區(qū)合并為一個分區(qū)：

ALTER TABLE student REORGANIZE PARTITION p1, p2 INTO (
    PARTITION p1_2 VALUES LESS THAN (150)
);

6. 檢查分區(qū)信息

你可以使用 SHOW 語句查看表的分區(qū)信息：

SHOW CREATE TABLE student;

總結(jié)

綜合示例展示了如何創(chuàng)建分區(qū)表、插入數(shù)據(jù)以及進(jìn)行查詢和維護(hù)操作：

-- 創(chuàng)建分區(qū)表
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `classes_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK4l5dnicegnvpmu0pv6vdvrmb6` (`classes_id`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb3
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (50),
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (150),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- 插入數(shù)據(jù)
INSERT INTO student (name, classes_id) VALUES ('Alice', 1);
INSERT INTO student (name, classes_id) VALUES ('Bob', 2);
-- 查詢數(shù)據(jù)
SELECT * FROM student WHERE id < 50;
-- 刪除分區(qū)中的數(shù)據(jù)
DELETE FROM student WHERE id < 50;
-- 添加新分區(qū)
ALTER TABLE student ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (200)
);
-- 刪除分區(qū)
ALTER TABLE student DROP PARTITION p0;
-- 檢查分區(qū)信息
SHOW CREATE TABLE student;

目前先整理這么多，以后有深入學(xué)習(xí)使用了再繼續(xù)?。?！

到此這篇關(guān)于SQL中的partition分區(qū)功能使用的文章就介紹到這了,更多相關(guān)sql partition分區(qū)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論