如何提高上百萬級(jí)記錄MySQL數(shù)據(jù)庫(kù)查詢速度

關(guān)于mysql處理百萬級(jí)以上的數(shù)據(jù)時(shí)如何提高其查詢速度的方法

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比稱多網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式稱多網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋稱多地區(qū)。費(fèi)用合理售后完善，十年實(shí)體公司更值得信賴。

最近一段時(shí)間由于工作需要，開始關(guān)注針對(duì)Mysql數(shù)據(jù)庫(kù)的select查詢語句的相關(guān)優(yōu)化方法。

由于在參與的實(shí)際項(xiàng)目中發(fā)現(xiàn)當(dāng)mysql表的數(shù)據(jù)量達(dá)到百萬級(jí)時(shí)，普通SQL查詢效率呈直線下降，而且如果where中的查詢條件較多時(shí)，其查詢速度簡(jiǎn)直無法容忍。曾經(jīng)測(cè)試對(duì)一個(gè)包含400多萬條記錄（有索引）的表執(zhí)行一條條件查詢，其查詢時(shí)間竟然高達(dá)40幾秒，相信這么高的查詢延時(shí)，任何用戶都會(huì)抓狂。因此如何提高sql語句查詢效率，顯得十分重要。以下是網(wǎng)上流傳比較廣泛的30種SQL查詢語句優(yōu)化方法：

1、應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

2、對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

3、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

4、盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5、下面的查詢也將導(dǎo)致全表掃描：(不能前置百分號(hào))

select id from t where name like ‘%c%’

若要提高效率，可以考慮全文檢索。

6、in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

9、應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)=’abc’–name以abc開頭的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

應(yīng)改為:

select id from t where name like ‘a(chǎn)bc%’

select id from t where createdate=’2005-11-30′ and createdate’2005-12-1′

10、不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

11、在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使 用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12、不要寫一些沒有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(…)

13、很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段 sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。

16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

17、盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

18、盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

19、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20、盡量使用表變量來代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。

21、避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22、臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В纾?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使 用導(dǎo)出表。

23、在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

24、如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

25、盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

26、使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

27、與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí) 間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28、在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送 DONE_IN_PROC 消息。

29、盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。

30、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

Mysql中如何創(chuàng)建一個(gè)表讓id自動(dòng)增長(zhǎng)？

CREATE TABLE users (

id int(5) NOT NULL auto_increment,

name varchar(20)NOT NULL,

PRIMARY KEY ?(`id`)

)

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，目前屬于?Oracle?旗下產(chǎn)品。MySQL 最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，在 WEB 應(yīng)用方面MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件之一。

MySQL是一種關(guān)聯(lián)數(shù)據(jù)庫(kù)管理系統(tǒng)，關(guān)聯(lián)數(shù)據(jù)庫(kù)將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，它分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

由于其社區(qū)版的性能卓越，搭配?PHP?和?Apache?可組成良好的開發(fā)環(huán)境。

mysql如何加快海量數(shù)據(jù)的刪除速度？

下面一些方法可以加快

mysql數(shù)據(jù)庫(kù)導(dǎo)入數(shù)據(jù)的速度：

1、最快的當(dāng)然是直接 copy 數(shù)據(jù)庫(kù)表的數(shù)據(jù)文件（版本和平臺(tái)最好要相同或相似）；

2、 設(shè)置 innodb_flush_log_at_trx_commit = 0 ，相對(duì)于 innodb_flush_log_at_trx_commit = 1 可以十分明顯的提升導(dǎo)入速度；

3、 使用 load data local infile 提速明顯；

4、 修改參數(shù) bulk_insert_buffer_size， 調(diào)大批量插入的緩存；

5、 合并多條 insert 為一條: insert into t values(a,b,c), (d,e,f) ,,,

6、手動(dòng)使用事物；

如何提升MySQL批量插入的效率

需要將大量數(shù)據(jù)(大概5W條)插入MySQL數(shù)

據(jù)庫(kù)，用普通的SQL

Statement執(zhí)行,時(shí)間大概是幾分鐘。于是想到用PreparedStatement，但是改了之后發(fā)現(xiàn)效率并沒有很大的提升。不成，想到了

load data local

infile...命令，以前一直認(rèn)為這條命令僅限MySQL終端上使用而不是標(biāo)準(zhǔn)的SQL語句，今天看了幾篇文章之后有了很大的收獲。

1. 使用PreparedStatement batch operation

以前使用PreparedStatement性能沒有很大提升的原因在于：

沒有使用批處理方法

在語句執(zhí)行之前應(yīng)關(guān)閉事務(wù)自動(dòng)提交，語句執(zhí)行完之后再提交

public

void batchLoad(Connection connection)

{

try

{

connection.setAutoCommit(false);

BufferedReader reader =

new BufferedReader(new

FileReader("tfacts_result"));

String sqlString =

"insert into test(node1, node2, weight) values(?, ?, ?)";

PreparedStatement pstmt = connection.prepareStatement(sqlString);

String line =

null;

while(true)

{

line = reader.readLine();

if(line == null)

{

break;

}

String[] columns = line.split("\t");

for(int

i = 1; i = columns.length; i++)

{

pstmt.setString(i, columns[i-1]);

}

pstmt.addBatch();

}

pstmt.executeBatch();

connection.commit();

pstmt.close();

reader.close();

}

catch (FileNotFoundException e) {

e.printStackTrace();

}catch

(SQLException e){

e.printStackTrace();

}catch

(IOException e){

e.printStackTrace();

}

2.使用load data local infile into tabel XXX（注意在文件中用\t將每列數(shù)據(jù)隔開）

public

void loadData(Connection connection)

{

long

starTime = System.currentTimeMillis();

String sqlString =

"load data local infile ? into table test";

PreparedStatement pstmt;

try

{

pstmt = connection.prepareStatement(sqlString);

pstmt.setString(1,

"tfacts_result");

pstmt.executeUpdate();

pstmt.close();

}

catch (SQLException e) {

e.printStackTrace();

}

long

endTime = System.currentTimeMillis();

System.out.println("program runs "

+ (endTime - starTime) + "ms");

}

測(cè)試了5W條數(shù)據(jù)，PreparedStatement耗時(shí)10s，而load data infile耗時(shí)3s。

把很多TXT文件內(nèi)容寫入MYSQL數(shù)據(jù)庫(kù)，有一億條數(shù)據(jù)，如何提高寫入效率？求高手賜教修改如下代碼

非root用戶運(yùn)行MySQL，當(dāng)MySQL配置比較高時(shí)，MySQL運(yùn)行中生效的參數(shù)值與配置的值不一樣,所以具體分析一下MySQL是怎么調(diào)整這些參數(shù)值的。?這篇文章的目的是為了說明在系統(tǒng)資源不夠的情況下，MySQL 是怎么調(diào)整者三個(gè)參數(shù)的。說明此文涉及到三個(gè)參數(shù)open_files_limit、?max_connections、?table_open_cache。與這三個(gè)參數(shù)相關(guān)的系統(tǒng)資源是打開文件數(shù)限制，即文件描述符（fd）限制。系統(tǒng)參數(shù)與文件描述符的關(guān)系?-?max_connection??fd?: 每一個(gè)MySQL connection ? ? ?都需要一個(gè)文件描述符；-?table_open_cache??fd?打開一張表至少需要一個(gè) ? ? ?文件描述符，如打開MyISAM需要兩個(gè)fd?；- 系統(tǒng)最大打開文件數(shù)可以通過?ulimit -n查看。MySQL調(diào)整參數(shù)的方式

根據(jù)配置（三個(gè)參數(shù)的配置值或默認(rèn)值）計(jì)算?request_open_files（需要的文件描述符）；

2.獲取有效的系統(tǒng)的限制值effective_open_files；? 3.根據(jù)effective_open_files調(diào)整request_open_files；? 4.根據(jù)調(diào)整后的request_open_files,計(jì)算實(shí)際生效的參數(shù)值（show variables?可查看參數(shù)值）。計(jì)算request_open_filesrequest_open_files有三個(gè)計(jì)算公式：1. ??? ?// 最大連接數(shù)+同時(shí)打開的表的最大數(shù)量+其他（各種日志等等）2. ? ? limit_1= max_connections+table_cache_size * 2 + 10;3.? ?4.???? ?//假設(shè)平均每個(gè)連接打開的表的數(shù)量（2-4）5. ? ? ?//源碼中是這么寫的：6. ? ? ?//We are trying to allocate no less than?7. ? ???// max_connections*5 file handles8. ? ? ?limit_2= max_connections * 5;9.? ?10. ? ?//mysql 默認(rèn)的默認(rèn)是500011. ? ?limit_3= open_files_limit ? open_files_limit : 5000;12. ?13. ? ? 所以open_files_limit期待的最低14. ? ? request_open_files= max(limit_1,limit_2,limit_3);計(jì)算effective_open_files:MySQL 的思路：

在有限值的的范圍內(nèi)MySQL?盡量將effective_open_files的值設(shè)大。

修正request_open_files

requested_open_files= min(effective_open_files,?request_open_files)

重新計(jì)算參數(shù)值

修正open_files_limit

open_files_limit?=?effective_open_files

修正max_connections

max_connections?根據(jù)?request_open_files?來做修正。1.? limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2;

如果配置的max_connections值大于limit，則將max_connections?的值修正為limit

其他情況下?max_connections?保留配置值

修正table_cache_size

table_cache_size?會(huì)根據(jù)?request_open_files?來做修正1. ??// mysql table_cache_size 最小值，4002. ? limit1 = TABLE_OPEN_CACHE_MIN3. ??// 根據(jù) requested_open_files 計(jì)算4. ? limit2 = (requested_open_files - 10 - max_connections) / 25. ? limit = max(limit1,limt2);

如果配置的table_cache_size?值大于limit，則將?table_cache_size?的值修正為limit

其他情況下table_cache_size?保留配置值

舉例

以下用例在非 root 用戶下運(yùn)行

參數(shù)設(shè)置：

//mysql

max_connections = 500

table_open_cache = 999

//ulimit -n

1500

生效的值：

open_files_limit = 1500? ?max_connections = min[(1500 - 10 - 800),500] = 500

table_open_cache = ( 1500 - 10 - 500) / 2 =495

分享標(biāo)題：mysql怎么將數(shù)據(jù)提升 mysql怎么更新數(shù)據(jù)
網(wǎng)頁鏈接：http://chinadenli.net/article14/hhjgge.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供虛擬主機(jī)、手機(jī)網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作、品牌網(wǎng)站制作、全網(wǎng)營(yíng)銷推廣、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)