MySQL的show index 選擇率

show index from tbl_name\G;

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

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;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| 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 的聯合索引

Column_name:索引列的名稱

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

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

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

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

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

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

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

Comment:注釋

Index_comment:索引注釋

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

Cardinalilty:因為單詞的意思為：基數、基準的意思

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

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

e.g:

select * from stu where sex='F';

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

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

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

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

InnoDB存儲引擎內部對更新Cardinalilty信息的策略為：

1.表中1/16的數據已發生變化就需要更新信息

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

也是就是當計數器stat_modified_counter發生變化的次數大于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;? ?-- 多多執行幾次

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;

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

| Table | Non_unique | Key_name? ? | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| 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表的行數應盡可能接近1越好的緣故了。