sql 分页_MySQL 隐式转换与分页查询的SQL优化

在介绍之前，先看一下我们的示例表，建表语句如下：

---

隐式转换

当我们对不同类型的值进行比较的时候，为了使得这些数值可比较，MySQL会做一些隐式转化(Implicit type conversion)。

SQL查询语句的条件中字段赋值与字段定义类型不匹配是一种常见的错误用法。

如上，字段account的定义为varchar，在WHERE 条件中，account字段类型与条件赋值两者数据类型不一样，这时是没法直接进行比较的，需要进行类型转换。MySQL的策略是将表中字段全部转换为整型之后再比较，由于函数作用于表字段，引起索引失效，导致全表扫描，正确的写法如下：

---

由隐式类型转换引入的安全问题

隐式类型转换不仅可能引起性能问题，还有可能产生安全问题。加入前端没有WAF防护，那么下面的sql是很容易被注入的。

此时登录页面username输入 OR 1='1 ，password随便输入，这样就生成了下面的查询，有可能登录系统：

---

关于官方的隐试转换说明

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换。
• 两个参数都是字符串，会按照字符串来比较，不做类型转换。
• 两个参数都是整数，按照整数来比较，不做类型转换。
• 十六进制的值和非数字做比较时，会被当做二进制串。
• 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp。
• 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较。
• 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

​

---

LIMIT 分页

分页查询在我们的实际应用中非常普遍，也是最容易出问题的查询场景。比如对于下面简单的语句，一般想到的办法是在name，age，register_time字段上创建复合索引。这样查询条件排序可以有效的利用复合索引，提升查询性能。

如上例子，当 LIMIT 子句变成 “LIMIT 100000, 50” 时，此时我们会发现，只取50条语句为何会这么慢?

原因很简单，MySQL并不知道第100000条记录从什么地方开始，即使有索引也需要从头计算一次，因此会感觉非常的慢，一般我们在做翻页时，是可以获取上一页中的某个数据标志来缩小查询范围的，比如时间，可以将上一页的最大值时间作为查询条件的一部分，SQL可以优化为这样：

---