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

I. introduction of MySQL index

Index is a kind of data structure, which provides users with high-performance query with its unique way of recording data. The index is like a catalogue of Xinhua dictionaries, through which we can quickly find the data we want to find.

Second, the general index mainly used by MySQL: the basic index type in MySQL, which only has the function of accelerating query, allowing duplicate values and null values to be inserted in the columns that define the index. Primary key index: there are two functions: accelerated query and unique constraint (cannot contain null) unique index: there are two functions: accelerated query and unique constraint (can include null) combined index: combined index is the combination of n columns into an index 3, general index 1, create index part1: create index create table user (nid int not null auto_increment primary key, name varchar (50) not null when creating a table Passwd varchar (100) not null, extra text, index idx1 (name))! [] (https://s1.51cto.com/images/blog/201809/14/39334e56fbb24560becb2c8a0ae9cc98.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

Note: for CHAR,VARCHAR, length can be less than the actual length of the field; for BLOB and TEXT, length must be specified.

Part2: create index create index idx2 on user (name); 2. View index show index from user;3, delete index drop index idx2 on user; IV, unique index 1, create index part1: create index create table user (nid int not null auto_increment primary key, name varchar (50) not null, passwd varchar (100) not null, extra text, unique idx1 (name)) part2: create index create unique idx2 on user (name) 2. View index show index from user;3, delete index drop index idx2 on user; 5, primary key index 1, create primary key index part1: create index create table user (nid int not null auto_increment, name varchar (50) not null, passwd varchar (100) not null, extra text, primary key (nid), index idx1 (name)) when creating a table

Part2: creating an index

Alter table idx2 add primary key (nid); 2. Delete primary key alter table user drop primary key;alter table user modify nid int, drop primary key; VI, composite index 1, create composite index part1: create index create table user (nid int not null auto_increment primary key, name varchar (50) not null, passwd varchar (100) not null, index idx (nid,name,passwd)) part2: create index create index idx on user (nid,name,passwd) 2. View index show index from user;3 and delete index drop index idx on user

The combined index query follows the leftmost match: for example, query:

Nid and name: using indexes

Nid and passwd: using indexes

Passwd: index not applicable

VII. Overlay index

For the above four indexes commonly used in MySQL, the order of looking for data is to look for data in the index first, and then look for it in the data table. If a query can be completed only in an index without looking for it in a data table, such an index is called an override index. An overlay index must require that the columns of the index be stored, so only the btree index can use a more efficient override index.

View overlay index (Using index)

VIII. Implementation of the plan

Explain is used to display SQL execution information parameters, and SQL can be optimized according to reference information.

Mysql > explain select * from T1 +-- + | id | select_type | Table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 2 | 100.00 | NULL | +-+-- -+-+ 1 row in set 1 warning (0.01 sec)

Interpretation of the EXPLAIN column:

Select_type: query type

Table: the name of the table being accessed

Type: connection type, performance: all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

Possible_keys: displays the indexes that may be applied to this table, or the appropriate statement after the where statement

Key: the index actually used

Key_len: the byte length of the index actually used

Rows: estimate the number of rows to read in order to find what you need

Extra: description information is as follows

Using index: the column data to be queried uses only the information in the index and does not read the information in the data table

Using temporary:mysql needs to create a temporary table to store the results, which usually occurs on the order by of different column sets rather than group by, in which case the query needs to be optimized

Using filesort: this means that mysql sorts the results using an external index instead of reading rows from the table in indexed order. Mysql has two file sorting algorithms, both of which can be done in memory or on disk. Explain won't tell you which file sorting mysql will use, nor will it tell you whether the sorting will be done in memory or on disk.

Range checked for each record (index map: #): no suitable index was found, so for each combination of rows from the previous table, # is the bitmap indexed in the possible_keys column and is redundant.

For more details, see the MySQL official documentation.

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.