1,限制语句
分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在类型、名称、create_time字段上加组合索引。这样条件排序都能有效的利用到索引,性能迅速提升。
SELECT *
从操作
类型=癝QLStats”
和名称=' SlowLog '
ORDER BY create_time
限制1000、10; 好吧,可能90%以上的DBA解决该问题就到此为止。但当限制子句变成“限制1000000,10”时,程序员仍然会抱怨:我只取10条记录为什么还是慢吗?
要知道数据库也并不知道第1000000条记录从什么地方开始,即使有索引也需要从头计算一次。出现这种性能问题,多数情形下是程序员偷懒了。
在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的. sql重新设计如下:
SELECT *
从操作
类型=癝QLStats”
和名称=' SlowLog '
和create_time比;“2017-03-16 14:00:00”
ORDER BY create_time限制10; 在新设计下查询时间基本固定,不会随着数据量的增长而发生变化。
2,隐式转换
SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句:
mysql>解释扩展SELECT *
比;从my_balance b
比;在b。症=14000000123
比;和b。isverified是零;
mysql>显示警告;
| 1739 | |警告不能使用ref访问> 3,关联更新,删除
虽然MySQL5.6引入了物化特性,但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成加入。欢迎大家关注我的公种浩【程序员追风】,整理了2019年多家公司java面试题资料100多页的pdf文档,文章都会在里面更新,整理的资料也会放在里面。
比如下面更新语句,MySQL实际执行的是循环/嵌套子查询(相关子查询),其执行时间可想而知。
更新操作
设置状态=坝τ谩?
在o。id(选择id
(选择o.id,
o.status
从操作啊
在o。组=123
和o。状态不在(“做”)
由o.parent秩序,
o.id
限制1)t); 执行计划:
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| | id select_type表| | |型possible_keys关键| | key_len | ref额外| | |行
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
主要| 1 | | |阿指数| | 8主| | | 24 |使用的地方;使用临时|
| 2 |相关子查询| | | | | | | | |不可能的地方发现读完const表
| 3 |中| |阿ref | idx_2, idx_5 | idx_5 | 8 | const | 1 |使用;使用filesort |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 重写为加入之后,子查询的选择模式从相关子查询变成,执行速度大大加快,从7秒降低到2毫秒
更新操作
加入(选择o.id,
o.status
从操作啊
在o。组=123
和o。状态不在(“做”)
由o.parent秩序,
o.id
限制1)t> + - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
| | id select_type表| | |型possible_keys关键| | key_len | ref额外| | |行
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
主要| 1 | | | | | | | | | |不可能的地方发现读完const表
| 2 |中| |阿ref | idx_2, idx_5 | idx_5 | 8 | const | 1 |使用;使用filesort |
+ - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - +
