MySQL's index

1. About Query Caching

Take a look at 2 examples directly: 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4C/C1/wKioL1RE1_aQWzaqAAC-tRq76Ak323.jpg "title=" Mysql13.png "alt=" Wkiol1re1_aqwzaqaac-trq76ak323.jpg "/> 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4C/C1/wKioL1RE2AWhEA05AACPYb6eaGk941.jpg "title=" Mysql14.png "alt=" Wkiol1re2awhea05aacpyb6eagk941.jpg "/> It can be found that the result of the first execution of SQL is cached, and the second time you execute the same SQL, it will be much faster. Can that be done in the first time, very fast? Inexpensive: Use index.

2. About Indexes

Primary key Index Note that when a table field is primary key, it automatically becomes the primary key index. 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4C/C1/wKiom1RE2TXx5zXFAAFqbv-ZYpw749.jpg "title=" Mysql15.png "alt=" Wkiom1re2txx5zxfaafqbv-zypw749.jpg "/> "Use show index (ES)/keys from table to view an index on a table" Querying on an indexed column is generally very fast, for example: 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4C/C1/wKiom1RE2azzoU5NAABSMFWEW-w066.jpg "title=" Mysql16.png "alt=" Wkiom1re2azzou5naabsmfwew-w066.jpg "/> Note that this table is very large (+300w), but the time to find a record is almost 0. Normal index is to create it on a regular column. Full-Text Indexing For example, to search for keywords in a piece of paper. A table for the MyISAM engine type. To create a full-text indexing method: Fulltext (Col1,col2,...) If you want to apply a full-text index, you cannot use the SELECT * from user where name like '%keyword% ' method. How to use: Match (col1,col2,...) against ' keyword ' In real-world development, we often need to know whether a SELECT statement is using an index, how MySQL executes it, and we can use the explain command to view it to help us optimize the SQL structure. Examples are as follows: 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/51/74/wKiom1RjYNvSP4zYAACH9NYl2a0938.jpg "title=" Index.png "alt=" Wkiom1rjynvsp4zyaach9nyl2a0938.jpg "/> Focus Attention: ID MySQL Query serial number Select_type has simple/union/subquery ... Type scan means "all represents a full table scan, the system table has only one row, and the const has a maximum matching row" Possible_keys the index that may be used The index that key actually uses Key_len uses the length of the index. This is used in the composite index, which is the multi-column index, to determine which indexes are used effectively, and which indexes are not using the "leftmost prefix principle to adjust the use of the index policy" Rows indicates the estimated number of result set rows Extra additional information for SQL statements "Using where means that you do not have to read all the information in the table, you can only get the data you need through the index, using temporary some group by order by operations to use temporary tables" With the help of the explain command, we can adjust the SQL, after the SQL is adjusted, we can actually use: Show Profiles; To analyze the execution time of SQL before and after adjustment

3. Index principle Analysis

MySQL defines the index as: A data structure that helps MySQL to get a high-efficiency database. Rows, tables, files 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/4D/F4/wKioL1Rdfr3QNdE1AAE_RU-UYP8119.jpg "title=" Index01.png "alt=" Wkiol1rdfr3qnde1aae_ru-uyp8119.jpg "/> Analysis: A. Rows are stored in the file. B. Rows, tables are the concept of a database, and the operating system does not know these logic. C.mysql's MyISAM storage engine stores data from one table to one file, and InnoDB by default, you will data from different tables is stored in a single file. D. Each file can be divided into pages. For example, 5 pages are divided. E. To add data, MySQL inserts data on the last line of the last page, and if the last page is full, You need to be reborn into a page. F. If there are gaps in each page, how is this done? According to E, it is certainly not the result of adding data. This is caused by the deletion of data. In other words, the deletion causes the page to fragment. So why doesn't MySQL automatically fill this void when it's added? If a table is large, Every time MySQL adds data, it needs to go from top to down to find gaps, so there's a big delay. G. How many lines can a page have? For example, a page size of 4KB, a row of records occupy 90 bytes, then 4*1024/90=45, that is to say probably a page to place 45 data. The size of the page is related to the operating system and database. H. page is the basic unit of I/O. The database could not instruct the operating system to get some records or get a few bytes of data, only to tell the loading Which pages to memory, and then the database finds the desired data in the page. In other words, a record should be The page mark + line marks together constitute the only indication of this record. How does an index work? If a table is large, there are millions of rows, each time using the sequential search line, it is bound to inefficient. And the index, this data structure, provides another way to find a table. Look at a simplified version of the MySQL index: "Build the No column of the table in the diagram above" 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4D/F5/wKiom1RdhW_TZFceAAFQ4WQgjnA855.jpg "title=" Index05.png "alt=" Wkiom1rdhw_tzfceaafq4wqgjna855.jpg "/> A. The data structure of an index is represented as a multi-node tree B. There is a root node C. The values within the node are ordered D. You can see that the value of the node that the root node of 44 P1 points to is less than or equal to 44. Actually the other nodes It's all the same. E. The pointer px in the node, either pointing to the next node, such as P3, or to a record in the table, such as P6. for P6 this point The node that is recorded in the table is the leaf F. The leaves are linked, meaning that a leaf points to the next leaf G. Actually P6 equivalent to the page Mark + line mark Index Lookup Example Analysis: Case one: Find all rows of no=39 using an index The first step, starting from the root node, at which point the root becomes the active node The second step is to determine if the active node is not a leaf, and if it is not a leaf, proceed to the third step; Fourth Step The third step, because of the 39<44, so P1 point to the node becomes the active node, continue the second step Fourth, find the value in the active node, remove the pointer, that is, the value of the page marked Fifth, the database instructs the operating system to load these pages, and then find the no=39 rows in these pages Visible, find a specific value for all the rows, MySQL takes advantage of the index and does not need to browse all the rows that can be quickly found. Case two: Get all rows sorted by no Find the leaf directly, get all the pages of P6 first, then all the pages of P7. Because there are leaves behind the leaves, the next leaf continues the process. no=2, I need to take the second page. No=6, you need to take the first page No=7, I need to take the third page. No=8, I need to take page fourth. no=27, I need to take the second page. ... It can be found that because the rows in the file are not sequential, some pages must be fetched multiple times, which increases Processing time. (The number of pages in memory is limited, and it is likely that some pages are not already in memory, so you must Get the page back from the hard drive) In order to speed up this process, the rows in the file must be stored in an orderly fashion, so it is possible that each page only needs to get Once, for example no=2 in x page, then no=6 does not need to fetch the page again, because the correct page already exists memory , MySQL understands this. "Above is the concept of a clustered index"

4. Index Summary

Insert,update,delete, MySQL is required to maintain the index tree The pointer to the leaf in the image above may point to a row, or it may point to multiple lines If no is a primary key, then obviously a no can have only one record, then the pointer points to only one record. If we are indexing on name, the pointer will point to multiple because name is not unique. An indexed node is like a row in a table and is a physical space that needs to be occupied.

This article is from the "I want to surpass myself" blog, please be sure to keep this source http://zhangfengzhe.blog.51cto.com/8855103/1575893

MySQL's index