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)).

• 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))

• DROP INDEX [indexName] ON mytable;

• Check: SHOW INDEX FROM table_name\G