Mysql索引原理-简书深入理解mysql索引( 七 ) _生活百科

表：student
字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from student;
SQL案例：select * from student where age=18 and name like '张%';（全表扫描）

优化1

alter table student add index(name); //追加name索引

优化2

alter table student add index(age,name); //追加age,name索引

优化3

可以看到，index condition pushdown 优化的效果还是很不错的。
再进一步优化，我们可以把名字的第一个字和年龄做一个联合索引，这里可以使用 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);
explain select * from student where first_name='张' and age=18;
慢查询原因总结：全表扫描：explain分析type属性all 全索引扫描：explain分析type属性index 索引过滤性不好：靠索引字段选型、数据量和状态、表设计频繁的回表查询开销：尽量少用select *，使用覆盖索引
4.3 分页查询优化
4.3.1 一般性分页
一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：
SELECT * FROM 表名 LIMIT [offset,] rows
第一个参数指定第一个返回记录行的偏移量，注意从0开始；第二个参数指定返回记录行的最大数目；如果只给定一个参数，它表示返回最大的记录行数目；
如果偏移量固定，返回记录量对执行时间有什么影响？
select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;
结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。
如果查询偏移量变化，返回记录数固定对执行时间有什么影响？
select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;
结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）
4.3.2 分页优化方案
第一步：利用覆盖索引优化
【Mysql索引原理-简书深入理解mysql索引】select * from user limit 10000,100;
select id from user limit 10000,100;
第二步：利用子查询优化
select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。
Tags：