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

This article mainly gives you a brief introduction to some simple knowledge of mysql index. you can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on some simple knowledge of mysql index can bring you some practical help.

What's the index?

An index is like a catalogue of a book

The index is used to quickly find the row with a specific value in a column, without using the index, MySQL must read the entire table from the first record until finding out the relevant row, the larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data, then it will save a lot of time.

Advantages and disadvantages

Advantages

1. Greatly speed up the query

2. All field types can be indexed.

Shortcoming

1. It takes time to create and maintain indexes. The more data, the more time-consuming.

2. The index takes up storage space, and the data in the data table will also have the maximum online setting. If we have a large number of indexes, the index file may reach the online value faster than the data file.

3. When the data in the table is added, deleted or modified, the index also needs dynamic maintenance, which reduces the maintenance speed of the data.

Use principles and scenarios

1. The more indexes, the better. It depends on the situation.

2. Tables that are updated frequently should have as few indexes as possible

3. Index the fields frequently used for query

4. try not to use indexes for fields with small amount of data. It takes less time to query all the data than to traverse the index, and the index will have no optimization effect.

5. Try not to use indexes for fields with few different values, such as gender fields with only two different values for men and women.

Index classification

Note: indexes are implemented in storage engines, that is, different storage engines use different indexes

MyISAM and InnoDB storage engines: only BTREE indexes are supported, that is, BTREE is used by default and cannot be replaced.

MEMORY/HEAP storage engine: supports HASH and BTREE indexes

1. Single column index

An index contains only a single column, but a table can have multiple single-column indexes

1.1. General index

The basic index type in MySQL, with no restrictions, allows duplicate values and null values to be inserted in the columns that define the index, simply to query the data faster.

1.2. Unique index

The value in the index column must be unique, but null values are allowed

1.3. Primary key index

Is a special unique index, and null values are not allowed

two。 Combinatorial index

An index created on a combination of multiple fields in a table will be used only if the left field of these fields is used in the query condition, and the best left prefix rule is followed when using the combined index.

3. Full-text index

Full-text indexing, which can only be used on the MyISAM engine, can only be used on CHAR,VARCHAR,TEXT type fields. Full-text index, that is, in a pile of text, through one of the keywords, you can find the record row to which the field belongs, such as "you are a big pen, idiot." Through the big pen, you may be able to find the record.

4. Spatial index

Spatial index is an index established on the fields of spatial data types. There are four spatial data types in MySQL: GEOMETRY, POINT, LINESTRING and POLYGON. Use the SPATIAL keyword when creating a spatial index. Requires that the engine creates a column with a spatial index for MyISAM, which must be declared as NOT NULL

Index mode

Principle of use: if the value difference is large, and mainly equivalent search (=, in), Hash index is a more efficient choice, it has O (1) search complexity; if the value difference is relatively poor, and mainly range search, B-tree is a better choice, it supports range search.

B-Tree index

B-tree index has the ability of range search and prefix lookup. For B-tree with N nodes, the complexity of retrieving a record is O (LogN). It's the equivalent of a binary search.

Hash index

The hash index can only be equal to lookup, but no matter how large the Hash table is, the lookup complexity is O (1).

Index creation and deletion

Create

Create when creating a table

CREATE TABLE table name [field name data type] [UNIQUE | FULLTEXT | SPATIAL |.] [INDEX | KEY] [Index name] (field name [length]) [ASC | DESC]

Example:

CREATE TABLE `NewTable` (`id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR (255) NOT NULL, `name` VARCHAR (255) NOT NULL, `sex` TINYINT NOT NULL DEFAULT 0, `address` VARCHAR (255) NULL, PRIMARY KEY (`id`), # primary key index INDEX `name` (`name`) USING BTREE, # general index UNIQUE INDEX `username` (`username`) USING BTREE # unique index INDEX `uroomna` (`username`, `name`, `address`) USING BTREE # combined index)

Table creation already exists

ALTER TABLE table name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index name] (index field name) [ASC | DESC]

Example:

ALTER TABLE `test`add PRIMARY KEY (`id`), # Primary key index ADD INDEX `name` (`name`) USING BTREE, # General index ADD UNIQUE INDEX `username` (`username`) USING BTREE, # unique index ADD INDEX `uuserna` (`username`, `name`, `address`) USING BTREE; # combined index

Delete index

ALTER TABLE table name DROP INDEX index name.

Example:

ALTER TABLE `test`DROP PRIMARY KEY,DROP INDEX `username`, DROP INDEX `name`, DROP INDEX `uSecretna`

Update index

Delete first and then build

ALTER TABLE `test`DROP INDEX `username`, ADD UNIQUE INDEX `username1` (`username`) USING BTREE, DROP INDEX `name`, ADD INDEX `name2` (`name`) USING BTREE, DROP INDEX `uroomna`, ADD INDEX `urooman` (`username`, `address`, `name`) USING BTREE

Index failure

1. The combined field does not follow the best left prefix rule.

two。 Fuzzy queries, such as like'% test

# Index effective select * from `test` where `name` like "123"; # Index effective select * from `test` where `name` like "123"; # Index invalidation select * from `test` where `name` like "3"; # Index invalidation select * from `test` where `name` like "3"

3. Any of the following operations (calculation, function, (automatic or manual) type replacement) on an index column will cause the index to fail and result in a full table scan

For example, add an index to the sex field

# Index failure select * from `test` where `sex` * 0.5 = 1

4. Range index (>, 1 and `name` ='a'

5.! =, is null, is not null cannot use the index

6. String field value without single quotation marks (number does not report error, English error) index invalidation

Build an index

ALTER TABLE `test`add INDEX `name` (`name`) USING BTREE

Example

# Index invalidation select * from `test` where `name` = 123 X # Index effective select * from `test` where `name` = '123'

7. Index invalidation caused by or condition

Build an index

ALTER TABLE `test`add INDEX `sex` (`sex`) USING BTREE; ADD INDEX `nu` (`name`, `username`) USING BTREE

Example:

# select * from `test` where (`name` = 'aa' and `username` =' aa') or `sex` > 1 # index sex effective select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa')

Appendix

Best left prefix rule

If you index multiple columns, follow the leftmost prefix rule. This means that the query starts at the leftmost front column of the index and does not skip the columns in the index

Build the index as follows

ALTER TABLE `test` ADD INDEX `uyogan` (`username`, `address`, `name`) USING BTREE

The query is as follows

# hit part select * from `test` where `username` = 'aaa';# hit part select * from `test` where `username` =' aa' and `address` = 'aaa';# all hit select * from `test` where `username` =' aa' and `address` = 'aaa' and `name` =' aholeswitch # not hit, the first condition field is not usernameselect * from `test` where `address` = 'aaa'

Mysql index of some simple knowledge to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.