Important questions about mysql Indexes

Important questions about mysql Indexes

This article describes several important questions about mysql indexes. We will share this with you for your reference. The details are as follows:

1. What is indexing?

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found.
The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data.
Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored in Tree B. Only the R-tree is used for spatial column indexes, and the MEMORY table also supports hash indexes.

2. The indexing is complicated. How can I understand the indexing? Is there any more image example?

Yes. Imagine that there is a dictionary in front of you. The data is the body of the book. You are the cpu, and the index is the directory of the book.

3. The more indexes, the better?

In most cases, the index can greatly improve the query efficiency,:

Indexes must be maintained for data changes (add, delete, and modify). Therefore, more indexes mean more maintenance costs.

More indexes mean more space is needed (a 100-page book with 50-page directories ?)

If a table is too small, index creation may be slower. :) (read a 2-page publicity manual and go to the directory first ?)

4. Questions about index Field Types

Text type, you can also create an index (length must be specified)

The length of the index key of the myisam storage engine cannot exceed 1000 bytes.

The value to be filtered must be of the same data type as the index column.

5. Does like not support indexing?

Try to reduce like, but it is not absolutely unavailable. "xxxx %" can be used for indexing,

Imagine that you are reading an idiom dictionary, which is created in the alphabetical order of idioms. The query requirement is that you want to find an idiom starting with "1" ("1% "), and you want to find an idiom that contains a Word ("% 1% ")

In addition to like, the following operators can also be used for indexing:

<, <=, =,>,> =, BETWEEN, IN
<>, Not in ,! = No

6. What fields are not suitable for indexing?

In general, the uniqueness of the column value is too small (such as gender, type or something), and it is not suitable for creating an index (how is it too small? Half of the data with the same value exceeds 15 percent of the table, so there is no need to create an index)

For a column that is too long, you can choose to create only partial indexes (for example, to index only the top 10 columns)

Frequently updated data is not suitable for indexing? Italian meeting)

7. Can I use multiple indexes for a single query?

No

8. How to create an index for multi-column queries?

Only one index can be used for one query at a time. Therefore, the index creation schemes for a and B are first shot.
A or B? Who has a higher degree of discrimination (the minimum value for the same value) and who is the author!
Of course, joint indexing is also a good solution, AB, or ba, the same as above, with a high degree of discrimination.

9. What is the issue of joint indexing?

Where a = "xxx" can use AB joint index
Where B = "xxx" is not allowed (imagine, this is the book directory ?)
Therefore, in most cases, if you have an AB index, you do not need to create an A index.

10. Which common cases cannot be indexed?

Like "% xxx"

Not in ,! =

Function operations on columns (for example, where md5 (password) = "xxxx ")

WHERE index = 1 or a = 10

If you store a numeric string field (such as a mobile phone number), do not discard the quotation marks. Otherwise, you cannot use the index related to this field. Otherwise, it does not matter.

That is

select * from test where mobile = 13711112222;

However, the index of the mobile field cannot be used (if mobile is of the char or varchar type)

Btw, do not try to use int to store the mobile phone number (why? Think for yourself! Try it on your own)

11. Covering Indexes are more efficient.

If the index contains all the required values, only select them. In other words, only select fields are required. If not necessary, avoid select *

12. NULL

NULL will cause the index to be empty. Therefore, when designing the table structure, avoid the existence of NULL (use other methods to express the NULL you want to express, such as-1 ?)

13. How can I view the index information and analyze whether the index is used correctly?

show index from tablename;explain select ……;

For more information about explain, you can find a special time to write an entry post. Before that, you can try google

14. Understand your system and do not optimize it too early!

Early optimization has always been a very annoying and always-Existing Problem. Most of the time, it is because you do not know your system, and you do not know the real Carrying Capacity of your system.

For example, for a news table with thousands of pieces of data, we can search for texts several hundred thousand times a day. Most of the time, we can rest assured that we should not create a full-text search or something, after all, the cpu is much worse than the human brain.

15. Share a small case:

A friend once asked the Board and said: the master helped me to see if the company's website could not be opened.
Laugh: Do you dare to be a master? Wait for me to check it out.
The Board took 10 minutes to analyze: Small and Medium-Sized Enterprise sites, small volume (20 thousands or 30 thousands pv per day), independent servers, small data volume (less than MB), should not be too slow
A project by an outsourcing team is out of repair and completely transformed? Unrealistic!
As a result, it took 20 minutes for the Board to add an index to all the fields that can be indexed, so the world was quiet.
My friend said: Another guy said that optimization requires at least 2 W outsourcing fee. You only need 30 minutes. It seems that you are well-deserved. Choose the best restaurant.
Banzi: Let's have a Western meal. You'll be waiting for KFC at Changshu Road Subway Station!

16. Last: never forget the keyword SQL Injection

I hope this article will help you design MySQL database programs.

Articles you may be interested in:

• Index optimization for Mysql Databases
• Mysql performance optimization case study-Covering Index and SQL _NO_CACHE
• Index optimization for mysql Performance Optimization
• Primary Key Index of MySQL Indexes
• Introduction to clustered indexes of MySQL Indexes
• How to create an index using Inplace and Online in MySQL
• Simple tutorial on unique indexes in MySQL
• Tutorial on joint Indexing in MySQL
• How to view, create, and delete indexes in MySQL
• Brief Introduction to MYSQL index types
• Mysql database index corruption and restoration experience