自己做了一个关于社区的项目，需要优化一下SQL查询，特此记录一下


文章目录

  • 练习一、
  • 练习二、
  • 练习三


练习一、

  • explain分析用户sql
    访问类型type为ALL类型，查询效率最低
  • 查看索引
    此时 t_user 表中只有一个主键索引
  • 创建索引
  • 再次查看索引
  • 继续分析sql

    type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，得保证查询至少达到range级别，最好能达到ref。
    type达到了index级，Extra为Using index（覆盖索引），避免访问了表的数据行，效率不错，同时出现using where，表明索引被用来执行索引键值的查找。
  • 虽然索引到达了index级，但是还是不够，仅仅只比ALL好一点，重新创建索引。
  • 继续使用explain查看sql

    可以看到，type从index(索引全扫描)变成了ref(使用非唯一索引扫描)，上面两个SQL 索引仅仅只是交换了位置 Type却大不相同是因为 BTREE索引中匹配最左前缀，查询从索引的最左前列开始并且不跳过索引中的列。
    type达到了ref级，是我们创建索引所期待的情况，该SQL语句优化成功

练习二、

  • 通知列表的查询索引
  • 查看sql，type为ALL，需要优化
  • 创建索引，查询列多的情况下尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））
  • 查询该索引优化后的结果
  • type达到了ref级，key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好，ref列；显示索引哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值，ref 列的 const 便是指这种常量。至此优化完成。

练习三

  • 分析sql

    Extra中出现了Using filesort，MySQL中无法利用索引完成排序操作成为“文件排序”，说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，出现 Using filesort 及其不好，需要尽快优化 SQL！！
  • 创建索引，因为查询列很多，所以我们尽量使用了覆盖索引，在content上MySQL只能将BLOB/TEXT类型字段设置索引为BLOB/TEXT数据的前N个字符.索引指定下content的长度就可以了，给其他长度很长的字段也指定下长度
  • 再次分析sql
  • type达到了ref级(使用非唯一索引扫描)，是我们创建索引所期待的情况，我们的表上有一个自增的 id 列，这样新数据的 id 值就会比旧数据的 id 值大，业务会根据一定条件查询出满足条件的，最新的 5 行数据，在Extra列上，Backward index scan 是 MySQL-8.0.x 针对上面场景的一个专用优化项，它可以从索引的后面往前面读，性能上比加索引提示要好的多，该SQL语句优化成功。