Examples introducing the use of MySQL indexes

mysql tutorial Column examples explain the use of MySQL indexes

##More related free learning recommendations: mysql Tutorial(Video)

Usage examples of MySQL index

• 1. Slow query log
  • 2. Query Analyzer——explain
  • 3. Basic use of index
  • 4. Composite index
  • 5. Covering index

1. Slow query log

//查看是否开启慢查询日志
mysql> show variables like '%slow%';//临时开启慢查询日志
mysql> set global slow_query_log=ON;//查看是否开启慢查询日志
mysql> show variables like '%slow%';

//查询超过多少时间就可以记录，上面是如果超过10秒就要记录
mysql> show variables like '%long%';//改成一秒，如果超过一秒就写到慢日志里面去(一般一秒是最好的)mysql> set long_query_time=1;//查看日记存储方式，默认FILE
mysql> show variables like '%log_output%';// 慢查询日志文件所在位置
mysql> show variables like '%datadir%';

//响应时间是3秒，超过了原先设定的一秒
mysql> select sleep(3);

We When I went to check the folder, I found that it had been stored in the slow query log

This part explains how to find out through the slow log

is slower SQL, the following part will talk about why it is slow and how to make it faster.

2. Query Analyzer - explain

Function: Through this, you can know where the sql is slow and what aspects need to be optimized

columns : We create a

employee data table

create table employee(
	id int not null auto_increment primary key,
	name varchar(30) comment '姓名',
	sex varchar(1) comment '性别',
	salary int comment '薪资（元）',
	dept varchar(30) comment '部门');insert into employee(name, sex, salary, dept) values('张三', '男', 5500, '部门A');insert into employee(name, sex, salary, dept) values('李洁', '女', 4500, '部门C');insert into employee(name, sex, salary, dept) values('李小梅', '女', 4200, '部门A');insert into employee(name, sex, salary, dept) values('欧阳辉', '男', 7500, '部门C');insert into employee(name, sex, salary, dept) values('李芳', '女', 8500, '部门A');insert into employee(name, sex, salary, dept) values('张江', '男', 6800, '部门A');insert into employee(name, sex, salary, dept) values('李四', '男', 12000, '部门B');insert into employee(name, sex, salary, dept) values('王五', '男', 3500, '部门B');insert into employee(name, sex, salary, dept) values('马小龙', '男', 6000, '部门A');insert into employee(name, sex, salary, dept) values('龙五', '男', 8000, '部门B');insert into employee(name, sex, salary, dept) values('冯小芳', '女', 10000, '部门C');insert into employee(name, sex, salary, dept) values('马小花', '女', 4000, '部门B');insert into employee(name, sex, salary, dept) values('柳峰', '男', 8800, '部门A');

//通过explain解读他，后面加一个\G便于阅读
mysql> explain select * from employee where name='柳峰'\G;//扫描快捷
mysql> explain select * from employee where id=13\G;

Effect: As shown below, you can see why it was so slow before , requires four seconds response time

3. Basic use of index

mysql> show index from employee\G;//主键会默认建一个id索引

Improved index creation efficiency

//查询分析
mysql> explain select * from employee where name='柳峰';//创建普通索引
mysql> create index idx_name on employee(name);

//删除
mysql> drop index idx_name on employee;

Teacher’s list:

If you use like to search, the efficiency remains the same, so you have to look How do you use

4. Composite index

//查的时候可以看到一个主键索引
mysql> show index from employee\G;

Currently it is an all global scan

select * from employee where name ='柳峰';//查询分析
explain select * from employee where name ='柳峰'\G;

Create index

//创建索引
create index idx_name_salary_dept on employee(name,salary,dept);//查询分析
explain select * from employee where name ='柳峰'\G;

Verify that the index can be indexed if there is a name

// name和salary
mysql> explain select * from employee where name ='柳峰' and salary=8800\G;//name和dept
mysql> explain select * from employee where name ='柳峰' and dept='部门A'\G;

No Name cannot use index

mysql> explain select * from employee where  salary=8800;mysql> explain select * from employee where  dept='部门A';

5. Covering index

Following the above steps, we can see four indexes, the first one is

Primary key index , followed by compound 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 ID only

mysql> explain select id from employee  employee where id=11\G;mysql> explain select id from employee\G;

//查name,salary
mysql> explain select name,salary from employee;//查name,salary,dept
mysql> explain select name,salary,dept from employee;//因为没有sxe条件，所以只能做全部扫描type为null
mysql> explain select name,sex,salary,dept from employee;

The above is the detailed content of Examples introducing the use of MySQL indexes. For more information, please follow other related articles on the PHP Chinese website!