获取问题SQL

MySQL

  • 慢查询日志
  • 测试工具loadrunner
  • Percona公司的ptquery等工具

TODO

SQL编写技巧

SQL编写有以下几个通用的技巧：

• 合理使用索引

索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；

一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

• 使用UNION ALL替代UNION

UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序

• 避免select * 写法

执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

• JOIN字段建议建立索引

一般JOIN字段都提前加上索引

• 避免复杂SQL语句

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

• 避免where 1=1写法

• 避免order by rand()类似写法

RAND()导致数据列被多次扫描

  • in vs or

对索引字段或非索引字段单个值操作时，两者无异；但是对非索引字段多个值操作，相比in，or效率会随着值的个数增加效率相对下滑

  • group by vs distinct

案例：统计学生表不重复姓名的总数

group by：select count(1) from (select name from student group by name )student_temp

distinct：select count(1) from (select distinct name from student )student_temp

distinct需要将name列中的全部内容都存储在一个内存中，可以理解为一个hash结构，key为col的值，最后计算hash结构中有多少个key即可得到结果,内存消耗可能较大。

而数据库中的group一般使用sort的方法，即数据库会先对name进行排序。而排序的基本理论是，时间复杂为nlogn，空间为1，然后只要单纯的计数就可以了。优点是空间复杂度小，缺点是要进行一次排序，执行时间会较长。

因此选择方案一还是方案二要根据具体的数据而定，一般情况下：

离散型大数据：distinct空间占用较大，在时间复杂度允许的情况下，group 可以发挥空间复杂度优势

集中型大数据：distinct空间占用较小，可以发挥时间复杂度优势

而group by的方式是先将col排序。而数据库中的group一般使用sort的方法，即数据库会先对col进行排序。而排序的基本理论是，时间复杂为nlogn，空间为1.，然后只要单纯的计数就可以了。优点是空间复杂度小，缺点是要进行一次排序，执行时间会较长。

  • like vs in

案例：select count() from student where hobby like ‘%球%’

这个sql作用是统计学生表喜欢球类运动的学生总数

优化方案：select count() from student where hobby in (‘羽毛球’,‘乒乓球’,‘篮球’,‘足球’)

对于一些值集类型的字段，我们可以用in来代替 like

  • 日期函数

案例：select count() yestoday_num from log where date_format(creation_date,’%Y-%m-%d’) = date_sub(curdate() ,interval 1 day )

这个sql用于统计昨天产生的日志条数

优化方案：select count() yestoday_num from log where creation_date between date_add(curdate(), interval -1 day) and curdate();

加索引 ALTER TABLE log ADD INDEX idx_creation_date ( creation_date );

  • 避免在索引列上使用 NOT、 计算，把>=替代>
  • 表连接 替代 exists ； exists 替代 distinct、in ； group by 替代 distinct
  • where子句替换 haveing子句
  • union all 替换union ； union替换or 在是索引的前提下；

文章好文 ： https://blog.csdn.net/n950814abc/article/details/85229520

SOME-PROBLEM

在表关联中，类型隐式转换导致索引失效

参考 https://blog.csdn.net/qq_30051265/article/details/104983532

参考：https://blog.csdn.net/jianzhang11/article/details/102867120