1. 慢SQL定位与分析
做SQL优化的第一步，就是确定慢SQL，分析出它速度慢的原因。通常，我们通过查看慢SQL日志，利用explain命令对其中的慢SQL进行分析，并以此为依据制定合适的优化方案
1.1 慢SQL日志
- 查看是否开启慢SQL日志
show variables like "%slow_query_log%";
- 开启慢SQL日志
# 临时设置
set global slow_query_log=1;
# 永久慢SQL配置
slow_query_log=1;
slow_query_log_file=路径;
- 查看慢SQL日志阈值
show variables like 'long_query_time%';
- 修改慢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字段
explain结果的字段12列，其中，我们需要重点关注type,key,rows,Extra这几列
- 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在优化过程中分解语句，执行时甚至不用访问表或索引。例如从索引列中取最小值
- possible_keys和key
-
possible_keys:表示MySQL能使用那个索引在表上查找记录，但不一定在查询时被使用
-
key:表示MySQL实际使用的索引，必然包含在possible_keys中
- rows
MySQL根据表统计信息和索引选用情况，估算的扫描行数
- 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为老年代设置了一个停留时间，只有在老年代停留时间大于该时间的缓存数据，才会移动到新生代头部，这样保证新生代缓存不被轻易替换。
网友评论