Advanced knowledge points of MySQL (II)

3, Index optimization

1. What is an index

• MySQL's official definition of Index is: Index is a data structure that helps MySQL obtain data efficiently. We can get the essence of Index: Index is a data structure.

  It can be simply understood as: ordered fast search data structure

• In addition to data, the database system also maintains data structures that meet specific search algorithms. These data structures refer to (point to) data in some way, so that advanced search algorithms can be realized on these data structures. This data structure is the index. The following figure is an example of a possible indexing method:

• On the left is the data table, which has two columns and seven records. On the far left is the physical address of the data record. In order to speed up the search of Col2, a binary search tree shown on the right can be maintained. Each node contains an index key value and a pointer to the physical address of the corresponding data record. In this way, the binary search can be used to obtain the corresponding data within a certain complexity, so as to quickly retrieve the qualified records
• Generally speaking, the index itself is also very large, and it is impossible to store it all in memory. Therefore, the index is often stored on the disk in the form of index file

2. Advantages and disadvantages of index

advantage

• Improve the efficiency of data retrieval and reduce the IO cost of database
• Sorting data by index column reduces the cost of data sorting and CPU consumption

shortcoming

• Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE. When updating a table, MySQL should not only save the data, but also save the index file. Every time the field with index column is updated, the index information after the key value changes caused by the UPDATE will be adjusted
• In fact, the index is also a table, which saves the primary key and index fields and points to the records of the entity table, so the index column also takes up space

3. Classification of indexes

Basic grammar

• establish

  CREATE [UNIQUE] INDEX [indexName] ON table_name(column);
  

• delete

  DROP INDEX [indexName] ON table_name;
  

• see

  SHOW INDEX FROM table_name;
  

classification

• Single valued index

  • Definition: that is, an index contains only a single column, and a table can have multiple single column indexes

  • Syntax:

    --Create with table
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name) --Single valued index
    );
    
    --Create single valued index separately
    CREATE INDEX idx_customer_name ON customer(customer_name);
    

• unique index

  • Definition: the value of index column must be unique, but null value is allowed

  • Syntax:

    --Create with table
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name), --Single valued index
    UNIQUE (customer_no) --unique index
    );
    
    --Create unique index separately
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
    

• primary key

  • Definition: when set as the primary key, the database will automatically establish an index, and innodb is a clustered index

  • Syntax:

    --Create with table
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id) --primary key 
    );
    
    --Create primary key index separately
    ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);
    
    --Delete primary key index
    ALTER TABLE customer DROP PRIMARY KEY;
    
    --Modify and build primary key index
     You must delete it first(drop)Original index, and then new(add)Indexes
    

• Composite index

  • Definition: that is, an index contains multiple columns

  • Syntax:

    --Create with table
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name), --Single valued index
    UNIQUE (customer_no), --unique index
    KEY (customer_no,customer_name) --Composite index
    );
    
    --Create composite index separately
    CREATE INDEX idx_no_name ON customer(customer_no,customer_name);