2018 September 04 mysql

MySql（二）过滤

问题引出

在卡口事件检索的场景中，假设过车事件的表有1000w的记录，共10000个卡口，每个用户只能看到他有权限的卡口的过车记录。

如果把卡口划分为一个个区域，按照区域授权，那么可以在区域上加上个索引，（大概100个区域，一般一个用户一到两个区域的权限）检索的时候使用where precinct = xx or precinct = xxx这种，就可以很简单的通过几个条件找到该用户的有权限看到的过车事件。

但如果一个用户的权限是按照每个卡口授权，一个用户有100到200个左右卡口权限，那么用户在检索事件的时候，sql的过滤语句就变成了where device_id in (xx, xx, xx…)

看上去上面两个区别不大，但是对于mysql，按照区域进行过滤的时候会走索引，而按照device_id 的in的方式，有可能不走索引。为什么呢？

where一定走索引？

首先看下mysql官网的How MySQL Uses Indexes，其中在“MySQL uses indexes for these operations”有说明：

To find the rows matching a WHERE clause quickly.

不是说在“能加快WHERE过滤速度”的时候会用索引么？

但后面还有说明：

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

这里涉及到了一个特性和一个假设，mysql是跑在机械磁盘上的（假设，也是通常的情况），而机械磁盘的顺序读取速度远大于随机的读取（一般特性）。

上面说了两种有索引也不用的场景，一是小表，另外一种是大表但需要访问大多数行情况。

小表的情况，这种表数据很少，可能一次IO多读几页，这张表就读完了，然后去内存中查找，虽然在内存中查找比对次数多，但关键可能就一次IO，而如果通过索引，首先索引这颗树也是需要存到磁盘上了，一次查找先读索引，至少一次IO，在根据索引上的主键去读主键的那颗树（innodb），又至少一次IO，而一般是两次IO，上面说的索引一次IO找到都是比较理想的情况，而一张小表通过一次连续IO读完，确是大概率的情况。

另外一种情况，大表，但可能访问大多数行。也是从IO角度上分析，如果估计出来要从1000w数据中过滤后还剩700w，那么每条数据都走次索引，走次主键索引，还不如直接全盘扫描逐一比对来的快。

分析完了，几个简单结论

即使有索引，即使where条件在索引上，但也不一定使用索引
小表或者大表但预估命中条数多可能能不走索引
随着系统运行，表中的数据条数变化，或某一条的分布变化都可能影响mysql使用索引的方式，所以很多表上的索引都是随着系统运行而变化的，这也是DBA的主要工作之一。

PS：在阿里巴巴开发规范中也有类似的说明：

in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内

如何保证MySQL一定用索引

首先，进入这部分讨论之前，要搞清楚“为什么需要MySQL走索引”，通过上面的分析，有些场景MySQL的优化器会去分析是否有必要走索引，可能有些情况下不走索引查询的响应时间或者对整体的吞吐有利。

首先，MySQL的优化器也是有开销的，比如一张表的索引非常多，优化器会做出选择，要不要走索引，如果走索引，先用那个索引，这个计算的时间，也是一种开销。

如果确定，这个场景应该用索引，向上面的这种情况，不需要MySQL去统计分析，直接用索引就好了，与通过where方式比，还可以用inner join这种方式。

先看下官网上说明MySQL是如何实现join操作的：

MySQL executes joins between tables using a nested-loop algorithm or variations on it.

目前MySQL实现join只通过nested-loop algorithm算法和它的变种，其他的数据库如PostgreSQL还有Hash Join、Merge Join两种方式。

Hash Join

从字面意思看上，使用Hash算法，那么应该是需要加载到内存的来做，而且如果表能放到内存中，往往放到内存中的表不能太大。

连接方式上，受限于数据结构Hash这种方式与B树比，是不能支持范围的操作，只能进行等值连接（=）。

适用的场景是在数据仓库环境下，如果表的纪录数多，效率高。

Merge Join

这种join要对表进行排序，而且主要开销也是在排序的过程，如果两个表的join列已经有聚簇索引，那么适合这种join。

Nested-loop Join

最后说的这种，名字听上去就很朴素，嵌套循环连接，多层for循环：

Table   Join Type
t1      range
t2      ref
t3      ALL

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

MySQL会进行一些优化，比如使用批量块处理的方式。

这种join方式适合于一张大表（join字段有索引）与一张小表（join字段可以没有索引），并且结果不多的场景。之所以需要大表有索引，因为循环的时候是以小表的每一行进行判断，根据小表中的这个字段，去大表的索引中去快速查找满足条件的行，这样进行join速度其实还挺快的。如果对一张有1w条左右的小表和一张百万级别的大表进行inner join，速度大概1秒左右。

上面说了，在join的时候，把小表中的join列的值拿出来去大表中进行满足条件判断，这就要求，两张表join的列的类型是一样的，而且如果是char或varchar，算同种类型，长度也要求一样，具体的原因不是特别清楚，官网的表述：

To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

Understanding Nested Loops Joins
SQL优化（一） Merge Join vs. Hash Join vs. Nested Loop
Nested-Loop Join Algorithms
How MySQL Uses Indexes
Avoiding Full Table Scans