VII. mysql index selection and mysql Index
VII. mysql index selection
1. A single myisam, bdb, innodb, and memory table supports at least 16 indexes. create index id_index on emp (id) creates an index for the id field id_index for the emp table. drop index id_index on emp Delete the id_index index of the emp table 4. explain xxxxxxx, which can be used to view related execution results and optimize the table. the most suitable index column is the "condition column" instead of "select column", for example, select name from emp where year> = 2014. It is best to create an index on the year field, instead of name field 6. if you use an index field, it is best to have a unique field value. Because the more unique the field value is, the more meaningful the index is. b-tree indexes are accessed through indexes, while hash indexes scan the entire table.
MySQL database optimization (7): How does MySQL use index indexes to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If the field has an index, MySQL can quickly determine the location where the data file is located to search for records without searching all the data. If the table contains 1000 records, this is at least 100 times faster than reading data sequentially. Note: If you need to access almost all 1000 records, sequential reading will be faster, because this will minimize the number of disk searches.
Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored as B trees. Only Spatial fields are stored in the R tree. MEMORY (HEAP) tables support hash indexes.
By default, strings are automatically compressed with spaces in the prefix and suffix.
Generally, indexes can be used in the following situations. The unique feature of hash indexes (for MEMORY tables) will be discussed later.
Find the record that matches the WHERE clause as soon as possible.
Exclude records based on conditions. If multiple indexes are available, MySQL usually selects the index with the least records.
Query records from other tables during table join queries.
You want to find its MIN () or MAX () value on the specified index field key_col. The optimizer will check the index
Before the key_col field, check whether the WHERE key_part _ # = constant clause is used in other indexes. In this case,
MySQL performs an index search separately for MIN () or MAX () expressions and replaces them with constants. When all expressions are replaced with constants, the query returns immediately. As follows:
Select min (key_part2), MAX (key_part2) FROM tbl_name WHERE key_part1 = 10;
Sort or group a table. When grouping or sorting an available leftmost prefix index (such as ORDER
BY key_part1, key_part2 ). If all the indexes are sorted by DESC, the indexes are sorted in reverse order.
In some cases, the query can be optimized so that results can be directly obtained without computing data. When the query uses a numeric field in the table and the field is the leftmost part of the index, the results may be obtained quickly from the index tree:
SELECTkey_part3FROMtbl_nameWHEREkey_part1 = 1
Assume that the SELECT statement is as follows:
If there is a multi-field index on col1 and col2, the corresponding records can be obtained directly.
MySQL database optimization (7): How does MySQL use index indexes to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If the field has an index, MySQL can quickly determine the location where the data file is located to search for records without searching all the data. If the table contains 1000 records, this is at least 100 times faster than reading data sequentially. Note: If you need to access almost all 1000 records, sequential reading will be faster, because this will minimize the number of disk searches.
Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored as B trees. Only Spatial fields are stored in the R tree. MEMORY (HEAP) tables support hash indexes.
By default, strings are automatically compressed with spaces in the prefix and suffix.
Generally, indexes can be used in the following situations. The unique feature of hash indexes (for MEMORY tables) will be discussed later.
Find the record that matches the WHERE clause as soon as possible.
Exclude records based on conditions. If multiple indexes are available, MySQL usually selects the index with the least records.
Query records from other tables during table join queries.
You want to find its MIN () or MAX () value on the specified index field key_col. The optimizer will check the index
Before the key_col field, check whether the WHERE key_part _ # = constant clause is used in other indexes. In this case,
MySQL performs an index search separately for MIN () or MAX () expressions and replaces them with constants. When all expressions are replaced with constants, the query returns immediately. As follows:
Select min (key_part2), MAX (key_part2) FROM tbl_name WHERE key_part1 = 10;
Sort or group a table. When grouping or sorting an available leftmost prefix index (such as ORDER
BY key_part1, key_part2 ). If all the indexes are sorted by DESC, the indexes are sorted in reverse order.
In some cases, the query can be optimized so that results can be directly obtained without computing data. When the query uses a numeric field in the table and the field is the leftmost part of the index, the results may be obtained quickly from the index tree:
SELECTkey_part3FROMtbl_nameWHEREkey_part1 = 1
Assume that the SELECT statement is as follows:
If there is a multi-field index on col1 and col2, the corresponding records can be obtained directly. If there are independent indexes in col1 and col2, the optimizer will first find the index with the most limit, and then decide which index to use based on which index can find fewer records.
