MySQL索引优化与查询优化

MySQL索引优化与查询优化

---

前言

sql优化可以分为物理查询优化和逻辑查询优化

• 物理查询优化是通过索引和表连接方式等技术来进行优化
• 逻辑查询优化就是通过SQL等价变化提升查询效率

一、索引失效

• 计算、函数、类型转换、会导致索引失效
• 范围条件右边的列索引失效（在联合索引中尽量把等值的数据放在靠前的位置，将范围查询的条件放置最后）
• 不等于索引失效
• is null可以使用索引 is not null 不可以使用索引
• like以通配符%开头导致索引失效
• OR前后存在非索引的列，索引失效
• 数据库与表的字符集要统一使用，不同的字符集进行比较前需要进行转化会造成索引失效
• ORDER BY时不limit，索引失效或时序规则不一致（顺序错，方向相反都不索引）

二.关联查询优化

驱动表就是主表，被驱动表就是从表，非驱动表

1.外连接

• 小表驱动大表：LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

2.内连接

• 对于内连接来说，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为驱动表出现。如果两个表的连接条件都存在索引的情况下，会选择数据量较少的作为驱动表
• 两表连接字段类型必须一致：两个表JOIN字段数据类型保持绝对一致。防止自动类型转换导致索引失效

3.子查询优化

因为子查询的效率不高，我们可以使用连接（JOIN）查询来代替子查询，连接查询不需要建立表，其速度比子查询快，查询可以使用索引。

4.排序优化

• SQL中，可以在WHERE子句和ORDER BY 子句中使用索引，目的是在WHERE中避免全表扫描，在ORDER BY 子句中避免使用 FileSort 排序。
• 尽量使用index完成ORDER BY 排序
• 当范围条件和group by或order by同时出现时，优先观察体条件字段的过滤数量，如果过滤数据足够多，而需要的排序数据并不多时，有限把索引放在范围字段。

5.GROUP BY优化

• GROUP BY 使用索引几乎和ORDER BY 一致
• GROUP BY先排序在分组，按照最佳左前缀法则

三.覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引
索引列＋主键包含select到from之间查询的所有列
覆盖索引可以减少树的搜索次数，显著提升查询性能

四.索引条件下推（ICP）

IPC可以把WHERE条件放到存储引擎筛选，使用索引来筛选数据，并且只有在满足这一条件时才从表中读出。
启用ICP，可以有效地减少存储引擎必须访问基表的次数和MYSQL服务器必须访问存储引擎的次数。

#打开索引下推
SET optimizer_switch = 'index_condition_pushdown =off';
#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown =on';

五.其他查询优化策略

COUNT(*)与COUNT(具体字段)效率

• COUNT(*)和COUNT(1)都是对所有结果进行COUNT，本质上并没有区别，如果有WHERE子句，则是对所有符合条件的数据进行统计；如果没有WHERE子句，则对数据表的数据进行统计。
• 如果采用COUNT(具体字段)来统计数据，尽量采用二级索引。

关于select(*)

在查询中建议明确字段，不要使用*作为查询的字段，因为MySQL在解析的过程中，会通过查询数据字典转化为所有列名，会消耗资源和浪费时间，也无法使用覆盖索引

关于LIMIT 1

针对于全表扫描的sql，确定结果只有一条或者只需要一条，加上LIMIT 1，当找到结果时就回停止扫描，会加快查询速度。