Shulou(Shulou.com)06/01 Report--

This paper takes MySQL database as the research object and discusses some topics related to database index. In particular, MySQL supports many storage engines, and various storage engines support different indexes, so MySQL database supports a variety of index types, such as BTree index, hash index, full-text index and so on. To avoid confusion, this article will only focus on BTree indexes, because this is the main index you normally deal with when using MySQL. Hash indexes and full-text indexes will not be discussed in this article.

The main content of the article is divided into three parts.

The first part mainly discusses the mathematical basis of MySQL database index from the aspects of data structure and algorithm theory.

The second part discusses the topics such as clustered index, nonclustered index and overlay index based on the architecture of index in MyISAM and InnoDB data storage engine in MySQL database.

According to the above theoretical basis, the third part discusses the strategy of using index with high performance in MySQL.

The essence of the basic Index of data structure and algorithm

MySQL officially defines index as: index (Index) is a data structure that helps MySQL to obtain data efficiently. Extract the trunk of the sentence and you can get the essence of the index: the index is the data structure.

We know that database query is one of the most important functions of the database. We all want to query data as fast as possible, so the designer of the database system will optimize it from the point of view of query algorithm. Of course, the most basic query algorithm is sequential search (linear search). This algorithm with O (n) complexity is obviously bad when there is a large amount of data. fortunately, the development of computer science provides many better search algorithms, such as binary search (binary search), binary tree search (binary tree search) and so on. If you analyze it a little bit, you will find that each search algorithm can only be applied to a specific data structure, for example, binary search requires that the retrieved data is ordered, while binary tree search can only be applied to binary search trees. but the organizational structure of the data itself cannot fully satisfy a variety of data structures (for example, it is theoretically impossible to organize both columns sequentially at the same time), so outside the data The database system also maintains data structures that meet specific lookup algorithms, which refer to (point to) data in some way, so that advanced lookup algorithms can be implemented on these data structures. This kind of data structure is the index.

Http://gyjkyy.51sole.com/

Http://gyjk.b2b168.com/

Http://www.wenbing.cn/gyjk

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.