mysql 一張有近一百個字段的表，通過主鍵查詢數(shù)據(jù)非常慢,查詢一條數(shù)據(jù)需要2秒左右。怎么作優(yōu)化

1張表100個字段。。。。分表，然后用表外連接查詢可以調(diào)高查詢效率，也可以用復(fù)合查詢，不過復(fù)合查詢效率沒有外連接查詢效率高，但是sql語句寫起來方便。如果數(shù)據(jù)量不是上百萬級別的，推薦用復(fù)合查詢。

創(chuàng)新互聯(lián)建站專注于企業(yè)營銷型網(wǎng)站、網(wǎng)站重做改版、江川網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、H5技術(shù)、商城網(wǎng)站制作、集團公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為江川等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

mysql排序字段中有時間函數(shù)如何優(yōu)化性能

您好，MySQL排序字段中有時間函數(shù)的優(yōu)化性能可以從以下幾個方面入手：

1. 使用索引：在排序字段中使用索引可以有效提高排序查詢的性能，并且可以減少排序查詢的時間。

2. 避免使用復(fù)雜的時間函數(shù)：盡量避免使用復(fù)雜的時間函數(shù)，因為這些函數(shù)會增加查詢的復(fù)雜度，從而降低查詢的性能。

3. 避免使用大量的排序字段：盡量避免使用大量的排序字段，這樣可以減少排序查詢的時間，從而提高查詢的性能。

4. 使用更高效的排序算法：使用更高效的排序算法，比如快速排序，可以更有效地排序查詢，從而提高查詢的性能。

5. 使用緩存：使用緩存可以有效地提高查詢的性能，尤其是對于頻繁查詢的排序字段。

總之，MySQL排序字段中有時間函數(shù)的優(yōu)化性能可以從以上幾個方面入手，從而有效地提高查詢的性能。

MySql中LongText類型大字段查詢優(yōu)化

1.mysql在操作數(shù)據(jù)的時候，以page為單位

??不管是更新，插入，刪除一行數(shù)據(jù)，都需要將那行數(shù)據(jù)所在的page讀到內(nèi)存中，然后在進行操作，這樣就存在一個命中率的問題，如果一個page中能夠相對的存放足夠多的行，那么命中率就會相對高一些，性能就會有提升

2.innodb的page大小默認(rèn)為16kb

??innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點為一雙向鏈表，因此每個頁中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k，但事實上應(yīng)該更小，有一些InnoDB內(nèi)部數(shù)據(jù)結(jié)構(gòu)要存儲以及預(yù)留操作空間，

3.blob，text大字段

??innodb只會存放前768字節(jié)在數(shù)據(jù)頁中，而剩余的數(shù)據(jù)則會存儲在溢出段中（發(fā)生溢出情況的時候適用），最大768字節(jié)的作用是便于創(chuàng)建前綴索引/prefix index，其余更多的內(nèi)容存儲在額外的page里，哪怕只是多了一個字節(jié)。因此，所有列長度越短越好

4.擴展存儲禁用了自適應(yīng)哈希

??因為需要完整的比較列的整個長度，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)（哈希幫助InnoDB非常快速的找到“猜測的位置”，但是必須檢查“猜測的位置”是不是正確）。因為自適應(yīng)哈希是完全的內(nèi)存結(jié)構(gòu)，并且直接指向Buffer Pool中訪問“最”頻繁的頁面，但對于擴展存儲空間卻無法使用Adaptive Hash

變長大字段類型包括blob,text,varchar，其中varchar列值長度大于某數(shù)N時也會存溢出頁，在latin1字符集下N值可以這樣計算：innodb的塊大小默認(rèn)為16kb，由于innodb存儲引擎表為索引組織表，樹底層的葉子節(jié)點為一雙向鏈表，因此每個頁中至少應(yīng)該有兩行記錄，這就決定了innodb在存儲一行數(shù)據(jù)的時候不能夠超過8k，減去其它列值所占字節(jié)數(shù)，約等于N。對于InnoDB，內(nèi)存是極為珍貴的，如果把768字節(jié)長度的blob都放在數(shù)據(jù)頁，雖然可以節(jié)省部分IO，但是能緩存行數(shù)就變少，也就是能緩存的索引值變少了，降低了索引效率

Mysql把每個BLOB和TEXT值當(dāng)作一個獨立的對象處理。存儲引擎在存儲時通常會做特殊處理。當(dāng)BLOB和TEXT值太大時，InnoDB會使用專門的“外部”儲存區(qū)域來進行存儲，此時每個值在行內(nèi)需要1~4個字節(jié)存儲一個指針，然后在內(nèi)部存儲區(qū)域存儲實際的值。

Mysql不能將BLOB和TEXT列全部長度的字符串進行索引

mysql的 io 以page為單位，因此不必要的數(shù)據(jù)（大字段）也會隨著需要操作的數(shù)據(jù)一同被讀取到內(nèi)存中來，這樣帶來的問題由于大字段會占用較大的內(nèi)存（相比其他小字段），使得內(nèi)存利用率較差，造成更多的隨機讀取。從上面的分析來看，我們已經(jīng)看到性能的瓶頸在于由于大字段存放在數(shù)據(jù)頁中，造成了內(nèi)存利用較差，帶來過多的隨機讀，那怎么來優(yōu)化掉這個大字段的影響

5.6版本以后，新增選項 innodb_page_size 可以修改innodb的page默認(rèn)大小，但并不推薦修改這個配置

5.6版本之后mysql新增索引FULLTEXT可用來增加大文本搜索速度

mysql百萬數(shù)據(jù)分頁查詢4秒，求教怎么優(yōu)化

很多應(yīng)用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導(dǎo)航欄。然而，如何通過MySQL更好的實現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。

我們先從一個常用但性能很差的查詢來看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數(shù)都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的問題在于offset(分頁偏移量)很大的時候，像下面這樣：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執(zhí)行計劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數(shù)據(jù)，MySQL會將大量最終不會使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請求也會對整個系統(tǒng)造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應(yīng)時間的方差變小。

對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。

下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。

高效的計算行數(shù)

如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會將行數(shù)存儲到表的元信息中。但如果引擎是InnoDB情況就會復(fù)雜一些，因為InnoDB不保存表的具體行數(shù)。

我們可以將行數(shù)緩存起來，然后可以通過一個守護進程定期更新或者用戶的某些操作導(dǎo)致緩存失效時，執(zhí)行下面的語句：

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個新的表“news”，按照時事性排序(最新發(fā)布的在最前面)，實現(xiàn)一個高性能的分頁。為了簡單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當(dāng)前頁面展示的最后一個Id。

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

查詢上一頁的語句類似，只不過需要傳入當(dāng)前頁的第一個Id，并且要逆序。

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查詢方式適合實現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現(xiàn)真正的頁面導(dǎo)航還是很難的,下面看看另一種方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage * $buttons

)C

WHERE cnt = 0;

通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應(yīng)的id。這種方法還有一個好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發(fā)布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發(fā)表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。

如果表中的記錄很少被刪除、修改，還可以將記錄對應(yīng)的頁碼存儲到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個記錄的時候，需要執(zhí)行下面的查詢重新生成對應(yīng)的頁號。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

當(dāng)然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON C.id = T.id

SET T.page = C.page;

現(xiàn)在想獲取任意一頁的元素就很簡單了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數(shù)據(jù)集相對小，并且沒有可用的索引的情況下—比如處理搜索結(jié)果時。在一個普通的服務(wù)器上執(zhí)行下面的查詢，當(dāng)有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() * 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下來就可以向下面一樣執(zhí)行分頁查詢了。

SELECT *

FROM _tmp

WHERE OFFSET = $offset

ORDER BY OFFSET

LIMIT $perpage;

簡單來說，對于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時掃描過多的記錄。

當(dāng)前名稱：mysql大字段怎么優(yōu)化 mysql字段大寫還是小寫
轉(zhuǎn)載注明：http://www.yijiale78.com/article22/dddoscc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、用戶體驗、手機網(wǎng)站建設(shè)、微信公眾號、自適應(yīng)網(wǎng)站、網(wǎng)頁設(shè)計公司

廣告

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