mysql模糊查詢最佳化,mysql模糊查詢

來源:互聯網
上載者:User

mysql模糊查詢最佳化,mysql模糊查詢

首先,對資料庫要搜尋的欄位建立索引(mysql索引不熟悉,可參考這裡)。

其次,使用下面的函數來進行模糊查詢,如果出現的位置>0,表示包含該字串。

查詢效率比like要高。
如: 

table.field like  ‘%AAA%’ 可以改為 locate (‘AAA’ , table.field) > 0

註:locate(substr,str)


用explain查看結果,rows越少越好!

1、用like查詢

   

2、用locate查詢

   

    可以明顯的看到,在此處雖然兩者的rows一樣,但是filtered的值,用了locate後的值是用like的值的10倍。

    註:The filtered column is described in the MySQL manual:

     

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED. (New in MySQL 5.1.12)

    也就是說,filtered的越高越好

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.