北京赛车pk10直播开奖
首頁
登錄 | 注冊

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)

[email protected][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越好的緣故了。

MySQL的show index 選擇率


2019 monjeep.com webmaster#monjeep.com
12 q. 0.010 s.
京ICP備10005923號
北京赛车pk10直播开奖
大乐透不变的规律技巧 中国体彩网官方网 澳洲幸运8开奖网app 高中五大联赛是哪五大 一分快三精准计划网址 快乐飞艇是那个博彩公司出的 足彩身价 河南481走势图今天30期 中国福彩北京赛车pk10 11186期31选7