Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

18 | 为什么这些SQL语句逻辑相同,性能却差异巨大? #28

Open
git-zjx opened this issue Aug 1, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

commented Aug 1, 2019

案例一:条件字段函数操作

B+ 树依靠同一层兄弟节点的有序性而拥有快速定位能力,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。但优化器并不是要放弃使用这个索引,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引

对于不改变有序性的函数,也不会考虑使用索引,例如:select * from tradelog where id + 1 = 10000

案例二:隐式类型转换
在 MySQL 中,如果查询字段类型和参数类型不一致时,会执行类型转换,也就会触发案例一的情况。
字符串和数字做比较会将字符串转换成数字

案例三:隐式字符编码转换

表连接查询的时候如果两个表的字符集不同则用不上关联字段的索引,有两种办法解决:

  • 将两个表的字符集修改一致
  • 查询时主动转换关联字段字符集

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Aug 1, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
1 participant
You can’t perform that action at this time.