MySQL Learning Note (vi): Index

This article mainly introduces some problems about indexes in MySQL, such as: The role of indexes, how to create indexes, the principles of designing indexes, how to optimize indexes and so on.

One: Index overview

All MySQL column types can be indexed, well designed to improve the performance of the query, but if the index is too large, because each update operation will update the index, it will affect the overall performance of the database. Therefore, it is very important to design appropriate indexes according to certain principles.

(1): Syntax for creating indexes

CREATE [unique| Fulltext| Spqtial] INDEX index_name [USING Index_type] on table_name (COL_NAME)

Example: built a user table with properties name, age, address, the following figure shows the difference between a query when there is no index and when an index is established.

Two: Several principles of design index

(1): The column in the columns than select in the WHERE clause is more suitable for indexing.

(2): Select those columns with large cardinality, which will be better indexed, which will make the index a good way to distinguish between different values.

(3): Using a short index, such as a char (200) column, if the first 20 characters can well distinguish between different values, there is no need to index the entire column, which can greatly reduce the storage space of the index.

(4): Do not over-index, only create the required index. Too many indexes can waste disk space, reduce write performance, and also bring more work to query optimization, making MySQL choose the best index.

(5): InnoDB as far as possible to specify the primary key: InnoDB engine stored tables will be saved in a certain order, such as primary key, unique index, if not all will automatically generate an internal column, according to these access is the fastest, so InnoDB try to specify the primary key. When more than one column can be a primary key, select the column that is most commonly used as the access condition as the primary key.

Example: InnoDB does not create an index, but it establishes a primary key, which is queried with the primary key.

Three: Btree index and hash index

The specific principle is not much to say, many online, MyISAM and InnoDB The default is to create a btree index, the memory engine is created by default, the hash index, when used is more critical:

"Key" The hash index is only available for exact queries, and when used, <, <=, or like ' string% ', you need to use the Btree index. So when using the hash index, you need to pay attention to the writing of SQL, to ensure that the index can be used, if there is scope to find, the index needs to be set to Btree index.

Four: Optimization of indexes

After following the design principles of the index, you need to be aware of several features of index usage when designing indexes and writing sql:

(1). prefix attribute: When a multi-column index is created, the index works as long as the previous column is used. For example, a two-column index (a, b) is created, and if only a is present in the WHERE Condition statement, the index is used, but only B is present, and the index will not be used.

(2). When using a like query,% cannot appear in the first character, and should be "constant +%", so that the index may work.

(3). When searching for large text, use full-text indexing instead of using like '%...%

(4). The condition that is separated by or, if the column before or is indexed, the subsequent column is not indexed, then the index is not used.

(5). If the column type is a string, remember to enclose the string constant value in quotation marks in the Where condition.

View the usage of the index, use the show status like ' handle_read% ' to view the Handle_read_key (the value big indicates that the index is well used, and vice versa) and Handle_read_rnd_next ( A large value indicates that a large number of full-size scans have been performed, and the index is not being used well.

MySQL Learning Note (vi): Index