有趣的地方

有趣的地方

mysql性能索引调优易混点总结

文章目录

一、 前言

近几年看了很多和mysql相关的书,文章或视频,但仍然有一些点,看过之后又忘记了,这里做一些笔记来总结一下。

二、explain相关

  1. explain解析后,id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。
  2. select_type列代表的是对应行是简单还是复杂查询。
    1)simple:简单查询。查询不包含子查询和union
    2)primary:复杂查询中最外层的 select
    3)subquery:包含在 select 中的子查询(不在 from 子句中)
    4)derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
  3. 当type是index的时候,代表扫描全索引就能拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,这种查询一般为使用覆盖索引,二级索引一般比较小,所以这种通常比ALL快一些。
  4. 可以通过key_len来判断使用了是吗索引,char(n)和varchar(n)中n代表的是字符数,而不是字节数。
    varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为varchar是变长字符串。

三、索引优化相关

联合索引

  1. 联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描。当然可以使用forceidex强制走索引,但是效率不一定高。可以用覆盖索引优化。
  2. in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描。

索引下推

  • **好处:**索引下推主要是为了减少回表次数,并且只能用于二级索引,innodb主键索引保存的是全行数据,索引下推不会减少回表次数。
  • **原理:**比如对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则
    SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'
    这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和position是无序的,无法很好的利用索引。
    在MySQL5.6之前的版本,这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引,然后拿这些索引对应的主键逐个回表,到主键索引上找出相应的记录,再比对age和position这两个字段的值是否符合。
    MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数。使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后,同时还会在索引里过滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据。

排序和分组相关优化

  1. 在order by和group by中,也能使用到索引,但不会显示在key_len字段中,会在extra中有显示,比如当某个字段用在排序,那么额外字段里没有using filesort。
  2. 对于排序来说,多个相等条件(in),也是范围查询,也会出现using filesort。

分页优化

EXPLAIN select * from employees ORDER BY name limit 90000,5;

  • 对于深度分页,比如limit 10000,10,mysql会查询出前面10010条数据,并舍弃掉10000条数据,只要后面的10条数据这样效率很低。
  • 如果是连续的,可以用 id>10000 limit 5实现。但弊端很大,要保证数据连续,还要保证如果排序了,排序的时候是按照主键排序的。(扫描整个索引并查找到没索引的行,可能要遍历多个索引树)的成本比扫描全表的成本更高,所以优化器放弃使用索引。)
  • 如何优化?
    其实关键是让返回的数据尽可能少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录,SQL改写如下
    select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

这样避免了filesort文件排序,还走了索引。

表关联优化

  • 执行计划中首先执行的是驱动表,后执行的是被驱动表,驱动表一般数量级少。 使用 inner join 时,排在前面的表并不一定就是驱动表。
  • 当使用left join时,左表是驱动表,右表是被驱动表,当使用right join时,右表时驱动表,左表是被驱动表,当使用join时,mysql会选择数据量比较小的表作为驱动表,大表作为被驱动表。

嵌套循环连接 Nested-Loop Join(NLJ) 算法

前提:t2表100行数据,t1表1万行数据。

一次一行循环地从第一张表(称为驱动表)中读取行,在这行数据中取到关联字段,根据关联字段在另一张表(被驱动表)里取出满足条件的行,然后取出两张表的结果合集。

如果被驱动表的关联字段没索引,使用NLJ算法性能会比较低,mysql会选择Block Nested-Loop Join算法。
如果上面使用 Nested-Loop Join,那么扫描行数为 100 * 10000 = 100万次,这个是磁盘扫描。
很显然,用BNL磁盘扫描次数少很多,相比于磁盘扫描,BNL的内存计算会快得多。

从表 t2 中读取一行数据(如果t2表有查询过滤条件的,用先用条件过滤完,再从过滤结果里取出一行数据);
从第 1 步的数据中,取出关联字段 a,到表 t1 中查找;
取出表 t1 中满足条件的行,跟 t2 中获取到的结果合并,作为结果返回给客户端;
重复上面 3 步。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?
用BNL磁盘扫描次数少很多,相比于磁盘扫描,BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询,一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法,有索引的情况下 NLJ 算法比 BNL算法性能更高。

in和exsits优化

原则:小表驱动大表,即小的数据集驱动大的数据集(应建立索引)
in:当B表的数据集小于A表的数据集时,in优于exists

select * from A where id in (select id from B)
先执行B,B相当于小表,B表数据越少,执行越快

发表评论:

Powered By Z-BlogPHP 1.7.3

© 2018-2020 有趣的地方 粤ICP备18140861号-1 网站地图