A MySQL index is a structure that sorts the values of one or more columns in a database table, and the use of indexes improves the efficiency of querying specific data in the database.This section describes the meaning, classification and design principles of indexes.
7.1.1 Meaning and characteristics of indexes
An index is a separate, database structure stored on disk that contains pointers to references to all records in a data table. Using an index allows you to quickly find rows that have a particular value in one or more columns, and all MySQL column types can be indexed. Using indexes on related columns is the best way to speed up query operations.
For example, there are 20,000 records in the database, now you want to execute a query "SELECT * FROM table where num=10000", if there is no index, you have to traverse the whole table until the row with num equal to 10000 is found; if you create an index on the num column, MySQL does not need to do any scanning, just look for 10000 inside the index, then you can find out the location of this row. If you create an index on the num column, MySQL does not need any scanning, directly in the index to find 10000, you can learn the location of this line. It can be seen that the establishment of the index can speed up the database query speed.
Indexes are implemented in storage engines, each of which may not be identical, and each of which may not support all index types. The maximum number of indexes per table and the maximum index length are defined according to the storage engine. All storage engines support at least 16 indexes per table with a total index length of at least 256 bytes. Most storage engines have higher limits. there are two types of storage for indexes in MySQL, BTREE and HASH, depending on the storage engine of the table: the MyISAM and InnoDB storage engines only support BTREE indexes; the MEMORY/HEAP storage engine can support both HASH and BTREE indexes.
There are 4 main advantages of indexing:
(1) By creating unique indexes, you can ensure the uniqueness of each row of data in a database table.
(2) It can greatly speed up the querying of data, which is the main reason for creating indexes.
(3) In realizing the referential integrity of data, it can accelerate the connection between tables and tables.
(4) When using grouping and sorting clauses for data queries, the time spent on grouping and sorting in a query can be significantly reduced.
There are also many disadvantages to adding an index, mainly in the following 3 areas:
(1) Creating and maintaining indexes is time consuming and increases as the amount of data increases.
(2) Indexes take up disk space. In addition to the data space occupied by the data tables, each index takes up a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.
(3) When adding, deleting, and modifying data in a table, the index also has to be maintained dynamically, which reduces the speed of data maintenance.
7.1.2 Classification of indexes
MySQL indexes can be categorized as follows.
1. General and unique indexes
Ordinary indexes are the basic index type in MySQL, allowing duplicate and null values to be inserted in the columns where the index is defined.
A unique index requires that the values of the indexed columns must be unique, but null values are allowed. In the case of a combinatorial index, the combination of column values must be unique. Primary key indexes are a special type of unique index that does not allow null values.
2. Single-column and combined indexes
A single-column index is an index that contains only a single column, and a table can have multiple single-column indexes.
Combined indexes are indexes created on a combination of fields in a table. The indexes are used only when the query condition uses the left field of these fields. The use of combined indexes follows the "leftmost prefix" principle.
3. Full text index
Full-text indexes of type FULLTEXT support full-text lookups of values on the columns defined for indexing, allowing duplicate and null values to be inserted in these indexed columns. Full-text indexes can be created on columns of type CHAR, VARCHAR, or TEXT.Full-text indexes are supported only by the MyISAM storage engine in MySQL.
4. Spatial indexing
A spatial index is an index created on a field of a spatial data type, and there are four spatial data types in MySQL, GEOMETRY, POINT, LINESTRING, and POLYGON.MySQL extends this with the SPATIAL keyword, which makes it possible to create spatial indexes with syntax similar to that used to create regular indexes. The columns for which spatial indexes are created must be declared NOT NULL, and spatial indexes can only be created in tables whose storage engine is MyISAM.
7.1.3 Index design principles
Poorly designed indexes or lack of indexes can be a barrier to database and application performance. Efficient indexes are important for good performance. The following guidelines should be considered when designing indexes:
(1) index is not the more the better, a table, such as a large number of indexes, not only occupies disk space, but also affects the performance of INSERT, DELETE, UPDATE and other statements, because the table data changes, the index will also be adjusted and updated.
(2) Avoid over-indexing tables that are frequently updated and have as few columns in the index as possible. Indexes should be created for fields that are frequently used in queries, but avoid adding unnecessary fields.
(3) It is better not to use indexes for tables with small amounts of data. When there is less data, queries may take less time than traversing the index, so indexes may not produce optimization results.
(4) In the conditional expression is often used in the different values of the columns on the establishment of indexes, in the different values of the columns do not build indexes. For example, the student table "gender" field only "male" and "female" two different values, so there is no need to set up an index, if you set up an index, not only will not improve the query efficiency, but will be If you set up an index, it will not improve the query efficiency, but will seriously reduce the data update speed.
(5) Specify unique indexes when uniqueness is a characteristic of some data itself. Ensure data integrity of defined columns when using unique indexes to improve query speed.
(6) Create indexes on columns that are frequently sorted or grouped (i.e., subject to GROUP BY or ORDER BY operations), and if there is more than one column to be sorted, you can create combined indexes on those columns.
————————————————
