Shulou(Shulou.com)06/01 Report--

Show index from tbl_name\ G

What does each field information represent?

DROP TABLE IF EXISTS t

CREATE TABLE t (

An 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 (cpene 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)

# description

TABLE: the name of the table in which the index is located

Non_unique: a non-unique index, which must be unique. For example, the index defined above to the primary key aQuery unique d is 0.

Key_name: the name of the index

Seq_in_index: the position of the column in the index, such as the federated index of idx_c_b

Column_name: the name of the index column

Collation: how columns exist in the index, either An or the NULL,B+ tree index is always A, that is, sorted. If the Heap storage engine is used and the Hash index is established, NULL will be displayed here

Because Hash stores index data based on hash buckets, rather than sorting the data.

Cardinalilty: this value is critical and represents an estimate of the number of unique values in the index. The number of rows in the Cardinality table should be as close to 1 as possible (why? How do you calculate this value? This field is described in detail below:

Sub_part: whether it is a partial index of the column. For example, the idx_e above shows 10, which means that only the first 10 characters of the e column are indexed. If the entire column is indexed, the field is NULL. (why does idx_b,idx_c_b index only 191?)

Packed: how keywords are compressed. If not, it is displayed as NULL

Null: whether the indexed column contains null values, such as idx_b, which means null values are allowed, so YES is displayed, while null values are not allowed for primary keys and defined c columns.

Index_type: the type of index. The InnoDB storage engine only supports B+ tree indexes, so what is shown here is BTREE.

Comment: comment

Index_comment: index comment

/ /

Cardinalilty: because the word means cardinality, datum

Not all columns that appear in query conditions need to be indexed. For when to add a Btree index, in general, it makes sense to use a Btree index when accessing a small portion of the data in the table. For gender fields, region fields,

Type fields, which have a small range of values and become low selectivity.

E.g:

Select * from stu where sex='F'

When querying by gender, the range of values available is generally only 'Manners, girls and girls'. Therefore, the above results may be 50% of the data in the table. There is no need to add an index at this time.

On the contrary, if a field has a wide range of values and almost no repetition, that is, it is highly selective, it is more appropriate to use an index.

So how to see if the index has a high selection rate?

One is to look at the column Cardinalilty in the show index result, which represents the estimate of the number of records that are not repeated in the index (calculated by adoption), which is not an exact value. Cardinalilty/table_row_counts is as close to 1 as possible

The internal policy for updating Cardinalilty information within the InnoDB storage engine is:

1. The data of 1apace 16 in the table has changed and the information needs to be updated.

2.stat_modified_counter > 2 000 000 000 (2 billion)

That is, when the counter stat_modified_counter changes more than 2 billion times, the Cardinalilty information needs to be updated.

The second method can use the SQL statement to calculate whether it is a high selection rate:

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,1101)

Insert into t_car values (NULL,2,0102)

Insert into t_car values (NULL,3,1103)

Insert into t_car values (NULL,4,1104)

Insert into t_car values (NULL,5,0105)

Insert into t_car values (NULL,6,1106)

Insert into t_car values (NULL,7,1107)

Insert into t_car values (NULL,8,0108)

Insert into t_car values (NULL,9,1109)

Insert into t_car values (NULL,10,1110)

Insert into t_car

Select NULL,id,status,dept_no from tweeka;-- execute it more often.

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)

# indicates that the selection rate of id column is high, which is suitable for indexing, while the selectivity of status column is low, so it is not suitable for indexing on status column.

This is why the number of rows in the Cardinality table should be as close to 1 as possible.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.