Knowledge points about indexing

What is an index?

The index is a data structure that allows the storage engine to quickly find records. It can also be said to be a directory of data. The index file is saved in B-Tree format

What is the role of index?

1. Indexes can speed up data retrieval, as well as data grouping and sorting;
2. Indexes can ensure the uniqueness of data
3. Indexes can speed up relational tables The connection speed

Disadvantages of indexing?

1. The creation and maintenance of indexes takes time, and increases with the increase of index volume.
2. The creation of indexes requires physical space.
3. The data in the table increases When deleting and modifying, the index also needs to be dynamically maintained

Why can the query speed be improved?

Mainly because the efficiency of sequential search is lower than that of B tree structure search (this needs to be understood in detail)

Index type?

1. Primary key index
primark key It is a special unique index that does not allow null values. Generally, the primary key index is created at the same time when creating the table. Note: A table can only have one primary key.
2. Unique index
The keyword unique for unique index setting The value of the unique index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique.
3. Ordinary index
This is the most basic index, it has no restrictions.
4. Composite index
That is, an index contains multiple columns, mostly used to avoid table query
5. Full-text index

1. -- 1. Add PRIMARY KEY (primary key index)

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) ;

2. -- 2. Add UNIQUE (unique index)

ALTER TABLE `table_name` ADD UNIQUE (`column`);

3. -- 3. Add INDEX (normal index)

ALTER TABLE `table_name` ADD INDEX index_name (`column`);

4. -- 4. Add FULLTEXT (full-text index)

ALTER TABLE `table_name` ADD FULLTEXT (`column`);

5. -- 5. Add multi-column index

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

The index cannot be modified once it is created. If you want to modify the index, you can only delete and rebuild it. . You can use DROP INDEX index_name ON table_name; to delete an index.

Key points

Why do we need a combined index on the basis of a single column index?

Because mysql will only use one of the most efficient indexes without creating a combined index, and the efficiency is mediocre

What is the leftmost principle of combined index?

The combined index usernname, city, age is actually equivalent to establishing the following three sets of combined indexes respectively. :
1.usernname,city,age; 2.usernname,city; 3.usernname
Why is there no combined index like city and age? This is a result of the "leftmost prefix" of the MySQL composite index. The simple understanding is to only start the combination from the leftmost one. Not only queries containing these three columns will use this combined index, the following SQL will use this combined index:

SELECT * FROM mytable WHREE username="admin" AND city="Zhengzhou "SELECT * FROM mytable WHREE username="admin"

The following ones will not be used:

SELECT * FROM mytable WHREE age=20 AND city="Zhengzhou"SELECT * FROM mytable WHREE city="Zhengzhou"

Principles for creating indexes:

1. Leftest principle

2. It is best not to create indexes for tables with small cardinality

3. Try to choose columns with high differentiation as indexes

4. Try to do your best Extended index, do not create a new index.

5. Create indexes for fields that often need to be used as query conditions, sorting, grouping and joint operations

6. Limit the number of indexes and do not blindly create indexes

7. Try to use indexes with a small amount of data and use prefixes for indexes

8. Delete indexes that are no longer used or rarely used

About index optimization:

1. Avoid using calculations on index columns

2. Avoid using not on indexed columns

3. Avoid using is null and is not null on indexed columns

4. Observe The leftmost principle of the index

5. You should try to avoid using != or <> operators in the where clause, otherwise the engine will give up using the index and perform a full table scan

6. Fuzzy queries cannot be preceded by a percent sign, otherwise it will result in a full table scan

7. Using or in the where clause to connect conditions will cause the engine to give up using the index When performing a full table scan

8. If the column type is a string, the data must be quoted in quotation marks in the condition, otherwise the index will not be used

The above is the detailed content of Knowledge points about indexing. For more information, please follow other related articles on the PHP Chinese website!