preface
The establishment of MySQL index is very important for the efficient operation of MySQL, index can greatly improve the speed of MySQL retrieval.
For example, if a properly designed MySQL with an index is a Lamborghini, then MySQL without an index is a human tricycle.
Take the Chinese dictionary’s catalogue page (index) as an example, we can quickly find the word we need according to the catalogue (index) sorted by pinyin, strokes, radicals, etc.
Index can be divided into single column index and composite index. Single-column index, that is, an index contains only a single column. A table can have multiple single-column indexes, but this is not a composite index. Composite indexes, in which an index contains multiple columns.
When creating an index, you need to ensure that the index is a condition applied to an SQL query (typically as a condition in a WHERE clause).
In fact, an index is also a table that holds the primary key and index fields and points to the records of the entity table.
All this is about the benefits of using indexes, but using them too much can lead to abuse. Therefore, indexes also have their drawbacks: while indexes greatly speed up queries, they slow down the speed of updating tables, such as INSERTS, updates, and deletes. When updating a table, MySQL not only saves the data, but also the index file.
Index files that take up disk space.
1. What does an index do?
Indexes are used to quickly find rows that have a particular value in a column. Instead of using indexes, mysql must start with the first record and read the entire table until it finds the relevant rows. The bigger the table, the more time it takes. If the query column in the table has an index, mysql can quickly get to a place to search the middle of the data file without having to look at all the data. Most mysql indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B trees. However, indexes for spatial column types use R-trees, and MEMORY tables also support hash indexes.
2, index is so complicated, how should I understand index, is there a more vivid example?
Yes, imagine that you have a dictionary in front of you, and the data is the text of the book, you are the CPU, and the index is the table of contents
3. The more indexes, the better?
Indexes can greatly improve query efficiency in most cases, but:
Data changes (additions, deletions, changes) require maintaining indexes, so more indexes mean more maintenance costs
More indexes mean more space (50 pages of table of contents for a 100-page book?).
If a table is too small, building an index may be slower :(reading a 2-page brochure, you have to find the table of contents first?)
4, index field type problem
Text or index (length required)
The length of myISAM storage engine index keys cannot exceed 1000 bytes
Try to keep the filtered values of the same data type as the indexed columns
5, like can’t use index?
Keep like to a minimum, but it’s not absolutely impossible. “XXXX %” can be used with indexes,
Imagine you are looking at a dictionary of idioms. The list is set up in alphabetical order. The search requirements are: you want to find an idiom that begins with “one” (” one percent “), and you want to find an idiom that contains one word (“% one percent “).
In addition to like, the following operators can also be used for indexes:
<, <=, =, >, >=, BETWEEN, IN<>, notin,! = is notCopy the code6. What fields are not suitable for indexing?
In general, columns with values that are too unique (gender, type, etc.) are not suitable for indexing (what is too small? At half time, if the value of the same data exceeds 15% of the table, there is no need to build an index.)
If a column is too long, you can select a partial index (e.g., the first ten digits only).
Data that is updated very frequently is not suitable for indexing. Sense)
Can multiple indexes be used for a query?
Can’t
8, how to build index for multi-column query?
Only one index can be used in a query, so we should first kill a and B to create index scheme A or B? Whoever has the highest degree of differentiation (the fewest equals), build who! Of course, the joint index is also a good solution, ab, or BA, same as above, the high degree of differentiation, first
9. Joint index problems?
Where a = “XXX” where a = “XXX” where b = “XXX” So, in most cases, if you have an AB index, you don’t need to create an A index
10. What are some common cases where indexes cannot be used?
like "%xxx"
not in,! =Copy the code
Where md5(password) = “XXXX”
WHERE index=1 OR A=10Copy the code
If you store a numeric string field (such as a mobile phone number), do not drop the quotation marks of the value, otherwise you cannot use the index related to the field. Otherwise, it does not matter
That is
?
select * from test where mobile = 13711112222;Copy the code
We cannot use the index mobile (if mobile is a char or vARCHar).
BTW, never try to use int to store phone numbers (why? You want to! Try it yourself)
11. Covering Indexes are more efficient
If the index contains all the values you need, select them only. In other words, select only the fields you need. Avoid select * if you don’t have to
12. The NULL problem
Nulls cause indexes to become NULL, so avoid nulls when designing table structures (express your desired NULL in other ways, e.g. -1?).
13, how to view the index information, how to analyze whether to use the index correctly?
show index from tablename; Explain the select... ;Copy the code
For Explain, you can write a how-to post sometime, but until then, try Google
Know your system, don’t optimize it too early!
Premature optimization has always been a very annoying and ever-present problem, most of the time because you do not know your system, do not know the real carrying capacity of your system
For example: thousands of data news tables, hundreds of thousands of text searches every day, most of the time we can rest assured to like, rather than to build a set of full-text search what, after all, CPU is still more powerful than the human brain too much
15. Share a small case:
Once there was a friend looking for a board, said: the master to help see, the company website can not open the board smiled: the master can not dare to ah, wait for me to see again board spent 10 minutes to analyze the next: Small and medium-sized enterprise station, the amount is not large (twenty or thirty thousand PV per day), independent server, the amount of data is not large (100M less than), should not be too slow an outsourcing team to do the project, disrepair, complete transformation? Don’t reality! Spent 20 minutes and the board can be added to the index of the fields are added to the index, so quiet in the world the friend say: another elder brothers say, outsourcing cost, optimization of at least 2 w you in 30 minutes, it seems, the master is worthy, you choose a best restaurant Bat: like some western food then, changshu KFC road subway station waiting for you!
16. Finally: Never forget the keyword SQL injection
I hope this article will help you with mysql database programming.
The last
Welcome everyone to pay attention to my public kind hao [Programmer Tracking wind], sorted out 2019 many companies Java interview questions more than 100 pages of PDF documents, articles will be updated in it, sorted materials will also be placed in it.