MySQL index and optimization Query

MySQL index and optimization Query

An appropriate index can accelerate the query speed. There are four types: primary key, unique index, full-text index, and common index.

Primary Key: It is unique and has no null value.
Create table pk_test (f1 int not null, primary key (f1 ));
Alter table customer modify id int not null, add primary key (id );

Normal index: Repeated values are allowed.
Create table tableanme (fieldname1 columntype, fieldname2 columntype, index [indexname] (fieldname1 [, fieldname2. ..]);
Create table tablename add index [indexname] (fieldname1 [fieldname2. ..]);
Alter table slaes add index (value );

Full-text index: Used to index the text fields (char, varchar, text) of a large table. The syntax is the same as that of normal indexes-fulltext.
Full-text index: create table ft2 (f1 varchar (255), fulltext (f1 ));
Insert into ft2 values ('wating for the bvarbariands'), ('in the heart of the country'), ('the master of petersburg '), ('writing and being'), ('Heart of the beast '), ('master master ');
Select * from ft2 where match (f1) against ('master'); // match ()-match field; against () match value.
Mysql ignores some words, causing query errors:. more than 50% of domain words; B. A word with less than three words; c. A pre-defined list of mysql, including.
Query statement: select * from ft2 where match (f1) against ('the master'); // It is different from the expected result
Correlation score query: select f1, (match (f1) against ('master') from ft2;
New Function of mysql4-boolean full-text query: select * from ft2 where match (f1) against ('+ master-pet' in boolean mode); // operator type +-<> () ~ *"

Unique index: Except for duplicate records, other indexes are the same as normal indexes.
Create table ui_test (f1 int, f2 int, unique (f1 ));
Alter table ui_test add unique (f2 );
Create an index for the region (varchar, char, blob, text): alter table customer add index (surname (10 ));
Automatically add domain: each time a record is inserted, the value of one domain is automatically added. It can only be used for one domain and the domain has an index.
Create table tablename (fieldname int auto_increment, [fieldname2. ..,] primary key (filedname ));
Alter table tablename modify fieldname columntype auto_increment;
The last_insert_id () function returns the auto-added value of the latest insert.
Select last_insert_id () from customer limit 1;
An error occurs when the function is connected at the same time.
Reset the automatically added counter value:
Create table tablename (fieldname int auto_increment, [fieldname2. ..,] primary key (filedname) auto_increment = 50 );
Alter table tablename auto_increment = 50;
If the reset value is smaller than the existing value, the counter is automatically increased from the maximum value in the record, for example, the IDs in the customer table already include 1, 2, 3, 15, 16, and 20. When the counter value is set to 1, the next inserted record starts from 21.
Automatically increase the counter's out-of-bounds: valid value range: 1 ~ The power of 2 is 127, that is, 2147483647. If this value is exceeded (including negative values), mysql automatically sets it to the maximum value, which leads to a duplicate key value error.
The use of fields in Multi-column indexes is automatically added:
Create table staff (rank enum ('Employee', 'manager', 'tractor ') not null, position varchar (100), id int not null auto_increment, primary key (rank, id ));
Insert into staff (rank, position) values ('Employee', 'cleaner'), ('cotractor ', 'network maintenance'), ('manager ', 'sales manager ');
When you add some data to each level, you will see the auto-increment phenomenon that you are familiar:
Insert into staff (rank, position) values ('Employee', 'cleaner1'), ('Employee', 'network maintenance1'), ('manager ', 'sales manager1 ');
In this case, you cannot reset the auto-increment counter.
Delete or modify an index: You must delete the index before redefinition.
Alter table tablename drop primary key;
Alter table drop index indexname;
Drop index on tablename;

Efficient use of indexes: What Will indexes bring to us?
1) obtain the matched rows in the where clause: select * from customer where surname> 'C ';
2) When searching for max () and min () values, mysql only needs to find the first and last values in the sorted index.
3) The returned part is part of the index. mysql does not need to query the data of the entire table, but only needs to view the index: select id from customer;
4) where order by is used for the domain: select * from customer order by surname;
5) You can also accelerate the table connection: select first_name, surname, commission from sales, sales_rep wheresales. sales_rep = sales_rep.employee_number and code = 8;
6) in the case of wildcards: select * from sales_rep where surname like 'ser % ';
This case does not work: select * from sales_rep where surname like '% ser % ';
Select index:
1 .) when an index is required for a query (such as the condition field in the where clause), an index is created. If the first character is not used, an index is created for an index.
2.) The fewer rows returned by the created index, the better the primary key. Enumeration indexes are useless.
3.) use a short index (for example, the first 10 characters of the name instead of all ).
4.) do not create too many indexes. Although it accelerates the query speed, it increases the time for updating and adding records. If the index is rarely used in the query, but no index only slightly affects the speed, do not create an index.
Leftmost rule: This occurs in multiple indexed domains. mysql uses them sequentially from the leftmost of the index list.
Alter table customer add initial varchar (5 );
Alter table customer add index (surname, initial, first_name );
Update customer set initial = 'X' where id = 1;
Update customer set initial = 'C' where id = 2;
Update customer set initial = 'V' where id = 3;
Update customer set initial = 'B' where id = 4;
Update customer set initial = 'n' where id = 20;
Update customer set initial = 'M' where id = 21;
If these three fields are used in the query, the index is used to the maximum extent: select * from customer where surname = 'clegg 'and initial = 'X' and first_name = 'yvonne ';
Or use most of the indexes: select * from customer where surname = 'clegg 'and initial = 'X ';
Or just surname: select * from customer where surname = 'clegg ';
If the leftmost rule is broken, the following example will not use the index: select * from customer where initial = 'X' and first_name = 'yvonne ';
Select * from customer where initial = 'X ';
Select * from customer where first_name = 'yvonne ';
Select * from customer where surname = 'clegg 'and first_name = 'yvonne ';

Explain-explain how mysql uses indexes to process select statements and connect tables.
After you enter explain select * from customer;, a table appears. The meanings of the rows are as follows:
Table-shows the table to which this row belongs;
Type-an important column that shows the connections used. The values are const, eq_ref, ref, range, index, and all in sequence, which are described in detail below;
Possible_keys-indexes that can be applied to this table. If it is null, no indexes are available;
Key-actually used index. If it is null, the index is not used;
Key_len-the length of the index. The shorter the index, the better, without losing precision;
Ref-It indicates which column of the index is used. If possible, it is a constant;
Rows-number of returned data rows;
Extra-for more information about how mysql parses the query, the following describes in detail.

Description of the extra row: distinct-mysql no longer searches if the row that matches the domain row is found;
Not exists-mysql optimizes left join. Once the row matching left join is found, it no longer searches;
Range checked for each-the ideal index is not found, and each row in the preceding table is combined at a time;
Record (index map: #)-check which index to use and use it to return rows from the table. This is the slowest way to use the index;
Using filesort-if you see this, you need to optimize the query. mysql requires additional steps to find out how to sort the returned rows. It sorts all rows based on the connection type and the row pointer that stores the sort key value and all rows matching the condition.
Using index-the column data is returned from a table that only uses the information in the index but does not read the actual row. This occurs when all the request columns of the table are the same index;
Using temporary-if you see this, you need to optimize the query. mysql needs to create a temporary table to query the storage results. This usually happens when order by is performed for multiple lists, rather than group;
Where used-the where clause is used to limit which rows match the next table or are returned to the user. If you do not want to return all rows in the table and the connection type is all or index, this may occur or the query may be faulty.

Type description:
System-The table has only one row, which is a special case of the const connection type;
Const-the maximum value of a record in a table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because mysql first reads this value and treats it as a constant;
Eq_ref-read a record from the Union of each record in the previous table. It is used to query all indexes that use the primary key or unique index;
Ref-only a portion that is not a primary key or a unique index is used. For each row in the preceding table, all records will be read from the table. This connection type depends heavily on the number of index matching records-the less the better;
Range-when an index is used to return rows in a range, such as> or <search;
Index-this connection type performs a full scan of each record in the preceding table (better than all, because the index is generally smaller than the table data );
All-This join type is used to perform a full scan for each record in the preceding table. This is poor and should be avoided as much as possible.
For example, create index sales_rep on sales (sales_rep); // you can compare the changes before and after the index is created.
Explain select * from sales_rep left join sales on sales. sales_rep = sales_rep.employee_number;

The result is as follows:

Table	Type	Possible_keys	Key	Key_len	Ref	Rows	Extra
Sales_rep	All	Null	Null	Null	Null	5
Sales	Ref	Sales_rep	Sales_rep	5	Sales_rep.employee_number	2

The result indicates that the sales_rep table has a bad connection type-all, and no index is used. The number of rows to be queried is 5; the sales connection type is ref, and the available index is sales_rep, the sales_rep index is also used. The length of the index is 5, the corresponding column is employee_number, and the number of rows to be queried is 2. Therefore, the table is queried 5 × 2 times in this query.

View index information: show index from tablename;
Column Description: table-the name of the table being viewed; non_unique-1 or 1.0 indicates that the index cannot contain duplicate values (primary key and unique index); 1 indicates yes; key_name-index name; seq_in_index-sequence of columns in the index, starting from 1; column_name-column name; collation-a or null; a indicates that the index is sorted in ascending order; null indicates that the column is not sorted; cardinality-number of unique values in the index; sub_part-if the entire column is indexed, the value is null; otherwise, the index size is represented by characters; packed-Indicates whether to pack; null-if the column can contain null, the value is yes; comment-comments.

This article permanently updates the link address: