Learn more about MySQL indexes

1. Indexing principle

Indexes are used to quickly find rows with a specific value in a column. Without an index, MySQL would have to start with the first record and then read the entire table until it found the relevant rows. The larger the table, the more time it takes. For an ordered field, a binary search can be applied, which is why the performance can be substantially improved. Both MYISAM and INNODB use B+Tree as the index structure

(Primary key and unique key will add index by default)

2. Create an index

If we do not use the index, we will perform a full table scan when we query for salaries greater than 1500.

When do you need to add an index to a field?

-The amount of data in this field in the table is huge

-Fields that are frequently searched and often appear in where clauses

- It is not recommended to add indexes to fields that are frequently operated by DML

An index is like a table of contents in a book.

The primary key will automatically add an index, so it is more efficient to query based on the primary key.

If you frequently query based on sal and encounter performance bottlenecks, first check whether there is an algorithm problem in the program, and then consider indexing sal. The index is created as follows:

1. create unique index index name on table name (column name);

create unique index u_ename on emp(ename);

2. alter table table name add unique index index name (column name);

2. alter table table name add unique index index name (column name); 

3. View the index

show index from emp; 

4. Use indexes

Note that you must not use select * ... You can see that type!=all, indicating that the index is used

explain select sal from emp where sal > 1500;

The sal in the condition uses an index

As shown above: If we want to find all rows where sal is greater than 1500, we can scan the index. The index is sorted and the result is 7 rows. We know that there will be no more matching records and we can exit.

If you are looking for a value that does not appear before some intermediate point in the index table, there are also location algorithms that find its first matching index entry without doing a sequential scan of the table (such as binary search).

In this way, the first matching value can be quickly located to save a lot of search time. Databases use a variety of techniques to quickly locate index values, which are usually the work of the DBA.

5. Delete index

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

The first two statements are equivalent and delete the index index_name in table_name.

The third statement is only used when deleting the PRIMARY KEY index, because a table can only have one PRIMARY KEY index.

mysql> ALTER TABLE EMP DROP INDEX test_index;

After the deletion, the index is no longer used and the query will perform a full table scan.

The above is the detailed content of in-depth understanding of MySQL index. For more information about MySQL index, please pay attention to other related articles on 123WORDPRESS.COM!