mysql索引不生效 - tw511教學網

並不是索引越多越好，索引是一種以空間換取時間的方式，所以建立索引是要消耗一定的空間，況且在索引的維護上也會消耗資源。本文首發我的個人部落格mysql索引不生效

這裡有張使用者瀏覽商品表，建表語句：

CREATE TABLE `product_view` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `duration` int(11) NOT NULL,
  `times` varchar(11) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `user_product` (`user_id`,`product_id`) USING BTREE,
  KEY `times` (`times`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可以看出目前這張表是有3個索引的：

我往這張表裡面匯入了10萬多條記錄。

mysql不走索引的情況

1、like查詢以「%」開頭(如果開頭、結果都有「%」，也不會使用索引，走的是全表掃描)；

我這裡用了列出了4種情況，發現都是全表掃描，不走索引的。可能因為times取值不夠離散，索引沒有走索引。

2、or語句前後沒有同時使用索引;

首先看看or語句前後同時使用索引：

查詢走索引了。

再看看or語句前後沒有同時使用索引，product_id是索引欄位，server_id不是索引欄位：

是沒有走索引的。

3、組合索引中不是使用第一列索引；（不符合最左匹配原則）

這張表索引為時間time和一個組合索引：

這裡組合索引user_product，我們先使用user_id這個第一列索引，作為查詢條件，檢視執行計劃：

使用了索引。

接下來使用組合索引user_product的非第一列索引product_id，再看看執行計劃：

沒有使用索引。

4、where條件中型別為字串的欄位沒有使用引號引起來；【查詢where條件資料型別不匹配也無法使用索引，字串與數位比較不使用索引，因為正規表示式不使用索引，如varchar不加單引號的話可能會自動轉換為int型，使索引無效，產生全表掃描】

首先看看where後條件字串正常使用引號：

使用了索引。

where後條件字串不使用引號，而使用數位：

可以看到，查詢沒有走索引。

5、當全表掃描速度比索引速度快時，mysql會使用全表掃描，此時索引失效；(*資料量少*)

我把表資料刪了，裡面留了7條資料：

條件查詢索引欄位列times:

顯然是沒有走索引的。

6、在索引欄位上使用「not」，「<>」，「!=」等等；

經過驗證發現，使用這些符號後，依然會走索引。

7、對索引欄位進行計算操作、使用函數;

MySql 如果表中某個時間欄位（datetime/…）設定了索引，以函數 DATE_FORMAT() 為查詢條件時，為datetime設定的索引不生效，會引起全表掃描導致查詢很慢。

沒有用函數時候是走了索引的，查出具體到時分秒的資料：

使用函數data_format函數，查出2020-08-14這一天的所有資料：

沒有走索引，那麼怎麼解決呢？

如果一定要用函數，比如date_format，可以通過這種方式，就會走索引。

8、索引雜湊值（重複多）不適合建索引，例：性別、狀態等欄位不適合。

不應該建立索引的欄位規則

不應該在欄位比較長的欄位上建立索引，因為會消耗大量的空間
對於頻繁更新、插入的欄位應該少建立索引，因為在修改和插入之後，資料庫會去維護索引，會消耗資源
儘量少在無用欄位上建立索引【where條件中用不到的欄位】
表記錄太少不應該建立索引
資料重複且分佈平均的表欄位不應該建立索引【選擇性太低，例如性別、狀態、真假值等欄位】
參與列計算的列不適合建索引【保持列"乾淨"，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因是b+樹中存的都是資料表中的欄位值，但進行檢索時需要把所有元素都應用函數才能比較，顯然成本太大，所以語句應該寫成create_time = unix_timestamp('2014-05-29')】