數(shù)據(jù)庫分區(qū)常用的技術(shù)_數(shù)據(jù)庫分區(qū)有哪些技術(shù)

　　當(dāng)MySQL的總記錄數(shù)超過了100萬后，會出現(xiàn)性能的大幅度下降嗎?下面由學(xué)習(xí)啦小編為大家整理的數(shù)據(jù)庫分區(qū)技術(shù)，希望大家喜歡!

　　數(shù)據(jù)庫分區(qū)技術(shù)

　　一、概述

　　1.只能在相同結(jié)構(gòu)的 MyISAM 表上使用

　　2.無法享受到 MyISAM 的全部功能，例如無法在 MERGE 類型上執(zhí)行 FULLTEXT 搜索

　　3.它需要使用更多的文件描述符

　　4.讀取索引更慢

　　這個時候，MySQL 5.1 中新增的分區(qū)(Partition)功能的優(yōu)勢也就很明顯了：

　　1.與單個磁盤或文件系統(tǒng)分區(qū)相比，可以存儲更多的數(shù)據(jù)

　　2.很容易就能刪除不用或者過時的數(shù)據(jù)

　　3.一些查詢可以得到極大的優(yōu)化

　　4.涉及到 SUM()/COUNT() 等聚合函數(shù)時，可以并行進行

　　5.IO吞吐量更大

　　分區(qū)允許可以設(shè)置為任意大小的規(guī)則，跨文件系統(tǒng)分配單個表的多個部分。實際上，表的不同部分在不同的位置被存儲為單獨的表。

　　分區(qū)應(yīng)該注意的事項：

　　1、 做分區(qū)時，要么不定義主鍵，要么把分區(qū)字段加入到主鍵中。

　　2、 分區(qū)字段不能為NULL，要不然怎么確定分區(qū)范圍呢，所以盡量NOT NULL

　　二、分區(qū)的類型

　　1.RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。

　　2.LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。

　　2.HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數(shù)可以包>含MySQL中有效的、產(chǎn)生非負整數(shù)值的任何表達式。

　　3.KEY分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含>整數(shù)值。

　　數(shù)據(jù)庫分頁查詢方法

　　第一種方案、最簡單、普通的方法：

　　復(fù)制代碼代碼如下:

　　SELECT TOP 30 * FROM ARTICLE WHERE ID NOT IN(SELECT TOP 45000 ID FROM ARTICLE ORDER BY YEAR DESC, ID DESC) ORDER BY YEAR DESC,ID DESC

　　平均查詢100次所需時間：45s

　　第二種方案：

　　復(fù)制代碼代碼如下:

　　SELECT * FROM (

　　SELECT TOP 30 * FROM (SELECT TOP 45030 * FROM ARTICLE ORDER BY YEAR DESC, ID DESC) f ORDER BY f.YEAR ASC, f.ID DESC) s ORDER BY s.YEAR DESC,s.ID DESC

　　平均查詢100次所需時間：138S

　　第三種方案：

　　復(fù)制代碼代碼如下:

　　SELECT * FROM ARTICLE w1,

　　(

　　SELECT TOP 30 ID FROM

　　(

　　SELECT TOP 50030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

　　) w ORDER BY w.YEAR ASC, w.ID ASC

　　) w2 WHERE w1.ID = w2.ID ORDER BY w1.YEAR DESC, w1.ID DESC

　　平均查詢100次所需時間：21S

　　第四種方案：

　　復(fù)制代碼代碼如下:

　　SELECT * FROM ARTICLE w1

　　WHERE ID in

　　(

　　SELECT top 30 ID FROM

　　(

　　SELECT top 45030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC

　　) w ORDER BY w.YEAR ASC, w.ID ASC

　　)

　　ORDER BY w1.YEAR DESC, w1.ID DESC

　　平均查詢100次所需時間：20S

　　第五種方案：

　　復(fù)制代碼代碼如下:

　　SELECT w2.n, w1.* FROM ARTICLE w1, (

　　SELECT TOP 50030 row_number() OVER (ORDER BY YEAR DESC, ID DESC) n, ID FROM ARTICLE ) w2 WHERE w1.ID = w2.ID AND w2.n > 50000 ORDER BY w2.n ASC

　　平均查詢100次所需時間：15S

　　數(shù)據(jù)庫臨時表刪除的方法

　　1、錯誤的刪除操作：

　　--錯誤的臨時表刪除操作，因為所在數(shù)據(jù)庫不同

　　IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[#tempTable]') AND type in (N'U'))

　　Begin

　　DROP TABLE [dbo].[tempTable]

　　End

　　--錯誤的臨時表刪除操作，因為臨時表名已變

　　if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[#temptable]'))

　　Begin

　　drop table #temptable

　　End

　　2、正確的刪除方式：

　　--正確的臨時表刪除操作

　　if object_id('tempdb#tempTable') is not null Begin

　　drop table #tempTable

　　End