mysql-查询优化总结

一、limit查询慢的问题

针对下面慢查询语句有什么优化方法

select * from table limit 100000,10

MySQL并不是跳过offeset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的底下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

1、设计优化

1. 建立索引，触发索引。当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引，使查询时触发索引。
2. 减少回表。如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

SELECT id,title,content FROM items WHERE id IN 
       (SELECT id FROM items ORDER BY id limit 900000, 10);

3. 减少Limit扫描的行数。如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment

## 原来的查询条件是：
SELECT * FROM users ORDER BY id LIMIT 456891, 10;
## 优化后
SELECT * FROM users WHERE id > 456890 ORDER BY id LIMIT 0, 10;

疑问：上面的sql,那么怎么知道最大的Id呢。有多种方法，比如

1. 传给前端，在前端查询的时候带过来
2. 使用覆盖索引的方法查询

SELECT id FROM table where 条件=? ORDER BY id DESC LIMIT 0,1
## 注意条件一定是带有索引的，这样就不需要回表了。速度就很快

2、语法优化

2.1、limit m,n分页语句（低效）

## 优化-触发了索引还省去回表的操作
select id from table order by id limit 90000,10

2.2、limit m语句 （有局限）

原理：减少Limit扫描的行数

## 倒序
select * from table where deptno<100 order by deptno desc limit 0,10;

这个方法有个局限，就是需要deptno是连续的，也就是中间的记录不能有删除。（可以用逻辑删除代替真正的物理删除）

2.3、使用子查询优化

查询条件放到子查询中，且where后面全触发索引，子查询也只查主键ID，然后使用子查询中确定的主键关联查询其他的属性字段
原理：子查询减少回表操作

例如：先快速定位需要获取的id段，然后再关联：

SELECT
	a.* 
FROM
	表 1 a,
	( SELECT id FROM 表 1 WHERE 条件 LIMIT 100000, 20 ) b 
WHERE
	a.id = b.id;

二、in会不会走索引

1. 当in()种的数据很大时，不走索引
2. 当in()中存在子查询、格式化函数等同样也会使索引失效！
3. 当查询的列是char类型没有加引号，mysql优化器会自动给填充引号，同时也会导致索引失效

本文转载至：https://blog.csdn.net/qq_36276335/article/details/73824243

三、MySQL like模糊匹配是否走索引

答案是满足特定条件，会，如下:

like '%中国%'; 不会触发
like '%中国';  不会触发
like '中国%';  会触发

说明：最左前缀不仅仅适用于组合索引，还适用于varchar的like语句，但是要注意，只有like "XXX%"的情况走索引，like "%XXX"是不走索引的。

本文转自：https://blog.csdn.net/qq_33898609/article/details/119990397

假设有这么个需求，查询表中“名字第一个字是张，性别男，年龄为10岁的所有记录”。那么，查询语句是这么写的：

四、索引下推

mysq> select * from tuser where name like '张 %' and age=10 and ismale=1;

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。

图 1 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

总结
如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

本文转自：https://www.jianshu.com/p/d0d3de6832b9