MySQL optimization index

MySQL optimization using the index

1, Index Introduction

An index is a single spatial structure that exists alone. The data table can be indexed fields, and physical addresses, exist in a block, this space is ' index '.

Query data from the index query first, after the query, you can directly locate the physical address, through the physical address, directly find the real data. Queries are faster.

An index is a way to change time in space, sacrificing space and writing speed, and improving query speed

2. Prepare Demo Data Sheet

Here, for example, the database for the MyISAM engine, I have prepared a table of 1.8 million data, which is stored with three files. FRM is a table structure file. MyD is a table data file. Myi is the table index file, under Mac Use Administrator mode to copy these three files to MySQL corresponding directory

Tip: Mac uses the SU command to switch to root mode

Exit root mode

See if there is a table in the database student

Look at the results, there are no primary keys, because the primary key is indexed by default

3, adding use Index

First look at the query speed when not indexed

Set the empno as the primary key

Look at the query speed again.

You will find that query speed is greatly improved

So how do you add a non-primary key index, for example, to check the records of Ename= Jkymol?

The query takes 0.37 seconds to add an index to ename

Check again.

Now let's summarize the syntax for adding indexes

PRIMARY KEY index: ALTER TABLE name add PRIMARY key (primary key field)

Unique index: ALTER TABLE name add unique key field

Normal index: ALTER TABLE name add key (normal field)

Full-text index: ALTER TABLE name ADD fulltext key (text Type field, mysql5.6 following MyISAM support)

Compliant index: ALTER TABLE name add index type [index name] (field)

5, the index can be added then can be deleted, how to delete it

with the self-increment property primary key index deleted, you need to delete the self-increment property first

Delete (Modify) The Increment property syntax: ALTER TABLE table name modify Field field property

Delete PRIMARY KEY index syntax: ALTER TABLE name drop PRIMARY key

Delete non-primary KEY index syntax: ALTER TABLE name DROP INDEX name

For example, delete an index ename

Now, check the data again.

You can see that the query time is getting longer.

Add: See if a query statement will use the index

Syntax: Explain SQL statement \g

Description: The following \g is the formatted output

For example:

6, Index principle

Many times, the index is added, but it is not used. In this case, the index is actually not playing a role. To avoid this situation.

Column Independent, that is, the fields of the SQL statement do not allow operations, including function methods

Left fixed, use fuzzy query only left fixed to use index

Add an index to the ENAME field first ALTER TABLE EMP add key (ENAME);

Use show create table emp; command to view add results

Query the Ename field using a fuzzy query

Find that the query speed is only fast when left fixed

Composite index, multiple field combinations are indexed, fields appear at the same time, can be used to index, separate occurrences are not used to index

Build a composite index for enamel and DEPTNO

View Index Usage

Or principle, index is available at both ends of the or condition, index is unavailable, and there is no index at one end, indexes are not available

MySQL optimization index