做SQL优化的第一步,就是确定慢SQL,分析出它速度慢的原因。通常,我们通过查看慢SQL日志,利用explain命令对其中的慢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;
EXPLAIN SELECT * from user where age = '1'
explain结果的字段12列,其中,我们需要重点关注type,key,rows,Extra这几列
type
对表的访问方式,表示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表字段没有加索引,需要加索引优化)
SQL优化,那么最基本,最常见的手段,就是给表添加索引,加快其查询速度。当添加索引的方式有很多,选择合适的索引字段以及索引类型,对SQL效率的提升是巨大的。
最好避免使用随机的字段来作为主键索引,例如使用UUID。相对于使用自增的主键,使用随机主键有以下的缺点。
如果没有必要,那么能使用普通索引尽量不要用唯一索引,相对于唯一索引,普通索引的效率会更高
mysql一条sql只能使用一个索引,复合索引相对于单个索引,数据的区分度更高,因此查询的效率更高。使用复合索引,有以下几个需要注意的点
InnoDB中,主键索引为聚簇索引,数据和主键索引在一起,而其他索引则是非聚簇索引。所以,InnoDB先根据普通索引找到主键,再通过主键查询到具体的数据,这就称为回表。覆盖索引,就是值的索引的字段覆盖了所要查询的所有字段值,所以InnoDB可以直接返回索引,而不需要回表。所以在select语句中,避免使用select *,而是需要什么就查什么,这样能够有效利用覆盖索引提升查询效率。
在Mysql5.6的版本上推出。在使用复合索引时,先根据索引的数据过滤一遍数据(包括因最左前缀原则跳过的索引字段),减少需要回表查询的行数。
Innodb不支持hash索引,但我们可以自己实现,增对一些较长的字段(例如 身份证号),若直接建索引,那么可能导致索引节点过大,那么我们可以新增一个字段存需要索引字段的hash值,对新增的字段建立索引。缺点在于,需要新维护一个hash字段,同时这样的索引不能用于范围查询,也不能用于范围查找和order by,group by等操作
为了提升效率,MySQL都是先将磁盘中的数据读取到内存中(缓冲池),再在内存中做处理后将结果返回给用户。所以针对缓冲池参数的设置,能极大影响MySQL的查询效率。根据局部性原理,为提高效率,MySQL每次进行I/O操作时,出了目标页之外,还会读取相邻的几页数据,这称之为预读。这些数据保持在内存中,MySQL以优化过的LRU算法进行管理。
通常情况下,在满足其他进程正常运行时,缓冲池大小设置的越大越好
由于为了提高效率,MySQL会进行预读,但预读到内存中的页,并非一定会被读取到,若这些页一直不被读取,那么就会发生预读失效的问题。为了解决这个问题,MYSQL对LRU算法做了改进,将内存中的LUR队列划分为了新生代和老年代(和JVM的不一样),老年代位于LRU队列的尾部。预读的页一开始会被放在老年代的队列头部,只有被真正读取时,才会移动到新生代头部。这样之,真的发生预读失效时,由于这些页在LRU队列较为靠后的部分,很快就会被淘汰,不会长时间挤占内空间。
假如碰到模糊查询(like %xx%)的情况,需要大批量扫描读取数据的情况,可能把缓冲池中的所有页都替换出去,导致mysql性能急剧下降,这样的问题称之为缓存污染。MySQL为老年代设置了一个停留时间,只有在老年代停留时间大于该时间的缓存数据,才会移动到新生代头部,这样保证新生代缓存不被轻易替换。