mysql查詢所用時(shí)間過長 如何優(yōu)化？

建議這種情況下，可以只查詢部分內(nèi)容即可?？梢杂胠imit方法進(jìn)行限制查詢條數(shù)的多少。

創(chuàng)新互聯(lián)提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)，品牌網(wǎng)站制作，廣告投放等致力于企業(yè)網(wǎng)站建設(shè)與公司網(wǎng)站制作，十多年的網(wǎng)站開發(fā)和建站經(jīng)驗(yàn),助力企業(yè)信息化建設(shè)，成功案例突破上千,是您實(shí)現(xiàn)網(wǎng)站建設(shè)的好選擇.

舉例：

select * from tablename;//此時(shí)查詢的是所有的記錄，用時(shí)可能比較多。

select * from tablename limit 20,40;//這樣就只會查詢出需要的第20條到40條。

備注：除了此優(yōu)化外，可以只查詢需要的字段，還可以增加其余的一些where條件，來減少數(shù)據(jù)查詢的壓力。

mysql數(shù)據(jù)量大通過時(shí)間范圍查詢?nèi)绾蝺?yōu)化

最簡單的方式是根據(jù)時(shí)間建立索引，以提升查詢效率。具體還要根據(jù)情況分析，查詢的結(jié)果過大，會造成查詢慢。

mysql 千萬級數(shù)據(jù) 分頁優(yōu)化 條件是某個(gè)日期

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

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

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個(gè)查詢耗時(shí)0.00sec。So，這個(gè)查詢有什么問題呢？實(shí)際上，這個(gè)查詢語句和參數(shù)都沒有問題，因?yàn)樗玫搅讼旅姹淼闹麈I，而且只讀取15條記錄。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

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

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

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

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

SELECT COUNT(*)

FROM city;

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

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

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

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

高效的計(jì)算行數(shù)

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

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

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

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

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

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

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

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

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查詢方式適合實(shí)現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實(shí)現(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;

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

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

SET p:= 0;

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

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

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é)果時(shí)。在一個(gè)普通的服務(wù)器上執(zhí)行下面的查詢，當(dāng)有2M條記錄時(shí)，要耗費(fèi)2sec左右。這種方式比較簡單，創(chuàng)建一個(gè)用來存儲所有Id的臨時(shí)表即可(這也是最耗費(fèi)性能的地方)。

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ù)量大時(shí)掃描過多的記錄。

mysql 優(yōu)化包括哪些內(nèi)容？

mysql的優(yōu)化大的有兩方面：

1、配置優(yōu)化

配置的優(yōu)化其實(shí)包含兩個(gè)方面的：操作系統(tǒng)內(nèi)核的優(yōu)化和mysql配置文件的優(yōu)化

1)系統(tǒng)內(nèi)核的優(yōu)化對專用的mysql服務(wù)器來說，無非是內(nèi)存實(shí)用、連接數(shù)、超時(shí)處理、TCP處理等方面的優(yōu)化，根據(jù)自己的硬件配置來進(jìn)行優(yōu)化，這里不多講；

2)mysql配置的優(yōu)化，一般來說包含：IO處理的常用參數(shù)、最大連接數(shù)設(shè)置、緩存使用參數(shù)的設(shè)置、慢日志的參數(shù)的設(shè)置、innodb相關(guān)參數(shù)的設(shè)置等，如果有主從關(guān)系在設(shè)置主從同步的相關(guān)參數(shù)即可，網(wǎng)上的相關(guān)配置文件很多，大同小異，常用的設(shè)置大多修改這些差不多就夠用了。

2、sql語句的優(yōu)化

1） ?盡量稍作計(jì)算

Mysql的作用是用來存取數(shù)據(jù)的，不是做計(jì)算的，做計(jì)算的話可以用其他方法去實(shí)現(xiàn)，mysql做計(jì)算是很耗資源的。

2）盡量少 join

MySQL 的優(yōu)勢在于簡單，但這在某些方面其實(shí)也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩

3）盡量少排序

排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL的響應(yīng)時(shí)間。

對于MySQL來說，減少排序有多種辦法，比如：

通過利用索引來排序的方式進(jìn)行優(yōu)化

減少參與排序的記錄條數(shù)

非必要不對數(shù)據(jù)進(jìn)行排序

4）盡量避免 select *

在數(shù)據(jù)量少并且訪問量不大的情況下，select * 沒有什么影響，但是量級達(dá)到一定級別的時(shí)候，在執(zhí)行效率和IO資源的使用上，還是有很大關(guān)系的，用什么字段取什么字段，減少不必要的資源浪費(fèi)。

5）盡量用 join 代替子查詢

雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計(jì)劃一直存在較大的問題，雖然這個(gè)問題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認(rèn)這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個(gè)版本較好的解決了這一問題。

一條sql執(zhí)行過長的時(shí)間，你如何優(yōu)化，從哪些方面？

1、查看sql是否涉及多表的聯(lián)表或者子查詢，如果有，看是否能進(jìn)行業(yè)務(wù)拆分，相關(guān)字段冗余或者合并成臨時(shí)表（業(yè)務(wù)和算法的優(yōu)化）

2、涉及鏈表的查詢，是否能進(jìn)行分表查詢，單表查詢之后的結(jié)果進(jìn)行字段整合

3、如果以上兩種都不能操作，非要鏈表查詢，那么考慮對相對應(yīng)的查詢條件做索引。加快查詢速度

4、針對數(shù)量大的表進(jìn)行歷史表分離（如交易流水表）

5、數(shù)據(jù)庫主從分離，讀寫分離，降低讀寫針對同一表同時(shí)的壓力，至于主從同步，mysql有自帶的binlog實(shí)現(xiàn) 主從同步

6、explain分析sql語句，查看執(zhí)行計(jì)劃，分析索引是否用上，分析掃描行數(shù)等等

7、查看mysql執(zhí)行日志，看看是否有其他方面的問題

個(gè)人理解：從根本上來說，查詢慢是占用mysql內(nèi)存比較多，那么可以從這方面去酌手考慮

文章名稱：mysql日期怎么優(yōu)化 MySQL日期加減
轉(zhuǎn)載源于：http://chinadenli.net/article4/hpehoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、網(wǎng)站制作、建站公司、動態(tài)網(wǎng)站、手機(jī)網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化

廣告

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