在MySQL里,like'XXX%可以用到索引,但like '%XXX%'卻不行,比如,以下這個(gè)案例:
查看測試表行數(shù):
點(diǎn)擊(此處)折疊或打開
我們提供的服務(wù)有:網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、銅仁ssl等。為上1000+企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù),是有科學(xué)管理、有技術(shù)的銅仁網(wǎng)站制作公司
-
mysql> select count(*) from test03;
-
+----------+
-
| count(*) |
-
+----------+
-
| 117584 |
-
+----------+
兩次like匹配對比:
-
mysql> explain select count(*) from test03 where username like '1%';
-
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
-
| 1 | SIMPLE | test03 | range | idx_test03_name| idx_test03_name | 302 | NULL | 58250 | Using where; Using index |
-
+----+-------------+--------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
-
1 row in set (0.03 sec)
-
-
mysql> explain select count(*) from test03 where username like '%1%';
-
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
-
| 1 | SIMPLE | test03| index | NULL | idx_test03_name | 302 | NULL | 116500 | Using where; Using index |
-
+----+-------------+--------+-------+---------------+-----------------+---------+------+--------+--------------------------+
-
1 row in set (0.00 sec)
優(yōu)化思路:
這個(gè)測試表中,id是主鍵,葉子節(jié)點(diǎn)上保存了數(shù)據(jù),從索引中就可以去到select的的id的列,不必讀取數(shù)據(jù)行(只有select字段正好就是索引,那么就用到了覆蓋索引),通過覆蓋索引,減少I/O,提高性能。
優(yōu)化之前的執(zhí)行計(jì)劃:
-
mysql> explain select count(*) from test03 where username like '%1%';
-
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
-
| 1 | SIMPLE | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
-
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
優(yōu)化之后的執(zhí)行計(jì)劃:
-
mysql> explain select count(*) from test03 a join (select id from test03 where username like '%1%') b on a.id=b.id;
-
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
-
| 1 | PRIMAR | <derived2> | ALL | NULL | NULL | NULL | NULL | 7164 | NULL |
-
| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | Using index |
-
| 2 | DERIVED | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
-
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+
網(wǎng)站欄目:模式匹配like'%XXX%'優(yōu)化
鏈接分享:http://www.muchs.cn/article26/ghjjcg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站制作、搜索引擎優(yōu)化、靜態(tài)網(wǎng)站、品牌網(wǎng)站制作、移動網(wǎng)站建設(shè)、網(wǎng)站維護(hù)
廣告
聲明:本網(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)