After that, a series of articles will be published for database index, please look forward to it
Preface several reasons for learning index
- High paid programmers must have knowledge, no matter where to interview, database index optimization is a required knowledge
- Work essential, no matter any system must deal with the database, when the amount of data reaches more than one million, the query speed will slow down, affecting the concurrency of the database, thus affecting the overall system concurrency
- The programmer who can’t index database is not a qualified programmer, so learn index quickly, ha ha
What is the index
The official definition of index in MySQL is: index is a data structure that helps MySQL universities to obtain data. We can get the essence of index: index is a data structure, and the purpose of index is to improve query efficiency. It can be compared with English Xinhua dictionary. If we want to query the word mysql, we need to locate m in the directory (index), then y, and so on to find SQL. If there is no index, then we need to traverse the search from a to Z until we find what we need, one by one, and directly locate the data according to the directory. Is there a big difference? This is the magic of index.
Index underlying data structure
When there is a large amount of data, the index data is also large, so it is impossible to put all the indexes into the memory. Therefore, the indexes are generally stored on the hard disk in the form of files.
In addition to the data itself, the database also maintains a data structure that meets the specific search algorithm. These structures point to the data in a certain way, so that the advanced search algorithm can be implemented based on these data structures.
Types of indexing algorithms
- B-tree index
- Hash index
- Full text index
- R-tree index
The advantages of indexing
- Similar to the bibliographic index of university library, it can improve the efficiency of data retrieval and reduce the IO cost of database
- The index column is used to sort the data to reduce the sorting cost and CPU consumption
Disadvantages of index
- In fact, the index is also a table. The table holds the primary key and index fields, and points to the records of the entity table. Therefore, the index column also takes up space
- Although the index greatly improves the query speed, it will slow down the update speed of the table, if you insert, update and delete the table. When updating a table, MySQL not only does not save data, but also saves the index file. Each time the fields of the index column are added, the index information after the key value changes caused by the update will be adjusted
- Indexing is only a factor to improve efficiency. If your MySQL has large data tables, you need to spend time researching and building excellent indexes or optimizing query statements
- Single value index: that is, an index only contains a single column, and a table can have multiple single column indexes
- Unique index: the value of an index column must be unique, but null values are allowed
- Composite index: an index contains multiple columns
- Create one: create [unique] index indexname on tablename (columnname (length)).
- If it is char, varchar type, length can be less than the actual length of the field; if it is
Blob and text types, length must be specified.
- Create 2: alter tablename add [unique] index [indexname] on (columnname (length))
- Delete: drop index [indexname] on mytable;
- View: Show index from table_ nameG
What situations need to be indexed
- The primary key automatically establishes a unique index
- Fields that are frequently used as criteria for queries should be indexed
- The fields associated with other tables in the query are indexed by foreign key relationship
- Frequently updated fields are not suitable for creating an index: each update not only updates the record, but also updates the index, increasing the IO burden
- The fields not used in the where condition are not indexed
- Selection of single / combined index
- If the sorted fields in the query are accessed through the index, the sorting speed will be greatly improved
- Statistics or grouping fields in query
What is not suitable for indexing
- There are too few records in the table
- Frequently added, deleted and modified tables
- Table fields with duplicate and evenly distributed data, so you should only index data columns that are frequently queried and sorted. Note that if a data column contains a lot of duplicate content, indexing it has little practical effect.
Today’s index first introduces here, and then the article will focus on index optimization