【一起学习Mysql】全面分析Mysql查询优化

每日一句：想试试我的草莓味唇膏吗？

数据库优化的主要方向为：数据表优化、索引优化、查询优化，本文主要介绍的为查询优化相关。

如果对Mysql数据类型优化、Mysql索引优化想了解更多的小伙伴可以查看本系列中相关文章

查询优化

一般而言做查询优化主要有哪些方面：

• 查询的方式（单条查询还是复杂的关联查询）

• 查询了太多不需要的数据/返回所有的行

• 多表关联查询返回所有的数据

• SQL语句中的子查询、关联查询等使用不合理

• SQL优化器进行优化

查询方式选择

在查询数据的时候，如果SQL较为复杂，那么会选择执行一条复杂关联查询完成查询还是会进行拆分，使用多条简单SQL来进行查询呢？

• 复杂关联查询的优点和缺点
  • 优点：无需创建多个数据库链接、网络通信、查询解析等额外开销
  • 缺点：如果关联的表较多或者关联表的数据量较大，那么查询效率非常慢，数据库做大量的查询工作，如果数据库做分库分表，加大关联难度和重构
• 多条简单查询的优点和缺点
  • 优点：简单查询可以使用到缓存、更好的使用覆盖索引，如果后续对数据做分库分表，不会影响分库分表的扩展，在应用层做数据关联，都是在内存中做匹配，执行效率高。
  • 缺点：创建多个数据库连接，增加网络通信、查询解析等额外的开销
• 如何选择
  • 如果关联查询很复杂，关联了超过多张表，数据表的数据量较大，并且查询的效率非常慢，后续有做分库分表的计划，那么最好选择切分查询（多条简单查询），在内存中做匹配，相对效率更高，有利用后续做扩展。并且Mysql从设计上建立连接和断开连接都相对轻量级
  • 如果关联查询不是特别复杂，查询的数据表数据量较小，暂时没有分库分表的计划，当前的执行查询时间可以接受，数据表增量并不是特别大，可以选择一条关联查询来作为执行计划

Mysql如何执行一条SELECT

对SQL的查询进行优化，首先就是了解Mysql在执行一条查询语句的执行流程，才能更好的对SQL优化，以及了解Mysql会对这条SQL做什么事情

SQL优化器如何优化SQL

在整个查询的过程中，会对SQL对优化以及在查询优化阶段需要关注的就是SQL优化器了，会对SQL进行一系列的优化，并生成执行计划来进行查询

优化器会优化哪些查询

• 关联查询中，重新定义表关联顺序
• 等价变换规则（例如会将5=5这种常量表达式移除）
• 覆盖索引扫描查询
• 子查询优化
• 列表IN的查询优化（先对IN中的数据进行排序，然后使用二分的方式进行查询）

关联查询优化

• Mysql如何执行关联查询

  Mysql对所有的冠梁查询都执行嵌套循环关联操作，即Mysql从一个表中查询出来所有的数据，然后对另一个关联的表进行循环匹配，直到取出来所有匹配到的数据并加入到结果集

  例如：

  select a.name, b.age from tb1 a inner join tb2 b on a.id = b.id where a.name = '1';

  这句SQL使用伪代码可以表示为：

  outer1 = select tb1 where a.name = '1'
  for col1 in outer1:
  	outer2 = select tb2 where id = outer1.id
  	for col2 in outer2:
  		result_out.add(col1.name, col2.age)
  

  总体的查询流程：嵌套循环并回溯

• 优化器对关联查询优化

  Mysql优化器会对关联查询进行优化，根据关联查询的执行方式嵌套循环，那么如果外层循环的次数少，总的循环的次数就会变少，关联查询的效率就会变高，所以优化器的优化策略为：调整表关联的顺序，生成优化器认为最优的执行方案。即符合一个优化最常用的规则小表驱动大表（写SQL时也应该使用这种思想）

  优化器在执行的时候会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划。如何可能的话，Mysql会统计所有的关联顺序带来的成本，并选择一个最优的执行计划。

  另：不过Mysql选择的执行计划也不总是最优的，这个时候可以使用STRAIGHT_JOIN来影响优化器，保证表关联的顺序和SQL中的一致

• 优化器对排序优化

  • 普通查询的优化

    有两种方式来进行排序：索引或者文件排序

    索引本来就是有序的数据结构，如果有索引，则直接使用索引的排序即可

    如果没有索引，则需要Mysql使用文件排序来对数据进行排序：

    • **第一种：**当排序的数据量较小，可以加载到内存中进行排序，则将数据加载到内存中进行排序，效率较高
    • **第二种：**当排序的数据量较大，无法加载到内存中进行排序，则会借助磁盘进行排序，将大的数据量采用分治的思想，分成多个小的数据块，然后将每个小的数据块加载到内存中进行排序，然后将排序之后的数据保存到磁盘中，最后再合并起来并返回结果

  • 关联查询的优化

    在关联查询中需要进行排序，较为复杂，并且特定情况可以对这种SQL进行优化来提高查询效率

    • **第一种：**如果ORDER BY的列都是来自第一个表（优化器关联查询优化之后的第一个表），那么在查询的时候，就会进行排序。如果是这样，那么在EXPLAIN中可以看到只有Using filesort
    • **第二种：**如果ORDER By的列来自第二个表或者两个表都有，则执行排序的流程：先进行关联查询，将查询的结果放到临时表中，然后在临时表中进行排序，返回结果。在EXPLAIN中可以看到Using temporary,Using filesort

  **文件排序：**即使是完全在内存中排序以及借助磁盘进行排序都统称为"文件排序"

• Mysql返回结果到客户端

  Mysql在执行查询之后，不会等待所有的结果都查询完成才会开始返回，而是在查询到第一条结果之后，就会将该结果返回客户端，减少服务端需要存储的数据占用太大的内存

优化器的局限性

在同一个表上查询和更新

update tbl a set a.name = (select name from tb1 b where a.type = b.type);

这个SQL可以执行吗？

• 不可以执行。You can't specify target table 'tbl' for update in FROM clause

在《高性能Mysql》一书中232页，说明了这个观点，并且实验得到的结果确实是不能更新，不过该书中没有说明为什么不能更新。

原因：根据查询资料和Mysql中锁的关系，之所以不能对同一个表进行查询并更新，在查询的时候会给表中的数据加上共享锁，在更新的时候需要给表中的数据加上排他锁，这两个锁是互斥的，所以无法同时执行

• 解决方式：

  • 子查询1：

  update tbl a  set a.name = 
     (select * from 
  		(select name from tb1 b where a.type = b.type)
  	);
  

  • 子查询2：

  update tb1 a inner join (select name, type from tb1) b on a.type = b.type set a.name = b.name
  

  **原理：**利用了子查询的特性（创建临时表），将查询到的数据放入到临时表，然后原表进行排它锁的时候，不会影响临时表，可以进行进行匹配

优化器的提示（hint）

一些特定的场景，可能优化器的优化执行计划方式并不是最好的执行计划，可以通过一些特定的关键字来影响优化器。

• STRAIGHT_JOIN

  放置在SELECT关键字之后，也可以放置在关联表的名字之间。

  • 放置在SELECT关键字之后表示这条SQL语句中所有表的连接顺序按照语句中出现的顺序进行关联查询
  • 如果放在两个关联表名字之间，表示只是固定这两个表的查询顺序

• SQL_SMALL_RESULT和SQL_BIG_RESULT

  这两个提示只对SELECT语句生效，提示优化器对于GROUP BY或者DISTINCT查询如何是使用临时表以及排序。SQL_SMALL_RESULT表示结果集很小，可以放在内存临时表，SQL_BIG_RESULT表示结果集较大，使用磁盘临时表做排序等操作

• SQL_CACHE和SQL_NO_CACHE

  一般情况下，查询的结果集会在查询之后不仅会返回给客户端，也会保存在缓存中，下次相同的查询则直接查询缓存就可以返回数据，在更新数据的时候会清除缓存中的数据抱枕数据的一致性。（Mysql8.0取消了缓存这个逻辑）

  放置在SELECT语句之后

  小知识点：

  在测试某条SQL语句性能的时候，一般需要使用SQL_NO_CACHE或者重启Mysql，否则第二次查询的时候会查询缓存，导致测试的结果不准确

• FOR UPDATE和LOCK IN SHARE MODE

  这个不是真正的优化器提示，这两个是SELECT语句的锁机制，但是只对实现了行锁的存储引擎有效（INNODB）FOR UPDATE加的是排它锁，其他的读和写操作都会被阻塞，LOCK IN SHARE MODE实现的共享锁，读操作可以共享，写操作会阻塞。

• USE INDEX、IGNORE INDEX和FORCE INDEX

  USE INDEX和FORCE INDEX基本相同，都是表示让优化器使用特定的索引进行查询而不是全表扫描，不过FORCE INDEX会强制走索引，就算这个索引的非常的慢。IGNORE iNDDEX表示在查询的时候，不使用某个索引

  如果发现优化器使用了错误的索引则可以使用USE INDEX和FORCE INDEX指定查询使用特定的索引

  • 使用姿势

  SELECT * FROM TABLE FORCE INDEX(index_name) WHERE ...
  

  SELECT * FROM tbl a force index(index_name) left join tbl  on a.id = b.id where...
  

  一般来讲，Mysql的优化器可以根据策略来优化SQL，生成比较优的执行计划，比较少的去影响优化器的执行

优化特定类型查询

一些情况下的SQL语句可以通过特定的方式和小技巧还可以做一些优化，提高执行效率，简单来说就是通过小技巧来实现相同功能

优化count()查询

• count(1)、count(*)和count(字段)哪个更快呢

  • count(字段)：查询该字段中非NULL值的行数，在统计的过程中需要再判断非空
  • count(*)：是统计行数的标准写法，会统计所有的行数（包括NULL），Mysql对count(*)做了优化，会查询最小的一个索引树进行统计（在没有where条件的情况下）
  • count(1)：和count(*)基本一致

  count(*)≈count(1)>count(字段)

• 如何优化count()

  没有where条件的统计，则直接使用count(*)和count(1)都是较好的选择，有where条件的统计则使用小技巧会统计的更快

  select count(*) from test where id > 5;

  这个sql需要扫描所有ID>5的数据然后返回结果集。

  • 优化的写法

  select (select count(*) from test) - count(*) from test where id <= 5;

  这个sql只需要扫描id<=5的数据即可，扫描的行数非常少，速度比较快。通过总数-不符合的数据量=需要匹配的数据量。

  Innodb中查询count(*)较快，所以这样做速度也较快

  Myisam中就更快了，因为Myisam中已经保存好了count(*)的数据，直接计算即可

优化关联查询

前面已经了解了Mysql中关联查询的方式，已经优化器会对关联查询的优化，对关联查询的优化需要增加在查询时的速度

• 在关联表的关联列上建立索引，增加查询速度

  例如：经过优化器之后的关联顺序为 A，B，那么可以在关联的列上创建索引，其实在B表上创建索引就可以了，因为A表作为第一个表，会根据A中数据循环去B表中查询匹配列上的数据。所以A表中不用创建索引，减少索引的额外开销

  前提是这个关联的SQL中没有WHERE条件对A中的列进行过滤，如果有还是要创建索引

• 关联查询的GROUP BY和ORDER BY

  对于关联查询中的GROUP BY和ORDER BY，根据上面了解的关联查询中的排序优化，如果关联查询中的排序涉及到第二个表或者两个表则会使用临时表进行排序。所以一般关联查询中的GROUP BY和ORDER BY最好在第一个表中的一个字段，则可以使用到索引。

优化子查询

子查询、DISTINCT、UNION等在查询的过程中都会产生临时表。临时表会有创建临时表、数据填充到临时表、销毁临时表等一系列较慢的操作，临时表没有索引。

子查询可以改写为关联查询，则可以使用索引来进一步提高查询效率

优化GROUP BY和ORDER BY

GROUP BY和ORDER BY在可以使用索引的情况下是较快的，如果无法使用索引则只能通过临时表或者文件排序来查询，效率较慢。

可以通过优化器的提示SQL_BIG_RESULT和SQL_SMALL_RESULT来提示使用内存临时表提高效率

优化LIMIT分页

分页条件的偏移量较大的情况下，就算每次查询数据量很少，因为偏移量大，所以查询的效率也是较慢的，因为在偏移量大的情况下，需要扫描大量的数据，然后并将偏移量之前的数据丢弃掉。

• 没有优化的SQL

  select * from test limit 1000,10;

  扫描1010条数据，并将前1000条数据丢弃，返回10条数据，这样的效率相对较慢

• 优化分页查询-方式1

  select * from test a inner join (select id from test limit 1000, 10) b on a.id = b.id;

  使用关联查询，并通过子查询先查询出来10条数据的ID，然后进行匹配到指定的数据回表查询10次，返回所有的数据。（利用了查询ID时无需回表，覆盖索引的方式快速查询10条数据，然后再回表查询所有的数据并返回）

• 优化分页查询-方式2

  select * from test where id > 1000 limit 10;

  依据上次查询到的最大的ID，使用Id来限制查询的范围，并返回10条数据，查询效率也非常高。

优化FOR UPDATE

for update是在查询语句中对查询的数据进行加排它锁，让其他对这些数据进行操作的操作进行阻塞。

• 修改数据时，会先查询然后再修改，那么如果并发条件下，可能查询之后进行更改，会出现数据的不一致性，虽然在当前的事务中因为有MVCC不会出现不可重复读，但是会出现数据覆盖的情况

  • 解决方式：查询的时候加锁，保证查询和更新是完整的原子性操作

  -- 加写锁查询
  select id from test where age = 10 for update;
  -- 使用查询到的Id进行更新
  update test set name = 'test' where id = 10;
  

  • 优化：无需通过先查询加锁，然后再更新（更新的时候也还会加排他锁）

    提交事务的速度更快，持有锁的时间变短，减少锁竞争和阻塞时间，提升执行效率

  update test set name = 'test' where age = 10;
  

总结

• 了解查询语句的执行过程

• 了解SQL优化器会如何对SQL进行优化，以及如何影响SQL优化器生成的执行计划，可以更好的来执行更优的SQL（大多数情况，SQL优化器的选择都是相对较好的选择）

• 了解关联查询如何执行

  会先对第一个表中的数据进行查询，然后循环第一个表中的所有数据，依次取第一个表中查询到的每条数据到第二个表中查询。嵌套循环回溯

• 无法对同一个表同时查询和更新（会有共享锁和排它锁的互斥），可以使用子查询来进行优化（子查询会产生临时表，相当于临时表和查询表进行关联）

• 在count()时，没有where条件时，count(*)和count(1)性能基本相同，count(*)是标准写法

• 关联查询需要了解优化之后的表关联顺序，然后再第二个表中关联列上建立索引即可（减少索引的额外消耗）

  因为关联查询的执行为"嵌套循环"

• 子查询因为会产生临时表，在表的数据量较大的情况下，可以使用关联查询来进行优化

  临时的创建、向临时表中填充数据、销毁临时表都是相对耗时的操作

  并且临时表中没有索引

• GROUP BY和ORDER BY中的列最好是索引列。在关联查询中的GROUP BY和ORDER BY后面的列最好是第一个表中的列

  如果是第一个表中的列进行排序和分组，则可以使用索引直接排序或分组，如果是第二个表或者两个表都有，则现将关联查询的结果放入到临时表中，然后对临时表进行排序或分组

• LIMIT分页SQL在偏移量过大的情况则会非常慢，可以采用关联查询（覆盖索引子查询）或者使用ID来进行限定查询的条数

• SELECT...FOR UPDATE;然后再UPDATE在查询时，可以直接合并到UPDATE语句中，减少锁竞争和阻塞时间

彩蛋

• Mysql中in和exist执行的效率哪个更高？为什么

  一般关联查询中会遵"小表驱动大表"

  大表IN小表

  小表EXIST大表

  • select * from A where id in (select id from B);

  • 执行方式：

    • 先执行子查询select id from B，将子查询的结果集缓存起来
    • 然后根据A中的数据到B的结果集中进行匹配并返回结果集

    int[] B = select id from B
    for(int a : (select * from A)){
    	for(int b : B) {
    		if(a.id == b){// 匹配到则进行下一个的匹配
    			result.add(a)
    			continue;
    		}
    	}
    }
    

    如果内循环的B表足够小，那么每次A表中的数据需要循环匹配的次数就变少，查询速度较快，并且子查询产生的临时表如果数据小则会优化为内存临时表，提升匹配效率

  • select * from A where exist(select 1 from B where A.id = B.id);

  • 执行方式：

    • 先执行select * from A
    • 然后获取A中的每条数据，并到根据关联列ID，每次都到数据库中B表中去匹配数据

    int[] A = select * from A
    for(int a : A) {
    	boolean exist = select * from B where id = a.id;
    	if(exist) {
    		result.add(a)
    	}
    }
    

    会查询所有的数据，然后按照ID依次取B表中查询数据，如果匹配到则加入到结果集。这样的执行方式，则查询的总次数只和A表的数据大小有关，A表数据条数越少则循环的次数就越少。每次都要去B表中查询数据，所以和B表的数据量大小关系并不是特别的大。

• Mysql关联查询SQL是如何执行的

  select * from A a left join B b on a.id = b.id where a.name = 'test';

  sql执行的过程中，会先查询select * from A a where a.name='test'这句sql，然后收集结果集。根据查询的结果集遍历select * from B并通过id进行关联匹配，并通过结果集收集匹配到的数据返回客户端。

  伪代码：

  outer1 = select tb1 where a.name = '1'
  for col1 in outer1:
  	outer2 = select tb2 where id = outer1.id
  	for col2 in outer2:
  		result_out.add(col1.name, col2.age)
  

• Mysql事务是如何实现的？原子性、隔离性、一致性和原子性是通过什么方式实现？

  Mysql的事务主要是为了保证数据的一致性，那么事务是如何保证的呢，主要是通过Mysql的ACDI原则来实现。

  Mysql事务有四大特性：ACDI

  • Atomicity（原子性）：通过InnoDB的undo log日志实现

    • undo log：回滚日志，InnoDB主要用来保证事务的回滚。在操作数据的时候的流程：先将原始数据写入undo log -> 然后将数据在内存中更新 -> 然后将数据写入到redo log-> 提交事务 -> 后台线程刷盘

      如果事务回滚，则从undo log中拿到之前的数据作为当前数据

      Mysql事务的已提交读和可重复读如何实现的呢？

      通过Mysql的MVCC机制实现，在MVCC机制中核心就是每行记录都会有2个隐藏列事务版本号和回滚指针以及在查询时产生的ReadView。

      其中回滚指针就是当前斑斑指向上一个版本的一个指向，上一个版本的数据则保存到undo log中。

  • Durability（持久性）：通过InnoDB的redo log实现

    • redo log：事务日志，InnoDB才有的日志，并不是Mysql Server层的日志，在增加、删除、修改数据的时候，InnoDB会先在innoDB_buffer_pool中对数据进行操作，就是一个缓冲区，现在内存中对数据进行操作，然后将数据同步到redo log中，redo log也会持久化到磁盘中，在每次事务提交或者缓冲区满的时候会将数据刷新到redo log磁盘文件。InnoDB会通过一个后台线程将buffer_pool中的脏数据块刷新到数据磁盘文件中（称为“刷盘”）。

      redo log只是用来将随机I/O变为顺序I/O，提高数据操作效率，并且将数据也会持久化到redo log磁盘文件，保证数据不丢失，如果buffer_pool中的数据还没有刷新到磁盘中断电，则在Mysql重启之后，会从redo log中恢复数据

      为什么将数据都持久化到redo log的磁盘文件，而不直接持久化到数据的磁盘文件呢？

      这是因为，redo log的写操作是循环写，数据文件小，并且磁盘I/O是顺序I/O，而对于数据磁盘文件的操作是随机I/O，效率较慢

  • Consistency（一致性）：事务的目标就是保证一致性，ACI这3大特性都是为了保证一致性

  • Isolation（隔离性）：通过MVCC（读操作）及锁机制（写操作）来实现

• MVCC和隔离级别有什么关系

  • 隔离级别有几种？会有对应的什么问题？

    • 未提交读：脏读、不可重复读、幻读
    • 已提交读：不可重复读、幻读
    • 可重复读：幻读
    • 串行化

    脏读：A事务中可以读取到B事务中没有提交的数据记录

    不可重复读：在同一个事务中，两次相同的查询sql读取到的数据显示不一样，主要针对于其他事务修改了数据。例如：A事务中第一次查询到数据，然后B事务修改了表中的数据，A事务再次读取数据，发现数据不相同

    幻读：不可重复读的特殊，主要针对于新增操作，例如：A事务第一次查询数据只有10条，B事务新增了1条，A事务再次读取，就会读取到11条。

  • Mysql默认的隔离级别是什么？

    Mysql的默认隔离级别是可重复读，解决了幻读

  • Mysql是如何解决幻读的？

    • 如果是快照读：通过MVCC机制解决幻读
    • 如果是当前读：通过Next-key Lock（行锁+间隙锁）解决

    什么是快照读？什么是当前读？

    • 快照读：select * from test;这样的sql就称为快照读，主要是通过MVCC机制在查询时生成的ReadView（相当于快照）来进行查询，防止幻读
    • 当前读：sql加锁就称为当前读，select * from test for update;或者update test set id = 2;这样的sql就称为当前读。当前读会通过行锁并且再加上Mysql自动加上的间隙锁来解决幻读（简单提一下：行锁和间隙锁都不是直接锁数据记录，都是通过锁索引来实现）

    MVCC和锁每个单独列出来都是有许多的内容，这里就先不说啦~，后续会写关于MVCC和锁的相关内容

------ 本文参考书籍：《高性能Mysql》

微信公众号「指尖上的代码」，欢迎关注～

原创不易!!! 点个赞再走呗，欢迎关注，给你带来更精彩的文章！

你的点赞和关注是写文章最大的动力～