记一次mysql索引优化

2019-07-08 本文已影响0人语落心生

下午事真的多的一匹，正好测试送来一批mysql慢查询的日志。于是便看了起来

第一次慢查询

SELECT d.id, d.name
FROM rec_kb_drugs d
    LEFT JOIN dict_kb_classify c ON d.dept_code = c.CODE
    LEFT JOIN dict_kb_classify cc ON c.parent_id = cc.id
WHERE 1 = 1 
-- AND (d.name LIKE CONCAT(CONCAT(?,?),?)
--  OR cc.value LIKE CONCAT(CONCAT(?, ?), ?))
    AND d.status = 1
ORDER BY d.sort_no ASC

执行计划如下:

12.png

34.png

索引使用情况在possible_keys、key和key_len三列,表的扫描情况在type

type：MySQL在表中找到所需行的方式，或者叫访问类型
性能从最差到最好：ALL < index < range < ref < eq_ref < const/system < NULL

eq_ref的意思是使用唯一索引或者主键扫描，对于某个索引键值，表中只有一条索引记录匹配。通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较慢

rows就是mysql认为必须要逐行去检查和判断的记录的条数，即加索引后受影响的行数

possible_keys指出MySQL能使用哪个索引在该表中找到行
key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

我们发现cc表即dict_kb_classify表。这里就不太清楚了，为什么不直接把同一张表的条件放到where后面呢？

因为mysql在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回。
在使用left jion时，on和where条件的区别如下：

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。

从执行计划可以看出加cc表走了主键索引，且受索引影响的行数为1，证明了表数据量也不多，典型的小表驱动大表的案例。所以考虑在小表上加索引。

既然parent_id和id都是同一张表的，根据mysql的索引类型eq_ref正好支持UNIQUE和PRIMARY，所以对两个字段使用联合索引

13.png

优化结果:

134.png

127.png

row按需扫描的行减少了

第二次慢查询

早上过来一看又一波慢查，顿时感觉整个人都炸了

344.png

执行计划:

356.png

咦！我加了索引呀，怎么没走过去，看了看顺序完全符合最左前缀原则的我，满脸问号

233.png

走的是const类型的查询方式，但仍然造成1s左右的耗时，怀疑是顺序错了..好吧channe_id和user_id换个位置就好了

第三波慢查询


SELECT *
FROM rec_channel
WHERE id IN (
        SELECT channel_id
        FROM rec_attention
        WHERE user_id = '2c90a2f26b669c87016b68a9cfc20420'
            AND product_code = 'PLATFORM'
    )
    OR type = 1
    AND subscribe_flag = 1
    AND product_code = 'PLATFORM'
ORDER BY subscribe_flag DESC
LIMIT 100, 56546

虽然这里选择用IN做内联查询是很耗费性能，建议尽量用子查询，会根据扫描的行数而记录内循环，且建索引后会先走子查询


SELECT *
FROM rec_channel c,
   (
        SELECT channel_id
        FROM rec_attention
        WHERE user_id = '2c90a2f26b669c87016b68a9cfc20420'
            AND product_code = 'PLATFORM'
    )  b,
WHERE c.id = b.id
    OR type = 1
    AND subscribe_flag = 1
    AND product_code = 'PLATFORM'
ORDER BY subscribe_flag DESC
LIMIT 100, 56546

那么为什么要这样做呢？

根据大表分页查询优化的方案,之所以单独查询id是为了通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

举个例子
select * from t where kid =3 and type=1 order by id desc 8,2;
对于Innodb表，系统是根据 idxkidtype 二级索引里面包含的主键去查找对应的行。下图是mysql中两种数据库引擎的结构

23.png

56.png

InnoDB的数据文件本身就是主键聚合索引文件，其他列的索引叶节点上的data存储的主键(不存放数据的地址的原因是地址可能会变)
MyISAM叶节点上的data不是数据本身，而是数据存放的地址

上图我们可以看出二级索引和数据叶子节点不在同一个物理块儿上存储，二级索引与主键的相对无序映射关系，那么也就是说我们只需要先现找出主键索引中数据行（row）的位置，就必须要进行全表扫描每一个索引页和数据行，才能找到对应的值。

因此，我们考虑将id单独拿出来作为索引，进行对应数据行的检索。

555.png

556.png

这样从而避免了全表扫描，跳过前面无关的数据页遍历，可以直接通过索引定位到具体的数据行。

记一次mysql索引优化

猜你喜欢

热点阅读