Summary of Common Problems with Mysql Indexes

Q1: What indexes does the database have? What are the advantages and disadvantages?

1. B-tree index: the index used by most databases (innoDB uses b+ tree). It can speed up the access to data, especially the search of range data. The disadvantage is that you can only search from the leftmost column of the index, and you cannot skip columns in the index. If a column in the query uses a range query, all columns on the right cannot use index optimization to search.

2. Hash index: implemented based on hash table. In MySQL, only the Memory engine explicitly supports hash searches. Hash lookups are very fast, but hash indexes only contain hash values ​​and row pointers and do not store field values, so the values ​​in the index cannot be used to avoid reading rows or perform sorting. Because hash indexes use the entire contents of the indexed columns to calculate hash values, they do not support partial-column matching searches. Hashes only support equality comparisons and do not support any range queries. Once there are many hash conflicts, the maintenance cost will be very high. InnoDB supports "adaptive hash index".

3. Full-text index: Full-text index is a special type of index that searches for keywords in the text instead of comparing index values. Initially, it could only be used on MyISAM, but after 5.6.24, innoDB also supported full-text indexing. Full-text index queries use Match....against. There will be no conflict when creating full-text search and value-based B-Tree indexes on the same column at the same time.

4. Spatial data index (R-tree index). MyISAM supports R-tree index. The advantage is that it does not require prefix query and indexes data from all latitudes, so it can be used to store geographic data. The disadvantage is that you must use MySQL's GIS-related functions such as MBRCONTAINS() to maintain data. However, since GIS in MySQL is not perfect, most people will not use this feature.

Q2: Why don’t you use binary search trees or red-black trees as database indexes?

When a binary tree processes massive amounts of data, the tree height is too high. Although the indexing efficiency is very high, reaching logN, a large amount of disk IO will be performed, which is not worth the cost. Moreover, deleting or inserting data may cause the data structure to change into a linked list, which requires an improved balancing algorithm. However, when inserting and deleting elements in a red-black tree, the color will change and the rotation (left rotation, right rotation) will be performed frequently, which is a waste of time. However, when the amount of data is small, it can be put into a red-black tree. At this time, the time complexity of the red-black tree is lower than that of the B-tree. Therefore, considering the above, the database finally chose b-tree as the index.

Q3: Application scenarios of B tree and B+ tree:

1. B-trees are commonly used in file systems and a few database indexes, such as mongoDB.

2.B+ tree is mainly used for MySQL database index.

Q4: Advantages of B+ tree vs. B tree

In addition to storing indexes pointing to child nodes, each node of the B-tree also stores data fields. Therefore, a single node does not have many indexes pointing to child nodes, the tree is high, and the number of disk IO times is high. The height of the B+ tree is lower, and all data is stored in leaf nodes, which are all on the same level. Therefore, the query performance is stable and range search is convenient.

Q5: Pitfalls of using indexes when sorting multiple columns

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values ​​are always stored in ascending order.

According to the MySQL documentation, you can add asc or desc when creating an index, for example: add index idx(a asc,b desc) . However, MySQL will actually ignore it (what a pitfall...). It seems that desc is supported after version 8.0.

What impact will this have?

Suppose there are columns test1 and test2, both of type int.

We create the index ``idx1(test1,test2),
Suppose we want to sort by test1 and test2, for example, SQL

explain select * from table order by test1 ,test2 limit 1;

You can use index sorting:

• order by test1
• order by test1 desc
• order by test1,test2
• order by test1 desc,test2 desc
  

Sorting by index cannot be used:

• order by test1,test2,desc
• order by test1 desc,test2
  

Because the index does not support desc, the multi-column index is stored in ascending order of all columns. So if you only sort one column, all columns in ascending order, or all columns in descending order, you can use the index. However, if you use ascending order for the first column and descending order for the second column, or descending order for the first column and ascending order for the second column, you cannot use the index.

The above is the detailed summary of common problems with MySQL indexes. For more information about MySQL indexes, please pay attention to other related articles on 123WORDPRESS.COM!