MySQL Basics (iii)

Index

Index is a data structure in the database that is designed to help users quickly query data. Similar to a directory in a dictionary, you can find the contents of the dictionary based on the directory to locate the data, and then get directly.

An index is another file that is created in the database.

There is a huge gap between indexed and non-indexed query times.

Full table scan is the least efficient.

Tens of thousands of data with a tree structure, at least 14 this will be done.

Common Index Categories:

• Normal index
• Unique index
• Primary key Index
• Combined index
• Full-Text Indexing

A normal index can only help find
Unique indexes can only help find, content is not allowed to repeat, can have null
Primary key index can only help find, content is not allowed to repeat, do not allow null, a table can only have one primary key
Composite index Multi-column Common index, normal multi-column index (NAME,EMAIL)
Full-text index word segmentation, segmentation (according to commas, spaces), Chinese support is not good, Chinese full-text index without MySQL engine. Common Solr,lucence,sphix

1. General Index

The normal index has only one function: Accelerated query

Create a normal index

View Code

Create a table and then index it Create Index  on table_name (column_name)

Delete Index Drop  on table_name;

View Index
Index  from table_name;

Note: If you are creating an index with a blob and TEXT type, you must specify length.

2. Unique index

Unique index has two functions: Accelerated Query and UNIQUE constraint (can contain null)

Create Tablein1 (Nidint  not NULLAuto_incrementPrimary Key, namevarchar( +) not NULL, emailvarchar( -) not NULL, Extratext,    Uniqueix_name (name)) CREATE TABLE+Unique index

View Code

Create a unique index Create Unique Index  on table name (column name)

To Delete a unique index Drop Unique Index  on table name

3. Primary KEY index

Primary key has two functions: Accelerated Query and UNIQUE constraint (non-nullable)

Create Tablein1 (Nidint  not NULLAuto_incrementPrimary Key, namevarchar( +) not NULL, emailvarchar( -) not NULL, Extratext,    Indexix_name (name))ORCreate Tablein1 (Nidint  not NULLauto_increment, namevarchar( +) not NULL, emailvarchar( -) not NULL, Extratext,    Primary Key(NI1),Indexix_name (name)) CREATE TABLE+Create a primary key

View Code

Create a primary key Alter Table Add Primary Key (column name);

Delete primary Key Alter Table Drop Primary Key ; Alter  Table name  modify  intdropprimarykey;

4. Combined Index

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

The application scenario is: Frequent simultaneous use of n columns for querying, such as: where N1 = ' Alex ' and N2 = 666.

 create  table   in3 (nid  int  not  null  auto_increment primary  key   varchar  ( 32 ) not  null  , email  varchar  (64 ) not  null   text  ) CREATE TABLE  

View Code

Create a composite index Create Index  on in3 (Name,email);

Note: The performance of a composite index is better than multiple single-index merges for simultaneous search of n conditions.

MySQL Basics (iii)