MySQL索引的使用实例

本是古典何须时尚 2022-12-17 15:56 189阅读 0赞

前言

> 这是我听老师讲课做的笔记,考试要看的。 [这是视频地址][Link 1]  
> 作者：陈运智  
> 关注我的[csdn博客][csdn]，更多Linux笔记知识还在更新  
> 本人只在csdn写博客

[配套这篇文章观看效果更佳][Link 2]

### MySQL索引的使用实例 ###

*   *  一. 慢查询日志
     *  二. 查询分析器——explain
     *  三. 索引的基本使用
     *  四. 复合索引
     *  五. 覆盖索引

## 一. 慢查询日志 ##

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 1]

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

![在这里插入图片描述][20200724223058157.png]  
我们去文件夹里面查看时发现它已经被存入慢查询日记里面

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 2]

这部分写明了如何通过慢日志找出**比较慢**的SQL，后面部分要说**为什么慢**，如何能更快一点。

## 二. 查询分析器——explain ##

**作用**：通过这个可以知道查看sql慢在哪里，需要朝那些方面优化

列：我们创建一个**employee数据表**

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');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 3]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 4]

效果：如下图，可以看之前为什么那么慢,需要四秒响应时间

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 5]

## 三. 索引的基本使用 ##

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 6]

创建索引 效率提升

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 7]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 8]  
老师 事列：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 9]

如过用like检索，效率还是不变，所以要看你怎么用

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 10]

## 四. 复合索引 ##

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 11]

目前是all全局扫描

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 12]

创建索引

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 13]

验证有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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 14]

没有name就不能使用索引

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 15]

## 五. 覆盖索引 ##

按照上面步骤，我们可以看到四个索引，第一个是**主键索引**，后面是**复合索引**`name_salary_dept`

mysql> show index from employee;

![在这里插入图片描述][20200730140533137.png]  
如何触发

我们用id作为查询数据

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 16]

只查id

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 17]

//查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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 18]

> 本人博客：[https://blog.csdn.net/weixin\_46654114][csdn]  
> 本人b站求关注：[https://space.bilibili.com/391105864][https_space.bilibili.com_391105864]  
> 转载说明：跟我说明，务必注明来源，附带本人博客连接。

请给我点个赞鼓励我吧  
![在这里插入图片描述][20200507113619405.png_pic_center]

[Link 1]: https://edu.csdn.net/course/play/27328/370724
[csdn]: https://blog.csdn.net/weixin_46654114
[Link 2]: https://chenyunzhi.blog.csdn.net/article/details/109257969
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70]: /images/20221122/2ee1b419307f4f6f9b09cc8844fce686.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 1]: /images/20221122/0e8b31e9b9e14d1ca8021099d5ee8e54.png
[20200724223058157.png]: /images/20221122/2a032e97e0b64a9e9eb1fa290f9ba66e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 2]: /images/20221122/c2879127799d468baa9e46568be9ee83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 3]: /images/20221122/17e4f0cb25e048629ea98e66b5c12436.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 4]: /images/20221122/7bff066bae0c4fd1bef08a240e12e704.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 5]: /images/20221122/ca88336098f74d76bd254d32fc27747b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 6]: /images/20221122/9abc1a08f2974ed28370e63a4af184d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 7]: /images/20221122/eb783a647d49479dae5777c2bde6a3fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 8]: /images/20221122/dca771ebd0d74c7f8e009c00ee9917dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 9]: /images/20221122/c49fc87f739c4dd4ba9fd86765189074.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 10]: /images/20221122/06570d8874d14bc6bdb7e21a2b6549b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 11]: /images/20221122/28b6fd2b425f4b8d80be3ae974c7404a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 12]: /images/20221122/8b724bff24f345f9b561ddadc58cbcb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 13]: /images/20221122/81d22f12a749446e904e63cb9af6561d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 14]: /images/20221122/919882d1e43a4ff2881d06c8b37287a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 15]: /images/20221122/a8755e93e2f84a178a53d97b73078ed4.png
[20200730140533137.png]: /images/20221122/44efe8b14cc54a8d8c4f31fde152900e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 16]: /images/20221122/4e1b10f0e8e44047b8ee2a752d4711d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 17]: /images/20221122/8f68f5f245014fbb9fd2efa4738922bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NjY1NDExNA_size_16_color_FFFFFF_t_70 18]: /images/20221122/01fb5c44190e4dfba5fa1f057f25c633.png
[https_space.bilibili.com_391105864]: https://space.bilibili.com/391105864
[20200507113619405.png_pic_center]: /images/20221122/a4d34a346ebc42fb8821736a5aed25a7.png