Daily Study Notes (15) _ MySQL

Daily Study Notes (15) today I will continue to read the index section in Chapter 1 of MySQL Technology Insider InnoDB storage engine.

When should I use B + tree indexes? Not all columns that appear under all query conditions need to be indexed. The author points out that when a few rows in the table are accessed, using B + tree indexes makes sense. For fields such as gender, region, and type, their optional values are small, that is, low selectivity. for example, Select * from student where sex = 'M ', the result may be 50% of the table's data. adding a B + tree index is completely unnecessary. On the contrary, if a field has a wide range of values and has almost no repeatability, it is highly selective, in this case, using the B + tree index is the most suitable, for example, the name field.

Therefore, when you access a highly selective field and retrieve a small portion of the data from the table, it is necessary to add a B + tree index to this field. However, if the access field is highly selective, but the retrieved row data occupies most of the data in the table, MySQL will not use the B + tree index. The MySQL Optimizer predicts the possible rows to be queried through the EXPLAIN rows field. if the value is greater than a certain value, the B + tree will scan the entire table. As for this value, the author estimates that it is generally 20%, that is, if the retrieved data volume exceeds 20% of the data in the table, the optimizer will not use the index, but scan the entire table. However, sometimes the optimizer is not completely correct, and you can use the index forcibly.

Author: Dongting Sangren

Source: http://phinecos.cnblogs.com/

This blog follows Creative Commons Attribution 3.0 LicenseIf it is used for non-commercial purposes, you can reprint it freely, but please keep the original author information and the article URL.