MySQL中怎么優(yōu)化查詢性能，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡(jiǎn)單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：主機(jī)域名、虛擬主機(jī)、營(yíng)銷軟件、網(wǎng)站建設(shè)、淮陽網(wǎng)站維護(hù)、網(wǎng)站推廣。

WHERE條件字段使用函數(shù)

假設(shè)我們有如下創(chuàng)建表的語句

mysql> CREATE TABLE `tradelog` (  `id` int(11) NOT NULL,  `tradeid` varchar(32) DEFAULT NULL,  `operator` int(11) DEFAULT NULL,  `t_modified` datetime DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `tradeid` (`tradeid`),  KEY `t_modified` (`t_modified`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上面是一種時(shí)間維度的業(yè)務(wù)表，此時(shí)如果我們要僅僅查詢所有數(shù)據(jù)中 7月份的交易筆數(shù)。此時(shí)我們可能會(huì)想到如下SQL

mysql> select count(*) from tradelog where month(t_modified)=7;

從上面的建表語句我們可以看出，索引是建在 t_modified 上面的。此時(shí)如果我們要查詢上面的SQL 查詢，執(zhí)行過程將會(huì)是如下：

從上圖可以看出，當(dāng)對(duì)索引字段做函數(shù)操作后，可能會(huì)造成索引結(jié)構(gòu)順序的錯(cuò)亂。因此，MySQL  會(huì)放棄走搜索樹的查詢結(jié)構(gòu)，取而代之的是全索引掃描。（優(yōu)化器選擇走 t_modified 索引全表遍歷，而不選擇 主鍵索引的原因是  t_modified 索引相對(duì)小一點(diǎn)）

通常情況下，我們需要人工的去優(yōu)化SQL 。當(dāng)然這往往需要結(jié)合具體的業(yè)務(wù)數(shù)據(jù)去處理了，如上面的查詢可能會(huì)優(yōu)化為如下的情況：

select count(*) from tradelog where (t_modified >= '2016-7-1' and t_modified < '2016-8-1') or (t_modified >= '2017-7-1' and t_modified < '2017-8-1') or  (t_modified >= '2018-7-1' and t_modified < '2018-8-1');

對(duì)于MySQL 的簡(jiǎn)單查詢來說，還有一個(gè)坑就是：

SELECT * FROM tradelog WHERE id + 1 = 999;

這個(gè)時(shí)候，MySQL 也不會(huì)主動(dòng)的去做 “移項(xiàng)”的優(yōu)化，此時(shí)也會(huì)造成全表掃描。

字段隱式轉(zhuǎn)換

MySQL 中的字段隱式轉(zhuǎn)換可能會(huì)引起索引不可用，下面我們先看一個(gè)字符與數(shù)字比較的例子。如下所示：

mysql> select '10' > 9;

當(dāng)我們執(zhí)行上面的SQL 時(shí)，會(huì)得到如下結(jié)果

從執(zhí)行結(jié)果可以看出，字符類型默認(rèn)會(huì)轉(zhuǎn)換為數(shù)字類型。需要注意的點(diǎn)是：'10' ->10、'10A' -> 10、但是 'A10' -> 0 ，轉(zhuǎn)換會(huì)過濾掉無效字符，但是需要數(shù)字開頭，否則就轉(zhuǎn)化為 0 。

現(xiàn)在我們看一下如下語句：

mysql> explain select * from tradelog where tradeid = 222;

因?yàn)? tradeid 是 VARCHAR 類型，MySQL 會(huì)將其轉(zhuǎn)化為 數(shù)字然后比較，最終導(dǎo)致索引不可用，全表掃描。當(dāng)我們對(duì) int  類型字段查詢時(shí)，對(duì)應(yīng)的value 值可以隨意使用 10 或者 '10' ，此時(shí)都會(huì)轉(zhuǎn)化為 數(shù)字 10 ，使用索引。上面的語句執(zhí)行就相當(dāng)于如下：

mysql> explain select * from tradelog where CAST(tradeid AS signed int) = 222;

也就是隱藏的在查詢字段上面使用了函數(shù)操作，從而導(dǎo)致了全表掃描。

隱式字符編碼轉(zhuǎn)換

上面的案例介紹了，不同類型字段之間的類型轉(zhuǎn)換。對(duì)于相同類型(VARCHAR) 的不同字符集編碼也可能會(huì)出現(xiàn)隱式轉(zhuǎn)換。下面再創(chuàng)建一張日志詳情表(trade_detail)，然后在寫入一些數(shù)據(jù)，如下所示：

mysql> CREATE TABLE `trade_detail` (  `id` int(11) NOT NULL,  `tradeid` varchar(32) DEFAULT NULL,  `trade_step` int(11) DEFAULT NULL, /* 操作步驟 */  `step_info` varchar(32) DEFAULT NULL, /* 步驟信息 */  PRIMARY KEY (`id`),  KEY `tradeid` (`tradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into tradelog values(1, 'aaaaaaaa', 1000, now()); insert into tradelog values(2, 'aaaaaaab', 1000, now()); insert into tradelog values(3, 'aaaaaaac', 1000, now()); insert into trade_detail values(1, 'aaaaaaaa', 1, 'add'); insert into trade_detail values(2, 'aaaaaaaa', 2, 'update'); insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit'); insert into trade_detail values(4, 'aaaaaaab', 1, 'add'); insert into trade_detail values(5, 'aaaaaaab', 2, 'update'); insert into trade_detail values(6, 'aaaaaaab', 3, 'update again'); insert into trade_detail values(7, 'aaaaaaab', 4, 'commit'); insert into trade_detail values(8, 'aaaaaaac', 1, 'add'); insert into trade_detail values(9, 'aaaaaaac', 2, 'update'); insert into trade_detail values(10, 'aaaaaaac', 3, 'update again'); insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

當(dāng)我們需要查詢一條交易記錄(trade_log) 中的全部交易詳情(trade_detail) 時(shí)，可能會(huì)使用如下SQL

mysql> explain select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

上面是對(duì) trade_log 的 id = 2 的這一條記錄執(zhí)行的查詢，使用了主鍵索引，掃描行數(shù) 1 ；但是第二條沒有使用 trade_detail 上的 tradeid索引，是不是感到有些奇怪。

在上面的執(zhí)行計(jì)劃里面，先是從 trade_log 里面去查詢 id=2 的記錄，然后再去匹配 trade_detail 。這里面 trade_log 稱為 驅(qū)動(dòng)表，trade_detail 稱為 被驅(qū)動(dòng)表，其執(zhí)行流程如下所示：

那么上面第二條執(zhí)行計(jì)劃為什么沒有走索引呢，仔細(xì)看你會(huì)發(fā)現(xiàn)上面  2 張表創(chuàng)建時(shí)所使用的字符集編碼不同，一個(gè)是 utf8 一個(gè)是 utf8mb4 。utfutf8mb4 是 utf8 字符集的超集，當(dāng)我們將  兩張表的字段進(jìn)行比較時(shí)，utf8 會(huì)轉(zhuǎn)換為utf8mb4 （避免精度丟失）。

上圖中的第 3步可以認(rèn)為是執(zhí)行如下操作（$L2.tradeid.value 是 utf8mb4 的字符值）：

mysql> select * from trade_detail where tradeid = $L2.tradeid.value;

隱式轉(zhuǎn)換后的執(zhí)行SQL 如下：

mysql> select * from trade_detail where CONVERT(tradeid USING utf8mb4)=$L2.tradeid.value;

由此看來，執(zhí)行的過程中對(duì) trade_detail 的查詢字段 tradeid 使用了函數(shù)，因此不走索引。但是當(dāng)我們反過來查詢時(shí)，也就是從一條 trade_detail 去關(guān)聯(lián)對(duì)應(yīng)的 trade_log 時(shí)，會(huì)是什么情況呢？

mysql> explain select l.operator from tradelog l, trade_detail d where d.tradeid=l.tradeid and d.id=4;

由上圖可以看出，第二次查詢使用到了 tradelog的 tradeid 索引了。當(dāng)執(zhí)行計(jì)劃找到 trade_detail 中 id=4 的記錄后（R4），再去tradelog 中關(guān)聯(lián)對(duì)應(yīng)的記錄時(shí)，執(zhí)行的SQL 如下：

mysql> select operator from tradelog where traideid =$R4.tradeid.value;

此時(shí) 等號(hào)右邊的 value 值需要做隱式轉(zhuǎn)換，并沒有在索引字段上做函數(shù)操作，如下所示：

mysql> select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

解決方案

對(duì)于字符集不同造成的索引不可用，可以使用如下 2 中方式去解決。

• 修改表的字符集編碼。

mysql> alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

• 手工字符編碼轉(zhuǎn)換。

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

看完上述內(nèi)容，你們掌握MySQL中怎么優(yōu)化查詢性能的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

網(wǎng)頁標(biāo)題：MySQL中怎么優(yōu)化查詢性能
文章轉(zhuǎn)載：http://jinyejixie.com/article12/pgesdc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、網(wǎng)站維護(hù)、網(wǎng)站設(shè)計(jì)、虛擬主機(jī)、云服務(wù)器、網(wǎng)站設(shè)計(jì)公司

廣告

聲明：本網(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)