How to design and optimize MySQL indexes

What is an index?

A database index is a data structure that increases the speed of data retrieval operations on a database table at the cost of additional writes and storage space. In layman's terms, an index is similar to the table of contents of a book, and you can quickly find the content you need based on the page numbers recorded in it. ——What are the common indexes in Wikipedia?

• Normal index: the most basic index, without any restrictions
• Unique index: Similar to "normal index", except that the value of the index column must be unique, but null values ​​are allowed
• Primary key index: It is a special index that does not allow null values.
• Full-text index: It can only be used for MyISAM tables. For large data, generating full-text indexes is time-consuming and space-consuming.
• Combined index: In order to improve the efficiency of multi-condition query, a combined index can be established, following the "leftmost prefix matching principle"
  

Here we take a relatively complex combination as an example to introduce how to optimize it.

Leftmost prefix matching principle

First of all, we need to know what the leftmost prefix matching principle is.

The leftmost prefix matching principle means that when using a B+Tree joint index for data retrieval, the MySQL optimizer will read the predicate (filter condition) and match rightward in the order in which the joint index fields are created until a range query or a non-equal query is encountered. The index columns after this field will not be used. At this time, calculating key_len can analyze which index columns are actually used by the joint index.

How to calculate key_len

The key_len calculation also helps us understand the leftmost prefix matching principle of the index.

key_len indicates the length [number of bytes] of the selection index used to obtain the result set, excluding order by . That is, if order by also uses an index, key_len is not included.

Before calculating key_len , let's review the basic data types (taking UTF8 encoding as an example):

The test data table is as follows:

CREATE TABLE `test_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NOT NULL,
 `b` int(11) DEFAULT NOT NULL,
 `c` int(11) DEFAULT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `test_table_a_b_c_index` (`a`,`b`,`c`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hit Index:

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

You can see key_len = 12 How is this calculated?
Because the character set is UTF8, one field occupies four bytes, and three fields are 4 * 3 = 12 bytes.

Whether NULL is allowed. If NULL is allowed, an extra byte is needed to mark the field. Different data types require different byte sizes.

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

It can be seen that when the field is allowed to be empty, key_len becomes 15 = 4 3 + 1 3 (when the INT type is empty, it occupies an extra byte).

Index optimization

With these basic knowledge, we can judge the performance based on the actual SQL.

Taking the above data table as an example, create a joint index for the three fields a, b, and c.

Usually when viewing an execution plan, if the Extra column is Using index, it means that the optimizer uses a covering index.

• SQL1 can use covering indexes, which has good performance
• SQL2 can use covering indexes and avoid sorting, which has good performance
• SQL3 can use covering indexes, but needs to be filtered based on the where clause
• SQL4 can use partial index a, but cannot avoid sorting, and performance is poor
• SQL5 can fully use covering indexes and avoid sorting, which has good performance.
• SQL6 can use covering indexes, but cannot avoid sorting (this is because MySQL InnoDB defaults to ascending order when creating an index, and the index cannot be automatically sorted in descending order)
• SQL7 can use covering indexes, but it needs to be filtered according to the where clause (non-fixed value query)
  

Create an index specification

• Considering the cost of index maintenance, the number of indexes in a single table should not exceed 5, and the number of fields in a single index should not exceed 5.
• Do not create indexes on low-cardinality columns, such as Gender. Index queries created on low-cardinality columns do not necessarily have a performance advantage over full table scans, especially when there is a table return cost.
• Create a joint index properly, (a,b,c) is equivalent to (a), (a,b), (a,b,c).
• Reasonable use of covering indexes reduces IO and avoids sorting.

The above is the details of how to design and optimize MySQL indexes. For more information on MySQL index design and optimization, please pay attention to other related articles on 123WORDPRESS.COM!