MYSQL 索引類型、什麼情況下用不上索引、什麼情況下不推薦使用索引

標籤：char   str   不同的   欄位   sel   開頭   href   使用   資料庫表   

 

mysql explain的使用：

http://blog.csdn.net/kaka1121/article/details/53394426

 

索引類型

在資料庫表中，對欄位建立索引可以大大提高查詢速度。假如我們建立了一個 mytable表

代碼如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );

我們隨機向裡面插入了10000條記錄，其中有一條：5555, admin。

在尋找username="admin"的記錄 SELECT * FROM mytable WHERE username=‘admin‘;時，如果在username上已經建立了索引，MySQL無須任何掃描，即準確可找到該記錄。相反，MySQL會掃描所有記錄，即要查詢10000條記錄。

索引分單列索引和複合式索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是複合式索引。複合式索引，即一個索包含多個列。

 

MySQL索引類型包括：

一、普通索引

這是最基本的索引，它沒有任何限制。它有以下幾種建立方式：

1.建立索引

代碼如下:

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

2.修改表結構

代碼如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 建立表的時候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

-- 刪除索引的文法：

DROP INDEX [indexName] ON mytable;

二、唯一索引

它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一。它有以下幾種建立方式：

代碼如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表結構
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 建立表的時候直接指定
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   );

三、主鍵索引

它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時建立主鍵索引：

代碼如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );

當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。

四、複合式索引

為了形象地對比單列索引和複合式索引，為表添加多個欄位：

代碼如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );

為了進一步榨取MySQL的效率，就要考慮建立複合式索引。就是將 name, city, age建到一個索引裡：

代碼如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

 

什麼情況下有索引，但用不上？

索引並不是時時都會生效的，比如以下幾種情況，將導致索引失效：

1. 如果條件中有or，即使其中有部分條件帶索引也不會使用(這也是為什麼盡量少用or的原因)，例子中user_id無索引

注意：要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引

2.對於多列索引，不是使用的第一部分，則不會使用索引

3.like查詢是以%開頭

4.存在索引列的資料類型隱形轉換，則用不上索引，比如列類型是字串，那一定要在條件中將資料使用引號引用起來,否則不使用索引

5. where 子句裡對索引列上有數學運算，用不上索引

6. where 子句裡對有索引列使用函數，用不上索引

 

7.如果mysql估計使用全表掃描要比使用索引快,則不使用索引

比如資料量極少的表

什麼情況下不推薦使用索引？

1) 資料唯一性差（一個欄位的取值只有幾種時）的欄位不要使用索引

比如性別，只有兩種可能資料。意味著索引的二叉樹層級少，多是平級。這樣的二叉樹尋找無異於全表掃描。

2) 頻繁更新的欄位不要使用索引

比如logincount登入次數，頻繁變化導致索引也頻繁變化，增大資料庫工作量，降低效率。

3) 欄位不在where語句出現時不要添加索引,如果where後含IS NULL /IS NOT NULL/ like ‘%輸入符%’等條件，不建議使用索引

只有在where語句出現，mysql才會去使用索引

4） where 子句裡對索引列使用不等於（<>），使用索引效果一般

 

 

使用覆蓋索引，提高查詢效率

覆蓋索引（covering index）：MySQL只需要通過索引就可以返回查詢所需要的資料，而不必在查到索引之後再去查詢資料

具體請參考：

https://xiaobin.net/201109/strange-sql-performance-problem/

 

MySQL效能最佳化的最佳21條經驗

請直接參考：

http://blog.csdn.net/kaka1121/article/details/53395587

MySql的最佳化步驟

請直接參考：

http://www.cnblogs.com/hongfei/archive/2012/10/19/2731342.html

 

其他一些關於mysql的博文

http://www.cnblogs.com/hongfei/category/349096.html

 

參考文章：

http://www.jb51.net/article/49346.htm

http://www.cnblogs.com/hongfei/archive/2012/10/20/2732589.html

https://xiaobin.net/201109/strange-sql-performance-problem/

http://www.cnblogs.com/hongfei/archive/2012/10/19/2731342.html

MYSQL 索引類型、什麼情況下用不上索引、什麼情況下不推薦使用索引