Is the more indexes in a table the better?

2022-04-24 0 By

When adding data to an InnoDB table, a cluster index is automatically created based on the primary key.As we keep inserting data into the table, we’re going to keep inserting data into the data page.When a data page is full, it will split into multiple data pages, and then you need index pages to point to each data page.If the data page is too much, then the index page in the data page pointer will be too much, the index page will inevitably be full, so the index page will also split, and then form a higher index page.By default, MySQL creates clustered indexes based on primary key values. The leaf nodes of clustered indexes are data pages, which contain inserted rows of complete data.The records in the data pages and index pages are arranged in a one-way linked list, ordered by data size. The data pages and index pages are arranged in a two-way linked list with each other, ordered by data size. So the B+ index tree is a completely ordered data structure, both within and between pages, so that when you look up data,It is efficient to start directly from the root node and work down by the data value size.MySQL > select * from table where index ();Data can be directly searched according to the index B+ tree of a field, without full table search, which improves performance.But there is a downside: space Each B+ tree takes up a lot of disk space, and too many indexes consume disk space.Index B+ tree (B+ tree) : < span style = "color: RGB (74, 74, 74); color: RGB (74, 74, 74);All values of the next page must be greater than all values of the previous page, so constantly increasing, deleting, changing and checking will inevitably lead to the value size between each data page may be out of order, such as the next data page inserted a relatively small value, unexpectedly smaller than the value of the previous data page!At this point, there is no way, only to move data pages, maintain the order between pages.Time to insert data, the data pages of each index will keep splitting, keep adding new index pages, this process is very time-consuming.So if you make too much index in a table, it is likely to lead to your increase, delete and change the speed of the poor, perhaps the query speed is indeed can improve, but the increase, delete and change will be affected, so do not recommend a table to make too much index!