Shulou(Shulou.com)05/31 Report--

This article mainly introduces the reasons for the use of MySQL index, the article is very detailed, has a certain reference value, interested friends must read it!

There are two ways for a database system to access data:

(1) sequential access

Sequential access is to perform a full table scan in the table, traversing row by row from beginning to end, until the qualified target data is found in the unordered row data. Sequential access is relatively simple to implement, but it is very inefficient when there is a large amount of data in the table.

(2) Index access

Index access is a way to directly access the rows of records in a table by traversing the index. The premise of using this method is to establish an index on the table, and after the index is created on the column, the location of the corresponding record row can be found directly according to the index on the column, so that the data can be found quickly. The index stores pointers to the specified column data values, which are sorted according to the specified sort order. Through the index, when querying data, you do not have to read all the information of the record, but only query the index column. Otherwise, the database system will read all the information from each record to match. Take the catalogue page (index) of the Chinese dictionary for example, we can quickly find the needed words according to the catalogue (index) sorted by pinyin, stroke, partial radical and so on. If there is no index, the database system will compare the internal libraries one by one when querying. Therefore, the use of 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:

It can be retrieved quickly, reducing the number of Istroke O and speeding up the retrieval speed, and grouping and sorting according to the index can speed up the grouping and sorting.

Disadvantages:

The index itself is also a table, so it takes up storage space, generally speaking, the space occupied by the index table is 1.5 times that of the data table, and the maintenance and creation of the index table takes time cost, which increases with the increase of the amount of data. building an index reduces the efficiency of data table modification operations (delete, add, modify), because you need to modify the index table while modifying the data table

These are all the contents of this article entitled "reasons for using MySQL Index". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.