MySQL Advanced Introduction to Indexes (Summary Sharing)

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about indexes, including the advantages and disadvantages of indexes, classification of indexes, index structure and indexing Let's take a look at the creation and other issues below. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

What is index

MySQL official definition of index: Index (Index) is a data structure that helps MySQL obtain data efficiently.
The essence of index: Index is a data structure.
Purpose of index: Improve query efficiency, which can be compared to a dictionary.

Note: You can simply understand the index as "sorted fast search data structure". Generally speaking, the index itself is also very large, and it is impossible to store it all in the memory. Therefore, the index is often stored on the disk in the form of an index file.

What we usually call the index, unless otherwise specified, They all refer to the index organized by the B-tree (multi-way search tree, not necessarily binary) structure. Among them, clustered index, compound index, prefix index and unique index all use B-tree index by default, collectively referred to as index.

Advantages and Disadvantages of Indexing

Advantages

Similar to how university libraries build bibliographic indexes to improve data retrieval Efficiency, reduce the IO cost of the database

Sort data through index columns, reduce the cost of data sorting, and reduce CPU consumption

Disadvantages

The index is actually a table that saves the primary key and index fields and points to the records of the entity table, so the index columns will also take up space

Although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file. Every time a field that adds an index column is updated, the index information after the key value changes caused by the update will be adjusted

Indexes are only one factor to improve efficiency. If your MySQL has a large amount of data tables, you need to spend time researching and building the best indexes, or optimizing queries

MySQL index classification

Single value index

Definition: An index only contains a single column, and a table can have multiple Single column index

Note:Generally, a table should not have more than 5 indexes at most

Unique index

Definition: The value of the index column must be unique, but null values ​​are allowed

Compound index

Definition: An index contains multiple columns

Basic syntax

• Create

CREATE [UNIQUE] INDEX indexName ON mytable(columnnname(length));ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

• Delete

 DROP INDEX [indexName] ON mytable;

• View

SHOW INDEX FROM table_name\G

• Use ALTER command

There are four ways to add the index of the data table:

ALTER TABLE tbl_name ADD PRIMARY KEY ( column_list): This statement adds a primary key, which means that the index value must be unique and cannot be NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list): The value of the index created by this statement must be unique (except NULL, NULL may appear multiple times).

ALTER TABLE tbl_name ADD INDEX index_name(column_list): Add a normal index, the index value can appear multiple times.

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list): This statement specifies the index as FULLTEXT, which is used for full-text indexing.

Note: [PRIMARY | UNIQUE | FULLTEXT]: The type of index, indicating unique index and full-text index respectively. When creating a data table, the index type can be omitted.
[INDEX | KEY]: Indicates specifying to create an index in the data table.

MySQL index structure

• BTree index

• Hash index

• full_textFull text index

• ##R-Tree index

Creation of index

In which cases it is necessary to create an index

• Primary key automatic Create a unique index

• Fields that are frequently used as query conditions should be indexed

• Fields associated with other tables in the query, foreign key relationships are indexed

• Frequently updated fields are not suitable for creating indexes, because each update not only updates the record but also updates the index

• Used in the Where condition No index will be created for unreachable fields

• Single key/combined index selection issue (combined indexes tend to be created under high concurrency)

• The sorted fields in the query, if the sorted fields pass the index Access will greatly improve the sorting speed

• Statistics or grouping fields in queries

What situations are not suitable for creating indexes

• Too few table records

• Tables that are frequently added, deleted or modified

• The data is duplicated and evenly distributed table fields, so only the most frequently queried and most frequently sorted data columns should be indexed.
  Note that if a data column contains a lot of repeated content, indexing it will not have much practical effect

Recommended learning: mysql video tutorial

The above is the detailed content of MySQL Advanced Introduction to Indexes (Summary Sharing). For more information, please follow other related articles on the PHP Chinese website!