Why use MySQL indexes?

Two ways for the database system to access data:

(1) Sequential access

Sequential access is to perform a full table scan in the table, one by one from beginning to end. The rows are traversed until the target data that meets the conditions is found in the unordered row data. Sequential access is relatively simple to implement, but when there is a large amount of data in the table, the efficiency is very low.

(2) Index access

Index access is a way to directly access the record rows in the table by traversing the index. The premise of using this method is to create an index on the table. After creating the index on the column, when searching for data, you can directly find the location of the corresponding record row based on the index on the column, thereby quickly finding the data. The index stores pointers to the data values ​​of the specified columns, sorting these pointers according to the specified sort order. Through the index, you do n’t need to read all the information recorded when you query the data, but just query the index column. Otherwise, the database system will read all information of each record for matching. Take the directory page (index) of a Chinese dictionary as an example. We can quickly find the required words in the directory (index) sorted by pinyin, strokes, radicals, etc. If there is no index, the database system will query the internal library. One-to-one comparison is performed. Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

Advantages and disadvantages of index access:

Advantages:

Can be retrieved quickly and reduce I/O times to speed up retrieval; grouping and sorting according to the index can speed up grouping and sorting;

Disadvantages:

The index itself is also a table, so it will Occupying storage space, generally speaking, the space occupied by the index table is 1.5 times that of the data table; the maintenance and creation of the index table requires time costs, and this cost increases with the increase in the amount of data; building the index will reduce the modification of the data table The efficiency of operations (delete, add, modify), because when modifying the data table, you also need to modify the index table;

Recommendation: mysql tutorial

The above is the detailed content of Why use MySQL indexes?. For more information, please follow other related articles on the PHP Chinese website!