一,问题分享
面试问题一、 如何判断是否为慢查询?
MySQL判断一条语句是否为慢查询语句,主要依据SQL语句的执行时间,它把当前语句的执 行时间跟 long_query_time 参数做比较,如果语句的执行时间 > long_query_time,就会把 这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s,该参数值可 以根据自己的业务需要进行调整。
面试问题二、如何判断是否应用了索引?
SQL语句是否使用了索引,可根据SQL语句执行过程中有没有用到表的索引,可通过 explain 命令分析查看,检查结果中的 key 值,是否为NULL。
面试问题三、应用了索引是否一定快?
用例:select * from user where id>0;
虽然使用了索引,但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树,进行了 全表扫描,此时索引就失去了意义。 而像 select * from user where id = 2; 这样的语句,才是我们平时说的使用了索引。它表示 的意思是,我们使用了索引的快速搜索功能,并且有效地减少了扫描行数。
查询是否使用索引,只是表示一个SQL语句的执行过程;而是否为慢查询,是由它执行的时间决定 的,也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。 我们在使用索引时,不要只关注是否起作用,应该关心索引是否减少了查询扫描的数据行数,如果 扫描行数减少了,效率才会得到提升。对于一个大表,不止要创建索引,还要考虑索引过滤性,过滤性好,执行速度才会快。
二,提高索引的过滤性
慢查询原因总结
- 全表扫描:explain分析type属性all
- 全索引扫描:explain分析type属性index
- 索引过滤性不好:靠索引字段选型、数据量和状态、表设计
- 频繁的回表查询开销:尽量少用select *,使用覆盖索引
实例 Step1: select * 导致会回表,全表查询
alter table student add index(name); //追加name索引
select * from student where age=18 and name like '张%';
优化 Step2: 增加复核索引
alter table student add index(age,name); //追加age,name索引
优化 Step3: 把名 字的第一个字和年龄做一个联合索引,这里可以使用 MySQL 5.7 引入的虚拟列来实现。
//为user表添加first_name虚拟列,以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
三,分页查询的优化
分页查询执行时间的实验数据这里不贴出来了, 就是简单的limit的应用,直接说结论:
1,在查询记录时,返回记录量低于100条,查询时间基本没有变化,差距不大。随着查询记录 量越大,所花费的时间也会越来越多。
2,在查询记录时,如果查询记录量相同,偏移量超过100后就开始随着偏移量增大,查询时间 急剧的增加。(这种分页查询机制,每次都会从数据库第一条记录开始扫描,越往后查询越慢,而 且查询的数据越多,也会拖慢总查询速度。)
优化方式: 通过覆盖索引优化,然后通过子查询去进行分页(黄色标记部分为覆盖索引的使用)
select * from user limit 10000,100; select * from user where id>= (select id from user limit 10000,1) limit 100;