美文网首页微服务实践
记一次mysql索引优化

记一次mysql索引优化

作者: 语落心生 | 来源:发表于2019-07-08 17:11 被阅读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_iduser_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索引知多少

    mysql索引 总结关于mysql的索引,查询优化,SQL技巧等 1 索引类型 B-Tree索引 Hash索引 ...

  • 记一次mysql索引优化

    下午事真的多的一匹,正好测试送来一批mysql慢查询的日志。于是便看了起来 第一次慢查询 执行计划如下: 索引使用...

  • mysql 查询优化

    参考文章:mysql 如何优化left joinmysql 创建索引和删除索引mysql 查看索引 查看字符编码

  • MySQL(4)应用优化

    MySQL应用优化 4.1-MySQL索引优化与设计 索引的作用 快速定位要查找的数据 数据库索引查找 全表扫描 ...

  • Mysql 相关

    MySQL索引 MySQL索引背后的数据结构及算法原理 覆盖索引和回表操作 MySQL性能优化 MySql表分区详...

  • MySQL,必须掌握的6个知识点

    目录 一、索引B+ Tree 原理 MySQL 索引 索引优化 索引的优点 索引的使用条件 二、查询性能优化使用 ...

  • MySQL相关文章索引(2)

    1.MySQL性能优化 对MySQL语句的性能分析与优化 Mysql 监控 Innodb 阻塞状况 MySQL索引...

  • 17.MySQL优化

    《高性能MySQL》——这本书都有的 “字段”优化总结 “索引”优化总结 索引的优化 “查询SQL”优化总结 “引...

  • mysql性能优化-慢查询分析、优化索引和配置

    mysql性能优化-慢查询分析、优化索引和配置 分类:Mysql/postgreSQL 目录 一、优化概述 二、查...

  • 第三个模块 MySQL-UUID、分词字典、MySQL全文索引

    论mysql5.7.13性能优化之索引优化mysql优化(1)show命令 慢查询日志 explain profi...

网友评论

    本文标题:记一次mysql索引优化

    本文链接:https://www.haomeiwen.com/subject/lftahctx.html