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

The editor will share with you how to use the MySQL index. I hope you will gain a lot after reading this article. Let's discuss it together.

Examples of using MySQL index

one。 Slow query log

two。 Query Analyzer-- explain

three。 Basic use of index

four。 Composite index

five。 Overlay index

one。 Slow log / / check whether slow log is enabled mysql > show variables like'% slow%';// temporarily enable slow log mysql > set global slow_query_log=ON;// check whether slow log mysql > show variables like'% slow%' is enabled

/ / query how long it takes to record. If it takes more than 10 seconds, mysql > show variables like'% long%';// will be changed to one second, and if it exceeds one second, it will be written to the slow log (usually one second is the best) mysql > set long_query_time=1;// to view the diary storage mode. By default, FILEmysql > show variables like'% log_output%';// slow query log file location mysql > show variables like'% datadir%'

/ / the response time is 3 seconds, which exceeds the previously set one second mysql > select sleep (3)

When we checked the folder, we found that it had been stored in the slow query diary.

This section shows how to find the slower SQL through the slow log, and the later part will talk about why it is slow and how it can be faster.

two。 Query Analyzer-- explain

Purpose: through this, you can know where sql is slow and which aspects you need to optimize.

Column: we create an employee data table

Create table employee (id int not null auto_increment primary key, name varchar (30) comment 'name', sex varchar (1) comment 'gender', salary int comment 'salary (yuan)', dept varchar (30) comment 'Department'); insert into employee (name, sex, salary, dept) values ('Zhang San', 'male', 5500, 'Department A') Insert into employee (name, sex, salary, dept) values ('Li Jie', 'female', 4500, 'Department C'); insert into employee (name, sex, salary, dept) values ('Li Xiaomei', 'female', 4200, 'Department A'); insert into employee (name, sex, salary, dept) values ('Ouyang Hui', 'male', 7500, 'Department C') Insert into employee (name, sex, salary, dept) values ('Li Fang', 'female', 8500, 'Department A'); insert into employee (name, sex, salary, dept) values ('Zhang Jiang', 'male', 6800, 'Department A'); insert into employee (name, sex, salary, dept) values ('Li Si', 'male', 12000, 'Department B') Insert into employee (name, sex, salary, dept) values ('Wang Wu', 'male', 3500, 'Department B'); insert into employee (name, sex, salary, dept) values ('Ma Xiaolong', 'male', 6000, 'Department A'); insert into employee (name, sex, salary, dept) values ('Dragon five', 'male', 8000, 'Department B') Insert into employee (name, sex, salary, dept) values ('Feng Xiaofang,' female', 10000, 'Department C'); insert into employee (name, sex, salary, dept) values ('Ma Xiaohua', 'female', 4000, 'Department B'); insert into employee (name, sex, salary, dept) values ('Liu Feng', 'male', 8800, 'Department A')

/ / interpret him through explain, followed by a\ G to make it easy to read mysql > explain select * from employee where name=' Liu Feng'\ Gram / scan Quick mysql > explain select * from employee where id=13\ G

Effect: as shown in the following figure, you can see why it was so slow before, and it takes four seconds to respond.

three。 The basic use of the index is mysql > show index from employee\ G dominating / primary key will build an id index by default

Improve the efficiency of creating indexes

/ / query and analyze mysql > explain select * from employee where name=' Liufeng'; / / create a common index mysql > create index idx_name on employee (name)

/ / Delete mysql > drop index idx_name on employee

Teacher's list:

If you use like search, the efficiency remains the same, so it depends on how you use it.

four。 When you look up the composite index / / you can see a primary key index mysql > show index from employee\ G

Currently it is an all global scan.

Select * from employee where name = 'Liufeng'; / / query analysis explain select * from employee where name = 'Liufeng'\ G

Create an index

/ / create an index create index idx_name_salary_dept on employee (name,salary,dept); / / query and analyze explain select * from employee where name = 'Liufeng'\ G

Verify that name can be indexed.

/ / name and salarymysql > explain select * from employee where name = 'Liufeng' and salary=8800\ G; and deptmysql > explain select * from employee where name = 'Liufeng' and dept=' Department A'\ G

Indexes cannot be used without name

Mysql > explain select * from employee where salary=8800;mysql > explain select * from employee where dept=' Department A'

five。 Overlay index

Following the above steps, we can see four indexes, the first is the primary key index, followed by the composite index name_salary_dept

Mysql > show index from employee

How to trigger

We use id as query data

Mysql > select * from employee;mysql > select * from employee where id = 11

Check only id

Mysql > explain select id from employee employee where id=11\ GmistMySQL > explain select id from employee\ G

/ / check name,salarymysql > explain select name,salary from employee;//, check name,salary,deptmysql > explain select name,salary,dept from employee;//, because there is no sxe condition, so you can only scan all the type: nullmysql > explain select name,sex,salary,dept from employee.

After reading this article, I believe you have a certain understanding of how to use the MySQL index, want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.