mysql--Index Base

In this article, we'll start with the index basics, describe what are the indexes and several types of indexes, and then learn how to create indexes and the basic principles of index design.

The structure of the user table used to test index creation in this article is as follows:

What is an index

The index (also called "Key Key" in MySQL) is a data structure that the storage engine quickly finds records

--"High performance MySQL"

We need to know that the index is actually a data structure, its function is to help us quickly match to find the data rows needed, is one of the most common tools for database performance optimization. Its role is equivalent to the supermarket shopping guide, books in the catalogue.

Index type

You can use SHOW INDEX FROM table_name; view index details

1. primary key index PRIMARY key

   It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table.

   Note: A table can have only one primary key

2. unique index unique

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

   Can be passed ALTER TABLE table_name ADD UNIQUE (column); Create a unique index

   Available via ALTER TABLE table_name ADD unique (COLUMN1,COLUMN2); Create a unique composite index

3. Normal index index

   Most basic indexes, it has no restrictions.

   Can be passed ALTER TABLE table_name ADD INDEX index_name (column); Create a normal index

4. Composite Index index

   Combined index, that is, one index contains multiple columns. Used to avoid back-table queries.

   Can be passed ALTER TABLE table_name ADD INDEX index_name (column1, Column2, column3); Create a composite index

5. full-text indexing fulltext

   Full-text indexing (also known as full-text search) is a key technology used by search engines today.

   Can be passed ALTER TABLE table_name ADD fulltext (column); Create a full-text index

Once an index is created, it cannot be modified, and if you want to modify the index, you can only delete rebuilds. You can use the DROP INDEX index_name ON table_name; Delete index.

Principles of Index Design

1. The column that is appropriate for the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause

2. A class with a lower cardinality, with poor indexes, and no need to index this column

3. With a short index, if you index long string columns, you should specify a prefix length, which can save a lot of index space

4. Do not over-index. The index requires additional disk space and reduces the performance of the write operation. When you modify the contents of the table, the index is updated or even refactored, and the more indexed the columns, the longer the time. So just keep the required index in favor of the query.

Reference

• "In layman's MySQL"

The thing about the code.
Public Number:

Source: http://songwenjie.cnblogs.com/
Statement: This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction. If you think it's good, just click on the "recommend" button below, thanks for your support. Reprint and quote please specify the source.

mysql--Index Base