MySQL-索引优化与查询优化

最新推荐文章于 2025-04-30 14:41:06 发布

zl979899

最新推荐文章于 2025-04-30 14:41:06 发布

阅读量991

点赞数 23

分类专栏： DataBase 文章标签： mysql 数据库服务器

本文链接：https://blog.youkuaiyun.com/zl979899/article/details/122800221

版权

DataBase 专栏收录该内容

22 篇文章

订阅专栏

SQL调优

1. 简单概括

索引失效、没有充分利用到索引 ---索引建立
关联查询太多JOIN（设计缺陷或不得已的需求） ---SQL优化
服务器调优及各个参数设置（缓冲、线程数等） ---调整my.cnf
数据过多 ---分库分表

2. 物理查询优化和逻辑查询优化

物理查询优化是通过索引和表连接方式等技术来进行优化
逻辑查询优化是通过SQL等价变换提升查询效率，就是换一种查询执行效率更高的写法

索引失效案例

1. 非索引字段全词匹配

2. 最佳左前缀法则

一个索引可以包括16个字段
对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
如果查询条件中没有使用定义索引字段中的第1个字段时，多列（或联合）索引不会被使用。

3. 主键插入顺序

当插入数据的主键忽大忽小时，会造成页面分裂和记录移位，产生性能损耗
最好让插入记录的主键值依次递增，让主键具有AUTO_INCREMENT，存储引擎自己为表生成主键

4. 计算、函数、类型转换(自动或手动)导致索引失效

5. 类型转换导致索引失效

假设字符串类型name字段上有索引

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

6. 范围条件右边的列索引失效

最好将范围查询条件放置语句最后

# 只用到age，classId是索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

7. 不等于(!= 或者<>)索引失效

8. is null可以使用索引，is not null无法使用索引

最好在设计数据表的时候就将字段设置为NOT NULL约束
比如将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串（''）
同理，在查询中使用not like也无法使用索引，导致全表扫描

9. like以通配符%开头索引失效

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

10. OR 前后存在非索引的列，索引失效

11. 数据库和表的字符集统一使用utf8mb4

统一字符集可以避免由于字符集转换产生的乱码
不同的字符集进行比较前需要进行转换会造成索引失效

12. 查询建议

对于单列索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

关联查询优化

1. 对于内连接来讲

如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表

2. join语句原理

Index Nested-Loop Join：被驱动表t2的字段a上有索引，先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录
Simple Nested-Loop Join：当表t2的字段b上没有索引，因此执行流程时，每次到t2去匹配的时候，就要做一次全表扫描
Block Nested-Loop Join：被驱动表上没有可用的索引，把表t1的数据读入线程内存join_buffer中，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。此种情况还是应该让小表当作驱动表

EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);

3. 是否使用join语句

如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引时可以使用
如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用
判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”

4. 驱动表的选择

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表

5. 几个结论

保证被驱动表的JOIN字段已经创建了索引
需要JOIN 的字段，数据类型保持绝对一致
LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数
INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略
能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询
衍生表建不了索引

子查询优化

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
对于返回结果集比较大的子查询，其对查询性能的影响也就越大
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL

排序优化

1. 在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优

2. order by不使用索引的情况

order by字段没有建立索引
order by时不limit，索引失效
order by时字段排列顺序错误，索引失效
order by时规则不一致, 索引失效（顺序错，不索引；递增递减方向反，不索引）
无过滤，不索引，order by字段没有进行条件过滤或者limit限制

3. filesort算法：双路排序和单路排序

双路排序（慢）：MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
单路排序（快）：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出

4. 优化策略

提高sort_buffer_size

SHOW VARIABLES LIKE '%sort_buffer_size%';

提高 max_length_for_sort_data
- 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘/O活动和低的处理器使用率
- 如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法
- 可以在1024-8192字节之间调整

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

Order by 时select * 是一个大忌。最好只Query需要的字段

GROUP BY优化

group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where效率高于having，能写在where限定的条件就不要写在having中了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢

优化分页查询

EXPLAIN SELECT * FROM student LIMIT 2000000,10;

方式一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a WHERE t.id = a.id;

方式二：适用于主键自增的表，可以把Limit 查询转换成某个位置的查询

SELECT * FROM student WHERE id > 2000000 LIMIT 10;

优先考虑覆盖索引

1. 覆盖索引概念

当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引
非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列
（即建索引的字段正好是覆盖查询条件中所涉及的字段）
简单概括：索引列+主键包含 SELECT 到 FROM之间查询的列

2. 覆盖索引的利弊

好处：避免Innodb表进行索引的二次查询（回表）；可以把随机IO变成顺序IO加快查询效率
弊端：索引字段的维护总是有代价的

给字符串添加索引

1. 前缀索引

默认情况，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
区分度越高越好。因为区分度越高，意味着重复的键值越少

2. 前缀索引对覆盖索引的影响

在使用前缀索引时，innoDB需要再回表一次确定查询结果是否正确
因此，使用前缀索引就用不上覆盖索引对查询性能的优化

索引下推

1. 索引下推概念

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式
ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数

2. 不使用ICP索引扫描的过程

storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行

3. 使用ICP扫描的过程

storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层
server 层：对返回的数据，使用table filter条件做最后的过滤

4. ICP的使用条件

只能用于二级索引(secondary index)
explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
ICP可以用于MyISAM和InnnoDB存储引擎
MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
当SQL使用覆盖索引时，不支持ICP优化方法

5. ICP的开启

set optimizer_switch='index_condition_pushdown=on';

普通索引 vs 唯一索引

1. 查询过程

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足where条件的记录
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
二者性能相差不大

2. change buffer介绍

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作
将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发merge外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作
将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率
change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max-_size来动态设置
这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%

3. 更新过程

当更新记录的目标页在内存中时，唯一索引与普通索引性能相差不大
当更新记录的目标页不在内存中时
- 对于唯一索引来说，需要将数据页读入内存，判断有没有冲突，插入这个值，语句执行结束
- 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了，效率更高

4. change buffer使用

change buffer只限于用在普通索引的场景下，而不适用于唯一索引
merge的时候才是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多，收益就越大
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，作用效果并不好

其它查询优化策略

1. EXISTS 与 IN 的选择

选择标准为小表驱动大表

SELECT * FROM A WHERE cc IN(SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS(SELECT cc FROM B WHERE B.cc=A.cc)

A表小就用EXISTS，exists实现逻辑

for i in A 
    for j in B 
        if j.cc==i.cc then.…

B表小就用IN， in实现逻辑

for i in B 
    for j in A 
        if j.cc==i.cc then...

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

二者本质没有区别，执行效率几乎相等
在InnoDB引擎中，如果采用COUNT（具体字段）来统计数据行数，要尽量采用二级索引。对于COUNT（*）和COUNT（1）来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计

3. 关于SELECT(*)

在解析的过程中，会通过查询数据字典将"*"按序转换成所有列名，会大大的耗费资源和时间
无法使用覆盖索引

4. LIMIT 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1

5. 多使用COMMIT

程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放
的资源而减少
COMMIT 所释放的资源：
- 回滚段上用于恢复数据的信息
- 被程序语句获得的锁
- redo / undo log buffer 中的空间
- 管理上述 3 种资源中的内部花费