What is an index? Why use indexes?

An index is a sorted list that stores the value of the index and the physical address of the row containing the value. When the data is very large, an index can greatly speed up queries because it can locate a row without scanning the entire table. Instead, the index table is used to find the physical address of the row and access the corresponding data

Now, if you were asked to find chapter 3, section 2 of a book, what would you do? You would go to the table of contents, find the number of pages in the corresponding chapter, and then turn to the corresponding page, which is actually the table of contents index, to help the reader find the chapter quickly. An index in mysql is like a table of contents in a book. Building an index can help us quickly find the content we want. Without indexes, mysql scans all records in the entire table, and the larger the table, the slower the query. Using indexes, mysql can quickly find the location of data without scanning the entire table

For example, suppose I have a contact list with 5W records, and I want to find the information of a certain contact through Mobile. If no index is created on the mobile field, mysql scans the entire contact table. If an index is created on mobile, mysql will walk the index and not scan the entire table. An underlying implementation of the index will be shared later

Advantages and disadvantages of mysql indexes and when to use them

Advantages:

  • All MySql field types can be indexed
  • Improve query efficiency
  • When using grouping and sorting clauses for data retrieval, the grouping and sorting time in queries can also be significantly reduced

Disadvantages:

  • Creating and maintaining indexes takes time, and the time increases as the data increases
  • Indexes take up physical space
  • When data in a table is added, deleted, or modified, the index needs to be dynamically maintained, which reduces the data maintenance speed

When to use indexes?

  • Reduce index creation for tables that are frequently updated. Indexes should be created for fields that are often used for queries
  • Do not build an index for a table with fewer records, because it may take longer to traverse the index than to scan the entire table, and the index will not optimize the query speed
  • Do not index columns that have only two distinct values, such as gender

So what is an index? Why index? And what are the pros and cons of indexes? When to use it? Here are the types of indexes

The index classification

Note: Because indexes are implemented in mysql’s storage engine, different storage engines use different indexes

MyISAM and InnoDB storage engine: BTREE index only supported

MEMORY/HEAP storage engine: Supports HASH and BTREE indexes

Mysql indexes can be divided into four categories: singleton indexes, composite indexes, full-text indexes, and spatial indexes

Singleton index

Singleton indexes include primary key indexes, unique indexes, and normal indexes

Primary key index: When we create a table, we usually have an ID field and set it as the primary key. The value of the column to which the primary key index is added must be unique and cannot have empty values

Unique index: A column to which a unique index is added. The value must be unique but null is allowed

Normal indexes: Columns that are added to normal indexes have no restrictions, can have duplicate values, and can be null, simply to speed up queries

Composite index

A composite index is an index created on multiple fields. It should be noted that composite indexes are only effective if the leftmost prefix collection is followed. If you don’t understand, you will understand after reading the following examples

The full text indexing

InnoDB started to support full-text indexing after mysql5.6, and Chinese was supported through the ngram plugin after 5.7, and MyISAM supported full-text indexing before 5.6. Full-text index means that we can query the corresponding record by keyword, which will be introduced in the following examples.

MySQL allows full-text indexing on types char, vARCHar, and text

Spatial index

Spatial indexes are built on fields of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. Only the MyISAM storage engine supports it

Index related operations

Create indexes

Create an index. You can create an index when the table is created, or you can add indexes to certain fields in the table after the table is created

Create index ();

CREATE TABLE `contacts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 'type' tinyint(11) NOT NULL DEFAULT '0' COMMENT ' 0- other ', 'status' TINyInt (11) NOT NULL DEFAULT '0' COMMENT' Whether the contact is valid 0- NULL ', 'name' varchar(20) NOT NULL DEFAULT 'COMMENT ',' mobile 'bigint(64) NOT NULL DEFAULT '0' COMMENT ', 'mail' varchar(64) NOT NULL DEFAULT COMMENT ', 'relation' varchar(32) NOT NULL DEFAULT COMMENT ', Address 'varchar(128) NOT NULL DEFAULT COMMENT ', 'mobile_location' varchar(20) NOT NULL DEFAULT 'COMMENT ',' memo 'text COMMENT' 'create_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ', 'update_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', PRIMARY KEY (`id`), UNIQUE KEY `uniq_m` (`mobile`), KEY `idx_n`(`name`), KEY `idx_multi` (`id`,`name`,`mobile`), FULLTEXT KEY 'idx_full' (' memo ') WITH PARSER ngram) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' Contact table,add by book 2019.04.04 ';Copy the code

Note: Chinese full text index is supported after mysql5.7. WITH PARSER ngram is added because mysql has built-in Ngram full text PARSER to support Chinese, Japanese and Korean word segmentation

Select * from table where index (s) = 1; select * from table where index (s) = 1

The ALTER TABLE TABLE name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [INDEX name] (INDEX field name)Copy the code

For example, add a full-text index to the Mail field in the contacts table:

ALTER TABLE contacts ADD FULLTEXT KEY `idx_multi2`(`mail`) WITH PARSER ngram; You can also use INDEXCopy the code

We can also add indexes to created table fields like this:

The CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] INDEX name ON the name of the table (CREATE INDEX field name)Copy the code

Delete indexes and view indexes

To see if the index was deleted successfully, let’s look at how to check which indexes are in a table:

SHOW INDEX FROM table name;Copy the code

Take a look at what indexes are created in the Contacts table:Note: The meaning of each of these columns will be described later

There are two ways to drop an index:

1, ALTER TABLE, the TABLE name DROP KEY/INDEX INDEX 2, DROP the KEY | INDEX INDEX name ON the name of the TABLECopy the code

Suppose you now want to delete the full-text index on mail:

ALTER TABLE contacts DROP INDEX idx_multi2;
Copy the code

Take a look at the results:

We can see that the index on mail has been successfully deleted

The meaning of each column here:

Using the index

Once you know how to create an index, view an index, and drop an index, how do you use it? How do I know if an index is used?

Now create a student table

CREATE TABLE `student` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 'name' varchar(20) NOT NULL DEFAULT '0' COMMENT ', 'sex' tinyint(64) NOT NULL DEFAULT '0' COMMENT '1- female, 2- male ', 'age' int(11) NOT NULL DEFAULT '0' COMMENT 'age ',' phone 'bigint(20) NOT NULL DEFAULT '0' COMMENT' cellphone ', 'mail' varchar(32) NOT NULL DEFAULT 'COMMENT ',' detail_info 'text COMMENT' detail_info ', 'create_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time ', 'update_time' datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'UPDATE time ', KEY 'idx_uni' (' phone '); KEY 'idx_nor' (' mail '); -- common index KEY 'idx_multi' (' name ', 'sex', 'age'),-- joint index FULLTEXT KEY 'idx_full' (' detail_info ') WITH PARSER ngram -- FULLTEXT index) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' ';Copy the code

With the EXPLAIN keyword in mysql, we can see the execution of an SQL statement, that is, we can see whether the query uses an index

I inserted three pieces of data into the student table:

Take a look at the index of this table:

Then we use ID, detail_info, (name, sex, age combination) as query conditions to query:

To introduce the meaning of each of these:

Because type is particularly important, here are the six common types of type values:

Remember possible_key and key for the above

Using full-text indexes

Using composite indexes

As mentioned earlier, composite indexes follow the leftmost prefix. Rows are matched with the leftmost set of columns in the index. This set of columns is called the leftmost prefix.name.sex.ageIndex (name, sex, age), (name, sex), (name, sex) or (name); If the field to be queried does not prefix the left-most part of the index, then the index is not used. For example, the combination of sex or (sex, age) is not used

conclusion

So now that I’ve basically covered some of the basics of mysql indexes, what is an index? What is it? Why? How to use it? What is the principle of index failure and index implementation