How to use mysql indexes

Mysql index tutorial bitsCN.com

Mysql Tutorial: index usage

1. index is a data structure that helps MySQL efficiently obtain data.

It is critical for high performance, but people often forget or misunderstand it.

The more data the index is, the more important the index is. Databases with small sizes and low loads can have good performance even if they do not have indexes. However, when data increases, the performance decreases rapidly.

Tip: worm replication, which can quickly replicate a large amount of data

Example: insert into emp select * from emp;

2. common indexes in MySQL

◆ Normal index

◆ Unique index

◆ Primary key index

◆ Composite Index

◆ Full-text index

◆ Foreign key (only supported by the innodb storage engine)

2.1 Normal indexes:

This is the most basic index with no restrictions. There are several creation methods:

There are several creation methods:

◆ Create an index

Create index indexName ON tablename (username (length ));

◆ Modify table structure

ALTER tablename add index indexName (username (length ))

Tip: length can be smaller than the actual length of the field. if it is BLOB or TEXT, you must specify length, the same below

◆ When creating a table, specify

Create table mytableuuu (id int not null, username VARCHAR (16) not null, INDEX indexName (username (length )));

Create table mytable (id int not null, username VARCHAR (16) not null );

Create index index1 on mytable (id); // create a common index

◆ Delete an index:

Drop index index1 on mytable;

There is a concept,

Row definition: defined when Fields (columns) are declared, for example, primary key.

Table definition: defined after all fields (columns) are declared, such as primary key and index.

Create table mytable (id int not null, username VARCHAR (16) not null, index index1 (username ));

3.0 unique index (unique)

The value of the index column must be unique, but null values are allowed.

1) Create an INDEX: Create unique index indexName ON tableName (tableColumns (length ))

2) modify the table structure: Alter tableName add unique [indexName] ON (tableColumns (length)

3) when creating a TABLE, specify: Create TABLE tableName ([...], UNIQUE [indexName] (tableColumns (length ));

4.0 primary key index (primary key)

It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:

Create table mytable (id int not null, username VARCHAR (16) not null, primary key (ID ));

Of course, you can also use the ALTER command.

Tip: Remember: a table can only have one primary key. The primary key index is what we call the primary key. In a table, only one primary key can exist, but multiple common indexes and unique indexes can exist.

5.0 Composite Index

More than 5.1 column indexes are composed of multiple columns to form an index.

Add Composite Index

Alter table mytable add index name_city_age (username, city, age );

Composite indexes can be used only when they are prefixed with the leftmost index in order.

The following can be used for composite indexes:

◆ Username, city, age

◆ Usernname, city

◆ Usernname

The following cannot use composite indexes:

◆ City, age

◆ City

◆ Age

Note that composite indexes: if multiple indexes exist in a table, we can consider using composite indexes for optimization.

5.2 Explain statement: you can query the index types used by SQL query statements.

6. why are there indexes and query acceleration?

6.1 In MySQL, BTREE and binary tree

Binary Tree sorting 35 17 39 9 28 65 56 87

6.2 Advantages of indexing:

Accelerate query speed.

6.2 index disadvantages:

Occupies a large amount of disk space.

But it has an impact on insertion, deletion, and update.

6.3 Tips and notes for using indexes:

◆ The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the index. if a column in the composite index contains a NULL value, this column is invalid for this Composite Index. Therefore, do not set the default value of a field to NULL during database design.

◆ Use short indexes

Index a string or column. if possible, specify a prefix length. For example, if a CHAR (255) column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

◆ Sorting of index columns

MySQL queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. try not to include the sorting of multiple columns. it is best to create a composite index for these columns if necessary.

◆ Like statement operation

Generally, like operations are not encouraged. if they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

◆ Do not perform operations on columns

Select * from users where YEAR (adddate) <2007; the operation will be performed on each row, which will cause the index to fail and scan the entire table, so we can change it

Select * from users where adddate <'2014-01-01 ';

◆ Do NOT use not in and <> Operations

BitsCN.com