MySQL Index learning

Indexes are used to quickly find rows that have a specific value in a column.

If the index is not useful, MySQL must start reading the entire table from the first record until the relevant row is found.

The larger the table, the more time it takes to query the data.

If there is an index to the columns queried in the table, MySQL can quickly reach a location to search for data files without having to look at all the data.

Content navigation

Understand what an index is

Mastering the methods and techniques of creating indexes

Familiar with how to delete an index

Mastering the methods and techniques of index creation in a comprehensive case

Familiar with operational index common problems

Introduction to Indexes

An index is a structure that sorts the values of a list of multiple columns in a database table, using an index to increase the query speed of specific data in the database.

The meaning and characteristics of the index

An index is a separate database structure, stored on disk, that contains pointers to references to all records in the data table. Using indexes to quickly find rows with a specific value in one or more columns, all MySQL column types can be indexed, and using indexes on related columns is the best way to improve the speed of query operations.

For example, there are 20,000 records in the database, and now you want to execute a query like this: SELECT * from table where num=10000. If you do not have an index, you must traverse the entire table until this line of Num equals 10000 is found, and if you create an index on the NUM column, MySQL does not need any scanning, and you can find the location of the line by looking at 10000 directly in the index. Yes, creating an index can improve the query speed of your database.

Indexes are implemented in the storage engine, so the indexes for each storage engine are not necessarily identical, and each storage engine does not necessarily support all index types. Defines the maximum number of indexes and the maximum index length per table based on 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 a higher limit. There are two types of storage for indexes in MySQL: Btree and hash, specifically related to the table's storage engine.

The main advantages of the engine are the following:

By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

The query speed of the data can be greatly accelerated, which is also the most important reason to create the index.

You can speed up the connection between tables and tables in terms of referential integrity for your data.

When you use grouping and sort clauses for data queries, you can also significantly reduce the time to group and sort in queries.

There are many disadvantages to increasing the index:

It takes time to create indexes and maintain indexes, and increases the time it takes to increase the amount of data.

The index takes up disk space, in addition to the data table occupies the data space, each index also occupies a certain amount of physical space, if there are a large number of indexes, the index file may reach the maximum size faster than the data file.

When the data in the table is added, deleted, and modified, the index is also maintained dynamically, thus reducing the maintenance speed of the data.

Classification of indexes

Normal index and unique index

Normal and unique indexes are basic index types in MySQL, allowing duplicate and null values to be inserted into the columns that define the index.

Unique index, the value of the indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique.

A primary key index is a special unique index that does not allow null values.

Single-column indexes and combined indexes

Single-column indexes are an index that contains only a single column and can have multiple single-row indexes on one table.

A composite index is an index that is created on multiple field combinations in a table, and is used only if the left field of those fields is used in the query criteria. The combination index is used to follow the leftmost prefix collection.

Full-Text Indexing

The full-text index type is fulltext, which supports full-text lookups of values on columns that define the index, allowing duplicate and null values to be inserted in these indexed columns. A full-text index can be created on a column of char, varchar, or text type. MySQL only MyISAM storage engine supports full-text indexing.

Spatial index

Spatial indexes are indexes on fields of spatial data types, with 4 types of spatial data in MySQL: GEOMETRY, point, linestring, and polygon. MySQL uses the spatial keyword to expand to create spatial indexes that can be used to create a syntax similar to regular indexes. The column that creates the spatial index must be declared as not NULL, and the spatial index can only be created in a table where the storage engine is MyISAM.

Design principles for Indexes

MySQL Index learning