MySQL index (index invalidation)

Index

The index is also a table that holds the primary key and index fields and points to the records of the entity table.

MyISAM storage engine, data files, index files, table structure files are stored separately

InnoDB storage engine, data and indexes stored in one file

B+tree Index

Hash index

Hash index (only supported by Memory storage engine), finding a record is very fast

B+tree indexes (InnoDB and MyISAM support), more suitable for operations such as sorting

Data types that are suitable for indexing

(1) Smaller data types, requiring less space

(2) Simple data type, the integer type is smaller than the string cost

(3) Avoid using null,null will result in index invalidation

Primary key Index

Unique index

Normal index

Full-Text Indexing

Combined index

Displays all index information for the current table

Show index from Tablename\g

1. General Index

Create INDEX Idx_name on TableName (colname (length))

If it is char or varchar length can be less than the actual length, if the Blob and text must specify length

ALTER TABLE TableName Add index Idx_name (colname)

Drop Index idx_name on TableName

A single table can have multiple normal indexes

2. Unique index

The value of the column must be unique, allowing null values

If it is a composite index, the combination of the values of the columns must be unique

Create Union Index Idx_name on TableName (colname)

ALTER TABLE table_name add unique Idx_name (colname)

A table can have multiple unique indexes

3. Primary KEY index

The data type of the primary key is preferably an integral type

The primary key index can have only one

The value of the primary key column must be unique and cannot be null

Auto-growing columns must be primary keys

Primary key columns are not necessarily auto-growing

ALTER TABLE TableName add primary key (Col_name)

4. Full-Text Indexing

InnoDB not supported

ALTER TABLE TableName add fulltext idx_name (col_name)

Index invalidation

1. Best left prefix principle

Composite index, starting at the leftmost column of the order that is not defined by the index

2.like '%Z '

Like fuzzy query, start with%, cause index to fail

3. Full expiration after range

4. Null value encountered, index invalidated

5. An explicit or implicit operation on an indexed column causes the index to fail

6.order by

Because the query uses only one index, if the where statement uses an index, the order BY statement does not use the

7. Order by not in one Direction causes index invalidation

Full ASC or Full desc

8. Combine index, skip a column in the middle, and the subsequent columns are all invalidated

MySQL index (index invalidation)