MySQL index and query optimization operations (1)

The following articles mainly describe the actual operation process of MySQL index and query optimization. We all know that MySQL index and query optimization have a high proportion in actual operations, so a deeper understanding of it will gain some gains in your future studies.

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 MySQL index: Used to index the text fields (char, varchar, text) of large tables.

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 a MySQL 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.

Automatically adds the use of domains in multiple MySQL indexes:

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 data is part of the MySQL index. MySQL does not need to query the data in the entire table, but only needs to view the index: select id from custo AND mer;

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 where sales. 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 % ';