本文主要是介绍面试八股文(一) mySQL的select语句该如何优化？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

面试八股文(一) mySQL的select语句该如何优化？

1. 慢SQL定位与分析

​ 做SQL优化的第一步，就是确定慢SQL，分析出它速度慢的原因。通常，我们通过查看慢SQL日志，利用explain命令对其中的慢SQL进行分析，并以此为依据制定合适的优化方案

1.1 慢SQL日志

1. 查看是否开启慢SQL日志

   show variables like "%slow_query_log%";

2. 开启慢SQL日志

   # 临时设置
   set global slow_query_log=1;
   
   # 永久慢SQL配置
   slow_query_log=1;
   slow_query_log_file=路径;
   

3. 查看慢SQL日志阈值

   show variables like 'long_query_time%';

4. 修改慢SQL日志阈值

   # 临时修改(单位:秒)
   set global long_query_time=3;
   
   #永久慢日志阈值配置(单位:秒)
   long_query_time=3;
   log_output=FILE;
   

1.2 explain

EXPLAIN SELECT * from user where age = '1'

explain结果的字段12列，其中，我们需要重点关注type,key,rows,Extra这几列

1. type

   对表的访问方式，表示MySQL在表中找到所需行的方式，常见的有以下几种方式(从上到下，性能逐渐增加)

   • ALL:全表扫描，性能最差
   • Index:也是进行全表扫描，只不过遍历的是索引树。一般出现在覆盖索引查全部数据，或者order by 索引时会出现。
   • range:有范围的索引扫描，相对于Index，有一定范围限制(>,<,between等)，因此优于Index。
   • ref:查询条件使用了索引(等值查询)，且该索引不是主键或唯一索引
   • ref_eq:相比ref,mySQL知道查找结果集的结果只有1个,例如使用了主键或者唯一索引(等值查询)
   • const和system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
   • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。例如从索引列中取最小值

2. possible_keys和key

   • possible_keys:表示MySQL能使用那个索引在表上查找记录，但不一定在查询时被使用

   • key:表示MySQL实际使用的索引，必然包含在possible_keys中

3. rows

   MySQL根据表统计信息和索引选用情况，估算的扫描行数

4. Extra

   • Using where：SQL使用了where条件过滤数据

   • Using index: SQL所需的所有数据均在一棵索引树上，无需回表

   • Using index condition:命中了索引，但并非所有数据都在索引树上，需要回表查询(可考虑覆盖索引优化)

   • Using filesort:对于得到的结果集，需要对记录进行文件排序(效率较差，考虑order by字段加索引优化)

   • Using temporary:需要建立临时表来暂存中间结果(典型的像group by和order by字段不一致，效率较差，考虑优化)

   • Using join buffer(Block Nested Loop):需要进行嵌套循环计算(典型的有 join表字段没有加索引，需要加索引优化)

2. 索引优化

​ SQL优化，那么最基本，最常见的手段，就是给表添加索引，加快其查询速度。当添加索引的方式有很多，选择合适的索引字段以及索引类型，对SQL效率的提升是巨大的。

2.1 主键索引

​ 最好避免使用随机的字段来作为主键索引，例如使用UUID。相对于使用自增的主键，使用随机主键有以下的缺点。

• 在插入数据时，目标页可能已经刷新到磁盘并从缓存中移除，或者时还没加载到缓存中。InnoDB将不得不从磁盘中读取目标页到内存中，这将导致大量随机I/O
• 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间
• 频繁的页分裂会导致页变的稀疏，并被不规律填充，导致最终数据会有碎片

2.2 唯一索引

​ 如果没有必要，那么能使用普通索引尽量不要用唯一索引，相对于唯一索引，普通索引的效率会更高

• select和delete语句，普通索引和普通索引没有区别。
• update语句，普通索引和唯一索引都需要先将数据的目标页加载到内存中，在内存中做修改，唯一索引相比普通索引多了一步重复性校验，但因为都是在内存中操作，所以效率影响可以忽略不计
• insert语句，相对于唯一索引，普通索引可以利用change buffer优化，不需要将目标页读取到内存中。而唯一索引则为了保证唯一性校验，无法利用change buffer优化。这就导致唯一索引比普通索引多了一次随机I/O操作。普通索引效率高于唯一索引

2.3 复合索引

​ mysql一条sql只能使用一个索引，复合索引相对于单个索引，数据的区分度更高，因此查询的效率更高。使用复合索引，有以下几个需要注意的点

• 最左前缀原则：建立一个(a,b,c)的索引，可以等同于建了(a),(a,b),(a,b,c)三个索引
  • 复合索引的顺序:通常来讲，我们会将选择度较高的列放在前面，但实际开发中，要结合具体的sql(例如存在 group by|order by|distinct，这时候能够避免随机I/O和排序的顺序更为重要)。
  • NULL字段不进索引:这种情况极易容易导致索引失效，所以在建表时，字段竟可能设置为not null

2.4 覆盖索引

​ InnoDB中，主键索引为聚簇索引，数据和主键索引在一起，而其他索引则是非聚簇索引。所以，InnoDB先根据普通索引找到主键，再通过主键查询到具体的数据，这就称为回表。覆盖索引，就是值的索引的字段覆盖了所要查询的所有字段值，所以InnoDB可以直接返回索引，而不需要回表。所以在select语句中，避免使用select *，而是需要什么就查什么，这样能够有效利用覆盖索引提升查询效率。

2.5 索引下推

​ 在Mysql5.6的版本上推出。在使用复合索引时，先根据索引的数据过滤一遍数据(包括因最左前缀原则跳过的索引字段)，减少需要回表查询的行数。

2.6 Hash索引

​ Innodb不支持hash索引，但我们可以自己实现，增对一些较长的字段(例如 身份证号)，若直接建索引，那么可能导致索引节点过大，那么我们可以新增一个字段存需要索引字段的hash值，对新增的字段建立索引。缺点在于，需要新维护一个hash字段，同时这样的索引不能用于范围查询，也不能用于范围查找和order by，group by等操作

3. 缓冲池参数优化

​ 为了提升效率，MySQL都是先将磁盘中的数据读取到内存中(缓冲池)，再在内存中做处理后将结果返回给用户。所以针对缓冲池参数的设置，能极大影响MySQL的查询效率。根据局部性原理，为提高效率，MySQL每次进行I/O操作时，出了目标页之外，还会读取相邻的几页数据，这称之为预读。这些数据保持在内存中，MySQL以优化过的LRU算法进行管理。

3.1 缓冲池大小(innodb_buffer_pool_size )

​ 通常情况下，在满足其他进程正常运行时，缓冲池大小设置的越大越好

3.2 老年代(innodb_old_blocks_pct )

​ 由于为了提高效率，MySQL会进行预读，但预读到内存中的页，并非一定会被读取到，若这些页一直不被读取，那么就会发生预读失效的问题。为了解决这个问题，MYSQL对LRU算法做了改进，将内存中的LUR队列划分为了新生代和老年代(和JVM的不一样)，老年代位于LRU队列的尾部。预读的页一开始会被放在老年代的队列头部，只有被真正读取时，才会移动到新生代头部。这样之，真的发生预读失效时，由于这些页在LRU队列较为靠后的部分，很快就会被淘汰，不会长时间挤占内空间。

3.3 老年代停留时间窗口(innodb_old_blocks_time )

​ 假如碰到模糊查询(like %xx%)的情况，需要大批量扫描读取数据的情况，可能把缓冲池中的所有页都替换出去，导致mysql性能急剧下降，这样的问题称之为缓存污染。MySQL为老年代设置了一个停留时间，只有在老年代停留时间大于该时间的缓存数据，才会移动到新生代头部，这样保证新生代缓存不被轻易替换。

这篇关于面试八股文(一) mySQL的select语句该如何优化？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！