MySQL查詢優(yōu)化

通過(guò)慢查詢?nèi)罩菊页鲂什畹腟QL, 再進(jìn)一步判斷索引是否高效;

創(chuàng)新互聯(lián)主要從事網(wǎng)站制作、成都網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)順義,10多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):13518219792

analyze table score;

explain select * from score where falseG;

盡量使用數(shù)值操作;

“小”類型夠用，就不要選用“大”類型;

MyISAM中，盡量使用固定長(zhǎng)度數(shù)據(jù)列，減少存儲(chǔ)碎片的發(fā)生；

對(duì)于Memory，InnoDB引擎，固定長(zhǎng)度還是可變長(zhǎng)度，不影響性能；

如果字符串?dāng)?shù)據(jù)列的不同取值的個(gè)數(shù)是有限的，就應(yīng)該把它轉(zhuǎn)換為ENUM數(shù)據(jù)列;

select * from score procedure analyse();

1) MyISAM表消除碎片: optimize table president;

2) 導(dǎo)出導(dǎo)入, 適用所有存儲(chǔ)引擎

mysqldump sampdb president dump.sql;

mysql sampdb dump.sql

批量加載數(shù)據(jù)比單行數(shù)據(jù)加載效率高;

無(wú)索引表比有索引表, 更快;

SQL語(yǔ)句短比SQL語(yǔ)句長(zhǎng), 更快;

加載效率比較: load data load data local insert into …

如果只能使用insert, 則推薦使用 單語(yǔ)句多行插入 ;

MySQL 查詢優(yōu)化（六）： MySQL 的查詢優(yōu)化排序優(yōu)化機(jī)制

對(duì)結(jié)果進(jìn)行排序操作的代價(jià)可能很高，因此可以通過(guò)避免排序或讓參與排序的數(shù)據(jù)行更少來(lái)優(yōu)化查詢性能。

當(dāng) MySQL 不能使用索引產(chǎn)生有序結(jié)果時(shí)，它必須對(duì)數(shù)據(jù)行進(jìn)行排序。這有可能是在內(nèi)存中進(jìn)行也可能是在磁盤進(jìn)行，但 MySQL 始終將這個(gè)過(guò)程稱之為 filesort，即便實(shí)際上并沒(méi)有使用一個(gè)文件。

如果用于排序的值可以一次性放入排序緩存中，MySQL 可以在內(nèi)存中使用快排算法進(jìn)行排序。如果 MySQL 不能在內(nèi)存中進(jìn)行排序，則會(huì)在磁盤中按塊逐塊排序。它對(duì)每個(gè)塊使用快排算法，然后在將這些排序好的塊合并到結(jié)果中。

有兩個(gè)文件排序（filesort）算法：

很難說(shuō)哪種算法更有效，對(duì)每個(gè)算法來(lái)說(shuō)都會(huì)有最優(yōu)和最壞案例。MySQL 在數(shù)據(jù)表全部列加上用于排序的列的大小不超過(guò) max_length_for_sort_data 時(shí)會(huì)使用單次遍歷算法?？梢酝ㄟ^(guò)修改這個(gè)參數(shù)影響排序算法的選擇。

需要注意的是，MySQL 的 filesort使用的臨時(shí)存儲(chǔ)空間可能會(huì)超出你的預(yù)期，這是因?yàn)樗鼘?duì)每個(gè)排序元素都分配了固定大小的存儲(chǔ)空間。這些存儲(chǔ)空間要足夠大以便容下存儲(chǔ)最大的元素，而且 VARCHAR這類字段使用的是對(duì)應(yīng)的最大長(zhǎng)度。而且，如果使用的是 UTF-8字符集，MuSQL 會(huì)對(duì)每個(gè)字符分配3個(gè)字節(jié)。結(jié)果是，我們會(huì)發(fā)現(xiàn)那些沒(méi)怎么優(yōu)化的查詢會(huì)導(dǎo)致磁盤上的臨時(shí)存儲(chǔ)空間是數(shù)據(jù)表自身存儲(chǔ)空間的好幾倍。

而在對(duì)聯(lián)合查詢進(jìn)行排序時(shí)，MySQL 可能會(huì)在查詢執(zhí)行過(guò)程中執(zhí)行兩次文件排序。如果 ORDER BY 子句只是引用聯(lián)合查詢的第一張表，MySQL 可以先對(duì)這個(gè)表進(jìn)行文件排序，然后再處理聯(lián)合查詢。如果是這種情況，在 EXPLAIN 時(shí)會(huì)在 Extra 字段顯示“Using filesort”。而對(duì)于其他的排序情況——例如排序不是針對(duì)第一張表，或者是 ORDER BY 使用的列對(duì)應(yīng)了不止一個(gè)數(shù)據(jù)表，MySQL 必須使用臨時(shí)表緩存查詢結(jié)果，然而在聯(lián)合查詢完成后，再對(duì)臨時(shí)表進(jìn)行文件排序。在這種情況下，EXPLAIN 會(huì)在 Extra 字段顯示“Using temorary; Using filesort”。如果包含 LIMIT 約束的話，會(huì)發(fā)生在文件排序后，因此臨時(shí)表和文件排序的存儲(chǔ)空間可能非常大。

MySQL 5.6在只需要對(duì)數(shù)據(jù)行的子集（例如 LIMIT）進(jìn)行排序時(shí)，引入了一個(gè)重大改進(jìn)。相對(duì)于對(duì)整個(gè)結(jié)果集進(jìn)行排序再返回部分?jǐn)?shù)據(jù)，MySQL 有時(shí)候會(huì)在排序的時(shí)候直接丟棄掉不需要的數(shù)據(jù)行來(lái)提高效率。不管怎么樣，排序也需要小心使用，很可能會(huì)導(dǎo)致存儲(chǔ)占用的飆升最終導(dǎo)致系統(tǒng)負(fù)荷過(guò)大。

【Mysql】查詢優(yōu)化——減少回表操作

??聚集索引：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。 葉子結(jié)點(diǎn)存儲(chǔ)索引和行記錄，聚簇索引查詢會(huì)很快，因?yàn)榭梢灾苯佣ㄎ坏叫杏涗洝?/p>

??非聚集索引：該索引中索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。 葉子節(jié)點(diǎn)存儲(chǔ)聚簇索引值（主鍵id），需要掃碼兩遍索引樹(shù)，先通過(guò)普通索引定位到主鍵值id，再通過(guò)聚集索引定位到行記錄。

??回表查詢可以理解為普通索引的查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹(shù)更低。

??索引覆蓋，即將查詢sql中的字段添加到聯(lián)合索引里面，只要保證查詢語(yǔ)句里面的字段都在索引文件中，就無(wú)需進(jìn)行回表查詢；

??實(shí)際開(kāi)發(fā)中，不可能把所有字段建立到聯(lián)合索引，可根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景，把經(jīng)常需要查詢的字段建立到聯(lián)合索引中。

?? 在Mysql5.6的版本上推出，用于優(yōu)化查詢。 在索引遍歷過(guò)程中，對(duì)索引中包含的字段先做判斷，直接過(guò)濾掉不滿足條件的記錄，減少回表次數(shù)。

?? 優(yōu)化超多分頁(yè)場(chǎng)景。 查詢條件放到子查詢中，子查詢只查主鍵id，然后使用子查詢中確定的主鍵關(guān)聯(lián)查詢其他的屬性字段。

MySQL大數(shù)據(jù)量分頁(yè)查詢方法及其優(yōu)化

使用子查詢優(yōu)化大數(shù)據(jù)量分頁(yè)查詢

這種方式的做法是先定位偏移位置的id，然后再往后查詢，適用于id遞增的情況。

使用id限定優(yōu)化大數(shù)據(jù)量分頁(yè)查詢

使用這種方式需要先假設(shè)數(shù)據(jù)表的id是連續(xù)遞增的，我們根據(jù)查詢的頁(yè)數(shù)和查詢的記錄數(shù)可以算出查詢的id的范圍，可以使用 id between and 來(lái)查詢：

當(dāng)然了，也可以使用in的方式來(lái)進(jìn)行查詢，這種方式經(jīng)常用在多表關(guān)聯(lián)的情況下，使用其他表查詢的id集合來(lái)進(jìn)行查詢：

但是使用這種in查詢方式的時(shí)候要注意的是，某些MySQL版本并不支持在in子句中使用limit子句。

參考 sql優(yōu)化之大數(shù)據(jù)量分頁(yè)查詢（mysql） - yanggb - 博客園 (cnblogs點(diǎn)抗 )