最近一直在優化一個查詢資料的接口,怎麼調整查詢都很緩慢,查詢條件字段都有設定索引,一直好奇是哪裡出了問題;突然發現一張表的索引有點差別,兩個字段對應一個索引名。然後随口問了問同僚,發現是組合索引(遵循”最左字首“原則)。額,我不懂,尴尬了。重新對查詢字段單獨設定一個普通索引,速度立馬加快了。
一. 索引的類型與差別:
Mysql常見索引有:主鍵索引、唯一索引、普通索引、全文索引、組合索引
主鍵索引(PRIMARY): 它 是一種特殊的唯一索引,不允許有空值。
唯一索引(UNIQUE):與"普通索引"類似,不同的就是:索引列的值必須唯一,但允許有空值。
全文索引(FULLTEXT ): 僅可用于 MyISAM 表, 用于在一篇文章中,檢索文本資訊的, 針對較大的資料,生成全文索引很耗時好空間;用在比較短的文本,如果就一兩行字的,普通的 INDEX 就可以了。
普通索引(INDEX): 最基本的索引,沒有任何限制。
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
組合索引:為了更多的提高mysql效率可建立組合索引,遵循”最左字首“原則。
二. 索引的使用:(普通索引,組合索引)
1. 如果針對單個字段進行查詢,建立普通索引就滿足了;
檢視索引: show index from 表名;
指令顯示的結果列的含義
建立索引:create index 索引名稱 on 表名(字段名(字段長度))
create index title_index on test_index(title(10))
增加索引: alter table '表名' add index index_name on 字段名(字段長度)
删除索引: 可以使用ALTER TABLE或DROP INDEX語句來實作
drop index 索引名稱 on 表名;
drop index titile_index on test_index;
或: alter table 表名 drop index 索引名 ;
alter table table_name drop index index_name ;
2. 如果是針對兩個或兩個以上條件的查詢,就需要使用組合索引了;
如:将三個條件建立到一個索引裡: ALTER TABLE 表名 ADD INDEX name_city_age(索引名) (Name(10),City,Age);
# 如果分别在Name,City,Age上建立單列索引,讓該表有3個單列索引,查詢時比上述的組合索引效率一樣吧?嘿嘿,大不一樣,遠遠低于我們的組合索引!
# 組合索引遵循”最左字首“原則
ALTER TABLE 表名 ADD INDEX name_city_age(索引名) (Name(10),City,Age);
建立這樣的組合索引,其實是相當于分别建立了三個組合索引:
1)Name, City, Age
2) Name, City
3) Name
簡單的了解就是隻從最左面的開始組合。并不是隻要包含這三列的查詢都會用到該組合索引,下面的幾個T-SQL會用到:
SELECT
*
FROM
myIndex WHREE Name=
"erquan"
AND
City=
"鄭州"
SELECT
*
FROM
myIndex WHREE Name=
而下面幾個則不會用到:
"erquan"
SELECT
*
FROM
myIndex WHREE Age=20
AND
City=
"鄭州"
SELECT
*
FROM
myIndex WHREE City=
"鄭州"
三,什麼條件需使用索引
一般來說,在 where 和 join 中出現的列需要建立索引,但也不完全如此,因為MySQL隻對 <,<=,=,>,>=,BETWEEN,IN,以及某些時候的LIKE(後面有說明)才會使用索引。
SELECT t.Name FROM testIndex t LEFT JOIN myIndex m ON t.Name=m.Name WHERE m.Age=20 AND m.City='鄭州' 時,有對myIndex表的 City 和 Age 建立索引的需要,由于testIndex表的Name開出現在了JOIN子句中,也有對Name建立索引的必要(兩個表的Name 都需要建索引)。
剛才提到了,隻有某些時候的LIKE才需建立索引?是的。因為在以通配符 % 和 _ 開頭作查詢時,MySQL不會使用索引,如
SELECT
*
FROM
myIndex
WHERE
vc_Name
會使用索引;
like'erquan%'
SELECT
*
FROM
myIndex WHEREt vc_Name
like
'%erquan' 不會使用索引;
四. 索引不足之處:
1. 雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對表進行INSERT、UPDATE和DELETE。因為更新表時,MySQL不僅要儲存資料,還要儲存一下索引檔案。
2. 建立索引會占用磁盤空間的索引檔案。一般情況這個問題不太嚴重,但如果你在一個大表上建立了多種組合索引,索引檔案的會膨脹很快。
源位址:https://www.jb51.net/article/69676.htm
五. SQL中explaind的使用介紹(檢視索引情況):
位址:https://blog.csdn.net/weixin_39680135/article/details/81084324
-------------------------------------------------------------------------------------------------------------------------
navicat 手動添加索引
# 設計表,打開界面 --> 索引 --> 添加索引--> 儲存
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiAzNfRHLGZkRGZkRfJ3bs92YsYTMfVmepNHL1Z0Ra5WNXl1ck1mYox2MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnL0QjN3AzM1kTM1ITMxAjMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
navicat 對應的參數:
1)索引類型:
normal:最基本的索引狀态,所建立的索引項可以重複。
unique:與normal唯一不同的是所建立的索引項不能有重複
full text:全文搜尋
2) 索引方式:
hash與btree
- hash類型的索引:查詢單條快,範圍查詢慢
- btree類型的索引:b+樹,層數越多,資料量指數級增長(我們就用它,因為innodb預設支援它)(一般是使用這個)
1)索引方法 btree 可以用于“ >、 < 、=”查詢 ,如果查id=1000的資料 建立索引後 二分查找最多13次就可以查出相應的資料;
2)hash 不能做order by排序 不能做 用like模糊查詢。
介紹位址:https://blog.csdn.net/weixin_41827162/article/details/87891221