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

An index, also known as a key in mysql, is a data structure used by the storage engine to quickly find records.

Index structure types (there are two common types):

1. B-Tree index

This index is supported by most mysql engines

1.B-Tree usually means that all values are stored sequentially

two。 And the distance from each leaf to the root is the same.

Instead of doing a full table scan to get the required data, the storage engine starts searching from the root node of the index

Applicable to:

a. Full key value lookup (full value match)

b. Range of key values (if there is a merged index with multiple columns, you need to match one column exactly and the range matches another)

c. Key prefix lookup

d. Search in order (order by)

Restrictions:

a. If you do not start the search by the leftmost column of the index, you cannot use the index

b. Columns in the index cannot be skipped

c. If there is a range query for a column in the query, none of the columns on the right can optimize the query using the index

two。 Hash indexing

In mysql, only the memory engine explicitly supports hash indexes; based on the hash table implementation, only queries that exactly match all the columns of the index are valid; for each row of data, the storage engine calculates a hash code for all index columns, and different key rows calculate different hash codes, and the hash index stores all hash codes in the index, while storing pointers to each data row in the hash table. (hash index structure: hash value: pointer)

Advantages:

The search speed is very fast.

Restrictions:

a. Cannot be used for sorting

b. Partial index column matching lookup is not supported

c. Only equivalent comparison queries are supported

When the storage engine does not support hash indexing, you can establish a custom hash:

Such as: building a table pseudohash:id,url,url_crc

Create trigger: set new.url_crc = crc32 (new.url) when insert data

Set new.url_crc = crc32 (new.url) when Update data

Find: select url, url_crc from pseudohash where url_crc = crc32 ("www.baidu.com") and url = "www.baidu.com"

Take human hash values and corresponding column values in the where statement.

Type of indexing function:

1. General Index (INDEX)

CREATE INDEX index_name ON table_name (column_list)

two。 Unique index (UNIQUE INDEX)

The value of the index column in the mysql database must be unique, but vacancy is allowed. If it is a combinatorial index, the combination of column values must be unique.

CREATE UNIQUE INDEX index_name ON table_name (column_list)

3. Primary key Index (PRIMARY KEY)

Is a special unique index, which is not allowed to be left vacant. It is generally created when the table is created.

CREATE TABLE table_name (

ID INT NOT NULL

[column] VARCHAR (16) NOT NULL

PRIMARY KEY (ID)

);

4. Full-text indexing: (FULLTEXT) can only be used in MyISAM engines

Index big data's text, search the words to be found in the index, and locate which text data includes the words to be searched.

1, build index 2, search for location in the index

/ / A full-text index is made for content:

CREATE TABLE `table` (

`id`int (11) NOT NULL AUTO_INCREMENT

`title`char (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL

PRIMARY KEY (`id`)

FULLTEXT (content))

When looking for:

SELECT * FROM article WHERE MATCH (content) AGAINST ('string you want to query')

Advantages of the index:

1. The server can be quickly located to the specified location of the table, which greatly reduces the amount of data that the server needs to scan.

two。 The most common b-tree indexes store data sequentially and can be used to do order by and group by to help servers avoid sorting and temporary tables, and change random Imax O into sequential Imax O

3. Because the actual column values are stored in the index, some queries only need to use the index to complete all the queries

Index optimization

1. Prefix index

Problem: sometimes the index needs to store long strings, which makes the index large and slow.

Solution: 1. Hash index 2. Prefix index

Index selectivity: non-duplicate index values / total number of records in the data table. The higher the value, the better the performance.

The selectivity of the unique index is 1, and the performance is the best.

Calculate the appropriate prefix length so that the selectivity of the prefix is close to that of the complete column. Mysql cannot use prefix indexes for order by and group by, nor can it use prefix indexes for overwrite scanning

two。 Select the appropriate index column order

Put the column with the highest selectivity in the front column of the index

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.