MySQL的showindex選擇率

show index from tbl_name\G;

西峽網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)公司等網(wǎng)站項目制作，到程序開發(fā)，運營維護。成都創(chuàng)新互聯(lián)2013年開創(chuàng)至今到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。

里面的每個字段信息各代表什么呢？

DROP TABLE IF EXISTS t;

CREATE TABLE t(

a? int not null,

b varchar(2000) ,

c int not null,

d int,

e varchar(200),

primary key(a),

key idx_b(b),

key idx_c(c),

key idx_c_b(c,b),

unique key(d),

key idx_e(e(10))

)engine=innodb;

MySQL>show index from t;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t? ? ?|? ? ? ? ? 0 | PRIMARY? |? ? ? ? ? ? 1 | a? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 0 | d? ? ? ? |? ? ? ? ? ? 1 | d? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ?NULL | NULL? ?| YES? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 1 | idx_b? ? |? ? ? ? ? ? 1 | b? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ? 191 | NULL? ?| YES? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 1 | idx_c? ? |? ? ? ? ? ? 1 | c? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 1 | idx_c_b? |? ? ? ? ? ? 1 | c? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 1 | idx_c_b? |? ? ? ? ? ? 2 | b? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ? 191 | NULL? ?| YES? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t? ? ?|? ? ? ? ? 1 | idx_e? ? |? ? ? ? ? ? 1 | e? ? ? ? ? ?| A? ? ? ? ?|? ? ? ? ? ?0 |? ? ? ?10 | NULL? ?| YES? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

7 rows in set (0.00 sec)

#說明

TABLE：索引所在的表名

Non_unique:非唯一的索引，必須要唯一，例如上面定義到主鍵a，unique d? ?都是顯示是0

Key_name:索引的名字

Seq_in_index:索引中該列的位置，如idx_c_b 的聯(lián)合索引

Column_name:索引列的名稱

Collation:列是以什么方式存在在索引中索引中的，可以是A或是NULL，B+樹索引總是A，即是排序的。如果使用了Heap存儲引擎，并且建立了Hash索引，這里就會顯示NULL了

? ? ? ? ? 因為Hash根據(jù)hash桶存放索引數(shù)據(jù)的，而不是對數(shù)據(jù)進行排序。

Cardinalilty:這個值非常關(guān)鍵，表示索引中唯一值的數(shù)目的估計值。Cardinality表的行數(shù)應(yīng)盡可能接近1（為什么？怎么計算這個值？），下面會對這個字段進行詳細的說明：

Sub_part:是否是列的部分索引，例如上面的idx_e就顯示10，表示只對e列的前10個字符進行索引。如果索引整個列，則該字段為NULL。(idx_b,idx_c_b為什么只索引191個呢？)

Packed:關(guān)鍵字如何被壓縮。若沒有，則顯示為NULL

Null:是否索引的列含有NULL值，例如看到的idx_b,就表示可以有NULL值，所以顯示YES，而主鍵和定義了c列就不允許有NULL值

Index_type:索引的類型，InnoDB存儲引擎只支持B+樹索引，所以這里顯示的都是BTREE。

Comment:注釋

Index_comment:索引注釋

////////////////////////////////////////

Cardinalilty:因為單詞的意思為：基數(shù)、基準的意思

?并不是在所有的查詢條件中出現(xiàn)的列都需要添加索引，對于什么時候添加B+樹索引，一般情況下，在訪問表中很少的一部分數(shù)據(jù)時使用B+樹索引才有意義。對于性別字段、地區(qū)字段、

類型字段，它們可取值的范圍很小，成為低選擇性。

e.g:

select * from stu where sex='F';

按性別進行查詢時，可取值的范圍一般只有'M','F'。因此上述得到結(jié)果可能是表50%的數(shù)據(jù)。這時添加索引完全沒有必要。

相反，如果某個字段的取值范圍比較廣，幾乎沒有重復(fù)，即屬于高選擇性，則使用索引比較合適。

那怎么樣看索引是否有高選擇率呢？

一是通過show index結(jié)果中的列Cardinalilty來觀察，此值表示索引中不重復(fù)記錄數(shù)量的預(yù)估值（是通過采用來進行計算的），這個值不是一個精確值。Cardinalilty/table_row_counts盡可能的接近1

InnoDB存儲引擎內(nèi)部對更新Cardinalilty信息的策略為：

1.表中1/16的數(shù)據(jù)已發(fā)生變化就需要更新信息

2.stat_modified_counter>2 000 000 000 (20億)

也是就是當計數(shù)器stat_modified_counter發(fā)生變化的次數(shù)大于20億時，需要更新Cardinalilty信息。

第二種方法可以用SQL語句來進行計算是否是高選擇率：

DROP TABLE IF EXISTS t_car;

CREATE TABLE t_car(

id BIGINT NOT NULL AUTO_INCREMENT ,

mem_id BIGINT NOT NULL,

status TINYINT(1),

dept_no INT NOT NULL,

PRIMARY KEY(id),

KEY idx_mem_id(mem_id),

KEY idx_status(status),

KEY idx_dept_no(dept_no)

)ENGINE=innodb;

insert into t_car values(NULL,1,1,101);

insert into t_car values(NULL,2,0,102);

insert into t_car values(NULL,3,1,103);

insert into t_car values(NULL,4,1,104);

insert into t_car values(NULL,5,0,105);

insert into t_car values(NULL,6,1,106);

insert into t_car values(NULL,7,1,107);

insert into t_car values(NULL,8,0,108);

insert into t_car values(NULL,9,1,109);

insert into t_car values(NULL,10,1,110);

insert into t_car

select NULL,id,status,dept_no from t_car;??-- 多多執(zhí)行幾次

mysql>select count(*) from t_car;

+----------+

| count(*) |

+----------+

|? ? 20480 |

+----------+

1 row in set (0.10 sec)

mysql>update t_car set mem_id=id;

Query OK, 20460 rows affected (3.43 sec)

Rows matched: 20480? Changed: 20460? Warnings: 0

mysql>show index from t_car;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| t_car |? ? ? ? ? 0 | PRIMARY? ? ?|? ? ? ? ? ? 1 | id? ? ? ? ? | A? ? ? ? ?|? ? ? ?20108 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t_car |? ? ? ? ? 1 | idx_mem_id? |? ? ? ? ? ? 1 | mem_id? ? ? | A? ? ? ? ?|? ? ? ?20108 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t_car |? ? ? ? ? 1 | idx_status? |? ? ? ? ? ? 1 | status? ? ? | A? ? ? ? ?|? ? ? ?10054 |? ? ?NULL | NULL? ?| YES? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

| t_car |? ? ? ? ? 1 | idx_dept_no |? ? ? ? ? ? 1 | dept_no? ? ?| A? ? ? ? ?|? ? ? ?20108 |? ? ?NULL | NULL? ?|? ? ? | BTREE? ? ? |? ? ? ? ?|? ? ? ? ? ? ? ?|

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

root@localhost[zjkj]:04:07:14>select count(distinct(id))/count(*) as id_select,count(distinct(status))/count(*) as status from t_car;

+-----------+--------+

| id_select | status |

+-----------+--------+

|? ? 1.0000 | 0.0001 |

+-----------+--------+

1 row in set (0.16 sec)

#說明id列的選擇率較高，適合建立索引，而status列選擇性較低，因此status列上不適合建立索引。

這也是為什么Cardinality表的行數(shù)應(yīng)盡可能接近1越好的緣故了。

新聞標題：MySQL的showindex選擇率
文章鏈接：http://bm7419.com/article48/psschp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設(shè)計公司、網(wǎng)站設(shè)計、品牌網(wǎng)站制作、響應(yīng)式網(wǎng)站、用戶體驗、云服務(wù)器

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

猜你還喜歡下面的內(nèi)容