Stay tuned for a series of articles indexed to the database

Preface – Several reasons for learning index

  • High salary programmer essential knowledge, no matter where to interview, database index optimization is required knowledge
  • Work necessary, no matter any system must deal with the database, when the amount of data reaches millions of levels above, the query speed will be slow, affect the database concurrency, thus affecting the overall system concurrency
  • Not database index programmer, not a qualified programmer, so quickly to learn index bar, ha ha

What is the index

MySQL officially defines an Index as: an Index is a data structure that helps MySQL to obtain data.

Can get the essence of index: index is a data structure, the purpose of index is to improve the efficiency of query, can be analogous to the English Xinhua dictionary, if we want to query the word MySQL, first we need to locate the directory (index) to M, and then locate to Y, and so on to find SQL.

If there is no index, then we need to go from A to Z to search again, until we find what we need, one by one and directly according to the directory to locate the data, is not A big difference, this is the clever use of index.

Index the underlying data structure

When the amount of data is large, the amount of index data is also large. Therefore, indexes cannot be stored in the memory. Therefore, indexes are generally stored on hard disks in the form of files.

In addition to the data itself, the database maintains a data structure that satisfies a specific lookup algorithm, and that points to the data in a way that allows you to implement advanced lookup algorithms based on those data structures.

 

Types of indexing algorithms

  • B-tree index B-tree index
  • A Hash index
  • Full – text index
  • R – tree indexes

Advantages of indexes

  • Similar to university library bibliographic index, improve data retrieval efficiency, reduce database IO cost
  • Sorting data through indexed columns reduces the cost of sorting data and CPU consumption

Disadvantages of indexes

  • The index is actually a table that holds the primary key and index fields and points to the records of the entity table, so the index columns also take up space
  • While indexes greatly speed up queries, they also slow down the speed of updating tables for INSERTS, updates, and deletes. MySQL does not only save the index file when updating the table, but also changes the index file when updating the index column
  • Indexes are only one factor in improving efficiency. If you have MySQL with large tables, you may need to spend time on building good indexes or optimizing queries

The index classification

  • Single-value index: that is, an index contains only a single column. A table can have multiple single-column indexes
  • Unique index: The value of the indexed column must be unique, but empty values are allowed
  • Compound index: That is, an index contains multiple columns

The index of grammar

  • Create 1: create [unique] index indexName on tableName (columnName (length)).Copy the code

For CHAR,VARCHAR, length can be smaller than the actual field length. If it is

BLOB and TEXT types, length must be specified.

  • Alter tableName add [unique] index [indexName] on (columnName (length))Copy the code

 

  • DROP INDEX [indexName] ON mytable;Copy the code

 

  • Check: SHOW INDEX FROM table_name\GCopy the code

 

Which cases need to be indexed

  • The primary key automatically creates a unique index
  • Fields that are frequently the condition of a query should be indexed
  • The foreign key relationship is used to index the fields associated with other tables in the query
  • Fields that update frequently are not suitable for creating indexes: each update not only updates the record but also updates the index, increasing the IO burden
  • Indexes are not created for fields that are not needed in the Where condition
  • Single/composite index selection issues (tendency to create composite indexes under high concurrency)
  • The sorted fields in a query can be accessed through an index, which greatly improves the sorting speed
  • Statistics or group fields in the query

What is not appropriate for indexing

  • Too few table records
  • A watch that is often added, deleted, or modified
  • Table fields with repetitive and evenly distributed data, so you should index only frequently queried and frequently ordered data columns. Note that if a data column contains a lot of duplicate content, there is not much practical value in indexing it.

Today, the index will be introduced here, and then the article will focus on index optimization

Stay tuned!!

Follow wechat public account: IT elder brother

Java Foundation, Java Web, JavaEE, including Spring Boot, etc

Reply: Resume template, you can get 100 beautiful resumes

Reply: Java learning route, you can get the latest and most complete a learning roadmap

Re: Java ebooks, get 13 must-read books for top programmers