mysql typeindex_MySQL index type, index structure and index operation

Index classification

Single index ： An index contains only one column .

ALTER TABLE table_name ADD INDEX index_name (col);

Composite index ： An index contains multiple columns .

ALTER TABLE table_name ADD INDEX index_name (col1, col2);

Index type

primary key ：

ALTER TABLE table_name ADD PRIMARY KEY (col);

unique index ：

The value of the index column must be unique , But allow NULL value .

ALTER TABLE table_name ADD UNIQUE index_name (col);

General index ：

ALTER TABLE table_name ADD INDEX index_name (column_list);

Full-text index ：

ALTER TABLE table_name ADD FULLTEXT index_name (column_list);

Index structure

BTree Indexes

Hash Indexes

Hash index (hash index) Implementation based on hash table , Only queries that exactly match all columns of the index are valid . For each row of data , The storage engine calculates a hash code for all index columns (hash code), The hash code is a smaller value , And the hash codes calculated by the lines with different key values are also different . Hash index stores all hash codes in the index , At the same time, the pointer to the physical address of each data row is saved in the hash table .

Operation of index

Create index ：

ALTER TABLE table_name ADD INDEX index_name (column_list);

Delete index ：

DROP INDEX index_name ON table_name;

Look at the index ：

SHOW INDEX FROM table_name\G