Shulou(Shulou.com)06/01 Report--

Let me tell you a little bit about the content of MySQL database index. have you known about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the introduction to the index of MySQL database.

Storage engine is the core of database, and the commonly used MySQL storage engine is InnoDB,MyISAM,memory. Index is a data structure that accelerates the query of data.

1. Brief introduction to Index

1.1 Index benefits

Query the connection between data block data uniqueness acceleration tables

1.2 Index shortcomings

The index will take up storage space and may be larger than its own table data. it is only beneficial to search, and increases the burden of additions, deletions and modifications.

1.3 considerations for using indexes

Data with large differences in field values can be indexed (gender columns should not be indexed). Character fields can be indexed by selecting only the first few characters of the field, which can store less data.

two。 Two important storage engines InnoDB and MyISAM

2.1 InnoDB

2.1.1 Features

Support transaction data large table data and index separate storage support row-level locking default 6B length primary key itself is the index, the index maps not the address, but the primary key, thus finding the row record

2.1.2 InnoDB Index

Only B+ trees are supported

2.2 MyISAM

2.2.1 Features

Does not support transaction insertion and lookup very fast does not support transactions simply to check the first choice of data

2.2.2 MyISAM Index

Only B+ trees are supported

2.2.3 memory Index

Support for B+ tree and HASH

MySQL database generally defaults to innoDB storage engine.

3. Classification of indexes

General index and unique index single-column index and combined index (combined index: leftmost matching) full-text index: create a participle, map the address for each participle, and then find the address of the data by finding the participle in the index. Spatial index

4. Principles of index design

Not the more, the better. Avoid building too many indexes on tables that are updated frequently. Tables with small amounts of data do not use indexes. The index should be based on columns with large differences in values. Set up a composite index on a frequent grouping operation table. Try to use short indexes to save disk space.

This article is all over here, more exciting content can be followed by the MySQL video tutorial column!

These are the details of the content of the MySQL database index, please pay attention to other related articles!

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.