MySQL Index knowledge point Rollup

1. What is a database index?

A: An index is a data structure that sorts the values of one or more columns in a database. Emphasis: A data structure that sorts the values of a column.

Use indexes to quickly access records in a database

2. What is the main purpose of the index?

A: indexes are used when executing a SELECT statement , and indexes are used primarily to improve query performance . Because the index is optimized by some algorithm, the number of lookups is much less.

Another primary use of the index is in sorting.

3. How is the index executed?

A: The index file produces an intermediate result set, and then queries the table for specific records based on the intermediate result set.

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. in the absence of any record of the scanned data file , MySQL correctly finds the target record of the search!

4. How do SQL query statements execute without using an index?

A: If you do not have an index, you must traverse the entire table. For example such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. because the index is optimized by some algorithm, the number of lookups is much less . It is visible that the index is used for positioning.

5. Is the order of the records in the index consistent with the order of physical records in the table?

A: Of course not, because the index sorts the field values and the index is stored with a special optimization algorithm. But the clustered index is different, and the order of the records in the clustered index is consistent with the order of the records in the physical table. Clustered indexes provide faster access to data than nonclustered indexes.

Note, however, that there can be only one clustered index in a table.

6. How is the index stored?

A: The index is stored with the tables in the database. stored in the database. and the index using binary tree algorithm storage, convenient query speed quickly.

7. What are the types of indexes?

A: You can add three indexes, primary key index, unique index, and clustered index in the database.

1) Unique index unique index is an index that does not allow any two rows to have the same index value. When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

2) primary key index database tables often have one or more column combinations whose values uniquely identify each row in the table. This column is called the primary key of the table. (Multiple columns can also be used as primary keys), and primary key indexes are specific types of unique indexes. Because the unique index requires that the values of the indexed columns not be the same, and the primary key is not the same, the primary key is used to distinguish between different records, which of course cannot be duplicated. Therefore, the primary key index is a special form of the unique index.

The primary key index is automatically established by the database, and the primary key index is automatically created by the database as long as the primary key is specified.

3) The physical order of the rows in the table in the clustered index is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.

8. Is there a difference between single-row indexes and combined index execution efficiency?

A: If you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age? The answer is no, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

9. What are the benefits of a composite index? What is the leftmost prefix?

For:

A multi-column index has another advantage, which is manifested by the concept of the leftmost prefix (leftmost prefixing). Continuing to consider the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

8. How do I select an indexed column? Focus

A: 1) The index is usually established on the Where and join judgment fields. In order to facilitate the query speed, the index is used on the judging condition column to conveniently locate the record quickly.

2) You can create indexes in one or more columns.

If you frequently search two or more columns or sort by two or more columns at the same time, building a composite index can greatly improve query speed. For example, if you frequently set criteria for a first and last name in the same query, it would make sense to create a multicolumn index on those two columns.

3) often used in connected columns, these columns are mainly foreign keys, can speed up the connection;

4) Create an index on a column that often needs to be searched by Scope , because the index is sorted and its specified range is continuous ;

9. Which fields are not suitable for indexing?

A: First, columns that are seldom used in a query should not create an index.

Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has little value and is not conducive to the use of indexes.

The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced.

9. What is the return result of the index?

A: The return result of the index is an "intermediate result set", and the database finds the records of the specific tables in the database based on the intermediate result set.

10. Which index file is selected for the specific query?

A: Only one index per query can be used, and the default database chooses the most restrictive index.

11. How do I determine if an index is used? and view the usage performance of indexes?

Answer: Use the explain command

Explainselect * from Mytablewhere category_id=1 and user_id=2; This was what Postgres 7.1 returns (Exactlyasi expected) notice:query Plan:index Scan using Mytable_categoryid_userid Onmyta BLE (cost=0.00..2.02 Rows=1 width=16) Explain above is the data of postgres, you can see that the database uses an index (a good start) when querying, and it uses the second index created. See the benefits of naming above, and immediately know that it uses the appropriate index. 12. How does the sorting grouping work with the index? A: Because the index is automatically sorted by default, when using sorting or grouping, it is very fast to query with indexes, resulting in a sorted or grouped intermediate result set, and then locating the records in a specific table based on the intermediate result set. SELECT * from Mytablewhere category_id=1 and user_id=2order by Adddate DESC; as simple as creating an index for the field in the WHERE clause, the order Create an index on the field in the By clause: CREATE INDEX mytable_categoryid_userid_adddate on mytable (category_id,user_id,adddate); Note: "MyTable _categoryid_userid_adddate "will be truncated to" Mytable_categoryid_userid_addda "

MySQL Index knowledge point Rollup