Mysql---Index

Directory

• Introduction to the Index
• The role of the index
• What kinds of common indexes are there?
• Normal index
• Unique index
• Primary key Index
• Combined index
• Index nouns
• Leftmost prefix
• Considerations for Indexing

Introduction to the index

A data structure in the database that is designed to help users quickly find data. Similar to a directory in a dictionary, when looking up the contents of a dictionary

Depending on the directory, you can find the location where the data is stored, and then get

the role of the index

Constraints and accelerated lookups

What kinds of common indexes are there?

-Normal Index

-Unique index

-PRIMARY Key index

-Federated Index (multiple columns)

-Federated Primary Key index

-Federated Unique Index

-Federated Common Index

What is the difference between index-free and indexed?

No index words from go to the next one a query

Indexed: The essence of creating an index is to create additional files, (some format storage, query, the first to go to an extra file to find, set a position, and then go to the original table directly query, but the more index created, will also have a loss on the hard disk)

What is the purpose of indexing?

1, extra files save special data structure

2, query fast, but insert update delete is still slow

3, the index must be hit to be valid after the index is created

Hash index and Btree index

Index of type 1,hash: Query single fast, range query slow

Index of 2,btree type: B + Tree, more layers, exponential growth of data volume

Normal Index

Function: Only one accelerated search

CREATE TABLE UserInfo (                   intnull  auto_increment primary key,                   name varchar (  + NULL ,                   email varchar (null,                   index Ix_name (name)               );

CREATE TABLE + Normal index

Normal index:

Create index name on table name (column name)

To delete an index:

Drop index index name on table name

View index:

Show index from table name

Unique index

Unique indexes have two features: accelerated Find and UNIQUE constraints

CREATE TABLE UserInfo (                   intnull  auto_increment primary key,                   name varchar (  + NULL ,                   email varchar (null,                   unique  index  ix_name (name)               );

CREATE TABLE + unique index

Unique index:

Create unique index index name on table name

To delete a unique index:

Drop index index name on table name;

PRIMARY KEY Index

The primary key index has two functions: accelerated lookups and UNIQUE constraints

CREATE TABLE userinfo (IDintNotNULLauto_increment primary key, name varchar ( +) notNULL, email varchar ( -) notNULL, unique index Ix_name (name)) or CREATE TABLE UserInfo ( IDintNotNULLauto_increment, name varchar ( +) notNULL, email varchar ( -) notNULL, primary key (NID), unique index Ix_name (name)) creates a table+ PRIMARY KEY Index

CREATE TABLE + primary key index

Primary KEY index:

ALTER TABLE name add primary key (column name);

To delete a primary key index:

ALTER TABLE name drop PRIMARY key;

ALTER TABLE table name modify column name Int,drop primary key;

Combined Index

A composite index is the combination of n columns into one index

Federated Common Index:

Create index index name on table name (column name 1, column name 2);

index Nouns

Overwrite index: Get data directly in the index file

Example: select name from userinfo where name = "afan1000000";

Index merging: Merging multiple single-column indexes into use

Example: SELECT * from UserInfo where name= "afan1000000" and id= "1313131";

What is the leftmost prefix ?

Leftmost prefix match:        CREATE index Ix_name_email on userinfo (name,email);                 SELECT * from userinfo where name = ' Alex ';                 SELECT * from userinfo where name = ' Alex ' and email= ' [email protected] ';                 SELECT * from userinfo where  email= ' [email protected] ';             If you use the combined index, name and email combination index, query             (1) name and email---use index             (2) name        ---use index             (3) Email       ---Not applicable index              combined indexes perform better than multiple single-column indexes for simultaneous search of n conditions
Performance of composite indexes > Performance of index merging *********

Considerations for Indexing

1. Avoid using SELECT *

2. Count (1) or count (column) instead of COUNT (*)

3. Try to use char instead of varchar when creating a table

4. Table field Order fixed-Length field priority

5. Combining indexes instead of multiple single-column indexes (when multiple conditional queries are used frequently)

6. Try to use a short index (CREATE index ix_title on TB (title ()); Reshuffle data type text type

7. Use a connection (join) instead of a subquery

8. Note that the condition type must be consistent when connecting the table

9. Index hashes (few repetitions) do not apply to indexes such as: gender inappropriate

Mysql---Index