MySQL index primary, NORMAL, UNIQUE, fulltext differences and usage scenarios

Index

The database's index is like a book's directory, which can speed up the database query.

MySQL index has four kinds of primary, index, unique, Fulltext, of which primary, index, unique is a class, Fulltext is a class.

These four are single-column indexes, that is, they are all in a single row, so they are also called single-row indexes; However, an index can also be used on more than one column, called a composite index or a composite index.

Single-column index

Create a new test sheet

CREATE TABLE T_user INT  not NULL VARCHAR (notNULL);

(1) PRIMARY: PRIMARY key index. Indexed columns unique and cannot be empty； A table can have only one primary key index(primary key index is usually specified when building a table)

CREATE TABLE INT  not NULL VARCHAR (notNULL,PRIMARYKEY(ID))

(2) NORMAL: Normal index. There is no limit to the index column;

• Specifying when a table is built
  

  CREATE TABLE INT  not NULL VARCHAR (notNULL,INDEX username_index (USERNAME// Username a normal index to a column Username_index

• The ALTER statement specifies

  ALTER TABLE ADD INDEX // username General index for column U_index

• Delete Index

  DROP INDEX  on T_user  // Delete index in table T_user U_index

  (3)unique: Unique index. The value of the indexed column must be unique, but allowed to be empty;
• Specifying when a table is built

  CREATE TABLE INT  not NULL VARCHAR (notNULL,unique// add unique index to column username T_user

• The ALTER statement specifies

  ALTER TABLE ADD UNIQUE // Add a unique index to the column T_user U_index

• Delete Index

  DROP INDEX  on T_user

(4) Fulltext: The index of the full-text search. Fulltext works best when searching for a very long article. Used in relatively short text, if the one or two lines of the word, the normal INDEX can also. The new and deleted indexes are the same as above and are no longer listed here ... Combined index (composite index)

Create a new table

CREATE TABLE INT  not NULL VARCHAR (+ notNULLvarchar(Ten),  PRIMARYKEY(ID))

A composite index is the addition of multiple columns to a single index, such as a new table T_user, where we create a composite index for Username+city+phone

ALTER TABLE ADD index name_city_phone (username,city,phone)  // Combined General index ALTERTABLE ADD UNIQUE // Combined unique index

Such a combination of indexes, in fact, is equivalent to the establishment of ( useranme,city,phone username,city Username,phone) of three indexes. Why is there no index (city,phone)? This is because the MySQL combo query "The result of the leftmost prefix ". The simple understanding is that it only starts from the leftmost combination. The combined index is not used for query statements that contain these three columns: A query statement that is used to create a combined index

SELECT *  fromT_userwhereUSERNAME="Parry" andCity=Guangzhou andPHONE=" the"SELECT *  fromT_userwhereUSERNAME="Parry" andCity="Guangzhou"SELECT *  fromT_userwhereUSERNAME="Parry" andPHONE=" the"

Such a query statement is not used to create a composite index

 select  *   t_user where  city=  "Guangzhou" and  phone=  "180   select   *  from  t_user where  City=   "Guangzhou"  select  *  from  t_user where  Phone=  "180 " 

The index is insufficient (1) The index improves the speed of the query, but reduces the speed of INSERT, UPDATE, delete, because when inserting, modifying, deleting the data, it is also necessary to manipulate the index file at the same time, (2) The index is not seen will occupy a certain amount of disk space.      Index usage considerations (1) As long as the column contains null values will not be included in the index, the composite index as long as there is a column containing null values, then this column is not valid for the composite index, so we should design the database is not the default value of the field is null; (2) Use a short index if it is possible to specify a length for the index, for example: a varchar (255) column, but only 20 bits of the actual stored data, the index should be created with a length of 20, rather than default. As follows

ALTER TABLE Add INDEX U_index (USERNAME (ALTERTABLEaddINDEX u_index (USERNAME)

Using a short index will not only improve query speed, but also save disk operations and I/O operations.

(3) Index column sorting

MySQL uses only one index when querying , so if the WHERE clause already uses an index, the column in order by does not use the index, so order by try not to include more than one sort of column, and if you want to sort more columns, It is best to use composite indexes.

(4) Like statement

In general, it is not encouraged to use like, if not used, it is important to note that like "%aaa%" does not use indexes, but like "aaa%" uses indexes.

(5) Do not use not in and <> operation

Index method Hash and Btree comparison (1) hash

　　　　For peer comparison, such as "=" and "<=>"

(2) BTREE

Btree index look at the name to know the index is stored in a tree structure, usually used in such as "=,>,>=,<,<=, between, like" and other operator query efficiency is high;

By comparison, we often use Btree index method, of course, MySQL default is the Btree way.

　　

MySQL index primary, NORMAL, UNIQUE, fulltext differences and usage scenarios