MySQL database optimization (4) - MySQL index optimization

1. Index basics
Index type:
1. Ordinary index: Created into any data type
2. Unique index: restricted index The value must be unique
3. Full-text index: It can only be created on char, varchar, and text type fields. It is mainly used to improve text query speed. MyISAM engine support.
4. Single-column index: Create an index for a single field in the table
5. Multi-column index: Create an index for multiple fields
6. Spatial index: Created using spatial parameters to provide the system with the efficiency of obtaining control data
Basic operations of index:

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)  
             );
             
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
             );
           
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)//多列索引
             );
             
CREATE  INDEX index_userName ON t_user4(userName);--对已经创建的表指定索引


CREATE  UNIQUE INDEX index_userName ON t_user4(userName);


CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);


ALTER TABLE t_user5 ADD INDEX index_userName(userName);--修改索引


ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);


ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);


DROP INDEX index_userName ON t_user5;


DROP INDEX index_userName_password ON t_user5;

Adding an index can speed up query efficiency and avoid full table data query. Instead, use Search the index and find the target data. select t.name from user t where t.id=5, if an index is created on the actor_id column, mysql uses the index to find the row with id=5, that is to say, it first searches by value on the index, and then returns all the rows containing the row of data for the value.
The index can contain one or more columns. If the index contains multiple columns, the order of the columns is also very important, because MySQL can only efficiently use the leftmost prefix column of the index. Moreover, the creation and maintenance of indexes also require system resources, which involves how to create efficient indexes to improve query efficiency.
2. Index type of mysql
Generally speaking, the data index itself is also very large and it is impossible to store it all in the memory. Therefore, the index is often based on the index file. The form is stored on disk. In this case, disk I/O consumption will occur during the index search process, compared with memory access, so the structural organization of the index should minimize the number of disk I/O accesses during the search process. This requires a high-quality data structure to organize and store database indexes.
General index types make use of data structure algorithms. For example, B-Tree is both an index type in mysql and a dynamic search tree: Binary Search Tree, Balanced Binary Search Tree , Red-Black Tree, binary tree structure of B-tree/B+-tree/B*-tree (B~Tree).
Since these MySQL index types are based on the implementation of data structure algorithms, general development begins to be applied on the basis. The applications of indexes created based on different data structures are also different.

3. Common misunderstandings in index use
1. Independent column index
Wrong use : select t.name from user t where t.age+1=5. For this condition age+1=5, mysql cannot automatically parse. Even if an index is created for the age column, mysql is Index query will not be used during query, and full table scan will still be performed.
Incorrect usage: select t.name from user t where TO_DAYS(`CURRENT_DATE`())-TO_DAYS(date_col)<=10; Same as above
Correct principle:Always put the index column on the separate side of the comparison operator.
2. Multi-column index
Common mistakes: Create an independent index without columns, or create multiple columns in the wrong order Index, or simply index all the columns in the where condition.
Correct use: Select the appropriate index order for different index types
For example, select t.name from user t where t.staffId= 2 and custom_id=7;
In response to the above query, should we create a (staffId, custom_id) or reverse the order of these two columns.

Taking B-Tree as an example, the order of the index columns means that the index will be sorted by the leftmost column first, from left to right. It is generally wise to put the fields with the highest frequency of filter conditions at the front of the index. The index designed in this way can filter out the required rows as quickly as possible.

4. Summary

Check out a java cloud platform project I did before, using the ORM framework. At that time, the project experienced slow cascading queries, so I combined JDBC+ORM combined form programming. Now I’m looking at the table designs of several databases, and for index optimization. It still needs to be applied again. Generally, the indexes created only use a single column index and are created using the primary key. There is no big problem with this, but if the query time-consuming problem occurs, table structure optimization, index optimization, and query optimization need to go hand in hand, but Relying on ORM framework re-selection or re-optimization cannot solve the problem.

The above is the content of MySQL database optimization (4) - MySQL index optimization. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!