0. The conceptual index of an index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain reference pointers to all records in the datasheet. Like a book in front of the directory, can speed up the database query speed. Whether it is the MyISAM and InnoDB engine, if the table is not displayed when the definition of a row of primary key columns, he will automatically create a hidden primary key index; 1. Advantages of the index: a. Significantly reduce the amount of data that the server needs to scan B. Help the server avoid sorting and staging tables c. turn random io into sequential io 2. " Samsung "System: Index puts related records together, obtains the order of data in a star index and arranges the order in the lookup all the time, and then the column in the one-star index covers all the columns needed in the query, then one star 3. Index type hash index by implementation: Hash table based implementation, Only queries that accurately match all columns of the index are valid, only the memory engine implements a spatial data index (R-tree), and only the MyISAM engine implements full-text indexing: Word-breaker search for keywords, for match Against Operations Btree Index: B-tree index restriction: Indexes cannot be used if they are not found in the order they appear, or columns in the index cannot be skipped, or subsequent indexes are invalid; if there is a range query for a column in the query, none of its right columns can be found by using index optimization; B + Tree index: For full key matching, first column matching (combined index), column prefix matching, range value matching, exact match of first column and range matching second column, access only index (also called index overlay) match;
B+tree indexes are used on MyISAM and InnoDB, but they are implemented in a completely different way. Refer to MySQL's MyISAM and InnoDB index mode https://www.cnblogs.com/zlcxbb/p/5757245 in detail. Html4. The index is divided into clustered index and non-clustered index InnoDB clustered index: The primary key index of the leaf node directly below the data, the other sub-index of the leaf node point to the primary key ID, clustered index can improve the speed of multi-row retrieval. MyISAM non-clustered index: The leaf node of the primary key index only holds the pointer to the data on the physical disk, and the other secondary indexes are the same; the non-clustered index is fast 5 for single-line retrieval. Index types are divided by how they are used
A. Normal index: This is the most basic index and it has no limitations.
B. Unique indexes unique: Similar to a normal index, the difference is that the value of the indexed column must be unique, but allows for a null value. If it is a composite index, the combination of column values must be unique
C. Primary key index PRIMARY key: It is a special unique index and is not allowed to have null values.
D. Full-text index fulltext : Available only for MyISAM tables, generating full-text indexes is time consuming and space-intensive for larger data.
E. Single-column indexes, multicolumn indexes: Multiple single-column indexes differ from the query effect of a single multicolumn index, because MySQL can only use one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.
F. Combined index: In order to extract the efficiency of MySQL, we should consider the establishment of a composite index. For example, create a composite index for title and time in the table: ALTER table Article ADD index Index_titme_time (title (), Time (10)). The establishment of such a composite index, in fact, is equivalent to the following two sets of composite indexes, why there is no time such a combination index it? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index
–title,time–title 6. Index Overlay: If the data you are looking for is exactly an indexed column, then you do not have to go to the physical disk to find the data, that is, do not return to the row, called the index overlay; 7.Index Optimization:Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly. Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements. A. When do I use a clustered or nonclustered index?
| Action description |
use clustered index |
using nonclustered indexes |
| columns are often sorted by grouping |
using |
using |
| to return data in a range |
use |
do not use |
| One or very few different values |
do not use |
do not use |
| small number of different values |
use |
do not use |
| large number of different values |
do not use |
use |
| frequently updated columns |
do not use |
use |
| foreign key column |
using |
using |
| primary key column |
using |
using |
| frequently modifying indexed columns |
do not use |
use |
In fact, we can understand the above table through examples of the previous clustered index and the definition of a nonclustered index. For example, to return data in a range. For example, if you have a table with a time column and you have the aggregate index in that column, you will be very fast when you query the entire data from January 1, 2004 to October 1, 2004, because the body of your dictionary is sorted by date, A clustered index only needs to find the beginning and end data in all the data to be retrieved, rather than a nonclustered index, you must first look up the page number for each item in the table of contents, and then find the specific content based on the page number. B. The index will not contain a column with null values as long as the column contains null values will not be included in the index, as long as one column in the composite index contains null values, then this column is invalid for this composite index. So we don't want the default value of the field to be null when the database is designed. C. Use a short index to index string columns, if possible specify a prefix length. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations. MySQL does not allow the full length of these columns (BLOB, TEXT, long varchar) to be indexed d. Index column sort the MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns. MySQL can use an index to sort the results only if the column order of the index is exactly the same as the ORDER BY clause, and if all columns are sorted in the same direction. Like statement operations: the use of like operations is generally discouraged, and how to use them if they are not used is an issue. The like "%aaa%" does not use the index. And like "aaa%" can use the index. F. Do not perform operations on columns: for example: SELECT * from the users where year (adddate) <2007, the operation will be performed on each row, which will cause the index to fail with a full table scan, so we can change to: SELECT * from Users where adddate< ' 2007-01-01′ g. Multi-column index: If you do an and condition query on multiple individual indexes, you should combine multiple individual indexes into one multicolumn index. If you do an OR condition query on multiple individual indexes, you consume a lot of CPU, memory on the algorithm's cache, sort, and merge. Multiple separate indexes should beMerged into a multicolumn index. Using explain to check SQL statements, if you find an index merge problem, you should modify the SQL statement to select the appropriate index column order. High-selectivity column forward H. Overwrite index: If an index contains all the field values to query (third star in Samsung system), less disk IO operations, improve query performance I. The index cannot be too many: the more indexes will cause the table to be updated slower, because in addition to updating the data, the index J is updated: MySQL uses index <,<=,=,>,>=,between,in only for the following operators, and sometimes like (cases that do not start with a wildcard character of% or _). In theory, you can create up to 16 indexes per table, but it's not so much fun to use indexes unless you have a lot of data. Nonclustered indexes and. Clustered index (&NBSP;&NBSP;&NBSP;&NBSP;8)
Nonclustered Indexes, similar to the appendix to the book, in which section the jargon appears, which are in order, but where there is no order. Each table can have only one clustered index, because records in one table can be stored in only one physical order. However, a table can have more than one non-clustered index. MyISAM is a non-clustered index, and the data on the leaf node of B+tree is not the data itself, but the address where it resides. There is no difference between the primary and secondary indexes, except that the key in the main index must be unique. The indexes here are non-clustered indexes. Non-clustered index two B + trees look nothing different, the structure of the node is exactly the same as the content of the storage, the primary key index B + Tree node stores the primary key, secondary key index B + Tree stores the secondary key. Table data is stored in a separate place, the leaf nodes of the two B + trees use an address to point to the real table data, and there is no difference between the two keys for table data. Because the index tree is independent, retrieve the index tree without accessing the primary key through the secondary key. The InnoDB data file itself is the index file, the data on the leaf node of the b+tree is the database itself, key is the key, which is the clustered index. Clustered index, the data on the leaf node is the primary key (so the clustered index key cannot be too long).
Clustered IndexThe physical order of the data is consistent with the index order, that is, as long as the index is contiguous, the corresponding data must also be stored adjacent to the disk. Clustered indexes are much more efficient than non-clustered index queries. Clustered index The benefit of this primary + secondary index is that the secondary index tree does not need to be updated when data row movement or page splitting occurs because the secondary index tree stores the primary key keyword for the primary index, not the physical address of the data.
InnoDB is a clustered index, the primary key is organized into a B + tree, and the row data is stored on the leaf node, if you use the "Where id = 14" condition to find the primary key, then according to the B + Tree search algorithm can find the corresponding leaf node, and then obtain the row data. A conditional search on the Name column requires two steps: The first step is to retrieve the name in the secondary index B + tree and reach its leaf node for the corresponding primary key. The second step uses the primary key to perform a B + tree retrieval operation at the main index B + species, and finally reaches the leaf node to get the entire row of data.
9.mysql Index and page relationships
Indexes are often stored as index files on a disk, and the structure of the index is organized to minimize the number of disk I/O accesses during the lookup process. To achieve this, the disk reads on demand and requires that the length of the read-ahead is usually an integer multiple of the page. and the database system sets the size of a node equal to one page, so that each node can be fully loaded with only one I/O. Each time you create a new node, request a page space directly, so that a node is physically stored in a page, and the computer storage allocation is page-aligned, the implementation of a node only one time I/O. And the B-tree in the M-value is very large, it will let the height of the tree down, in favor of a full load. Memory management contents refer to operating system memory management
MySQL Index Introduction
