用mysql查詢某字段是否有索引怎么做？

顯示一個(gè)表所有索引的SQL語(yǔ)句是：

成都服務(wù)器托管，創(chuàng)新互聯(lián)公司提供包括服務(wù)器租用、成都移動(dòng)機(jī)房、帶寬租用、云主機(jī)、機(jī)柜租用、主機(jī)租用托管、CDN網(wǎng)站加速、申請(qǐng)域名等業(yè)務(wù)的一體化完整服務(wù)。電話咨詢：18982081108

show index from 數(shù)據(jù)庫(kù)名.表名

查看某表某一列上的索引使用下面的SQL語(yǔ)句：

show index from 數(shù)據(jù)庫(kù)名.表名 where column_name like '列名'

下面的SQL語(yǔ)句在我的數(shù)據(jù)庫(kù)上執(zhí)行成功：

show index from web.clubuser where column_name like 'user'。

MySQL前綴索引

前綴索引顧名思義，定義字符串的一部分當(dāng)做索引，而不是把整個(gè)字符串當(dāng)做索引。默認(rèn)地，如果你創(chuàng)建索引的語(yǔ)句不指定前綴長(zhǎng)度，那么索引就會(huì)包含整個(gè)字符串。

假設(shè)一張表有 id,name,email 2個(gè)字段

1.創(chuàng)建email列的普通索引應(yīng)該是： alter table T add index idx_email1( email )

2.前綴索引的創(chuàng)建規(guī)則為： alter table table T add index idx_email2( email(6) )

當(dāng)然第一索引包含是的整個(gè)字符串，第二個(gè)是該字段前6個(gè)字節(jié)（注意是字節(jié)）

對(duì)于這2中索引，B+樹(shù)怎么存儲(chǔ)呢？

INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('劍圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韓信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com');

【誰(shuí)還不是個(gè)野王啊】

普通索引存儲(chǔ)為：

是的你沒(méi)看錯(cuò)，前綴索引那顆樹(shù)上的存儲(chǔ)的是email的前6位字節(jié)，也就是你創(chuàng)建前綴索引時(shí)指定的前綴字節(jié)長(zhǎng)度。2種樹(shù)相比，前綴索引存儲(chǔ)了更少的數(shù)據(jù)，那么他所耗費(fèi)的空間也就相比較少，這正是他的一個(gè)優(yōu)點(diǎn)。同樣的也就相對(duì)的增加了掃描行數(shù)。

什么增加了掃描行數(shù)？？？？ 這是為什么呢？

那么小朋友咱們一起來(lái)看下吧。

假設(shè)SQL如此這般： select id,name,email from T where email = 'zhangsh1234@163.com'

那么這2個(gè)SQL，應(yīng)該怎么操作呢。

idx_email1:

2.到主鍵上查到主鍵為ID1的，判斷email值是否正確【為什么判斷呢，其實(shí)我理解是為了二次判斷保證數(shù)據(jù)一致性吧，比較官方的解釋尚未找到】，正確放入結(jié)果集

3.取 idx_email1 索引樹(shù)上剛剛查到的位置的下一條記錄，如此往復(fù)。

循環(huán)過(guò)程中，需要回主鍵取1次數(shù)據(jù)，所以系統(tǒng)可以認(rèn)為只掃描了一行【1次是數(shù)第一棵樹(shù)數(shù)出來(lái)的】

idx_email2:

1.從 索引數(shù)上找到滿足索引值為 'zhangs'的該記錄，取得 ID1的值

2.到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值是’ zhangsh1234@xxx.com ’，這行記錄放入結(jié)果集【不是要的值，丟棄，進(jìn)行下一步】

3.取 idx_email2 上剛剛查到的位置的下一條記錄,重復(fù)以上步驟

在這個(gè)過(guò)程中，要回主鍵索引取 3 次數(shù)據(jù)，也就是掃描了 3 行。通過(guò)這個(gè)對(duì)比，你很容易就可以發(fā)現(xiàn)，使用前綴索引后，可能會(huì)導(dǎo)致查詢語(yǔ)句讀數(shù)據(jù)的次數(shù)變多。

但是，對(duì)于這個(gè)查詢語(yǔ)句來(lái)說(shuō)，如果你定義的 idx_email2 不是 email(6) 而是 email(8），也就是說(shuō)取 email 字段的前 8 個(gè)字節(jié)來(lái)構(gòu)建索引的話，即滿足前綴’zhangsh’的記錄只有一個(gè)，也能夠直接查到 ID1，只掃描一行就結(jié)束了。也就是說(shuō)使用前綴索引，定義好長(zhǎng)度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本。

那么問(wèn)題來(lái)了，到底定義多長(zhǎng)才算是合理呢？

一般的定義原則是 count(distinct(columnName))/count(*) ,當(dāng)前綴索引【count(distinct(columnName(length)))，length是你想要?jiǎng)?chuàng)建列的前綴字節(jié)長(zhǎng)度】越接近此值越好，當(dāng)有多個(gè)前綴字節(jié)都一樣且都等于這個(gè)值時(shí)怎么選擇呢，當(dāng)然是 字節(jié)越少越好了哈，字節(jié)越少越省空間。索引選取的越長(zhǎng)，占用的磁盤(pán)空間就越大，相同的數(shù)據(jù)頁(yè)能放下的索引值就越少，搜索的效率也就會(huì)越低。

count(distinct(columnName(length))) 翻譯到SQL 為： count(dictinct(left(colunmName, length)))

前面我們說(shuō)了使用前綴索引可能會(huì)增加掃描行數(shù)，這會(huì)影響到性能。其實(shí)，前綴索引的影響不止如此，我們?cè)倏匆幌铝硗庖粋€(gè)場(chǎng)景。

來(lái)呀，上SQL： select id,email from T where email='zhangsh1234@163.com'

如果按照email全字段索引，那么此SQL 是不需要回表的【為什么不需要回表？兄嘚，這個(gè)相當(dāng)于覆蓋索引了哈】

那么如果按照前綴索引是否需要回表呢？答案是的。

因?yàn)楫?dāng)判斷前6個(gè)字節(jié)相等后，需要拿到id 回表拿到email的全部?jī)?nèi)容進(jìn)行比較，如果不相同，丟棄這行，否則加入結(jié)果集。

那么有人會(huì)問(wèn)了，我把長(zhǎng)度放大點(diǎn)，包含所有字節(jié)不就好了嗎？

那么此時(shí)會(huì)有如下問(wèn)題。

1.當(dāng)你此時(shí)的長(zhǎng)度是囊括了全字段，但是系統(tǒng)是不知道的，他還是需要回表再次判斷的，去確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?/p>

2.此時(shí)長(zhǎng)度是夠了，那么能肯定因?yàn)闃I(yè)務(wù)日后不會(huì)增加長(zhǎng)度嗎？

3.盡可能的加長(zhǎng)長(zhǎng)度，還不如直接建立全字段索引呢

綜上，使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素。

前面說(shuō)到的是，可以根據(jù)字段前面幾個(gè)字節(jié)進(jìn)行查詢的，那么對(duì)于身份證這種，一共 18 位，其中前 6 位是地址碼，所以同一個(gè)縣的人的身份證號(hào)前 6 位一般會(huì)是相同的。

或許你會(huì)說(shuō)，多弄幾個(gè)字節(jié)不就好嗎？那么請(qǐng)問(wèn)下自己為什么使用前綴索引呢，不就是為了節(jié)省空間嗎？

那么這么做合適嗎？ 不合適對(duì)嗎？ 乖~，快去反省下吧

那么采用前綴索引顯示是不行的，那么如果用前綴索引怎么辦呢，聰明的你應(yīng)該已經(jīng)猜到了，采用倒敘存儲(chǔ)，然后建立前綴索引。

放到SQL 中就應(yīng)該是這樣的： select field_list from t where id_card = reverse('id_card_string');

當(dāng)然了，這種邏輯建議放到業(yè)務(wù)邏輯中實(shí)現(xiàn)，而不是放到SQL 中。

按照上述第4節(jié)的內(nèi)容，有人或許會(huì)有另一個(gè)想法，還倒敘建立前綴索引復(fù)雜不，hash索引或者h(yuǎn)ash字段不香嗎？

有人會(huì)問(wèn)了，為什么要在創(chuàng)建一個(gè)值來(lái)存儲(chǔ)hash值呢，如果不存儲(chǔ)你知道原值是什么嗎？ 同時(shí)hash算法是有一定重復(fù)可能的（hash值碰撞）

【可以了解下partition算法哦:[ 】。如果重復(fù)了，不存儲(chǔ)原值，你是無(wú)法判斷出正確數(shù)據(jù)的。

注：【hash字段不代表hash索引，hash索引原理正在快馬加鞭】，簡(jiǎn)單說(shuō)下hash索引，hash索引不需要?jiǎng)?chuàng)建一個(gè)值來(lái)存儲(chǔ)hash值，而是有hasn表來(lái)存儲(chǔ)【hash值碰撞時(shí)，由一個(gè)鏈表來(lái)搞定了】，存儲(chǔ)的內(nèi)容為 hash值和每行的行指針 。

說(shuō)回來(lái)啊，跑題了

查詢時(shí)： select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不過(guò)有個(gè)問(wèn)題相信你也想到了，不管是hash存儲(chǔ)值還是hash索引都是不支持范圍查詢的。

來(lái)總結(jié)下這2個(gè)優(yōu)缺點(diǎn)吧

1.從占用空間來(lái)看呢，倒敘索引不需要額外開(kāi)辟存儲(chǔ)空間，而hash字段需要額外的一個(gè)字段，所以從這點(diǎn)上看倒敘索引更勝一籌，NO!并不準(zhǔn)確,如果前綴長(zhǎng)度過(guò)長(zhǎng)，那么這2個(gè)情況額外的空間也就相差無(wú)幾了

3.從查詢效率上看，使用 hash 字段方式的查詢性能相對(duì)更穩(wěn)定一些。因?yàn)?crc32 算出來(lái)的值雖然有沖突的概率，但是概率非常小，可以認(rèn)為每次查詢的平均掃描行數(shù)接近 1。而倒序存儲(chǔ)方式畢竟還是用的前綴索引的方式，也就是說(shuō)還是會(huì)增加掃描行數(shù)

1.全字段完整索引比較占空間，但是而走覆蓋索引

2.前綴索引，節(jié)省空間，但會(huì)增加掃描 次數(shù) 并且不能使用覆蓋索引【每次都需回表校驗(yàn)】

3.倒序存儲(chǔ)，再創(chuàng)建前綴索引，用于繞過(guò)字符串本身前綴的區(qū)分度不夠的問(wèn)題。【倒敘方法建立放到業(yè)務(wù)邏輯中】

4.hash字段索引，相比前綴索引性能較為穩(wěn)定，但是有額外的存儲(chǔ)空間和計(jì)算消耗，同時(shí)也 不 支持范圍查詢

mysql中如何查看和刪除唯一索引

mysql中如何查看和刪除唯一索引。

查看唯一索引:

show

index

from

mytable;//mytable

是表名

查詢結(jié)果如下：

查詢到唯一索引后，如何刪除唯一索引呢，使用如下命令：

alter

table

mytable

drop

index

mdl_tag_use_ix;//mdl_tag_use_ix是上表查出的索引名，key_name

怎么查看表的索引mysql

查看索引的語(yǔ)法格式如下：

SHOW INDEX FROM 表名 [ FROM 數(shù)據(jù)庫(kù)名]

語(yǔ)法說(shuō)明如下：

表名：指定需要查看索引的數(shù)據(jù)表名。

數(shù)據(jù)庫(kù)名：指定需要查看索引的數(shù)據(jù)表所在的數(shù)據(jù)庫(kù)，可省略。比如，SHOW INDEX FROM student FROM test; 語(yǔ)句表示查看 test 數(shù)據(jù)庫(kù)中 student 數(shù)據(jù)表的索引。

示例

使用 SHOW INDEX 語(yǔ)句查看《MySQL創(chuàng)建索引》一節(jié)中 tb_stu_info2 數(shù)據(jù)表的索引信息，SQL 語(yǔ)句和運(yùn)行結(jié)果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要參數(shù)說(shuō)明如下：

參數(shù) 說(shuō)明

Table 表示創(chuàng)建索引的數(shù)據(jù)表名，這里是 tb_stu_info2 數(shù)據(jù)表。

Non_unique 表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。

Key_name 表示索引的名稱。

Seq_in_index 表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。

Column_name 表示定義索引的列字段。

Collation 表示列以何種順序存儲(chǔ)在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無(wú)分類(lèi)。

Cardinality 索引中唯一值數(shù)目的估計(jì)值。基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)計(jì)數(shù)，所以即使對(duì)于小型表，該值也沒(méi)有必要是精確的。基數(shù)越大，當(dāng)進(jìn)行聯(lián)合時(shí)，MySQL 使用該索引的機(jī)會(huì)就越大。

Sub_part 表示列中被編入索引的字符的數(shù)量。若列只是部分被編入索引，則該列的值為被編入索引的字符的數(shù)目；若整列被編入索引，則該列的值為 NULL。

Packed 指示關(guān)鍵字如何被壓縮。若沒(méi)有被壓縮，值為 NULL。

Null 用于顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒(méi)有，則該列的值為 NO。

Index_type 顯示索引使用的類(lèi)型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 顯示評(píng)注。

新聞標(biāo)題：mysql怎么查看索引值,查看mysql的索引的命令
網(wǎng)站URL：http://chinadenli.net/article24/dsgocje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、網(wǎng)站維護(hù)、搜索引擎優(yōu)化、品牌網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷(xiāo)推廣、網(wǎng)頁(yè)設(shè)計(jì)公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)