MySQL's index and stored procedures

Objective:

One: What is an index

Index is relative to the column that the data can be table

Each table has its own field and table data, field columns, and table data corresponds to one row of data in the table

The index will change this field's

All MySQL column types can be indexed, which means that you can set an index on any of the table's fields.

Greatly accelerates data query speed

Creating an index takes time and increases with the amount of data that is not time consuming.

The index also takes up space, and we know that the data in the data table will also have the highest maximum setting, and if we have a large number of index creation, the index file may be faster than the data file to reach the online value

When the data in the table is added, modified, deleted, the index also needs dynamic maintenance, reducing the data maintenance speed

Look up the man with Xinhua dictionary

We get a man who doesn't know, and wants to know what it means. How to read

Make sure it has no radicals, and if not, just count how many strokes it has.

If you have radicals, count how many of its radicals are.

By the numbers, we find out which page the radicals are in.

Where to find that night and start looking for this guy.

If there are no radicals, we need to start looking for the first page until we find it.

Obviously, this is a very slow speed.

If you have a pinch surface index directory, it will greatly reduce the time we query

Dictionaries have thousands of men, in the absence of an index directory, we want to find a word that takes a lot of time

But through the index directory, it greatly reduces our query time, greatly improve the efficiency

The radical character of the stroke radicals and the part that removes the radicals can be used to define the kanji in the dictionary.

And then we go straight to where it's going to be.

The concept of an index in a database is similar to this comparison

Indexed by all values for a field in a table

When we want to know where it is

Directly through the index to find its location quickly

Otherwise, the database will be the same as us. From the first line to retrieve, if the amount of data is particularly large, this efficiency is obviously very low

As for how the database is indexed to a field, there is no energy to study it, just knowing the meaning and purpose of the index can

--Advantages

Theoretically, we can create an index on any field in the table (which is definitely not needed in practice).

Greatly accelerates the database query speed for indexed fields

--Disadvantages

Creating an index can be time consuming

Indexes require a database to maintain him, when we change the data in the table, (modify, add, delete index columns) The index of the entire table will also be modified, which will cause us to add, or modify the speed will be slow

Memory database to allocate memory to the index, this is a very expensive

　　

--Principle of use:

Reduce the number of indexes by a table that frequently changes data

Tables with small amounts of data do not need to be indexed

Avoid columns that have more duplicate values, create an index

Often appear in the field of the conditional judgment to build the index

--Index classification

Single-column index:

Such an index only works for a column in a database table

Unique index:

The values in the indexed column must be unique, allowing a null value

Primary key Index

　　　　

Combined index

The index corresponds to several columns in the table field,

Full-text index:

Only the MyISAM engine can be used, and the index can only be used on Char VARCHAR text type fields

Similar keyword search

Spatial index:

Only the MyISAM engine can be used

　　　　

To create and delete an index:

　　

MySQL's index and stored procedures