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

This article shows you how to optimize the Mysql index, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Click "programmer Xiaoming" above and select "Star"

You can not work overtime tonight!

History of Mysql

A brief review of the history of Mysql shows that Mysql is a relational database management system developed by Mysql AB in Sweden and currently belongs to Oracle. Relational databases store data in different tables instead of all data in a large warehouse, which increases speed and flexibility.

Advantages of Mysql

Mysql is open source, so you don't have to pay extra.

Mysql supports large databases. It can handle large databases with tens of millions of records.

Mysql uses the standard SQL data language form.

Mysql can run on multiple systems and supports multiple languages.

Mysql can be customized, using the GPL protocol, you can modify the source code to develop your own Mysql system.

Storage classification of index

Indexing is implemented in the storage engine layer of MYSQL, not in the service layer. So the indexes of each storage engine are not necessarily the same, and not all storage engines support all index types. MYSQL currently provides the following four indexes.

Normal General Index

The basic index type can be used in most cases.

Unique unique index

Indicates a unique index that does not allow duplicates, and can be set to unique if the field information is guaranteed not to be duplicated, such as when the ID number is used as an index. Unique and Primary Key provide a guarantee of uniqueness for columns or sets of columns. Primary Key has automatically defined Unique constraints, but there can be multiple Unique constraints in each table, but only one Primary Key constraint.

Full Text full-text index

Full-text indexes can be created on columns of type varchar, char, and text. MyISAM supports full-text indexing, while InnoDB supports full-text indexing after mysql5.6. FULLTEXT works best when searching for a long article. Used in short text, if only one or two lines of words, ordinary INDEX can also be used.

SPATIAL spatial index

Spatial index is an index established on the fields of spatial data types. There are four spatial data types in MYSQL, namely GEOMETRY (geometry), POINT (point), LINESTRING (line) and POLYGON (polygon). MYSQL is extended with the SPATIAL keyword to enable the syntax used to create regular index types to create spatial indexes. The column that creates the spatial index must be declared as NOT NULL, and the spatial index can only be created in tables where the storage engine is MYISAM.

Some of my views on the index

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.

Database query is one of the most important functions of 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.

When you find that your query is slow, the quickest way to solve the problem is to use an index. The use of index is an important factor that affects the speed of query. Other actions to optimize the query before using the index are simply a waste of time, and only after the index is used reasonably, it is necessary to consider other optimization methods.

The above content is how to optimize the Mysql index. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are 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.