Notes about MySQL Indexes

Notes about MySQL Indexes

Index

In fact, the data in the database is stored by page.
In fact, indexes are stored by page.
Therefore, in essence, indexes also occupy hard disk space (in exchange for the greatest benefit with the minimum consumption)

Indexing is an effective way to combine data! Pave the way for Quick Searching for specified records

The purpose is to quickly or a record! Improves the database retrieval speed!
Purpose:
Greatly improving the database retrieval speed
Improve database performance

MySQL index storage type classification
01. B-tree indexes: supported by InnoDB and MyISAM
02. Hash Index

In fact, indexing is a double-edged sword (both advantages and disadvantages). Improper use will reduce index performance.

Primary Key: accelerate data retrieval/Unique Identifier

Common Index types:
01. Normal index: duplicate value and null value can appear in the column defined as index!
02. Unique index: duplicate values are not allowed in the index column, but a null value can appear!
03. Primary Key Index: A primary key index is created by default when a primary key is created! Unique! Cannot be blank!
04. Composite Index: Combine multiple columns! (Name, sid) name and ID card number!
05. Full-text index: Full-text query of allowed values! Null and repeated values are allowed! It is suitable for columns with more content! Text!
06. Spatial Index: Index created for spatial data Columns

■ Select columns for indexing according to the following criteria
Columns that are frequently searched
Columns frequently used for query Selection
Columns sorted and grouped frequently
, Which is often used as the connection column (primary key/foreign key)
■ Do not create an index using the following columns
Only several columns with different values
The table contains only a few rows.

Eg:

-- Query the index of a specified table
Show index from student;
TABLE: The TABLE where the index is located.
Non_unique: indicates whether the index is unique. 0: Unique. 1: not unique.
Key_name: Index name
Seq_in_index: Position of the column in the Index
Column_name: name of the Column Used for definition
Null: whether the column is empty
Index_type: Index type

-- Add a common index to the name
Create index index_studentName
ON student (studentName );
-- Adds a combined index for the Student name and ID card number
Create index index_name_sid
ON student (studentName, identityCard)

-- Delete an index
Drop index index_name_sid ON student;
Drop index index_studentName ON student;

-- Principles for creating Indexes
01. columns frequently queried
02. columns that are frequently used for selection
03. columns that are sorted frequently.
04. columns frequently used for connection (primary key/foreign key)

Notes for using indexes:
01. Reduce the use of * to return all columns during query. Do not return unwanted columns!
02. Try to minimize the number of indexes. Create an index on a column with a small number of bytes!
03. When the where clause contains multiple conditional expressions, columns containing indexes must be placed before other expressions!
04. Avoid using expressions in order by statements!

This article permanently updates link: https://www.bkjia.com/Linux/2018-02/151074.htm