MySQL（四）——索引的优化

目录

一、索引的执行分析1.单表查询执行过程以及优化—简单查询2.单表查询执行过程以及优化—简单查询+排序或分组3.多表查询—连接查询的执行过程及优化 二、索引失效三、SQL和索引的优化1、慢查询日志2、查看磁盘I/O读写的数据量3、show status

一、索引的执行分析

1.单表查询执行过程以及优化—简单查询

特点：同一个SQL查询过程中，只会命中一个索引 有student表如下： 表中的数据：

查询SID=1的学生信息的执行过程： 可以看到：possible_keys: PRIMARY,id ，在SQL执行过程中用主键索引进行查找 key: PRIMARY，在主键索引上一条数据就可以查到结果。查询name="ZhaoLei"的学生信息的执行过程： where 后面跟的是 Sname=‘ZhaoLei’ ,分析能够通过索引idx_name辅助索引树找到name=‘ZhaoLei’ 对应的主键，到主键索引上查询主键为1对应的一列数据并返回。查找Sname=‘ZhaoLei’ 对应的SID where 后面跟的是 Sname=‘ZhaoLei’ ,分析能够通过索引idx_name辅助索引树找到name=‘ZhaoLei’ 对应的主键，select查询的是SID，在辅助索引上已经满足条件，所以不需要再主键索引上再次查询。

2.单表查询执行过程以及优化—简单查询+排序或分组

创建一个订单表 插入数据： 查看表如下： 从结果可以看出，该表没有添加任何索引

查询userid=1，按照日期升序排序：

给userid创建索引 给date创建索引 创建联合索引

3.多表查询—连接查询的执行过程及优化

有两个表student和SC 联表查询分析： 在使用多表进行连接查询时，MySQL首先会判断哪个表小（表小指的是行数少） 连接查询时，小表总是要整表搜索，建索引是没有用的，大表创建索引可以提高查询效率，小表决定了查询次数，小表决定了查询时间。

在连接查询中，大小表的角色是不一定的，没有where字句，那么就按表的行数来决定，如果有where子句，就按条件过滤完的行数来定大小表！！

二、索引失效

like后面的通配符在前面，索引会失效

select * from student where Sname like 'z%'; //索引有效 select * from student where Sname like '%z'; //索引失效

mot in、!=、or、函数、类型转换、未使用联合索引第一列等都无法使用索引

三、SQL和索引的优化

1、慢查询日志

MySQL可以设置慢查询日志，当SQL执行时间超过设定阈值，SQL就会被记录到慢查询当中，通过查看日志，用explain分析SQL执行计划来判定效率低下的原因。

SQL执行超过阈值时间参数可以设置：long_query_time（单位：秒） 查看SQL：show variables like ‘long%’; 慢查询日志存放在MySQL路径下，默认名称是：host_name_show.log

2、查看磁盘I/O读写的数据量

3、show status

MySQL提供show status 命令，查看MySQL运行参数，可以查看select、insert、delete、update语句执行频率，慢查询次数，事务的提交和回滚的次数。