Shulou(Shulou.com)05/31 Report--

This article shows you what the index of MySQL is, which is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Like other databases, the MySQL index sorts the specified columns in the table and saves them separately, which is used to quickly find rows with specific values. If there is no index, you must start with the first row and read the entire table lookup. The larger the table, the higher the cost. If you have an index of related columns in the table, you can quickly determine where to look in the middle of the data file without having to view all the data, which is much faster than reading each row in order.

MySQL's PRIMARY KEY index, UNIQUE index, normal index, and FULLTEXT index all use B-trees storage, while Spatial index uses R-trees storage.

Index creation syntax in MySQL 5.7:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[index_type]

ON tbl_name (key_part, …)

[index_option]

[algorithm_option | lock_option]...

Key_part:

Col_name [(length)] [ASC | DESC]

Index_option:

KEY_BLOCK_SIZE [=] value

| | index_type |

| | WITH PARSER parser_name |

| | COMMENT 'string' |

Index_type:

USING {BTREE | HASH}

Algorithm_option:

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

Lock_option:

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

You can also use the alter table statement to create an index.

MySQL supports prefix indexes, that is, indexes are created on the first N characters of the index field.

Access to the original text to enhance the reading experience: https://www.modb.pro/db/22805?cyn

Root@database-one 21:56: [gftest] > select * from emp +-+ | ename | age | sal | hiredate | deptno | +-+ | Guo Jun | 27 | 8400.00 | | 2019-12-08 | 10 | Liu Jie | 30 | 9100.00 | 2018-04-09 | 10 | | Wang Yan | 24 | 6000.00 | 2020-01-05 | 20 | | Mary | 26 | 7200.00 | 2018-07-06 | 30 | | Xiao Wei | 29 | 8700.00 | 2017-05-28 | 30 | +-| +-+ 5 rows in set (0.02sec) root@database-one 21:57: [gftest] > show index from emp\ GEmpty set (0.01sec) root@database-one 21:57: [gftest] > create index idx_emp_ename on emp (ename (2)) Query OK 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0root@database-one 21:57: [gftest] > show index from emp\ gateway * 1. Row * * Table: empNon_unique: 1Key_name: idx_emp_enameSeq_in_index: 1Column_name: enameCollation: ACardinality: 5Sub_part: 2Packed: NULLNull: YESIndex_type: BTREEComment:Index_comment:1 row in set (0.00 sec) root@database-one 21:57: [gftest] > explain select * from emp where ename like 'King%' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | SIMPLE | emp | NULL | range | idx_emp_ename | idx_emp_ename | 9 | NULL | 1 | 100.00 | Using where | +- -+ 1 row in set 1 warning (0.07sec) root@database-one 21:58: [gftest] > explain select * from emp where sal > 6000 +-+ | Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-- + -+ | 1 | SIMPLE | emp | NULL | ALL | NULL | 5 | 33.33 | Using where | +- -+ 1 row in set 1 warning (0.03 sec)

As you can see, the index created is of type BTREE and is used when querying by ename.

There are always some principles for using indexes:

Consider indexing columns that often appear in where and join conditions.

Index columns with good selectivity if necessary. For example, in the user table, the ID card column has different values, the selectivity is very good, and the index is particularly efficient when the index is used; the surname column has good selectivity, and the index is also more efficient when used; and the gender column, which contains only male and female, is of poor selectivity, so indexing is of little use.

Don't overcreate the index. The more indexes, the better. Each index takes up disk space and degrades the performance of DML operations. In addition, when MySQL generates an execution plan, too many indexes will aggravate the work of the optimizer and may even interfere with the optimizer's failure to select the best index.

The above is what the index of MySQL is. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.